From 590c839d9d3f73f6bdaea7746e4bb4730d756590 Mon Sep 17 00:00:00 2001 From: jian he Date: Sat, 25 Apr 2026 12:11:20 +0800 Subject: [PATCH v1 1/1] COPY ON_CONFLICT TABLE not sure how to deal with excludsion constraint reference: https://web.archive.org/web/20240328094030/https://riggs.business/blog/f/postgresql-todo-2023 discussion: https://postgr.es/m/ commitfest entry: https://commitfest.postgresql.org/patch/ --- doc/src/sgml/monitoring.sgml | 6 +- doc/src/sgml/ref/copy.sgml | 90 ++++ src/backend/commands/copy.c | 50 +++ src/backend/commands/copyfrom.c | 520 ++++++++++++++++++++++- src/backend/commands/explain.c | 3 +- src/backend/executor/nodeModifyTable.c | 2 +- src/backend/parser/gram.y | 1 + src/include/commands/copy.h | 5 + src/include/commands/copyfrom_internal.h | 4 + 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 | 88 ++++ src/test/regress/sql/copy.sql | 11 + src/test/regress/sql/copy2.sql | 86 ++++ 15 files changed, 863 insertions(+), 15 deletions(-) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 08d5b824552..0860da3d23b 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -6745,9 +6745,9 @@ FROM pg_stat_get_backend_idset() AS backendid; Number of tuples skipped because they contain malformed data. - This counter only advances when - ignore is specified to the ON_ERROR - option. + This counter advances when + ignore is specified to the ON_ERROR option + or table is specified to the ON_CONFLICT option. diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml index 4706c9a4410..7410248c0b4 100644 --- a/doc/src/sgml/ref/copy.sgml +++ b/doc/src/sgml/ref/copy.sgml @@ -44,6 +44,8 @@ COPY { table_name [ ( column_name [, ...] ) | * } FORCE_NOT_NULL { ( column_name [, ...] ) | * } FORCE_NULL { ( column_name [, ...] ) | * } + ON_CONFLICT conflict_action + CONFLICT_TABLE conflict_table ON_ERROR error_action REJECT_LIMIT maxerror ENCODING 'encoding_name' @@ -440,6 +442,92 @@ COPY (SELECT j FROM (VALUES ('null'::json), (NULL::json)) v(j)) + + ON_CONFLICT + + + Specifies the behavior when a row violates a unique constraint. + An conflict_action value of + stop means fail the command, while + table means save the conflicting input row to table + conflict_table + specified by CONFLICT_TABLE and continue with the next one. + The default is stop. + + + The table + options are applicable only for COPY FROM + when the FORMAT is text or csv. + + + If ON_CONFLICT is set to table, a + NOTICE message is emitted at the end of the command + reporting the number of rows that were inserted to table conflict_table + due to unique constraint violation, provided that at least one row was affected. + + + When the LOG_VERBOSITY option is set to + verbose, a NOTICE message is emitted + for each row insert by ON_CONFLICT, containing the + input line that violated the unique constraint. When set to + silent, no messages are emitted regarding discarded rows. + + + This uses the same mechanism as INSERT ... ON CONFLICT. + However, exclusion constraints are not supported; only NOT DEFERRABLE + unique constraints are checked for violations. + + + + + + CONFLICT_TABLE + + + Specifies a destination table (conflict_table) + to store details regarding unique constraint violations encountered during + the COPY FROM operation. The target table must define + exactly four columns, though the specific column names are not restricted. + The required column order and data types are: + + + + + + Data Type + Description + + + + + oid + + The OID of the destination table for the COPY FROM command. + This corresponds to pg_class.oid. + Note that no formal dependency is maintained; if the referenced table is dropped, this value will persist as a stale reference. + + + + text + The file path of the COPY FROM input. + + + bigint + The line number within the input source where the unique constraint violation occurred (starting at 1). + + + text + The raw line text content of the record that caused the violation. + + + + + + + + + + ON_ERROR @@ -493,6 +581,8 @@ COPY (SELECT j FROM (VALUES ('null'::json), (NULL::json)) v(j)) If not specified, ON_ERROR=ignore allows an unlimited number of errors, meaning COPY will skip all erroneous data. + Note: Rows ignored due to unique constraint violations via the + ON_CONFLICT option do not count toward this limit. diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index 003b70852bb..6101ebd500f 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -561,6 +561,29 @@ defGetCopyLogVerbosityChoice(DefElem *def, ParseState *pstate) return COPY_LOG_VERBOSITY_DEFAULT; /* keep compiler quiet */ } +/* + * Extract a OnConflictAction value from a DefElem. + */ +static OnConflictAction +defGetdefGetCopyOnConflictChoice(DefElem *def, ParseState *pstate) +{ + char *sval; + + sval = defGetString(def); + 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 +610,11 @@ ProcessCopyOptions(ParseState *pstate, bool freeze_specified = false; bool header_specified = false; bool on_error_specified = false; + bool conflict_tbl_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 */ @@ -774,6 +799,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 = defGetdefGetCopyOnConflictChoice(defel, pstate); + } + else if (strcmp(defel->defname, "conflict_table") == 0) + { + if (conflict_tbl_specified) + errorConflictingDefElem(defel, pstate); + conflict_tbl_specified = true; + + opts_out->on_conflict_tbl = defGetString(defel); + } else ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), @@ -782,6 +822,16 @@ ProcessCopyOptions(ParseState *pstate, parser_errposition(pstate, defel->location))); } + if (!(opts_out->on_conflict == ONCONFLICT_TABLE) && conflict_tbl_specified) + ereport(ERROR, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("COPY %s requires %s option", "CONFLICT_TABLE", "ON_CONFLICT")); + + if ((opts_out->on_conflict == ONCONFLICT_TABLE) && !conflict_tbl_specified) + ereport(ERROR, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("COPY %s requires %s option", "CONFLICT_TABLE", "ON_CONFLICT")); + /* * 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 64ac3063c61..8017f0b236e 100644 --- a/src/backend/commands/copyfrom.c +++ b/src/backend/commands/copyfrom.c @@ -42,16 +42,23 @@ #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 "storage/lmgr.h" #include "tcop/tcopprot.h" +#include "utils/acl.h" +#include "utils/builtins.h" +#include "utils/injection_point.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 +127,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(Relation relation); +static void RangeVarCallbackForCopyConflictTable(const RangeVar *rv, Oid relid, Oid oldrelid, + void *arg); +static void CopyFromConflictTableInit(CopyFromState cstate); +static void CopyConflictTablePermissionCheck(ParseState *pstate, Relation rel); /* @@ -801,6 +813,7 @@ CopyFrom(CopyFromState cstate) bool has_before_insert_row_trig; bool has_instead_insert_row_trig; bool leafpart_use_multi_insert = false; + TupleTableSlot *conflictslot = NULL; Assert(cstate->rel); Assert(list_length(cstate->range_table) == 1); @@ -808,6 +821,13 @@ CopyFrom(CopyFromState cstate) if (cstate->opts.on_error != COPY_ON_ERROR_STOP) Assert(cstate->escontext); + if (cstate->opts.on_conflict == ONCONFLICT_TABLE) + { + conflictslot = ExecInitExtraTupleSlot(estate, + RelationGetDescr(cstate->conflictRel), + &TTSOpsVirtual); + } + /* * The target must be a plain, foreign, or partitioned relation, or have * an INSTEAD OF INSERT row trigger. (Currently, such triggers are only @@ -923,7 +943,7 @@ 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, true); /* * Set up a ModifyTableState so we can let FDW(s) init themselves for @@ -1052,6 +1072,11 @@ CopyFrom(CopyFromState cstate) */ insertMethod = CIM_SINGLE; } + else if (cstate->opts.on_conflict != ONCONFLICT_NONE && + resultRelInfo->ri_NumIndices > 0) + { + insertMethod = CIM_SINGLE; + } else { /* @@ -1164,7 +1189,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) @@ -1425,15 +1450,218 @@ CopyFrom(CopyFromState cstate) } else { - /* OK, store the tuple and create index entries for it */ - table_tuple_insert(resultRelInfo->ri_RelationDesc, - myslot, mycid, ti_options, bistate); + if (cstate->opts.on_conflict == ONCONFLICT_NONE) + { + /* + * OK, store the tuple and create index entries + * for it + */ + table_tuple_insert(resultRelInfo->ri_RelationDesc, + myslot, mycid, ti_options, bistate); - if (resultRelInfo->ri_NumIndices > 0) + if (resultRelInfo->ri_NumIndices > 0) + recheckIndexes = ExecInsertIndexTuples(resultRelInfo, + estate, 0, + myslot, NIL, + NULL); + } + else if (resultRelInfo->ri_NumIndices > 0 && + cstate->opts.on_conflict != ONCONFLICT_NONE) + { + /* Perform a speculative insertion. */ + uint32 specToken; + ItemPointerData conflictTid; + ItemPointerData invalidItemPtr; + bool specConflict; + List *arbiterIndexes; + + ItemPointerSetInvalid(&invalidItemPtr); + arbiterIndexes = resultRelInfo->ri_onConflictArbiterIndexes; + + /* + * Do a non-conclusive check for conflicts first. + * + * We're not holding any locks yet, so this + * doesn't guarantee that the later insert won't + * conflict. But it avoids leaving behind a lot + * of canceled speculative insertions, if you run + * a lot of INSERT ON CONFLICT statements that do + * conflict. + * + * We loop back here if we find a conflict below, + * either during the pre-check, or when we + * re-check after inserting the tuple + * speculatively. Better allow interrupts in case + * some bug makes this an infinite loop. + */ + vlock: + CHECK_FOR_INTERRUPTS(); + specConflict = false; + if (!ExecCheckIndexConstraints(resultRelInfo, myslot, estate, + &conflictTid, &invalidItemPtr, + arbiterIndexes)) + { + /* + * This is equivalent to ON CONFLICT DO + * SELECT. We need verify that the tuple is + * visible to the executor's MVCC snapshot at + * higher isolation levels. See comments in + * ExecInsert->ExecCheckIndexConstraints also. + */ + if (cstate->opts.on_conflict == ONCONFLICT_TABLE) + { + int j = 0; + Datum *newvalues; + bool *nulls; + ModifyTableState *mstate = cstate->conflict_mstate; + EState *conflict_estate = mstate->ps.state; + TupleDesc tupdesc = RelationGetDescr(cstate->conflictRel); + + if (!table_tuple_fetch_row_version(resultRelInfo->ri_RelationDesc, + &conflictTid, + SnapshotAny, + ExecGetReturningSlot(conflict_estate, resultRelInfo))) + elog(ERROR, "failed to fetch conflicting tuple for ON CONFLICT"); + + ExecCheckTupleVisible(conflict_estate, + resultRelInfo->ri_RelationDesc, + ExecGetReturningSlot(conflict_estate, resultRelInfo)); + + ExecClearTuple(conflictslot); + + newvalues = conflictslot->tts_values; + nulls = conflictslot->tts_isnull; + + /* Prepare to build the result tuple */ + 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(RelationGetRelid(cstate->rel)); + 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(pnstrdup(cstate->line_buf.data, + cstate->line_buf.len)); + break; + + default: + elog(ERROR, "COPY ON CONFLICT table must have 4 attributes"); + break; + } + } + + /* Build the virtual tuple. */ + ExecStoreVirtualTuple(conflictslot); + + /* + * Check constraint and not-null + * constraint vertification + */ + if (tupdesc->constr) + ExecConstraints(mstate->resultRelInfo, conflictslot, conflict_estate); + + /* insert the tuple normally */ + table_tuple_insert(cstate->conflictRel, conflictslot, + conflict_estate->es_output_cid, + 0, NULL); + + /* insert index entries for tuple */ + if (mstate->resultRelInfo->ri_NumIndices > 0) + recheckIndexes = ExecInsertIndexTuples(mstate->resultRelInfo, + conflict_estate, + 0, conflictslot, NIL, + NULL); + list_free(recheckIndexes); + + cstate->num_conflicts++; + + /* + * Report that this tuple was skipped by + * the ON_ERROR or ON_CONFLICT clause + */ + pgstat_progress_update_param(PROGRESS_COPY_TUPLES_SKIPPED, + (cstate->num_conflicts + cstate->num_errors)); + + continue; + } + } + + /* + * Before we start insertion proper, acquire our + * "speculative insertion lock". Others can use + * that to wait for us to decide if we're going to + * go ahead with the insertion, instead of waiting + * for the whole transaction to complete. + */ + INJECTION_POINT("exec-copy-insert-before-insert-speculative", NULL); + specToken = SpeculativeInsertionLockAcquire(GetCurrentTransactionId()); + + /* insert the tuple, with the speculative token */ + table_tuple_insert_speculative(resultRelInfo->ri_RelationDesc, myslot, + estate->es_output_cid, + 0, + NULL, + specToken); + + /* insert index entries for tuple */ recheckIndexes = ExecInsertIndexTuples(resultRelInfo, - estate, 0, - myslot, NIL, - NULL); + estate, EIIT_NO_DUPE_ERROR, + myslot, arbiterIndexes, + &specConflict); + + /* adjust the tuple's state accordingly */ + table_tuple_complete_speculative(resultRelInfo->ri_RelationDesc, myslot, + specToken, !specConflict); + + /* + * Wake up anyone waiting for our decision. They + * will re-check the tuple, see that it's no + * longer speculative, and wait on our XID as if + * this was a regularly inserted tuple all along. + * Or if we killed the tuple, they will see it's + * dead, and proceed as if the tuple never + * existed. + */ + SpeculativeInsertionLockRelease(GetCurrentTransactionId()); + + /* + * If there was a conflict, start from the + * beginning. We'll do the pre-check again, which + * will now find the conflicting tuple (unless it + * aborts before we get there). + */ + if (specConflict) + { + list_free(recheckIndexes); + goto vlock; + } + + /* + * Since there was no insertion conflict, we're + * done + */ + } } /* AFTER ROW INSERT Triggers */ @@ -1482,6 +1710,18 @@ CopyFrom(CopyFromState cstate) cstate->num_errors)); } + if (cstate->num_conflicts > 0 && + cstate->opts.log_verbosity >= COPY_LOG_VERBOSITY_DEFAULT) + { + if (cstate->opts.on_conflict == ONCONFLICT_TABLE) + 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))); + } + if (bistate != NULL) FreeBulkInsertState(bistate); @@ -1515,6 +1755,17 @@ CopyFrom(CopyFromState cstate) FreeExecutorState(estate); + /* Close/release resouces associated with copy error saving */ + if (cstate->conflictRel) + { + ExecResetTupleTable(cstate->conflict_mstate->ps.state->es_tupleTable, false); + + ExecCloseResultRelations(cstate->conflict_mstate->ps.state); + ExecCloseRangeTableRelations(cstate->conflict_mstate->ps.state); + + FreeExecutorState(cstate->conflict_mstate->ps.state); + } + return processed; } @@ -1634,6 +1885,44 @@ BeginCopyFrom(ParseState *pstate, else cstate->escontext = NULL; + cstate->conflict_mstate = NULL; + if (cstate->opts.on_conflict == ONCONFLICT_TABLE) + { + Oid conflictRelid; + RangeVar *relvar; + List *relname_list; + + Assert(cstate->opts.on_conflict_tbl != NULL); + + relname_list = stringToQualifiedNameList(cstate->opts.on_conflict_tbl, NULL); + relvar = makeRangeVarFromNameList(relname_list); + + /* + * We might insert tuples into the conflict error-saving table later, + * so we first need to check its lock status. If it is already heavily + * locked, our subsequent COPY FROM may stuck. Instead of letting COPY + * FROM hang, report an error indicating that the conflict + * error-saving table is under heavy lock. + */ + 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_conflict_tbl)); + + cstate->conflictRel = table_open(conflictRelid, NoLock); + + CopyFromConflictTableInit(cstate); + + table_close(cstate->conflictRel, NoLock); + } + if (cstate->opts.on_error == COPY_ON_ERROR_SET_NULL) { int attr_count = list_length(cstate->attnumlist); @@ -1998,3 +2287,216 @@ ClosePipeFromProgram(CopyFromState cstate) errdetail_internal("%s", wait_result_to_str(pclose_rc)))); } } + +/* + * The conflict_table must be a plain table and is subject to the following + * restrictions: it cannot have foreign key constraints; nor can it have column + * DEFAULT values, triggers, rules, or row-level security policies. + * + * These restrictions are necessary to allow the use of table_tuple_insert(); + * otherwise, the executor would need to perform extensive additional checks and + * setup for each inserted error row. + */ +static void +CopyFromConflictTableCheck(Relation relation) +{ + int valid_col_count = 0; + TupleDesc tupDesc = RelationGetDescr(relation); + char *errdetail_msg = NULL; + + if (tupDesc->constr) + { + if (tupDesc->constr->has_generated_stored || tupDesc->constr->has_generated_virtual) + errdetail_msg = _("The conflict_table cannot have generated columns."); + } + + if (!errdetail_msg) + { + if (list_length(RelationGetFKeyList(relation)) > 0) + errdetail_msg = _("The conflict_table cannot have foreign keys."); + else if (relation->rd_rules) + errdetail_msg = _("The conflict_table cannot have rules."); + else if (relation->trigdesc) + errdetail_msg = _("The conflict_table cannot have triggers."); + else if (relation->rd_rel->relrowsecurity) + errdetail_msg = _("The conflict_table cannot have row-level security policies."); + } + + 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)); + + 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 must be type OID."); + break; + case 2: + if (attr->atttypid != TEXTOID) + errdetail_msg = _("The second column of the conflict_table must be type TEXT."); + break; + case 3: + if (attr->atttypid != INT8OID) + errdetail_msg = _("The third column of the conflict_table must be type BIGINT."); + break; + case 4: + if (attr->atttypid != TEXTOID) + errdetail_msg = _("The fourth column of the conflict_table must be type TEXT."); + break; + default: + errdetail_msg = _("The conflict_table must have exactly four columns."); + break; + } + } + + if (valid_col_count != 4) + errdetail_msg = _("The conflict_table is incomplete; exactly four columns are required."); + + 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)); +} + +static void +CopyFromConflictTableInit(CopyFromState cstate) +{ + ParseState *pstate; + ResultRelInfo *resultRelInfo; + EState *estate = CreateExecutorState(); + ModifyTableState *mtstate; + Relation relation; + + relation = cstate->conflictRel; + pstate = make_parsestate(NULL); + + CopyConflictTablePermissionCheck(pstate, relation); + + CopyFromConflictTableCheck(relation); + + /* + * 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)); + resultRelInfo = makeNode(ResultRelInfo); + ExecInitResultRelation(estate, resultRelInfo, 1); + + /* Verify the named relation is a valid target for INSERT */ + CheckValidResultRel(resultRelInfo, CMD_INSERT, ONCONFLICT_NONE, NIL); + + ExecOpenIndices(resultRelInfo, false); + + /* Set up a ModifyTableState for inserting record to CONFLICT_TABLE */ + mtstate = makeNode(ModifyTableState); + mtstate->ps.plan = NULL; + mtstate->ps.state = estate; + mtstate->operation = CMD_INSERT; + mtstate->mt_nrels = 1; + mtstate->resultRelInfo = resultRelInfo; + mtstate->rootResultRelInfo = resultRelInfo; + + cstate->conflict_mstate = mtstate; +} + +static void +CopyConflictTablePermissionCheck(ParseState *pstate, Relation rel) +{ + LOCKMODE lockmode = RowExclusiveLock; + ParseNamespaceItem *nsitem; + RTEPermissionInfo *perminfo; + TupleDesc tupDesc; + + nsitem = addRangeTableEntryForRelation(pstate, rel, lockmode, + NULL, false, false); + perminfo = nsitem->p_perminfo; + perminfo->requiredPerms = ACL_INSERT; + + tupDesc = RelationGetDescr(rel); + + for (int i = 0; i < tupDesc->natts; i++) + { + Bitmapset **bms; + int attno; + + CompactAttribute *attr = TupleDescCompactAttr(tupDesc, i); + + if (attr->attisdropped) + continue; + + attno = i + 1 - FirstLowInvalidHeapAttributeNumber; + bms = &perminfo->insertedCols; + + *bms = bms_add_member(*bms, attno); + + } + ExecCheckPermissions(pstate->p_rtable, list_make1(perminfo), true); +} + +/* + * Callback to RangeVarGetRelidExtended(). + * + * Checks the following: + * - the relation specified is a table. + * - current user must have INSERT priviledge on the 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; + AclResult aclresult; + + tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(relid)); + if (!HeapTupleIsValid(tuple)) + return; + + classform = (Form_pg_class) GETSTRUCT(tuple); + relkind = classform->relkind; + + /* Must have INSERT privilege */ + aclresult = pg_class_aclcheck(relid, GetUserId(), ACL_INSERT); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, get_relkind_objtype(get_rel_relkind(relid)), + rv->relname); + + /* 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)); + + /* The conflict error saving table must be a regular realtion */ + 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 4cb057ca4f9..4f7a3451bc2 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -380,7 +380,7 @@ ExecProcessReturning(ModifyTableContext *context, * path) on the basis of another tuple that is not visible to MVCC snapshot. * Check for the need to raise a serialization failure, and do so as necessary. */ -static void +extern void ExecCheckTupleVisible(EState *estate, Relation rel, TupleTableSlot *slot) 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..3aeebf24b67 100644 --- a/src/include/commands/copy.h +++ b/src/include/commands/copy.h @@ -36,6 +36,7 @@ typedef enum CopyOnErrorChoice COPY_ON_ERROR_STOP = 0, /* immediately throw errors, default */ COPY_ON_ERROR_IGNORE, /* ignore errors */ COPY_ON_ERROR_SET_NULL, /* set error field to null */ + COPY_ON_ERROR_TABLE, /* saving errors info to table */ } CopyOnErrorChoice; /* @@ -94,9 +95,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_conflict_tbl; /* on error, save error info to the table, + * table name */ } 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..ec8f6981fae 100644 --- a/src/include/commands/copyfrom_internal.h +++ b/src/include/commands/copyfrom_internal.h @@ -73,6 +73,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 +103,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 +192,7 @@ 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 */ + ModifyTableState *conflict_mstate; } CopyFromStateData; extern void ReceiveCopyBegin(CopyFromState cstate); diff --git a/src/include/executor/nodeModifyTable.h b/src/include/executor/nodeModifyTable.h index f6070e1cdf3..6d6eba159a5 100644 --- a/src/include/executor/nodeModifyTable.h +++ b/src/include/executor/nodeModifyTable.h @@ -29,5 +29,8 @@ extern void ExecReScanModifyTable(ModifyTableState *node); extern void ExecInitMergeTupleSlots(ModifyTableState *mtstate, ResultRelInfo *resultRelInfo); +extern void ExecCheckTupleVisible(EState *estate, + Relation rel, + TupleTableSlot *slot); #endif /* NODEMODIFYTABLE_H */ diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index a2925ae4946..22a329cb810 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 (ON_CONFLICT TABLE) */ } OnConflictAction; /* diff --git a/src/test/regress/expected/copy.out b/src/test/regress/expected/copy.out index 1714faab39c..36fe3c00765 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 7600e5239d2..7c45400c8dd 100644 --- a/src/test/regress/expected/copy2.out +++ b/src/test/regress/expected/copy2.out @@ -884,7 +884,95 @@ 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_tbl(a int, b int, c text); +CREATE TABLE err_tbl0(copy_tbl oid primary key); +CREATE TABLE err_tbl1(copy_tbl oid, filename text, lineno bigint, line text generated always as ('hh') stored); +ALTER TABLE err_tbl1 ADD CONSTRAINT con1 FOREIGN KEY (copy_tbl) REFERENCES err_tbl0(copy_tbl); +CREATE TRIGGER trg_x_after AFTER INSERT ON err_tbl1 FOR EACH ROW EXECUTE PROCEDURE fn_x_after(); +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; +-- The conflict error saving table must be a plain table and is subject to the +-- following restrictions: it cannot contain foreign key constraints; it must +-- not have triggers, rules, or row-level security policies. +COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', 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; +COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', 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 foreign keys. +ALTER TABLE err_tbl1 DROP CONSTRAINT con1; +COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', 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 triggers. +DROP TRIGGER IF EXISTS trg_x_after ON err_tbl1; +COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', 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 (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1); -- error +ERROR: cannot use relation "err_tbl1" for COPY on_conflict error saving +DETAIL: The fourth column of the conflict_table must be type TEXT. +ALTER TABLE err_tbl1 DROP COLUMN line; +COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1); -- error +ERROR: cannot use relation "err_tbl1" for COPY on_conflict error saving +DETAIL: The conflict_table is incomplete; exactly four columns are required. +ALTER TABLE err_tbl1 ADD COLUMN line text, ADD column extra int; +COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1); -- error +ERROR: cannot use relation "err_tbl1" for COPY on_conflict error saving +DETAIL: The conflict_table is incomplete; exactly four columns are required. +ALTER TABLE err_tbl1 DROP COLUMN extra; +CREATE VIEW err_tbl4 AS SELECT * FROM err_tbl1; +COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl4); -- error +ERROR: cannot use relation "err_tbl4" for COPY on_conflict error saving +DETAIL: This operation is not supported for views. +COPY t_copy_tbl(c, b) FROM STDIN (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1, log_verbosity verbose); -- ok +-- COPY ON_CONFLICT TABLE cannot apply to deferred unqiue 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); +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 +COPY t_copy_tbl(b,a, c) FROM STDIN (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1, log_verbosity verbose); +NOTICE: 3 rows were saved to conflict table "err_tbl1" due to unique constraint violation +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,1,xxxxxxxx + t_copy_tbl | STDIN | 5 | 13,1,xxxxxxxx + err_tbl6 | STDIN | 2 | [1,10) [1,12) +(6 rows) + -- clean up +DROP TABLE err_tbl0, err_tbl1 CASCADE; +NOTICE: drop cascades to view err_tbl4 +DROP DOMAIN d_text; DROP TABLE forcetest; DROP TABLE vistest; DROP FUNCTION truncate_in_subxact(); diff --git a/src/test/regress/sql/copy.sql b/src/test/regress/sql/copy.sql index eaad290b257..045cb17666f 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 e0810109473..c939c8602c2 100644 --- a/src/test/regress/sql/copy2.sql +++ b/src/test/regress/sql/copy2.sql @@ -636,7 +636,93 @@ a {7} 7 10 {10} 10 \. +CREATE DOMAIN d_text as TEXT; +CREATE TABLE t_copy_tbl(a int, b int, c text); +CREATE TABLE err_tbl0(copy_tbl oid primary key); + +CREATE TABLE err_tbl1(copy_tbl oid, filename text, lineno bigint, line text generated always as ('hh') stored); +ALTER TABLE err_tbl1 ADD CONSTRAINT con1 FOREIGN KEY (copy_tbl) REFERENCES err_tbl0(copy_tbl); +CREATE TRIGGER trg_x_after AFTER INSERT ON err_tbl1 FOR EACH ROW EXECUTE PROCEDURE fn_x_after(); +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; + +-- The conflict error saving table must be a plain table and is subject to the +-- following restrictions: it cannot contain foreign key constraints; it must +-- not have triggers, rules, or row-level security policies. +COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1); +ALTER TABLE err_tbl1 ALTER COLUMN line DROP EXPRESSION; +COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1); +ALTER TABLE err_tbl1 DROP CONSTRAINT con1; +COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1); +DROP TRIGGER IF EXISTS trg_x_after ON err_tbl1; +COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', 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 (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1); -- error +ALTER TABLE err_tbl1 DROP COLUMN line; +COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1); -- error + +ALTER TABLE err_tbl1 ADD COLUMN line text, ADD column extra int; +COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1); -- error +ALTER TABLE err_tbl1 DROP COLUMN extra; + +CREATE VIEW err_tbl4 AS SELECT * FROM err_tbl1; +COPY t_copy_tbl FROM STDIN WITH (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl4); -- error +COPY t_copy_tbl(c, b) FROM STDIN (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1, log_verbosity verbose); -- ok +3,2 +\. + +-- COPY ON_CONFLICT TABLE cannot apply to deferred unqiue 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); + +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 +\. + +COPY t_copy_tbl(b,a, c) FROM STDIN (DELIMITER ',', ON_CONFLICT TABLE, CONFLICT_TABLE err_tbl1, log_verbosity verbose); +4,17,aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +6,11,aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +11,1,xxxxxxxx +12,1,xxxxxxxx +13,1,xxxxxxxx +\. + +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_tbl0, err_tbl1 CASCADE; +DROP DOMAIN d_text; DROP TABLE forcetest; DROP TABLE vistest; DROP FUNCTION truncate_in_subxact(); -- 2.34.1