[GENERAL] duplicate key value violates unique constraint and duplicated records

Поиск
Список
Период
Сортировка
От Timokhin Maxim
Тема [GENERAL] duplicate key value violates unique constraint and duplicated records
Дата
Msg-id 2150831498728539@web17j.yandex.ru
обсуждение исходный текст
Ответы Re: [GENERAL] duplicate key value violates unique constraint andduplicated records
Re: [GENERAL] duplicate key value violates unique constraint andduplicated records
Re: [GENERAL] duplicate key value violates unique constraint andduplicated records
Список pgsql-general
Дерево обсуждения
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"
     [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 0
 url             | text                        |
 title           | text                        |
 description     | text                        |
 body            | text                        |
 status          | smallint                    | not null default 0
 image           | text                        |
 orig_id         | integer                     | not null default 0
 mtime           | timestamp without time zone | not null default now()
 checksum        | text                        |
 video_url       | text                        |
 audio_url       | text                        |
 content_type    | smallint                    | default 0
 author          | text                        |
 video           | text                        |
 fulltext_status | smallint                    | default 0
 summary         | 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 NULL
Referenced by:
    TABLE "cluster_image" CONSTRAINT "cluster_image_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "cluster_meta" CONSTRAINT "cluster_meta_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "events" CONSTRAINT "events_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "item_cluster" CONSTRAINT "item_cluster_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "items_blogs" CONSTRAINT "items_blogs_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "items_reflink" CONSTRAINT "items_reflink_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "items_related" CONSTRAINT "items_related_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "items_summaries" CONSTRAINT "items_summaries_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "quotations" CONSTRAINT "quotations_fkey3" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "summaries" CONSTRAINT "summaries_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
 
 
Everything 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
 

В списке pgsql-general по дате отправления:

Предыдущее
От: Ken Tanzer
Дата:
Сообщение: Re: [GENERAL] 9.6 parameters messing up my 9.2 pg_dump/pg_restore
Следующее
От: Rob Nikander
Дата:
Сообщение: Re: [GENERAL] Is the row version available in SQL?