#27090: pg_get_serial_sequence is broken on postgres, use a lookup in
information_schema.columns instead
-------------------------------+-------------------------------
Reporter: hmpf | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 1.10
Severity: Normal | Keywords: postgres sequence
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+-------------------------------
pg_get_serial_sequence() is broken because it does '''not''' look up the
actual sequence in use for a table-column pair, it only concats the
tablename and columnname together to yield "tablename_columnname_seq",
which is what django used to do itself prior to postgres 8.1 or so.
To verify: manually alter which sequence to use for a table-column pair by
changing the default value for the column, then manually run "select
pg_get_serial_sequence(tablename, columname);". You will not get back the
sequence you just set, at least on postgres 9.3 and 9.5. If you try
pg_get_serial_sequence() on a child table utilizing postgres' table
inheritance you'll get a single row back with an empty string.
This is not a problem for databases created by django but turns up again
and again on legacy systems. It also means existing django-built columns
cannot switch to a different sequence.
The following methods in
django.db.backends.postgresql_psycopg2.base.DatabaseOperations use the
postgres function pg_get_serial_sequence() to fetch a sequence name:
* last_insert_id
* sequence_reset_by_name_sql
* sequence_reset_sql
I use the function in the attached patch in a fork of the postgres-backend
instead. It looks up the sequence name that is actually in use in the
"information_schema.columns"-table. The SQL's been tested on postgres 9.3
and 9.5, which is what I currently have available.
It would be nice if postgres would fix pg_get_serial_sequence() but we
don't have to wait for that.
--
Ticket URL: <https://code.djangoproject.com/ticket/27090>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/047.be84bc96bd8bb19f4e233e16192dda2c%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
