Thread: What's the best way to deal with the pk_seq sequence value after a restore (bulk loading)?

Hi All,

After a restore, I got a lot errors like this one : "duplicate key value violates unique constraint "bug_pkey"".  After
lookingat the dump file, it has 

CREATE SEQUENCE bug_pk_seq
    INCREMENT BY 1
    MAXVALUE 2147483647
    NO MINVALUE
    CACHE 1;

ALTER TABLE
SELECT pg_catalog.setval('bug_pk_seq', 6818, true);
 setval
--------
   6818
(1 row)

Here is the bug table
CREATE TABLE bug (
    bug_id integer DEFAULT nextval('bug_pk_seq'::text) NOT NULL,
    group_id integer DEFAULT '0' NOT NULL,
    status_id integer DEFAULT '0' NOT NULL,
    priority integer DEFAULT '0' NOT NULL,
    category_id integer DEFAULT '0' NOT NULL,
    submitted_by integer DEFAULT '0' NOT NULL,
    assigned_to integer DEFAULT '0' NOT NULL,
    date integer DEFAULT '0' NOT NULL,
    summary text,
    details text,
    close_date integer,
    bug_group_id integer DEFAULT '0' NOT NULL,
    resolution_id integer DEFAULT '0' NOT NULL
);

Because the current value is 6818, during the restore process, it complained about "duplicate key value violates unique
constraint"bug_pkey, because the value of  bug_pk_seq for a insert has been already been used.  So what is the best way
toresolve this?  Should I set the value for bug_pk_seq to be 1 in the beginning of the dump file? 

Any suggestions?

Mary

"Wang, Mary Y" <mary.y.wang@boeing.com> writes:
> After a restore, I got a lot errors like this one : "duplicate key value violates unique constraint "bug_pkey"".
Afterlooking at the dump file, it has 
> ...
> Because the current value is 6818, during the restore process, it
> complained about "duplicate key value violates unique constraint
> "bug_pkey, because the value of  bug_pk_seq for a insert has been
> already been used.

No, the setting of the sequence doesn't have anything to do with that,
because the dumped data doesn't rely on using the column's default
expression.  It's pretty strange to get such an error during restore,
though.  It implies that the data was inconsistent in the original
database.

Or are you saying that after you've completed the restore, subsequent
attempts to insert get that type of error?  If that's the case, what
you need to do is set the sequence value *higher* than the max value
currently present in the table, not reset it to 1.

            regards, tom lane

Actually I got that type of error during the restore process( I used pg_dump --insert option when doing the dump).  But
theinteresting thing is that even though it flagged as an error, those rows of inserts still made to the table with the
correctbug_id. So I'm not too worried about it right now. 

I've always suspected that the database was inconsistent state.

Thanks
Mary


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Saturday, March 06, 2010 8:06 AM
To: Wang, Mary Y
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] What's the best way to deal with the pk_seq sequence value after a restore (bulk loading)?

"Wang, Mary Y" <mary.y.wang@boeing.com> writes:
> After a restore, I got a lot errors like this one : "duplicate key
> value violates unique constraint "bug_pkey"".  After looking at the dump file, it has ...
> Because the current value is 6818, during the restore process, it
> complained about "duplicate key value violates unique constraint
> "bug_pkey, because the value of  bug_pk_seq for a insert has been
> already been used.

No, the setting of the sequence doesn't have anything to do with that, because the dumped data doesn't rely on using
thecolumn's default expression.  It's pretty strange to get such an error during restore, though.  It implies that the
datawas inconsistent in the original database. 

Or are you saying that after you've completed the restore, subsequent attempts to insert get that type of error?  If
that'sthe case, what you need to do is set the sequence value *higher* than the max value currently present in the
table,not reset it to 1. 

            regards, tom lane

"Wang, Mary Y" <mary.y.wang@boeing.com> writes:
> Because the current value is 6818, during the restore process, it
> complained about "duplicate key value violates unique constraint
> "bug_pkey, because the value of bug_pk_seq for a insert has been
> already been used.  So what is the best way to resolve this?  Should I
> set the value for bug_pk_seq to be 1 in the beginning of the dump
> file?
>
> Any suggestions?

See the following article:

  http://tapoueh.org/articles/blog/_Resetting_sequences._All_of_them,_please!.html

Regards,
--
dim

"Wang, Mary Y" <mary.y.wang@boeing.com> writes:
> Actually I got that type of error during the restore process( I used pg_dump --insert option when doing the dump).
Butthe interesting thing is that even though it flagged as an error, those rows of inserts still made to the table with
thecorrect bug_id. So I'm not too worried about it right now. 
> I've always suspected that the database was inconsistent state.

OK, what probably happened was that the data was loaded okay and then
you got these errors from the commands that attempted to create unique
indexes.  So aside from the problem that your data is inconsistent,
you now also lack indexes.  You probably ought to see about fixing
the duplications so that you can establish the indexes.

            regards, tom lane

> OK, what probably happened was that the data was loaded okay and then
> you got these errors from the commands that attempted to create unique
> indexes.

Isn't it also possible that she was not restoring into an empty database?

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

Scott Ribe <scott_ribe@killerbytes.com> writes:
>> OK, what probably happened was that the data was loaded okay and then
>> you got these errors from the commands that attempted to create unique
>> indexes.

> Isn't it also possible that she was not restoring into an empty database?

Maybe, but then she should have seen complaints about pre-existing
tables and so forth, too.

            regards, tom lane