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.

Table of Contents

Sequences: Avoid manual values

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

test=# CREATE TABLE t_data (
    id          serial     PRIMARY KEY, 
    payload     text
);
CREATE TABLE
test=# INSERT INTO t_data (payload) VALUES ('foo') RETURNING *;
 id | payload
----+---------
  1 | foo
(1 row)

INSERT 0 1
test=# INSERT INTO t_data (payload) VALUES ('bar') RETURNING *;
 id | payload
----+---------
  2 | bar
(1 row)

INSERT 0 1

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

test=# CREATE TABLE t_data (

id serial PRIMARY KEY,

payload text

);

CREATE TABLE

test=# INSERT INTO t_data (payload) VALUES ('foo') RETURNING *;

id | payload

----+---------

1 | foo

(1 row)

INSERT 0 1

test=# INSERT INTO t_data (payload) VALUES ('bar') RETURNING *;

id | payload

----+---------

2 | bar

(1 row)

INSERT 0 1

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:

test=# INSERT INTO t_data VALUES (3, 'bang') RETURNING *;
 id | payload
----+---------
  3 | bang
(1 row)

INSERT 0 1
test=# INSERT INTO t_data (payload) VALUES ('boom') RETURNING *;
ERROR: duplicate key value violates unique constraint 't_data_pkey'
DETAIL: Key (id)=(3) already exists.

1

2

3

4

5

6

7

8

9

10

test=# INSERT INTO t_data VALUES (3, 'bang') RETURNING *;

id | payload

----+---------

3 | bang

(1 row)

INSERT 0 1

test=# INSERT INTO t_data (payload) VALUES ('boom') RETURNING *;

ERROR: duplicate key value violates unique constraint 't_data_pkey'

DETAIL: Key (id)=(3) already exists.

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:

iMac:pg_sequence_fixer hs$ make install
/bin/sh /usr/local/lib/postgresql/pgxs/src/makefiles/../../config/install-sh -c -d '/usr/local/share/postgresql/extension'
/bin/sh /usr/local/lib/postgresql/pgxs/src/makefiles/../../config/install-sh -c -d '/usr/local/share/postgresql/extension'
/bin/sh /usr/local/lib/postgresql/pgxs/src/makefiles/../../config/install-sh -c -d '/usr/local/Cellar/postgresql/13.1/share/doc/postgresql/extension'
/usr/bin/install -c -m 644 .//pg_sequence_fixer.control '/usr/local/share/postgresql/extension/'
/usr/bin/install -c -m 644 .//pg_sequence_fixer--*.sql '/usr/local/share/postgresql/extension/'
/usr/bin/install -c -m 644 .//README.md '/usr/local/Cellar/postgresql/13.1/share/doc/postgresql/extension/'

1

2

3

4

5

6

7

iMac:pg_sequence_fixer hs$ make install

/bin/sh /usr/local/lib/postgresql/pgxs/src/makefiles/../../config/install-sh -c -d '/usr/local/share/postgresql/extension'

/bin/sh /usr/local/lib/postgresql/pgxs/src/makefiles/../../config/install-sh -c -d '/usr/local/Cellar/postgresql/13.1/share/doc/postgresql/extension'

/usr/bin/install -c -m 644 .//pg_sequence_fixer.control '/usr/local/share/postgresql/extension/'

/usr/bin/install -c -m 644 .//pg_sequence_fixer--*.sql '/usr/local/share/postgresql/extension/'

/usr/bin/install -c -m 644 .//README.md '/usr/local/Cellar/postgresql/13.1/share/doc/postgresql/extension/'

The extension is now available and one can enable it:

test=# CREATE EXTENSION pg_sequence_fixer;
CREATE EXTENSION

1 2	test=# CREATE EXTENSION pg_sequence_fixer; CREATE EXTENSION

Finally, you can run the procedure:

test=# SELECT pg_sequence_fixer(1000, false);
NOTICE: setting sequence for t_data to 1003
 pg_sequence_fixer
-------------------

(1 row)

1

2

3

4

5

6

test=# SELECT pg_sequence_fixer(1000, false);

NOTICE: setting sequence for t_data to 1003

pg_sequence_fixer

-------------------

(1 row)

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.