Re: pg_dump versus SERIAL, round N

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Our two SERIAL TODO items are:

> * %Disallow changing DEFAULT expression of a SERIAL column?

> This should be done only if the existing SERIAL problems cannot be
> fixed.

> * %Disallow ALTER SEQUENCE changes for SERIAL sequences because pg_dump
> does not dump the changes

> How would your proposal handle these cases?

Both those TODOs go into the bit bucket. There won't be any reason
to forbid either, because pg_dump will do the right things.

> Would changing the default
> of a SERIAL column detach the column/sequence dependency?

As proposed, changing the default would not delete the OWNED BY
dependency, but you could do that by hand if it were no longer
appropriate. I don't see any risk of accidentally dropping a
still-used sequence, because of the already-added dependencies
for nextval() references. Consider

CREATE TABLE t1 (f1 serial);

CREATE TABLE t2 (f2 int default nextval('t1_f1_seq'));

At this point there's a normal dependency from t1.f1's default
expression to t1_f1_seq, and another one from t2.f2's default.
With my proposal there would also be an auto (not internal
anymore) dependency from t1_f1_seq to the column t1.f1.

If you now do

ALTER TABLE t1 ALTER COLUMN f1 DROP DEFAULT

then the first of the aforementioned dependencies goes away,
but the other two are still there. If you now try, say,

DROP TABLE t1;

it will auto-cascade from t1 to t1_f1_seq, then try to normal cascade
to t2.f2's default, and there error out because you didn't say CASCADE.
At this point you could either CASCADE (and lose the default for t2.f2)
or do ALTER SEQUENCE to move or drop the OWNED BY link.

Almost everything I just said is already how it works today; the
difference is that today you do not have the option to drop t1 without
dropping the sequence, because there's no (non-hack) way to remove the
dependency.

regards, tom lane