pg_dump'ing sequences that are part of a primary key

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

Browse pgsql-admin by date

  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