Hi,
Rebased the patch to add support for OLD.* and NEW.*.
Andreas
From ffa108d03455fc3d901ff32b585dd8dc62a25963 Mon Sep 17 00:00:00 2001
From: Andreas Karlsson <andr...@proxel.se>
Date: Mon, 18 Nov 2024 00:29:15 +0100
Subject: [PATCH v5] Add support for ON CONFLICT DO SELECT [ FOR ... ]
Adds support for DO SELECT action for ON CONFLICT clause where we
select the tuples and optionally lock them. If the tuples are locked
with check for conflicts, otherwise not.
---
doc/src/sgml/ref/insert.sgml | 17 +-
src/backend/commands/explain.c | 33 ++-
src/backend/executor/nodeModifyTable.c | 278 +++++++++++++++---
src/backend/optimizer/plan/createplan.c | 2 +
src/backend/parser/analyze.c | 26 +-
src/backend/parser/gram.y | 20 +-
src/backend/parser/parse_clause.c | 7 +
src/backend/rewrite/rowsecurity.c | 42 ++-
src/include/nodes/execnodes.h | 2 +
src/include/nodes/lockoptions.h | 3 +-
src/include/nodes/nodes.h | 1 +
src/include/nodes/parsenodes.h | 4 +-
src/include/nodes/plannodes.h | 2 +
src/include/nodes/primnodes.h | 9 +-
src/test/regress/expected/insert_conflict.out | 151 ++++++++--
src/test/regress/sql/insert_conflict.sql | 79 +++--
16 files changed, 571 insertions(+), 105 deletions(-)
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index 3f139917790..6f4de8ab090 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -37,6 +37,7 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
<phrase>and <replaceable class="parameter">conflict_action</replaceable> is one of:</phrase>
DO NOTHING
+ DO SELECT [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } ]
DO UPDATE SET { <replaceable class="parameter">column_name</replaceable> = { <replaceable class="parameter">expression</replaceable> | DEFAULT } |
( <replaceable class="parameter">column_name</replaceable> [, ...] ) = [ ROW ] ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) |
( <replaceable class="parameter">column_name</replaceable> [, ...] ) = ( <replaceable class="parameter">sub-SELECT</replaceable> )
@@ -88,18 +89,24 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
<para>
The optional <literal>RETURNING</literal> clause causes <command>INSERT</command>
- to compute and return value(s) based on each row actually inserted
- (or updated, if an <literal>ON CONFLICT DO UPDATE</literal> clause was
- used). This is primarily useful for obtaining values that were
+ to compute and return value(s) based on each row actually inserted.
+ If an <literal>ON CONFLICT DO UPDATE</literal> clause was used,
+ <literal>RETURNING</literal> also returns tuples which were updated, and
+ in the presence of an <literal>ON CONFLICT DO SELECT</literal> clause all
+ input rows are returned. With a traditional <command>INSERT</command>,
+ the <literal>RETURNING</literal> clause is primarily useful for obtaining
+ values that were
supplied by defaults, such as a serial sequence number. However,
any expression using the table's columns is allowed. The syntax of
the <literal>RETURNING</literal> list is identical to that of the output
- list of <command>SELECT</command>. Only rows that were successfully
+ list of <command>SELECT</command>. If an <literal>ON CONFLICT DO SELECT</literal>
+ clause is not present, only rows that were successfully
inserted or updated will be returned. For example, if a row was
locked but not updated because an <literal>ON CONFLICT DO UPDATE
... WHERE</literal> clause <replaceable
class="parameter">condition</replaceable> was not satisfied, the
- row will not be returned.
+ row will not be returned. <literal>ON CONFLICT DO SELECT</literal>
+ works similarly, except no update takes place.
</para>
<para>
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index d8a7232cedb..559e1174902 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -4595,10 +4595,35 @@ show_modifytable_info(ModifyTableState *mtstate, List *ancestors,
if (node->onConflictAction != ONCONFLICT_NONE)
{
- ExplainPropertyText("Conflict Resolution",
- node->onConflictAction == ONCONFLICT_NOTHING ?
- "NOTHING" : "UPDATE",
- es);
+ const char *resolution;
+
+ if (node->onConflictAction == ONCONFLICT_NOTHING)
+ resolution = "NOTHING";
+ else if (node->onConflictAction == ONCONFLICT_UPDATE)
+ resolution = "UPDATE";
+ else if (node->onConflictAction == ONCONFLICT_SELECT)
+ {
+ switch (node->onConflictLockingStrength)
+ {
+ case LCS_NONE:
+ resolution = "SELECT";
+ break;
+ case LCS_FORKEYSHARE:
+ resolution = "SELECT FOR KEY SHARE";
+ break;
+ case LCS_FORSHARE:
+ resolution = "SELECT FOR SHARE";
+ break;
+ case LCS_FORNOKEYUPDATE:
+ resolution = "SELECT FOR NO KEY UPDATE";
+ break;
+ case LCS_FORUPDATE:
+ resolution = "SELECT FOR UPDATE";
+ break;
+ }
+ }
+
+ ExplainPropertyText("Conflict Resolution", resolution, es);
/*
* Don't display arbiter indexes at all when DO NOTHING variant
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index b0fe50075ad..131b4defb87 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -145,12 +145,23 @@ static void ExecCrossPartitionUpdateForeignKey(ModifyTableContext *context,
ItemPointer tupleid,
TupleTableSlot *oldslot,
TupleTableSlot *newslot);
+static bool ExecOnConflictLockRow(ModifyTableContext *context,
+ TupleTableSlot *existing,
+ ItemPointer conflictTid,
+ Relation relation,
+ LockTupleMode lockmode,
+ bool isUpdate);
static bool ExecOnConflictUpdate(ModifyTableContext *context,
ResultRelInfo *resultRelInfo,
ItemPointer conflictTid,
TupleTableSlot *excludedSlot,
bool canSetTag,
TupleTableSlot **returning);
+static bool ExecOnConflictSelect(ModifyTableContext *context,
+ ResultRelInfo *resultRelInfo,
+ ItemPointer conflictTid,
+ bool canSetTag,
+ TupleTableSlot **returning);
static TupleTableSlot *ExecPrepareTupleRouting(ModifyTableState *mtstate,
EState *estate,
PartitionTupleRouting *proute,
@@ -1138,6 +1149,26 @@ ExecInsert(ModifyTableContext *context,
else
goto vlock;
}
+ else if (onconflict == ONCONFLICT_SELECT)
+ {
+ /*
+ * In case of ON CONFLICT DO SELECT, optionally lock the
+ * conflicting tuple, fetch it and project RETURNING on
+ * it. Be prepared to retry if fetching fails because of a
+ * concurrent UPDATE/DELETE to the conflict tuple.
+ */
+ TupleTableSlot *returning = NULL;
+
+ if (ExecOnConflictSelect(context, resultRelInfo,
+ &conflictTid, canSetTag,
+ &returning))
+ {
+ InstrCountTuples2(&mtstate->ps, 1);
+ return returning;
+ }
+ else
+ goto vlock;
+ }
else
{
/*
@@ -2676,52 +2707,26 @@ redo_act:
}
/*
- * ExecOnConflictUpdate --- execute UPDATE of INSERT ON CONFLICT DO UPDATE
- *
- * Try to lock tuple for update as part of speculative insertion. If
- * a qual originating from ON CONFLICT DO UPDATE is satisfied, update
- * (but still lock row, even though it may not satisfy estate's
- * snapshot).
- *
- * Returns true if we're done (with or without an update), or false if
- * the caller must retry the INSERT from scratch.
+ * ExecOnConflictLockRow --- lock the row for ON CONFLICT DO UPDATE/SELECT
*/
static bool
-ExecOnConflictUpdate(ModifyTableContext *context,
- ResultRelInfo *resultRelInfo,
- ItemPointer conflictTid,
- TupleTableSlot *excludedSlot,
- bool canSetTag,
- TupleTableSlot **returning)
+ExecOnConflictLockRow(ModifyTableContext *context,
+ TupleTableSlot *existing,
+ ItemPointer conflictTid,
+ Relation relation,
+ LockTupleMode lockmode,
+ bool isUpdate)
{
- ModifyTableState *mtstate = context->mtstate;
- ExprContext *econtext = mtstate->ps.ps_ExprContext;
- Relation relation = resultRelInfo->ri_RelationDesc;
- ExprState *onConflictSetWhere = resultRelInfo->ri_onConflict->oc_WhereClause;
- TupleTableSlot *existing = resultRelInfo->ri_onConflict->oc_Existing;
TM_FailureData tmfd;
- LockTupleMode lockmode;
TM_Result test;
Datum xminDatum;
TransactionId xmin;
bool isnull;
/*
- * Parse analysis should have blocked ON CONFLICT for all system
- * relations, which includes these. There's no fundamental obstacle to
- * supporting this; we'd just need to handle LOCKTAG_TUPLE like the other
- * ExecUpdate() caller.
- */
- Assert(!resultRelInfo->ri_needLockTagTuple);
-
- /* Determine lock mode to use */
- lockmode = ExecUpdateLockMode(context->estate, resultRelInfo);
-
- /*
- * Lock tuple for update. Don't follow updates when tuple cannot be
- * locked without doing so. A row locking conflict here means our
- * previous conclusion that the tuple is conclusively committed is not
- * true anymore.
+ * Don't follow updates when tuple cannot be locked without doing so. A
+ * row locking conflict here means our previous conclusion that the tuple
+ * is conclusively committed is not true anymore.
*/
test = table_tuple_lock(relation, conflictTid,
context->estate->es_snapshot,
@@ -2763,7 +2768,7 @@ ExecOnConflictUpdate(ModifyTableContext *context,
(errcode(ERRCODE_CARDINALITY_VIOLATION),
/* translator: %s is a SQL command name */
errmsg("%s command cannot affect row a second time",
- "ON CONFLICT DO UPDATE"),
+ isUpdate ? "ON CONFLICT DO UPDATE" : "ON CONFLICT DO SELECT"),
errhint("Ensure that no rows proposed for insertion within the same command have duplicate constrained values.")));
/* This shouldn't happen */
@@ -2820,6 +2825,50 @@ ExecOnConflictUpdate(ModifyTableContext *context,
}
/* Success, the tuple is locked. */
+ return true;
+}
+
+/*
+ * ExecOnConflictUpdate --- execute UPDATE of INSERT ON CONFLICT DO UPDATE
+ *
+ * Try to lock tuple for update as part of speculative insertion. If
+ * a qual originating from ON CONFLICT DO UPDATE is satisfied, update
+ * (but still lock row, even though it may not satisfy estate's
+ * snapshot).
+ *
+ * Returns true if we're done (with or without an update), or false if
+ * the caller must retry the INSERT from scratch.
+ */
+static bool
+ExecOnConflictUpdate(ModifyTableContext *context,
+ ResultRelInfo *resultRelInfo,
+ ItemPointer conflictTid,
+ TupleTableSlot *excludedSlot,
+ bool canSetTag,
+ TupleTableSlot **returning)
+{
+ ModifyTableState *mtstate = context->mtstate;
+ ExprContext *econtext = mtstate->ps.ps_ExprContext;
+ Relation relation = resultRelInfo->ri_RelationDesc;
+ ExprState *onConflictSetWhere = resultRelInfo->ri_onConflict->oc_WhereClause;
+ TupleTableSlot *existing = resultRelInfo->ri_onConflict->oc_Existing;
+ LockTupleMode lockmode;
+
+ /*
+ * Parse analysis should have blocked ON CONFLICT for all system
+ * relations, which includes these. There's no fundamental obstacle to
+ * supporting this; we'd just need to handle LOCKTAG_TUPLE like the other
+ * ExecUpdate() caller.
+ */
+ Assert(!resultRelInfo->ri_needLockTagTuple);
+
+ /* Determine lock mode to use */
+ lockmode = ExecUpdateLockMode(context->estate, resultRelInfo);
+
+ /* Lock tuple for update. */
+ if (!ExecOnConflictLockRow(context, existing, conflictTid,
+ resultRelInfo->ri_RelationDesc, lockmode, true))
+ return false;
/*
* Verify that the tuple is visible to our MVCC snapshot if the current
@@ -2910,6 +2959,133 @@ ExecOnConflictUpdate(ModifyTableContext *context,
return true;
}
+/*
+ * ExecOnConflictSelect --- execute SELECT of INSERT ON CONFLICT DO SELECT
+ *
+ * Returns true if if we're done (with or without an update), or false if the
+ * caller must retry the INSERT from scratch.
+ */
+static bool
+ExecOnConflictSelect(ModifyTableContext *context,
+ ResultRelInfo *resultRelInfo,
+ ItemPointer conflictTid,
+ bool canSetTag,
+ TupleTableSlot **rslot)
+{
+ ModifyTableState *mtstate = context->mtstate;
+ ExprContext *econtext = mtstate->ps.ps_ExprContext;
+ Relation relation = resultRelInfo->ri_RelationDesc;
+ ExprState *onConflictSelectWhere = resultRelInfo->ri_onConflict->oc_WhereClause;
+ TupleTableSlot *existing = resultRelInfo->ri_onConflict->oc_Existing;
+ LockClauseStrength lockstrength = resultRelInfo->ri_onConflict->oc_LockingStrength;
+
+ /*
+ * Parse analysis should have blocked ON CONFLICT for all system
+ * relations, which includes these. There's no fundamental obstacle to
+ * supporting this; we'd just need to handle LOCKTAG_TUPLE like the other
+ * ExecUpdate() caller.
+ */
+ Assert(!resultRelInfo->ri_needLockTagTuple);
+
+ if (lockstrength != LCS_NONE)
+ {
+ LockTupleMode lockmode;
+
+ switch (lockstrength)
+ {
+ case LCS_FORKEYSHARE:
+ lockmode = LockTupleKeyShare;
+ break;
+ case LCS_FORSHARE:
+ lockmode = LockTupleShare;
+ break;
+ case LCS_FORNOKEYUPDATE:
+ lockmode = LockTupleNoKeyExclusive;
+ break;
+ case LCS_FORUPDATE:
+ lockmode = LockTupleExclusive;
+ break;
+ default:
+ elog(ERROR, "unexpected lock strength %d", lockstrength);
+ }
+
+ if (!ExecOnConflictLockRow(context, existing, conflictTid,
+ resultRelInfo->ri_RelationDesc, lockmode, false))
+ return false;
+ }
+ else
+ {
+ if (!table_tuple_fetch_row_version(relation, conflictTid, SnapshotAny, existing))
+ return false;
+ }
+
+ /*
+ * For the same reasons as ExecOnConflictUpdate, we must verify that the
+ * tuple is visible to our snapshot.
+ */
+ ExecCheckTupleVisible(context->estate, relation, existing);
+
+ /*
+ * Make the tuple available to ExecQual and ExecProject. EXCLUDED is not
+ * used at all.
+ */
+ econtext->ecxt_scantuple = existing;
+ econtext->ecxt_innertuple = NULL;
+ econtext->ecxt_outertuple = NULL;
+
+ if (!ExecQual(onConflictSelectWhere, econtext))
+ {
+ ExecClearTuple(existing); /* see return below */
+ InstrCountFiltered1(&mtstate->ps, 1);
+ return true; /* done with the tuple */
+ }
+
+ if (resultRelInfo->ri_WithCheckOptions != NIL)
+ {
+ /*
+ * Check target's existing tuple against UPDATE-applicable USING
+ * security barrier quals (if any), enforced here as RLS checks/WCOs.
+ *
+ * The rewriter creates UPDATE RLS checks/WCOs for UPDATE security
+ * quals, and stores them as WCOs of "kind" WCO_RLS_CONFLICT_CHECK,
+ * but that's almost the extent of its special handling for ON
+ * CONFLICT DO UPDATE.
+ *
+ * The rewriter will also have associated UPDATE applicable straight
+ * RLS checks/WCOs for the benefit of the ExecUpdate() call that
+ * follows. INSERTs and UPDATEs naturally have mutually exclusive WCO
+ * kinds, so there is no danger of spurious over-enforcement in the
+ * INSERT or UPDATE path.
+ */
+ ExecWithCheckOptions(WCO_RLS_CONFLICT_CHECK, resultRelInfo,
+ existing,
+ mtstate->ps.state);
+ }
+
+ /* Parse analysis should already have disallowed this */
+ Assert(resultRelInfo->ri_projectReturning);
+
+ *rslot = ExecProcessReturning(context, resultRelInfo, CMD_INSERT,
+ existing, NULL, context->planSlot);
+
+ if (canSetTag)
+ context->estate->es_processed++;
+
+ /*
+ * Before releasing the existing tuple, make sure rslot has a local copy
+ * of any pass-by-reference values.
+ */
+ ExecMaterializeSlot(*rslot);
+
+ /*
+ * Clear out existing tuple, as there might not be another conflict among
+ * the next input rows. Don't want to hold resources till the end of the
+ * query.
+ */
+ ExecClearTuple(existing);
+ return true;
+}
+
/*
* Perform MERGE.
*/
@@ -4891,6 +5067,34 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
onconfl->oc_WhereClause = qualexpr;
}
}
+ else if (node->onConflictAction == ONCONFLICT_SELECT)
+ {
+ OnConflictSetState *onconfl = makeNode(OnConflictSetState);
+
+ /* already exists if created by RETURNING processing above */
+ if (mtstate->ps.ps_ExprContext == NULL)
+ ExecAssignExprContext(estate, &mtstate->ps);
+
+ /* create state for DO SELECT operation */
+ resultRelInfo->ri_onConflict = onconfl;
+
+ /* initialize slot for the existing tuple */
+ onconfl->oc_Existing =
+ table_slot_create(resultRelInfo->ri_RelationDesc,
+ &mtstate->ps.state->es_tupleTable);
+
+ /* initialize state to evaluate the WHERE clause, if any */
+ if (node->onConflictWhere)
+ {
+ ExprState *qualexpr;
+
+ qualexpr = ExecInitQual((List *) node->onConflictWhere,
+ &mtstate->ps);
+ onconfl->oc_WhereClause = qualexpr;
+ }
+
+ onconfl->oc_LockingStrength = node->onConflictLockingStrength;
+ }
/*
* If we have any secondary relations in an UPDATE or DELETE, they need to
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 816a2b2a576..1a97f608d4b 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -7156,6 +7156,7 @@ make_modifytable(PlannerInfo *root, Plan *subplan,
node->onConflictSet = NIL;
node->onConflictCols = NIL;
node->onConflictWhere = NULL;
+ node->onConflictLockingStrength = LCS_NONE;
node->arbiterIndexes = NIL;
node->exclRelRTI = 0;
node->exclRelTlist = NIL;
@@ -7174,6 +7175,7 @@ make_modifytable(PlannerInfo *root, Plan *subplan,
node->onConflictCols =
extract_update_targetlist_colnos(node->onConflictSet);
node->onConflictWhere = onconflict->onConflictWhere;
+ node->onConflictLockingStrength = onconflict->lockingStrength;
/*
* If a set of unique index inference elements was provided (an
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 76f58b3aca3..806d2689f20 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -684,7 +684,7 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
ListCell *icols;
ListCell *attnos;
ListCell *lc;
- bool isOnConflictUpdate;
+ bool requiresUpdatePerm;
AclMode targetPerms;
/* There can't be any outer WITH to worry about */
@@ -703,8 +703,10 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
qry->override = stmt->override;
- isOnConflictUpdate = (stmt->onConflictClause &&
- stmt->onConflictClause->action == ONCONFLICT_UPDATE);
+ requiresUpdatePerm = (stmt->onConflictClause &&
+ (stmt->onConflictClause->action == ONCONFLICT_UPDATE ||
+ (stmt->onConflictClause->action == ONCONFLICT_SELECT &&
+ stmt->onConflictClause->lockingStrength != LCS_NONE)));
/*
* We have three cases to deal with: DEFAULT VALUES (selectStmt == NULL),
@@ -754,7 +756,7 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
* to the joinlist or namespace.
*/
targetPerms = ACL_INSERT;
- if (isOnConflictUpdate)
+ if (requiresUpdatePerm)
targetPerms |= ACL_UPDATE;
qry->resultRelation = setTargetTable(pstate, stmt->relation,
false, false, targetPerms);
@@ -1061,6 +1063,12 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
false, true, true);
}
+ if (stmt->onConflictClause && stmt->onConflictClause->action == ONCONFLICT_SELECT && !stmt->returningClause)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("ON CONFLICT DO SELECT requires a RETURNING clause"),
+ parser_errposition(pstate, stmt->onConflictClause->location)));
+
/* Process ON CONFLICT, if any. */
if (stmt->onConflictClause)
qry->onConflict = transformOnConflictClause(pstate,
@@ -1287,8 +1295,15 @@ transformOnConflictClause(ParseState *pstate,
Assert((ParseNamespaceItem *) llast(pstate->p_namespace) == exclNSItem);
pstate->p_namespace = list_delete_last(pstate->p_namespace);
}
+ else if (onConflictClause->action == ONCONFLICT_SELECT)
+ {
+ onConflictWhere = transformWhereClause(pstate,
+ onConflictClause->whereClause,
+ EXPR_KIND_WHERE, "WHERE");
+
+ }
- /* Finally, build ON CONFLICT DO [NOTHING | UPDATE] expression */
+ /* Finally, build ON CONFLICT DO [NOTHING | SELECT | UPDATE] expression */
result = makeNode(OnConflictExpr);
result->action = onConflictClause->action;
@@ -1296,6 +1311,7 @@ transformOnConflictClause(ParseState *pstate,
result->arbiterWhere = arbiterWhere;
result->constraint = arbiterConstraint;
result->onConflictSet = onConflictSet;
+ result->lockingStrength = onConflictClause->lockingStrength;
result->onConflictWhere = onConflictWhere;
result->exclRelIndex = exclRelIndex;
result->exclRelTlist = exclRelTlist;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 7d99c9355c6..f1e7b65505e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -473,7 +473,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <ival> OptNoLog
%type <oncommit> OnCommitOption
-%type <ival> for_locking_strength
+%type <ival> for_locking_strength opt_for_locking_strength
%type <node> for_locking_item
%type <list> for_locking_clause opt_for_locking_clause for_locking_items
%type <list> locked_rels_list
@@ -12299,12 +12299,24 @@ insert_column_item:
;
opt_on_conflict:
+ ON CONFLICT opt_conf_expr DO SELECT opt_for_locking_strength where_clause
+ {
+ $$ = makeNode(OnConflictClause);
+ $$->action = ONCONFLICT_SELECT;
+ $$->infer = $3;
+ $$->targetList = NIL;
+ $$->lockingStrength = $6;
+ $$->whereClause = $7;
+ $$->location = @1;
+ }
+ |
ON CONFLICT opt_conf_expr DO UPDATE SET set_clause_list where_clause
{
$$ = makeNode(OnConflictClause);
$$->action = ONCONFLICT_UPDATE;
$$->infer = $3;
$$->targetList = $7;
+ $$->lockingStrength = LCS_NONE;
$$->whereClause = $8;
$$->location = @1;
}
@@ -12315,6 +12327,7 @@ opt_on_conflict:
$$->action = ONCONFLICT_NOTHING;
$$->infer = $3;
$$->targetList = NIL;
+ $$->lockingStrength = LCS_NONE;
$$->whereClause = NULL;
$$->location = @1;
}
@@ -13551,6 +13564,11 @@ for_locking_strength:
| FOR KEY SHARE { $$ = LCS_FORKEYSHARE; }
;
+opt_for_locking_strength:
+ for_locking_strength { $$ = $1; }
+ | /* EMPTY */ { $$ = LCS_NONE; }
+ ;
+
locked_rels_list:
OF qualified_name_list { $$ = $2; }
| /* EMPTY */ { $$ = NIL; }
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 2e64fcae7b2..ab16e1495a3 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -3312,6 +3312,13 @@ transformOnConflictArbiter(ParseState *pstate,
errhint("For example, ON CONFLICT (column_name)."),
parser_errposition(pstate,
exprLocation((Node *) onConflictClause))));
+ else if (onConflictClause->action == ONCONFLICT_SELECT && !infer)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("ON CONFLICT DO SELECT requires inference specification or constraint name"),
+ errhint("For example, ON CONFLICT (column_name)."),
+ parser_errposition(pstate,
+ exprLocation((Node *) onConflictClause))));
/*
* To simplify certain aspects of its design, speculative insertion into
diff --git a/src/backend/rewrite/rowsecurity.c b/src/backend/rewrite/rowsecurity.c
index 4dad384d04d..e2877faca91 100644
--- a/src/backend/rewrite/rowsecurity.c
+++ b/src/backend/rewrite/rowsecurity.c
@@ -301,11 +301,14 @@ get_row_security_policies(Query *root, RangeTblEntry *rte, int rt_index,
}
/*
- * For INSERT ... ON CONFLICT DO UPDATE we need additional policy
- * checks for the UPDATE which may be applied to the same RTE.
+ * For INSERT ... ON CONFLICT DO UPDATE and DO SELECT FOR ... we need
+ * additional policy checks for the UPDATE or locking which may be
+ * applied to the same RTE.
*/
if (commandType == CMD_INSERT &&
- root->onConflict && root->onConflict->action == ONCONFLICT_UPDATE)
+ root->onConflict && (root->onConflict->action == ONCONFLICT_UPDATE ||
+ (root->onConflict->action == ONCONFLICT_SELECT &&
+ root->onConflict->lockingStrength != LCS_NONE)))
{
List *conflict_permissive_policies;
List *conflict_restrictive_policies;
@@ -334,9 +337,9 @@ get_row_security_policies(Query *root, RangeTblEntry *rte, int rt_index,
/*
* Get and add ALL/SELECT policies, as WCO_RLS_CONFLICT_CHECK WCOs
* to ensure they are considered when taking the UPDATE path of an
- * INSERT .. ON CONFLICT DO UPDATE, if SELECT rights are required
- * for this relation, also as WCO policies, again, to avoid
- * silently dropping data. See above.
+ * INSERT .. ON CONFLICT, if SELECT rights are required for this
+ * relation, also as WCO policies, again, to avoid silently
+ * dropping data. See above.
*/
if (perminfo->requiredPerms & ACL_SELECT)
{
@@ -364,8 +367,8 @@ get_row_security_policies(Query *root, RangeTblEntry *rte, int rt_index,
/*
* Add ALL/SELECT policies as WCO_RLS_UPDATE_CHECK WCOs, to ensure
* that the final updated row is visible when taking the UPDATE
- * path of an INSERT .. ON CONFLICT DO UPDATE, if SELECT rights
- * are required for this relation.
+ * path of an INSERT .. ON CONFLICT, if SELECT rights are required
+ * for this relation.
*/
if (perminfo->requiredPerms & ACL_SELECT)
add_with_check_options(rel, rt_index,
@@ -376,6 +379,29 @@ get_row_security_policies(Query *root, RangeTblEntry *rte, int rt_index,
hasSubLinks,
true);
}
+
+ /*
+ * For INSERT ... ON CONFLICT DO SELELT we need additional policy
+ * checks for the SELECT which may be applied to the same RTE.
+ */
+ if (commandType == CMD_INSERT &&
+ root->onConflict && root->onConflict->action == ONCONFLICT_SELECT &&
+ root->onConflict->lockingStrength == LCS_NONE)
+ {
+ List *conflict_permissive_policies;
+ List *conflict_restrictive_policies;
+
+ get_policies_for_relation(rel, CMD_SELECT, user_id,
+ &conflict_permissive_policies,
+ &conflict_restrictive_policies);
+ add_with_check_options(rel, rt_index,
+ WCO_RLS_CONFLICT_CHECK,
+ conflict_permissive_policies,
+ conflict_restrictive_policies,
+ withCheckOptions,
+ hasSubLinks,
+ true);
+ }
}
/*
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index a323fa98bbb..4b31c763216 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -428,6 +428,8 @@ typedef struct OnConflictSetState
TupleTableSlot *oc_Existing; /* slot to store existing target tuple in */
TupleTableSlot *oc_ProjSlot; /* CONFLICT ... SET ... projection target */
ProjectionInfo *oc_ProjInfo; /* for ON CONFLICT DO UPDATE SET */
+ LockClauseStrength oc_LockingStrength; /* strengh of lock for ON CONFLICT
+ * DO SELECT, or LCS_NONE */
ExprState *oc_WhereClause; /* state for the WHERE clause */
} OnConflictSetState;
diff --git a/src/include/nodes/lockoptions.h b/src/include/nodes/lockoptions.h
index 0b534e30603..59434fd480e 100644
--- a/src/include/nodes/lockoptions.h
+++ b/src/include/nodes/lockoptions.h
@@ -20,7 +20,8 @@
*/
typedef enum LockClauseStrength
{
- LCS_NONE, /* no such clause - only used in PlanRowMark */
+ LCS_NONE, /* no such clause - only used in PlanRowMark
+ * and ON CONFLICT SELECT */
LCS_FORKEYSHARE, /* FOR KEY SHARE */
LCS_FORSHARE, /* FOR SHARE */
LCS_FORNOKEYUPDATE, /* FOR NO KEY UPDATE */
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 580238bfab1..2f2be070c7a 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -418,6 +418,7 @@ typedef enum OnConflictAction
ONCONFLICT_NONE, /* No "ON CONFLICT" clause */
ONCONFLICT_NOTHING, /* ON CONFLICT ... DO NOTHING */
ONCONFLICT_UPDATE, /* ON CONFLICT ... DO UPDATE */
+ ONCONFLICT_SELECT, /* ON CONFLICT ... DO SELECT */
} OnConflictAction;
/*
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 0b208f51bdd..f252e8167d5 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1630,9 +1630,11 @@ typedef struct InferClause
typedef struct OnConflictClause
{
NodeTag type;
- OnConflictAction action; /* DO NOTHING or UPDATE? */
+ OnConflictAction action; /* DO NOTHING, SELECT or UPDATE? */
InferClause *infer; /* Optional index inference clause */
List *targetList; /* the target list (of ResTarget) */
+ LockClauseStrength lockingStrength; /* strengh of lock for DO SELECT, or
+ * LCS_NONE */
Node *whereClause; /* qualifications */
ParseLoc location; /* token location, or -1 if unknown */
} OnConflictClause;
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index bf1f25c0dba..24a76545714 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -312,6 +312,8 @@ typedef struct ModifyTable
OnConflictAction onConflictAction;
/* List of ON CONFLICT arbiter index OIDs */
List *arbiterIndexes;
+ /* lock strength for ON CONFLICT SELECT */
+ LockClauseStrength onConflictLockingStrength;
/* INSERT ON CONFLICT DO UPDATE targetlist */
List *onConflictSet;
/* target column numbers for onConflictSet */
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index d0576da3e25..eefe11e7d99 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -21,6 +21,7 @@
#include "access/cmptype.h"
#include "nodes/bitmapset.h"
#include "nodes/pg_list.h"
+#include "nodes/lockoptions.h"
typedef enum OverridingKind
@@ -2358,9 +2359,15 @@ typedef struct OnConflictExpr
Node *arbiterWhere; /* unique index arbiter WHERE clause */
Oid constraint; /* pg_constraint OID for arbiter */
+ /* both ON CONFLICT SELECT and UPDATE */
+ Node *onConflictWhere; /* qualifiers to restrict SELECT/UPDATE to */
+
+ /* ON CONFLICT SELECT */
+ LockClauseStrength lockingStrength; /* strengh of lock for DO SELECT, or
+ * LCS_NONE */
+
/* ON CONFLICT UPDATE */
List *onConflictSet; /* List of ON CONFLICT SET TargetEntrys */
- Node *onConflictWhere; /* qualifiers to restrict UPDATE to */
int exclRelIndex; /* RT index of 'excluded' relation */
List *exclRelTlist; /* tlist of the EXCLUDED pseudo relation */
} OnConflictExpr;
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index fdd0f6c8f25..2edf04c78f3 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -249,6 +249,68 @@ insert into insertconflicttest values (2, 'Orange') on conflict (key, key, key)
insert into insertconflicttest
values (1, 'Apple'), (2, 'Orange')
on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
+-- DO SELECT
+delete from insertconflicttest where fruit = 'Apple';
+insert into insertconflicttest values (1, 'Apple') on conflict (key) do select; -- fails
+ERROR: ON CONFLICT DO SELECT requires a RETURNING clause
+LINE 1: ...nsert into insertconflicttest values (1, 'Apple') on conflic...
+ ^
+insert into insertconflicttest values (1, 'Apple') on conflict (key) do select returning *;
+ key | fruit
+-----+-------
+ 1 | Apple
+(1 row)
+
+insert into insertconflicttest values (1, 'Apple') on conflict (key) do select returning *;
+ key | fruit
+-----+-------
+ 1 | Apple
+(1 row)
+
+insert into insertconflicttest values (1, 'Apple') on conflict (key) do select where fruit <> 'Apple' returning *;
+ key | fruit
+-----+-------
+(0 rows)
+
+delete from insertconflicttest where fruit = 'Apple';
+insert into insertconflicttest values (1, 'Apple') on conflict (key) do select for update returning *;
+ key | fruit
+-----+-------
+ 1 | Apple
+(1 row)
+
+insert into insertconflicttest values (1, 'Apple') on conflict (key) do select for update returning *;
+ key | fruit
+-----+-------
+ 1 | Apple
+(1 row)
+
+insert into insertconflicttest values (1, 'Apple') on conflict (key) do select for update where fruit <> 'Apple' returning *;
+ key | fruit
+-----+-------
+(0 rows)
+
+insert into insertconflicttest as ict values (3, 'Pear') on conflict (key) do select for update returning old.*, new.*, ict.*;
+ key | fruit | key | fruit | key | fruit
+-----+-------+-----+-------+-----+-------
+ | | 3 | Pear | 3 | Pear
+(1 row)
+
+insert into insertconflicttest as ict values (3, 'Banana') on conflict (key) do select for update returning old.*, new.*, ict.*;
+ key | fruit | key | fruit | key | fruit
+-----+-------+-----+-------+-----+-------
+ 3 | Pear | | | 3 | Pear
+(1 row)
+
+explain (costs off) insert into insertconflicttest values (1, 'Apple') on conflict (key) do select for key share returning *;
+ QUERY PLAN
+---------------------------------------------
+ Insert on insertconflicttest
+ Conflict Resolution: SELECT FOR KEY SHARE
+ Conflict Arbiter Indexes: key_index
+ -> Result
+(4 rows)
+
-- Give good diagnostic message when EXCLUDED.* spuriously referenced from
-- RETURNING:
insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit;
@@ -269,26 +331,26 @@ LINE 1: ... 'Apple') on conflict (key) do update set fruit = excluded.f...
^
HINT: Perhaps you meant to reference the column "excluded.fruit".
-- inference fails:
-insert into insertconflicttest values (3, 'Kiwi') on conflict (key, fruit) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (4, 'Kiwi') on conflict (key, fruit) do update set fruit = excluded.fruit;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
-insert into insertconflicttest values (4, 'Mango') on conflict (fruit, key) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (5, 'Mango') on conflict (fruit, key) do update set fruit = excluded.fruit;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
-insert into insertconflicttest values (5, 'Lemon') on conflict (fruit) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (6, 'Lemon') on conflict (fruit) do update set fruit = excluded.fruit;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
-insert into insertconflicttest values (6, 'Passionfruit') on conflict (lower(fruit)) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (7, 'Passionfruit') on conflict (lower(fruit)) do update set fruit = excluded.fruit;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
-- Check the target relation can be aliased
-insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (key) do update set fruit = excluded.fruit; -- ok, no reference to target table
-insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (key) do update set fruit = ict.fruit; -- ok, alias
-insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (key) do update set fruit = insertconflicttest.fruit; -- error, references aliased away name
+insert into insertconflicttest AS ict values (7, 'Passionfruit') on conflict (key) do update set fruit = excluded.fruit; -- ok, no reference to target table
+insert into insertconflicttest AS ict values (7, 'Passionfruit') on conflict (key) do update set fruit = ict.fruit; -- ok, alias
+insert into insertconflicttest AS ict values (7, 'Passionfruit') on conflict (key) do update set fruit = insertconflicttest.fruit; -- error, references aliased away name
ERROR: invalid reference to FROM-clause entry for table "insertconflicttest"
LINE 1: ...onfruit') on conflict (key) do update set fruit = insertconf...
^
HINT: Perhaps you meant to reference the table alias "ict".
-- Check helpful hint when qualifying set column with target table
-insert into insertconflicttest values (3, 'Kiwi') on conflict (key, fruit) do update set insertconflicttest.fruit = 'Mango';
+insert into insertconflicttest values (4, 'Kiwi') on conflict (key, fruit) do update set insertconflicttest.fruit = 'Mango';
ERROR: column "insertconflicttest" of relation "insertconflicttest" does not exist
-LINE 1: ...3, 'Kiwi') on conflict (key, fruit) do update set insertconf...
+LINE 1: ...4, 'Kiwi') on conflict (key, fruit) do update set insertconf...
^
HINT: SET target columns cannot be qualified with the relation name.
drop index key_index;
@@ -297,16 +359,16 @@ drop index key_index;
--
create unique index comp_key_index on insertconflicttest(key, fruit);
-- inference succeeds:
-insert into insertconflicttest values (7, 'Raspberry') on conflict (key, fruit) do update set fruit = excluded.fruit;
-insert into insertconflicttest values (8, 'Lime') on conflict (fruit, key) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (8, 'Raspberry') on conflict (key, fruit) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (9, 'Lime') on conflict (fruit, key) do update set fruit = excluded.fruit;
-- inference fails:
-insert into insertconflicttest values (9, 'Banana') on conflict (key) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (10, 'Banana') on conflict (key) do update set fruit = excluded.fruit;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
-insert into insertconflicttest values (10, 'Blueberry') on conflict (key, key, key) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (11, 'Blueberry') on conflict (key, key, key) do update set fruit = excluded.fruit;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
-insert into insertconflicttest values (11, 'Cherry') on conflict (key, lower(fruit)) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (12, 'Cherry') on conflict (key, lower(fruit)) do update set fruit = excluded.fruit;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
-insert into insertconflicttest values (12, 'Date') on conflict (lower(fruit), key) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (13, 'Date') on conflict (lower(fruit), key) do update set fruit = excluded.fruit;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
drop index comp_key_index;
--
@@ -315,17 +377,17 @@ drop index comp_key_index;
create unique index part_comp_key_index on insertconflicttest(key, fruit) where key < 5;
create unique index expr_part_comp_key_index on insertconflicttest(key, lower(fruit)) where key < 5;
-- inference fails:
-insert into insertconflicttest values (13, 'Grape') on conflict (key, fruit) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (14, 'Grape') on conflict (key, fruit) do update set fruit = excluded.fruit;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
-insert into insertconflicttest values (14, 'Raisin') on conflict (fruit, key) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (15, 'Raisin') on conflict (fruit, key) do update set fruit = excluded.fruit;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
-insert into insertconflicttest values (15, 'Cranberry') on conflict (key) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (16, 'Cranberry') on conflict (key) do update set fruit = excluded.fruit;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
-insert into insertconflicttest values (16, 'Melon') on conflict (key, key, key) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (17, 'Melon') on conflict (key, key, key) do update set fruit = excluded.fruit;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
-insert into insertconflicttest values (17, 'Mulberry') on conflict (key, lower(fruit)) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (18, 'Mulberry') on conflict (key, lower(fruit)) do update set fruit = excluded.fruit;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
-insert into insertconflicttest values (18, 'Pineapple') on conflict (lower(fruit), key) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (19, 'Pineapple') on conflict (lower(fruit), key) do update set fruit = excluded.fruit;
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
drop index part_comp_key_index;
drop index expr_part_comp_key_index;
@@ -735,13 +797,58 @@ insert into selfconflict values (6,1), (6,2) on conflict(f1) do update set f2 =
ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.
commit;
+begin transaction isolation level read committed;
+insert into selfconflict values (7,1), (7,2) on conflict(f1) do select returning *;
+ f1 | f2
+----+----
+ 7 | 1
+ 7 | 1
+(2 rows)
+
+commit;
+begin transaction isolation level repeatable read;
+insert into selfconflict values (8,1), (8,2) on conflict(f1) do select returning *;
+ f1 | f2
+----+----
+ 8 | 1
+ 8 | 1
+(2 rows)
+
+commit;
+begin transaction isolation level serializable;
+insert into selfconflict values (9,1), (9,2) on conflict(f1) do select returning *;
+ f1 | f2
+----+----
+ 9 | 1
+ 9 | 1
+(2 rows)
+
+commit;
+begin transaction isolation level read committed;
+insert into selfconflict values (10,1), (10,2) on conflict(f1) do select for update returning *;
+ERROR: ON CONFLICT DO SELECT command cannot affect row a second time
+HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.
+commit;
+begin transaction isolation level repeatable read;
+insert into selfconflict values (11,1), (11,2) on conflict(f1) do select for update returning *;
+ERROR: ON CONFLICT DO SELECT command cannot affect row a second time
+HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.
+commit;
+begin transaction isolation level serializable;
+insert into selfconflict values (12,1), (12,2) on conflict(f1) do select for update returning *;
+ERROR: ON CONFLICT DO SELECT command cannot affect row a second time
+HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.
+commit;
select * from selfconflict;
f1 | f2
----+----
1 | 1
2 | 1
3 | 1
-(3 rows)
+ 7 | 1
+ 8 | 1
+ 9 | 1
+(6 rows)
drop table selfconflict;
-- check ON CONFLICT handling with partitioned tables
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index 549c46452ec..b80b7dae91a 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -101,6 +101,21 @@ insert into insertconflicttest
values (1, 'Apple'), (2, 'Orange')
on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
+-- DO SELECT
+delete from insertconflicttest where fruit = 'Apple';
+insert into insertconflicttest values (1, 'Apple') on conflict (key) do select; -- fails
+insert into insertconflicttest values (1, 'Apple') on conflict (key) do select returning *;
+insert into insertconflicttest values (1, 'Apple') on conflict (key) do select returning *;
+insert into insertconflicttest values (1, 'Apple') on conflict (key) do select where fruit <> 'Apple' returning *;
+delete from insertconflicttest where fruit = 'Apple';
+insert into insertconflicttest values (1, 'Apple') on conflict (key) do select for update returning *;
+insert into insertconflicttest values (1, 'Apple') on conflict (key) do select for update returning *;
+insert into insertconflicttest values (1, 'Apple') on conflict (key) do select for update where fruit <> 'Apple' returning *;
+insert into insertconflicttest as ict values (3, 'Pear') on conflict (key) do select for update returning old.*, new.*, ict.*;
+insert into insertconflicttest as ict values (3, 'Banana') on conflict (key) do select for update returning old.*, new.*, ict.*;
+
+explain (costs off) insert into insertconflicttest values (1, 'Apple') on conflict (key) do select for key share returning *;
+
-- Give good diagnostic message when EXCLUDED.* spuriously referenced from
-- RETURNING:
insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit;
@@ -112,18 +127,18 @@ insert into insertconflicttest values (1, 'Apple') on conflict (keyy) do update
insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruitt;
-- inference fails:
-insert into insertconflicttest values (3, 'Kiwi') on conflict (key, fruit) do update set fruit = excluded.fruit;
-insert into insertconflicttest values (4, 'Mango') on conflict (fruit, key) do update set fruit = excluded.fruit;
-insert into insertconflicttest values (5, 'Lemon') on conflict (fruit) do update set fruit = excluded.fruit;
-insert into insertconflicttest values (6, 'Passionfruit') on conflict (lower(fruit)) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (4, 'Kiwi') on conflict (key, fruit) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (5, 'Mango') on conflict (fruit, key) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (6, 'Lemon') on conflict (fruit) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (7, 'Passionfruit') on conflict (lower(fruit)) do update set fruit = excluded.fruit;
-- Check the target relation can be aliased
-insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (key) do update set fruit = excluded.fruit; -- ok, no reference to target table
-insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (key) do update set fruit = ict.fruit; -- ok, alias
-insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (key) do update set fruit = insertconflicttest.fruit; -- error, references aliased away name
+insert into insertconflicttest AS ict values (7, 'Passionfruit') on conflict (key) do update set fruit = excluded.fruit; -- ok, no reference to target table
+insert into insertconflicttest AS ict values (7, 'Passionfruit') on conflict (key) do update set fruit = ict.fruit; -- ok, alias
+insert into insertconflicttest AS ict values (7, 'Passionfruit') on conflict (key) do update set fruit = insertconflicttest.fruit; -- error, references aliased away name
-- Check helpful hint when qualifying set column with target table
-insert into insertconflicttest values (3, 'Kiwi') on conflict (key, fruit) do update set insertconflicttest.fruit = 'Mango';
+insert into insertconflicttest values (4, 'Kiwi') on conflict (key, fruit) do update set insertconflicttest.fruit = 'Mango';
drop index key_index;
@@ -133,14 +148,14 @@ drop index key_index;
create unique index comp_key_index on insertconflicttest(key, fruit);
-- inference succeeds:
-insert into insertconflicttest values (7, 'Raspberry') on conflict (key, fruit) do update set fruit = excluded.fruit;
-insert into insertconflicttest values (8, 'Lime') on conflict (fruit, key) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (8, 'Raspberry') on conflict (key, fruit) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (9, 'Lime') on conflict (fruit, key) do update set fruit = excluded.fruit;
-- inference fails:
-insert into insertconflicttest values (9, 'Banana') on conflict (key) do update set fruit = excluded.fruit;
-insert into insertconflicttest values (10, 'Blueberry') on conflict (key, key, key) do update set fruit = excluded.fruit;
-insert into insertconflicttest values (11, 'Cherry') on conflict (key, lower(fruit)) do update set fruit = excluded.fruit;
-insert into insertconflicttest values (12, 'Date') on conflict (lower(fruit), key) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (10, 'Banana') on conflict (key) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (11, 'Blueberry') on conflict (key, key, key) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (12, 'Cherry') on conflict (key, lower(fruit)) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (13, 'Date') on conflict (lower(fruit), key) do update set fruit = excluded.fruit;
drop index comp_key_index;
@@ -151,12 +166,12 @@ create unique index part_comp_key_index on insertconflicttest(key, fruit) where
create unique index expr_part_comp_key_index on insertconflicttest(key, lower(fruit)) where key < 5;
-- inference fails:
-insert into insertconflicttest values (13, 'Grape') on conflict (key, fruit) do update set fruit = excluded.fruit;
-insert into insertconflicttest values (14, 'Raisin') on conflict (fruit, key) do update set fruit = excluded.fruit;
-insert into insertconflicttest values (15, 'Cranberry') on conflict (key) do update set fruit = excluded.fruit;
-insert into insertconflicttest values (16, 'Melon') on conflict (key, key, key) do update set fruit = excluded.fruit;
-insert into insertconflicttest values (17, 'Mulberry') on conflict (key, lower(fruit)) do update set fruit = excluded.fruit;
-insert into insertconflicttest values (18, 'Pineapple') on conflict (lower(fruit), key) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (14, 'Grape') on conflict (key, fruit) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (15, 'Raisin') on conflict (fruit, key) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (16, 'Cranberry') on conflict (key) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (17, 'Melon') on conflict (key, key, key) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (18, 'Mulberry') on conflict (key, lower(fruit)) do update set fruit = excluded.fruit;
+insert into insertconflicttest values (19, 'Pineapple') on conflict (lower(fruit), key) do update set fruit = excluded.fruit;
drop index part_comp_key_index;
drop index expr_part_comp_key_index;
@@ -454,6 +469,30 @@ begin transaction isolation level serializable;
insert into selfconflict values (6,1), (6,2) on conflict(f1) do update set f2 = 0;
commit;
+begin transaction isolation level read committed;
+insert into selfconflict values (7,1), (7,2) on conflict(f1) do select returning *;
+commit;
+
+begin transaction isolation level repeatable read;
+insert into selfconflict values (8,1), (8,2) on conflict(f1) do select returning *;
+commit;
+
+begin transaction isolation level serializable;
+insert into selfconflict values (9,1), (9,2) on conflict(f1) do select returning *;
+commit;
+
+begin transaction isolation level read committed;
+insert into selfconflict values (10,1), (10,2) on conflict(f1) do select for update returning *;
+commit;
+
+begin transaction isolation level repeatable read;
+insert into selfconflict values (11,1), (11,2) on conflict(f1) do select for update returning *;
+commit;
+
+begin transaction isolation level serializable;
+insert into selfconflict values (12,1), (12,2) on conflict(f1) do select for update returning *;
+commit;
+
select * from selfconflict;
drop table selfconflict;
--
2.47.2
