v4-0002-COPY-ON_CONFLICT-TABLE.patch
text/x-patch
Filename: v4-0002-COPY-ON_CONFLICT-TABLE.patch
Type: text/x-patch
Part: 2
From 187975f7644e7bdd07bff2f5f4b834241b0e537b Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Wed, 27 May 2026 21:32:58 +0800
Subject: [PATCH v4 2/2] COPY ON_CONFLICT TABLE
reference: https://web.archive.org/web/20240328094030/https://riggs.business/blog/f/postgresql-todo-2023
See infer_arbiter_indexes, comments:
/*
* Quickly return NIL for ON CONFLICT DO NOTHING without an inference
* specification or named constraint. ON CONFLICT DO SELECT/UPDATE
* statements must always provide one or the other (but parser ought to
* have caught that already).
*/
discussion: https://postgr.es/m/CACJufxG672yotDt87Dbazf1C9scnZm7QSB+zu6vHc+j5QrjXvA@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/6736
---
doc/src/sgml/monitoring.sgml | 7 +-
doc/src/sgml/ref/copy.sgml | 87 +++
src/backend/commands/copy.c | 68 +++
src/backend/commands/copyfrom.c | 551 +++++++++++++++++-
src/backend/commands/explain.c | 3 +-
src/backend/executor/nodeModifyTable.c | 18 +-
src/backend/parser/gram.y | 1 +
src/include/commands/copy.h | 4 +
src/include/commands/copyfrom_internal.h | 11 +
src/include/executor/nodeModifyTable.h | 3 +-
src/include/nodes/nodes.h | 1 +
src/test/regress/expected/copy.out | 8 +
src/test/regress/expected/copy2.out | 217 +++++++
src/test/regress/expected/insert_conflict.out | 21 +
src/test/regress/expected/rangetypes.out | 12 +-
src/test/regress/sql/copy.sql | 11 +
src/test/regress/sql/copy2.sql | 164 ++++++
src/test/regress/sql/insert_conflict.sql | 31 +
src/test/regress/sql/rangetypes.sql | 15 +-
19 files changed, 1214 insertions(+), 19 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 08d5b824552..73c597ddcc2 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -6744,10 +6744,9 @@ FROM pg_stat_get_backend_idset() AS backendid;
<structfield>tuples_skipped</structfield> <type>bigint</type>
</para>
<para>
- Number of tuples skipped because they contain malformed data.
- This counter only advances when
- <literal>ignore</literal> is specified to the <literal>ON_ERROR</literal>
- option.
+ Number of tuples skipped because they contain malformed data or constraint violations (unique or exclusion).
+ This counter advances when <literal>ignore</literal> is specified to the <literal>ON_ERROR</literal>
+ option or <literal>table</literal> is specified to the <literal>ON_CONFLICT</literal> option.
</para></entry>
</row>
</tbody>
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 4706c9a4410..d772f81b384 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -44,6 +44,8 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
FORCE_QUOTE { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
FORCE_NOT_NULL { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
FORCE_NULL { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
+ ON_CONFLICT <replaceable class="parameter">conflict_action</replaceable>
+ CONFLICT_TABLE <replaceable class="parameter">conflict_table</replaceable>
ON_ERROR <replaceable class="parameter">error_action</replaceable>
REJECT_LIMIT <replaceable class="parameter">maxerror</replaceable>
ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
@@ -440,6 +442,89 @@ COPY (SELECT j FROM (VALUES ('null'::json), (NULL::json)) v(j))
</listitem>
</varlistentry>
+ <varlistentry id="sql-copy-params-on-conflict">
+ <term><literal>ON_CONFLICT</literal></term>
+ <listitem>
+ <para>
+ Specifies the behavior when a row violates a unique or exclusion constraint.
+ If <replaceable class="parameter">conflict_action</replaceable> is set to
+ <literal>stop</literal> (the default), the command will fail. If it is set to
+ <literal>table</literal>, the conflicting row's information is inserted to
+ <replaceable class="parameter">conflict_table</replaceable> specified by
+ <literal>CONFLICT_TABLE</literal>, and continue with the next one.
+ Under the hood, this uses the same mechanism as <link linkend="sql-on-conflict"><command>INSERT ... ON CONFLICT</command></link>.
+ However it does not support <literal>NOT DEFERRABLE</literal> unique or exclusion constraints.
+ </para>
+
+ <para>
+ The <literal>table</literal> option is applicable only for
+ <command>COPY FROM</command> when the <literal>FORMAT</literal>
+ is <literal>text</literal> or <literal>csv</literal>.
+ If <literal>ON_CONFLICT</literal> is set to <literal>table</literal>, a
+ <literal>NOTICE</literal> message is emitted at the end of the command
+ reporting the number of rows that were inserted into table <replaceable class="parameter">conflict_table</replaceable>
+ due to unique or exclusion constraint violation, provided that at least one row was affected.
+ When the <literal>LOG_VERBOSITY</literal> option is set to
+ <literal>verbose</literal>, a <literal>NOTICE</literal> message is emitted
+ for each row inserted by <literal>ON_CONFLICT</literal>, containing the
+ input line that violates unique or exclusion constraint. When the <literal>LOG_VERBOSITY</literal> option set to
+ <literal>silent</literal>, no messages are emitted regarding discarded rows.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="sql-copy-params-conflict-table">
+ <term><literal>CONFLICT_TABLE</literal></term>
+ <listitem>
+ <para>
+ Specifies a destination table (<replaceable class="parameter">conflict_table</replaceable>)
+ to capture unique and exclusion constraint violations encountered while running
+ <command>COPY FROM</command> operation.
+ The destination table <replaceable class="parameter">conflict_table</replaceable> must define
+ exactly four columns, though the specific column names are not restricted.
+ The required column order and data types are:
+ <informaltable>
+ <tgroup cols="2">
+ <thead>
+ <row>
+ <entry>Data Type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><type>oid</type></entry>
+ <entry>
+ The OID of the target table for the <command>COPY FROM</command> command.
+ This corresponds to <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>oid</structfield>.
+ Note that no formal dependency is maintained; if the referenced table is dropped,
+ this value will persist as a stale reference.
+ </entry>
+ </row>
+ <row>
+ <entry><type>text</type></entry>
+ <entry>The file path of the <command>COPY FROM</command> input</entry>
+ </row>
+ <row>
+ <entry><type>bigint</type></entry>
+ <entry>
+ The line number within the input source where the unique or exclusion
+ constraint violation occurred (starting at 1)
+ </entry>
+ </row>
+ <row>
+ <entry><type>text</type></entry>
+ <entry>The raw line text content of the record that caused the violation</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </informaltable>
+ </para>
+
+ </listitem>
+ </varlistentry>
+
+
<varlistentry id="sql-copy-params-on-error">
<term><literal>ON_ERROR</literal></term>
<listitem>
@@ -493,6 +578,8 @@ COPY (SELECT j FROM (VALUES ('null'::json), (NULL::json)) v(j))
If not specified, <literal>ON_ERROR</literal>=<literal>ignore</literal>
allows an unlimited number of errors, meaning <command>COPY</command> will
skip all erroneous data.
+ Note: Rows skipped due to unique or exclusion constraint violations handled by the
+ <literal>ON_CONFLICT</literal> option do not count toward this error limit.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 003b70852bb..6ae8e64ab0e 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -561,6 +561,36 @@ defGetCopyLogVerbosityChoice(DefElem *def, ParseState *pstate)
return COPY_LOG_VERBOSITY_DEFAULT; /* keep compiler quiet */
}
+/*
+ * Extract an OnConflictAction value from a DefElem.
+ */
+static OnConflictAction
+defGetCopyOnConflictChoice(DefElem *def, ParseState *pstate, bool is_from)
+{
+ char *sval;
+
+ sval = defGetString(def);
+
+ if (!is_from)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("COPY %s cannot be used with %s", "ON_CONFLICT", "COPY TO"),
+ parser_errposition(pstate, def->location));
+
+ if (pg_strcasecmp(sval, "stop") == 0)
+ return ONCONFLICT_NONE;
+ else if (pg_strcasecmp(sval, "table") == 0)
+ return ONCONFLICT_TABLE;
+
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ /*- translator: first %s is the name of a COPY option, e.g. ON_ERROR */
+ errmsg("COPY %s \"%s\" not recognized", "ON_CONFLICT", sval),
+ parser_errposition(pstate, def->location));
+
+ return ONCONFLICT_NONE; /* keep compiler quiet */
+}
+
/*
* Process the statement option list for COPY.
*
@@ -587,9 +617,11 @@ ProcessCopyOptions(ParseState *pstate,
bool freeze_specified = false;
bool header_specified = false;
bool on_error_specified = false;
+ bool conflict_rel_specified = false;
bool log_verbosity_specified = false;
bool reject_limit_specified = false;
bool force_array_specified = false;
+ bool on_conflict_specified = false;
ListCell *option;
/* Support external use for option sanity checking */
@@ -599,6 +631,7 @@ ProcessCopyOptions(ParseState *pstate,
opts_out->file_encoding = -1;
/* default format */
opts_out->format = COPY_FORMAT_TEXT;
+ opts_out->on_conflict = ONCONFLICT_NONE;
/* Extract options from the statement node tree */
foreach(option, options)
@@ -774,6 +807,21 @@ ProcessCopyOptions(ParseState *pstate,
reject_limit_specified = true;
opts_out->reject_limit = defGetCopyRejectLimitOption(defel);
}
+ else if (strcmp(defel->defname, "on_conflict") == 0)
+ {
+ if (on_conflict_specified)
+ errorConflictingDefElem(defel, pstate);
+ on_conflict_specified = true;
+ opts_out->on_conflict = defGetCopyOnConflictChoice(defel, pstate, is_from);
+ }
+ else if (strcmp(defel->defname, "conflict_table") == 0)
+ {
+ if (conflict_rel_specified)
+ errorConflictingDefElem(defel, pstate);
+ conflict_rel_specified = true;
+
+ opts_out->on_conflictRel = defGetString(defel);
+ }
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -782,6 +830,26 @@ ProcessCopyOptions(ParseState *pstate,
parser_errposition(pstate, defel->location)));
}
+ /* Check CONFLICT_TABLE and ON_CONFLICT option */
+ if (opts_out->on_conflict != ONCONFLICT_TABLE)
+ {
+ if (conflict_rel_specified)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("COPY %s requires %s option specified as TABLE", "CONFLICT_TABLE", "ON_CONFLICT"));
+ }
+ else
+ {
+ if (!conflict_rel_specified)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("COPY %s requires %s option", "ON_CONFLICT", "CONFLICT_TABLE"));
+ else if (opts_out->format == COPY_FORMAT_BINARY)
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("only ON_CONFLICT STOP is allowed in BINARY mode"));
+ }
+
/*
* Check for incompatible options (must do these three before inserting
* defaults)
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.c
index 0087585b2c4..48c5b302e21 100644
--- a/src/backend/commands/copyfrom.c
+++ b/src/backend/commands/copyfrom.c
@@ -42,16 +42,21 @@
#include "miscadmin.h"
#include "nodes/miscnodes.h"
#include "optimizer/optimizer.h"
+#include "parser/parse_relation.h"
#include "pgstat.h"
#include "rewrite/rewriteHandler.h"
#include "storage/fd.h"
#include "tcop/tcopprot.h"
+#include "utils/acl.h"
+#include "utils/builtins.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/portal.h"
+#include "utils/regproc.h"
#include "utils/rel.h"
#include "utils/snapmgr.h"
#include "utils/typcache.h"
+#include "utils/syscache.h"
/*
* No more than this many tuples per CopyMultiInsertBuffer
@@ -120,6 +125,11 @@ static void CopyFromBinaryInFunc(CopyFromState cstate, Oid atttypid,
FmgrInfo *finfo, Oid *typioparam);
static void CopyFromBinaryStart(CopyFromState cstate, TupleDesc tupDesc);
static void CopyFromBinaryEnd(CopyFromState cstate);
+static void CopyFromConflictTableCheck(CopyFromState cstate);
+static void RangeVarCallbackForCopyConflictTable(const RangeVar *rv, Oid relid, Oid oldrelid,
+ void *arg);
+static void CopyFromConflictTableInit(CopyFromState cstate);
+static void CopyConflictTablePermissionCheck(ParseState *pstate, Relation rel);
/*
@@ -774,6 +784,61 @@ CopyMultiInsertInfoStore(CopyMultiInsertInfo *miinfo, ResultRelInfo *rri,
miinfo->bufferedBytes += tuplen;
}
+/*
+ * Does this relation have a unique or exclusion constraint
+ *
+ * COPY (ON_CONFLICT table) uses ExecInsert to insert data, which is more
+ * expensive than table_tuple_insert. Therefore we should avoid
+ * ExecInsert and use table_tuple_insert or table_multi_insert if the
+ * target table does not have unique or exclusion constraints.
+ *
+ * For partitioned tables, we would need to check whether every individual
+ * partition has these constraints. This is not trivial, also some partitions
+ * may have these constraints while others do not. Therefore, for partitioned
+ * tables, we simply assume they have unique or exclusion constraints.
+ */
+static bool
+rel_has_unique_or_exclusion_constr(ResultRelInfo *resultRelInfo)
+{
+ int j = 0;
+ int numIndices;
+ RelationPtr relationDescs;
+ IndexInfo **indexInfoArray;
+ Relation heapRelation;
+
+ numIndices = resultRelInfo->ri_NumIndices;
+ relationDescs = resultRelInfo->ri_IndexRelationDescs;
+ indexInfoArray = resultRelInfo->ri_IndexRelationInfo;
+ heapRelation = resultRelInfo->ri_RelationDesc;
+
+ Assert(heapRelation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ||
+ heapRelation->rd_rel->relkind == RELKIND_RELATION);
+
+ if (heapRelation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ return true;
+
+ for (int i = 0; i < numIndices; i++)
+ {
+ Relation indexRelation = relationDescs[i];
+ IndexInfo *indexInfo;
+
+ if (indexRelation == NULL)
+ continue;
+
+ indexInfo = indexInfoArray[i];
+
+ if (!indexInfo->ii_Unique && !indexInfo->ii_ExclusionOps)
+ continue;
+
+ j++;
+ }
+
+ if (j > 0)
+ return true;
+ else
+ return false;
+}
+
/*
* Copy FROM file to relation.
*/
@@ -801,6 +866,17 @@ CopyFrom(CopyFromState cstate)
bool has_before_insert_row_trig;
bool has_instead_insert_row_trig;
bool leafpart_use_multi_insert = false;
+ ModifyTableContext mtcontext; /* Used only when ON_CONFLICT is specified */
+ TupleTableSlot *conflictslot = NULL;
+ bool insert_on_conflict = false;
+ ModifyTable *node = makeNode(ModifyTable);
+
+ node->operation = CMD_INSERT;
+ node->canSetTag = false;
+ node->rootRelation = 0;
+ node->resultRelations = list_make1_int(1);
+ node->onConflictAction = ONCONFLICT_NONE;
+ node->arbiterIndexes = NIL;
Assert(cstate->rel);
Assert(list_length(cstate->range_table) == 1);
@@ -910,6 +986,13 @@ CopyFrom(CopyFromState cstate)
ti_options |= TABLE_INSERT_FROZEN;
}
+ /*
+ * Copy other important information into the EState.
+ */
+ estate->es_output_cid = mycid;
+ estate->es_snapshot = RegisterSnapshot(GetActiveSnapshot());
+ estate->es_crosscheck_snapshot = InvalidSnapshot;
+
/*
* We need a ResultRelInfo so we can use the regular executor's
* index-entry-making machinery. (There used to be a huge amount of code
@@ -923,14 +1006,37 @@ CopyFrom(CopyFromState cstate)
/* Verify the named relation is a valid target for INSERT */
CheckValidResultRel(resultRelInfo, CMD_INSERT, ONCONFLICT_NONE, NIL);
- ExecOpenIndices(resultRelInfo, false);
+ ExecOpenIndices(resultRelInfo, cstate->opts.on_conflict != ONCONFLICT_NONE);
+
+ if (cstate->opts.on_conflict == ONCONFLICT_TABLE)
+ {
+ if (cstate->rel->rd_rel->relkind != RELKIND_RELATION &&
+ cstate->rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
+ ereport(ERROR,
+ errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot perform COPY ON_CONFLICT on relation \"%s\"", RelationGetRelationName(cstate->rel)),
+ errdetail_relkind_not_supported(cstate->rel->rd_rel->relkind));
+
+ node->onConflictAction = ONCONFLICT_NOTHING;
+
+ conflictslot = ExecInitExtraTupleSlot(estate,
+ RelationGetDescr(cstate->conflictRel),
+ &TTSOpsVirtual);
+
+ CopyFromConflictTableInit(cstate);
+
+ insert_on_conflict =
+ rel_has_unique_or_exclusion_constr(resultRelInfo);
+ }
+ else
+ cstate->mtcontext = NULL;
/*
* Set up a ModifyTableState so we can let FDW(s) init themselves for
* foreign-table result relation(s).
*/
mtstate = makeNode(ModifyTableState);
- mtstate->ps.plan = NULL;
+ mtstate->ps.plan = (Plan *) node;
mtstate->ps.state = estate;
mtstate->operation = CMD_INSERT;
mtstate->mt_nrels = 1;
@@ -982,6 +1088,8 @@ CopyFrom(CopyFromState cstate)
if (cstate->rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
proute = ExecSetupPartitionTupleRouting(estate, cstate->rel);
+ mtstate->mt_partition_tuple_routing = proute;
+
if (cstate->whereClause)
cstate->qualexpr = ExecInitQual(castNode(List, cstate->whereClause),
&mtstate->ps);
@@ -1052,6 +1160,19 @@ CopyFrom(CopyFromState cstate)
*/
insertMethod = CIM_SINGLE;
}
+ else if (cstate->opts.on_conflict == ONCONFLICT_TABLE && insert_on_conflict)
+ {
+ /*
+ * Cannot use multi-inserts when the ON_CONFLICT option is specified
+ * as TABLE and the target table has unique or exclusion constraints.
+ * Partitioned tables will use single inserts, because we assume they
+ * have these constraints, see rel_has_unique_or_exclusion_constr. If
+ * a regular table doesn't have unique or exclusion constraints,
+ * performing bulk inserts is OK, since without these constraints, a
+ * unique violation is not possible.
+ */
+ insertMethod = CIM_SINGLE;
+ }
else
{
/*
@@ -1110,6 +1231,8 @@ CopyFrom(CopyFromState cstate)
errcallback.arg = cstate;
errcallback.previous = error_context_stack;
error_context_stack = &errcallback;
+ mtcontext.mtstate = mtstate;
+ mtcontext.estate = estate;
for (;;)
{
@@ -1164,7 +1287,7 @@ CopyFrom(CopyFromState cstate)
/* Report that this tuple was skipped by the ON_ERROR clause */
pgstat_progress_update_param(PROGRESS_COPY_TUPLES_SKIPPED,
- cstate->num_errors);
+ (cstate->num_conflicts + cstate->num_errors));
if (cstate->opts.reject_limit > 0 &&
cstate->num_errors > cstate->opts.reject_limit)
@@ -1204,6 +1327,121 @@ CopyFrom(CopyFromState cstate)
}
}
+ /*
+ * For COPY FROM(ON_CONFLICT TABLE), we use ExecInsert() to insert the
+ * input data into the destination table. The conflict_relOid
+ * indicates whether a unique constraint violation occurred for ON
+ * CONFLICT. If a conflict happened, we construct the conflict tuple
+ * and insert it into the conflict_table.
+ */
+ if (cstate->opts.on_conflict == ONCONFLICT_TABLE &&
+ insert_on_conflict)
+ {
+ Oid conflict_relOid = InvalidOid;
+
+ Assert(IsA(mtcontext.mtstate->ps.plan, ModifyTable));
+
+ Assert(((ModifyTable *) mtcontext.mtstate->ps.plan)->onConflictAction == ONCONFLICT_NOTHING);
+
+ mtcontext.estate->es_processed = 0;
+
+ ExecInsert(&mtcontext,
+ resultRelInfo,
+ myslot,
+ false,
+ NULL,
+ NULL,
+ &conflict_relOid);
+
+ if (!OidIsValid(conflict_relOid))
+ processed++;
+ else
+ {
+ int j = 0;
+ Datum *newvalues;
+ bool *nulls;
+ MemoryContext tmpcontext;
+ ModifyTableState *conflict_mstate = cstate->mtcontext->mtstate;
+ TupleDesc tupdesc = RelationGetDescr(cstate->conflictRel);
+
+ ExecClearTuple(conflictslot);
+
+ newvalues = conflictslot->tts_values;
+ nulls = conflictslot->tts_isnull;
+
+ for (int i = 0; i < tupdesc->natts; i++)
+ {
+ Form_pg_attribute att = TupleDescAttr(tupdesc, i);
+
+ if (att->attisdropped)
+ {
+ newvalues[i] = (Datum) 0;
+ nulls[i] = true;
+ continue;
+ }
+
+ j++;
+ nulls[i] = false;
+
+ switch (j)
+ {
+ case 1:
+ newvalues[i] = ObjectIdGetDatum(conflict_relOid);
+ break;
+
+ case 2:
+ newvalues[i] = CStringGetTextDatum(cstate->filename ? cstate->filename : "STDIN");
+ break;
+
+ case 3:
+ newvalues[i] = Int64GetDatum((int64) cstate->cur_lineno);
+ break;
+
+ case 4:
+ newvalues[i] = CStringGetTextDatum(cstate->line_buf.data);
+ break;
+
+ default:
+ elog(ERROR, "COPY conflict_table must have exactly 4 attributes");
+ break;
+ }
+ }
+
+ /* Build the virtual tuple. */
+ ExecStoreVirtualTuple(conflictslot);
+
+ tmpcontext =
+ MemoryContextSwitchTo(cstate->mtcontext->estate->es_query_cxt);
+
+ AfterTriggerBeginQuery();
+ conflict_mstate->mt_transition_capture =
+ MakeTransitionCaptureState(cstate->conflictRel->trigdesc,
+ RelationGetRelid(cstate->conflictRel),
+ CMD_INSERT);
+ /* Execute BEFORE STATEMENT insertion triggers */
+ ExecBSInsertTriggers(cstate->mtcontext->estate,
+ cstate->mtcontext->mtstate->rootResultRelInfo);
+ ExecInsert(cstate->mtcontext,
+ conflict_mstate->resultRelInfo,
+ conflictslot,
+ false,
+ NULL,
+ NULL,
+ NULL);
+ pgstat_progress_update_param(PROGRESS_COPY_TUPLES_SKIPPED,
+ ++cstate->num_conflicts);
+ /* Execute AFTER STATEMENT insertion triggers */
+ ExecASInsertTriggers(cstate->mtcontext->estate,
+ conflict_mstate->rootResultRelInfo,
+ conflict_mstate->mt_transition_capture);
+ AfterTriggerEndQuery(cstate->mtcontext->estate);
+
+ MemoryContextSwitchTo(tmpcontext);
+ }
+
+ continue;
+ }
+
/* Determine the partition to insert the tuple into */
if (proute)
{
@@ -1487,6 +1725,45 @@ CopyFrom(CopyFromState cstate)
MemoryContextSwitchTo(oldcontext);
+ /*
+ * This should be aligned with the resource release and destruction
+ * performed on the EState by ExecutorFinish and ExecutorEnd.
+ */
+ if (cstate->opts.on_conflict == ONCONFLICT_TABLE)
+ {
+ MemoryContext tmpcontext;
+ ModifyTableState *on_conflict_mtstate;
+
+ if (cstate->num_conflicts > 0 &&
+ cstate->opts.log_verbosity >= COPY_LOG_VERBOSITY_DEFAULT)
+ ereport(NOTICE,
+ errmsg_plural("%" PRIu64 " row was saved to conflict table \"%s\" due to unique constraint violation",
+ "%" PRIu64 " rows were saved to conflict table \"%s\" due to unique constraint violation",
+ cstate->num_conflicts,
+ cstate->num_conflicts,
+ RelationGetRelationName(cstate->conflictRel)));
+
+ tmpcontext = MemoryContextSwitchTo(cstate->mtcontext->estate->es_query_cxt);
+
+ on_conflict_mtstate = cstate->mtcontext->mtstate;
+ on_conflict_mtstate->mt_done = true;
+ cstate->mtcontext->estate->es_finished = true;
+
+ /* Release resources associated with conflict_table */
+ ExecResetTupleTable(cstate->mtcontext->estate->es_tupleTable, false);
+ ExecCloseResultRelations(cstate->mtcontext->estate);
+ ExecCloseRangeTableRelations(cstate->mtcontext->estate);
+
+ /* Do away with our snapshots */
+ UnregisterSnapshot(cstate->mtcontext->estate->es_snapshot);
+ UnregisterSnapshot(cstate->mtcontext->estate->es_crosscheck_snapshot);
+
+ /* Must switch out of context before destroying it */
+ MemoryContextSwitchTo(tmpcontext);
+
+ FreeExecutorState(cstate->mtcontext->estate);
+ }
+
/* Execute AFTER STATEMENT insertion triggers */
ExecASInsertTriggers(estate, target_resultRelInfo, cstate->transition_capture);
@@ -1513,6 +1790,10 @@ CopyFrom(CopyFromState cstate)
ExecCloseResultRelations(estate);
ExecCloseRangeTableRelations(estate);
+ /* Do away with our snapshots */
+ UnregisterSnapshot(estate->es_snapshot);
+ UnregisterSnapshot(estate->es_crosscheck_snapshot);
+
FreeExecutorState(estate);
return processed;
@@ -1634,6 +1915,43 @@ BeginCopyFrom(ParseState *pstate,
else
cstate->escontext = NULL;
+ if (cstate->opts.on_conflict == ONCONFLICT_TABLE)
+ {
+ Oid conflictRelid;
+ RangeVar *relvar;
+ List *relname_list;
+
+ Assert(cstate->opts.on_conflictRel != NULL);
+
+ relname_list = stringToQualifiedNameList(cstate->opts.on_conflictRel, NULL);
+ relvar = makeRangeVarFromNameList(relname_list);
+
+ /*
+ * Before inserting tuples into conflict_table, we first check its
+ * lock status. If it is already heavily locked, the subsequent COPY
+ * FROM (ON_CONFLICT TABLE) could hang waiting for the lock. To avoid
+ * this, we use RVR_NOWAIT and report an error immediately if
+ * conflict_table cannot be locked.
+ */
+ conflictRelid = RangeVarGetRelidExtended(relvar,
+ RowExclusiveLock,
+ RVR_NOWAIT,
+ RangeVarCallbackForCopyConflictTable,
+ NULL);
+
+ if (RelationGetRelid(cstate->rel) == conflictRelid)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("cannot use relation \"%s\" for COPY on_conflict error saving while copying data to it",
+ cstate->opts.on_conflictRel));
+
+ cstate->conflictRel = table_open(conflictRelid, NoLock);
+
+ CopyFromConflictTableCheck(cstate);
+
+ /* We will do permission check for conflict_table later */
+ }
+
if (cstate->opts.on_error == COPY_ON_ERROR_SET_NULL)
{
/*
@@ -1956,6 +2274,9 @@ EndCopyFrom(CopyFromState cstate)
pgstat_progress_end_command();
+ if (cstate->conflictRel != NULL)
+ table_close(cstate->conflictRel, NoLock);
+
MemoryContextDelete(cstate->copycontext);
pfree(cstate);
}
@@ -1994,3 +2315,227 @@ ClosePipeFromProgram(CopyFromState cstate)
errdetail_internal("%s", wait_result_to_str(pclose_rc))));
}
}
+
+/*
+ * The conflict_table must be a plain table and must not have generated
+ * columns, rules, or row-level security policies.
+ *
+ * It also must follow a specific schema: the first column is an OID
+ * (recording the COPY FROM source relation), the second is the COPY FILE path,
+ * the third is the line number, and the fourth contains the raw line content.
+ */
+static void
+CopyFromConflictTableCheck(CopyFromState cstate)
+{
+ int valid_col_count = 0;
+ char *errdetail_msg = NULL;
+ Relation relation = cstate->conflictRel;
+ TupleDesc tupDesc = RelationGetDescr(relation);
+
+ if (tupDesc->constr &&
+ (tupDesc->constr->has_generated_stored || tupDesc->constr->has_generated_virtual))
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot use relation \"%s\" for COPY on_conflict error saving",
+ RelationGetRelationName(relation)),
+ errdetail("The conflict_table cannot have generated columns."));
+
+ if (relation->rd_rules || relation->rd_rel->relrowsecurity)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot use relation \"%s\" for COPY on_conflict error saving",
+ RelationGetRelationName(relation)),
+ relation->rd_rules ? errdetail("The conflict_table cannot have rules.")
+ : errdetail("The conflict_table cannot have row-level security policies."));
+
+ for (int i = 0; i < tupDesc->natts; i++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(tupDesc, i);
+
+ /* Skip columns marked as dropped */
+ if (attr->attisdropped)
+ continue;
+
+ valid_col_count++;
+
+ /* Check types based on the effective column position */
+ switch (valid_col_count)
+ {
+ case 1:
+ if (attr->atttypid != OIDOID)
+ errdetail_msg = _("The first column of the conflict_table data type is not OID.");
+ break;
+ case 2:
+ if (attr->atttypid != TEXTOID)
+ errdetail_msg = _("The second column of the conflict_table data type is not TEXT.");
+ break;
+ case 3:
+ if (attr->atttypid != INT8OID)
+ errdetail_msg = _("The third column of the conflict_table data type is not BIGINT.");
+ break;
+ case 4:
+ if (attr->atttypid != TEXTOID)
+ errdetail_msg = _("The fourth column of the conflict_table data type is not TEXT.");
+ break;
+ default:
+ errdetail_msg = _("The conflict_table should only have four columns");
+ break;
+ }
+ }
+
+ if (valid_col_count != 4)
+ errdetail_msg = _("The conflict_table should only have four columns");
+
+ if (errdetail_msg)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot use relation \"%s\" for COPY on_conflict error saving",
+ RelationGetRelationName(relation)),
+ errdetail_internal("%s", errdetail_msg),
+ errhint("The conflict_table must contain exactly four columns with data types, in order: OID, TEXT, BIGINT, TEXT"));
+}
+
+/*
+ * Initialize executor infrastructure needed to insert rows into the
+ * conflict table during COPY FROM (ON_CONFLICT TABLE)
+ *
+ * Performs permission checks, builds a ResultRelInfo with open indexes, sets up
+ * snapshots, and populates CopyFromState->mtcontext with a ready-to-use
+ * ModifyTableState.
+ */
+static void
+CopyFromConflictTableInit(CopyFromState cstate)
+{
+ ModifyTableState *mtstate;
+ ModifyTable *node;
+ MemoryContext tmpcontext;
+ ParseState *pstate = make_parsestate(NULL);
+ EState *estate = CreateExecutorState();
+
+ cstate->mtcontext = palloc0_object(ModifyTableContext);
+
+ tmpcontext = MemoryContextSwitchTo(estate->es_query_cxt);
+
+ estate->es_output_cid = GetCurrentCommandId(true);
+ estate->es_snapshot = RegisterSnapshot(GetActiveSnapshot());
+ estate->es_crosscheck_snapshot = RegisterSnapshot(InvalidSnapshot);
+
+ /* permission check for conflict_table */
+ CopyConflictTablePermissionCheck(pstate, cstate->conflictRel);
+
+ node = makeNode(ModifyTable);
+ node->operation = CMD_INSERT;
+ node->canSetTag = false;
+ node->rootRelation = 0;
+ node->resultRelations = list_make1_int(1);
+ node->onConflictAction = ONCONFLICT_NONE;
+
+ /*
+ * We need a ResultRelInfo so we can use the regular executor's
+ * index-entry-making machinery.
+ */
+ ExecInitRangeTable(estate, pstate->p_rtable, pstate->p_rteperminfos,
+ bms_make_singleton(1));
+
+ /* Populate the ModifyTableState for inserting record to conflict_table */
+ mtstate = makeNode(ModifyTableState);
+ mtstate->ps.plan = (Plan *) node;
+ mtstate->ps.state = estate;
+
+ mtstate->operation = CMD_INSERT;
+ mtstate->canSetTag = node->canSetTag;
+ mtstate->mt_done = false;
+
+ mtstate->mt_nrels = 1;
+ mtstate->resultRelInfo = palloc_array(ResultRelInfo, mtstate->mt_nrels);
+
+ mtstate->rootResultRelInfo = mtstate->resultRelInfo;
+ ExecInitResultRelation(estate, mtstate->resultRelInfo,
+ linitial_int(node->resultRelations));
+
+ /* Verify the named relation is a valid target for INSERT */
+ CheckValidResultRel(mtstate->resultRelInfo, node->operation,
+ node->onConflictAction, NIL);
+
+ /*
+ * Open the table's indexes, if we have not done so already, so that we
+ * can add new index entries for the inserted tuple.
+ */
+ if (cstate->conflictRel->rd_rel->relhasindex &&
+ mtstate->resultRelInfo->ri_IndexRelationDescs == NULL)
+ ExecOpenIndices(mtstate->resultRelInfo, node->onConflictAction != ONCONFLICT_NONE);
+
+ MemoryContextSwitchTo(tmpcontext);
+
+ cstate->mtcontext->mtstate = mtstate;
+ cstate->mtcontext->estate = estate;
+}
+
+/*
+ * COPY (ON_CONFLICT TABLE) log COPY FROM unique constraint violation details to
+ * the conflict_table. Obviously, the current user must have INSERT privileges
+ * on all columns of the conflict_table.
+ */
+static void
+CopyConflictTablePermissionCheck(ParseState *pstate, Relation rel)
+{
+ LOCKMODE lockmode = RowExclusiveLock;
+ AclResult aclresult;
+
+ /* Must have INSERT privilege on the conflict_table */
+ aclresult = pg_class_aclcheck(RelationGetRelid(rel), GetUserId(), ACL_INSERT);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, get_relkind_objtype(get_rel_relkind(RelationGetRelid(rel))),
+ RelationGetRelationName(rel));
+
+ addRangeTableEntryForRelation(pstate, rel, lockmode,
+ NULL, false, false);
+}
+
+/*
+ * Callback to RangeVarGetRelidExtended().
+ *
+ * Checks the following:
+ * - the relation specified is a table.
+ * - the table is not a system table.
+ *
+ * If any of these checks fails then an error is raised.
+ */
+static void
+RangeVarCallbackForCopyConflictTable(const RangeVar *rv, Oid relid, Oid oldrelid,
+ void *arg)
+{
+ HeapTuple tuple;
+ Form_pg_class classform;
+ char relkind;
+
+ tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(tuple))
+ return;
+
+ classform = (Form_pg_class) GETSTRUCT(tuple);
+ relkind = classform->relkind;
+
+ /* No system table modifications unless explicitly allowed. */
+ if (!allowSystemTableMods && IsSystemClass(relid, classform))
+ ereport(ERROR,
+ errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied: \"%s\" is a system catalog",
+ rv->relname));
+
+ /*
+ * Currently, the conflict_table table must be a regular relation.
+ *
+ * TODO: Allow conflict_table to be a partitioned table. This should be
+ * not difficult, but requires proper handling of constraints and triggers
+ * on the partitioned table.
+ */
+ if (relkind != RELKIND_RELATION)
+ ereport(ERROR,
+ errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot use relation \"%s\" for COPY on_conflict error saving",
+ rv->relname),
+ errdetail_relkind_not_supported(relkind));
+
+ ReleaseSysCache(tuple);
+}
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 112c17b0d64..acefcb20498 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -4857,9 +4857,8 @@ show_modifytable_info(ModifyTableState *mtstate, List *ancestors,
resolution = "NOTHING";
else if (node->onConflictAction == ONCONFLICT_UPDATE)
resolution = "UPDATE";
- else
+ else if (node->onConflictAction == ONCONFLICT_SELECT)
{
- Assert(node->onConflictAction == ONCONFLICT_SELECT);
switch (node->onConflictLockStrength)
{
case LCS_NONE:
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 85f3df7c09a..50f822ed3d9 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -825,6 +825,10 @@ ExecGetUpdateNewTuple(ResultRelInfo *relinfo,
* *insert_destrel is the relation where it was inserted.
* These are only set on success.
*
+ * If conflict_relOid is not NULL, we also checks if a unique constraint
+ * violation actually occurred for the ON CONFLICT DO NOTHING clause. If so,
+ * we sets *conflict_relOid to the OID of that relation.
+ *
* This may change the currently active tuple conversion map in
* mtstate->mt_transition_capture, so the callers must take care to
* save the previous value to avoid losing track of it.
@@ -836,7 +840,8 @@ ExecInsert(ModifyTableContext *context,
TupleTableSlot *slot,
bool canSetTag,
TupleTableSlot **inserted_tuple,
- ResultRelInfo **insert_destrel)
+ ResultRelInfo **insert_destrel,
+ Oid *conflict_relOid)
{
ModifyTableState *mtstate = context->mtstate;
EState *estate = context->estate;
@@ -1120,6 +1125,9 @@ ExecInsert(ModifyTableContext *context,
&conflictTid, &invalidItemPtr,
arbiterIndexes))
{
+ if (conflict_relOid)
+ *conflict_relOid = RelationGetRelid(resultRelationDesc);
+
/* committed conflict tuple found */
if (onconflict == ONCONFLICT_UPDATE)
{
@@ -1581,7 +1589,7 @@ ExecForPortionOfLeftovers(ModifyTableContext *context,
AfterTriggerBeginQuery();
ExecSetupTransitionCaptureState(mtstate, estate);
fireBSTriggers(mtstate);
- ExecInsert(context, resultRelInfo, leftoverSlot, false, NULL, NULL);
+ ExecInsert(context, resultRelInfo, leftoverSlot, false, NULL, NULL, NULL);
fireASTriggers(mtstate);
AfterTriggerEndQuery(estate);
}
@@ -2321,7 +2329,7 @@ ExecCrossPartitionUpdate(ModifyTableContext *context,
/* Tuple routing starts from the root table. */
context->cpUpdateReturningSlot =
ExecInsert(context, mtstate->rootResultRelInfo, slot, canSetTag,
- inserted_tuple, insert_destrel);
+ inserted_tuple, insert_destrel, NULL);
/*
* Reset the transition state that may possibly have been written by
@@ -4083,7 +4091,7 @@ ExecMergeNotMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
mtstate->mt_merge_action = action;
rslot = ExecInsert(context, mtstate->rootResultRelInfo,
- newslot, canSetTag, NULL, NULL);
+ newslot, canSetTag, NULL, NULL, NULL);
mtstate->mt_merge_inserted += 1;
break;
case CMD_NOTHING:
@@ -4914,7 +4922,7 @@ ExecModifyTable(PlanState *pstate)
ExecInitInsertProjection(node, resultRelInfo);
slot = ExecGetInsertNewTuple(resultRelInfo, context.planSlot);
slot = ExecInsert(&context, resultRelInfo, slot,
- node->canSetTag, NULL, NULL);
+ node->canSetTag, NULL, NULL, NULL);
break;
case CMD_UPDATE:
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ff4e1388c55..2854f2a884f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3755,6 +3755,7 @@ copy_generic_opt_arg:
| NumericOnly { $$ = (Node *) $1; }
| '*' { $$ = (Node *) makeNode(A_Star); }
| DEFAULT { $$ = (Node *) makeString("default"); }
+ | TABLE { $$ = (Node *) makeString("table"); }
| '(' copy_generic_opt_arg_list ')' { $$ = (Node *) $2; }
| /* EMPTY */ { $$ = NULL; }
;
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index abecfe51098..35e86e4a724 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -94,9 +94,13 @@ typedef struct CopyFormatOptions
bool *force_null_flags; /* per-column CSV FN flags */
bool convert_selectively; /* do selective binary conversion? */
CopyOnErrorChoice on_error; /* what to do when error happened */
+ OnConflictAction on_conflict; /* what to do when unique conflict
+ * happened */
CopyLogVerbosityChoice log_verbosity; /* verbosity of logged messages */
int64 reject_limit; /* maximum tolerable number of errors */
List *convert_select; /* list of column names (can be NIL) */
+ char *on_conflictRel; /* Name of the table used to log details of
+ * unique constraint violations. */
} CopyFormatOptions;
/* These are private in commands/copy[from|to].c */
diff --git a/src/include/commands/copyfrom_internal.h b/src/include/commands/copyfrom_internal.h
index 9d3e244ee55..de91b380b1c 100644
--- a/src/include/commands/copyfrom_internal.h
+++ b/src/include/commands/copyfrom_internal.h
@@ -16,6 +16,7 @@
#include "commands/copy.h"
#include "commands/trigger.h"
+#include "executor/nodeModifyTable.h"
#include "nodes/miscnodes.h"
/*
@@ -73,6 +74,7 @@ typedef struct CopyFromStateData
/* parameters from the COPY command */
Relation rel; /* relation to copy from */
+ Relation conflictRel; /* relation for copy from conflict saving */
List *attnumlist; /* integer list of attnums to copy */
char *filename; /* filename, or NULL for STDIN */
bool is_program; /* is 'filename' a program to popen? */
@@ -102,6 +104,8 @@ typedef struct CopyFromStateData
* execution */
uint64 num_errors; /* total number of rows which contained soft
* errors */
+ uint64 num_conflicts; /* total number of rows skipped due to unique
+ * constraint conflict */
int *defmap; /* array of default att numbers related to
* missing att */
ExprState **defexprs; /* array of default att expressions for all
@@ -189,6 +193,13 @@ typedef struct CopyFromStateData
#define RAW_BUF_BYTES(cstate) ((cstate)->raw_buf_len - (cstate)->raw_buf_index)
uint64 bytes_processed; /* number of bytes processed so far */
+
+ /*
+ * INSERT operation context for inserting COPY FROM unique constraint
+ * violation failure information to conflict_table. This is set only when
+ * COPY FROM (ON_CONFLICT TABLE) is used; otherwise it remains NULL.
+ */
+ ModifyTableContext *mtcontext;
} CopyFromStateData;
extern void ReceiveCopyBegin(CopyFromState cstate);
diff --git a/src/include/executor/nodeModifyTable.h b/src/include/executor/nodeModifyTable.h
index 250bd64ad15..916899f1e1c 100644
--- a/src/include/executor/nodeModifyTable.h
+++ b/src/include/executor/nodeModifyTable.h
@@ -68,7 +68,8 @@ extern TupleTableSlot *ExecInsert(ModifyTableContext *context,
TupleTableSlot *slot,
bool canSetTag,
TupleTableSlot **inserted_tuple,
- ResultRelInfo **insert_destrel);
+ ResultRelInfo **insert_destrel,
+ Oid *conflict_relOid);
extern void ExecEndModifyTable(ModifyTableState *node);
extern void ExecReScanModifyTable(ModifyTableState *node);
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index a2925ae4946..5bf26cae088 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -429,6 +429,7 @@ typedef enum OnConflictAction
ONCONFLICT_NOTHING, /* ON CONFLICT ... DO NOTHING */
ONCONFLICT_UPDATE, /* ON CONFLICT ... DO UPDATE */
ONCONFLICT_SELECT, /* ON CONFLICT ... DO SELECT */
+ ONCONFLICT_TABLE, /* COPY FROM (ON_CONFLICT TABLE) */
} OnConflictAction;
/*
diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out
index 37498cdd6e7..877b5c1e1a6 100644
--- a/src/test/regress/expected/copy.out
+++ b/src/test/regress/expected/copy.out
@@ -430,6 +430,14 @@ copy tab_progress_reporting from :'filename'
where (salary < 2000);
INFO: progress: {"type": "FILE", "command": "COPY FROM", "relname": "tab_progress_reporting", "tuples_skipped": 0, "has_bytes_total": true, "tuples_excluded": 1, "tuples_processed": 2, "has_bytes_processed": true}
-- Generate COPY FROM report with PIPE, with some skipped tuples.
+create unique index tab_progress_reporting_idx1 on tab_progress_reporting(name);
+create temp table conflict_tbl(copy_tbl oid, filename text, lineno bigint, line text);
+copy tab_progress_reporting from stdin(on_conflict table, conflict_table 'conflict_tbl');
+NOTICE: 3 rows were saved to conflict table "conflict_tbl" due to unique constraint violation
+INFO: progress: {"type": "PIPE", "command": "COPY FROM", "relname": "tab_progress_reporting", "tuples_skipped": 3, "has_bytes_total": false, "tuples_excluded": 0, "tuples_processed": 0, "has_bytes_processed": true}
+drop index tab_progress_reporting_idx1;
+drop table conflict_tbl;
+-- Generate COPY FROM report with PIPE, with some skipped tuples.
copy tab_progress_reporting from stdin(on_error ignore);
NOTICE: 2 rows were skipped due to data type incompatibility
INFO: progress: {"type": "PIPE", "command": "COPY FROM", "relname": "tab_progress_reporting", "tuples_skipped": 2, "has_bytes_total": false, "tuples_excluded": 0, "tuples_processed": 1, "has_bytes_processed": true}
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index 919eabd5f78..4765aa1f8ef 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -888,7 +888,224 @@ ERROR: skipped more than REJECT_LIMIT (3) rows due to data type incompatibility
CONTEXT: COPY check_ign_err, line 5, column n: ""
COPY check_ign_err FROM STDIN WITH (on_error ignore, reject_limit 4);
NOTICE: 4 rows were skipped due to data type incompatibility
+CREATE DOMAIN d_text as TEXT;
+CREATE TABLE t_copy_tblp(c text, b int, a int) PARTITION BY RANGE(a);
+CREATE TABLE t_copy_tbl(a int, b int, c text);
+ALTER TABLE t_copy_tblp ATTACH PARTITION t_copy_tbl FOR VALUES FROM (MINVALUE) TO (100);
+CREATE TABLE t_copy_tbl1 PARTITION OF t_copy_tblp FOR VALUES FROM (100) TO (200);
+CREATE TABLE err_tbl1(copy_tbl oid, filename text, lineno bigint, line text generated always as ('hh') stored);
+COPY instead_of_insert_tbl_view FROM STDIN (on_conflict table, conflict_table err_tbl1); -- error
+ERROR: cannot use relation "err_tbl1" for COPY on_conflict error saving
+DETAIL: The conflict_table cannot have generated columns.
+CREATE POLICY p1 ON err_tbl1 FOR SELECT USING (true);
+ALTER TABLE err_tbl1 ENABLE ROW LEVEL SECURITY;
+ALTER TABLE err_tbl1 FORCE ROW LEVEL SECURITY;
+CREATE VIEW err_tblv AS SELECT * FROM err_tbl1;
+COPY t_copy_tbl FROM STDIN WITH (on_conflict table, conflict_table err_tblv); -- error
+ERROR: cannot use relation "err_tblv" for COPY on_conflict error saving
+DETAIL: This operation is not supported for views.
+DROP VIEW err_tblv;
+COPY t_copy_tbl FROM STDIN WITH (on_conflict table); -- error
+ERROR: COPY ON_CONFLICT requires CONFLICT_TABLE option
+COPY t_copy_tbl FROM STDIN WITH (conflict_table err_tbl1); -- error
+ERROR: COPY CONFLICT_TABLE requires ON_CONFLICT option specified as TABLE
+COPY t_copy_tbl TO STDOUT (on_conflict table, conflict_table err_tbl1); -- error
+ERROR: COPY ON_CONFLICT cannot be used with COPY TO
+LINE 1: COPY t_copy_tbl TO STDOUT (on_conflict table, conflict_table...
+ ^
+-- error, conflict_table cannot have generated column
+COPY t_copy_tbl FROM STDIN WITH (on_conflict table, conflict_table err_tbl1);
+ERROR: cannot use relation "err_tbl1" for COPY on_conflict error saving
+DETAIL: The conflict_table cannot have generated columns.
+ALTER TABLE err_tbl1 ALTER COLUMN line DROP EXPRESSION;
+-- error, conflict_table cannot have RLS
+COPY t_copy_tbl FROM STDIN WITH (on_conflict table, conflict_table err_tbl1);
+ERROR: cannot use relation "err_tbl1" for COPY on_conflict error saving
+DETAIL: The conflict_table cannot have row-level security policies.
+DROP POLICY IF EXISTS p1 ON err_tbl1;
+ALTER TABLE err_tbl1 DISABLE ROW LEVEL SECURITY;
+ALTER TABLE err_tbl1 ALTER COLUMN line SET DATA TYPE d_text;
+COPY t_copy_tbl FROM STDIN WITH (on_conflict table, conflict_table err_tbl1); -- error, data type mismatch
+ERROR: cannot use relation "err_tbl1" for COPY on_conflict error saving
+DETAIL: The fourth column of the conflict_table data type is not TEXT.
+HINT: The conflict_table must contain exactly four columns with data types, in order: OID, TEXT, BIGINT, TEXT
+ALTER TABLE err_tbl1 DROP COLUMN line;
+COPY t_copy_tbl FROM STDIN WITH (on_conflict table, conflict_table err_tbl1); -- error, less column
+ERROR: cannot use relation "err_tbl1" for COPY on_conflict error saving
+DETAIL: The conflict_table should only have four columns
+HINT: The conflict_table must contain exactly four columns with data types, in order: OID, TEXT, BIGINT, TEXT
+ALTER TABLE err_tbl1 ADD COLUMN line text, ADD column extra int;
+COPY t_copy_tbl FROM STDIN WITH (on_conflict table, conflict_table err_tbl1); -- error, extra column
+ERROR: cannot use relation "err_tbl1" for COPY on_conflict error saving
+DETAIL: The conflict_table should only have four columns
+HINT: The conflict_table must contain exactly four columns with data types, in order: OID, TEXT, BIGINT, TEXT
+ALTER TABLE err_tbl1 DROP COLUMN extra;
+COPY t_copy_tblp(a, c, b) FROM STDIN (format binary, on_conflict table, conflict_table err_tbl1); -- error
+ERROR: only ON_CONFLICT STOP is allowed in BINARY mode
+COPY t_copy_tblp(a, c, b) FROM STDIN (on_conflict 'table', conflict_table 'err_tbl1'); -- single quote is ok
+COPY t_copy_tblp(a, c, b) FROM STDIN (on_conflict "table", conflict_table "err_tbl1"); -- double quote is ok
+COPY t_copy_tblp(a, c, b) FROM STDIN (delimiter ',', on_conflict table, conflict_table 'err_tbl1'); -- no quote is ok
+-- COPY on_conflict table cannot apply to deferred unique constraint
+ALTER TABLE t_copy_tbl ADD CONSTRAINT t_copy_tbl_unq1 UNIQUE (a) DEFERRABLE INITIALLY DEFERRED;
+BEGIN;
+COPY t_copy_tbl FROM STDIN (delimiter ',', on_conflict table, conflict_table err_tbl1);
+ERROR: ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters
+CONTEXT: COPY t_copy_tbl, line 1: "1,2,3"
+ROLLBACK;
+ALTER TABLE t_copy_tbl DROP CONSTRAINT t_copy_tbl_unq1;
+ALTER TABLE err_tbl1 ADD CONSTRAINT cc CHECK (lineno > 0);
+ALTER TABLE err_tbl1 ADD CONSTRAINT nn NOT NULL copy_tbl;
+CREATE UNIQUE INDEX ON t_copy_tbl (b) WHERE a = 1;
+CREATE UNIQUE INDEX ON t_copy_tbl ((b+1));
+CREATE UNIQUE INDEX ON t_copy_tbl (c);
+-- permission check
+BEGIN;
+CREATE USER regress_user31;
+GRANT INSERT(copy_tbl, filename, lineno) ON TABLE err_tbl1 TO regress_user31;
+GRANT SELECT ON TABLE err_tbl1 TO regress_user31;
+GRANT ALL ON TABLE t_copy_tbl TO regress_user31;
+SAVEPOINT s1;
+SET ROLE regress_user31;
+COPY t_copy_tbl FROM STDIN (delimiter ',',on_conflict table, conflict_table err_tbl1); -- error, insufficient privilege
+ERROR: permission denied for table err_tbl1
+ROLLBACK TO SAVEPOINT s1;
+GRANT INSERT ON TABLE err_tbl1 to regress_user31;
+GRANT INSERT(line) ON TABLE err_tbl1 TO regress_user31;
+SET ROLE regress_user31;
+COPY t_copy_tbl FROM STDIN (delimiter ',',on_conflict table, conflict_table err_tbl1); -- ok
+RESET ROLE;
+ROLLBACK;
+COPY t_copy_tbl(b, a, c) FROM STDIN (delimiter ',', on_conflict table, conflict_table err_tbl1, log_verbosity verbose); -- ok
+NOTICE: 2 rows were saved to conflict table "err_tbl1" due to unique constraint violation
+SELECT tableoid::regclass, * FROM t_copy_tblp;
+ tableoid | c | b | a
+------------+---+---+---
+ t_copy_tbl | 3 | 2 | 1
+(1 row)
+
+SELECT copy_tbl::regclass, filename, lineno, line FROM err_tbl1;
+ copy_tbl | filename | lineno | line
+------------+----------+--------+---------
+ t_copy_tbl | STDIN | 1 | 2,1,aaa
+ t_copy_tbl | STDIN | 2 | 2,1,XXX
+(2 rows)
+
+CREATE OR REPLACE FUNCTION trig_copy_conflict_insert()
+RETURNS TRIGGER LANGUAGE plpgsql AS
+$$
+BEGIN
+ if (TG_LEVEL = 'STATEMENT' and TG_WHEN = 'AFTER') then
+ RAISE NOTICE E'trigger name: %, % % FOR EACH %\n', TG_NAME, TG_WHEN, TG_OP, TG_LEVEL;
+ else
+ RAISE NOTICE 'trigger name: %, % % FOR EACH %', TG_NAME, TG_WHEN, TG_OP, TG_LEVEL;
+ end if;
+ if (TG_OP = 'INSERT' and TG_LEVEL = 'ROW' and TG_WHEN = 'BEFORE') then
+ RAISE NOTICE 'NEW lineno: %, line: %', NEW.lineno, NEW.line;
+ end if;
+ return new;
+END;
+$$;
+CREATE TRIGGER t_copy_tbl_before_row_trig
+ BEFORE INSERT ON err_tbl1
+ FOR EACH ROW EXECUTE PROCEDURE trig_copy_conflict_insert();
+CREATE TRIGGER t_copy_tbl_after_row_trig
+ AFTER INSERT ON err_tbl1
+ FOR EACH ROW EXECUTE PROCEDURE trig_copy_conflict_insert();
+CREATE TRIGGER t_copy_tbl_before_stmt_trig
+ BEFORE INSERT ON err_tbl1
+ FOR EACH STATEMENT EXECUTE PROCEDURE trig_copy_conflict_insert();
+CREATE TRIGGER t_copy_tbl_after_stmt_trig
+ AFTER INSERT ON err_tbl1
+ REFERENCING NEW TABLE AS new_rows
+ FOR EACH STATEMENT EXECUTE PROCEDURE trig_copy_conflict_insert();
+CREATE UNIQUE INDEX ON t_copy_tblp (a);
+table t_copy_tblp;
+ c | b | a
+---+---+---
+ 3 | 2 | 1
+(1 row)
+
+\d+ t_copy_tblp
+ Partitioned table "public.t_copy_tblp"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ c | text | | | | extended | |
+ b | integer | | | | plain | |
+ a | integer | | | | plain | |
+Partition key: RANGE (a)
+Indexes:
+ "t_copy_tblp_a_idx" UNIQUE, btree (a)
+Partitions:
+ t_copy_tbl FOR VALUES FROM (MINVALUE) TO (100)
+ t_copy_tbl1 FOR VALUES FROM (100) TO (200)
+
+-- Row-level and statement-level triggers will fire for each row inserted into
+-- conflict_table
+BEGIN ISOLATION LEVEL REPEATABLE READ;
+INSERT INTO t_copy_tblp(b, a, c) VALUES (14,7,'xxxxxxxx');
+DELETE FROM t_copy_tblp WHERE b = 14 and a = 7 and c = 'xxxxxxxx';
+COPY t_copy_tblp(b, a, c) FROM STDIN (delimiter ',', on_conflict table, conflict_table err_tbl1, log_verbosity verbose);
+NOTICE: trigger name: t_copy_tbl_before_stmt_trig, BEFORE INSERT FOR EACH STATEMENT
+NOTICE: trigger name: t_copy_tbl_before_row_trig, BEFORE INSERT FOR EACH ROW
+NOTICE: NEW lineno: 2, line: 6,11,aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
+NOTICE: trigger name: t_copy_tbl_after_row_trig, AFTER INSERT FOR EACH ROW
+NOTICE: trigger name: t_copy_tbl_after_stmt_trig, AFTER INSERT FOR EACH STATEMENT
+
+NOTICE: trigger name: t_copy_tbl_before_stmt_trig, BEFORE INSERT FOR EACH STATEMENT
+NOTICE: trigger name: t_copy_tbl_before_row_trig, BEFORE INSERT FOR EACH ROW
+NOTICE: NEW lineno: 4, line: 12,2,xxxxxxxx
+NOTICE: trigger name: t_copy_tbl_after_row_trig, AFTER INSERT FOR EACH ROW
+NOTICE: trigger name: t_copy_tbl_after_stmt_trig, AFTER INSERT FOR EACH STATEMENT
+
+NOTICE: trigger name: t_copy_tbl_before_stmt_trig, BEFORE INSERT FOR EACH STATEMENT
+NOTICE: trigger name: t_copy_tbl_before_row_trig, BEFORE INSERT FOR EACH ROW
+NOTICE: NEW lineno: 5, line: 13,3,xxxxxxxx
+NOTICE: trigger name: t_copy_tbl_after_row_trig, AFTER INSERT FOR EACH ROW
+NOTICE: trigger name: t_copy_tbl_after_stmt_trig, AFTER INSERT FOR EACH STATEMENT
+
+NOTICE: trigger name: t_copy_tbl_before_stmt_trig, BEFORE INSERT FOR EACH STATEMENT
+NOTICE: trigger name: t_copy_tbl_before_row_trig, BEFORE INSERT FOR EACH ROW
+NOTICE: NEW lineno: 7, line: 2,199,Z
+NOTICE: trigger name: t_copy_tbl_after_row_trig, AFTER INSERT FOR EACH ROW
+NOTICE: trigger name: t_copy_tbl_after_stmt_trig, AFTER INSERT FOR EACH STATEMENT
+
+NOTICE: 4 rows were saved to conflict table "err_tbl1" due to unique constraint violation
+COPY t_copy_tblp(b, a, c) FROM STDIN (delimiter ',', on_conflict table, conflict_table err_tbl1, log_verbosity verbose);
+NOTICE: trigger name: t_copy_tbl_before_stmt_trig, BEFORE INSERT FOR EACH STATEMENT
+NOTICE: trigger name: t_copy_tbl_before_row_trig, BEFORE INSERT FOR EACH ROW
+NOTICE: NEW lineno: 1, line: 199,199,Y
+NOTICE: trigger name: t_copy_tbl_after_row_trig, AFTER INSERT FOR EACH ROW
+NOTICE: trigger name: t_copy_tbl_after_stmt_trig, AFTER INSERT FOR EACH STATEMENT
+
+NOTICE: 1 row was saved to conflict table "err_tbl1" due to unique constraint violation
+ALTER TABLE err_tbl1 DISABLE TRIGGER USER;
+COMMIT;
+CREATE TABLE err_tbl6 (
+ id1 int4range,
+ valid_at int4range,
+ CONSTRAINT err_tbl6_uq UNIQUE (id1, valid_at WITHOUT OVERLAPS)
+);
+COPY err_tbl6 FROM STDIN (on_conflict table, conflict_table err_tbl1); -- error
+ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "err_tbl6"
+CONTEXT: COPY err_tbl6, line 1: "[11,12) empty"
+COPY err_tbl6 FROM STDIN (on_conflict table, conflict_table err_tbl1);
+NOTICE: 1 row was saved to conflict table "err_tbl1" due to unique constraint violation
+SELECT copy_tbl::regclass, filename, lineno, line FROM err_tbl1;
+ copy_tbl | filename | lineno | line
+-------------+----------+--------+----------------------------------------------------------------------------------
+ t_copy_tbl | STDIN | 1 | 2,1,aaa
+ t_copy_tbl | STDIN | 2 | 2,1,XXX
+ t_copy_tbl | STDIN | 2 | 6,11,aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
+ t_copy_tbl | STDIN | 4 | 12,2,xxxxxxxx
+ t_copy_tbl | STDIN | 5 | 13,3,xxxxxxxx
+ t_copy_tbl1 | STDIN | 7 | 2,199,Z
+ t_copy_tbl1 | STDIN | 1 | 199,199,Y
+ err_tbl6 | STDIN | 2 | [1,10) [1,12)
+(8 rows)
+
-- clean up
+DROP TABLE err_tbl1;
+DROP DOMAIN d_text;
DROP TABLE forcetest;
DROP TABLE vistest;
DROP FUNCTION truncate_in_subxact();
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 34e2e7ee355..15d944f94a0 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -761,15 +761,21 @@ insert into dropcol(key, keep1, keep2) values(1, '5', 5) on conflict(key)
;
DROP TABLE dropcol;
-- check handling of regular btree constraint along with gist constraint
+create table unique_conflict(copy_tbl oid, filename text, lineno bigint, line text);
create table twoconstraints (f1 int unique, f2 box,
exclude using gist(f2 with &&));
insert into twoconstraints values(1, '((0,0),(1,1))');
insert into twoconstraints values(1, '((2,2),(3,3))'); -- fail on f1
ERROR: duplicate key value violates unique constraint "twoconstraints_f1_key"
DETAIL: Key (f1)=(1) already exists.
+copy twoconstraints from stdin (delimiter ';', on_conflict table, conflict_table unique_conflict);
+NOTICE: 1 row was saved to conflict table "unique_conflict" due to unique constraint violation
insert into twoconstraints values(2, '((0,0),(1,2))'); -- fail on f2
ERROR: conflicting key value violates exclusion constraint "twoconstraints_f2_excl"
DETAIL: Key (f2)=((1,2),(0,0)) conflicts with existing key (f2)=((1,1),(0,0)).
+insert into twoconstraints values(2, '((0,0),(1,2))') on conflict do nothing; -- ok
+copy twoconstraints from stdin (delimiter ';', on_conflict table, conflict_table unique_conflict);
+NOTICE: 1 row was saved to conflict table "unique_conflict" due to unique constraint violation
insert into twoconstraints values(2, '((0,0),(1,2))')
on conflict on constraint twoconstraints_f1_key do nothing; -- fail on f2
ERROR: conflicting key value violates exclusion constraint "twoconstraints_f2_excl"
@@ -784,6 +790,21 @@ select * from twoconstraints;
drop table twoconstraints;
-- check handling of self-conflicts at various isolation levels
+create table selfconflict0 (f1 int primary key, f2 int);
+begin transaction isolation level read committed;
+copy selfconflict0 from stdin (delimiter ',', on_conflict table, conflict_table unique_conflict);
+NOTICE: 1 row was saved to conflict table "unique_conflict" due to unique constraint violation
+commit;
+begin transaction isolation level repeatable read;
+copy selfconflict0 from stdin (delimiter ',', on_conflict table, conflict_table unique_conflict);
+NOTICE: 1 row was saved to conflict table "unique_conflict" due to unique constraint violation
+commit;
+begin transaction isolation level serializable;
+copy selfconflict0 from stdin (delimiter ',', on_conflict table, conflict_table unique_conflict);
+NOTICE: 1 row was saved to conflict table "unique_conflict" due to unique constraint violation
+commit;
+drop table selfconflict0;
+drop table unique_conflict;
create table selfconflict (f1 int primary key, f2 int);
begin transaction isolation level read committed;
insert into selfconflict values (1,1), (1,2) on conflict do nothing;
diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out
index e062a4e5c2c..d31a5f9da86 100644
--- a/src/test/regress/expected/rangetypes.out
+++ b/src/test/regress/expected/rangetypes.out
@@ -1559,6 +1559,7 @@ drop table test_range_elem;
-- constraints with range types, use singleton int ranges for the "="
-- portion of the constraint.
--
+create temp table unique_conflict0(copy_tbl oid, filename text, lineno bigint, line text);
create table test_range_excl(
room int4range,
speaker int4range,
@@ -1571,15 +1572,22 @@ insert into test_range_excl
insert into test_range_excl
values(int4range(123, 123, '[]'), int4range(2, 2, '[]'), '[2010-01-02 11:00, 2010-01-02 12:00)');
insert into test_range_excl
- values(int4range(123, 123, '[]'), int4range(3, 3, '[]'), '[2010-01-02 10:10, 2010-01-02 11:00)');
+ values(int4range(123, 123, '[]'), int4range(3, 3, '[]'), '[2010-01-02 10:10, 2010-01-02 11:00)'); -- error
ERROR: conflicting key value violates exclusion constraint "test_range_excl_room_during_excl"
DETAIL: Key (room, during)=([123,124), ["Sat Jan 02 10:10:00 2010","Sat Jan 02 11:00:00 2010")) conflicts with existing key (room, during)=([123,124), ["Sat Jan 02 10:00:00 2010","Sat Jan 02 11:00:00 2010")).
+insert into test_range_excl
+ values(int4range(123, 123, '[]'), int4range(3, 3, '[]'), '[2010-01-02 10:10, 2010-01-02 11:00)') on conflict do nothing;
insert into test_range_excl
values(int4range(124, 124, '[]'), int4range(3, 3, '[]'), '[2010-01-02 10:10, 2010-01-02 11:10)');
insert into test_range_excl
- values(int4range(125, 125, '[]'), int4range(1, 1, '[]'), '[2010-01-02 10:10, 2010-01-02 11:00)');
+ values(int4range(125, 125, '[]'), int4range(1, 1, '[]'), '[2010-01-02 10:10, 2010-01-02 11:00)'); -- error
ERROR: conflicting key value violates exclusion constraint "test_range_excl_speaker_during_excl"
DETAIL: Key (speaker, during)=([1,2), ["Sat Jan 02 10:10:00 2010","Sat Jan 02 11:00:00 2010")) conflicts with existing key (speaker, during)=([1,2), ["Sat Jan 02 10:00:00 2010","Sat Jan 02 11:00:00 2010")).
+insert into test_range_excl
+ values(int4range(125, 125, '[]'), int4range(1, 1, '[]'), '[2010-01-02 10:10, 2010-01-02 11:00)') on conflict do nothing;
+copy test_range_excl from stdin with (delimiter ';', on_conflict table, conflict_table unique_conflict0);
+NOTICE: 2 rows were saved to conflict table "unique_conflict0" due to unique constraint violation
+drop table unique_conflict0;
-- test bigint ranges
select int8range(10000000000::int8, 20000000000::int8,'(]');
int8range
diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql
index 094fd76c12b..401f0ca8622 100644
--- a/src/test/regress/sql/copy.sql
+++ b/src/test/regress/sql/copy.sql
@@ -369,6 +369,17 @@ truncate tab_progress_reporting;
copy tab_progress_reporting from :'filename'
where (salary < 2000);
+-- Generate COPY FROM report with PIPE, with some skipped tuples.
+create unique index tab_progress_reporting_idx1 on tab_progress_reporting(name);
+create temp table conflict_tbl(copy_tbl oid, filename text, lineno bigint, line text);
+copy tab_progress_reporting from stdin(on_conflict table, conflict_table 'conflict_tbl');
+sharon 25 (115,12) 1000 sam
+bill 20 (111,10) 1000 sharon
+bill 20 (111,10) 1000 sharon
+\.
+drop index tab_progress_reporting_idx1;
+drop table conflict_tbl;
+
-- Generate COPY FROM report with PIPE, with some skipped tuples.
copy tab_progress_reporting from stdin(on_error ignore);
sharon x (15,12) x sam
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
index f853499021d..d6a5da4860a 100644
--- a/src/test/regress/sql/copy2.sql
+++ b/src/test/regress/sql/copy2.sql
@@ -640,7 +640,171 @@ a {7} 7
10 {10} 10
\.
+CREATE DOMAIN d_text as TEXT;
+CREATE TABLE t_copy_tblp(c text, b int, a int) PARTITION BY RANGE(a);
+CREATE TABLE t_copy_tbl(a int, b int, c text);
+ALTER TABLE t_copy_tblp ATTACH PARTITION t_copy_tbl FOR VALUES FROM (MINVALUE) TO (100);
+CREATE TABLE t_copy_tbl1 PARTITION OF t_copy_tblp FOR VALUES FROM (100) TO (200);
+
+CREATE TABLE err_tbl1(copy_tbl oid, filename text, lineno bigint, line text generated always as ('hh') stored);
+COPY instead_of_insert_tbl_view FROM STDIN (on_conflict table, conflict_table err_tbl1); -- error
+
+CREATE POLICY p1 ON err_tbl1 FOR SELECT USING (true);
+ALTER TABLE err_tbl1 ENABLE ROW LEVEL SECURITY;
+ALTER TABLE err_tbl1 FORCE ROW LEVEL SECURITY;
+
+CREATE VIEW err_tblv AS SELECT * FROM err_tbl1;
+COPY t_copy_tbl FROM STDIN WITH (on_conflict table, conflict_table err_tblv); -- error
+DROP VIEW err_tblv;
+
+COPY t_copy_tbl FROM STDIN WITH (on_conflict table); -- error
+COPY t_copy_tbl FROM STDIN WITH (conflict_table err_tbl1); -- error
+COPY t_copy_tbl TO STDOUT (on_conflict table, conflict_table err_tbl1); -- error
+
+-- error, conflict_table cannot have generated column
+COPY t_copy_tbl FROM STDIN WITH (on_conflict table, conflict_table err_tbl1);
+ALTER TABLE err_tbl1 ALTER COLUMN line DROP EXPRESSION;
+
+-- error, conflict_table cannot have RLS
+COPY t_copy_tbl FROM STDIN WITH (on_conflict table, conflict_table err_tbl1);
+DROP POLICY IF EXISTS p1 ON err_tbl1;
+ALTER TABLE err_tbl1 DISABLE ROW LEVEL SECURITY;
+
+ALTER TABLE err_tbl1 ALTER COLUMN line SET DATA TYPE d_text;
+COPY t_copy_tbl FROM STDIN WITH (on_conflict table, conflict_table err_tbl1); -- error, data type mismatch
+ALTER TABLE err_tbl1 DROP COLUMN line;
+COPY t_copy_tbl FROM STDIN WITH (on_conflict table, conflict_table err_tbl1); -- error, less column
+ALTER TABLE err_tbl1 ADD COLUMN line text, ADD column extra int;
+COPY t_copy_tbl FROM STDIN WITH (on_conflict table, conflict_table err_tbl1); -- error, extra column
+ALTER TABLE err_tbl1 DROP COLUMN extra;
+
+COPY t_copy_tblp(a, c, b) FROM STDIN (format binary, on_conflict table, conflict_table err_tbl1); -- error
+COPY t_copy_tblp(a, c, b) FROM STDIN (on_conflict 'table', conflict_table 'err_tbl1'); -- single quote is ok
+\.
+COPY t_copy_tblp(a, c, b) FROM STDIN (on_conflict "table", conflict_table "err_tbl1"); -- double quote is ok
+\.
+COPY t_copy_tblp(a, c, b) FROM STDIN (delimiter ',', on_conflict table, conflict_table 'err_tbl1'); -- no quote is ok
+1,3,2
+\.
+-- COPY on_conflict table cannot apply to deferred unique constraint
+ALTER TABLE t_copy_tbl ADD CONSTRAINT t_copy_tbl_unq1 UNIQUE (a) DEFERRABLE INITIALLY DEFERRED;
+BEGIN;
+COPY t_copy_tbl FROM STDIN (delimiter ',', on_conflict table, conflict_table err_tbl1);
+1,2,3
+\.
+ROLLBACK;
+ALTER TABLE t_copy_tbl DROP CONSTRAINT t_copy_tbl_unq1;
+
+ALTER TABLE err_tbl1 ADD CONSTRAINT cc CHECK (lineno > 0);
+ALTER TABLE err_tbl1 ADD CONSTRAINT nn NOT NULL copy_tbl;
+CREATE UNIQUE INDEX ON t_copy_tbl (b) WHERE a = 1;
+CREATE UNIQUE INDEX ON t_copy_tbl ((b+1));
+CREATE UNIQUE INDEX ON t_copy_tbl (c);
+
+-- permission check
+BEGIN;
+CREATE USER regress_user31;
+GRANT INSERT(copy_tbl, filename, lineno) ON TABLE err_tbl1 TO regress_user31;
+GRANT SELECT ON TABLE err_tbl1 TO regress_user31;
+GRANT ALL ON TABLE t_copy_tbl TO regress_user31;
+SAVEPOINT s1;
+SET ROLE regress_user31;
+COPY t_copy_tbl FROM STDIN (delimiter ',',on_conflict table, conflict_table err_tbl1); -- error, insufficient privilege
+1,2,3
+\.
+ROLLBACK TO SAVEPOINT s1;
+GRANT INSERT ON TABLE err_tbl1 to regress_user31;
+GRANT INSERT(line) ON TABLE err_tbl1 TO regress_user31;
+SET ROLE regress_user31;
+COPY t_copy_tbl FROM STDIN (delimiter ',',on_conflict table, conflict_table err_tbl1); -- ok
+\.
+RESET ROLE;
+ROLLBACK;
+
+COPY t_copy_tbl(b, a, c) FROM STDIN (delimiter ',', on_conflict table, conflict_table err_tbl1, log_verbosity verbose); -- ok
+2,1,aaa
+2,1,XXX
+\.
+
+SELECT tableoid::regclass, * FROM t_copy_tblp;
+SELECT copy_tbl::regclass, filename, lineno, line FROM err_tbl1;
+
+CREATE OR REPLACE FUNCTION trig_copy_conflict_insert()
+RETURNS TRIGGER LANGUAGE plpgsql AS
+$$
+BEGIN
+ if (TG_LEVEL = 'STATEMENT' and TG_WHEN = 'AFTER') then
+ RAISE NOTICE E'trigger name: %, % % FOR EACH %\n', TG_NAME, TG_WHEN, TG_OP, TG_LEVEL;
+ else
+ RAISE NOTICE 'trigger name: %, % % FOR EACH %', TG_NAME, TG_WHEN, TG_OP, TG_LEVEL;
+ end if;
+ if (TG_OP = 'INSERT' and TG_LEVEL = 'ROW' and TG_WHEN = 'BEFORE') then
+ RAISE NOTICE 'NEW lineno: %, line: %', NEW.lineno, NEW.line;
+ end if;
+ return new;
+END;
+$$;
+
+CREATE TRIGGER t_copy_tbl_before_row_trig
+ BEFORE INSERT ON err_tbl1
+ FOR EACH ROW EXECUTE PROCEDURE trig_copy_conflict_insert();
+CREATE TRIGGER t_copy_tbl_after_row_trig
+ AFTER INSERT ON err_tbl1
+ FOR EACH ROW EXECUTE PROCEDURE trig_copy_conflict_insert();
+CREATE TRIGGER t_copy_tbl_before_stmt_trig
+ BEFORE INSERT ON err_tbl1
+ FOR EACH STATEMENT EXECUTE PROCEDURE trig_copy_conflict_insert();
+CREATE TRIGGER t_copy_tbl_after_stmt_trig
+ AFTER INSERT ON err_tbl1
+ REFERENCING NEW TABLE AS new_rows
+ FOR EACH STATEMENT EXECUTE PROCEDURE trig_copy_conflict_insert();
+
+CREATE UNIQUE INDEX ON t_copy_tblp (a);
+table t_copy_tblp;
+\d+ t_copy_tblp
+
+-- Row-level and statement-level triggers will fire for each row inserted into
+-- conflict_table
+BEGIN ISOLATION LEVEL REPEATABLE READ;
+INSERT INTO t_copy_tblp(b, a, c) VALUES (14,7,'xxxxxxxx');
+DELETE FROM t_copy_tblp WHERE b = 14 and a = 7 and c = 'xxxxxxxx';
+
+COPY t_copy_tblp(b, a, c) FROM STDIN (delimiter ',', on_conflict table, conflict_table err_tbl1, log_verbosity verbose);
+4,17,aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
+6,11,aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
+15,21,xxxxxxxx
+12,2,xxxxxxxx
+13,3,xxxxxxxx
+199,199,Y
+2,199,Z
+\.
+
+COPY t_copy_tblp(b, a, c) FROM STDIN (delimiter ',', on_conflict table, conflict_table err_tbl1, log_verbosity verbose);
+199,199,Y
+\.
+ALTER TABLE err_tbl1 DISABLE TRIGGER USER;
+COMMIT;
+
+CREATE TABLE err_tbl6 (
+ id1 int4range,
+ valid_at int4range,
+ CONSTRAINT err_tbl6_uq UNIQUE (id1, valid_at WITHOUT OVERLAPS)
+);
+
+COPY err_tbl6 FROM STDIN (on_conflict table, conflict_table err_tbl1); -- error
+[11,12) empty
+\.
+
+COPY err_tbl6 FROM STDIN (on_conflict table, conflict_table err_tbl1);
+[1,10) [1,2)
+[1,10) [1,12)
+\.
+
+SELECT copy_tbl::regclass, filename, lineno, line FROM err_tbl1;
+
-- clean up
+DROP TABLE err_tbl1;
+DROP DOMAIN d_text;
DROP TABLE forcetest;
DROP TABLE vistest;
DROP FUNCTION truncate_in_subxact();
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index a5a84d1d4b8..93e4a6d1275 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -434,11 +434,19 @@ DROP TABLE dropcol;
-- check handling of regular btree constraint along with gist constraint
+create table unique_conflict(copy_tbl oid, filename text, lineno bigint, line text);
create table twoconstraints (f1 int unique, f2 box,
exclude using gist(f2 with &&));
insert into twoconstraints values(1, '((0,0),(1,1))');
insert into twoconstraints values(1, '((2,2),(3,3))'); -- fail on f1
+copy twoconstraints from stdin (delimiter ';', on_conflict table, conflict_table unique_conflict);
+1;((2,2),(3,3))
+\.
insert into twoconstraints values(2, '((0,0),(1,2))'); -- fail on f2
+insert into twoconstraints values(2, '((0,0),(1,2))') on conflict do nothing; -- ok
+copy twoconstraints from stdin (delimiter ';', on_conflict table, conflict_table unique_conflict);
+2;((0,0),(1,2))
+\.
insert into twoconstraints values(2, '((0,0),(1,2))')
on conflict on constraint twoconstraints_f1_key do nothing; -- fail on f2
insert into twoconstraints values(2, '((0,0),(1,2))')
@@ -447,6 +455,29 @@ select * from twoconstraints;
drop table twoconstraints;
-- check handling of self-conflicts at various isolation levels
+create table selfconflict0 (f1 int primary key, f2 int);
+begin transaction isolation level read committed;
+copy selfconflict0 from stdin (delimiter ',', on_conflict table, conflict_table unique_conflict);
+4,1
+4,2
+\.
+commit;
+
+begin transaction isolation level repeatable read;
+copy selfconflict0 from stdin (delimiter ',', on_conflict table, conflict_table unique_conflict);
+5,1
+5,2
+\.
+commit;
+
+begin transaction isolation level serializable;
+copy selfconflict0 from stdin (delimiter ',', on_conflict table, conflict_table unique_conflict);
+6,1
+6,2
+\.
+commit;
+drop table selfconflict0;
+drop table unique_conflict;
create table selfconflict (f1 int primary key, f2 int);
diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql
index 5c4b0337b7a..a25aab8e785 100644
--- a/src/test/regress/sql/rangetypes.sql
+++ b/src/test/regress/sql/rangetypes.sql
@@ -415,6 +415,7 @@ drop table test_range_elem;
-- constraints with range types, use singleton int ranges for the "="
-- portion of the constraint.
--
+create temp table unique_conflict0(copy_tbl oid, filename text, lineno bigint, line text);
create table test_range_excl(
room int4range,
@@ -429,11 +430,21 @@ insert into test_range_excl
insert into test_range_excl
values(int4range(123, 123, '[]'), int4range(2, 2, '[]'), '[2010-01-02 11:00, 2010-01-02 12:00)');
insert into test_range_excl
- values(int4range(123, 123, '[]'), int4range(3, 3, '[]'), '[2010-01-02 10:10, 2010-01-02 11:00)');
+ values(int4range(123, 123, '[]'), int4range(3, 3, '[]'), '[2010-01-02 10:10, 2010-01-02 11:00)'); -- error
+insert into test_range_excl
+ values(int4range(123, 123, '[]'), int4range(3, 3, '[]'), '[2010-01-02 10:10, 2010-01-02 11:00)') on conflict do nothing;
insert into test_range_excl
values(int4range(124, 124, '[]'), int4range(3, 3, '[]'), '[2010-01-02 10:10, 2010-01-02 11:10)');
insert into test_range_excl
- values(int4range(125, 125, '[]'), int4range(1, 1, '[]'), '[2010-01-02 10:10, 2010-01-02 11:00)');
+ values(int4range(125, 125, '[]'), int4range(1, 1, '[]'), '[2010-01-02 10:10, 2010-01-02 11:00)'); -- error
+insert into test_range_excl
+ values(int4range(125, 125, '[]'), int4range(1, 1, '[]'), '[2010-01-02 10:10, 2010-01-02 11:00)') on conflict do nothing;
+
+copy test_range_excl from stdin with (delimiter ';', on_conflict table, conflict_table unique_conflict0);
+[123,123];[3,3];[2010-01-02 10:10, 2010-01-02 11:00]
+[125,125];[1,1];[2010-01-02 10:10, 2010-01-02 11:00]
+\.
+drop table unique_conflict0;
-- test bigint ranges
select int8range(10000000000::int8, 20000000000::int8,'(]');
--
2.34.1