<div>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.</div><div>But I would do it on 9.6.3 because there is a great feature ''max_parallel_workers_per_gather" there.</div><div>Well, see what will happen.</div><div> </div><div>-- <br />Пожалуйста!</div><div>Используйте кнопку "ответить всем".</div><div>Не удаляйте историю переписки.</div><div>Спасибо. С уважением, Timokhin 'maf' Maxim</div><div> </div><div> </div><div> </div><div>30.06.2017, 00:22, "Melvin Davidson" <melvin6925(at)gmail(dot)com>:</div><blockquote type="cite"><div> <div> <div>On Thu, Jun 29, 2017 at 5:28 AM, Timokhin Maxim <span><<a target="_blank" href="mailto:ncx2(at)yandex(dot)com">ncx2(at)yandex(dot)com</a>></span> wrote:<blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;"><div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);">Hello.</div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);">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:</div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);"> </div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);">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 <a target="_blank" href="http://items.id/">items.id</a>'</div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);"> </div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);">Column url has unique constraint.</div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);"> </div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);"> </div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);">Also, we saw that during to update value into column status:</div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);"><div>(psycopg2.IntegrityError) duplicate key value violates unique constraint "items_url"</div><div> DETAIL: Key (url)=(<a target="_blank" style="color:rgb(153,0,153);" href="http://www.domainname.ru/ap_module/content/article/400-professional/140-professional/11880">http://www.domainname.ru/ap_module/content/article/400-professional/140-professional/11880</a>) already exists.</div><div> [SQL: 'UPDATE items SET status=%(status)s WHERE <a target="_blank" href="http://items.id/">items.id</a> IN ( ... )...</div><div> </div><div> </div><div>Our table:</div><div> </div></div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);"> </div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);"><div> Column | Type | Modifiers</div><div>-----------------+-----------------------------+-------------------------------------------------------------------</div><div> id | integer | not null default nextval(('public.items_id_seq'::text)::regclass)</div><div> ctime | timestamp without time zone | not null default now()</div><div> pubdate | timestamp without time zone | not null default now()</div><div> resource_id | integer | not null default 0</div><div> url | text |</div><div> title | text |</div><div> description | text |</div><div> body | text |</div><div> status | smallint | not null default 0</div><div> image | text |</div><div> orig_id | integer | not null default 0</div><div> mtime | timestamp without time zone | not null default now()</div><div> checksum | text |</div><div> video_url | text |</div><div> audio_url | text |</div><div> content_type | smallint | default 0</div><div> author | text |</div><div> video | text |</div><div> fulltext_status | smallint | default 0</div><div> summary | text |</div><div> image_id | integer |</div><div> video_id | integer |</div><div> priority | smallint |</div><div>Indexes:</div><div> "items_pkey" PRIMARY KEY, btree (id)</div><div> "items_url" UNIQUE, btree (url)</div><div> "items_resource_id" btree (resource_id)</div><div> "ndx__items__ctime" btree (ctime)</div><div> "ndx__items__image" btree (image_id)</div><div> "ndx__items__mtime" btree (mtime)</div><div> "ndx__items__pubdate" btree (pubdate)</div><div> "ndx__items__video" btree (video_id)</div><div>Foreign-key constraints:</div><div> "items_fkey1" FOREIGN KEY (image_id) REFERENCES images(id) ON UPDATE CASCADE ON DELETE SET NULL</div><div> "items_fkey2" FOREIGN KEY (video_id) REFERENCES videos(id) ON UPDATE CASCADE ON DELETE SET NULL</div><div>Referenced by:</div><div> TABLE "cluster_image" CONSTRAINT "cluster_image_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE</div><div> TABLE "cluster_meta" CONSTRAINT "cluster_meta_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE</div><div> TABLE "events" CONSTRAINT "events_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE</div><div> TABLE "item_cluster" CONSTRAINT "item_cluster_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE</div><div> TABLE "items_blogs" CONSTRAINT "items_blogs_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE</div><div> TABLE "items_reflink" CONSTRAINT "items_reflink_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE</div><div> TABLE "items_related" CONSTRAINT "items_related_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE</div><div> TABLE "items_summaries" CONSTRAINT "items_summaries_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE</div><div> TABLE "quotations" CONSTRAINT "quotations_fkey3" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE</div><div> TABLE "summaries" CONSTRAINT "summaries_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE</div></div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);"><div> </div><div> </div><div>Everything would be not bad if in the table weren't appeared duplicated records in url column.</div><div>Any idea how is it possible? </div><div> </div><div>Thank you!</div></div></div><div><span><font color="#888888"> </font></span></div><div><span><font color="#888888">-- <br />Timokhin 'maf' Maxim</font></span></div><div><span><font color="#888888"> </font></span></div></blockquote></div><br /><strong>It's possible you have index corruption on 9.4.8 version that was not detected.<br /><br />Try the following query on 9.4.8 to see if any rows are selected. Then you can decide<br />how to fix from there.<br /><br />SELECT <a href="http://a.id/">a.id</a>, a.url,<br /> <a href="http://b.id/">b.id</a>, b.url<br /> FROM items a,<br /> items b<br /> WHERE <a href="http://a.id/">a.id</a> <> <a href="http://b.id/">b.id</a><br /> AND a.url = b.url<br /> ORDER by <a href="http://a.id/">a.id</a>;</strong><br /><br />--<div><div><font size="4"><strong><span style="font-family:courier new,monospace;">Melvin Davidson</span></strong></font><br /><font size="3" style="font-weight:bold;"><span style="color:#8000ff;">I reserve the right to fantasize. Whether or not you </span><br style="color:rgb(128,0,255);" /><span style="color:#8000ff;">wish to share my fantasy is entirely up to you. </span><img style="color:rgb(128,0,255);" src="http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif" /></font></div></div></div></div></blockquote>
