From: | Timokhin Maxim <ncx2(at)yandex(dot)com> |
---|---|
To: | Melvin Davidson <melvin6925(at)gmail(dot)com>, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, stevenchang1213(at)gmail(dot)com |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: duplicate key value violates unique constraint and duplicated records |
Date: | 2017-06-30 11:58:32 |
Message-ID: | 2597491498823912@web6g.yandex.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
BTW, we are moving using:
pg_basebackup -h servername -R -P -D /data/upgrade/94 -U pgsql -v —xlog-method=stream —checkpoint=fast
After that we are upping version to 9.6.3.
I've looked through the documentation https://postgrespro.ru/docs/postgrespro/9.6/app-pgbasebackup and didn't find details about how pg_basebackup works with b-tree indexes.
Is it possible that pg_basebackup just copies indexes as is and that is cause of corruption. Or it pass indexes as instruction that says "after upping db make indexes" ?
Thank you.
--
Timokhin 'maf' Maxim
30.06.2017, 00:22, "Melvin Davidson" <melvin6925(at)gmail(dot)com>:
> On Thu, Jun 29, 2017 at 5:28 AM, Timokhin Maxim <ncx2(at)yandex(dot)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 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Mikhail | 2017-06-30 12:35:25 | Re: [GENERAL] Significant discrepancy in index cost estimation |
Previous Message | Timokhin Maxim | 2017-06-30 09:50:45 | Re: duplicate key value violates unique constraint and duplicated records |