Thread: pg_dump SERIAL and SEQUENCE
Hi,
I have a problem with pg_dump generating a code with no SERIALs id but with SEQUENCEs instead: if I write in a SERIAL I want to see a SERIAL, even when I reload the database.
We had this conversation before on this mailing list and I was told that's because SERIAL is just a kind of macro generating the same thing as the equivalent code translated using SEQUENCE.
This is not true however, if I create tab_a like this:
Then when I drop tab_b, the SEQUENCE tab_b_colb1_seq is not dropped, however when I drop tab_a, the SEQUENCE tab_a_cola1_seq is dropped too !
I am using PG 8.1 and ALTER SEQUENCE ... OWNED BY does not exist ! ! !
Cheers,
L@u
The Computing Froggy
I have a problem with pg_dump generating a code with no SERIALs id but with SEQUENCEs instead: if I write in a SERIAL I want to see a SERIAL, even when I reload the database.
We had this conversation before on this mailing list and I was told that's because SERIAL is just a kind of macro generating the same thing as the equivalent code translated using SEQUENCE.
This is not true however, if I create tab_a like this:
CREATE TABLE tab_a (and tab_b like this (whic is what pg_dump will do)
cola1 SERIAL
);
CREATE SEQUENCE tab_b_colb1_seq;
CREATE TABLE tab_b(
colb1 integer DEFAULT nextval('tab_b_colb1_seq') NOT NULL
);
Then when I drop tab_b, the SEQUENCE tab_b_colb1_seq is not dropped, however when I drop tab_a, the SEQUENCE tab_a_cola1_seq is dropped too !
I am using PG 8.1 and ALTER SEQUENCE ... OWNED BY does not exist ! ! !
L@u
The Computing Froggy
Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail
Laurent ROCHE <laurent_roche@yahoo.com> schrieb: > Then when I drop tab_b, the SEQUENCE tab_b_colb1_seq is not dropped, however > when I drop tab_a, the SEQUENCE tab_a_cola1_seq is dropped too ! > I am using PG 8.1 and ALTER SEQUENCE ... OWNED BY does not exist ! ! ! Read this message: <28684.1192378480@sss.pgh.pa.us> ,----[ quote Tom Lane ] | In 8.2 and up you can use ALTER SEQUENCE ... OWNED BY ... to establish `---- You see the difference? Regards, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
On Oct 18, 2007, at 1:02 PM, Andreas Kretschmer wrote: > Laurent ROCHE <laurent_roche@yahoo.com> schrieb: >> Then when I drop tab_b, the SEQUENCE tab_b_colb1_seq is not >> dropped, however >> when I drop tab_a, the SEQUENCE tab_a_cola1_seq is dropped too ! >> I am using PG 8.1 and ALTER SEQUENCE ... OWNED BY does not >> exist ! ! ! > > Read this message: <28684.1192378480@sss.pgh.pa.us> > > ,----[ quote Tom Lane ] > | In 8.2 and up you can use ALTER SEQUENCE ... OWNED BY ... to > establish > `---- > > You see the difference? > > > Regards, Andreas Andreas's point is that the OWNED BY clause was added in 8.2 to fix your issue and it is policy to not change functionality of older releases unless it is to fix an actual, proven error. Basically, if your sequence isn't owned by any particular table then it is considered an independent relation and you need to manage it's being dump/restored on its own just like with any other independent relation. Erik Jones Software Developer | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com