From fe79b310c287bf4bc6b37e25d53053b342d05767 Mon Sep 17 00:00:00 2001 From: Dean Rasheed 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 .* 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
.* 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