Re: pg_dump bug in 7.3.9 with sequences

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> Alvaro Herrera wrote:
>> ISTM this is a bug, but it's not clear to me what is the solution.
>> I can think of two:
>>
>> 1. Changing the default is forbidden
>> 2. When the default is changed, the dependency on the sequence is
>> dropped, and the sequence itself is dropped.

> 3. When the default is changed, the dependency is updated
> to reflect the new sequence. The old sequence is left intact
> as an independent object.

What exactly is the use-case of that (or any other manipulation of a
serial column's default)? There is no point that I can see in just
rolling one sequence object into a serial in place of another. Whatever
parameter change you might need to accomplish can be done with ALTER
SEQUENCE on the original sequence, without replacing the object per se.
(Except for renaming it; but given the way pg_dump handles this stuff,
you do not actually have the option to control the sequence name anyway.)

I also think that altering the default expression is useless --- it's
not a serial column anymore if you do that. It might be worth trying to
teach ALTER COLUMN TYPE to handle the cases of switching a serial column
to a non-serial type or vice versa, but I don't think users should be
allowed to reach in and mess with the default directly.

In short I vote for #1. If you want to support #2 then teach ALTER
COLUMN TYPE to handle it. #3 is simply pointless.

BTW, experimenting with this reveals a different pg_dump issue, which is
that it will not replicate a nondefault set of sequence parameters for a
serial sequence. For instance

dtest=# create table t1 (f1 serial);
NOTICE: CREATE TABLE will create implicit sequence "t1_f1_seq" for serial column "t1.f1"
CREATE TABLE
dtest=# alter sequence t1_f1_seq cycle;
ALTER SEQUENCE

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.)

regards, tom lane