On Fri, May 15, 2026 at 7:56 PM Zsolt Parragi <[email protected]> wrote: > > I also noticed the early relation close mentioned by Jim, which can > crash the patch. > fixed.
> + This uses the same mechanism as <link > linkend="sql-on-conflict"><command>INSERT ... ON > CONFLICT</command></link>. > + However, exclusion constraints are not supported; only > <literal>NOT DEFERRABLE</literal> > + 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_no_unique_idx_test.sql
Description: application/sql
From 422d14d667ac72d0ffb75ca4df9a8ca24431b885 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Sun, 10 May 2026 12:13:09 +0800 Subject: [PATCH v4 1/2] export ExecInsert The ExecInsert function encapsulates core logic for the insertion pipeline, including partition routing, BEFORE ROW triggers, INSTEAD OF triggers, and AFTER ROW triggers and others. exporting ExecInsert, the COPY FROM command can leverage the exact same execution path as standard inserts. discussion: https://postgr.es/m/cacjufxg672yotdt87dbazf1c9scnzm7qsb+zu6vhc+j5qrj...@mail.gmail.com commitfest entry: https://commitfest.postgresql.org/patch/6736/ --- src/backend/executor/nodeModifyTable.c | 40 +---------------------- src/include/executor/nodeModifyTable.h | 45 ++++++++++++++++++++++++++ 2 files changed, 46 insertions(+), 39 deletions(-) diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 478cb01783c..85f3df7c09a 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -84,44 +84,6 @@ typedef struct MTTargetRelLookup int relationIndex; /* rel's index in resultRelInfo[] array */ } MTTargetRelLookup; -/* - * Context struct for a ModifyTable operation, containing basic execution - * state and some output variables populated by ExecUpdateAct() and - * ExecDeleteAct() to report the result of their actions to callers. - */ -typedef struct ModifyTableContext -{ - /* Operation state */ - ModifyTableState *mtstate; - EPQState *epqstate; - EState *estate; - - /* - * Slot containing tuple obtained from ModifyTable's subplan. Used to - * access "junk" columns that are not going to be stored. - */ - TupleTableSlot *planSlot; - - /* - * Information about the changes that were made concurrently to a tuple - * being updated or deleted - */ - TM_FailureData tmfd; - - /* - * The tuple deleted when doing a cross-partition UPDATE with a RETURNING - * clause that refers to OLD columns (converted to the root's tuple - * descriptor). - */ - TupleTableSlot *cpDeletedSlot; - - /* - * The tuple projected by the INSERT's RETURNING clause, when doing a - * cross-partition UPDATE - */ - TupleTableSlot *cpUpdateReturningSlot; -} ModifyTableContext; - /* * Context struct containing output data specific to UPDATE operations. */ @@ -868,7 +830,7 @@ ExecGetUpdateNewTuple(ResultRelInfo *relinfo, * save the previous value to avoid losing track of it. * ---------------------------------------------------------------- */ -static TupleTableSlot * +TupleTableSlot * ExecInsert(ModifyTableContext *context, ResultRelInfo *resultRelInfo, TupleTableSlot *slot, diff --git a/src/include/executor/nodeModifyTable.h b/src/include/executor/nodeModifyTable.h index f6070e1cdf3..250bd64ad15 100644 --- a/src/include/executor/nodeModifyTable.h +++ b/src/include/executor/nodeModifyTable.h @@ -13,8 +13,47 @@ #ifndef NODEMODIFYTABLE_H #define NODEMODIFYTABLE_H +#include "access/tableam.h" #include "nodes/execnodes.h" +/* + * Context struct for a ModifyTable operation, containing basic execution + * state and some output variables populated by ExecUpdateAct() and + * ExecDeleteAct() to report the result of their actions to callers. + */ +typedef struct ModifyTableContext +{ + /* Operation state */ + ModifyTableState *mtstate; + EPQState *epqstate; + EState *estate; + + /* + * Slot containing tuple obtained from ModifyTable's subplan. Used to + * access "junk" columns that are not going to be stored. + */ + TupleTableSlot *planSlot; + + /* + * Information about the changes that were made concurrently to a tuple + * being updated or deleted + */ + TM_FailureData tmfd; + + /* + * The tuple deleted when doing a cross-partition UPDATE with a RETURNING + * clause that refers to OLD columns (converted to the root's tuple + * descriptor). + */ + TupleTableSlot *cpDeletedSlot; + + /* + * The tuple projected by the INSERT's RETURNING clause, when doing a + * cross-partition UPDATE + */ + TupleTableSlot *cpUpdateReturningSlot; +} ModifyTableContext; + extern void ExecInitGenerated(ResultRelInfo *resultRelInfo, EState *estate, CmdType cmdtype); @@ -24,6 +63,12 @@ extern void ExecComputeStoredGenerated(ResultRelInfo *resultRelInfo, CmdType cmdtype); extern ModifyTableState *ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags); +extern TupleTableSlot *ExecInsert(ModifyTableContext *context, + ResultRelInfo *resultRelInfo, + TupleTableSlot *slot, + bool canSetTag, + TupleTableSlot **inserted_tuple, + ResultRelInfo **insert_destrel); extern void ExecEndModifyTable(ModifyTableState *node); extern void ExecReScanModifyTable(ModifyTableState *node); -- 2.34.1
From 187975f7644e7bdd07bff2f5f4b834241b0e537b Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> 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+j5qrj...@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
