Thread: Problem with restoring database from a pg_dump generated script.

In short, when I try to restore a database from a pg_dump
generated SQL script, then try to insert additional records
into the restored datbase, it gives me a duplicate key error.

I am using the following version of the PostgreSQL database:

PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66

and I am having a problem with dumping and restoring data.

First, I dump an existing database to a text sql file with the
following command from the shell:

pg_dump -D -n logindb > testdump.sql

It produces the following file:

=== start of testdump.sql file
\connect - jdavis
CREATE SEQUENCE login_primary_key_seq start 1 increment 1 maxvalue
2147483647 minvalue 1  cache 1 ;
SELECT nextval ('login_primary_key_seq');
CREATE TABLE login (
    primary_key int4 DEFAULT nextval('login_primary_key_seq'::text) NOT NULL,
    user_index int4 NOT NULL,
    hash character varying(25) NOT NULL,
    last_used timestamp NOT NULL,
    expired character varying DEFAULT 0 NOT NULL,
    PRIMARY KEY (primary_key)
);
INSERT INTO login (primary_key,user_index,hash,last_used,expired) VALUES
(1,1,'W5GGk7yEM+0Kw260tDeBaA==','2001-04-25 15:56:43-05','1');
INSERT INTO login (primary_key,user_index,hash,last_used,expired) VALUES
(2,1,'ufrmu+395SIfuBC6ombmwA==','2001-04-26 08:45:10-05','1');
INSERT INTO login (primary_key,user_index,hash,last_used,expired) VALUES
(3,1,'A7V4Rvo20cqb4CFDnWkOvg==','2001-04-26 11:14:26-05','1');
INSERT INTO login (primary_key,user_index,hash,last_used,expired) VALUES
(4,1,'fIlh5Su9iMc2KXt5tqCAEw==','2001-04-26 13:02:38-05','1');
INSERT INTO login (primary_key,user_index,hash,last_used,expired) VALUES
(5,1,'9eLdWa6wl/DrqOpq/0QSWA==','2001-04-26 16:39:51-05','1');
INSERT INTO login (primary_key,user_index,hash,last_used,expired) VALUES
(6,1,'x9P/sRNj4rFenORY+POq1w==','2001-05-01 08:48:20-05','1');
INSERT INTO login (primary_key,user_index,hash,last_used,expired) VALUES
(7,1,'4nT7+E4wBxKChA1BDgW0+Q==','2001-05-03 14:37:27-05','1');
INSERT INTO login (primary_key,user_index,hash,last_used,expired) VALUES
(8,1,'H9QDcbiNVLGlMXnngo9ICQ==','2001-05-03 16:51:26-05','1');
=== end of testdump.sql file

Now, I do the following commands from the shell:

dropdb logindb
createdb logindb
psql -e logindb < testdump.sql

This works fine.  It recreates the table and inserts the data
records.  It does give me one notice message:

NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'login_pkey' for
table 'login'

Then I go into the psql utility to add new records.

psql logindb

This works fine and brings me to the psql prompt.  I enter the
following command:

insert into login (user_index,hash,last_used,expired) values
(1,'abcdefghijklmnop','2001-05-07 08:00:00-05','1');

This attempt to insert gives me the following error message:

ERROR: Cannot insert a duplicate key into unique index login_pkey

If I continually try the additional insert, it fails 8 times then
finally succeeds on the 9th try, and when I do the following
query:

select * from login;

it shows that the following record has been appended:

9,1,'abcdefghijklmnop','2001-05-07 08:00:00-05','1'

This suprises me, since the SQL in the testdump.sql file is
created by the pg_dump utility, not by me, so I would have thought
it would have everything necessary to restore the database without
a problem.  I also would have expected that the INSERT INTO
statements in the testdump.sql would have appropriately updated
the primary key.

Is there something missing from the original database that got
dumped, or something I have to manually do to the dumped script
that will allow a no-problem restore?  Or is this a bug in
PostgreSQL?  I would appreciate any help you can offer.

Jerry Davis
TEAM Development
jdavis@teamdev.com

"Jerry Davis" <jdavis@teamdev.com> writes:
> CREATE SEQUENCE login_primary_key_seq start 1 increment 1 maxvalue
> 2147483647 minvalue 1  cache 1 ;
> SELECT nextval ('login_primary_key_seq');

> Is there something missing from the original database that got
> dumped, or something I have to manually do to the dumped script
> that will allow a no-problem restore?

Hm.  Apparently pg_dump should have restored this sequence with an
initial value of 9, not 1.  Not sure why it didn't.  If I try

create table foo (f1 serial);
insert into foo default values;
insert into foo default values;
insert into foo default values;

and then dump this in 7.0.*, I get

\connect - tgl
CREATE SEQUENCE "foo_f1_seq" start 3 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('"foo_f1_seq"');
CREATE TABLE "foo" (
        "f1" int4 DEFAULT nextval('foo_f1_seq'::text) NOT NULL
);
COPY "foo" FROM stdin;
1
2
3
\.
CREATE UNIQUE INDEX "foo_f1_key" on "foo" using btree ( "f1" "int4_ops" );

which as you can see initializes the sequence to the right place (the
next nextval() will produce 4).

Can you recall anything about the history of your table that might have
caused the sequence to get reset?

Anyway, fixing the database state just requires issuing a setval() to
set the sequence to the desired place.

            regards, tom lane