Postgres auto-increment when duplicate key errors occurs

Question

I have a model and migration defined:

module.exports = (sequelize, DataTypes) => {
const user = sequelize.define('user', {
    name: DataTypes.STRING,
    username: DataTypes.STRING,
    email: DataTypes.STRING,
    password: DataTypes.STRING,
},
    {})

user.associate = function (models) {
    // associations can be defined here
}

return user

}

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      name: {
        allowNull: false,
        type: Sequelize.STRING
      },
      username: {
        unique: true,
        allowNull: false,
        type: Sequelize.STRING
      },
      email: {
        allowNull: false,
        type: Sequelize.STRING
      },
      password: {
        allowNull: false,
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('users');
  }
};

When I try to create a new user user.create({...}) that has duplicate key value(email or username in this case) sequelizes throwns an error as expected and even so it increases de primary key auto-increment value.

Example: I create a new user that has id 1, then I try to create 5 times a new user that has duplicate unique keys(username or email), then when I create a user successfully, the primary key id becomes 6.

Why this happens? The correct would be to follow the order, if error then does not increment, only increment when the user is inserted successfully.

FXD · Accepted Answer · 2019-02-05 12:09:05Z

I supposed values are generated by a sequence.

Then this is a normal behavior of sequences in all DBMS.

Imagine the following scenario.

User 1 asks for a new id. He gets id = 1.
User 2 asks for a new id. He gets id = 2.
User 1 fails to insert a record in a dependant table. He performs a rollback.
User 2 succeeds to insert every he needed. He performs a commit.

Now imagine what would be needed to reuse id = 1 with the sequence, since it was not committed.

Roll the sequence back to 1? Complicated as it would need to remember it returned id = 2 to another user.
Replace what User 2 has inserted by id=1? Expensive due to foreign keys and risky (User 2 may use a software that map id=2 to some object in memory, it is too late to change the id in the DB)
Make user 2 wait until user 1 has committed or rolled back his transaction to know what to return? Everybody would be waiting all the time.

On top of that, inserting records with consecutive values in id would still not be enough for it to stay consecutive. The first delete on the table will break it all down.

Conclusion: the cost of ensuring ids are consecutive is insane for the DB performance and the value is non-existant since it can break down at any time.

Collectives™ on Stack Overflow

Postgres auto-increment when duplicate key errors occurs

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related