Re: [GENERAL] duplicate key value violates unique constraint and duplicated records - Mailing list pgsql-general

From Timokhin Maxim
Subject Re: [GENERAL] duplicate key value violates unique constraint and duplicated records
Date
Msg-id 1905301498816196@web7o.yandex.ru
Whole thread Raw
In response to Re: [GENERAL] duplicate key value violates unique constraint andduplicated records  (Melvin Davidson <melvin6925@gmail.com>)
List pgsql-general
Hello! Yes, it looks like a bug or an index corruption. Now, I'm going to drop an index, find and fix duplicates, and create index again.
But I would do it on 9.6.3 because there is a great feature ''max_parallel_workers_per_gather" there.
Well, see what will happen.
 
-- 
Пожалуйста!
Используйте кнопку "ответить всем".
Не удаляйте историю переписки.
Спасибо. С уважением, Timokhin 'maf' Maxim
 
 
 
30.06.2017, 00:22, "Melvin Davidson" <melvin6925@gmail.com>:
 
 
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"
     [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
 

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.

pgsql-general by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: [GENERAL] 9.6 parameters messing up my 9.2 pg_dump/pg_restore
Next
From: Timokhin Maxim
Date:
Subject: Re: [GENERAL] duplicate key value violates unique constraint and duplicated records