Re: pg_dump versus SERIAL, round N

Tom Lane wrote:
> 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.

Bit bucket is good. :-)

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

Sounds good. The only user-visible change is that pg_dump no longer
dumps out "SERIAL". but psql \d doesn't show SERIAL either, so I don't
see any problem with that. The only complaint I can see is that someone
who wants pg_dump to dump out SERIAL so it appears just as he created
the table, doesn't get that. Could we have pg_dump do that if the
sequences all match the creation (weren't modified)?

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +