From: | Raymond Mitchell <rmitchell(at)indyme(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Cc: | Raymond Mitchell <rmitchell(at)indyme(dot)com> |
Subject: | pg_dump'ing sequences that are part of a primary key |
Date: | 2002-09-18 23:33:11 |
Message-ID: | 64D857A8547DD611AE9B00304821BA3114D84F@INDYMEMAIL.INDY.COM |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Hi all,
When I restore a database from the output of pg_dump, some sequences aren't
restored correctly. Specifically, sequences that are associated with a
column that is part of a mult-column primary key aren't restored. This is
the output of pg_dump (run with the -d flag to force inserts) for such a
sequence. These statement appear in the order they are produced by pg_dump
with intervening statements removed:
CREATE SEQUENCE "next_div_id" start 1 increment 1 maxvalue 2147483647
minvalue 1 cache 1;
CREATE TABLE "division_tbl" (
"division_name" character varying(25) NOT NULL,
"division_id" integer,
"level_id" integer NOT NULL,
Constraint "division_pkey" Primary Key ("division_name", "level_id")
);
INSERT INTO "division_tbl" VALUES ('TST',1,1);
INSERT INTO "division_tbl" VALUES ('11',2,2);
INSERT INTO "division_tbl" VALUES ('500',3,3);
INSERT INTO "division_tbl" VALUES ('11',4,4);
INSERT INTO "division_tbl" VALUES ('12',5,2);
CREATE TRIGGER "next_divsion_id" BEFORE INSERT OR UPDATE ON "division_tbl"
FOR EACH ROW EXECUTE PROCEDURE "autoinc" ('division_id', 'next_div_id');
SELECT setval ('"next_div_id"', 1, true);
Notice that the table being dumped contains five values (hence the five
INSERT statements) with division_id's 1 through 5, but the next_div_id is
being set to the starting value of 1 instead of 5. Also note that the
Primary Key for the division_tbl table is set to both the division_name and
division_id columns. When a similar table whose Primary Key is ONLY the
sequenced column "division_id" is dumped, the next_div_id is correctly
dumped as 5.
Shouldn't pg_dump set the sequence value to 5, regardless of whether the
referenced column is part of a multi-part primary key?
Thanks,
Ray
From | Date | Subject | |
---|---|---|---|
Next Message | Klaus Sonnenleiter | 2002-09-19 02:40:36 | Re: An Oracle opnion |
Previous Message | Andrew Sullivan | 2002-09-18 21:18:22 | Re: index performance question |