From: | Steven Chang <stevenchang1213(at)gmail(dot)com> |
---|---|
To: | Timokhin Maxim <ncx2(at)yandex(dot)com> |
Cc: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: duplicate key value violates unique constraint and duplicated records |
Date: | 2017-06-30 01:07:17 |
Message-ID: | CAEJt7k0JdXLYSHPiQ+6Fzs14psvxoeE8c-Dc2UvmJWfmy_u=aA@mail.gmail.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Interesting!! We also met the same situation on PK running on PPAS 9.0 last
night.
When surfing Internet, got returned this URL :
https://www.postgresql.org/message-id/20140811083748.2536.10437%40wrigleys.
postgresql.org
<https://www.postgresql.org/message-id/20140811083748.2536.10437%40wrigleys.postgresql.org>
You can check the reply.
2017-06-29 17:28 GMT+08:00 Timokhin Maxim <ncx2(at)yandex(dot)com>:
> 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
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Ken Tanzer | 2017-06-30 03:33:31 | Re: 9.6 parameters messing up my 9.2 pg_dump/pg_restore |
Previous Message | Melvin Davidson | 2017-06-29 23:59:21 | Re: postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting |