Fixing SQL ID Sequences in Postgres

Problem

When we perform manual operations with our database, there's a possibility the RDBMS (Relational Database Management System) will lose track of the last primary key value. Situations where this is likely to occur may include manual inserts, restoring from backups, or other data imports. When we forget to reset the primary key sequence, we'll run into issues the next time we try to insert a new record when the new record's primary key collides with a primary key already in the database.

Solution

In Postgres (or PostgreSQL), the command-line interface (CLI) makes it easy to determine where the sequence left off, and then reset it back to the proper value.

First, let's login to our database CLI and take a look at what tables are available.

>> psql -U <username> -d <databasename>
db-> \dt;

                          List of relations
 Schema |               Name               | Type  |      Owner      
--------+----------------------------------+-------+-----------------
 public | example_table                    | table | example_user

Now, let's examine the last primary key stored in the "example_table" and compare this with what Postgres has stored for the sequence. When retrieving the sequence value, the sequence name will likely by in the form: <table_name>_<column_name>_seq. So, for our example table on the id column we would have "example_table_id_seq."

db-> SELECT MAX(id) FROM example_table;
 max 
-----
  100
(1 row)

db-> SELECT * FROM example_table_id_seq;

 last_value | log_cnt | is_called 
------------+---------+-----------
          1 |      32 | t

From the above, we can see that our most recent record has a primary key of 100 whereas Postgres has the sequence currently recorded as 1. The sequence information shows "is_called" as "t" for True which means that the Postgres will try to insert the next record with primary key 2 since the "last_value" has been called/used. If "is_called" were "f" for False, Postgres would try to insert the next record with a primary key of 1 instead.

Now that we know an insert will cause the new record's primary key to collide with an existing primary key, let's reset the sequence to the appropriate value. Since we have primary keys through 100, we want to reset the sequence at 101. We want 101 and not 100 because the command "ALTER SEQUENCE" will set "is_called" to "f." The following will reset our sequence to the desired value:

db-> ALTER SEQUENCE example_table_id_seq RESTART WITH 101;
ALTER SEQUENCE

Let's double check our sequence value to make sure the change is what we expected.

 last_value | log_cnt | is_called 
------------+---------+-----------
        101 |      32 | f

We can see that the sequence has been properly reset. The next record will be inserted with a primary key of 101 and sequence can carry forward without any gaps or collision.

Other Considerations

If the database is in production, we need to be careful how we reset the sequence. If new records are inserted within the time it takes you to perform the above operations, your new sequence may still collide with existing records.

My first choice would be to take the whole application down for maintenance given this operation is critical. That way, there isn't any concern with data changing while database management operations are being performed.

Assuming taking the application down isn't desirable for one reason or another, my next choice would be to lock the table so that data in the table can be read but inserts that may cause us problems won't occur. We can accomplish this with:

db-> BEGIN;
BEGIN
db-> LOCK TABLE <table_name> IN SHARE MODE;
LOCK TABLE
db-> <do your thing>
db-> COMMIT;
COMMIT

Note, lock commands need to be supplied within a transaction block (BEGIN/COMMIT). When the transaction block is closed, the table lock will be released. You don't need to perform an action like "UNLOCK TABLE" which isn't valid SQL for Postgres. If you don't want to commit your changes, close the block with "ABORT" or "ROLLBACK" which are equivalent statements in Postgres. Also, it's best to lock a table using "SHARE MODE" or you risk interrupting important background processed like Automatic Vacuuming.

Final Thoughts

Resetting sequences can be a normal part of database management. Fixing a bad sequence value consists of determining what the current sequence value is and reestablishing the correct value using ALTER SEQUENCE. Be careful to understand whether the sequence value is the value where the next record will be inserted or if that value will be incremented. The column "is_called" tells us this. Values that are True let us know the sequence will be incremented whereas False values will use the sequence value as is. Last, be careful when performing actions on databases when in production. And before anything, it never hurts to back up a database.