#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 | Resolution:
Keywords: postgres sequence | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-----------------------------------+--------------------------------------
Changes (by hmpf):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Old description:
> 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.
New description:
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 file 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#comment:1>
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/062.16d3764a4a52a91b947e496730a50ff5%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
