GitHub user liang8283 added a comment to the discussion: How does cbcopy handle
a duplicate value conflict when the DISTRIBUTED BY column is also the PRIMARY
KEY?
Hi @asheraz048i2c, I looked into this in the cbcopy source and tested it on a
test cluster.
### Short answer
cbcopy does no pre-check, and it has no skip/log option for constraint
violations. In a normal full copy, the duplicate rows are loaded and the
primary key is silently not created.
### How cbcopy loads data:
- Each table is loaded with a plain COPY ... FROM PROGRAM 'cbcopy_helper ...'
[ON SEGMENT] CSV. It has no LOG ERRORS / SEGMENT REJECT LIMIT and no ON
CONFLICT. Single-row error handling only covers malformed input anyway, not
unique violations.
- Each table is loaded in its own transaction on the target: BEGIN → (TRUNCATE
if --truncate) → COPY → COMMIT. Any error rolls back the whole transaction. So
a table is all-or-nothing; conflicting rows are never skipped.
- Side note: a PRIMARY KEY can't be NOT VALID (ERROR: PRIMARY KEY constraints
cannot be marked NOT VALID).
### What happens in a full copy (metadata + data):
During the metadata phase, cbcopy runs DDL and data copy concurrently: each
table is queued for data copy as soon as its CREATE TABLE runs. The ALTER TABLE
... ADD CONSTRAINT ... PRIMARY KEY statements run near the end of the pre-data
restore. A failed metadata statement is logged as an error.
I reproduced this with a table DISTRIBUTED BY (id) + PRIMARY KEY (id),
2,000,010 rows / 2,000,000 distinct ids:
ERROR: ... ALTER TABLE ONLY public.t_dup ADD CONSTRAINT t_dup_pkey PRIMARY KEY
(id);
Error was: ERROR: could not create unique index "t_dup_pkey" (SQLSTATE
23505)
ERROR: Encountered 2 errors during metadata restore; see log file ...
INFO: Database dupsrc: successfully copied 3 tables, skipped 1 tables, failed
0 tables
The result on the target:
- All 2,000,010 rows, duplicates included, were loaded.
- The table had no primary key.
- The table was listed in the succeed file, and the failed file was empty.
- The only signs were the exit code (1) and the metadata error in the log.
GitHub link:
https://github.com/apache/cloudberry/discussions/2050#discussioncomment-18635978
----
This is an automatically sent email for [email protected].
To unsubscribe, please send an email to: [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
