Re: pg_dump bug in 7.3.9 with sequences

>>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.)
>
>
O.k. I will buy that. So I say:

#3 rev2: When the default is changed, the dependency is updated
to reflect the new sequence and the old sequence is dropped.

>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.
>
>
Well that would be fine if pg_dump actually handled the scenario
I presented in my previous email correctly. The problem
is you have situations where colummns became serial columns
after the fact or they are columns that were created in
a dataset before there was a serial data type (such as 7.2).

Sincerely,

Joshua D. Drake


>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
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL