Re: ON CONFLICT DO SELECT (take 3)
Started by Viktor Holmbergabout 2 months ago3 messages
Here are some updates that needed to be done after the improvements to the RLS docs / tests in 7dc4fa & 2e8424.
Show quoted text
On 10 Nov 2025, at 11:18, Viktor Holmberg <v@viktorh.net> wrote:
Ah, must’ve been that I added the previous thread for referene on the commitfest entry. Thanks for sorting that out.
Looking forward to your review!/Viktor
On 10 Nov 2025 at 10:21 +0100, Dean Rasheed <dean.a.rasheed@gmail.com>, wrote:On Tue, 7 Oct 2025 at 12:57, Viktor Holmberg <v@viktorh.net> wrote:
This patch implements ON CONFLICT DO SELECT.
I’ve kept the patches proposed there separate, in case any of the people involved back then would like to pick it up again.Grateful in advance to anyone who can help reviewing!
Thanks for picking this up. I haven't looked at it yet, but I'm
planning to do so.In the meantime, I noticed that the cfbot didn't pick up your latest
patches, and is still running the v7 patches, presumably based on
their names. So here they are as v8 (rebased, plus a couple of
indentation fixes in 0003, but no other changes).Regards,
Dean
Attachments:
v10-0001-Add-support-for-ON-CONFLICT-DO-SELECT-FOR.patchapplication/octet-streamDownload
From a5fc489de149919411e2801839b2106e54ebfd20 Mon Sep 17 00:00:00 2001
From: Andreas Karlsson <andreas@proxel.se>
Date: Mon, 18 Nov 2024 00:29:15 +0100
Subject: [PATCH v10 1/3] 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 0598b8dea34..76117c684c5 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 7e699f8595e..9f1b90a0eb2 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -4670,10 +4670,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
+ {
+ 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;
+ default: /* 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 4c5647ac38a..6b9f17366bd 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -146,12 +146,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,
@@ -1159,6 +1170,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
{
/*
@@ -2699,52 +2730,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,
@@ -2786,7 +2791,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 */
@@ -2843,6 +2848,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
@@ -2933,6 +2982,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.
*/
@@ -5061,6 +5237,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 8af091ba647..52839dbbf2d 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -7039,6 +7039,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;
@@ -7057,6 +7058,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 3b392b084ad..af50d705091 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -649,7 +649,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 */
@@ -668,8 +668,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),
@@ -719,7 +721,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);
@@ -1026,6 +1028,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,
@@ -1252,8 +1260,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;
@@ -1261,6 +1276,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 c3a0a354a9c..316587a8420 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -480,7 +480,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
@@ -12439,12 +12439,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;
}
@@ -12455,6 +12467,7 @@ opt_on_conflict:
$$->action = ONCONFLICT_NOTHING;
$$->infer = $3;
$$->targetList = NIL;
+ $$->lockingStrength = LCS_NONE;
$$->whereClause = NULL;
$$->location = @1;
}
@@ -13684,6 +13697,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 ca26f6f61f2..c5c4273208a 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -3375,6 +3375,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 18ae8f0d4bb..727807abed7 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -433,6 +433,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 fb3957e75e5..691b5d385d6 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -428,6 +428,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 d14294a4ece..03cd0638750 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1652,9 +1652,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 c4393a94321..43c4b62838e 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -362,6 +362,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 1b4436f2ff6..0af96f1bf15 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
@@ -2378,9 +2379,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 db668474684..f3d2e1da802 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.48.1
v10-0002-Review-comments-for-ON-CONFLICT-DO-SELECT.patchapplication/octet-streamDownload
From 2f3c9f8a26ed86f9f3a16a2bb1765e244963aa5e Mon Sep 17 00:00:00 2001
From: Dean Rasheed <dean.a.rasheed@gmail.com>
Date: Mon, 31 Mar 2025 15:20:47 +0100
Subject: [PATCH v10 2/3] Review comments for ON CONFLICT DO SELECT.
---
doc/src/sgml/ref/create_policy.sgml | 16 +++
src/backend/commands/explain.c | 11 +-
src/backend/executor/nodeModifyTable.c | 61 ++++----
src/backend/optimizer/plan/setrefs.c | 3 +-
src/backend/parser/analyze.c | 60 ++++----
src/backend/rewrite/rewriteHandler.c | 13 ++
src/backend/rewrite/rowsecurity.c | 131 ++++++++----------
src/backend/utils/adt/ruleutils.c | 69 +++++----
src/include/nodes/plannodes.h | 2 +-
src/test/regress/expected/insert_conflict.out | 40 +++++-
src/test/regress/expected/rules.out | 55 ++++++++
src/test/regress/sql/insert_conflict.sql | 10 +-
src/test/regress/sql/rules.sql | 26 ++++
13 files changed, 336 insertions(+), 161 deletions(-)
diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml
index 42d43ad7bf4..6e05808e110 100644
--- a/doc/src/sgml/ref/create_policy.sgml
+++ b/doc/src/sgml/ref/create_policy.sgml
@@ -606,6 +606,22 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
<entry>—</entry>
<entry>Check existing row</entry>
</row>
+ <row>
+ <entry><command>ON CONFLICT DO SELECT</command></entry>
+ <entry>Existing & new rows</entry>
+ <entry>—</entry>
+ <entry>—</entry>
+ <entry>—</entry>
+ <entry>—</entry>
+ </row>
+ <row>
+ <entry><command>ON CONFLICT DO SELECT FOR UPDATE/SHARE</command></entry>
+ <entry>Existing & new rows</entry>
+ <entry>—</entry>
+ <entry>Existing row</entry>
+ <entry>—</entry>
+ <entry>—</entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 9f1b90a0eb2..1a575cc96e8 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -4670,7 +4670,7 @@ show_modifytable_info(ModifyTableState *mtstate, List *ancestors,
if (node->onConflictAction != ONCONFLICT_NONE)
{
- const char *resolution;
+ const char *resolution = NULL;
if (node->onConflictAction == ONCONFLICT_NOTHING)
resolution = "NOTHING";
@@ -4678,6 +4678,7 @@ show_modifytable_info(ModifyTableState *mtstate, List *ancestors,
resolution = "UPDATE";
else
{
+ Assert(node->onConflictAction == ONCONFLICT_SELECT);
switch (node->onConflictLockingStrength)
{
case LCS_NONE:
@@ -4692,9 +4693,13 @@ show_modifytable_info(ModifyTableState *mtstate, List *ancestors,
case LCS_FORNOKEYUPDATE:
resolution = "SELECT FOR NO KEY UPDATE";
break;
- default: /* LCS_FORUPDATE */
+ case LCS_FORUPDATE:
resolution = "SELECT FOR UPDATE";
break;
+ default:
+ elog(ERROR, "unrecognized LockClauseStrength %d",
+ (int) node->onConflictLockingStrength);
+ break;
}
}
@@ -4707,7 +4712,7 @@ show_modifytable_info(ModifyTableState *mtstate, List *ancestors,
if (idxNames)
ExplainPropertyList("Conflict Arbiter Indexes", idxNames, es);
- /* ON CONFLICT DO UPDATE WHERE qual is specially displayed */
+ /* ON CONFLICT DO UPDATE/SELECT WHERE qual is specially displayed */
if (node->onConflictWhere)
{
show_upper_qual((List *) node->onConflictWhere, "Conflict Filter",
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 6b9f17366bd..80e2650366c 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -161,6 +161,7 @@ static bool ExecOnConflictUpdate(ModifyTableContext *context,
static bool ExecOnConflictSelect(ModifyTableContext *context,
ResultRelInfo *resultRelInfo,
ItemPointer conflictTid,
+ TupleTableSlot *excludedSlot,
bool canSetTag,
TupleTableSlot **returning);
static TupleTableSlot *ExecPrepareTupleRouting(ModifyTableState *mtstate,
@@ -1175,13 +1176,13 @@ ExecInsert(ModifyTableContext *context,
/*
* 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
+ * it. Be prepared to retry if locking fails because of a
* concurrent UPDATE/DELETE to the conflict tuple.
*/
TupleTableSlot *returning = NULL;
if (ExecOnConflictSelect(context, resultRelInfo,
- &conflictTid, canSetTag,
+ &conflictTid, slot, canSetTag,
&returning))
{
InstrCountTuples2(&mtstate->ps, 1);
@@ -2731,6 +2732,12 @@ redo_act:
/*
* ExecOnConflictLockRow --- lock the row for ON CONFLICT DO UPDATE/SELECT
+ *
+ * Try to lock tuple for update as part of speculative insertion for ON
+ * CONFLICT DO UPDATE or ON CONFLICT DO SELECT FOR UPDATE/SHARE.
+ *
+ * Returns true if the row is successfully locked, or false if the caller must
+ * retry the INSERT from scratch.
*/
static bool
ExecOnConflictLockRow(ModifyTableContext *context,
@@ -2888,7 +2895,7 @@ ExecOnConflictUpdate(ModifyTableContext *context,
/* Determine lock mode to use */
lockmode = ExecUpdateLockMode(context->estate, resultRelInfo);
- /* Lock tuple for update. */
+ /* Lock tuple for update */
if (!ExecOnConflictLockRow(context, existing, conflictTid,
resultRelInfo->ri_RelationDesc, lockmode, true))
return false;
@@ -2933,11 +2940,12 @@ ExecOnConflictUpdate(ModifyTableContext *context,
* 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.
+ * quals, and stores them as WCOs of "kind" WCO_RLS_CONFLICT_CHECK. If
+ * SELECT rights are required on the target table, the rewriter also
+ * adds SELECT RLS checks/WCOs for SELECT security quals, using WCOs
+ * of the same kind, so this check enforces them too.
*
- * The rewriter will also have associated UPDATE applicable straight
+ * 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
@@ -2985,13 +2993,18 @@ ExecOnConflictUpdate(ModifyTableContext *context,
/*
* 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
+ * If SELECT FOR UPDATE/SHARE is specified, try to lock tuple as part of
+ * speculative insertion. If a qual originating from ON CONFLICT DO UPDATE is
+ * satisfied, select the row.
+ *
+ * Returns true if if we're done (with or without a select), or false if the
* caller must retry the INSERT from scratch.
*/
static bool
ExecOnConflictSelect(ModifyTableContext *context,
ResultRelInfo *resultRelInfo,
ItemPointer conflictTid,
+ TupleTableSlot *excludedSlot,
bool canSetTag,
TupleTableSlot **rslot)
{
@@ -3049,11 +3062,13 @@ ExecOnConflictSelect(ModifyTableContext *context,
ExecCheckTupleVisible(context->estate, relation, existing);
/*
- * Make the tuple available to ExecQual and ExecProject. EXCLUDED is not
- * used at all.
+ * Make tuple and any needed join variables available to ExecQual. The
+ * EXCLUDED tuple is installed in ecxt_innertuple, while the target's
+ * existing tuple is installed in the scantuple. EXCLUDED has been made
+ * to reference INNER_VAR in setrefs.c, but there is no other redirection.
*/
econtext->ecxt_scantuple = existing;
- econtext->ecxt_innertuple = NULL;
+ econtext->ecxt_innertuple = excludedSlot;
econtext->ecxt_outertuple = NULL;
if (!ExecQual(onConflictSelectWhere, econtext))
@@ -3066,19 +3081,15 @@ ExecOnConflictSelect(ModifyTableContext *context,
if (resultRelInfo->ri_WithCheckOptions != NIL)
{
/*
- * Check target's existing tuple against UPDATE-applicable USING
+ * Check target's existing tuple against SELECT-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.
+ * The rewriter creates SELECT RLS checks/WCOs for SELECT security
+ * quals, and stores them as WCOs of "kind" WCO_RLS_CONFLICT_CHECK. If
+ * FOR UPDATE/SHARE was specified, UPDATE rights are required on the
+ * target table, and the rewriter also adds UPDATE RLS checks/WCOs for
+ * UPDATE security quals, using WCOs of the same kind, so this check
+ * enforces them too.
*/
ExecWithCheckOptions(WCO_RLS_CONFLICT_CHECK, resultRelInfo,
existing,
@@ -3088,8 +3099,9 @@ ExecOnConflictSelect(ModifyTableContext *context,
/* Parse analysis should already have disallowed this */
Assert(resultRelInfo->ri_projectReturning);
- *rslot = ExecProcessReturning(context, resultRelInfo, CMD_INSERT,
- existing, NULL, context->planSlot);
+ /* Process RETURNING like an UPDATE that didn't change anything */
+ *rslot = ExecProcessReturning(context, resultRelInfo, CMD_UPDATE,
+ existing, existing, context->planSlot);
if (canSetTag)
context->estate->es_processed++;
@@ -3106,6 +3118,7 @@ ExecOnConflictSelect(ModifyTableContext *context,
* query.
*/
ExecClearTuple(existing);
+
return true;
}
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index ccdc9bc264a..b4d9a998e07 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -1140,7 +1140,8 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset)
* those are already used by RETURNING and it seems better to
* be non-conflicting.
*/
- if (splan->onConflictSet)
+ if (splan->onConflictAction == ONCONFLICT_UPDATE ||
+ splan->onConflictAction == ONCONFLICT_SELECT)
{
indexed_tlist *itlist;
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index af50d705091..a41516ee962 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1028,11 +1028,14 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
false, true, true);
}
- if (stmt->onConflictClause && stmt->onConflictClause->action == ONCONFLICT_SELECT && !stmt->returningClause)
+ /* ON CONFLICT DO SELECT requires a RETURNING clause */
+ 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)));
+ 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)
@@ -1192,12 +1195,13 @@ transformOnConflictClause(ParseState *pstate,
OnConflictExpr *result;
/*
- * If this is ON CONFLICT ... UPDATE, first create the range table entry
- * for the EXCLUDED pseudo relation, so that that will be present while
- * processing arbiter expressions. (You can't actually reference it from
- * there, but this provides a useful error message if you try.)
+ * If this is ON CONFLICT ... UPDATE/SELECT, first create the range table
+ * entry for the EXCLUDED pseudo relation, so that that will be present
+ * while processing arbiter expressions. (You can't actually reference it
+ * from there, but this provides a useful error message if you try.)
*/
- if (onConflictClause->action == ONCONFLICT_UPDATE)
+ if (onConflictClause->action == ONCONFLICT_UPDATE ||
+ onConflictClause->action == ONCONFLICT_SELECT)
{
Relation targetrel = pstate->p_target_relation;
RangeTblEntry *exclRte;
@@ -1226,27 +1230,28 @@ transformOnConflictClause(ParseState *pstate,
transformOnConflictArbiter(pstate, onConflictClause, &arbiterElems,
&arbiterWhere, &arbiterConstraint);
- /* Process DO UPDATE */
- if (onConflictClause->action == ONCONFLICT_UPDATE)
+ /* Process DO UPDATE/SELECT */
+ if (onConflictClause->action == ONCONFLICT_UPDATE ||
+ onConflictClause->action == ONCONFLICT_SELECT)
{
- /*
- * Expressions in the UPDATE targetlist need to be handled like UPDATE
- * not INSERT. We don't need to save/restore this because all INSERT
- * expressions have been parsed already.
- */
- pstate->p_is_insert = false;
-
/*
* Add the EXCLUDED pseudo relation to the query namespace, making it
- * available in the UPDATE subexpressions.
+ * available in the UPDATE/SELECT subexpressions.
*/
addNSItemToQuery(pstate, exclNSItem, false, true, true);
- /*
- * Now transform the UPDATE subexpressions.
- */
- onConflictSet =
- transformUpdateTargetList(pstate, onConflictClause->targetList);
+ if (onConflictClause->action == ONCONFLICT_UPDATE)
+ {
+ /*
+ * Expressions in the UPDATE targetlist need to be handled like
+ * UPDATE not INSERT. We don't need to save/restore this because
+ * all INSERT expressions have been parsed already.
+ */
+ pstate->p_is_insert = false;
+
+ onConflictSet =
+ transformUpdateTargetList(pstate, onConflictClause->targetList);
+ }
onConflictWhere = transformWhereClause(pstate,
onConflictClause->whereClause,
@@ -1260,13 +1265,6 @@ 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 | SELECT | UPDATE] expression */
result = makeNode(OnConflictExpr);
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index adc9e7600e1..f3cd32b7222 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -655,6 +655,19 @@ rewriteRuleAction(Query *parsetree,
rule_action = sub_action;
}
+ /*
+ * If rule_action is INSERT .. ON CONFLICT DO SELECT, the parser should
+ * have verified that it has a RETURNING clause, but we must also check
+ * that the triggering query has a RETURNING clause.
+ */
+ if (rule_action->onConflict &&
+ rule_action->onConflict->action == ONCONFLICT_SELECT &&
+ (!rule_action->returningList || !parsetree->returningList))
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("ON CONFLICT DO SELECT requires a RETURNING clause"),
+ errdetail("A rule action is INSERT ... ON CONFLICT DO SELECT, which requires a RETURNING clause"));
+
/*
* If rule_action has a RETURNING clause, then either throw it away if the
* triggering query has no RETURNING clause, or rewrite it to emit what
diff --git a/src/backend/rewrite/rowsecurity.c b/src/backend/rewrite/rowsecurity.c
index e2877faca91..c9bdff6f8f5 100644
--- a/src/backend/rewrite/rowsecurity.c
+++ b/src/backend/rewrite/rowsecurity.c
@@ -301,45 +301,50 @@ get_row_security_policies(Query *root, RangeTblEntry *rte, int rt_index,
}
/*
- * 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.
+ * For INSERT ... ON CONFLICT DO UPDATE/SELECT we need additional
+ * policy checks for the UPDATE/SELECT which may be applied to the
+ * same RTE.
*/
- if (commandType == CMD_INSERT &&
- root->onConflict && (root->onConflict->action == ONCONFLICT_UPDATE ||
- (root->onConflict->action == ONCONFLICT_SELECT &&
- root->onConflict->lockingStrength != LCS_NONE)))
+ if (commandType == CMD_INSERT && root->onConflict &&
+ (root->onConflict->action == ONCONFLICT_UPDATE ||
+ root->onConflict->action == ONCONFLICT_SELECT))
{
- List *conflict_permissive_policies;
- List *conflict_restrictive_policies;
+ List *conflict_permissive_policies = NIL;
+ List *conflict_restrictive_policies = NIL;
List *conflict_select_permissive_policies = NIL;
List *conflict_select_restrictive_policies = NIL;
- /* Get the policies that apply to the auxiliary UPDATE */
- get_policies_for_relation(rel, CMD_UPDATE, user_id,
- &conflict_permissive_policies,
- &conflict_restrictive_policies);
-
- /*
- * Enforce the USING clauses of the UPDATE policies using WCOs
- * rather than security quals. This ensures that an error is
- * raised if the conflicting row cannot be updated due to RLS,
- * rather than the change being silently dropped.
- */
- add_with_check_options(rel, rt_index,
- WCO_RLS_CONFLICT_CHECK,
- conflict_permissive_policies,
- conflict_restrictive_policies,
- withCheckOptions,
- hasSubLinks,
- true);
+ if (perminfo->requiredPerms & ACL_UPDATE)
+ {
+ /*
+ * Get the policies that apply to the auxiliary UPDATE or
+ * SELECT FOR SHARE/UDPATE.
+ */
+ get_policies_for_relation(rel, CMD_UPDATE, user_id,
+ &conflict_permissive_policies,
+ &conflict_restrictive_policies);
+
+ /*
+ * Enforce the USING clauses of the UPDATE policies using WCOs
+ * rather than security quals. This ensures that an error is
+ * raised if the conflicting row cannot be updated/locked due
+ * to RLS, rather than the change being silently dropped.
+ */
+ add_with_check_options(rel, rt_index,
+ WCO_RLS_CONFLICT_CHECK,
+ conflict_permissive_policies,
+ conflict_restrictive_policies,
+ withCheckOptions,
+ hasSubLinks,
+ true);
+ }
/*
* 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, if SELECT rights are required for this
- * relation, also as WCO policies, again, to avoid silently
- * dropping data. See above.
+ * to ensure they are considered when taking the UPDATE/SELECT
+ * path of an 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)
{
@@ -355,52 +360,36 @@ get_row_security_policies(Query *root, RangeTblEntry *rte, int rt_index,
true);
}
- /* Enforce the WITH CHECK clauses of the UPDATE policies */
- add_with_check_options(rel, rt_index,
- WCO_RLS_UPDATE_CHECK,
- conflict_permissive_policies,
- conflict_restrictive_policies,
- withCheckOptions,
- hasSubLinks,
- false);
-
/*
- * 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, if SELECT rights are required
- * for this relation.
+ * For INSERT .. ON CONFLICT DO UPDATE, add additional policies to
+ * be checked when the auxiliary UPDATE is executed.
*/
- if (perminfo->requiredPerms & ACL_SELECT)
+ if (root->onConflict->action == ONCONFLICT_UPDATE)
+ {
+ /* Enforce the WITH CHECK clauses of the UPDATE policies */
add_with_check_options(rel, rt_index,
WCO_RLS_UPDATE_CHECK,
- conflict_select_permissive_policies,
- conflict_select_restrictive_policies,
+ conflict_permissive_policies,
+ conflict_restrictive_policies,
withCheckOptions,
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);
+ false);
+
+ /*
+ * 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, if SELECT
+ * rights are required for this relation.
+ */
+ if (perminfo->requiredPerms & ACL_SELECT)
+ add_with_check_options(rel, rt_index,
+ WCO_RLS_UPDATE_CHECK,
+ conflict_select_permissive_policies,
+ conflict_select_restrictive_policies,
+ withCheckOptions,
+ hasSubLinks,
+ true);
+ }
}
}
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 556ab057e5a..82e467a0b2f 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -426,6 +426,7 @@ static void get_update_query_targetlist_def(Query *query, List *targetList,
static void get_delete_query_def(Query *query, deparse_context *context);
static void get_merge_query_def(Query *query, deparse_context *context);
static void get_utility_query_def(Query *query, deparse_context *context);
+static char *get_lock_clause_strength(LockClauseStrength strength);
static void get_basic_select_query(Query *query, deparse_context *context);
static void get_target_list(List *targetList, deparse_context *context);
static void get_returning_clause(Query *query, deparse_context *context);
@@ -5997,30 +5998,9 @@ get_select_query_def(Query *query, deparse_context *context)
if (rc->pushedDown)
continue;
- switch (rc->strength)
- {
- case LCS_NONE:
- /* we intentionally throw an error for LCS_NONE */
- elog(ERROR, "unrecognized LockClauseStrength %d",
- (int) rc->strength);
- break;
- case LCS_FORKEYSHARE:
- appendContextKeyword(context, " FOR KEY SHARE",
- -PRETTYINDENT_STD, PRETTYINDENT_STD, 0);
- break;
- case LCS_FORSHARE:
- appendContextKeyword(context, " FOR SHARE",
- -PRETTYINDENT_STD, PRETTYINDENT_STD, 0);
- break;
- case LCS_FORNOKEYUPDATE:
- appendContextKeyword(context, " FOR NO KEY UPDATE",
- -PRETTYINDENT_STD, PRETTYINDENT_STD, 0);
- break;
- case LCS_FORUPDATE:
- appendContextKeyword(context, " FOR UPDATE",
- -PRETTYINDENT_STD, PRETTYINDENT_STD, 0);
- break;
- }
+ appendContextKeyword(context,
+ get_lock_clause_strength(rc->strength),
+ -PRETTYINDENT_STD, PRETTYINDENT_STD, 0);
appendStringInfo(buf, " OF %s",
quote_identifier(get_rtable_name(rc->rti,
@@ -6033,6 +6013,28 @@ get_select_query_def(Query *query, deparse_context *context)
}
}
+static char *
+get_lock_clause_strength(LockClauseStrength strength)
+{
+ switch (strength)
+ {
+ case LCS_NONE:
+ /* we intentionally throw an error for LCS_NONE */
+ elog(ERROR, "unrecognized LockClauseStrength %d",
+ (int) strength);
+ break;
+ case LCS_FORKEYSHARE:
+ return " FOR KEY SHARE";
+ case LCS_FORSHARE:
+ return " FOR SHARE";
+ case LCS_FORNOKEYUPDATE:
+ return " FOR NO KEY UPDATE";
+ case LCS_FORUPDATE:
+ return " FOR UPDATE";
+ }
+ return NULL; /* keep compiler quiet */
+}
+
/*
* Detect whether query looks like SELECT ... FROM VALUES(),
* with no need to rename the output columns of the VALUES RTE.
@@ -7125,7 +7127,7 @@ get_insert_query_def(Query *query, deparse_context *context)
{
appendStringInfoString(buf, " DO NOTHING");
}
- else
+ else if (confl->action == ONCONFLICT_UPDATE)
{
appendStringInfoString(buf, " DO UPDATE SET ");
/* Deparse targetlist */
@@ -7140,6 +7142,23 @@ get_insert_query_def(Query *query, deparse_context *context)
get_rule_expr(confl->onConflictWhere, context, false);
}
}
+ else
+ {
+ Assert(confl->action == ONCONFLICT_SELECT);
+ appendStringInfoString(buf, " DO SELECT");
+
+ /* Add FOR [KEY] UPDATE/SHARE clause if present */
+ if (confl->lockingStrength != LCS_NONE)
+ appendStringInfoString(buf, get_lock_clause_strength(confl->lockingStrength));
+
+ /* Add a WHERE clause if given */
+ if (confl->onConflictWhere != NULL)
+ {
+ appendContextKeyword(context, " WHERE ",
+ -PRETTYINDENT_STD, PRETTYINDENT_STD, 1);
+ get_rule_expr(confl->onConflictWhere, context, false);
+ }
+ }
}
/* Add RETURNING if present */
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 43c4b62838e..bdbbebd49fd 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -368,7 +368,7 @@ typedef struct ModifyTable
List *onConflictSet;
/* target column numbers for onConflictSet */
List *onConflictCols;
- /* WHERE for ON CONFLICT UPDATE */
+ /* WHERE for ON CONFLICT UPDATE/SELECT */
Node *onConflictWhere;
/* RTI of the EXCLUDED pseudo relation */
Index exclRelRTI;
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index f3d2e1da802..d226c472340 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -267,11 +267,28 @@ insert into insertconflicttest values (1, 'Apple') on conflict (key) do select r
1 | Apple
(1 row)
-insert into insertconflicttest values (1, 'Apple') on conflict (key) do select where fruit <> 'Apple' returning *;
+insert into insertconflicttest as i values (1, 'Apple') on conflict (key) do select where i.fruit = 'Apple' returning *;
+ key | fruit
+-----+-------
+ 1 | Apple
+(1 row)
+
+insert into insertconflicttest as i values (1, 'Apple') on conflict (key) do select where i.fruit = 'Orange' returning *;
+ key | fruit
+-----+-------
+(0 rows)
+
+insert into insertconflicttest as i values (1, 'Orange') on conflict (key) do select where excluded.fruit = 'Apple' returning *;
key | fruit
-----+-------
(0 rows)
+insert into insertconflicttest as i values (1, 'Orange') on conflict (key) do select where excluded.fruit = 'Orange' returning *;
+ key | fruit
+-----+-------
+ 1 | Apple
+(1 row)
+
delete from insertconflicttest where fruit = 'Apple';
insert into insertconflicttest values (1, 'Apple') on conflict (key) do select for update returning *;
key | fruit
@@ -285,11 +302,28 @@ insert into insertconflicttest values (1, 'Apple') on conflict (key) do select f
1 | Apple
(1 row)
-insert into insertconflicttest values (1, 'Apple') on conflict (key) do select for update where fruit <> 'Apple' returning *;
+insert into insertconflicttest as i values (1, 'Apple') on conflict (key) do select for update where i.fruit = 'Apple' returning *;
+ key | fruit
+-----+-------
+ 1 | Apple
+(1 row)
+
+insert into insertconflicttest as i values (1, 'Apple') on conflict (key) do select for update where i.fruit = 'Orange' returning *;
+ key | fruit
+-----+-------
+(0 rows)
+
+insert into insertconflicttest as i values (1, 'Orange') on conflict (key) do select for update where excluded.fruit = 'Apple' returning *;
key | fruit
-----+-------
(0 rows)
+insert into insertconflicttest as i values (1, 'Orange') on conflict (key) do select for update where excluded.fruit = 'Orange' returning *;
+ key | fruit
+-----+-------
+ 1 | Apple
+(1 row)
+
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
-----+-------+-----+-------+-----+-------
@@ -299,7 +333,7 @@ insert into insertconflicttest as ict values (3, 'Pear') on conflict (key) do se
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
+ 3 | Pear | 3 | Pear | 3 | Pear
(1 row)
explain (costs off) insert into insertconflicttest values (1, 'Apple') on conflict (key) do select for key share returning *;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 7c52181cbcb..be5d59b08ca 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -3562,6 +3562,61 @@ SELECT * FROM hat_data WHERE hat_name IN ('h8', 'h9', 'h7') ORDER BY hat_name;
(3 rows)
DROP RULE hat_upsert ON hats;
+-- DO SELECT with a WHERE clause
+CREATE RULE hat_confsel AS ON INSERT TO hats
+ DO INSTEAD
+ INSERT INTO hat_data VALUES (
+ NEW.hat_name,
+ NEW.hat_color)
+ ON CONFLICT (hat_name)
+ DO SELECT FOR UPDATE
+ WHERE excluded.hat_color <> 'forbidden' AND hat_data.* != excluded.*
+ RETURNING *;
+SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename;
+ definition
+--------------------------------------------------------------------------------------
+ CREATE RULE hat_confsel AS +
+ ON INSERT TO public.hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) +
+ VALUES (new.hat_name, new.hat_color) ON CONFLICT(hat_name) DO SELECT FOR UPDATE +
+ WHERE ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*))+
+ RETURNING hat_data.hat_name, +
+ hat_data.hat_color;
+(1 row)
+
+-- fails without RETURNING
+INSERT INTO hats VALUES ('h7', 'blue');
+ERROR: ON CONFLICT DO SELECT requires a RETURNING clause
+DETAIL: A rule action is INSERT ... ON CONFLICT DO SELECT, which requires a RETURNING clause
+-- works (returns conflicts)
+EXPLAIN (costs off)
+INSERT INTO hats VALUES ('h7', 'blue') RETURNING *;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
+ Insert on hat_data
+ Conflict Resolution: SELECT FOR UPDATE
+ Conflict Arbiter Indexes: hat_data_unique_idx
+ Conflict Filter: ((excluded.hat_color <> 'forbidden'::bpchar) AND (hat_data.* <> excluded.*))
+ -> Result
+(5 rows)
+
+INSERT INTO hats VALUES ('h7', 'blue') RETURNING *;
+ hat_name | hat_color
+------------+------------
+ h7 | black
+(1 row)
+
+-- conflicts excluded by WHERE clause
+INSERT INTO hats VALUES ('h7', 'forbidden') RETURNING *;
+ hat_name | hat_color
+----------+-----------
+(0 rows)
+
+INSERT INTO hats VALUES ('h7', 'black') RETURNING *;
+ hat_name | hat_color
+----------+-----------
+(0 rows)
+
+DROP RULE hat_confsel ON hats;
drop table hats;
drop table hat_data;
-- test for pg_get_functiondef properly regurgitating SET parameters
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index b80b7dae91a..72b8147f849 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -106,11 +106,17 @@ 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 *;
+insert into insertconflicttest as i values (1, 'Apple') on conflict (key) do select where i.fruit = 'Apple' returning *;
+insert into insertconflicttest as i values (1, 'Apple') on conflict (key) do select where i.fruit = 'Orange' returning *;
+insert into insertconflicttest as i values (1, 'Orange') on conflict (key) do select where excluded.fruit = 'Apple' returning *;
+insert into insertconflicttest as i values (1, 'Orange') on conflict (key) do select where excluded.fruit = 'Orange' 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 i values (1, 'Apple') on conflict (key) do select for update where i.fruit = 'Apple' returning *;
+insert into insertconflicttest as i values (1, 'Apple') on conflict (key) do select for update where i.fruit = 'Orange' returning *;
+insert into insertconflicttest as i values (1, 'Orange') on conflict (key) do select for update where excluded.fruit = 'Apple' returning *;
+insert into insertconflicttest as i values (1, 'Orange') on conflict (key) do select for update where excluded.fruit = 'Orange' 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.*;
diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql
index 3f240bec7b0..40f5c16e540 100644
--- a/src/test/regress/sql/rules.sql
+++ b/src/test/regress/sql/rules.sql
@@ -1205,6 +1205,32 @@ SELECT * FROM hat_data WHERE hat_name IN ('h8', 'h9', 'h7') ORDER BY hat_name;
DROP RULE hat_upsert ON hats;
+-- DO SELECT with a WHERE clause
+CREATE RULE hat_confsel AS ON INSERT TO hats
+ DO INSTEAD
+ INSERT INTO hat_data VALUES (
+ NEW.hat_name,
+ NEW.hat_color)
+ ON CONFLICT (hat_name)
+ DO SELECT FOR UPDATE
+ WHERE excluded.hat_color <> 'forbidden' AND hat_data.* != excluded.*
+ RETURNING *;
+SELECT definition FROM pg_rules WHERE tablename = 'hats' ORDER BY rulename;
+
+-- fails without RETURNING
+INSERT INTO hats VALUES ('h7', 'blue');
+
+-- works (returns conflicts)
+EXPLAIN (costs off)
+INSERT INTO hats VALUES ('h7', 'blue') RETURNING *;
+INSERT INTO hats VALUES ('h7', 'blue') RETURNING *;
+
+-- conflicts excluded by WHERE clause
+INSERT INTO hats VALUES ('h7', 'forbidden') RETURNING *;
+INSERT INTO hats VALUES ('h7', 'black') RETURNING *;
+
+DROP RULE hat_confsel ON hats;
+
drop table hats;
drop table hat_data;
--
2.48.1
v10-0003-Remaning-fixes-for-ON-CONFLICT-DO-SELECT.patchapplication/octet-streamDownload
From d9aa5c34b1c5fcc6e1d4e3da6f0cb53ab0147a41 Mon Sep 17 00:00:00 2001
From: Viktor Holmberg <v@viktorh.net>
Date: Thu, 4 Sep 2025 21:22:45 +0200
Subject: [PATCH v10 3/3] Remaning fixes for ON CONFLICT DO SELECT
---
doc/src/sgml/dml.sgml | 3 +-
doc/src/sgml/ref/create_policy.sgml | 32 ++--
doc/src/sgml/ref/insert.sgml | 89 +++++++++--
src/backend/executor/execPartition.c | 74 +++++++++-
src/backend/executor/nodeModifyTable.c | 6 +-
src/include/nodes/execnodes.h | 14 +-
src/include/nodes/parsenodes.h | 2 +-
src/include/nodes/primnodes.h | 2 +-
.../expected/insert-conflict-do-select.out | 138 ++++++++++++++++++
src/test/isolation/isolation_schedule | 1 +
.../specs/insert-conflict-do-select.spec | 53 +++++++
src/test/regress/expected/insert_conflict.out | 91 +++++++++++-
src/test/regress/expected/rowsecurity.out | 42 ++++++
src/test/regress/sql/insert_conflict.sql | 28 +++-
src/test/regress/sql/rowsecurity.sql | 14 ++
src/tools/pgindent/typedefs.list | 2 +-
16 files changed, 545 insertions(+), 46 deletions(-)
create mode 100644 src/test/isolation/expected/insert-conflict-do-select.out
create mode 100644 src/test/isolation/specs/insert-conflict-do-select.spec
diff --git a/doc/src/sgml/dml.sgml b/doc/src/sgml/dml.sgml
index 61c64cf6c49..7e5cce0bff0 100644
--- a/doc/src/sgml/dml.sgml
+++ b/doc/src/sgml/dml.sgml
@@ -387,7 +387,8 @@ UPDATE products SET price = price * 1.10
<command>INSERT</command> with an
<link linkend="sql-on-conflict"><literal>ON CONFLICT DO UPDATE</literal></link>
clause, the old values will be non-<literal>NULL</literal> for conflicting
- rows. Similarly, if a <command>DELETE</command> is turned into an
+ rows. Similarly, in an <command>INSERT</command> with an
+ <literal>ON CONFLICT DO SELECT</literal> clause, you can look at the old values to determine if your query inserted a row or not. If a <command>DELETE</command> is turned into an
<command>UPDATE</command> by a <link linkend="sql-createrule">rewrite rule</link>,
the new values may be non-<literal>NULL</literal>.
</para>
diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml
index 6e05808e110..09fd26f7b7d 100644
--- a/doc/src/sgml/ref/create_policy.sgml
+++ b/doc/src/sgml/ref/create_policy.sgml
@@ -571,6 +571,22 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
Check new row <footnoteref linkend="rls-on-conflict-update-priv"/>
</entry>
<entry>—</entry>
+ </row>
+ <row>
+ <entry><command>ON CONFLICT DO SELECT</command></entry>
+ <entry>Check existing rows</entry>
+ <entry>—</entry>
+ <entry>—</entry>
+ <entry>—</entry>
+ <entry>—</entry>
+ </row>
+ <row>
+ <entry><command>ON CONFLICT DO SELECT FOR UPDATE/SHARE</command></entry>
+ <entry>Check existing rows</entry>
+ <entry>—</entry>
+ <entry>Existing row</entry>
+ <entry>—</entry>
+ <entry>—</entry>
</row>
<row>
<entry><command>MERGE</command></entry>
@@ -606,22 +622,6 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
<entry>—</entry>
<entry>Check existing row</entry>
</row>
- <row>
- <entry><command>ON CONFLICT DO SELECT</command></entry>
- <entry>Existing & new rows</entry>
- <entry>—</entry>
- <entry>—</entry>
- <entry>—</entry>
- <entry>—</entry>
- </row>
- <row>
- <entry><command>ON CONFLICT DO SELECT FOR UPDATE/SHARE</command></entry>
- <entry>Existing & new rows</entry>
- <entry>—</entry>
- <entry>Existing row</entry>
- <entry>—</entry>
- <entry>—</entry>
- </row>
</tbody>
</tgroup>
</table>
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index 76117c684c5..7b883b799b5 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -37,7 +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 SELECT [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } ] [ WHERE <replaceable class="parameter">condition</replaceable> ]
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> )
@@ -113,7 +113,8 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
You must have <literal>INSERT</literal> privilege on a table in
order to insert into it. If <literal>ON CONFLICT DO UPDATE</literal> is
present, <literal>UPDATE</literal> privilege on the table is also
- required.
+ required. If <literal>ON CONFLICT DO SELECT</literal> is present,
+ <literal>SELECT</literal> privilege on the table is required.
</para>
<para>
@@ -125,6 +126,9 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
also requires <literal>SELECT</literal> privilege on any column whose
values are read in the <literal>ON CONFLICT DO UPDATE</literal>
expressions or <replaceable>condition</replaceable>.
+ For <literal>ON CONFLICT DO SELECT</literal>, <literal>SELECT</literal>
+ privilege is required on any column whose values are read in the
+ <replaceable>condition</replaceable>.
</para>
<para>
@@ -348,7 +352,10 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
For a simple <command>INSERT</command>, all old values will be
<literal>NULL</literal>. However, for an <command>INSERT</command>
with an <literal>ON CONFLICT DO UPDATE</literal> clause, the old
- values may be non-<literal>NULL</literal>.
+ values may be non-<literal>NULL</literal>. Similarly, for
+ <literal>ON CONFLICT DO SELECT</literal>, both old and new values
+ represent the existing row (since no modification takes place),
+ so old and new will be identical for conflicting rows.
</para>
</listitem>
</varlistentry>
@@ -384,6 +391,9 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
a row as its alternative action. <literal>ON CONFLICT DO
UPDATE</literal> updates the existing row that conflicts with the
row proposed for insertion as its alternative action.
+ <literal>ON CONFLICT DO SELECT</literal> returns the existing row
+ that conflicts with the row proposed for insertion, optionally
+ with row-level locking.
</para>
<para>
@@ -415,6 +425,13 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
INSERT</quote>.
</para>
+ <para>
+ <literal>ON CONFLICT DO SELECT</literal> similarly allows an atomic
+ <command>INSERT</command> or <command>SELECT</command> outcome. This
+ is also known as a <firstterm>idempotent insert</firstterm> or
+ <firstterm>get or create</firstterm>.
+ </para>
+
<variablelist>
<varlistentry>
<term><replaceable class="parameter">conflict_target</replaceable></term>
@@ -428,7 +445,8 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
specify a <parameter>conflict_target</parameter>; when
omitted, conflicts with all usable constraints (and unique
indexes) are handled. For <literal>ON CONFLICT DO
- UPDATE</literal>, a <parameter>conflict_target</parameter>
+ UPDATE</literal> and <literal>ON CONFLICT DO SELECT</literal>,
+ a <parameter>conflict_target</parameter>
<emphasis>must</emphasis> be provided.
</para>
</listitem>
@@ -440,10 +458,11 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
<para>
<parameter>conflict_action</parameter> specifies an
alternative <literal>ON CONFLICT</literal> action. It can be
- either <literal>DO NOTHING</literal>, or a <literal>DO
+ either <literal>DO NOTHING</literal>, a <literal>DO
UPDATE</literal> clause specifying the exact details of the
<literal>UPDATE</literal> action to be performed in case of a
- conflict. The <literal>SET</literal> and
+ conflict, or a <literal>DO SELECT</literal> clause that returns
+ the existing conflicting row. The <literal>SET</literal> and
<literal>WHERE</literal> clauses in <literal>ON CONFLICT DO
UPDATE</literal> have access to the existing row using the
table's name (or an alias), and to the row proposed for insertion
@@ -452,6 +471,18 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
target table where corresponding <varname>excluded</varname>
columns are read.
</para>
+ <para>
+ For <literal>ON CONFLICT DO SELECT</literal>, the optional
+ <literal>WHERE</literal> clause has access to the existing row
+ using the table's name (or an alias), and to the row proposed for
+ insertion using the special <varname>excluded</varname> table.
+ Only rows for which the <literal>WHERE</literal> clause returns
+ <literal>true</literal> will be returned. An optional
+ <literal>FOR UPDATE</literal>, <literal>FOR NO KEY UPDATE</literal>,
+ <literal>FOR SHARE</literal>, or <literal>FOR KEY SHARE</literal>
+ clause can be specified to lock the existing row using the
+ specified lock strength.
+ </para>
<para>
Note that the effects of all per-row <literal>BEFORE
INSERT</literal> triggers are reflected in
@@ -554,12 +585,14 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
<listitem>
<para>
An expression that returns a value of type
- <type>boolean</type>. Only rows for which this expression
- returns <literal>true</literal> will be updated, although all
- rows will be locked when the <literal>ON CONFLICT DO UPDATE</literal>
- action is taken. Note that
- <replaceable>condition</replaceable> is evaluated last, after
- a conflict has been identified as a candidate to update.
+ <type>boolean</type>. For <literal>ON CONFLICT DO UPDATE</literal>,
+ only rows for which this expression returns <literal>true</literal>
+ will be updated, although all rows will be locked when the
+ <literal>ON CONFLICT DO UPDATE</literal> action is taken.
+ For <literal>ON CONFLICT DO SELECT</literal>, only rows for which
+ this expression returns <literal>true</literal> will be returned.
+ Note that <replaceable>condition</replaceable> is evaluated last, after
+ a conflict has been identified as a candidate to update or select.
</para>
</listitem>
</varlistentry>
@@ -623,7 +656,7 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</replaceable>
</screen>
The <replaceable class="parameter">count</replaceable> is the number of
- rows inserted or updated. <replaceable>oid</replaceable> is always 0 (it
+ rows inserted, updated, or selected for return. <replaceable>oid</replaceable> is always 0 (it
used to be the <acronym>OID</acronym> assigned to the inserted row if
<replaceable>count</replaceable> was exactly one and the target table was
declared <literal>WITH OIDS</literal> and 0 otherwise, but creating a table
@@ -809,6 +842,36 @@ INSERT INTO distributors AS d (did, dname) VALUES (8, 'Anvil Distribution')
-- index to arbitrate taking the DO NOTHING action)
INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')
ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING;
+</programlisting>
+ </para>
+ <para>
+ Insert new distributor if possible, otherwise return the existing
+ distributor row. Example assumes a unique index has been defined
+ that constrains values appearing in the <literal>did</literal> column.
+ This is useful for get-or-create patterns:
+<programlisting>
+INSERT INTO distributors (did, dname) VALUES (11, 'Global Electronics')
+ ON CONFLICT (did) DO SELECT
+ RETURNING *;
+</programlisting>
+ </para>
+ <para>
+ Insert a new distributor if the name doesn't match, otherwise return
+ the existing row. This example uses the <varname>excluded</varname>
+ table in the WHERE clause to filter results:
+<programlisting>
+INSERT INTO distributors (did, dname) VALUES (12, 'Micro Devices Inc')
+ ON CONFLICT (did) DO SELECT WHERE dname = EXCLUDED.dname
+ RETURNING *;
+</programlisting>
+ </para>
+ <para>
+ Insert a new distributor or return and lock the existing row for update.
+ This is useful when you need to ensure exclusive access to the row:
+<programlisting>
+INSERT INTO distributors (did, dname) VALUES (13, 'Advanced Systems')
+ ON CONFLICT (did) DO SELECT FOR UPDATE
+ RETURNING *;
</programlisting>
</para>
<para>
diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.c
index aa12e9ad2ea..a8f7d1dc5bd 100644
--- a/src/backend/executor/execPartition.c
+++ b/src/backend/executor/execPartition.c
@@ -735,7 +735,7 @@ ExecInitPartitionInfo(ModifyTableState *mtstate, EState *estate,
*/
if (node->onConflictAction == ONCONFLICT_UPDATE)
{
- OnConflictSetState *onconfl = makeNode(OnConflictSetState);
+ OnConflictActionState *onconfl = makeNode(OnConflictActionState);
TupleConversionMap *map;
map = ExecGetRootToChildMap(leaf_part_rri, estate);
@@ -859,6 +859,78 @@ ExecInitPartitionInfo(ModifyTableState *mtstate, EState *estate,
}
}
}
+ else if (node->onConflictAction == ONCONFLICT_SELECT)
+ {
+ OnConflictActionState *onconfl = makeNode(OnConflictActionState);
+ TupleConversionMap *map;
+
+ map = ExecGetRootToChildMap(leaf_part_rri, estate);
+ Assert(rootResultRelInfo->ri_onConflict != NULL);
+
+ leaf_part_rri->ri_onConflict = onconfl;
+
+ onconfl->oc_LockingStrength =
+ rootResultRelInfo->ri_onConflict->oc_LockingStrength;
+
+ /*
+ * Need a separate existing slot for each partition, as the
+ * partition could be of a different AM, even if the tuple
+ * descriptors match.
+ */
+ onconfl->oc_Existing =
+ table_slot_create(leaf_part_rri->ri_RelationDesc,
+ &mtstate->ps.state->es_tupleTable);
+
+ /*
+ * If the partition's tuple descriptor matches exactly the root
+ * parent (the common case), we can re-use the parent's ON
+ * CONFLICT DO SELECT state. Otherwise, we need to remap the
+ * WHERE clause for this partition's layout.
+ */
+ if (map == NULL)
+ {
+ /*
+ * It's safe to reuse these from the partition root, as we
+ * only process one tuple at a time (therefore we won't
+ * overwrite needed data in slots), and the WHERE clause
+ * doesn't store state / is independent of the underlying
+ * storage.
+ */
+ onconfl->oc_WhereClause =
+ rootResultRelInfo->ri_onConflict->oc_WhereClause;
+ }
+ else if (node->onConflictWhere)
+ {
+ /*
+ * Map the WHERE clause, if it exists.
+ */
+ List *clause;
+
+ if (part_attmap == NULL)
+ part_attmap =
+ build_attrmap_by_name(RelationGetDescr(partrel),
+ RelationGetDescr(firstResultRel),
+ false);
+
+ clause = copyObject((List *) node->onConflictWhere);
+ clause = (List *)
+ map_variable_attnos((Node *) clause,
+ INNER_VAR, 0,
+ part_attmap,
+ RelationGetForm(partrel)->reltype,
+ &found_whole_row);
+ /* We ignore the value of found_whole_row. */
+ clause = (List *)
+ map_variable_attnos((Node *) clause,
+ firstVarno, 0,
+ part_attmap,
+ RelationGetForm(partrel)->reltype,
+ &found_whole_row);
+ /* We ignore the value of found_whole_row. */
+ onconfl->oc_WhereClause =
+ ExecInitQual(clause, &mtstate->ps);
+ }
+ }
}
/*
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 80e2650366c..54a9d8920c5 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -2997,7 +2997,7 @@ ExecOnConflictUpdate(ModifyTableContext *context,
* speculative insertion. If a qual originating from ON CONFLICT DO UPDATE is
* satisfied, select the row.
*
- * Returns true if if we're done (with or without a select), or false if the
+ * Returns true if we're done (with or without a select), or false if the
* caller must retry the INSERT from scratch.
*/
static bool
@@ -5201,7 +5201,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
*/
if (node->onConflictAction == ONCONFLICT_UPDATE)
{
- OnConflictSetState *onconfl = makeNode(OnConflictSetState);
+ OnConflictActionState *onconfl = makeNode(OnConflictActionState);
ExprContext *econtext;
TupleDesc relationDesc;
@@ -5252,7 +5252,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
}
else if (node->onConflictAction == ONCONFLICT_SELECT)
{
- OnConflictSetState *onconfl = makeNode(OnConflictSetState);
+ OnConflictActionState *onconfl = makeNode(OnConflictActionState);
/* already exists if created by RETURNING processing above */
if (mtstate->ps.ps_ExprContext == NULL)
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 727807abed7..297969efad3 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -422,21 +422,21 @@ typedef struct JunkFilter
} JunkFilter;
/*
- * OnConflictSetState
+ * OnConflictActionState
*
- * Executor state of an ON CONFLICT DO UPDATE operation.
+ * Executor state of an ON CONFLICT DO UPDATE/SELECT operation.
*/
-typedef struct OnConflictSetState
+typedef struct OnConflictActionState
{
NodeTag type;
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 */
+ LockClauseStrength oc_LockingStrength; /* strength of lock for ON
+ * CONFLICT DO SELECT, or LCS_NONE */
ExprState *oc_WhereClause; /* state for the WHERE clause */
-} OnConflictSetState;
+} OnConflictActionState;
/* ----------------
* MergeActionState information
@@ -582,7 +582,7 @@ typedef struct ResultRelInfo
List *ri_onConflictArbiterIndexes;
/* ON CONFLICT evaluation state */
- OnConflictSetState *ri_onConflict;
+ OnConflictActionState *ri_onConflict;
/* for MERGE, lists of MergeActionState (one per MergeMatchKind) */
List *ri_MergeActions[NUM_MERGE_MATCH_KINDS];
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 03cd0638750..31c73abe87b 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1655,7 +1655,7 @@ typedef struct OnConflictClause
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
+ LockClauseStrength lockingStrength; /* strength of lock for DO SELECT, or
* LCS_NONE */
Node *whereClause; /* qualifications */
ParseLoc location; /* token location, or -1 if unknown */
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 0af96f1bf15..d87686de000 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -2383,7 +2383,7 @@ typedef struct OnConflictExpr
Node *onConflictWhere; /* qualifiers to restrict SELECT/UPDATE to */
/* ON CONFLICT SELECT */
- LockClauseStrength lockingStrength; /* strengh of lock for DO SELECT, or
+ LockClauseStrength lockingStrength; /* strength of lock for DO SELECT, or
* LCS_NONE */
/* ON CONFLICT UPDATE */
diff --git a/src/test/isolation/expected/insert-conflict-do-select.out b/src/test/isolation/expected/insert-conflict-do-select.out
new file mode 100644
index 00000000000..bccfd47dcfb
--- /dev/null
+++ b/src/test/isolation/expected/insert-conflict-do-select.out
@@ -0,0 +1,138 @@
+Parsed test spec with 2 sessions
+
+starting permutation: insert1 insert2 c1 select2 c2
+step insert1: INSERT INTO doselect(key, val) VALUES(1, 'insert1') ON CONFLICT (key) DO SELECT RETURNING *;
+key|val
+---+--------
+ 1|original
+(1 row)
+
+step insert2: INSERT INTO doselect(key, val) VALUES(1, 'insert2') ON CONFLICT (key) DO SELECT RETURNING *;
+key|val
+---+--------
+ 1|original
+(1 row)
+
+step c1: COMMIT;
+step select2: SELECT * FROM doselect;
+key|val
+---+--------
+ 1|original
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: insert1_update insert2_update c1 select2 c2
+step insert1_update: INSERT INTO doselect(key, val) VALUES(1, 'insert1') ON CONFLICT (key) DO SELECT FOR UPDATE RETURNING *;
+key|val
+---+--------
+ 1|original
+(1 row)
+
+step insert2_update: INSERT INTO doselect(key, val) VALUES(1, 'insert2') ON CONFLICT (key) DO SELECT FOR UPDATE RETURNING *; <waiting ...>
+step c1: COMMIT;
+step insert2_update: <... completed>
+key|val
+---+--------
+ 1|original
+(1 row)
+
+step select2: SELECT * FROM doselect;
+key|val
+---+--------
+ 1|original
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: insert1_update insert2_update a1 select2 c2
+step insert1_update: INSERT INTO doselect(key, val) VALUES(1, 'insert1') ON CONFLICT (key) DO SELECT FOR UPDATE RETURNING *;
+key|val
+---+--------
+ 1|original
+(1 row)
+
+step insert2_update: INSERT INTO doselect(key, val) VALUES(1, 'insert2') ON CONFLICT (key) DO SELECT FOR UPDATE RETURNING *; <waiting ...>
+step a1: ABORT;
+step insert2_update: <... completed>
+key|val
+---+--------
+ 1|original
+(1 row)
+
+step select2: SELECT * FROM doselect;
+key|val
+---+--------
+ 1|original
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: insert1_keyshare insert2_update c1 select2 c2
+step insert1_keyshare: INSERT INTO doselect(key, val) VALUES(1, 'insert1') ON CONFLICT (key) DO SELECT FOR KEY SHARE RETURNING *;
+key|val
+---+--------
+ 1|original
+(1 row)
+
+step insert2_update: INSERT INTO doselect(key, val) VALUES(1, 'insert2') ON CONFLICT (key) DO SELECT FOR UPDATE RETURNING *; <waiting ...>
+step c1: COMMIT;
+step insert2_update: <... completed>
+key|val
+---+--------
+ 1|original
+(1 row)
+
+step select2: SELECT * FROM doselect;
+key|val
+---+--------
+ 1|original
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: insert1_share insert2_update c1 select2 c2
+step insert1_share: INSERT INTO doselect(key, val) VALUES(1, 'insert1') ON CONFLICT (key) DO SELECT FOR SHARE RETURNING *;
+key|val
+---+--------
+ 1|original
+(1 row)
+
+step insert2_update: INSERT INTO doselect(key, val) VALUES(1, 'insert2') ON CONFLICT (key) DO SELECT FOR UPDATE RETURNING *; <waiting ...>
+step c1: COMMIT;
+step insert2_update: <... completed>
+key|val
+---+--------
+ 1|original
+(1 row)
+
+step select2: SELECT * FROM doselect;
+key|val
+---+--------
+ 1|original
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: insert1_nokeyupd insert2_update c1 select2 c2
+step insert1_nokeyupd: INSERT INTO doselect(key, val) VALUES(1, 'insert1') ON CONFLICT (key) DO SELECT FOR NO KEY UPDATE RETURNING *;
+key|val
+---+--------
+ 1|original
+(1 row)
+
+step insert2_update: INSERT INTO doselect(key, val) VALUES(1, 'insert2') ON CONFLICT (key) DO SELECT FOR UPDATE RETURNING *; <waiting ...>
+step c1: COMMIT;
+step insert2_update: <... completed>
+key|val
+---+--------
+ 1|original
+(1 row)
+
+step select2: SELECT * FROM doselect;
+key|val
+---+--------
+ 1|original
+(1 row)
+
+step c2: COMMIT;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 5afae33d370..e30dc7609cb 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -54,6 +54,7 @@ test: insert-conflict-do-update
test: insert-conflict-do-update-2
test: insert-conflict-do-update-3
test: insert-conflict-specconflict
+test: insert-conflict-do-select
test: merge-insert-update
test: merge-delete
test: merge-update
diff --git a/src/test/isolation/specs/insert-conflict-do-select.spec b/src/test/isolation/specs/insert-conflict-do-select.spec
new file mode 100644
index 00000000000..dcfd9f8cb53
--- /dev/null
+++ b/src/test/isolation/specs/insert-conflict-do-select.spec
@@ -0,0 +1,53 @@
+# INSERT...ON CONFLICT DO SELECT test
+#
+# This test verifies locking behavior of ON CONFLICT DO SELECT with different
+# lock strengths: no lock, FOR KEY SHARE, FOR SHARE, FOR NO KEY UPDATE, and
+# FOR UPDATE.
+
+setup
+{
+ CREATE TABLE doselect (key int primary key, val text);
+ INSERT INTO doselect VALUES (1, 'original');
+}
+
+teardown
+{
+ DROP TABLE doselect;
+}
+
+session s1
+setup
+{
+ BEGIN ISOLATION LEVEL READ COMMITTED;
+}
+step insert1 { INSERT INTO doselect(key, val) VALUES(1, 'insert1') ON CONFLICT (key) DO SELECT RETURNING *; }
+step insert1_keyshare { INSERT INTO doselect(key, val) VALUES(1, 'insert1') ON CONFLICT (key) DO SELECT FOR KEY SHARE RETURNING *; }
+step insert1_share { INSERT INTO doselect(key, val) VALUES(1, 'insert1') ON CONFLICT (key) DO SELECT FOR SHARE RETURNING *; }
+step insert1_nokeyupd { INSERT INTO doselect(key, val) VALUES(1, 'insert1') ON CONFLICT (key) DO SELECT FOR NO KEY UPDATE RETURNING *; }
+step insert1_update { INSERT INTO doselect(key, val) VALUES(1, 'insert1') ON CONFLICT (key) DO SELECT FOR UPDATE RETURNING *; }
+step c1 { COMMIT; }
+step a1 { ABORT; }
+
+session s2
+setup
+{
+ BEGIN ISOLATION LEVEL READ COMMITTED;
+}
+step insert2 { INSERT INTO doselect(key, val) VALUES(1, 'insert2') ON CONFLICT (key) DO SELECT RETURNING *; }
+step insert2_update { INSERT INTO doselect(key, val) VALUES(1, 'insert2') ON CONFLICT (key) DO SELECT FOR UPDATE RETURNING *; }
+step select2 { SELECT * FROM doselect; }
+step c2 { COMMIT; }
+
+# Test 1: DO SELECT without locking - should not block
+permutation insert1 insert2 c1 select2 c2
+
+# Test 2: DO SELECT FOR UPDATE - should block until first transaction commits
+permutation insert1_update insert2_update c1 select2 c2
+
+# Test 3: DO SELECT FOR UPDATE - should unblock when first transaction aborts
+permutation insert1_update insert2_update a1 select2 c2
+
+# Test 4: Different lock strengths all properly acquire locks
+permutation insert1_keyshare insert2_update c1 select2 c2
+permutation insert1_share insert2_update c1 select2 c2
+permutation insert1_nokeyupd insert2_update c1 select2 c2
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index d226c472340..8a4d6f540df 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -893,11 +893,31 @@ insert into parted_conflict_test values (1, 'a') on conflict do nothing;
-- index on a required, which does exist in parent
insert into parted_conflict_test values (1, 'a') on conflict (a) do nothing;
insert into parted_conflict_test values (1, 'a') on conflict (a) do update set b = excluded.b;
+insert into parted_conflict_test values (1, 'a') on conflict (a) do select returning *;
+ a | b
+---+---
+ 1 | a
+(1 row)
+
+insert into parted_conflict_test values (1, 'a') on conflict (a) do select for update returning *;
+ a | b
+---+---
+ 1 | a
+(1 row)
+
-- targeting partition directly will work
insert into parted_conflict_test_1 values (1, 'a') on conflict (a) do nothing;
insert into parted_conflict_test_1 values (1, 'b') on conflict (a) do update set b = excluded.b;
+insert into parted_conflict_test_1 values (1, 'b') on conflict (a) do select returning b;
+ b
+---
+ b
+(1 row)
+
-- index on b required, which doesn't exist in parent
-insert into parted_conflict_test values (2, 'b') on conflict (b) do update set a = excluded.a;
+insert into parted_conflict_test values (2, 'b') on conflict (b) do update set a = excluded.a; -- fail
+ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
+insert into parted_conflict_test values (2, 'b') on conflict (b) do select returning b; -- fail
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
-- targeting partition directly will work
insert into parted_conflict_test_1 values (2, 'b') on conflict (b) do update set a = excluded.a;
@@ -915,6 +935,12 @@ alter table parted_conflict_test attach partition parted_conflict_test_2 for val
truncate parted_conflict_test;
insert into parted_conflict_test values (3, 'a') on conflict (a) do update set b = excluded.b;
insert into parted_conflict_test values (3, 'b') on conflict (a) do update set b = excluded.b;
+insert into parted_conflict_test values (3, 'a') on conflict (a) do select returning b;
+ b
+---
+ b
+(1 row)
+
-- should see (3, 'b')
select * from parted_conflict_test order by a;
a | b
@@ -928,6 +954,12 @@ create table parted_conflict_test_3 partition of parted_conflict_test for values
truncate parted_conflict_test;
insert into parted_conflict_test (a, b) values (4, 'a') on conflict (a) do update set b = excluded.b;
insert into parted_conflict_test (a, b) values (4, 'b') on conflict (a) do update set b = excluded.b where parted_conflict_test.b = 'a';
+insert into parted_conflict_test (a, b) values (4, 'b') on conflict (a) do select returning b;
+ b
+---
+ b
+(1 row)
+
-- should see (4, 'b')
select * from parted_conflict_test order by a;
a | b
@@ -941,6 +973,11 @@ create table parted_conflict_test_4_1 partition of parted_conflict_test_4 for va
truncate parted_conflict_test;
insert into parted_conflict_test (a, b) values (5, 'a') on conflict (a) do update set b = excluded.b;
insert into parted_conflict_test (a, b) values (5, 'b') on conflict (a) do update set b = excluded.b where parted_conflict_test.b = 'a';
+insert into parted_conflict_test (a, b) values (5, 'b') on conflict (a) do select where parted_conflict_test.b = 'a' returning b;
+ b
+---
+(0 rows)
+
-- should see (5, 'b')
select * from parted_conflict_test order by a;
a | b
@@ -961,6 +998,58 @@ select * from parted_conflict_test order by a;
4 | b
(3 rows)
+-- test DO SELECT with multiple rows hitting different partitions
+truncate parted_conflict_test;
+insert into parted_conflict_test (a, b) values (1, 'a'), (2, 'b'), (4, 'c');
+insert into parted_conflict_test (a, b) values (1, 'x'), (2, 'y'), (4, 'z') on conflict (a) do select returning *;
+ a | b
+---+---
+ 1 | a
+ 2 | b
+ 4 | c
+(3 rows)
+
+-- should see original values (1, 'a'), (2, 'b'), (4, 'c')
+select * from parted_conflict_test order by a;
+ a | b
+---+---
+ 1 | a
+ 2 | b
+ 4 | c
+(3 rows)
+
+-- test DO SELECT with WHERE filtering across partitions
+insert into parted_conflict_test (a, b) values (1, 'n') on conflict (a) do select where parted_conflict_test.b = 'a' returning *;
+ a | b
+---+---
+ 1 | a
+(1 row)
+
+insert into parted_conflict_test (a, b) values (2, 'n') on conflict (a) do select where parted_conflict_test.b = 'x' returning *;
+ a | b
+---+---
+(0 rows)
+
+-- test DO SELECT with EXCLUDED in WHERE across partitions with different layouts
+insert into parted_conflict_test (a, b) values (3, 't') on conflict (a) do select where excluded.b = 't' returning *;
+ a | b
+---+---
+ 3 | t
+(1 row)
+
+-- test DO SELECT FOR UPDATE across different partition layouts
+insert into parted_conflict_test (a, b) values (1, 'l') on conflict (a) do select for update returning *;
+ a | b
+---+---
+ 1 | a
+(1 row)
+
+insert into parted_conflict_test (a, b) values (3, 'l') on conflict (a) do select for update returning *;
+ a | b
+---+---
+ 3 | t
+(1 row)
+
drop table parted_conflict_test;
-- test behavior of inserting a conflicting tuple into an intermediate
-- partitioning level
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index c958ef4d70a..a01a2c883fd 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -217,6 +217,48 @@ NOTICE: SELECT USING on rls_test_tgt.(3,"tgt d","TGT D")
3 | tgt d | TGT D
(1 row)
+ROLLBACK;
+-- ON CONFLICT DO SELECT should be similar to DO UPDATE, except there
+-- is not need to check the UPDATE policy in that case.
+BEGIN;
+INSERT INTO rls_test_tgt VALUES (4, 'tgt a') ON CONFLICT (a) DO SELECT RETURNING *;
+NOTICE: INSERT CHECK on rls_test_tgt.(4,"tgt a","TGT A")
+NOTICE: SELECT USING on rls_test_tgt.(4,"tgt a","TGT A")
+ a | b | c
+---+-------+-------
+ 4 | tgt a | TGT A
+(1 row)
+
+INSERT INTO rls_test_tgt VALUES (4, 'tgt a') ON CONFLICT (a) DO SELECT RETURNING *;
+NOTICE: INSERT CHECK on rls_test_tgt.(4,"tgt a","TGT A")
+NOTICE: SELECT USING on rls_test_tgt.(4,"tgt a","TGT A")
+NOTICE: SELECT USING on rls_test_tgt.(4,"tgt a","TGT A")
+ a | b | c
+---+-------+-------
+ 4 | tgt a | TGT A
+(1 row)
+
+ROLLBACK;
+-- ON CONFLICT DO SELECT FOR UPDATE should have the exact same RLS behaviour as DO UPDATE
+BEGIN;
+INSERT INTO rls_test_tgt VALUES (5, 'tgt a') ON CONFLICT (a) DO SELECT FOR UPDATE RETURNING *;
+NOTICE: INSERT CHECK on rls_test_tgt.(5,"tgt a","TGT A")
+NOTICE: SELECT USING on rls_test_tgt.(5,"tgt a","TGT A")
+ a | b | c
+---+-------+-------
+ 5 | tgt a | TGT A
+(1 row)
+
+INSERT INTO rls_test_tgt VALUES (5, 'tgt c') ON CONFLICT (a) DO SELECT FOR UPDATE RETURNING *;
+NOTICE: INSERT CHECK on rls_test_tgt.(5,"tgt c","TGT C")
+NOTICE: SELECT USING on rls_test_tgt.(5,"tgt c","TGT C")
+NOTICE: UPDATE USING on rls_test_tgt.(5,"tgt a","TGT A")
+NOTICE: SELECT USING on rls_test_tgt.(5,"tgt a","TGT A")
+ a | b | c
+---+-------+-------
+ 5 | tgt a | TGT A
+(1 row)
+
ROLLBACK;
-- MERGE should always apply SELECT USING policy clauses to both source and
-- target rows
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index 72b8147f849..213b9fa96ab 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -513,13 +513,17 @@ insert into parted_conflict_test values (1, 'a') on conflict do nothing;
-- index on a required, which does exist in parent
insert into parted_conflict_test values (1, 'a') on conflict (a) do nothing;
insert into parted_conflict_test values (1, 'a') on conflict (a) do update set b = excluded.b;
+insert into parted_conflict_test values (1, 'a') on conflict (a) do select returning *;
+insert into parted_conflict_test values (1, 'a') on conflict (a) do select for update returning *;
-- targeting partition directly will work
insert into parted_conflict_test_1 values (1, 'a') on conflict (a) do nothing;
insert into parted_conflict_test_1 values (1, 'b') on conflict (a) do update set b = excluded.b;
+insert into parted_conflict_test_1 values (1, 'b') on conflict (a) do select returning b;
-- index on b required, which doesn't exist in parent
-insert into parted_conflict_test values (2, 'b') on conflict (b) do update set a = excluded.a;
+insert into parted_conflict_test values (2, 'b') on conflict (b) do update set a = excluded.a; -- fail
+insert into parted_conflict_test values (2, 'b') on conflict (b) do select returning b; -- fail
-- targeting partition directly will work
insert into parted_conflict_test_1 values (2, 'b') on conflict (b) do update set a = excluded.a;
@@ -534,6 +538,7 @@ alter table parted_conflict_test attach partition parted_conflict_test_2 for val
truncate parted_conflict_test;
insert into parted_conflict_test values (3, 'a') on conflict (a) do update set b = excluded.b;
insert into parted_conflict_test values (3, 'b') on conflict (a) do update set b = excluded.b;
+insert into parted_conflict_test values (3, 'a') on conflict (a) do select returning b;
-- should see (3, 'b')
select * from parted_conflict_test order by a;
@@ -544,6 +549,7 @@ create table parted_conflict_test_3 partition of parted_conflict_test for values
truncate parted_conflict_test;
insert into parted_conflict_test (a, b) values (4, 'a') on conflict (a) do update set b = excluded.b;
insert into parted_conflict_test (a, b) values (4, 'b') on conflict (a) do update set b = excluded.b where parted_conflict_test.b = 'a';
+insert into parted_conflict_test (a, b) values (4, 'b') on conflict (a) do select returning b;
-- should see (4, 'b')
select * from parted_conflict_test order by a;
@@ -554,6 +560,7 @@ create table parted_conflict_test_4_1 partition of parted_conflict_test_4 for va
truncate parted_conflict_test;
insert into parted_conflict_test (a, b) values (5, 'a') on conflict (a) do update set b = excluded.b;
insert into parted_conflict_test (a, b) values (5, 'b') on conflict (a) do update set b = excluded.b where parted_conflict_test.b = 'a';
+insert into parted_conflict_test (a, b) values (5, 'b') on conflict (a) do select where parted_conflict_test.b = 'a' returning b;
-- should see (5, 'b')
select * from parted_conflict_test order by a;
@@ -566,6 +573,25 @@ insert into parted_conflict_test (a, b) values (1, 'b'), (2, 'c'), (4, 'b') on c
-- should see (1, 'b'), (2, 'a'), (4, 'b')
select * from parted_conflict_test order by a;
+-- test DO SELECT with multiple rows hitting different partitions
+truncate parted_conflict_test;
+insert into parted_conflict_test (a, b) values (1, 'a'), (2, 'b'), (4, 'c');
+insert into parted_conflict_test (a, b) values (1, 'x'), (2, 'y'), (4, 'z') on conflict (a) do select returning *;
+
+-- should see original values (1, 'a'), (2, 'b'), (4, 'c')
+select * from parted_conflict_test order by a;
+
+-- test DO SELECT with WHERE filtering across partitions
+insert into parted_conflict_test (a, b) values (1, 'n') on conflict (a) do select where parted_conflict_test.b = 'a' returning *;
+insert into parted_conflict_test (a, b) values (2, 'n') on conflict (a) do select where parted_conflict_test.b = 'x' returning *;
+
+-- test DO SELECT with EXCLUDED in WHERE across partitions with different layouts
+insert into parted_conflict_test (a, b) values (3, 't') on conflict (a) do select where excluded.b = 't' returning *;
+
+-- test DO SELECT FOR UPDATE across different partition layouts
+insert into parted_conflict_test (a, b) values (1, 'l') on conflict (a) do select for update returning *;
+insert into parted_conflict_test (a, b) values (3, 'l') on conflict (a) do select for update returning *;
+
drop table parted_conflict_test;
-- test behavior of inserting a conflicting tuple into an intermediate
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 5d923c5ca3b..e5b78810e69 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -141,6 +141,19 @@ INSERT INTO rls_test_tgt VALUES (3, 'tgt a') ON CONFLICT (a) DO UPDATE SET b = '
INSERT INTO rls_test_tgt VALUES (3, 'tgt c') ON CONFLICT (a) DO UPDATE SET b = 'tgt d' RETURNING *;
ROLLBACK;
+-- ON CONFLICT DO SELECT should be similar to DO UPDATE, except there
+-- is not need to check the UPDATE policy in that case.
+BEGIN;
+INSERT INTO rls_test_tgt VALUES (4, 'tgt a') ON CONFLICT (a) DO SELECT RETURNING *;
+INSERT INTO rls_test_tgt VALUES (4, 'tgt a') ON CONFLICT (a) DO SELECT RETURNING *;
+ROLLBACK;
+
+-- ON CONFLICT DO SELECT FOR UPDATE should have the exact same RLS behaviour as DO UPDATE
+BEGIN;
+INSERT INTO rls_test_tgt VALUES (5, 'tgt a') ON CONFLICT (a) DO SELECT FOR UPDATE RETURNING *;
+INSERT INTO rls_test_tgt VALUES (5, 'tgt c') ON CONFLICT (a) DO SELECT FOR UPDATE RETURNING *;
+ROLLBACK;
+
-- MERGE should always apply SELECT USING policy clauses to both source and
-- target rows
MERGE INTO rls_test_tgt t USING rls_test_src s ON t.a = s.a
@@ -958,6 +971,7 @@ INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel')
RESET SESSION AUTHORIZATION;
DROP POLICY p3_with_all ON document;
+
ALTER TABLE document ADD COLUMN dnotes text DEFAULT '';
-- all documents are readable
CREATE POLICY p1 ON document FOR SELECT USING (true);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 23bce72ae64..a8c6ba13f73 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1813,9 +1813,9 @@ OldToNewMappingData
OnCommitAction
OnCommitItem
OnConflictAction
+OnConflictActionState
OnConflictClause
OnConflictExpr
-OnConflictSetState
OpClassCacheEnt
OpExpr
OpFamilyMember
--
2.48.1
Import Notes
Reference msg id not found: 652c53e5-4db4-4393-ba65-5ecb3f7877ad@Spark
On Fri, Nov 14, 2025 at 10:34 PM Viktor Holmberg <v@viktorh.net> wrote:
Here are some updates that needed to be done after the improvements to the RLS docs / tests in 7dc4fa & 2e8424.
hi.
I see this:
https://commitfest.postgresql.org/patch/6109/
already mentioned all the related discussion links.
Could you also include the discussion link in the commit message?
currently this
spread across several email threads, adding the link would help others
understand the context.
----------------------------
create table x(key int4, fruit text);
create unique index x_idx on x(key);
create role alice;
grant all on schema public to alice;
grant insert on x to alice;
grant select(key) on x to alice;
set role alice;
explain (costs off, verbose) insert into x as i values (1, 'Apple') on
conflict (key) do select where i.fruit = 'Apple' returning 1;
explain (costs off, verbose) insert into x as i values (1, 'Apple') on
conflict (key) do select returning 1;
The above simple tests seem to show ON CONFLICT DO SELECT
permission working as intended, (I didn't try harder this time).
It looks like we currently lack permission-related regression tests
(no ``ERROR: permission denied for``),
we obviously need more.
On Sat, Nov 15, 2025 at 5:24 AM jian he <jian.universality@gmail.com> wrote:
On Fri, Nov 14, 2025 at 10:34 PM Viktor Holmberg <v@viktorh.net> wrote:
Here are some updates that needed to be done after the improvements to the RLS docs / tests in 7dc4fa & 2e8424.
hi.
I did some simple tests, found out that
SELECT FOR UPDATE, the lock mechanism seems to be working as intended.
We can add some tests on contrib/pgrowlocks to demonstrate that.
infer_arbiter_indexes
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("ON CONFLICT DO UPDATE not supported
with exclusion constraints")));
I guess this works for ON CONFLICT SELECT?
we can leave some comments on the function infer_arbiter_indexes,
and also add some tests on src/test/regress/sql/constraints.sql after line 570.
changing
OnConflictSetState
to
OnConflictActionState
could make it a separate patch.
all these 3 patches can be merged together, I think.
----------------------------------------
typedef struct OnConflictExpr
{
NodeTag type;
OnConflictAction action; /* DO NOTHING or UPDATE? */
"/* DO NOTHING or UPDATE? */"
this comment needs to be changed?
----------------------------------------
src/backend/rewrite/rewriteHandler.c
parsetree->onConflict->action == ONCONFLICT_UPDATE
maybe we also need to do some logic to the ONCONFLICT_SELECT
(I didn't check this part deeply)
src/test/regress/sql/updatable_views.sql, there are many occurence of
"on conflict".
I think we also need tests for ON CONFLICT DO SELECT.
CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i);
CREATE VIEW rw_view15 AS SELECT a, upper(b) FROM base_tbl;
INSERT INTO rw_view15 (a) VALUES (3);
truncate base_tbl;
INSERT INTO rw_view15 (a) VALUES (3);
INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO SELECT WHERE
excluded.upper = 'UNSPECIFIED' RETURNING *;
INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO UPDATE SET a =
excluded.a WHERE excluded.upper = 'UNSPECIFIED' RETURNING *;
INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO SELECT WHERE
excluded.upper = 'Unspecified' RETURNING *;
INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO UPDATE SET a =
excluded.a WHERE excluded.upper = 'Unspecified' RETURNING *;
If you compare it with the result above, it seems the updatable view behaves
inconsistent with ON CONFLICT DO SELECT versus ON CONFLICT DO UPDATE.