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
– Richard Huxton, Commented Aug 28, 2014 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
– vdboor, Commented Aug 29, 2014 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
– Richard Huxton, Commented Aug 29, 2014 at 16:55

Erwin Brandstetter · Accepted Answer · 2014-08-28 18:35:45Z

2

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;

answered Aug 28, 2014 at 18:35

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL sequence being reset?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related