id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
27090	pg_get_serial_sequence is broken on postgres, use a lookup in information_schema.columns instead	Hanne Moa	nobody	"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."	Cleanup/optimization	closed	Database layer (models, ORM)	1.10	Normal	worksforme	postgres sequence	chris.jerdonek@…	Someday/Maybe	0	0	0	0	0	0