Primary key increment wrong

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.

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.

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