Opened 5 years ago
Closed 3 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 , 5 years ago
Resolution: | → worksforme |
---|---|
Status: | new → closed |
Summary: | SQL script from python manage.py sqlsequencereset doesn't work with Postgres 11.3 → SQL script from sqlsequencereset doesn't work on Postgres 11.3. |
Version: | 2.1 → master |
comment:2 by , 3 years ago
Resolution: | worksforme |
---|---|
Status: | closed → new |
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 , 3 years ago
Resolution: | → worksforme |
---|---|
Status: | new → closed |
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.
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.