Re: COPY ON_CONFLICT TABLE; save duplicated record to another table.

От:

Jim Jones <jim.jones@uni-muenster.de>

Дата:

5 мая в 13:06:44

Hi Jian

On 25/04/2026 06:12, jian he wrote:
> Comments are welcome!

Thanks for the patch!

A few comments:

== double defGet in function name ==

defGetdefGetCopyOnConflictChoice should probably be 
defGetCopyOnConflictChoice

== identical error message in ProcessCopyOptions() ==

The same error message is raised for conflict_tbl_specified and 
!conflict_tbl_specified

ereport(ERROR,
   errcode(ERRCODE_INVALID_PARAMETER_VALUE),
   errmsg("COPY %s requires %s option", "CONFLICT_TABLE", "ON_CONFLICT"));

== unused enum in CopyOnErrorChoice ==

The enum COPY_ON_ERROR_TABLE is introduced, but is never used anywhere.

== unconditional ExecOpenIndices() ==

ExecOpenIndices(resultRelInfo, true);

I'm not very familiar with this part of the code, but it looks like that 
this change would affect other COPY FROM operations. If I'm mistaken, a 
comment would add some value here. Or perhaps something like:

ExecOpenIndices(resultRelInfo, cstate->opts.on_conflict != ONCONFLICT_NONE);

== ON_CONFLICT TABLE not rejected in COPY TO ==

CONFLICT_TABLE is silently ignored, even if the table does not exist:

postgres=# COPY t TO '/dev/null' (ON_CONFLICT TABLE, CONFLICT_TABLE 
table_does_not_exist);
COPY 1

I guess adding a is_from to defGetdefGetCopyOnConflictChoice() is the 
way to go.

== redundant condition in CopyFrom() ==

The second condition seems unnecessary, as the previous if already tests 
for cstate->opts.on_conflict == ONCONFLICT_NONE:

else if (resultRelInfo->ri_NumIndices > 0 &&
   cstate->opts.on_conflict != ONCONFLICT_NONE)

== typos ==
regular realtion > regular relation
vertification > verification
resouces > resources
unqiue > unique

== unnecessary pnstrdup (?) ==

newvalues[i] = CStringGetTextDatum(pnstrdup(cstate->line_buf.data,
                                             cstate->line_buf.len));

Is the duplication really necessary? Wouldn't it suffice to use 
cstring_to_text_with_len() instead? Something like:

newvalues[i] = 
PointerGetDatum(cstring_to_text_with_len(cstate->line_buf.data, 
cstate->line_buf.len));

or even

newvalues[i] = CStringGetTextDatum(cstate->line_buf.data)

I'll check the documentation after we get more feedback on the syntax.

Thanks!
Best, Jim

Re: COPY ON_CONFLICT TABLE; save duplicated record to another table.

От:

jian he <jian.universality@gmail.com>

Дата:

27 мая в 17:06:27

On Fri, May 15, 2026 at 7:56 PM Zsolt Parragi  wrote:
>
> I also noticed the early relation close mentioned by Jim, which can
> crash the patch.
>
fixed.

> +      This uses the same mechanism as  linkend="sql-on-conflict">INSERT ... ON
> CONFLICT.
> +      However, exclusion constraints are not supported; only
> NOT DEFERRABLE
> +      unique constraints are checked for violations.
>
> EXCLUDE USING gist (... WITH =, ... WITH &&) seems to work fine?
> Except that the message mentions unique constraint violation.
>

I double-checked ExecCheckIndexConstraints, ExecInsertIndexTuples and
added some dummy regression tests to confirm
that INSERT ON CONFLICT DO NOTHING works fine with exclusion constraints.

> I also checked the same trigger behaviors as in the other thread[1],
> especially before triggers on the conflict table, and this patch
> behaves similarly, it silently drops rows.
> I think this could also use some more visibility/documentation about that.
>
> 1: https://www.postgresql.org/message-id/CAN4CZFPoohFvQTSE0wC%2BwcrfYiZOxFmUdOq0%2B9TCVR6Hk8n6iw%40mail.gmail.com
>
With the attached v4, row-level and statement-level triggers are now
fired for every insertion to conflict_table

In v3, there was a performance regression when a table don't have any unique or
exclusion constraint, but ON_CONFLICT was still specified as 'TABLE'.
I have attached an SQL test script demonstrating this.
With v4, this regression is now very very minimal for COPY operations where
ON_CONFLICT is set to 'TABLE' on a target table without any unique or exclusion
constraints.

I also polished the documentation.

Comments from Jim Jones also addressed.

--
jian
https://www.enterprisedb.com/

COPY ON_CONFLICT TABLE; save duplicated record to another table.

От:

jian he <jian.universality@gmail.com>

Дата:

25 апреля в 07:12:50

Hi,
This is for v20.

Reference: https://web.archive.org/web/20240328094030/https://riggs.business/blog/f/postgresql-todo-2023
COPY enhancement:
Detect duplicate rows and redirect them to a separate table without
aborting the load.

While reviewing this TODO, I quickly noticed this idea closely aligns with
https://commitfest.postgresql.org/patch/4817.

Both ideas share common elements: allowing a user-specified table,
validating its metadata, and storing rows in it.
Based on that, I spent some time working on the implementation.

Proposed syntax:

COPY FROM (ON_CONFLICT TABLE, CONFLICT_TABLE conflict_tbl);

The CONFLICT_TABLE requires exactly four columns: COPY target table, COPY
filename, the line number of the duplicate, and the duplicate record itself.

This structure is fixed, a pre-defined data type is unnecessary.  Validation is
based solely on the column data types (pg_attribute.atttypid) rather than their
names (pg_attribute.attname). The expected types are OID, TEXT, INT8, and TEXT,
respectively.

This uses INSERT ON CONFLICT infrastructure under the hood.

Demo:

CREATE TABLE t_copy_tbl(a int, b int, c text);
CREATE TABLE err_tbl1(copy_tbl oid, filename text, lineno bigint, line text);
CREATE UNIQUE INDEX ON t_copy_tbl (c);
COPY t_copy_tbl(b,a, c) FROM STDIN (DELIMITER ',', ON_CONFLICT TABLE,
CONFLICT_TABLE err_tbl1, log_verbosity verbose);
4,17,aaaaaa
6,11,aaaaaa
11,1,xxxxxxxx
12,1,xxxxxxxx
13,1,xxxxxxxx
\.

table err_tbl1 ;
 copy_tbl | filename | lineno |     line
----------+----------+--------+---------------
    18231 | STDIN    |      2 | 6,11,aaaaaa
    18231 | STDIN    |      4 | 12,1,xxxxxxxx
    18231 | STDIN    |      5 | 13,1,xxxxxxxx
(3 rows)

(I need to double-check the exclusion unique constraint)

Comments are welcome!



--
jian
https://www.enterprisedb.com/

Re: COPY ON_CONFLICT TABLE; save duplicated record to another table.

От:

Zsolt Parragi <zsolt.parragi@percona.com>

Дата:

7 мая в 01:17:35

Hello!

I tried the patch and found a few issues.

1. Two of them are null pointer dereference crashes, one with
partitioned tables:

CREATE TABLE part_t (a int PRIMARY KEY, b text) PARTITION BY RANGE (a);
CREATE TABLE part_t_p1 PARTITION OF part_t FOR VALUES FROM (0) TO (1000);
CREATE TABLE conflict_log (
    rel        oid,
    file_name  text,
    line_no    bigint,
    raw_line   text
);

INSERT INTO part_t VALUES (1, 'pre-existing');
COPY part_t (a, b) FROM stdin WITH (on_conflict 'table',
conflict_table 'conflict_log');
2 row-two
1 dup
3 row-three
\.


2. And another with repeateable reads:

CREATE TABLE t_rr (a int PRIMARY KEY, b text);
CREATE TABLE conflict_log (rel oid, fname text, ln bigint, raw text);
INSERT INTO t_rr VALUES (1, 'pre-committed');

BEGIN ISOLATION LEVEL REPEATABLE READ;
COPY t_rr FROM stdin WITH (on_conflict 'table', conflict_table 'conflict_log');
1 dup-row
\.


3. There's also a possible data loss scenario, reports 3 copied 0 actual:

CREATE TABLE conf_log (
    relname     oid,
    fname       text,
    lineno      bigint,
    rawline     text
);

CREATE TABLE no_idx_tgt (id int, payload text);

CREATE FUNCTION noop_trig() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    RETURN NEW;
END;
$$;

CREATE TRIGGER noop_before BEFORE INSERT ON no_idx_tgt
    FOR EACH ROW EXECUTE FUNCTION noop_trig();

COPY no_idx_tgt (id, payload) FROM STDIN
    WITH (ON_CONFLICT TABLE, CONFLICT_TABLE conf_log);
1 alpha
2 beta
3 gamma
\.

SELECT 'A: no_idx_tgt count' AS scenario, count(*) AS rows FROM no_idx_tgt;
SELECT 'A: conf_log count'  AS scenario, count(*) AS rows FROM conf_log;
SELECT * FROM no_idx_tgt ORDER BY id;


4. Shouldn't the following error out?

CREATE TABLE t (a int PRIMARY KEY, b text);
COPY t TO '/dev/null' (ON_CONFLICT TABLE, CONFLICT_TABLE no_such_table);