Re: pg_dump versus SERIAL, round N

Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
> As far as I understand your proposal I like it, but I'd like to insure
> that the situation where a sequence is used by multiple tables is
> handled correctly. There _are_ databases that reuse a sequence for
> multiple serial-like columns, and pgadmin supports this (including a
> pg_depend insert, which would need a version dependent fix).

What do you think is "correctly"?

It's already the case in 8.1 that saying DEFAULT nextval('foo_seq')
generates a normal dependency from the column default expression to
foo_seq, which means that you can't drop the sequence without saying
CASCADE, and if you say that then all the dependent defaults are
dropped.

My recommendation for a multi-table scenario would be that the sequence
be created "by hand" as a standalone object, in which case those normal
dependencies are the only ones involved, and my proposal won't change
the behavior at all from what it was in 8.1.

If you insist on initially creating the sequence by saying SERIAL for
the first of the tables, and then saying DEFAULT nextval('foo_seq')
for the rest, then under both 8.1 and my proposal you'd not be able to
drop the first table without dropping the sequence (thus requiring you
to say CASCADE so that the other tables' defaults can be dropped).
The difference is that I'm proposing a way to decouple the sequence from
its original owning column and make it into a true freestanding object,
after which you could drop the first table without losing the sequence and
the other defaults.

Basically the proposed command allows you to convert from the case where
a sequence was created by SERIAL to the case where it was created
free-standing, or vice versa.

The other change is that using an AUTO instead of INTERNAL dependency
makes it legal to drop the sequence without dropping the column.
My testing version does this:

regression=# create table zit (f1 serial);
NOTICE: CREATE TABLE will create implicit sequence "zit_f1_seq" for serial column "zit.f1"
CREATE TABLE
regression=# drop sequence zit_f1_seq;
NOTICE: default for table zit column f1 depends on sequence zit_f1_seq
ERROR: cannot drop sequence zit_f1_seq because other objects depend on it
HINT: Use DROP ... CASCADE to drop the dependent objects too.
regression=# drop sequence zit_f1_seq cascade;
NOTICE: drop cascades to default for table zit column f1
DROP SEQUENCE
regression=# \d zit
Table "public.zit"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer | not null

regression=#

Previous versions would have disallowed that DROP even with CASCADE;
the *only* way to get rid of a SERIAL-created sequence was to drop
the owning column (or its whole table) altogether. If you try the
same thing in 8.1 you get

regression=# drop sequence zit_f1_seq cascade;
ERROR: cannot drop sequence zit_f1_seq because table zit column f1 requires it
HINT: You may drop table zit column f1 instead.

AFAICS this doesn't disallow anything you could do before, and it
allows fixing the problems pg_dump is having. Is there something
you need it to do that it doesn't do?

regards, tom lane