The attached patch allows EXCLUDED values to appear in the RETURNING
list of INSERT ... ON CONFLICT DO UPDATE. For example:
CREATE TABLE t (a int PRIMARY KEY, b text);
INSERT INTO t VALUES (1, 'old value');
INSERT INTO t VALUES (1, 'excluded value')
ON CONFLICT (a) DO UPDATE SET b = 'new value'
RETURNING a, old.b, new.b, excluded.b;
a | b | b | b
---+-----------+-----------+----------------
1 | old value | new value | excluded value
(1 row)
If there is no conflict, then OLD and EXCLUDED values are NULL.
For the most part, this is just an extension of the code to support
returning OLD and NEW. Originally, I had intended to not use
varreturningtype, since EXCLUDED is a different RTE than the result
relation, so the executor just uses the Var's varno (set to INNER_VAR
in setrefs.c). However, the rewriter code needed to support updatable
views and virtual generated columns turns out to be simpler if these
Vars have a separate varreturningtype.
I still have a lot more testing to do, and docs to update, but so far
the results look promising. I'll add this to the next CF.
Regards,
Dean
From fe79b310c287bf4bc6b37e25d53053b342d05767 Mon Sep 17 00:00:00 2001
From: Dean Rasheed <dean.a.rash...@gmail.com>
Date: Mon, 23 Jun 2025 17:20:02 +0100
Subject: [PATCH v1] Allow EXCLUDED in RETURNING list of INSERT ON CONFLICT DO
UPDATE.
TODO:
* More testing
* Docs
---
src/backend/executor/execExpr.c | 44 +++++++++--
src/backend/executor/execExprInterp.c | 21 ++++-
src/backend/executor/execPartition.c | 14 ++++
src/backend/executor/nodeModifyTable.c | 51 +++++++++---
src/backend/optimizer/path/allpaths.c | 3 +-
src/backend/optimizer/plan/setrefs.c | 77 ++++++++++++-------
src/backend/optimizer/prep/prepjointree.c | 13 ++--
src/backend/optimizer/prep/preptlist.c | 9 ++-
src/backend/optimizer/util/clauses.c | 2 +-
src/backend/optimizer/util/var.c | 11 ++-
src/backend/parser/analyze.c | 17 +++-
src/backend/parser/parse_expr.c | 2 +-
src/backend/parser/parse_relation.c | 4 +-
src/backend/rewrite/rewriteHandler.c | 42 ++++++----
src/backend/rewrite/rewriteManip.c | 65 +++++++++-------
src/include/executor/execExpr.h | 7 +-
src/include/nodes/execnodes.h | 2 +
src/include/nodes/primnodes.h | 26 +++++--
src/include/parser/parse_node.h | 7 +-
src/include/rewrite/rewriteManip.h | 4 +-
src/test/regress/expected/arrays.out | 26 +++++--
src/test/regress/expected/inherit.out | 9 ++-
src/test/regress/expected/insert_conflict.out | 26 ++++---
src/test/regress/expected/updatable_views.out | 16 +++-
src/test/regress/sql/arrays.sql | 9 ++-
src/test/regress/sql/inherit.sql | 3 +-
src/test/regress/sql/insert_conflict.sql | 8 +-
src/test/regress/sql/updatable_views.sql | 6 +-
src/tools/pgindent/typedefs.list | 1 +
29 files changed, 366 insertions(+), 159 deletions(-)
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index f1569879b52..d13c82bdd30 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -455,6 +455,7 @@ ExecBuildProjectionInfo(List *targetList,
/*
* Get the tuple from the relation being scanned, or the
* old/new tuple slot, if old/new values were requested.
+ * Should not see EXCLUDED here (should be INNER_VAR).
*/
switch (variable->varreturningtype)
{
@@ -469,6 +470,10 @@ ExecBuildProjectionInfo(List *targetList,
scratch.opcode = EEOP_ASSIGN_NEW_VAR;
state->flags |= EEO_FLAG_HAS_NEW;
break;
+ case VAR_RETURNING_EXCLUDED:
+ elog(ERROR, "wrong varno %d (expected %d) for variable returning excluded",
+ variable->varno, INNER_VAR);
+ break;
}
break;
}
@@ -972,6 +977,10 @@ ExecInitExprRec(Expr *node, ExprState *state,
scratch.opcode = EEOP_NEW_SYSVAR;
state->flags |= EEO_FLAG_HAS_NEW;
break;
+ case VAR_RETURNING_EXCLUDED:
+ elog(ERROR, "wrong varno %d (expected %d) for variable returning excluded",
+ variable->varno, INNER_VAR);
+ break;
}
break;
}
@@ -1007,6 +1016,10 @@ ExecInitExprRec(Expr *node, ExprState *state,
scratch.opcode = EEOP_NEW_VAR;
state->flags |= EEO_FLAG_HAS_NEW;
break;
+ case VAR_RETURNING_EXCLUDED:
+ elog(ERROR, "wrong varno %d (expected %d) for variable returning excluded",
+ variable->varno, INNER_VAR);
+ break;
}
break;
}
@@ -2638,10 +2651,23 @@ ExecInitExprRec(Expr *node, ExprState *state,
ReturningExpr *rexpr = (ReturningExpr *) node;
int retstep;
- /* Skip expression evaluation if OLD/NEW row doesn't exist */
+ /*
+ * Skip expression evaluation if OLD/NEW/EXCLUDED row doesn't
+ * exist.
+ */
scratch.opcode = EEOP_RETURNINGEXPR;
- scratch.d.returningexpr.nullflag = rexpr->retold ?
- EEO_FLAG_OLD_IS_NULL : EEO_FLAG_NEW_IS_NULL;
+ switch (rexpr->retkind)
+ {
+ case RETURNING_OLD_EXPR:
+ scratch.d.returningexpr.nullflag = EEO_FLAG_OLD_IS_NULL;
+ break;
+ case RETURNING_NEW_EXPR:
+ scratch.d.returningexpr.nullflag = EEO_FLAG_NEW_IS_NULL;
+ break;
+ case RETURNING_EXCLUDED_EXPR:
+ scratch.d.returningexpr.nullflag = EEO_FLAG_INNER_IS_NULL;
+ break;
+ }
scratch.d.returningexpr.jumpdone = -1; /* set below */
ExprEvalPushStep(state, &scratch);
retstep = state->steps_len - 1;
@@ -2649,14 +2675,15 @@ ExecInitExprRec(Expr *node, ExprState *state,
/* Steps to evaluate expression to return */
ExecInitExprRec(rexpr->retexpr, state, resv, resnull);
- /* Jump target used if OLD/NEW row doesn't exist */
+ /* Jump target used if OLD/NEW/EXCLUDED row doesn't exist */
state->steps[retstep].d.returningexpr.jumpdone = state->steps_len;
/* Update ExprState flags */
- if (rexpr->retold)
+ if (rexpr->retkind == RETURNING_OLD_EXPR)
state->flags |= EEO_FLAG_HAS_OLD;
- else
+ else if (rexpr->retkind == RETURNING_NEW_EXPR)
state->flags |= EEO_FLAG_HAS_NEW;
+ /* we don't bother recording references to EXCLUDED */
break;
}
@@ -3013,6 +3040,10 @@ expr_setup_walker(Node *node, ExprSetupInfo *info)
case VAR_RETURNING_NEW:
info->last_new = Max(info->last_new, attnum);
break;
+ case VAR_RETURNING_EXCLUDED:
+ elog(ERROR, "wrong varno %d (expected %d) for variable returning excluded",
+ variable->varno, INNER_VAR);
+ break;
}
break;
}
@@ -3178,6 +3209,7 @@ ExecInitWholeRowVar(ExprEvalStep *scratch, Var *variable, ExprState *state)
state->flags |= EEO_FLAG_HAS_OLD;
else if (variable->varreturningtype == VAR_RETURNING_NEW)
state->flags |= EEO_FLAG_HAS_NEW;
+ /* we don't bother recording references to EXCLUDED */
/*
* If the input tuple came from a subquery, it might contain "resjunk"
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 8a72b5e70a4..2e8ecd8b30a 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -5340,7 +5340,21 @@ ExecEvalWholeRowVar(ExprState *state, ExprEvalStep *op, ExprContext *econtext)
switch (variable->varno)
{
case INNER_VAR:
- /* get the tuple from the inner node */
+
+ /*
+ * Get the tuple from the inner node.
+ *
+ * In a RETURNING expression, this is used for EXCLUDED values in
+ * an INSERT ... ON CONFLICT DO UPDATE. If the non-conflicting
+ * branch is taken, the EXCLUDED row is NULL, and we return NULL
+ * rather than an all-NULL record.
+ */
+ if (state->flags & EEO_FLAG_INNER_IS_NULL)
+ {
+ *op->resvalue = (Datum) 0;
+ *op->resnull = true;
+ return;
+ }
slot = econtext->ecxt_innertuple;
break;
@@ -5385,6 +5399,11 @@ ExecEvalWholeRowVar(ExprState *state, ExprEvalStep *op, ExprContext *econtext)
}
slot = econtext->ecxt_newtuple;
break;
+
+ case VAR_RETURNING_EXCLUDED:
+ elog(ERROR, "wrong varno %d (expected %d) for variable returning excluded",
+ variable->varno, INNER_VAR);
+ break;
}
break;
}
diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.c
index 514eae1037d..6adc07cbed3 100644
--- a/src/backend/executor/execPartition.c
+++ b/src/backend/executor/execPartition.c
@@ -642,12 +642,26 @@ ExecInitPartitionInfo(ModifyTableState *mtstate, EState *estate,
/*
* Convert Vars in it to contain this partition's attribute numbers.
+ * If we're doing an INSERT ... ON CONFLICT DO UPDATE, the RETURNING
+ * list might contain references to the EXCLUDED pseudo-relation
+ * (INNER_VAR), so we must map their attribute numbers too.
*/
if (part_attmap == NULL)
part_attmap =
build_attrmap_by_name(RelationGetDescr(partrel),
RelationGetDescr(firstResultRel),
false);
+
+ if (node->onConflictAction == ONCONFLICT_UPDATE)
+ {
+ returningList = (List *)
+ map_variable_attnos((Node *) returningList,
+ INNER_VAR, 0,
+ part_attmap,
+ RelationGetForm(partrel)->reltype,
+ &found_whole_row);
+ /* We ignore the value of found_whole_row. */
+ }
returningList = (List *)
map_variable_attnos((Node *) returningList,
firstVarno, 0,
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 54da8e7995b..1e09ceb00da 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -277,6 +277,7 @@ ExecCheckPlanOutput(Relation resultRel, List *targetList)
* oldSlot: slot holding old tuple deleted or updated
* newSlot: slot holding new tuple inserted or updated
* planSlot: slot holding tuple returned by top subplan node
+ * exclSlot: slot holding EXCLUDED tuple (for INSERT ... ON CONFLICT ...)
*
* Note: If oldSlot and newSlot are NULL, the FDW should have already provided
* econtext's scan tuple and its old & new tuples are not needed (FDW direct-
@@ -290,8 +291,11 @@ ExecProcessReturning(ModifyTableContext *context,
CmdType cmdType,
TupleTableSlot *oldSlot,
TupleTableSlot *newSlot,
- TupleTableSlot *planSlot)
+ TupleTableSlot *planSlot,
+ TupleTableSlot *exclSlot)
{
+ ModifyTableState *mtstate = context->mtstate;
+ ModifyTable *node = (ModifyTable *) mtstate->ps.plan;
EState *estate = context->estate;
ProjectionInfo *projectReturning = resultRelInfo->ri_projectReturning;
ExprContext *econtext = projectReturning->pi_exprContext;
@@ -332,10 +336,18 @@ ExecProcessReturning(ModifyTableContext *context,
else
econtext->ecxt_newtuple = NULL; /* No references to NEW columns */
+ /* Make EXCLUDED tuple available to ExecProject, if required */
+ if (exclSlot)
+ econtext->ecxt_innertuple = exclSlot;
+ else if (cmdType == CMD_INSERT && node->onConflictAction == ONCONFLICT_UPDATE)
+ econtext->ecxt_innertuple = ExecGetAllNullSlot(estate, resultRelInfo);
+ else
+ econtext->ecxt_innertuple = NULL;
+
/*
- * Tell ExecProject whether or not the OLD/NEW rows actually exist. This
- * information is required to evaluate ReturningExpr nodes and also in
- * ExecEvalSysVar() and ExecEvalWholeRowVar().
+ * Tell ExecProject whether or not the OLD/NEW/EXCLUDED rows actually
+ * exist. This information is required to evaluate ReturningExpr nodes
+ * and also in ExecEvalSysVar() and ExecEvalWholeRowVar().
*/
if (oldSlot == NULL)
projectReturning->pi_state.flags |= EEO_FLAG_OLD_IS_NULL;
@@ -347,6 +359,11 @@ ExecProcessReturning(ModifyTableContext *context,
else
projectReturning->pi_state.flags &= ~EEO_FLAG_NEW_IS_NULL;
+ if (exclSlot == NULL)
+ projectReturning->pi_state.flags |= EEO_FLAG_INNER_IS_NULL;
+ else
+ projectReturning->pi_state.flags &= ~EEO_FLAG_INNER_IS_NULL;
+
/* Compute the RETURNING expressions */
return ExecProject(projectReturning);
}
@@ -1330,7 +1347,7 @@ ExecInsert(ModifyTableContext *context,
}
result = ExecProcessReturning(context, resultRelInfo, CMD_INSERT,
- oldSlot, slot, planSlot);
+ oldSlot, slot, planSlot, NULL);
/*
* For a cross-partition UPDATE, release the old tuple, first making
@@ -1890,7 +1907,7 @@ ldelete:
}
rslot = ExecProcessReturning(context, resultRelInfo, CMD_DELETE,
- slot, NULL, context->planSlot);
+ slot, NULL, context->planSlot, NULL);
/*
* Before releasing the target tuple again, make sure rslot has a
@@ -2449,6 +2466,8 @@ ExecCrossPartitionUpdateForeignKey(ModifyTableContext *context,
* planSlot is the output of the ModifyTable's subplan; we use it
* to access values from other input tables (for RETURNING),
* row-ID junk columns, etc.
+ * exclSlot contains the EXCLUDED tuple if this is the auxiliary
+ * UPDATE of an INSERT ... ON CONFLICT DO UPDATE.
*
* Returns RETURNING result if any, otherwise NULL. On exit, if tupleid
* had identified the tuple to update, it will identify the tuple
@@ -2458,7 +2477,7 @@ ExecCrossPartitionUpdateForeignKey(ModifyTableContext *context,
static TupleTableSlot *
ExecUpdate(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
ItemPointer tupleid, HeapTuple oldtuple, TupleTableSlot *oldSlot,
- TupleTableSlot *slot, bool canSetTag)
+ TupleTableSlot *slot, TupleTableSlot *exclSlot, bool canSetTag)
{
EState *estate = context->estate;
Relation resultRelationDesc = resultRelInfo->ri_RelationDesc;
@@ -2691,7 +2710,8 @@ redo_act:
/* Process RETURNING if present */
if (resultRelInfo->ri_projectReturning)
return ExecProcessReturning(context, resultRelInfo, CMD_UPDATE,
- oldSlot, slot, context->planSlot);
+ oldSlot, slot, context->planSlot,
+ exclSlot);
return NULL;
}
@@ -2913,6 +2933,7 @@ ExecOnConflictUpdate(ModifyTableContext *context,
*returning = ExecUpdate(context, resultRelInfo,
conflictTid, NULL, existing,
resultRelInfo->ri_onConflict->oc_ProjSlot,
+ excludedSlot,
canSetTag);
/*
@@ -3537,7 +3558,8 @@ lmerge_matched:
CMD_UPDATE,
resultRelInfo->ri_oldTupleSlot,
newslot,
- context->planSlot);
+ context->planSlot,
+ NULL);
break;
case CMD_DELETE:
@@ -3546,7 +3568,8 @@ lmerge_matched:
CMD_DELETE,
resultRelInfo->ri_oldTupleSlot,
NULL,
- context->planSlot);
+ context->planSlot,
+ NULL);
break;
case CMD_NOTHING:
@@ -4310,12 +4333,16 @@ ExecModifyTable(PlanState *pstate)
* provide it here. The individual old and new slots are not
* needed, since direct-modify is disabled if the RETURNING list
* refers to OLD/NEW values.
+ *
+ * Currently, foreign tables do not support UNIQUE constraints,
+ * and therefore they do not support INSERT ... ON CONFLICT, and
+ * so the EXCLUDED slot is also not needed.
*/
Assert((resultRelInfo->ri_projectReturning->pi_state.flags & EEO_FLAG_HAS_OLD) == 0 &&
(resultRelInfo->ri_projectReturning->pi_state.flags & EEO_FLAG_HAS_NEW) == 0);
slot = ExecProcessReturning(&context, resultRelInfo, operation,
- NULL, NULL, context.planSlot);
+ NULL, NULL, context.planSlot, NULL);
return slot;
}
@@ -4505,7 +4532,7 @@ ExecModifyTable(PlanState *pstate)
/* Now apply the update. */
slot = ExecUpdate(&context, resultRelInfo, tupleid, oldtuple,
- oldSlot, slot, node->canSetTag);
+ oldSlot, slot, NULL, node->canSetTag);
if (tuplock)
UnlockTuple(resultRelInfo->ri_RelationDesc, tupleid,
InplaceUpdateTupleLock);
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 6cc6966b060..017a685959b 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -4013,8 +4013,7 @@ subquery_push_qual(Query *subquery, RangeTblEntry *rte, Index rti, Node *qual)
* each component query gets its own copy of the qual.
*/
qual = ReplaceVarsFromTargetList(qual, rti, 0, rte,
- subquery->targetList,
- subquery->resultRelation,
+ subquery->targetList, 0,
REPLACEVARS_REPORT_ERROR, 0,
&subquery->hasSubLinks);
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 846e44186c3..6d45e6e0c40 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -206,6 +206,8 @@ static List *set_returning_clause_references(PlannerInfo *root,
List *rlist,
Plan *topplan,
Index resultRelation,
+ Index exclRelRTI,
+ indexed_tlist *excl_itlist,
int rtoffset);
static List *set_windowagg_runcondition_references(PlannerInfo *root,
List *runcondition,
@@ -1061,10 +1063,14 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset)
{
ModifyTable *splan = (ModifyTable *) plan;
Plan *subplan = outerPlan(splan);
+ indexed_tlist *excl_itlist = NULL;
Assert(splan->plan.targetlist == NIL);
Assert(splan->plan.qual == NIL);
+ if (splan->onConflictSet)
+ excl_itlist = build_tlist_index(splan->exclRelTlist);
+
splan->withCheckOptionLists =
fix_scan_list(root, splan->withCheckOptionLists,
rtoffset, 1);
@@ -1090,6 +1096,8 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset)
rlist,
subplan,
resultrel,
+ splan->exclRelRTI,
+ excl_itlist,
rtoffset);
newRL = lappend(newRL, rlist);
}
@@ -1117,23 +1125,19 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset)
*/
if (splan->onConflictSet)
{
- indexed_tlist *itlist;
-
- itlist = build_tlist_index(splan->exclRelTlist);
-
splan->onConflictSet =
fix_join_expr(root, splan->onConflictSet,
- NULL, itlist,
+ NULL, excl_itlist,
linitial_int(splan->resultRelations),
rtoffset, NRM_EQUAL, NUM_EXEC_QUAL(plan));
splan->onConflictWhere = (Node *)
fix_join_expr(root, (List *) splan->onConflictWhere,
- NULL, itlist,
+ NULL, excl_itlist,
linitial_int(splan->resultRelations),
rtoffset, NRM_EQUAL, NUM_EXEC_QUAL(plan));
- pfree(itlist);
+ pfree(excl_itlist);
splan->exclRelTlist =
fix_scan_list(root, splan->exclRelTlist, rtoffset, 1);
@@ -2798,12 +2802,12 @@ build_tlist_index(List *tlist)
* build_tlist_index_other_vars --- build a restricted tlist index
*
* This is like build_tlist_index, but we only index tlist entries that
- * are Vars belonging to some rel other than the one specified. We will set
+ * are Vars belonging to some rel other than the ones specified. We will set
* has_ph_vars (allowing PlaceHolderVars to be matched), but not has_non_vars
* (so nothing other than Vars and PlaceHolderVars can be matched).
*/
static indexed_tlist *
-build_tlist_index_other_vars(List *tlist, int ignore_rel)
+build_tlist_index_other_vars(List *tlist, int ignore_rel1, int ignore_rel2)
{
indexed_tlist *itlist;
tlist_vinfo *vinfo;
@@ -2828,7 +2832,7 @@ build_tlist_index_other_vars(List *tlist, int ignore_rel)
{
Var *var = (Var *) tle->expr;
- if (var->varno != ignore_rel)
+ if (var->varno != ignore_rel1 && var->varno != ignore_rel2)
{
vinfo->varno = var->varno;
vinfo->varattno = var->varattno;
@@ -3067,10 +3071,14 @@ search_indexed_tlist_for_sortgroupref(Expr *node,
* acceptable_rel should be zero so that any failure to match a Var will be
* reported as an error.
* 2) RETURNING clauses, which may contain both Vars of the target relation
- * and Vars of other relations. In this case we want to replace the
- * other-relation Vars by OUTER_VAR references, while leaving target Vars
- * alone. Thus inner_itlist = NULL and acceptable_rel = the ID of the
- * target relation should be passed.
+ * and Vars of other relations, including the EXCLUDED pseudo-relation in
+ * an INSERT ... ON CONFLICT DO UPDATE command. In this case, we want to
+ * replace references to EXCLUDED with INNER_VAR references, and
+ * other-relation Vars with OUTER_VAR references, while leaving target Vars
+ * alone. Thus inner_itlist is to be EXCLUDED elements, if this is an
+ * INSERT with an ON CONFLICT DO UPDATE clause, outer_itlist is any other
+ * non-target relation elements, and acceptable_rel = the ID of the target
+ * relation.
* 3) ON CONFLICT UPDATE SET/WHERE clauses. Here references to EXCLUDED are
* to be replaced with INNER_VAR references, while leaving target Vars (the
* to-be-updated relation) alone. Correspondingly inner_itlist is to be
@@ -3131,15 +3139,16 @@ fix_join_expr_mutator(Node *node, fix_join_expr_context *context)
/*
* Verify that Vars with non-default varreturningtype only appear in
- * the RETURNING list, and refer to the target relation.
+ * the RETURNING list, and that OLD/NEW Vars refer to the target
+ * relation.
*/
if (var->varreturningtype != VAR_RETURNING_DEFAULT)
{
- if (context->inner_itlist != NULL ||
- context->outer_itlist == NULL ||
+ if (context->outer_itlist == NULL ||
context->acceptable_rel == 0)
- elog(ERROR, "variable returning old/new found outside RETURNING list");
- if (var->varno != context->acceptable_rel)
+ elog(ERROR, "variable returning old/new/excluded found outside RETURNING list");
+ if (var->varreturningtype != VAR_RETURNING_EXCLUDED &&
+ var->varno != context->acceptable_rel)
elog(ERROR, "wrong varno %d (expected %d) for variable returning old/new",
var->varno, context->acceptable_rel);
}
@@ -3369,11 +3378,14 @@ fix_upper_expr_mutator(Node *node, fix_upper_expr_context *context)
*
* If the query involves more than just the result table, we have to
* adjust any Vars that refer to other tables to reference junk tlist
- * entries in the top subplan's targetlist. Vars referencing the result
- * table should be left alone, however (the executor will evaluate them
- * using the actual heap tuple, after firing triggers if any). In the
- * adjusted RETURNING list, result-table Vars will have their original
- * varno (plus rtoffset), but Vars for other rels will have varno OUTER_VAR.
+ * entries in the top subplan's targetlist. Vars referencing the EXCLUDED
+ * pseudo-relation of an INSERT ... ON CONFLICT DO UPDATE command should be
+ * adjusted to reference INNER_VAR, and Vars referencing the result table
+ * should be left alone (the executor will evaluate them using the actual heap
+ * tuple, after firing triggers if any). In the adjusted RETURNING list,
+ * result-table Vars will have their original varno (plus rtoffset), but Vars
+ * for the EXCLUDED pseudo-relation and other rels will have varno INNER_VAR
+ * and OUTER_VAR espectively.
*
* We also must perform opcode lookup and add regclass OIDs to
* root->glob->relationOids.
@@ -3382,6 +3394,8 @@ fix_upper_expr_mutator(Node *node, fix_upper_expr_context *context)
* 'topplan': the top subplan node that will be just below the ModifyTable
* node (note it's not yet passed through set_plan_refs)
* 'resultRelation': RT index of the associated result relation
+ * 'exclRelRTI': RT index of EXCLUDED pseudo-relation
+ * 'excl_itlist': EXCLUDED pseudo-relation elements (or NULL)
* 'rtoffset': how much to increment varnos by
*
* Note: the given 'root' is for the parent query level, not the 'topplan'.
@@ -3396,6 +3410,8 @@ set_returning_clause_references(PlannerInfo *root,
List *rlist,
Plan *topplan,
Index resultRelation,
+ Index exclRelRTI,
+ indexed_tlist *excl_itlist,
int rtoffset)
{
indexed_tlist *itlist;
@@ -3403,9 +3419,11 @@ set_returning_clause_references(PlannerInfo *root,
/*
* We can perform the desired Var fixup by abusing the fix_join_expr
* machinery that formerly handled inner indexscan fixup. We search the
- * top plan's targetlist for Vars of non-result relations, and use
- * fix_join_expr to convert RETURNING Vars into references to those tlist
- * entries, while leaving result-rel Vars as-is.
+ * top plan's targetlist for Vars of non-result relations (other than
+ * EXCLUDED), and use fix_join_expr to convert RETURNING Vars into
+ * references to those tlist entries, and convert RETURNING EXCLUDED Vars
+ * into references to excl_itlist entries, while leaving result-rel Vars
+ * as-is.
*
* PlaceHolderVars will also be sought in the targetlist, but no
* more-complex expressions will be. Note that it is not possible for a
@@ -3414,12 +3432,13 @@ set_returning_clause_references(PlannerInfo *root,
* prepared to pick apart the PlaceHolderVar and evaluate its contained
* expression instead.
*/
- itlist = build_tlist_index_other_vars(topplan->targetlist, resultRelation);
+ itlist = build_tlist_index_other_vars(topplan->targetlist, resultRelation,
+ exclRelRTI);
rlist = fix_join_expr(root,
rlist,
itlist,
- NULL,
+ excl_itlist,
resultRelation,
rtoffset,
NRM_EQUAL,
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 87dc6f56b57..02c9dfe2585 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -71,8 +71,7 @@ typedef struct pullup_replace_vars_context
PlannerInfo *root;
List *targetlist; /* tlist of subquery being pulled up */
RangeTblEntry *target_rte; /* RTE of subquery */
- int result_relation; /* the index of the result relation in the
- * rewritten query */
+ int new_target_varno; /* see ReplaceVarFromTargetList() */
Relids relids; /* relids within subquery, as numbered after
* pullup (set only if target_rte->lateral) */
nullingrel_info *nullinfo; /* per-RTE nullingrel info (set only if
@@ -1038,7 +1037,7 @@ expand_virtual_generated_columns(PlannerInfo *root)
rvcontext.root = root;
rvcontext.targetlist = tlist;
rvcontext.target_rte = rte;
- rvcontext.result_relation = parse->resultRelation;
+ rvcontext.new_target_varno = rt_index;
/* won't need these values */
rvcontext.relids = NULL;
rvcontext.nullinfo = NULL;
@@ -1436,7 +1435,7 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
rvcontext.root = root;
rvcontext.targetlist = subquery->targetList;
rvcontext.target_rte = rte;
- rvcontext.result_relation = 0;
+ rvcontext.new_target_varno = 0;
if (rte->lateral)
{
rvcontext.relids = get_relids_in_jointree((Node *) subquery->jointree,
@@ -1987,7 +1986,7 @@ pull_up_simple_values(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte)
rvcontext.root = root;
rvcontext.targetlist = tlist;
rvcontext.target_rte = rte;
- rvcontext.result_relation = 0;
+ rvcontext.new_target_varno = 0;
rvcontext.relids = NULL; /* can't be any lateral references here */
rvcontext.nullinfo = NULL;
rvcontext.outer_hasSubLinks = &parse->hasSubLinks;
@@ -2147,7 +2146,7 @@ pull_up_constant_function(PlannerInfo *root, Node *jtnode,
NULL, /* resname */
false)); /* resjunk */
rvcontext.target_rte = rte;
- rvcontext.result_relation = 0;
+ rvcontext.new_target_varno = 0;
/*
* Since this function was reduced to a Const, it doesn't contain any
@@ -2681,7 +2680,7 @@ pullup_replace_vars_callback(Var *var,
newnode = ReplaceVarFromTargetList(var,
rcon->target_rte,
rcon->targetlist,
- rcon->result_relation,
+ rcon->new_target_varno,
REPLACEVARS_REPORT_ERROR,
0);
diff --git a/src/backend/optimizer/prep/preptlist.c b/src/backend/optimizer/prep/preptlist.c
index ffc9d6c3f30..098d8f8498a 100644
--- a/src/backend/optimizer/prep/preptlist.c
+++ b/src/backend/optimizer/prep/preptlist.c
@@ -291,7 +291,10 @@ preprocess_targetlist(PlannerInfo *root)
* used in RETURNING that belong to other relations. We need to do this
* to make these Vars available for the RETURNING calculation. Vars that
* belong to the result rel don't need to be added, because they will be
- * made to refer to the actual heap tuple.
+ * made to refer to the actual heap tuple. Vars that refer to the
+ * EXCLUDED pseudo-relation of an INSERT ... ON CONFLICT DO UPDATE command
+ * are also not needed, because they are handled specially in the
+ * executor.
*/
if (parse->returningList && list_length(parse->rtable) > 1)
{
@@ -308,7 +311,9 @@ preprocess_targetlist(PlannerInfo *root)
TargetEntry *tle;
if (IsA(var, Var) &&
- var->varno == result_relation)
+ (var->varno == result_relation ||
+ (parse->onConflict &&
+ var->varno == parse->onConflict->exclRelIndex)))
continue; /* don't need it */
if (tlist_member((Expr *) var, tlist))
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 26a3e050086..dc50f0a5c84 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -3407,7 +3407,7 @@ eval_const_expressions_mutator(Node *node,
fselect->resulttypmod,
fselect->resultcollid,
((Var *) arg)->varlevelsup);
- /* New Var has same OLD/NEW returning as old one */
+ /* New Var has same returningtype as old one */
newvar->varreturningtype = ((Var *) arg)->varreturningtype;
/* New Var is nullable by same rels as the old one */
newvar->varnullingrels = ((Var *) arg)->varnullingrels;
diff --git a/src/backend/optimizer/util/var.c b/src/backend/optimizer/util/var.c
index 8065237a189..c2642c88e92 100644
--- a/src/backend/optimizer/util/var.c
+++ b/src/backend/optimizer/util/var.c
@@ -501,8 +501,8 @@ contain_vars_of_level_walker(Node *node, int *sublevels_up)
*
* Returns true if any found.
*
- * Any ReturningExprs are also detected --- if an OLD/NEW Var was rewritten,
- * we still regard this as a clause that returns OLD/NEW values.
+ * Any ReturningExprs are also checked --- if an OLD/NEW Var was rewritten, we
+ * still regard this as a clause that returns OLD/NEW values.
*
* Does not examine subqueries, therefore must only be used after reduction
* of sublinks to subplans!
@@ -521,13 +521,16 @@ contain_vars_returning_old_or_new_walker(Node *node, void *context)
if (IsA(node, Var))
{
if (((Var *) node)->varlevelsup == 0 &&
- ((Var *) node)->varreturningtype != VAR_RETURNING_DEFAULT)
+ (((Var *) node)->varreturningtype == VAR_RETURNING_OLD ||
+ ((Var *) node)->varreturningtype == VAR_RETURNING_NEW))
return true; /* abort the tree traversal and return true */
return false;
}
if (IsA(node, ReturningExpr))
{
- if (((ReturningExpr *) node)->retlevelsup == 0)
+ if (((ReturningExpr *) node)->retlevelsup == 0 &&
+ (((ReturningExpr *) node)->retkind == RETURNING_OLD_EXPR ||
+ ((ReturningExpr *) node)->retkind == RETURNING_NEW_EXPR))
return true; /* abort the tree traversal and return true */
return false;
}
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 34f7c17f576..bf7ee465a14 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1234,12 +1234,21 @@ transformOnConflictClause(ParseState *pstate,
EXPR_KIND_WHERE, "WHERE");
/*
- * Remove the EXCLUDED pseudo relation from the query namespace, since
- * it's not supposed to be available in RETURNING. (Maybe someday we
- * could allow that, and drop this step.)
+ * Leave the EXCLUDED pseudo relation in the query namespace so that
+ * it is available in RETURNING expressions, but change it to be a
+ * table-only item so that its columns are only accessible using
+ * qualified names. This ensures that columns from the target
+ * relation can be accessed using unqualified names without ambiguity.
+ *
+ * Also, set its returning_type so that any RETURNING list Vars
+ * referencing it are marked correctly.
*/
Assert((ParseNamespaceItem *) llast(pstate->p_namespace) == exclNSItem);
- pstate->p_namespace = list_delete_last(pstate->p_namespace);
+ exclNSItem->p_cols_visible = false;
+
+ exclNSItem->p_returning_type = VAR_RETURNING_EXCLUDED;
+ for (int i = 0; i < list_length(exclNSItem->p_names->colnames); i++)
+ exclNSItem->p_nscolumns[i].p_varreturningtype = VAR_RETURNING_EXCLUDED;
}
/* Finally, build ON CONFLICT DO [NOTHING | UPDATE] expression */
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index d66276801c6..e551c0bd7a1 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -2653,7 +2653,7 @@ transformWholeRowRef(ParseState *pstate, ParseNamespaceItem *nsitem,
result = makeWholeRowVar(nsitem->p_rte, nsitem->p_rtindex,
sublevels_up, true);
- /* mark Var for RETURNING OLD/NEW, as necessary */
+ /* mark Var for RETURNING OLD/NEW/EXCLUDED, as necessary */
result->varreturningtype = nsitem->p_returning_type;
/* location is not filled in by makeWholeRowVar */
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index 04ecf64b1fc..1651c52eb87 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -258,7 +258,7 @@ scanNameSpaceForRelid(ParseState *pstate, Oid relid, int location)
/* If not inside LATERAL, ignore lateral-only items */
if (nsitem->p_lateral_only && !pstate->p_lateral_active)
continue;
- /* Ignore OLD/NEW namespace items that can appear in RETURNING */
+ /* Ignore OLD/NEW/EXCLUDED namespace items in RETURNING */
if (nsitem->p_returning_type != VAR_RETURNING_DEFAULT)
continue;
@@ -775,7 +775,7 @@ scanNSItemForColumn(ParseState *pstate, ParseNamespaceItem *nsitem,
}
var->location = location;
- /* Mark Var for RETURNING OLD/NEW, as necessary */
+ /* Mark Var for RETURNING OLD/NEW/EXCLUDED, as necessary */
var->varreturningtype = nsitem->p_returning_type;
/* Mark Var if it's nulled by any outer joins */
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 2ef0e7fbf3a..98a02167f23 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -97,7 +97,7 @@ static List *matchLocks(CmdType event, Relation relation,
static Query *fireRIRrules(Query *parsetree, List *activeRIRs);
static Bitmapset *adjust_view_column_set(Bitmapset *cols, List *targetlist);
static Node *expand_generated_columns_internal(Node *node, Relation rel, int rt_index,
- RangeTblEntry *rte, int result_relation);
+ RangeTblEntry *rte);
/*
@@ -643,7 +643,7 @@ rewriteRuleAction(Query *parsetree,
0,
rt_fetch(new_varno, sub_action->rtable),
parsetree->targetList,
- sub_action->resultRelation,
+ 0,
(event == CMD_UPDATE) ?
REPLACEVARS_CHANGE_VARNO :
REPLACEVARS_SUBSTITUTE_NULL,
@@ -2345,7 +2345,7 @@ CopyAndAddInvertedQual(Query *parsetree,
rt_fetch(rt_index,
parsetree->rtable),
parsetree->targetList,
- parsetree->resultRelation,
+ 0,
(event == CMD_UPDATE) ?
REPLACEVARS_CHANGE_VARNO :
REPLACEVARS_SUBSTITUTE_NULL,
@@ -3704,12 +3704,12 @@ rewriteTargetView(Query *parsetree, Relation view)
BuildOnConflictExcludedTargetlist(base_rel, new_exclRelIndex);
/*
- * Update all Vars in the ON CONFLICT clause that refer to the old
- * EXCLUDED pseudo-relation. We want to use the column mappings
- * defined in the view targetlist, but we need the outputs to refer to
- * the new EXCLUDED pseudo-relation rather than the new target RTE.
- * Also notice that "EXCLUDED.*" will be expanded using the view's
- * rowtype, which seems correct.
+ * Update all Vars in the ON CONFLICT clause and RETURNING list that
+ * refer to the old EXCLUDED pseudo-relation. We want to use the
+ * column mappings defined in the view targetlist, but we need the
+ * outputs to refer to the new EXCLUDED pseudo-relation rather than
+ * the new target RTE. Also notice that "EXCLUDED.*" will be expanded
+ * using the view's rowtype, which seems correct.
*/
tmp_tlist = copyObject(view_targetlist);
@@ -3722,7 +3722,18 @@ rewriteTargetView(Query *parsetree, Relation view)
0,
view_rte,
tmp_tlist,
- new_rt_index,
+ 0,
+ REPLACEVARS_REPORT_ERROR,
+ 0,
+ &parsetree->hasSubLinks);
+
+ parsetree->returningList = (List *)
+ ReplaceVarsFromTargetList((Node *) parsetree->returningList,
+ old_exclRelIndex,
+ 0,
+ view_rte,
+ tmp_tlist,
+ new_exclRelIndex,
REPLACEVARS_REPORT_ERROR,
0,
&parsetree->hasSubLinks);
@@ -4420,13 +4431,11 @@ RewriteQuery(Query *parsetree, List *rewrite_events, int orig_rt_length)
* virtual generated column expressions from rel, if there are any.
*
* The caller must also provide rte, the RTE describing the target relation,
- * in order to handle any whole-row Vars referencing the target, and
- * result_relation, the index of the result relation, if this is part of an
- * INSERT/UPDATE/DELETE/MERGE query.
+ * in order to handle any whole-row Vars referencing the target.
*/
static Node *
expand_generated_columns_internal(Node *node, Relation rel, int rt_index,
- RangeTblEntry *rte, int result_relation)
+ RangeTblEntry *rte)
{
TupleDesc tupdesc;
@@ -4454,8 +4463,7 @@ expand_generated_columns_internal(Node *node, Relation rel, int rt_index,
Assert(list_length(tlist) > 0);
- node = ReplaceVarsFromTargetList(node, rt_index, 0, rte, tlist,
- result_relation,
+ node = ReplaceVarsFromTargetList(node, rt_index, 0, rte, tlist, 0,
REPLACEVARS_CHANGE_VARNO, rt_index,
NULL);
}
@@ -4484,7 +4492,7 @@ expand_generated_columns_in_expr(Node *node, Relation rel, int rt_index)
rte->rtekind = RTE_RELATION;
rte->relid = RelationGetRelid(rel);
- node = expand_generated_columns_internal(node, rel, rt_index, rte, 0);
+ node = expand_generated_columns_internal(node, rel, rt_index, rte);
}
return node;
diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c
index cd786aa4112..b7bce45fd01 100644
--- a/src/backend/rewrite/rewriteManip.c
+++ b/src/backend/rewrite/rewriteManip.c
@@ -921,7 +921,7 @@ IncrementVarSublevelsUp_rtable(List *rtable, int delta_sublevels_up,
/*
* SetVarReturningType - adjust Var nodes for a specified varreturningtype.
*
- * Find all Var nodes referring to the specified result relation in the given
+ * Find all Var nodes referring to the specified relation in the given
* expression and set their varreturningtype to the specified value.
*
* NOTE: although this has the form of a walker, we cheat and modify the
@@ -931,7 +931,7 @@ IncrementVarSublevelsUp_rtable(List *rtable, int delta_sublevels_up,
typedef struct
{
- int result_relation;
+ int target_varno;
int sublevels_up;
VarReturningType returning_type;
} SetVarReturningType_context;
@@ -945,7 +945,7 @@ SetVarReturningType_walker(Node *node, SetVarReturningType_context *context)
{
Var *var = (Var *) node;
- if (var->varno == context->result_relation &&
+ if (var->varno == context->target_varno &&
var->varlevelsup == context->sublevels_up)
var->varreturningtype = context->returning_type;
@@ -967,12 +967,12 @@ SetVarReturningType_walker(Node *node, SetVarReturningType_context *context)
}
static void
-SetVarReturningType(Node *node, int result_relation, int sublevels_up,
+SetVarReturningType(Node *node, int target_varno, int sublevels_up,
VarReturningType returning_type)
{
SetVarReturningType_context context;
- context.result_relation = result_relation;
+ context.target_varno = target_varno;
context.sublevels_up = sublevels_up;
context.returning_type = returning_type;
@@ -1744,14 +1744,25 @@ map_variable_attnos(Node *node,
* relation. This is needed to handle whole-row Vars referencing the target.
* We expand such Vars into RowExpr constructs.
*
- * In addition, for INSERT/UPDATE/DELETE/MERGE queries, the caller must
- * provide result_relation, the index of the result relation in the rewritten
- * query. This is needed to handle OLD/NEW RETURNING list Vars referencing
- * target_varno. When such Vars are expanded, their varreturningtype is
- * copied onto any replacement Vars referencing result_relation. In addition,
- * if the replacement expression from the targetlist is not simply a Var
- * referencing result_relation, it is wrapped in a ReturningExpr node (causing
- * the executor to return NULL if the OLD/NEW row doesn't exist).
+ * In addition, the caller should provide new_target_varno, which is needed to
+ * handle OLD/NEW/EXCLUDED RETURNING list Vars referencing target_varno. When
+ * such Vars are expanded, their varreturningtype is copied onto any
+ * replacement Vars that reference new_target_varno. In addition, if the
+ * replacement expression from the targetlist is not simply a Var referencing
+ * new_target_varno, it is wrapped in a ReturningExpr node (causing the
+ * executor to return NULL if the OLD/NEW/EXCLUDED row doesn't exist). The
+ * caller should set new_target_varno as follows:
+ *
+ * If the input node contains Vars from the RETURNING list of a query, and
+ * target_varno is the resultRelation of that query, then new_target_varno
+ * should be the (possibly new) resultRelation of the rewritten query.
+ *
+ * If the input node contains Vars from the RETURNING list of an INSERT ...
+ * ON CONFLICT DO UPDATE query, and target_varno is the index of the
+ * EXCLUDED pseudo-relation, then new_target_varno should be the (possibly
+ * new) index of the EXCLUDED pseudo-relation in the rewritten query.
+ *
+ * Otherwise, new_target_varno is not used.
*
* Note that ReplaceVarFromTargetList always generates the replacement
* expression with varlevelsup = 0. The caller is responsible for adjusting
@@ -1765,7 +1776,7 @@ typedef struct
{
RangeTblEntry *target_rte;
List *targetlist;
- int result_relation;
+ int new_target_varno;
ReplaceVarsNoMatchOption nomatch_option;
int nomatch_varno;
} ReplaceVarsFromTargetList_context;
@@ -1780,7 +1791,7 @@ ReplaceVarsFromTargetList_callback(Var *var,
newnode = ReplaceVarFromTargetList(var,
rcon->target_rte,
rcon->targetlist,
- rcon->result_relation,
+ rcon->new_target_varno,
rcon->nomatch_option,
rcon->nomatch_varno);
@@ -1795,7 +1806,7 @@ Node *
ReplaceVarFromTargetList(Var *var,
RangeTblEntry *target_rte,
List *targetlist,
- int result_relation,
+ int new_target_varno,
ReplaceVarsNoMatchOption nomatch_option,
int nomatch_varno)
{
@@ -1844,7 +1855,7 @@ ReplaceVarFromTargetList(Var *var,
field = ReplaceVarFromTargetList((Var *) field,
target_rte,
targetlist,
- result_relation,
+ new_target_varno,
nomatch_option,
nomatch_varno);
rowexpr->args = lappend(rowexpr->args, field);
@@ -1856,7 +1867,7 @@ ReplaceVarFromTargetList(Var *var,
ReturningExpr *rexpr = makeNode(ReturningExpr);
rexpr->retlevelsup = 0;
- rexpr->retold = (var->varreturningtype == VAR_RETURNING_OLD);
+ rexpr->retkind = (ReturningExprKind) var->varreturningtype;
rexpr->retexpr = (Expr *) rowexpr;
return (Node *) rexpr;
@@ -1925,28 +1936,28 @@ ReplaceVarFromTargetList(Var *var,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("NEW variables in ON UPDATE rules cannot reference columns that are part of a multiple assignment in the subject UPDATE command")));
- /* Handle any OLD/NEW RETURNING list Vars */
+ /* Handle any OLD/NEW/EXCLUDED RETURNING list Vars */
if (var->varreturningtype != VAR_RETURNING_DEFAULT)
{
/*
* Copy varreturningtype onto any Vars in the tlist item that
- * refer to result_relation (which had better be non-zero).
+ * refer to new_target_varno (which had better be non-zero).
*/
- if (result_relation == 0)
- elog(ERROR, "variable returning old/new found outside RETURNING list");
+ if (new_target_varno == 0)
+ elog(ERROR, "variable returning old/new/excluded found outside RETURNING list");
- SetVarReturningType((Node *) newnode, result_relation,
+ SetVarReturningType((Node *) newnode, new_target_varno,
0, var->varreturningtype);
/* Wrap it in a ReturningExpr, if needed, per comments above */
if (!IsA(newnode, Var) ||
- ((Var *) newnode)->varno != result_relation ||
+ ((Var *) newnode)->varno != new_target_varno ||
((Var *) newnode)->varlevelsup != 0)
{
ReturningExpr *rexpr = makeNode(ReturningExpr);
rexpr->retlevelsup = 0;
- rexpr->retold = (var->varreturningtype == VAR_RETURNING_OLD);
+ rexpr->retkind = (ReturningExprKind) var->varreturningtype;
rexpr->retexpr = newnode;
newnode = (Expr *) rexpr;
@@ -1962,7 +1973,7 @@ ReplaceVarsFromTargetList(Node *node,
int target_varno, int sublevels_up,
RangeTblEntry *target_rte,
List *targetlist,
- int result_relation,
+ int new_target_varno,
ReplaceVarsNoMatchOption nomatch_option,
int nomatch_varno,
bool *outer_hasSubLinks)
@@ -1971,7 +1982,7 @@ ReplaceVarsFromTargetList(Node *node,
context.target_rte = target_rte;
context.targetlist = targetlist;
- context.result_relation = result_relation;
+ context.new_target_varno = new_target_varno;
context.nomatch_option = nomatch_option;
context.nomatch_varno = nomatch_varno;
diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h
index 75366203706..1ce4d98afce 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -26,9 +26,9 @@ struct JsonConstructorExprState;
/* Bits in ExprState->flags (see also execnodes.h for public flag bits): */
/* expression's interpreter has been initialized */
-#define EEO_FLAG_INTERPRETER_INITIALIZED (1 << 5)
+#define EEO_FLAG_INTERPRETER_INITIALIZED (1 << 6)
/* jump-threading is in use */
-#define EEO_FLAG_DIRECT_THREADED (1 << 6)
+#define EEO_FLAG_DIRECT_THREADED (1 << 7)
/* Typical API for out-of-line evaluation subroutines */
typedef void (*ExecEvalSubroutine) (ExprState *state,
@@ -338,7 +338,8 @@ typedef struct ExprEvalStep
/* but it's just the normal (negative) attr number for SYSVAR */
int attnum;
Oid vartype; /* type OID of variable */
- VarReturningType varreturningtype; /* return old/new/default */
+ /* if not default, return old/new/excluded value */
+ VarReturningType varreturningtype;
} var;
/* for EEOP_WHOLEROW */
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 2492282213f..cac584a88d4 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -82,6 +82,8 @@ typedef Datum (*ExprStateEvalFunc) (struct ExprState *expression,
#define EEO_FLAG_OLD_IS_NULL (1 << 3)
/* NEW table row is NULL in RETURNING list */
#define EEO_FLAG_NEW_IS_NULL (1 << 4)
+/* INNER (EXCLUDED) table row is NULL in RETURNING list */
+#define EEO_FLAG_INNER_IS_NULL (1 << 5)
typedef struct ExprState
{
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 01510b01b64..0fc24910c6d 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -227,7 +227,9 @@ typedef struct Expr
* varreturningtype is used for Vars that refer to the target relation in the
* RETURNING list of data-modifying queries. The default behavior is to
* return old values for DELETE and new values for INSERT and UPDATE, but it
- * is also possible to explicitly request old or new values.
+ * is also possible to explicitly request old or new values. For INSERT ...
+ * ON CONFLICT DO UPDATE, varreturningtype is also used for Vars in the
+ * RETURNING list that refer to the EXCLUDED pseudo-relation.
*
* In the parser, varnosyn and varattnosyn are either identical to
* varno/varattno, or they specify the column's position in an aliased JOIN
@@ -256,6 +258,7 @@ typedef enum VarReturningType
VAR_RETURNING_DEFAULT, /* return OLD for DELETE, else return NEW */
VAR_RETURNING_OLD, /* return OLD for DELETE/UPDATE, else NULL */
VAR_RETURNING_NEW, /* return NEW for INSERT/UPDATE, else NULL */
+ VAR_RETURNING_EXCLUDED, /* return EXCLUDED on conflict, else NULL */
} VarReturningType;
typedef struct Var
@@ -2138,14 +2141,15 @@ typedef struct InferenceElem
} InferenceElem;
/*
- * ReturningExpr - return OLD/NEW.(expression) in RETURNING list
+ * ReturningExpr - return OLD/NEW/EXCLUDED.(expression) in RETURNING list
*
* This is used when updating an auto-updatable view and returning a view
* column that is not simply a Var referring to the base relation. In such
- * cases, OLD/NEW.viewcol can expand to an arbitrary expression, but the
- * result is required to be NULL if the OLD/NEW row doesn't exist. To handle
- * this, the rewriter wraps the expanded expression in a ReturningExpr, which
- * is equivalent to "CASE WHEN (OLD/NEW row exists) THEN (expr) ELSE NULL".
+ * cases, OLD/NEW/EXCLUDED.viewcol can expand to an arbitrary expression, but
+ * the result is required to be NULL if the OLD/NEW/EXCLUDED row doesn't
+ * exist. To handle this, the rewriter wraps the expanded expression in a
+ * ReturningExpr, which is equivalent to "CASE WHEN (OLD/NEW/EXCLUDED row
+ * exists) THEN (expr) ELSE NULL".
*
* A similar situation can arise when rewriting the RETURNING clause of a
* rule, which may also contain arbitrary expressions.
@@ -2153,11 +2157,19 @@ typedef struct InferenceElem
* ReturningExpr nodes never appear in a parsed Query --- they are only ever
* inserted by the rewriter and the planner.
*/
+typedef enum ReturningExprKind
+{
+ /* values here match non-default VarReturningType values */
+ RETURNING_OLD_EXPR = VAR_RETURNING_OLD,
+ RETURNING_NEW_EXPR = VAR_RETURNING_NEW,
+ RETURNING_EXCLUDED_EXPR = VAR_RETURNING_EXCLUDED,
+} ReturningExprKind;
+
typedef struct ReturningExpr
{
Expr xpr;
int retlevelsup; /* > 0 if it belongs to outer query */
- bool retold; /* true for OLD, false for NEW */
+ ReturningExprKind retkind; /* return OLD/NEW/EXCLUDED expression */
Expr *retexpr; /* expression to be returned */
} ReturningExpr;
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index f7d07c84542..1f0c21543b2 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -280,7 +280,8 @@ struct ParseState
* forbid LATERAL references to an UPDATE/DELETE target table.
*
* While processing the RETURNING clause, special namespace items are added to
- * refer to the OLD and NEW state of the result relation. These namespace
+ * refer to the OLD and NEW state of the result relation, and the EXCLUDED
+ * pseudo-relation for an INSERT ... ON CONFLICT DO UPDATE. These namespace
* items have p_returning_type set appropriately, for use when creating Vars.
* For convenience, this information is duplicated on each namespace column.
*
@@ -301,7 +302,7 @@ struct ParseNamespaceItem
bool p_cols_visible; /* Column names visible as unqualified refs? */
bool p_lateral_only; /* Is only visible to LATERAL expressions? */
bool p_lateral_ok; /* If so, does join type allow use? */
- VarReturningType p_returning_type; /* Is OLD/NEW for use in RETURNING? */
+ VarReturningType p_returning_type; /* for RETURNING OLD/NEW/EXCLUDED */
};
/*
@@ -332,7 +333,7 @@ struct ParseNamespaceColumn
Oid p_vartype; /* pg_type OID */
int32 p_vartypmod; /* type modifier value */
Oid p_varcollid; /* OID of collation, or InvalidOid */
- VarReturningType p_varreturningtype; /* for RETURNING OLD/NEW */
+ VarReturningType p_varreturningtype; /* for RETURNING OLD/NEW/EXCLUDED */
Index p_varnosyn; /* rangetable index of syntactic referent */
AttrNumber p_varattnosyn; /* attribute number of syntactic referent */
bool p_dontexpand; /* not included in star expansion */
diff --git a/src/include/rewrite/rewriteManip.h b/src/include/rewrite/rewriteManip.h
index 7c018f2a4e3..81a1493ad31 100644
--- a/src/include/rewrite/rewriteManip.h
+++ b/src/include/rewrite/rewriteManip.h
@@ -107,14 +107,14 @@ extern Node *map_variable_attnos(Node *node,
extern Node *ReplaceVarFromTargetList(Var *var,
RangeTblEntry *target_rte,
List *targetlist,
- int result_relation,
+ int new_target_varno,
ReplaceVarsNoMatchOption nomatch_option,
int nomatch_varno);
extern Node *ReplaceVarsFromTargetList(Node *node,
int target_varno, int sublevels_up,
RangeTblEntry *target_rte,
List *targetlist,
- int result_relation,
+ int new_target_varno,
ReplaceVarsNoMatchOption nomatch_option,
int nomatch_varno,
bool *outer_hasSubLinks);
diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out
index b815473f414..5a5a06e6a06 100644
--- a/src/test/regress/expected/arrays.out
+++ b/src/test/regress/expected/arrays.out
@@ -1398,20 +1398,30 @@ create temp table arr_pk_tbl (pk int4 primary key, f1 int[]);
insert into arr_pk_tbl values (1, '{1,2,3}');
insert into arr_pk_tbl values (1, '{3,4,5}') on conflict (pk)
do update set f1[1] = excluded.f1[1], f1[3] = excluded.f1[3]
- returning pk, f1;
- pk | f1
-----+---------
- 1 | {3,2,5}
+ returning pk, f1, excluded.f1 as "excluded f1";
+ pk | f1 | excluded f1
+----+---------+-------------
+ 1 | {3,2,5} | {3,4,5}
(1 row)
insert into arr_pk_tbl(pk, f1[1:2]) values (1, '{6,7,8}') on conflict (pk)
do update set f1[1] = excluded.f1[1],
f1[2] = excluded.f1[2],
f1[3] = excluded.f1[3]
- returning pk, f1;
- pk | f1
-----+------------
- 1 | {6,7,NULL}
+ returning pk, f1, excluded.f1 as "excluded f1";
+ pk | f1 | excluded f1
+----+------------+-------------
+ 1 | {6,7,NULL} | {6,7}
+(1 row)
+
+insert into arr_pk_tbl(pk, f1[2]) values (1, 10) on conflict (pk)
+ do update set f1[1] = excluded.f1[1],
+ f1[2] = excluded.f1[2],
+ f1[3] = excluded.f1[3]
+ returning pk, f1, excluded.f1 as "excluded f1";
+ pk | f1 | excluded f1
+----+----------------+-------------
+ 1 | {NULL,10,NULL} | [2:2]={10}
(1 row)
-- note: if above selects don't produce the expected tuple order,
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index f9b0c415cfd..c17b79a00a6 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -2123,7 +2123,14 @@ select * from inhpar;
-- Also check ON CONFLICT
insert into inhpar as i values (3), (7) on conflict (f1)
- do update set (f1, f2) = (select i.f1, i.f2 || '+');
+ do update set (f1, f2) = (select i.f1, i.f2 || '+')
+ returning old, new, excluded;
+ old | new | excluded
+--------+---------+----------
+ (3,3-) | (3,3-+) | (3,)
+ (7,7-) | (7,7-+) | (7,)
+(2 rows)
+
select * from inhpar order by f1; -- tuple order might be unstable here
f1 | f2
----+-----
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index fdd0f6c8f25..3a2c63f4479 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -249,13 +249,13 @@ 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);
--- Give good diagnostic message when EXCLUDED.* spuriously referenced from
--- RETURNING:
+-- EXCLUDED.* referenced from RETURNING:
insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit;
-ERROR: invalid reference to FROM-clause entry for table "excluded"
-LINE 1: ...y) do update set fruit = excluded.fruit RETURNING excluded.f...
- ^
-DETAIL: There is an entry for table "excluded", but it cannot be referenced from this part of the query.
+ fruit
+-------
+ Apple
+(1 row)
+
-- Only suggest <table>.* column when inference element misspelled:
insert into insertconflicttest values (1, 'Apple') on conflict (keyy) do update set fruit = excluded.fruit;
ERROR: column "keyy" does not exist
@@ -628,11 +628,17 @@ insert into excluded AS target values(1, '2') on conflict (key) do update set da
1 | 2
(1 row)
--- make sure excluded isn't a problem in returning clause
+-- error, ambiguous
insert into excluded values(1, '2') on conflict (key) do update set data = 3 RETURNING excluded.*;
- key | data
------+------
- 1 | 3
+ERROR: table reference "excluded" is ambiguous
+LINE 1: ...n conflict (key) do update set data = 3 RETURNING excluded.*...
+ ^
+-- ok, aliased
+insert into excluded AS target values(1, '2') on conflict (key) do update set data = 3
+RETURNING target.*, excluded.*;
+ key | data | key | data
+-----+------+-----+------
+ 1 | 3 | 1 | 2
(1 row)
-- clean up
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 095df0a670c..0c08cf120c6 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -3653,7 +3653,13 @@ insert into uv_iocu_tab values ('xyxyxy', 0);
create view uv_iocu_view as
select b, b+1 as c, a, '2.0'::text as two from uv_iocu_tab;
insert into uv_iocu_view (a, b) values ('xyxyxy', 1)
- on conflict (a) do update set b = uv_iocu_view.b;
+ on conflict (a) do update set b = uv_iocu_view.b
+ returning old.*, new.*, excluded.*;
+ b | c | a | two | b | c | a | two | b | c | a | two
+---+---+--------+-----+---+---+--------+-----+---+---+--------+-----
+ 0 | 1 | xyxyxy | 2.0 | 0 | 1 | xyxyxy | 2.0 | 1 | 2 | xyxyxy | 2.0
+(1 row)
+
select * from uv_iocu_tab;
a | b
--------+---
@@ -3661,7 +3667,13 @@ select * from uv_iocu_tab;
(1 row)
insert into uv_iocu_view (a, b) values ('xyxyxy', 1)
- on conflict (a) do update set b = excluded.b;
+ on conflict (a) do update set b = excluded.b
+ returning old.*, new.*, excluded.*;
+ b | c | a | two | b | c | a | two | b | c | a | two
+---+---+--------+-----+---+---+--------+-----+---+---+--------+-----
+ 0 | 1 | xyxyxy | 2.0 | 1 | 2 | xyxyxy | 2.0 | 1 | 2 | xyxyxy | 2.0
+(1 row)
+
select * from uv_iocu_tab;
a | b
--------+---
diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql
index 47d62c1d38d..47a5d7c9d72 100644
--- a/src/test/regress/sql/arrays.sql
+++ b/src/test/regress/sql/arrays.sql
@@ -426,12 +426,17 @@ create temp table arr_pk_tbl (pk int4 primary key, f1 int[]);
insert into arr_pk_tbl values (1, '{1,2,3}');
insert into arr_pk_tbl values (1, '{3,4,5}') on conflict (pk)
do update set f1[1] = excluded.f1[1], f1[3] = excluded.f1[3]
- returning pk, f1;
+ returning pk, f1, excluded.f1 as "excluded f1";
insert into arr_pk_tbl(pk, f1[1:2]) values (1, '{6,7,8}') on conflict (pk)
do update set f1[1] = excluded.f1[1],
f1[2] = excluded.f1[2],
f1[3] = excluded.f1[3]
- returning pk, f1;
+ returning pk, f1, excluded.f1 as "excluded f1";
+insert into arr_pk_tbl(pk, f1[2]) values (1, 10) on conflict (pk)
+ do update set f1[1] = excluded.f1[1],
+ f1[2] = excluded.f1[2],
+ f1[3] = excluded.f1[3]
+ returning pk, f1, excluded.f1 as "excluded f1";
-- note: if above selects don't produce the expected tuple order,
-- then you didn't get an indexscan plan, and something is busted.
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 699e8ac09c8..da1d418abfc 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -806,7 +806,8 @@ select * from inhpar;
-- Also check ON CONFLICT
insert into inhpar as i values (3), (7) on conflict (f1)
- do update set (f1, f2) = (select i.f1, i.f2 || '+');
+ do update set (f1, f2) = (select i.f1, i.f2 || '+')
+ returning old, new, excluded;
select * from inhpar order by f1; -- tuple order might be unstable here
drop table inhpar cascade;
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index 549c46452ec..8d1ca65715c 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -101,8 +101,7 @@ insert into insertconflicttest
values (1, 'Apple'), (2, 'Orange')
on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
--- Give good diagnostic message when EXCLUDED.* spuriously referenced from
--- RETURNING:
+-- EXCLUDED.* referenced from RETURNING:
insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit;
-- Only suggest <table>.* column when inference element misspelled:
@@ -374,8 +373,11 @@ insert into excluded values(1, '2') on conflict (key) do update set data = exclu
insert into excluded AS target values(1, '2') on conflict (key) do update set data = excluded.data RETURNING *;
-- ok, aliased
insert into excluded AS target values(1, '2') on conflict (key) do update set data = target.data RETURNING *;
--- make sure excluded isn't a problem in returning clause
+-- error, ambiguous
insert into excluded values(1, '2') on conflict (key) do update set data = 3 RETURNING excluded.*;
+-- ok, aliased
+insert into excluded AS target values(1, '2') on conflict (key) do update set data = 3
+RETURNING target.*, excluded.*;
-- clean up
drop table excluded;
diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql
index c071fffc116..03f57774edc 100644
--- a/src/test/regress/sql/updatable_views.sql
+++ b/src/test/regress/sql/updatable_views.sql
@@ -1858,10 +1858,12 @@ create view uv_iocu_view as
select b, b+1 as c, a, '2.0'::text as two from uv_iocu_tab;
insert into uv_iocu_view (a, b) values ('xyxyxy', 1)
- on conflict (a) do update set b = uv_iocu_view.b;
+ on conflict (a) do update set b = uv_iocu_view.b
+ returning old.*, new.*, excluded.*;
select * from uv_iocu_tab;
insert into uv_iocu_view (a, b) values ('xyxyxy', 1)
- on conflict (a) do update set b = excluded.b;
+ on conflict (a) do update set b = excluded.b
+ returning old.*, new.*, excluded.*;
select * from uv_iocu_tab;
-- OK to access view columns that are not present in underlying base
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 32d6e718adc..bf9336a86f1 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2563,6 +2563,7 @@ ReturnSetInfo
ReturnStmt
ReturningClause
ReturningExpr
+ReturningExprKind
ReturningOption
ReturningOptionKind
RevmapContents
--
2.43.0
