Postgresql: SERIAL incremented on failed constraint INSERT

Question

Having a simple table structure like this:

CREATE TABLE test (
    id INT PRIMARY KEY,
    sid SERIAL
);

I noticed if I attempt to insert a row but it fails a constraint test (i.e. PRIMARY KEY constraint), the SERIAL counter will increment anyway, so the next successful insert, sid will be sid + 2 instead of sid + 1.

Is this normal behavior? Any way to prevent this?

Answer 1

Yes, it is by design. Serial datatype uses sequences and this behavior is described in the documentation (Sequence Manipulation Functions):

To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used, even if the transaction that did the nextval later aborts. This means that aborted transactions might leave unused "holes" in the sequence of assigned values.

There is no way to prevent it efficiently. You may however develop your own sequence by creating a one-row table and locking it while fetching the next value.