an error "duplicate key value violates unique constraint ..." happend when update table

From: 张明富 <mingfu(dot)zhang(at)xtaotech(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: an error "duplicate key value violates unique constraint ..." happend when update table
Date: 2017-07-12 10:56:54
Message-ID: AIsAUQAjAVxO5jy-mHeLYKr2.1.1499857014649.Hmail.mingfu.zhang@xtaotech.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi,
I write a program to scan folders and files in a filesystem volume periodicity to insert and update them to postgresql database. I create a table named r_coll_main to keep folders and r_data_main to keep files. The define is underline:
CREATE TABLE r_coll_main
(
coll_id uuid NOT NULL,
coll_path character varying(4096) NOT NULL,
mode oid,
uid oid,
gid oid,
create_ts oid default 0,
modify_ts oid default 0,
CONSTRAINT r_coll_main_pkey PRIMARY KEY (coll_id)
)
WITH (OIDS=FALSE, autovacuum_enabled=off, parallel_workers=8);
CREATE TABLE r_data_main
(
data_id uuid NOT NULL,
data_path character varying(4096) NOT NULL,
size bigint,
mode oid,
uid oid,
gid oid,
create_ts oid default 0,
modify_ts oid default 0,
CONSTRAINT r_data_main_pkey PRIMARY KEY (data_id)
)
WITH (OIDS=FALSE, autovacuum_enabled=off, parallel_workers=8);

I call odbc api (SQLExecDirect) to execute each sql statement. My problem is :
odbc: update R_DATA_MAIN set size=102400, mode=33261, uid=0, gid=0,modify_ts=1499702133 where data_id='af073dfb09354ccfb709a49029d1d5bd' [SQLSTATE: 08S01] [SQLCODE: 26] [SQLError message: ERROR: duplicate key value violates unique constraint "r_coll_main_pkey"
It seems that an update sql statement executed failed because of "duplicate key..." . This is stange!!! Annother issure is the update sql is for the r_data_main table but constraint by "r_coll_main_pkey" 。
Has anyone ever had this problem?

Browse pgsql-bugs by date

  From Date Subject
Next Message K S, Sandhya (Nokia - IN/Bangalore) 2017-07-12 11:20:58 Re: [HACKERS] Postgres process invoking exit resulting in sh-QUIT core
Previous Message David G. Johnston 2017-07-11 22:02:59 Re: Deferrable constraint execution not respecting "initially immediate"?