Hi,
Here is an updated version of the patch which fixes a few small bugs,
including making sure it checks the update permission plus a bug found
by Joel Jacobsson when it was called by SPI.
Andreas
From 21ccc735d9d261278564a98d8d2d8137485cd758 Mon Sep 17 00:00:00 2001
From: Andreas Karlsson <andr...@proxel.se>
Date: Mon, 18 Nov 2024 00:29:15 +0100
Subject: [PATCH v3] 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 | 255 +++++++++++++++---
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/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 | 97 ++++++-
src/test/regress/sql/insert_conflict.sql | 37 +++
15 files changed, 459 insertions(+), 56 deletions(-)
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index 6f0adee1a12..63ffb0d141c 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -36,6 +36,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> )
@@ -87,18 +88,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 a3f1d53d7a5..012f51d1491 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -4601,10 +4601,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 1161520f76b..d295f685fd6 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -138,12 +138,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,
@@ -1067,6 +1078,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
{
/*
@@ -2510,52 +2541,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,
@@ -2597,7 +2602,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 */
@@ -2654,6 +2659,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
@@ -2737,6 +2786,110 @@ 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 */
+ }
+
+ /* Parse analysis should already have disallowed this */
+ Assert(resultRelInfo->ri_projectReturning);
+
+ *rslot = ExecProcessReturning(resultRelInfo, existing, 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.
*/
@@ -4639,6 +4792,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 178c572b021..d934c911fca 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -7144,6 +7144,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;
@@ -7162,6 +7163,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 3864a675d2a..9455115c003 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->returningList)
+ 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,
@@ -1288,8 +1296,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;
@@ -1297,6 +1312,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 67eb96396af..6d64d427316 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -465,7 +465,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
@@ -12261,12 +12261,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;
}
@@ -12277,6 +12289,7 @@ opt_on_conflict:
$$->action = ONCONFLICT_NOTHING;
$$->infer = $3;
$$->targetList = NIL;
+ $$->lockingStrength = LCS_NONE;
$$->whereClause = NULL;
$$->location = @1;
}
@@ -13476,6 +13489,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 979926b6052..215d4cd46d7 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -3310,6 +3310,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/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 182a6956bb0..636d35bc250 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -412,6 +412,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 044ef42ee10..ffa90f9a71f 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 c1ab3d1358d..cec8754bdc5 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 0f9462493e3..ef202e017f5 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1621,9 +1621,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 52f29bcdb69..4fe22d11ddb 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -247,6 +247,8 @@ typedef struct ModifyTable
List *arbiterIndexes; /* List of ON CONFLICT arbiter index OIDs */
List *onConflictSet; /* INSERT ON CONFLICT DO UPDATE targetlist */
List *onConflictCols; /* target column numbers for onConflictSet */
+ LockClauseStrength onConflictLockingStrength; /* lock strength for ON
+ * CONFLICT SELECT */
Node *onConflictWhere; /* WHERE for ON CONFLICT UPDATE */
Index exclRelRTI; /* RTI of the EXCLUDED pseudo relation */
List *exclRelTlist; /* tlist of the EXCLUDED pseudo relation */
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index b0ef1952e8f..5b1f3d76a98 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -20,6 +20,7 @@
#include "access/attnum.h"
#include "nodes/bitmapset.h"
#include "nodes/pg_list.h"
+#include "nodes/lockoptions.h"
typedef enum OverridingKind
@@ -2329,9 +2330,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..f8f150d89e4 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -249,6 +249,56 @@ 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)
+
+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;
@@ -735,13 +785,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..6a3228a5836 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -101,6 +101,19 @@ 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 *;
+
+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;
@@ -454,6 +467,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.45.2
