PostgreSQL sequence being reset?

Question

On a regular occasion, my Django webapps produce SQL errors on M2M tables.

Each time it turns out the ID sequence is reset to a value within the range of existing rows.

The app performs normal SQL queries such as:

INSERT INTO "myapp_project" ("name") VALUES ('test1') RETURNING "myapp_project"."id"'

which cause errors such as:

IntegrityError: duplicate key value violates unique constraint "myapp_project_pkey"
DETAIL:  Key (id)=(29) already exists.

Then it turns out that the myapp_project_id_seq is pointing to an old ID number:

select currval('myapp_project_id_seq')
29

Which can then be reset by using:

select setval('myapp_project_id_seq', (select max(id) from myapp_project))

However, I can't explain why this is happening. It typically happens on M2M tables in Django. In this case, a normal table with admin-only input. Can someone enlighten me about this?

You are almost certainly inserting id's manually somewhere. Where I couldn't say, but that is where you should start looking. — Richard Huxton, Aug 28 '14 at 17:58
Interesting. @RichardHuxton: does inserting ID's also cause the sequence to be reset? Or would that mean that it was messed up before? — vdboor, Aug 29 '14 at 15:34
no what's happening is you insert rows 1,2,3 using the sequence then do 4,5 manually. Next auto-generated id will be 4 because it doesn't know what you've done. That gives an error. — Richard Huxton, Aug 29 '14 at 16:55

score 1 · Answer 1 · answered Aug 28 '14 at 18:35

This typically happens when you (or somebody) sometimes write values to id explicitly, instead of getting values from the sequence (by default or with nextval()).

Your repair code is missing a pair of parentheses.

SELECT setval('myapp_project_id_seq', (SELECT max(id) FROM myapp_project));

This is a tiny bit shorter & cheaper while doing the same, exactly:

SELECT setval('myapp_project_id_seq', max(id)) FROM myapp_project;

PostgreSQL sequence being reset?

1 Answers1