Opened 5 years ago

Closed 2 years ago

#30681 closed Bug (worksforme)

SQL script from sqlsequencereset doesn't work on Postgres 11.3.

Reported by: Iwan Paolucci Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

After migration to postgres I had to resync the primary key and used the following command.

python manage.py sqlsequencereset app | python manage.py dbshell

After that, the PK was still out of sync and I got PK violations. The output from sqlsequencereset was:

BEGIN;
SELECT setval(pg_get_serial_sequence('"tablename"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "tablename";
COMMIT;

After that I tried a different command which I wrote manually and worked.

SELECT setval('tablename_id_seq', (SELECT MAX(id) FROM tablename)+1)

It seems that the SQL produces by sqlsequencereset doesn't work on some postgres versions.

Postgres Version 11.3

Change History (3)

comment:1 by Mariusz Felisiak, 5 years ago

Resolution: worksforme
Status: newclosed
Summary: SQL script from python manage.py sqlsequencereset doesn't work with Postgres 11.3SQL script from sqlsequencereset doesn't work on Postgres 11.3.
Version: 2.1master

Thanks for this report, however it works for me. Moreover a related test SequenceResetTest.test_generic_relation works on PostgreSQL 11. Can you provide more details? or an example project to reproduce this issue.

comment:2 by bluppfisk, 2 years ago

Resolution: worksforme
Status: closednew

I find the same results on PostgreSQL 13 (Debian 11 bullseye) with PostGIS extension.

After migrating my database from another server with pg_dump/psql, I found that some sequences were no longer in order.
Using ./manage.py sqlsequencereset myapp | psql did not work and I found that some guides tell you to:

SELECT setval('load_load_id_seq', (SELECT MAX(load_id) FROM load)); (in my case)

instead of pg_get_serial_sequence().

comment:3 by Mariusz Felisiak, 2 years ago

Resolution: worksforme
Status: newclosed

After migrating my database from another server with pg_dump/psql, I found that some sequences were no longer in order.

pg_get_serial_sequence() should work, you dumped and loaded a database outside of Django, so it's not an issue in Django itself.

Note: See TracTickets for help on using tickets.
Back to Top