Re: Sequences/defaults and pg_dump

"John D. Burger" <john(at)mitre(dot)org> writes:
> Tom Lane wrote:
>> The correct solution to this is to forbid ALTER COLUMN SET DEFAULT on
>> a serial column, but we haven't gotten around to enforcing that yet.

> Is this per the Standard?

SERIAL isn't in the standard.

> If so, then the oft-repeated mantra that
> SERIAL is simply a macro for an INTEGER column with a particular
> DEFAULT seems a bit misleading ...

It started out that way, but we've been gradually moving in the
direction of making it a more integrated thing. It's already true that
you're not allowed to drop the serial column's sequence, and pg_dump has
special behavior for it (this is the bit that the OP thinks is a bug).

This is all in the name of preventing people from shooting themselves
in the foot, so forbidding changing the default expression seems like
a logical next step.

Now, the other direction we could go in is to back off all that and try
to make SERIAL just a table-creation macro again, as it was in the
beginning. It occurs to me that 8.1 has better solutions for the key
problems that the sequence-to-column binding was intended to prevent.
Even without SERIAL, you can't drop a sequence that some default is
depending on:

regression=# create sequence s1;
CREATE SEQUENCE
regression=# create table foo (f1 int default nextval('s1'));
CREATE TABLE
regression=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+---------------------------------
f1 | integer | default nextval('s1'::regclass)

regression=# drop sequence s1;
NOTICE: default for table foo column f1 depends on sequence s1
ERROR: cannot drop sequence s1 because other objects depend on it
HINT: Use DROP ... CASCADE to drop the dependent objects too.
regression=#

and the former bugaboo of renaming the sequence is gone too:

regression=# alter table s1 rename to foobar;
ALTER TABLE
regression=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+-------------------------------------
f1 | integer | default nextval('foobar'::regclass)

regression=#

So the only extra thing that a SERIAL column still does for you is
to auto-drop the sequence if you drop the table or column. Maybe
that bit of hand-holding isn't worth the inconsistency of having
SERIAL be a little bit more than a macro.

regards, tom lane