Attached is a fully-worked-out patch to make SERIAL column default expressions refer to the target sequence with a "regclass" literal instead of a "text" literal. Since the regclass literal is actually just an OID, it is impervious to renamings and schema changes of the target sequence. This fixes the long-standing hazard of renaming a serial column's sequence, as well as the recently added hazard of renaming the schema the sequence is in; and it lets us get rid of a very klugy solution in ALTER TABLE SET SCHEMA.
I've arranged for stored regclass literals to create dependencies on the referenced relation, which provides useful improvements even for handwritten defaults: given create sequence myseq; create table foo (f1 int default nextval('myseq'::regclass)); the system will not allow myseq to be dropped while the default expression remains. (This also ensures that pg_dump will emit the sequence before the table.) The patch also fixes a couple of places where code was still looking at the deprecated pg_attrdef.adsrc column, instead of reverse-compiling pg_attrdef.adbin. This ensures that psql's \d command shows the up-to-date form of a column default. (That should have happened quite some time ago; not sure why it was overlooked.) I propose applying this to fix the open issue that ALTER SCHEMA RENAME breaks serial columns. Comments, objections? regards, tom lane
bineyzK29dAnv.bin
Description: seq-regclass.patch.gz
---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings