CODEWITHSUNDEEP
djangopostgresqldjango-modelsdjango-database
vdboor
vdboor

Reputation: 22526

PostgreSQL sequence being reset?

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?

Upvotes: 1

Views: 747

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656754

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;

Upvotes: 2

Related Questions