What would cause Postgres to lose track of the next ID, and how could I fix it?

Question

I mysteriously got an error in my Rails app locally:

PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "users_pkey"
DETAIL:  Key (id)=(45) already exists.

The strange thing is that I didn't specify 45 as the ID. This number came from Postgres itself, which also then complained about it. I know this because when I tried it again I got the error with 46. The brute-force fix I used was to just repeat the insertion until it worked, therefore bringing Postgres' idea of the table's next available ID into line with reality.

500.times { User.create({employee_id: 1010101010101, blah_blah: "blah"}) rescue nil }

Since the employee_id has a unique constraint, any subsequent attempts to create the user after the first successful one would fail. And any previous to the first successful one would fail because Postgres tried to use an invalid id (primary key for the table).

So the brute-force approach works, but it's inelegant and it leaves me wondering what could have caused the database to get into this state. It also leaves me wondering how to check to see whether the production database is similarly inconsistent, and how to fix it (short of repeating the brute-force "fix").

score 1 · Answer 1 · answered Jan 05 '21 at 02:25

This sort of thing happens when rows with specified IDs were inserted into the table. Since the IDs are specified, Postgres doesn't increment its sequence when inserting, and then the sequence becomes out of date with the data in the table. This could happen when manually inserted rows, or copying in rows from a CSV file, or replicating in rows, etc.

To avoid the issue, you simply need to let Postgres always handle the IDs, and never specify the ID yourself. However, if you've already messed up and need to fix the sequence, you can do so with the ALTER SEQUENCE command (using RESTART WITH or INCREMENT).

score 1 · Accepted Answer · answered Jun 24 '22 at 15:41

Finding your Sequence

The first step to updating your sequence object is to figure out what the name of your sequence is. To find this, you can use the pg_get_serial_sequence() function.

SELECT pg_get_serial_sequence('table_name','id');

This will output something like public.person_id_seq, which is the relation name (regclass).

In Postgres 10+ there is also a pg_sequences view that you can use to find all sorts of information related to your sequences. The last_value column will show you the current value of the sequence:

SELECT * FROM pg_sequences;

Updating your Sequence

Once you have the sequence name, there are a few ways you can reset the sequence value:

1 - Use setval()

SELECT setval('public.person_id_seq',1020); -- Next value will be 1021
SELECT setval('public.person_id_seq',1020, False); -- Next value will be 1020

Source

2 - Use ALTER SEQUENCE (RESTART WITH)

ALTER SEQUENCE person_id_seq RESTART WITH 1030;

In this case, the value you provide (ex. 1030) will be the next value returned, so technically the sequence is being reset to <YOUR VALUE> - 1.

3 - Use ALTER SEQUENCE (START WITH, RESTART)

ALTER SEQUENCE person_id_seq START WITH 1030;
ALTER SEQUENCE person_id_seq RESTART;

Using this method is preferred if you need to repeatedly restart to a specific value. Subsequent calls to RESTART will reset the sequence to 1030 in this example.