Re: pg_dump versus SERIAL, round N

Tom Lane wrote:
> Bruce Momjian <bruce@momjian.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@momjian.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +