Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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?


pgsql-admin by date

Next:From: Klaus SonnenleiterDate: 2002-09-19 02:40:36
Subject: Re: An Oracle opnion
Previous:From: Andrew SullivanDate: 2002-09-18 21:18:22
Subject: Re: index performance question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group