Re: pg_dump bug in 7.3.9 with sequences

Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> writes:
>> pg_dump will just emit "create table t1 (f1 serial)" with no hint that
>> the sequence ought to be set to CYCLE mode. I'm not sure about an
>> appropriate fix offhand --- we can't very well use ALTER SEQUENCE in
>> just this way in the dump, because of the risk of the sequence name
>> being possibly different at reload. (Come to think of it, we are not
>> very good about propagating GRANTs on the sequence either, because of
>> the same risk.)

> I suggest some time ago an alternative syntax for ALTER SEQUENCE.

Could work, but we'd have to support it for GRANT/REVOKE too, which is
starting to get ugly. (Now, if we went down the recently suggested path
of allowing sub-selects to compute the target object names for all
utility statements, this would fall out nicely. But having a special
case in ALTER SEQUENCE and GRANT seems pretty wart-ish.)

Another alternative I was thinking about in the shower this morning is
to have pg_dump treat the sequence as an independent object. So the
dump script would CREATE it, set parameters and GRANTs, just the same as
for a plain sequence, and then do a magic ALTER TABLE command that
attaches it to the serial column, which the script would initially
declare as a plain integer or bigint. This seems relatively clean to me
because it is in the same spirit as the way we handle index constraints
now: they aren't in the initial table definition but get added by ALTER
at a suitable time. However the "magic command" is a big wart of its
own, no doubt. It's not just an ALTER COLUMN TYPE command because you'd
need to be able to specify the name of the sequence to attach. It's not
just an ALTER SET DEFAULT, either, because it would have special
side-effects on pg_depend.

regards, tom lane