Primary key increment wrong

Question

I have an issue when I try to create a model in the database using GORM and Gin.

This is my code in the controller:

func CreateSymbol(c *gin.Context) {
    var payload models.Symbol
    if err := c.ShouldBind(&payload); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"message": err.Error()})
        return
    }
    fmt.Println(payload)

    symbol, err := repositories.CreateSymbol(payload)
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"message": err.Error()})
        return
    }

    c.JSON(http.StatusOK, gin.H{"data": symbol})
}

And this is my function in the repository:

func CreateSymbol(model models.Symbol) (models.Symbol, error) {
    result := boot.DB.Create(&model)

    if result.Error != nil {
        return models.Symbol{}, result.Error
    }

    return model, nil
}

And this is my model and migration:

type Symbol struct {
    gorm.Model
    Code        string
    Icon        string
    Status      string
    MaxLeverage uint32
    Precision   uint32
    MinQty      float64
}

type Symbol struct {
    gorm.Model
    Code        string `gorm:"index;unique"`
    Precision   uint32
    MaxLeverage uint32
    MinQty      float64
    Icon        string
    Status      string `gorm:"index"`
}

This is my last record in the database:

        {
            "ID": 11,
            "CreatedAt": "2023-02-22T14:51:27.52Z",
            "UpdatedAt": "2023-02-22T14:51:27.52Z",
            "DeletedAt": null,
            "Code": "KLC2",
            "Icon": "/images/klc1.png",
            "Status": "inactive",
            "MaxLeverage": 20,
            "Precision": 4,
            "MinQty": 0
        }

When I completed my code and ran it from Postman, everything seemed OK until I accidentally create another symbol that duplicate Code field. The error was returned:

{
    "message": "Error 1062 (23000): Duplicate entry 'KLC2' for key 'symbols.code'"
}

Then, I changed the request data. The error is gone, but the symbol was created with ID is 13 that I expected is 12.

{
    "data": {
        "ID": 13,
        "CreatedAt": "2023-02-22T16:08:08.827Z",
        "UpdatedAt": "2023-02-22T16:08:08.827Z",
        "DeletedAt": null,
        "Code": "KLC3",
        "Icon": "/images/klc3.png",
        "Status": "inactive",
        "MaxLeverage": 20,
        "Precision": 4,
        "MinQty": 0
    }
}

I am not sure what I am doing wrong.

check if gorm has already increased the sequence in some table it maintains, if that is the case then it would have a gap in between. After the app runs the migration it will create some few additional tables — Neenad, Feb 22 '23 at 16:49

mkopriva · Answer 1 · 2023-02-22T18:03:31.250

1

I'm going to assume that you're using PostgreSQL.

There is no issue. The behaviour you described is as advertised, i.e. exactly according to the specification. In other words, that is how sequences work in PostgreSQL, and a sequence is what is used by columns with serial types and also by the new GENERATED ... AS IDENTITY columns.

Read the CAUTION block in https://www.postgresql.org/docs/15/functions-sequence.html

To avoid blocking concurrent transactions that obtain numbers from the same sequence, the value obtained by nextval is not reclaimed for re-use if the calling transaction later aborts. This means that transaction aborts or database crashes can result in gaps in the sequence of assigned values. That can happen without a transaction abort, too. For example an INSERT with an ON CONFLICT clause will compute the to-be-inserted tuple, including doing any required nextval calls, before detecting any conflict that would cause it to follow the ON CONFLICT rule instead. Thus, PostgreSQL sequence objects cannot be used to obtain “gapless” sequences.

. . .

If you're using MySQL, of which I have less knowledge, the described behaviour, although probably implemented differently, is also likely normal.

edited Feb 22 '23 at 18:03

answered Feb 22 '23 at 17:35

mkopriva

35,176
4
57
71

Thanks, @mkopriva but I am using MySQL. I got the explanation from #pilotpin and I think this is the behavior of Gorm to keep ID increasing in the sequence number. – Duy Nguyen Feb 23 '23 at 02:51
@DuyNguyen pilotpin's answer is inaccurate. The gaps have nothing to do with gorm. Why would gorm try and manage auto incrementing values if it's already being done, perfectly well, by the backends on which it depends? As I've already mentioned in my answer, although implemented differently, even MySQL's AUTO_INCREMENT is going to have gaps just the same as PostgreSQL's sequences. https://stackoverflow.com/questions/16582704/auto-increment-primary-leaving-gaps-in-counting. – mkopriva Feb 23 '23 at 05:10
Thank you! You are right @mkopriva. I find out that this depends on MySQL config mode of AUTO_INCREMENT just like you said. I am using MySQL 8, the default config of `innodb_autoinc_lock_mode = 2` that means `interleaved` lock mode. It comes with the cost that `traditional` mode is slower than `interleaved` and `consecutive` mode – Duy Nguyen Feb 23 '23 at 09:02

pilotpin · Answer 2 · 2023-02-22T18:22:07.603

Gorm will have increase the sequence number in advance of the failure. This is normal behavior for GORM and it keeps the sequence in a separate table.

Even putting the operation in a transaction will not prevent gorm/your database from agressively allocating keys.

If you really need sequntial keys with no gaps you would have to manage the ID allocation yourself and allocate the key in the same transaction as you create the object.

The following snippet will do that.


    type TestObj struct {
        gorm.Model
        Key string `gorm:"uniqueIndex"`
    }
    
    type IdTable struct {
        ID    string `gorm:"uniqueIndex primaryKey"`
        Value uint
    }
    func InsertTestObj(db *gorm.DB, obj TestObj) (TestObj, error) {
        err := db.Transaction(func(tx *gorm.DB) error {
            idRow := IdTable{ID: "test_objs"}
            tx.Find(&idRow)
            idRow.Value++
            if err := tx.Save(&idRow).Error; err != nil {
                fmt.Printf("Error on %v %v\n", idRow, err)
                return err
            }
            obj.ID = idRow.Value
            if err := tx.Save(&obj).Error; err != nil {
                fmt.Printf("Error on %s %v\n", obj.Key, err)
                return err
            }
            return nil
        })
        if err != nil {
            return TestObj{}, err
        }
        return obj, nil
    }

If I run that in a test case:

    func TestInsertTestObj(t *testing.T) {
        db := DbConnect()
        assert.NotNil(t, db)
    
        o1, err := InsertTestObj(db, TestObj{Key: "val1"})
        assert.Nil(t, err)
        fmt.Println(o1.ID)
        o2, err := InsertTestObj(db, TestObj{Key: "val2"})
        assert.Nil(t, err)
        fmt.Println(o2.ID)
        o3, err := InsertTestObj(db, TestObj{Key: "val3"})
        assert.Nil(t, err)
        fmt.Println(o3.ID)
        o4, err := InsertTestObj(db, TestObj{Key: "val3"})
        assert.NotNil(t, err)
        fmt.Println(o4.ID)
        o5, err := InsertTestObj(db, TestObj{Key: "val4"})
        assert.Nil(t, err)
        fmt.Println(o5.ID)
    }

I get the following output

1
2
3
Error on val3 ERROR: duplicate key value violates unique constraint "idx_test_objs_key" (SQLSTATE 23505)
0
4

Thanks a lot, @pilotpin, It is an accepted answer. I did understand and I think I will keep the default behavior of Gorm. No need to add extra effort to this process. — Duy Nguyen, Feb 23 '23 at 02:48

Primary key increment wrong

2 Answers2