Re: [GENERAL] duplicate key value violates unique constraint andduplicated records
От | Melvin Davidson |
---|---|
Тема | Re: [GENERAL] duplicate key value violates unique constraint andduplicated records |
Дата | |
Msg-id | CANu8FixqiMCwF6s7Qp8_3KwJEhS3yz+6QLtfXRR=jTPRfbNcqQ@mail.gmail.com обсуждение исходный текст |
Ответ на | [GENERAL] duplicate key value violates unique constraint and duplicated records (Timokhin Maxim <ncx2@yandex.com>) |
Ответы |
Re: [GENERAL] duplicate key value violates unique constraint and duplicated records
Re: [GENERAL] duplicate key value violates unique constraint and duplicated records |
Список | pgsql-general |
On Thu, Jun 29, 2017 at 5:28 AM, Timokhin Maxim <ncx2@yandex.com> wrote:
Hello.We are in process moving to new db from 9.4.8 -> 9.6.3.1. When we did it our application started to throw exception "duplicate key value violates unique constraint" during doing INSERT:INSERT INTO items (ctime, mtime, pubdate, url, title, description, body, status, fulltext_status, orig_id, image_id, video_id, resource_id, priority, checksum) VALUES (%(ctime)s, %(mtime)s, %(pubdate)s, %(url)s, %(title)s, %(description)s, %(body)s, %(status)s, %(fulltext_status)s, %(orig_id)s, %(image_id)s, %(video_id)s, %(resource_id)s, %(priority)s, %(checksum)s) RETURNING items.id'Column url has unique constraint.Also, we saw that during to update value into column status:(psycopg2.IntegrityError) duplicate key value violates unique constraint "items_url"DETAIL: Key (url)=(http://www.domainname.ru/ap_module/content/article/ 400-professional/140- professional/11880) already exists. [SQL: 'UPDATE items SET status=%(status)s WHERE items.id IN ( ... )...Our table:Column | Type | Modifiers-----------------+-----------------------------+------------ ------------------------------ ------------------------- id | integer | not null default nextval(('public.items_id_seq'::text)::regclass) ctime | timestamp without time zone | not null default now()pubdate | timestamp without time zone | not null default now()resource_id | integer | not null default 0url | text |title | text |description | text |body | text |status | smallint | not null default 0image | text |orig_id | integer | not null default 0mtime | timestamp without time zone | not null default now()checksum | text |video_url | text |audio_url | text |content_type | smallint | default 0author | text |video | text |fulltext_status | smallint | default 0summary | text |image_id | integer |video_id | integer |priority | smallint |Indexes:"items_pkey" PRIMARY KEY, btree (id)"items_url" UNIQUE, btree (url)"items_resource_id" btree (resource_id)"ndx__items__ctime" btree (ctime)"ndx__items__image" btree (image_id)"ndx__items__mtime" btree (mtime)"ndx__items__pubdate" btree (pubdate)"ndx__items__video" btree (video_id)Foreign-key constraints:"items_fkey1" FOREIGN KEY (image_id) REFERENCES images(id) ON UPDATE CASCADE ON DELETE SET NULL"items_fkey2" FOREIGN KEY (video_id) REFERENCES videos(id) ON UPDATE CASCADE ON DELETE SET NULLReferenced by:TABLE "cluster_image" CONSTRAINT "cluster_image_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADETABLE "cluster_meta" CONSTRAINT "cluster_meta_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADETABLE "events" CONSTRAINT "events_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADETABLE "item_cluster" CONSTRAINT "item_cluster_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADETABLE "items_blogs" CONSTRAINT "items_blogs_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADETABLE "items_reflink" CONSTRAINT "items_reflink_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADETABLE "items_related" CONSTRAINT "items_related_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADETABLE "items_summaries" CONSTRAINT "items_summaries_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADETABLE "quotations" CONSTRAINT "quotations_fkey3" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADETABLE "summaries" CONSTRAINT "summaries_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADEEverything would be not bad if in the table weren't appeared duplicated records in url column.Any idea how is it possible?Thank you!--
Timokhin 'maf' Maxim
It's possible you have index corruption on 9.4.8 version that was not detected.
Try the following query on 9.4.8 to see if any rows are selected. Then you can decide
how to fix from there.
SELECT a.id, a.url,
b.id, b.url
FROM items a,
items b
WHERE a.id <> b.id
AND a.url = b.url
ORDER by a.id;
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
В списке pgsql-general по дате отправления:
Предыдущее
От: "David G. Johnston"Дата:
Сообщение: Re: [GENERAL] INSERT INTO: string with apostrophe
Следующее
От: Adrian KlaverДата:
Сообщение: Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting