Fixing out-of-sync sequences in PostgreSQL

Creating auto increment columns in PostgreSQL is easy. Simply use two pseudo data types serial and serial8, respectively, then PostgreSQL will automatically take care of your auto increment columns. However, once in a while problems can still occur. Let us take a look and see.

Sequences: Avoid manual values

To understand the underlying problem, one has to understand how auto increment and sequences work in PostgreSQL:

In this case, everything is fine. But what happens if a manual value is added? Note that PostgreSQL does allow this. Other databases, such as Informix, explicitly prohibit it. However, in PostgreSQL an autoincrement value is really just a sequence which adds a default value. Nothing stops you from breaking things:

The trouble is that the manual insertion does not increment the sequence. Thus, the second INSERT statement is going to fail because the value is already there. This often happens when people import data into the database.

So the main question is: How can we fix these sequences? The answer is: Use pg_sequence_fixer.

Using pg_sequence_fixer

The idea behind pg_sequence_fixer is to have a tool that can easily fix these things with one simple stored procedure call. It automatically looks for all sequences associated with a column, finds the max value, and pumps it up to what is needed.

First clone the repository which can be found here: https://github.com/cybertec-postgresql/pg_sequence_fixer

Then simply install the extension:

The extension is now available and one can enable it:

Finally, you can run the procedure:

The first parameter (= 1000) means that we want to add a safety margin of 1000 to the max value determined by the system. The second parameter controls whether we want to lock those tables now or during the operation. Usually, locks are not desirable, but sometimes they are necessary in case you cannot take down the application in which you are fixing your sequences.

Note that for each sequence fixed, the tool will tell PostgreSQL to issue one log entry (= NOTICE).

Voila, your database is sound and healthy again.

Finally...

Fixing sequences is an important issue you can achieve easily using pg_sequence_fixer. Also: We want to point out that this is just the first incarnation of this tool, so if you are willing to contribute - feel free to submit changes.

If you want to learn more about PostgreSQL right now, consider checking out my post about parallel CREATE INDEX in PostgreSQL.