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

Actually I got that type of error during the restore process( I used pg_dump --insert option when doing the dump). But the interesting thing is that even though it flagged as an error, those rows of inserts still made to the table with the correct bug_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(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Saturday, March 06, 2010 8:06 AM
To: Wang, Mary Y
Cc: pgsql-general(at)postgresql(dot)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(dot)y(dot)wang(at)boeing(dot)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 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