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?