pg_get_serial_sequence is broken on postgres, use a lookup in information_schema.columns instead

[Hanne Moa]

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.

[Hanne Moa]

Python-function looking up sequence_name in postgres