"Joshua D. Drake" <jd@commandprompt.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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Tom Lane wrote:
> Christopher Kings-Lynne <chriskl@familyhealth.com.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.

Wouldn't a lot of this be solved (I know I am over simplyfing) by making
serial a real type? E.g; if you have type serial it is type serial not
type integer with a default of nextval('sequence'). Thus if I have an
integer with a default of (anything really) that is how it is restore.
If I have a serial, it is a serial and is restored in that manner.

Using this idea, you would get the can't alter default of a serial but
also the ability to alter the default if it is NOT a serial but will
still auto-increment.

Sincerely,

Joshua D. Drake






>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


--
Command Prompt, Inc., your source for PostgreSQL replication,
professional support, programming, managed services, shared
and dedicated hosting. Home of the Open Source Projects plPHP,
plPerlNG, pgManage, and pgPHPtoolkit.
Contact us now at: +1-503-667-4564 - http://www.commandprompt.com



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend