[Django] #30681: SQL script from python manage.py sqlsequencereset doesn't work with Postgres 11.3

[Django]

#30681: SQL script from python manage.py sqlsequencereset doesn't work with
Postgres 11.3
-------------------------------------+-------------------------------------
Reporter: ipa | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 2.1
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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

--
Ticket URL: <https://code.djangoproject.com/ticket/30681>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#30681: SQL script from sqlsequencereset doesn't work on Postgres 11.3.
-------------------------------------+-------------------------------------
Reporter: Iwan Paolucci | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | worksforme
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* status: new => closed
* version: 2.1 => master
* resolution: => worksforme

Comment:

Thanks for this report, however it works for me. Moreover a related test
[https://github.com/django/django/blob/25f21bd2376603c8e233a0a0e5a726a0fdfdd33e/tests/backends/tests.py#L172-L188
SequenceResetTest.test_generic_relation] works on PostgreSQL 11. Can you
provide more details? or an example project to reproduce this issue.

--
Ticket URL: <https://code.djangoproject.com/ticket/30681#comment:1>

[Django]

#30681: SQL script from sqlsequencereset doesn't work on Postgres 11.3.
-------------------------------------+-------------------------------------

Reporter: Iwan Paolucci | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by bluppfisk):

* status: closed => new
* resolution: worksforme =>

Comment:

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()`.

--
Ticket URL: <https://code.djangoproject.com/ticket/30681#comment:2>

[Django]

#30681: SQL script from sqlsequencereset doesn't work on Postgres 11.3.
-------------------------------------+-------------------------------------

Reporter: Iwan Paolucci | Owner: nobody
Type: Bug | Status: closed

Component: Database layer | Version: dev

(models, ORM) | Resolution:
Severity: Normal | worksforme
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* status: new => closed

* resolution: => worksforme

Comment:

> 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.

--
Ticket URL: <https://code.djangoproject.com/ticket/30681#comment:3>