Re: pg_dump versus SERIAL, round N - Mailing list pgsql-hackers
From | Tom Lane |
---|---|
Subject | Re: pg_dump versus SERIAL, round N |
Date | |
Msg-id | 27268.1156086655@sss.pgh.pa.us Whole thread Raw |
In response to | Re: pg_dump versus SERIAL, round N (Andreas Pflug <pgadmin@pse-consulting.de>) |
Responses |
Re: pg_dump versus SERIAL, round N
|
List | pgsql-hackers |
Andreas Pflug <pgadmin@pse-consulting.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
pgsql-hackers by date: