Thread: BUG #13735: pg_get_serial_sequence can be incorrect

The following bug has been logged on the website:

Bug reference:      13735
Logged by:          Colin Knox
Email address:      colin.knox@offers.com
PostgreSQL version: 9.4.1
Operating system:   OSX
Description:

If you create a table with a serial column, pg_get_serial_sequence will
accurately name the sequence that was created along with it.

If you create a NEW sequence object, and "alter table FOO alter column id
set default nextval('NEW_SEQUENCE'::regclass);,  then pg_get_serial_sequence
will still return the name of the original sequence.

You can alter the new sequence and SET OWNER to fix this behavior; but you
can break it again by altering the original sequence and setting owner to
NONE, and then back to table.column.

Basically, pg_get_serial_sequence does not actually check the default value
in the table, it only checks the most recent sequence to have declared
itself owned by that column, regardless of what sequence is actually in use.

colin.knox@offers.com writes:
> Basically, pg_get_serial_sequence does not actually check the default value
> in the table, it only checks the most recent sequence to have declared
> itself owned by that column, regardless of what sequence is actually in use.

Quite, and that's its documented behavior:

pg_get_serial_sequence returns the name of the sequence associated with a
column ... This association can be modified or removed with ALTER SEQUENCE
OWNED BY.  (The function probably should have been called
pg_get_owned_sequence; its current name reflects the fact that it's
typically used with serial or bigserial columns.)

            regards, tom lane