Обсуждение: What's the best way to deal with the pk_seq sequence value after a restore (bulk loading)?
What's the best way to deal with the pk_seq sequence value after a restore (bulk loading)?
От
"Wang, Mary Y"
Дата:
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
Re: What's the best way to deal with the pk_seq sequence value after a restore (bulk loading)?
От
"Wang, Mary Y"
Дата:
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
Re: What's the best way to deal with the pk_seq sequence value after a restore (bulk loading)?
От
Dimitri Fontaine
Дата:
"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