diff options
| author | Dean Rasheed <dean.a.rasheed@gmail.com> | 2024-03-17 13:58:59 +0000 |
|---|---|---|
| committer | Dean Rasheed <dean.a.rasheed@gmail.com> | 2024-03-17 13:58:59 +0000 |
| commit | c649fa24a42ba89bf5460c7110e4fc8eeca65959 (patch) | |
| tree | de7d51489c6c6fff56fddad66c0ced2aa427d6a5 /src | |
| parent | 6a004f1be87d34cfe51acf2fe2552d2b08a79273 (diff) | |
| download | postgresql-c649fa24a42ba89bf5460c7110e4fc8eeca65959.tar.gz postgresql-c649fa24a42ba89bf5460c7110e4fc8eeca65959.zip | |
Add RETURNING support to MERGE.
This allows a RETURNING clause to be appended to a MERGE query, to
return values based on each row inserted, updated, or deleted. As with
plain INSERT, UPDATE, and DELETE commands, the returned values are
based on the new contents of the target table for INSERT and UPDATE
actions, and on its old contents for DELETE actions. Values from the
source relation may also be returned.
As with INSERT/UPDATE/DELETE, the output of MERGE ... RETURNING may be
used as the source relation for other operations such as WITH queries
and COPY commands.
Additionally, a special function merge_action() is provided, which
returns 'INSERT', 'UPDATE', or 'DELETE', depending on the action
executed for each row. The merge_action() function can be used
anywhere in the RETURNING list, including in arbitrary expressions and
subqueries, but it is an error to use it anywhere outside of a MERGE
query's RETURNING list.
Dean Rasheed, reviewed by Isaac Morland, Vik Fearing, Alvaro Herrera,
Gurjeet Singh, Jian He, Jeff Davis, Merlin Moncure, Peter Eisentraut,
and Wolfgang Walther.
Discussion: http://postgr.es/m/CAEZATCWePEGQR5LBn-vD6SfeLZafzEm2Qy_L_Oky2=qw2w3Pzg@mail.gmail.com
Diffstat (limited to 'src')
50 files changed, 989 insertions, 171 deletions
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index 056b6733c8e..28cf8b040ab 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -281,12 +281,6 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt, { Assert(stmt->query); - /* MERGE is allowed by parser, but unimplemented. Reject for now */ - if (IsA(stmt->query, MergeStmt)) - ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("MERGE not supported in COPY")); - query = makeNode(RawStmt); query->stmt = stmt->query; query->stmt_location = stmt_location; diff --git a/src/backend/commands/copyto.c b/src/backend/commands/copyto.c index a6962e0cb77..ae8b2e36d72 100644 --- a/src/backend/commands/copyto.c +++ b/src/backend/commands/copyto.c @@ -503,7 +503,8 @@ BeginCopyTo(ParseState *pstate, { Assert(query->commandType == CMD_INSERT || query->commandType == CMD_UPDATE || - query->commandType == CMD_DELETE); + query->commandType == CMD_DELETE || + query->commandType == CMD_MERGE); ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c index ffd3ca4e619..728c8d5fda9 100644 --- a/src/backend/executor/execExpr.c +++ b/src/backend/executor/execExpr.c @@ -1107,6 +1107,19 @@ ExecInitExprRec(Expr *node, ExprState *state, break; } + case T_MergeSupportFunc: + { + /* must be in a MERGE, else something messed up */ + if (!state->parent || + !IsA(state->parent, ModifyTableState) || + ((ModifyTableState *) state->parent)->operation != CMD_MERGE) + elog(ERROR, "MergeSupportFunc found in non-merge plan node"); + + scratch.opcode = EEOP_MERGE_SUPPORT_FUNC; + ExprEvalPushStep(state, &scratch); + break; + } + case T_SubscriptingRef: { SubscriptingRef *sbsref = (SubscriptingRef *) node; diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c index 7c1f51e2e0a..a25ab7570fe 100644 --- a/src/backend/executor/execExprInterp.c +++ b/src/backend/executor/execExprInterp.c @@ -484,6 +484,7 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull) &&CASE_EEOP_AGGREF, &&CASE_EEOP_GROUPING_FUNC, &&CASE_EEOP_WINDOW_FUNC, + &&CASE_EEOP_MERGE_SUPPORT_FUNC, &&CASE_EEOP_SUBPLAN, &&CASE_EEOP_AGG_STRICT_DESERIALIZE, &&CASE_EEOP_AGG_DESERIALIZE, @@ -1592,6 +1593,14 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull) EEO_NEXT(); } + EEO_CASE(EEOP_MERGE_SUPPORT_FUNC) + { + /* too complex/uncommon for an inline implementation */ + ExecEvalMergeSupportFunc(state, op, econtext); + + EEO_NEXT(); + } + EEO_CASE(EEOP_SUBPLAN) { /* too complex for an inline implementation */ @@ -4246,6 +4255,45 @@ ExecEvalGroupingFunc(ExprState *state, ExprEvalStep *op) } /* + * ExecEvalMergeSupportFunc + * + * Returns information about the current MERGE action for its RETURNING list. + */ +void +ExecEvalMergeSupportFunc(ExprState *state, ExprEvalStep *op, + ExprContext *econtext) +{ + ModifyTableState *mtstate = castNode(ModifyTableState, state->parent); + MergeActionState *relaction = mtstate->mt_merge_action; + + if (!relaction) + elog(ERROR, "no merge action in progress"); + + /* Return the MERGE action ("INSERT", "UPDATE", or "DELETE") */ + switch (relaction->mas_action->commandType) + { + case CMD_INSERT: + *op->resvalue = PointerGetDatum(cstring_to_text_with_len("INSERT", 6)); + *op->resnull = false; + break; + case CMD_UPDATE: + *op->resvalue = PointerGetDatum(cstring_to_text_with_len("UPDATE", 6)); + *op->resnull = false; + break; + case CMD_DELETE: + *op->resvalue = PointerGetDatum(cstring_to_text_with_len("DELETE", 6)); + *op->resnull = false; + break; + case CMD_NOTHING: + elog(ERROR, "unexpected merge action: DO NOTHING"); + break; + default: + elog(ERROR, "unrecognized commandType: %d", + (int) relaction->mas_action->commandType); + } +} + +/* * Hand off evaluation of a subplan to nodeSubplan.c */ void diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.c index 8ca512db186..64fcb012dbe 100644 --- a/src/backend/executor/execPartition.c +++ b/src/backend/executor/execPartition.c @@ -609,8 +609,8 @@ ExecInitPartitionInfo(ModifyTableState *mtstate, EState *estate, * Build the RETURNING projection for the partition. Note that we didn't * build the returningList for partitions within the planner, but simple * translation of varattnos will suffice. This only occurs for the INSERT - * case or in the case of UPDATE tuple routing where we didn't find a - * result rel to reuse. + * case or in the case of UPDATE/MERGE tuple routing where we didn't find + * a result rel to reuse. */ if (node && node->returningLists != NIL) { @@ -619,12 +619,14 @@ ExecInitPartitionInfo(ModifyTableState *mtstate, EState *estate, List *returningList; /* See the comment above for WCO lists. */ - /* (except no RETURNING support for MERGE yet) */ Assert((node->operation == CMD_INSERT && list_length(node->returningLists) == 1 && list_length(node->resultRelations) == 1) || (node->operation == CMD_UPDATE && list_length(node->returningLists) == + list_length(node->resultRelations)) || + (node->operation == CMD_MERGE && + list_length(node->returningLists) == list_length(node->resultRelations))); /* diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c index 6e926ef4eed..539cd0a9991 100644 --- a/src/backend/executor/functions.c +++ b/src/backend/executor/functions.c @@ -1662,8 +1662,8 @@ check_sql_fn_retval(List *queryTreeLists, /* * If it's a plain SELECT, it returns whatever the targetlist says. - * Otherwise, if it's INSERT/UPDATE/DELETE with RETURNING, it returns - * that. Otherwise, the function return type must be VOID. + * Otherwise, if it's INSERT/UPDATE/DELETE/MERGE with RETURNING, it + * returns that. Otherwise, the function return type must be VOID. * * Note: eventually replace this test with QueryReturnsTuples? We'd need * a more general method of determining the output type, though. Also, it @@ -1681,7 +1681,8 @@ check_sql_fn_retval(List *queryTreeLists, else if (parse && (parse->commandType == CMD_INSERT || parse->commandType == CMD_UPDATE || - parse->commandType == CMD_DELETE) && + parse->commandType == CMD_DELETE || + parse->commandType == CMD_MERGE) && parse->returningList) { tlist = parse->returningList; @@ -1695,7 +1696,7 @@ check_sql_fn_retval(List *queryTreeLists, (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), errmsg("return type mismatch in function declared to return %s", format_type_be(rettype)), - errdetail("Function's final statement must be SELECT or INSERT/UPDATE/DELETE RETURNING."))); + errdetail("Function's final statement must be SELECT or INSERT/UPDATE/DELETE/MERGE RETURNING."))); return false; /* keep compiler quiet */ } diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 9351fbcf494..4abfe82f7fb 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -36,8 +36,7 @@ * RETURNING tuple after completing each row insert, update, or delete. * It must be called again to continue the operation. Without RETURNING, * we just loop within the node until all the work is done, then - * return NULL. This avoids useless call/return overhead. (MERGE does - * not support RETURNING.) + * return NULL. This avoids useless call/return overhead. */ #include "postgres.h" @@ -85,9 +84,6 @@ typedef struct ModifyTableContext */ TupleTableSlot *planSlot; - /* MERGE specific */ - MergeActionState *relaction; /* MERGE action in progress */ - /* * Information about the changes that were made concurrently to a tuple * being updated or deleted @@ -150,14 +146,15 @@ static TupleTableSlot *ExecMerge(ModifyTableContext *context, HeapTuple oldtuple, bool canSetTag); static void ExecInitMerge(ModifyTableState *mtstate, EState *estate); -static bool ExecMergeMatched(ModifyTableContext *context, - ResultRelInfo *resultRelInfo, - ItemPointer tupleid, - HeapTuple oldtuple, - bool canSetTag); -static void ExecMergeNotMatched(ModifyTableContext *context, - ResultRelInfo *resultRelInfo, - bool canSetTag); +static TupleTableSlot *ExecMergeMatched(ModifyTableContext *context, + ResultRelInfo *resultRelInfo, + ItemPointer tupleid, + HeapTuple oldtuple, + bool canSetTag, + bool *matched); +static TupleTableSlot *ExecMergeNotMatched(ModifyTableContext *context, + ResultRelInfo *resultRelInfo, + bool canSetTag); /* @@ -977,7 +974,7 @@ ExecInsert(ModifyTableContext *context, if (mtstate->operation == CMD_UPDATE) wco_kind = WCO_RLS_UPDATE_CHECK; else if (mtstate->operation == CMD_MERGE) - wco_kind = (context->relaction->mas_action->commandType == CMD_UPDATE) ? + wco_kind = (mtstate->mt_merge_action->mas_action->commandType == CMD_UPDATE) ? WCO_RLS_UPDATE_CHECK : WCO_RLS_INSERT_CHECK; else wco_kind = WCO_RLS_INSERT_CHECK; @@ -1831,7 +1828,7 @@ ExecCrossPartitionUpdate(ModifyTableContext *context, * additional rechecking, and might end up executing a different * action entirely). */ - if (context->relaction != NULL) + if (mtstate->operation == CMD_MERGE) return *tmresult == TM_Ok; else if (TupIsNull(epqslot)) return true; @@ -2072,7 +2069,7 @@ lreplace: * No luck, a retry is needed. If running MERGE, we do not do so * here; instead let it handle that on its own rules. */ - if (context->relaction != NULL) + if (context->mtstate->operation == CMD_MERGE) return result; /* @@ -2713,6 +2710,7 @@ static TupleTableSlot * ExecMerge(ModifyTableContext *context, ResultRelInfo *resultRelInfo, ItemPointer tupleid, HeapTuple oldtuple, bool canSetTag) { + TupleTableSlot *rslot = NULL; bool matched; /*----- @@ -2761,19 +2759,18 @@ ExecMerge(ModifyTableContext *context, ResultRelInfo *resultRelInfo, */ matched = tupleid != NULL || oldtuple != NULL; if (matched) - matched = ExecMergeMatched(context, resultRelInfo, tupleid, oldtuple, - canSetTag); + rslot = ExecMergeMatched(context, resultRelInfo, tupleid, oldtuple, + canSetTag, &matched); /* - * Either we were dealing with a NOT MATCHED tuple or ExecMergeMatched() - * returned "false", indicating the previously MATCHED tuple no longer - * matches. + * Deal with the NOT MATCHED case (either a NOT MATCHED tuple from the + * join, or a previously MATCHED tuple for which ExecMergeMatched() set + * "matched" to false, indicating that it no longer matches). */ if (!matched) - ExecMergeNotMatched(context, resultRelInfo, canSetTag); + rslot = ExecMergeNotMatched(context, resultRelInfo, canSetTag); - /* No RETURNING support yet */ - return NULL; + return rslot; } /* @@ -2785,8 +2782,8 @@ ExecMerge(ModifyTableContext *context, ResultRelInfo *resultRelInfo, * We start from the first WHEN MATCHED action and check if the WHEN quals * pass, if any. If the WHEN quals for the first action do not pass, we * check the second, then the third and so on. If we reach to the end, no - * action is taken and we return true, indicating that no further action is - * required for this tuple. + * action is taken and "matched" is set to true, indicating that no further + * action is required for this tuple. * * If we do find a qualifying action, then we attempt to execute the action. * @@ -2795,16 +2792,18 @@ ExecMerge(ModifyTableContext *context, ResultRelInfo *resultRelInfo, * with individual actions are evaluated by this routine via ExecQual, while * EvalPlanQual checks for the join quals. If EvalPlanQual tells us that the * updated tuple still passes the join quals, then we restart from the first - * action to look for a qualifying action. Otherwise, we return false -- - * meaning that a NOT MATCHED action must now be executed for the current - * source tuple. + * action to look for a qualifying action. Otherwise, "matched" is set to + * false -- meaning that a NOT MATCHED action must now be executed for the + * current source tuple. */ -static bool +static TupleTableSlot * ExecMergeMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo, - ItemPointer tupleid, HeapTuple oldtuple, bool canSetTag) + ItemPointer tupleid, HeapTuple oldtuple, bool canSetTag, + bool *matched) { ModifyTableState *mtstate = context->mtstate; - TupleTableSlot *newslot; + TupleTableSlot *newslot = NULL; + TupleTableSlot *rslot = NULL; EState *estate = context->estate; ExprContext *econtext = mtstate->ps.ps_ExprContext; bool isNull; @@ -2815,7 +2814,10 @@ ExecMergeMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo, * If there are no WHEN MATCHED actions, we are done. */ if (resultRelInfo->ri_matchedMergeAction == NIL) - return true; + { + *matched = true; + return NULL; + } /* * Make tuple and any needed join variables available to ExecQual and @@ -2905,12 +2907,15 @@ lmerge_matched: */ newslot = ExecProject(relaction->mas_proj); - context->relaction = relaction; + mtstate->mt_merge_action = relaction; if (!ExecUpdatePrologue(context, resultRelInfo, tupleid, NULL, newslot, &result)) { if (result == TM_Ok) - return true; /* "do nothing" */ + { + *matched = true; + return NULL; /* "do nothing" */ + } break; /* concurrent update/delete */ } @@ -2920,7 +2925,10 @@ lmerge_matched: { if (!ExecIRUpdateTriggers(estate, resultRelInfo, oldtuple, newslot)) - return true; /* "do nothing" */ + { + *matched = true; + return NULL; /* "do nothing" */ + } } else { @@ -2933,12 +2941,15 @@ lmerge_matched: * cross-partition update was done, then there's nothing * else for us to do --- the UPDATE has been turned into a * DELETE and an INSERT, and we must not perform any of - * the usual post-update tasks. + * the usual post-update tasks. Also, the RETURNING tuple + * (if any) has been projected, so we can just return + * that. */ if (updateCxt.crossPartUpdate) { mtstate->mt_merge_updated += 1; - return true; + *matched = true; + return context->cpUpdateReturningSlot; } } @@ -2951,12 +2962,15 @@ lmerge_matched: break; case CMD_DELETE: - context->relaction = relaction; + mtstate->mt_merge_action = relaction; if (!ExecDeletePrologue(context, resultRelInfo, tupleid, NULL, NULL, &result)) { if (result == TM_Ok) - return true; /* "do nothing" */ + { + *matched = true; + return NULL; /* "do nothing" */ + } break; /* concurrent update/delete */ } @@ -2966,7 +2980,10 @@ lmerge_matched: { if (!ExecIRDeleteTriggers(estate, resultRelInfo, oldtuple)) - return true; /* "do nothing" */ + { + *matched = true; + return NULL; /* "do nothing" */ + } } else result = ExecDeleteAct(context, resultRelInfo, tupleid, @@ -3046,7 +3063,8 @@ lmerge_matched: * If the tuple was already deleted, return to let caller * handle it under NOT MATCHED clauses. */ - return false; + *matched = false; + return NULL; case TM_Updated: { @@ -3092,13 +3110,19 @@ lmerge_matched: * NOT MATCHED actions. */ if (TupIsNull(epqslot)) - return false; + { + *matched = false; + return NULL; + } (void) ExecGetJunkAttribute(epqslot, resultRelInfo->ri_RowIdAttNo, &isNull); if (isNull) - return false; + { + *matched = false; + return NULL; + } /* * When a tuple was updated and migrated to @@ -3133,7 +3157,8 @@ lmerge_matched: * tuple already deleted; tell caller to run NOT * MATCHED actions */ - return false; + *matched = false; + return NULL; case TM_SelfModified: @@ -3161,13 +3186,13 @@ lmerge_matched: /* This shouldn't happen */ elog(ERROR, "attempted to update or delete invisible tuple"); - return false; + return NULL; default: /* see table_tuple_lock call in ExecDelete() */ elog(ERROR, "unexpected table_tuple_lock status: %u", result); - return false; + return NULL; } } @@ -3179,6 +3204,31 @@ lmerge_matched: break; } + /* Process RETURNING if present */ + if (resultRelInfo->ri_projectReturning) + { + switch (commandType) + { + case CMD_UPDATE: + rslot = ExecProcessReturning(resultRelInfo, newslot, + context->planSlot); + break; + + case CMD_DELETE: + rslot = ExecProcessReturning(resultRelInfo, + resultRelInfo->ri_oldTupleSlot, + context->planSlot); + break; + + case CMD_NOTHING: + break; + + default: + elog(ERROR, "unrecognized commandType: %d", + (int) commandType); + } + } + /* * We've activated one of the WHEN clauses, so we don't search * further. This is required behaviour, not an optimization. @@ -3189,19 +3239,22 @@ lmerge_matched: /* * Successfully executed an action or no qualifying action was found. */ - return true; + *matched = true; + + return rslot; } /* * Execute the first qualifying NOT MATCHED action. */ -static void +static TupleTableSlot * ExecMergeNotMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo, bool canSetTag) { ModifyTableState *mtstate = context->mtstate; ExprContext *econtext = mtstate->ps.ps_ExprContext; List *actionStates = NIL; + TupleTableSlot *rslot = NULL; ListCell *l; /* @@ -3251,10 +3304,10 @@ ExecMergeNotMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo, * so we don't need to map the tuple here. */ newslot = ExecProject(action->mas_proj); - context->relaction = action; + mtstate->mt_merge_action = action; - (void) ExecInsert(context, mtstate->rootResultRelInfo, newslot, - canSetTag, NULL, NULL); + rslot = ExecInsert(context, mtstate->rootResultRelInfo, + newslot, canSetTag, NULL, NULL); mtstate->mt_merge_inserted += 1; break; case CMD_NOTHING: @@ -3270,6 +3323,8 @@ ExecMergeNotMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo, */ break; } + + return rslot; } /* @@ -3732,9 +3787,17 @@ ExecModifyTable(PlanState *pstate) { EvalPlanQualSetSlot(&node->mt_epqstate, context.planSlot); - ExecMerge(&context, node->resultRelInfo, NULL, NULL, - node->canSetTag); - continue; /* no RETURNING support yet */ + slot = ExecMerge(&context, node->resultRelInfo, + NULL, NULL, node->canSetTag); + + /* + * If we got a RETURNING result, return it to the caller. + * We'll continue the work on next call. + */ + if (slot) + return slot; + + continue; /* continue with the next tuple */ } elog(ERROR, "tableoid is NULL"); @@ -3811,9 +3874,17 @@ ExecModifyTable(PlanState *pstate) { EvalPlanQualSetSlot(&node->mt_epqstate, context.planSlot); - ExecMerge(&context, node->resultRelInfo, NULL, NULL, - node->canSetTag); - continue; /* no RETURNING support yet */ + slot = ExecMerge(&context, node->resultRelInfo, + NULL, NULL, node->canSetTag); + + /* + * If we got a RETURNING result, return it to the + * caller. We'll continue the work on next call. + */ + if (slot) + return slot; + + continue; /* continue with the next tuple */ } elog(ERROR, "ctid is NULL"); @@ -3860,9 +3931,17 @@ ExecModifyTable(PlanState *pstate) { EvalPlanQualSetSlot(&node->mt_epqstate, context.planSlot); - ExecMerge(&context, node->resultRelInfo, NULL, NULL, - node->canSetTag); - continue; /* no RETURNING support yet */ + slot = ExecMerge(&context, node->resultRelInfo, + NULL, NULL, node->canSetTag); + + /* + * If we got a RETURNING result, return it to the + * caller. We'll continue the work on next call. + */ + if (slot) + return slot; + + continue; /* continue with the next tuple */ } elog(ERROR, "wholerow is NULL"); @@ -3924,7 +4003,6 @@ ExecModifyTable(PlanState *pstate) } slot = ExecGetUpdateNewTuple(resultRelInfo, context.planSlot, oldSlot); - context.relaction = NULL; /* Now apply the update. */ slot = ExecUpdate(&context, resultRelInfo, tupleid, oldtuple, diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c index 85857715bd8..a97a7e3bd4e 100644 --- a/src/backend/executor/spi.c +++ b/src/backend/executor/spi.c @@ -2032,6 +2032,8 @@ SPI_result_code_string(int code) return "SPI_OK_TD_REGISTER"; case SPI_OK_MERGE: return "SPI_OK_MERGE"; + case SPI_OK_MERGE_RETURNING: + return "SPI_OK_MERGE_RETURNING"; } /* Unrecognized code ... return something useful ... */ sprintf(buf, "Unrecognized SPI code %d", code); @@ -2885,7 +2887,10 @@ _SPI_pquery(QueryDesc *queryDesc, bool fire_triggers, uint64 tcount) res = SPI_OK_UPDATE; break; case CMD_MERGE: - res = SPI_OK_MERGE; + if (queryDesc->plannedstmt->hasReturning) + res = SPI_OK_MERGE_RETURNING; + else + res = SPI_OK_MERGE; break; default: return SPI_ERROR_OPUNKNOWN; diff --git a/src/backend/jit/llvm/llvmjit_expr.c b/src/backend/jit/llvm/llvmjit_expr.c index 0c448422e20..2a7d84f046b 100644 --- a/src/backend/jit/llvm/llvmjit_expr.c +++ b/src/backend/jit/llvm/llvmjit_expr.c @@ -1986,6 +1986,12 @@ llvm_compile_expr(ExprState *state) break; } + case EEOP_MERGE_SUPPORT_FUNC: + build_EvalXFunc(b, mod, "ExecEvalMergeSupportFunc", + v_state, op, v_econtext); + LLVMBuildBr(b, opblocks[opno + 1]); + break; + case EEOP_SUBPLAN: build_EvalXFunc(b, mod, "ExecEvalSubPlan", v_state, op, v_econtext); diff --git a/src/backend/jit/llvm/llvmjit_types.c b/src/backend/jit/llvm/llvmjit_types.c index 47c9daf4023..7d7aeee1f2b 100644 --- a/src/backend/jit/llvm/llvmjit_types.c +++ b/src/backend/jit/llvm/llvmjit_types.c @@ -155,6 +155,7 @@ void *referenced_functions[] = ExecEvalFuncExprFusage, ExecEvalFuncExprStrictFusage, ExecEvalGroupingFunc, + ExecEvalMergeSupportFunc, ExecEvalMinMax, ExecEvalNextValueExpr, ExecEvalParamExec, diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index 6ba8e732569..5b702809aec 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -66,6 +66,9 @@ exprType(const Node *expr) case T_WindowFunc: type = ((const WindowFunc *) expr)->wintype; break; + case T_MergeSupportFunc: + type = ((const MergeSupportFunc *) expr)->msftype; + break; case T_SubscriptingRef: type = ((const SubscriptingRef *) expr)->refrestype; break; @@ -809,6 +812,9 @@ exprCollation(const Node *expr) case T_WindowFunc: coll = ((const WindowFunc *) expr)->wincollid; break; + case T_MergeSupportFunc: + coll = ((const MergeSupportFunc *) expr)->msfcollid; + break; case T_SubscriptingRef: coll = ((const SubscriptingRef *) expr)->refcollid; break; @@ -1084,6 +1090,9 @@ exprSetCollation(Node *expr, Oid collation) case T_WindowFunc: ((WindowFunc *) expr)->wincollid = collation; break; + case T_MergeSupportFunc: + ((MergeSupportFunc *) expr)->msfcollid = collation; + break; case T_SubscriptingRef: ((SubscriptingRef *) expr)->refcollid = collation; break; @@ -1342,6 +1351,9 @@ exprLocation(const Node *expr) /* function name should always be the first thing */ loc = ((const WindowFunc *) expr)->location; break; + case T_MergeSupportFunc: + loc = ((const MergeSupportFunc *) expr)->location; + break; case T_SubscriptingRef: /* just use container argument's location */ loc = exprLocation((Node *) ((const SubscriptingRef *) expr)->refexpr); @@ -2034,6 +2046,7 @@ expression_tree_walker_impl(Node *node, case T_RangeTblRef: case T_SortGroupClause: case T_CTESearchClause: + case T_MergeSupportFunc: /* primitive node types with no expression subnodes */ break; case T_WithCheckOption: @@ -2868,6 +2881,7 @@ expression_tree_mutator_impl(Node *node, case T_RangeTblRef: case T_SortGroupClause: case T_CTESearchClause: + case T_MergeSupportFunc: return (Node *) copyObject(node); case T_WithCheckOption: { @@ -3832,6 +3846,7 @@ raw_expression_tree_walker_impl(Node *node, case T_ParamRef: case T_A_Const: case T_A_Star: + case T_MergeSupportFunc: /* primitive node types with no subnodes */ break; case T_Alias: @@ -4052,6 +4067,8 @@ raw_expression_tree_walker_impl(Node *node, return true; if (WALK(stmt->mergeWhenClauses)) return true; + if (WALK(stmt->returningList)) + return true; if (WALK(stmt->withClause)) return true; } diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c index 47e14723d2b..b9e0c960bd3 100644 --- a/src/backend/optimizer/plan/subselect.c +++ b/src/backend/optimizer/plan/subselect.c @@ -1855,7 +1855,8 @@ convert_EXISTS_to_ANY(PlannerInfo *root, Query *subselect, /* * Replace correlation vars (uplevel vars) with Params. * - * Uplevel PlaceHolderVars and aggregates are replaced, too. + * Uplevel PlaceHolderVars, aggregates, GROUPING() expressions, and + * MergeSupportFuncs are replaced, too. * * Note: it is critical that this runs immediately after SS_process_sublinks. * Since we do not recurse into the arguments of uplevel PHVs and aggregates, @@ -1909,6 +1910,12 @@ replace_correlation_vars_mutator(Node *node, PlannerInfo *root) if (((GroupingFunc *) node)->agglevelsup > 0) return (Node *) replace_outer_grouping(root, (GroupingFunc *) node); } + if (IsA(node, MergeSupportFunc)) + { + if (root->parse->commandType != CMD_MERGE) + return (Node *) replace_outer_merge_support(root, + (MergeSupportFunc *) node); + } return expression_tree_mutator(node, replace_correlation_vars_mutator, (void *) root); diff --git a/src/backend/optimizer/util/paramassign.c b/src/backend/optimizer/util/paramassign.c index a58da7c57ea..f461fedf194 100644 --- a/src/backend/optimizer/util/paramassign.c +++ b/src/backend/optimizer/util/paramassign.c @@ -308,6 +308,57 @@ replace_outer_grouping(PlannerInfo *root, GroupingFunc *grp) } /* + * Generate a Param node to replace the given MergeSupportFunc expression + * which is expected to be in the RETURNING list of an upper-level MERGE + * query. Record the need for the MergeSupportFunc in the proper upper-level + * root->plan_params. + */ +Param * +replace_outer_merge_support(PlannerInfo *root, MergeSupportFunc *msf) +{ + Param *retval; + PlannerParamItem *pitem; + Oid ptype = exprType((Node *) msf); + + Assert(root->parse->commandType != CMD_MERGE); + + /* + * The parser should have ensured that the MergeSupportFunc is in the + * RETURNING list of an upper-level MERGE query, so find that query. + */ + do + { + root = root->parent_root; + if (root == NULL) + elog(ERROR, "MergeSupportFunc found outside MERGE"); + } while (root->parse->commandType != CMD_MERGE); + + /* + * It does not seem worthwhile to try to de-duplicate references to outer + * MergeSupportFunc expressions. Just make a new slot every time. + */ + msf = copyObject(msf); + + pitem = makeNode(PlannerParamItem); + pitem->item = (Node *) msf; + pitem->paramId = list_length(root->glob->paramExecTypes); + root->glob->paramExecTypes = lappend_oid(root->glob->paramExecTypes, + ptype); + + root->plan_params = lappend(root->plan_params, pitem); + + retval = makeNode(Param); + retval->paramkind = PARAM_EXEC; + retval->paramid = pitem->paramId; + retval->paramtype = ptype; + retval->paramtypmod = -1; + retval->paramcollid = InvalidOid; + retval->location = msf->location; + + return retval; +} + +/* * Generate a Param node to replace the given Var, * which is expected to come from some upper NestLoop plan node. * Record the need for the Var in root->curOuterParams. diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 7f23d18b370..40ea19e6f10 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -72,7 +72,6 @@ static void determineRecursiveColTypes(ParseState *pstate, Node *larg, List *nrtargetlist); static Query *transformReturnStmt(ParseState *pstate, ReturnStmt *stmt); static Query *transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt); -static List *transformReturningList(ParseState *pstate, List *returningList); static Query *transformPLAssignStmt(ParseState *pstate, PLAssignStmt *stmt); static Query *transformDeclareCursorStmt(ParseState *pstate, @@ -551,7 +550,8 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt) qual = transformWhereClause(pstate, stmt->whereClause, EXPR_KIND_WHERE, "WHERE"); - qry->returningList = transformReturningList(pstate, stmt->returningList); + qry->returningList = transformReturningList(pstate, stmt->returningList, + EXPR_KIND_RETURNING); /* done building the range table and jointree */ qry->rtable = pstate->p_rtable; @@ -978,7 +978,8 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt) /* Process RETURNING, if any. */ if (stmt->returningList) qry->returningList = transformReturningList(pstate, - stmt->returningList); + stmt->returningList, + EXPR_KIND_RETURNING); /* done building the range table and jointree */ qry->rtable = pstate->p_rtable; @@ -2454,7 +2455,8 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt) qual = transformWhereClause(pstate, stmt->whereClause, EXPR_KIND_WHERE, "WHERE"); - qry->returningList = transformReturningList(pstate, stmt->returningList); + qry->returningList = transformReturningList(pstate, stmt->returningList, + EXPR_KIND_RETURNING); /* * Now we are done with SELECT-like processing, and can get on with @@ -2551,10 +2553,11 @@ transformUpdateTargetList(ParseState *pstate, List *origTlist) /* * transformReturningList - - * handle a RETURNING clause in INSERT/UPDATE/DELETE + * handle a RETURNING clause in INSERT/UPDATE/DELETE/MERGE */ -static List * -transformReturningList(ParseState *pstate, List *returningList) +List * +transformReturningList(ParseState *pstate, List *returningList, + ParseExprKind exprKind) { List *rlist; int save_next_resno; @@ -2571,7 +2574,7 @@ transformReturningList(ParseState *pstate, List *returningList) pstate->p_next_resno = 1; /* transform RETURNING identically to a SELECT targetlist */ - rlist = transformTargetList(pstate, returningList, EXPR_KIND_RETURNING); + rlist = transformTargetList(pstate, returningList, exprKind); /* * Complain if the nonempty tlist expanded to nothing (which is possible diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 3ad99fffe1d..39a801a1c38 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -733,7 +733,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOCKED LOGGED - MAPPING MATCH MATCHED MATERIALIZED MAXVALUE MERGE METHOD + MAPPING MATCH MATCHED MATERIALIZED MAXVALUE MERGE MERGE_ACTION METHOD MINUTE_P MINVALUE MODE MONTH_P MOVE NAME_P NAMES NATIONAL NATURAL NCHAR NEW NEXT NFC NFD NFKC NFKD NO NONE @@ -12374,6 +12374,7 @@ MergeStmt: USING table_ref ON a_expr merge_when_list + returning_clause { MergeStmt *m = makeNode(MergeStmt); @@ -12382,6 +12383,7 @@ MergeStmt: m->sourceRelation = $6; m->joinCondition = $8; m->mergeWhenClauses = $9; + m->returningList = $10; $$ = (Node *) m; } @@ -15795,6 +15797,14 @@ func_expr_common_subexpr: n->location = @1; $$ = (Node *) n; } + | MERGE_ACTION '(' ')' + { + MergeSupportFunc *m = makeNode(MergeSupportFunc); + + m->msftype = TEXTOID; + m->location = @1; + $$ = (Node *) m; + } ; @@ -17492,6 +17502,7 @@ col_name_keyword: | JSON_SCALAR | JSON_SERIALIZE | LEAST + | MERGE_ACTION | NATIONAL | NCHAR | NONE @@ -17881,6 +17892,7 @@ bare_label_keyword: | MATERIALIZED | MAXVALUE | MERGE + | MERGE_ACTION | METHOD | MINVALUE | MODE diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c index 9d151a880b8..bee7d8346a3 100644 --- a/src/backend/parser/parse_agg.c +++ b/src/backend/parser/parse_agg.c @@ -468,6 +468,7 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr) errkind = true; break; case EXPR_KIND_RETURNING: + case EXPR_KIND_MERGE_RETURNING: errkind = true; break; case EXPR_KIND_VALUES: @@ -915,6 +916,7 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc, errkind = true; break; case EXPR_KIND_RETURNING: + case EXPR_KIND_MERGE_RETURNING: errkind = true; break; case EXPR_KIND_VALUES: diff --git a/src/backend/parser/parse_cte.c b/src/backend/parser/parse_cte.c index 3c88c9abbae..6826d4f36a1 100644 --- a/src/backend/parser/parse_cte.c +++ b/src/backend/parser/parse_cte.c @@ -126,13 +126,6 @@ transformWithClause(ParseState *pstate, WithClause *withClause) CommonTableExpr *cte = (CommonTableExpr *) lfirst(lc); ListCell *rest; - /* MERGE is allowed by parser, but unimplemented. Reject for now */ - if (IsA(cte->ctequery, MergeStmt)) - ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("MERGE not supported in WITH query"), - parser_errposition(pstate, cte->location)); - for_each_cell(rest, withClause->ctes, lnext(withClause->ctes, lc)) { CommonTableExpr *cte2 = (CommonTableExpr *) lfirst(rest); @@ -153,7 +146,8 @@ transformWithClause(ParseState *pstate, WithClause *withClause) /* must be a data-modifying statement */ Assert(IsA(cte->ctequery, InsertStmt) || IsA(cte->ctequery, UpdateStmt) || - IsA(cte->ctequery, DeleteStmt)); + IsA(cte->ctequery, DeleteStmt) || + IsA(cte->ctequery, MergeStmt)); pstate->p_hasModifyingCTE = true; } diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 9300c7b9abc..d44b1f2ab2f 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -54,6 +54,7 @@ static Node *transformAExprDistinct(ParseState *pstate, A_Expr *a); static Node *transformAExprNullIf(ParseState *pstate, A_Expr *a); static Node *transformAExprIn(ParseState *pstate, A_Expr *a); static Node *transformAExprBetween(ParseState *pstate, A_Expr *a); +static Node *transformMergeSupportFunc(ParseState *pstate, MergeSupportFunc *f); static Node *transformBoolExpr(ParseState *pstate, BoolExpr *a); static Node *transformFuncCall(ParseState *pstate, FuncCall *fn); static Node *transformMultiAssignRef(ParseState *pstate, MultiAssignRef *maref); @@ -227,6 +228,11 @@ transformExprRecurse(ParseState *pstate, Node *expr) result = transformGroupingFunc(pstate, (GroupingFunc *) expr); break; + case T_MergeSupportFunc: + result = transformMergeSupportFunc(pstate, + (MergeSupportFunc *) expr); + break; + case T_NamedArgExpr: { NamedArgExpr *na = (NamedArgExpr *) expr; @@ -541,6 +547,7 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) case EXPR_KIND_LIMIT: case EXPR_KIND_OFFSET: case EXPR_KIND_RETURNING: + case EXPR_KIND_MERGE_RETURNING: case EXPR_KIND_VALUES: case EXPR_KIND_VALUES_SINGLE: case EXPR_KIND_CHECK_CONSTRAINT: @@ -1354,6 +1361,31 @@ transformAExprBetween(ParseState *pstate, A_Expr *a) } static Node * +transformMergeSupportFunc(ParseState *pstate, MergeSupportFunc *f) +{ + /* + * All we need to do is check that we're in the RETURNING list of a MERGE + * command. If so, we just return the node as-is. + */ + if (pstate->p_expr_kind != EXPR_KIND_MERGE_RETURNING) + { + ParseState *parent_pstate = pstate->parentParseState; + + while (parent_pstate && + parent_pstate->p_expr_kind != EXPR_KIND_MERGE_RETURNING) + parent_pstate = parent_pstate->parentParseState; + + if (!parent_pstate) + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("MERGE_ACTION() can only be used in the RETURNING list of a MERGE command"), + parser_errposition(pstate, f->location)); + } + + return (Node *) f; +} + +static Node * transformBoolExpr(ParseState *pstate, BoolExpr *a) { List *args = NIL; @@ -1767,6 +1799,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink) case EXPR_KIND_LIMIT: case EXPR_KIND_OFFSET: case EXPR_KIND_RETURNING: + case EXPR_KIND_MERGE_RETURNING: case EXPR_KIND_VALUES: case EXPR_KIND_VALUES_SINGLE: case EXPR_KIND_CYCLE_MARK: @@ -3115,6 +3148,7 @@ ParseExprKindName(ParseExprKind exprKind) case EXPR_KIND_OFFSET: return "OFFSET"; case EXPR_KIND_RETURNING: + case EXPR_KIND_MERGE_RETURNING: return "RETURNING"; case EXPR_KIND_VALUES: case EXPR_KIND_VALUES_SINGLE: diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index fdb3e6df338..0cbc950c95c 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -2599,6 +2599,7 @@ check_srf_call_placement(ParseState *pstate, Node *last_srf, int location) errkind = true; break; case EXPR_KIND_RETURNING: + case EXPR_KIND_MERGE_RETURNING: errkind = true; break; case EXPR_KIND_VALUES: diff --git a/src/backend/parser/parse_merge.c b/src/backend/parser/parse_merge.c index a7d8ba7e98c..04ed5e66dda 100644 --- a/src/backend/parser/parse_merge.c +++ b/src/backend/parser/parse_merge.c @@ -234,6 +234,10 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt) */ qry->jointree = makeFromExpr(pstate->p_joinlist, joinExpr); + /* Transform the RETURNING list, if any */ + qry->returningList = transformReturningList(pstate, stmt->returningList, + EXPR_KIND_MERGE_RETURNING); + /* * We now have a good query shape, so now look at the WHEN conditions and * action targetlists. @@ -391,9 +395,6 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt) qry->mergeActionList = mergeActionList; - /* RETURNING could potentially be added in the future, but not in SQL std */ - qry->returningList = NULL; - qry->hasTargetSRFs = false; qry->hasSubLinks = pstate->p_hasSubLinks; diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c index 6f5d9e26925..427b7325db8 100644 --- a/src/backend/parser/parse_relation.c +++ b/src/backend/parser/parse_relation.c @@ -2341,9 +2341,10 @@ addRangeTableEntryForCTE(ParseState *pstate, cte->cterefcount++; /* - * We throw error if the CTE is INSERT/UPDATE/DELETE without RETURNING. - * This won't get checked in case of a self-reference, but that's OK - * because data-modifying CTEs aren't allowed to be recursive anyhow. + * We throw error if the CTE is INSERT/UPDATE/DELETE/MERGE without + * RETURNING. This won't get checked in case of a self-reference, but + * that's OK because data-modifying CTEs aren't allowed to be recursive + * anyhow. */ if (IsA(cte->ctequery, Query)) { diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c index 5b92502b217..ea522b932b2 100644 --- a/src/backend/parser/parse_target.c +++ b/src/backend/parser/parse_target.c @@ -1820,6 +1820,10 @@ FigureColnameInternal(Node *node, char **name) /* make GROUPING() act like a regular function */ *name = "grouping"; return 2; + case T_MergeSupportFunc: + /* make MERGE_ACTION() act like a regular function */ + *name = "merge_action"; + return 2; case T_SubLink: switch (((SubLink *) node)->subLinkType) { diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index 7a46e8b3541..9fd05b15e73 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -3833,9 +3833,9 @@ RewriteQuery(Query *parsetree, List *rewrite_events, int orig_rt_length) ListCell *lc1; /* - * First, recursively process any insert/update/delete statements in WITH - * clauses. (We have to do this first because the WITH clauses may get - * copied into rule actions below.) + * First, recursively process any insert/update/delete/merge statements in + * WITH clauses. (We have to do this first because the WITH clauses may + * get copied into rule actions below.) */ foreach(lc1, parsetree->cteList) { @@ -3860,7 +3860,8 @@ RewriteQuery(Query *parsetree, List *rewrite_events, int orig_rt_length) if (!(ctequery->commandType == CMD_SELECT || ctequery->commandType == CMD_UPDATE || ctequery->commandType == CMD_INSERT || - ctequery->commandType == CMD_DELETE)) + ctequery->commandType == CMD_DELETE || + ctequery->commandType == CMD_MERGE)) { /* * Currently it could only be NOTIFY; this error message will diff --git a/src/backend/rewrite/rowsecurity.c b/src/backend/rewrite/rowsecurity.c index 450af27c7f5..59fd305dd7b 100644 --- a/src/backend/rewrite/rowsecurity.c +++ b/src/backend/rewrite/rowsecurity.c @@ -384,10 +384,10 @@ get_row_security_policies(Query *root, RangeTblEntry *rte, int rt_index, * on the final action we take. * * We already fetched the SELECT policies above, to check existing rows, - * but we must also check that new rows created by UPDATE actions are - * visible, if SELECT rights are required for this relation. We don't do - * this for INSERT actions, since an INSERT command would only do this - * check if it had a RETURNING list, and MERGE does not support RETURNING. + * but we must also check that new rows created by INSERT/UPDATE actions + * are visible, if SELECT rights are required. For INSERT actions, we only + * do this if RETURNING is specified, to be consistent with a plain INSERT + * command, which can only require SELECT rights when RETURNING is used. * * We don't push the UPDATE/DELETE USING quals to the RTE because we don't * really want to apply them while scanning the relation since we don't @@ -409,6 +409,8 @@ get_row_security_policies(Query *root, RangeTblEntry *rte, int rt_index, List *merge_delete_restrictive_policies; List *merge_insert_permissive_policies; List *merge_insert_restrictive_policies; + List *merge_select_permissive_policies = NIL; + List *merge_select_restrictive_policies = NIL; /* * Fetch the UPDATE policies and set them up to execute on the @@ -446,9 +448,6 @@ get_row_security_policies(Query *root, RangeTblEntry *rte, int rt_index, */ if (perminfo->requiredPerms & ACL_SELECT) { - List *merge_select_permissive_policies; - List *merge_select_restrictive_policies; - get_policies_for_relation(rel, CMD_SELECT, user_id, &merge_select_permissive_policies, &merge_select_restrictive_policies); @@ -497,6 +496,21 @@ get_row_security_policies(Query *root, RangeTblEntry *rte, int rt_index, withCheckOptions, hasSubLinks, false); + + /* + * Add ALL/SELECT policies as WCO_RLS_INSERT_CHECK WCOs, to ensure + * that the inserted row is visible when executing an INSERT action, + * if RETURNING is specified and SELECT rights are required for this + * relation. + */ + if (perminfo->requiredPerms & ACL_SELECT && root->returningList) + add_with_check_options(rel, rt_index, + WCO_RLS_INSERT_CHECK, + merge_select_permissive_policies, + merge_select_restrictive_policies, + withCheckOptions, + hasSubLinks, + true); } table_close(rel, NoLock); diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 83f86a42f79..fa66b8017ed 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -2138,11 +2138,10 @@ QueryReturnsTuples(Query *parsetree) case CMD_SELECT: /* returns tuples */ return true; - case CMD_MERGE: - return false; case CMD_INSERT: case CMD_UPDATE: case CMD_DELETE: + case CMD_MERGE: /* the forms with RETURNING return tuples */ if (parsetree->returningList) return true; diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 2231752613e..f2893d40861 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -7199,8 +7199,13 @@ get_merge_query_def(Query *query, deparse_context *context, appendStringInfoString(buf, "DO NOTHING"); } - /* No RETURNING support in MERGE yet */ - Assert(query->returningList == NIL); + /* Add RETURNING if present */ + if (query->returningList) + { + appendContextKeyword(context, " RETURNING", + -PRETTYINDENT_STD, PRETTYINDENT_STD, 1); + get_target_list(query->returningList, context, NULL, colNamesVisible); + } } @@ -8300,6 +8305,7 @@ isSimpleNode(Node *node, Node *parentNode, int prettyFlags) case T_Aggref: case T_GroupingFunc: case T_WindowFunc: + case T_MergeSupportFunc: case T_FuncExpr: case T_JsonConstructorExpr: /* function-like: name(..) or name[..] */ @@ -8654,6 +8660,10 @@ get_rule_expr(Node *node, deparse_context *context, get_windowfunc_expr((WindowFunc *) node, context); break; + case T_MergeSupportFunc: + appendStringInfoString(buf, "MERGE_ACTION()"); + break; + case T_SubscriptingRef: { SubscriptingRef *sbsref = (SubscriptingRef *) node; diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c index 76e01b02a39..2830bde4951 100644 --- a/src/bin/psql/common.c +++ b/src/bin/psql/common.c @@ -983,13 +983,17 @@ PrintQueryResult(PGresult *result, bool last, else success = true; - /* if it's INSERT/UPDATE/DELETE RETURNING, also print status */ + /* + * If it's INSERT/UPDATE/DELETE/MERGE RETURNING, also print + * status. + */ if (last || pset.show_all_results) { cmdstatus = PQcmdStatus(result); if (strncmp(cmdstatus, "INSERT", 6) == 0 || strncmp(cmdstatus, "UPDATE", 6) == 0 || - strncmp(cmdstatus, "DELETE", 6) == 0) + strncmp(cmdstatus, "DELETE", 6) == 0 || + strncmp(cmdstatus, "MERGE", 5) == 0) PrintQueryStatus(result, printStatusFout); } diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index aec49079c12..43a9a707094 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -57,6 +57,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202403171 +#define CATALOG_VERSION_NO 202403172 #endif diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h index a28ddcdd771..8953d767387 100644 --- a/src/include/executor/execExpr.h +++ b/src/include/executor/execExpr.h @@ -243,6 +243,7 @@ typedef enum ExprEvalOp EEOP_AGGREF, EEOP_GROUPING_FUNC, EEOP_WINDOW_FUNC, + EEOP_MERGE_SUPPORT_FUNC, EEOP_SUBPLAN, /* aggregation related nodes */ @@ -810,6 +811,8 @@ extern void ExecEvalJsonConstructor(ExprState *state, ExprEvalStep *op, ExprContext *econtext); extern void ExecEvalJsonIsPredicate(ExprState *state, ExprEvalStep *op); extern void ExecEvalGroupingFunc(ExprState *state, ExprEvalStep *op); +extern void ExecEvalMergeSupportFunc(ExprState *state, ExprEvalStep *op, + ExprContext *econtext); extern void ExecEvalSubPlan(ExprState *state, ExprEvalStep *op, ExprContext *econtext); extern void ExecEvalWholeRowVar(ExprState *state, ExprEvalStep *op, diff --git a/src/include/executor/spi.h b/src/include/executor/spi.h index c13e1f64fb1..48b87730ea0 100644 --- a/src/include/executor/spi.h +++ b/src/include/executor/spi.h @@ -97,6 +97,7 @@ typedef struct _SPI_plan *SPIPlanPtr; #define SPI_OK_REL_UNREGISTER 16 #define SPI_OK_TD_REGISTER 17 #define SPI_OK_MERGE 18 +#define SPI_OK_MERGE_RETURNING 19 #define SPI_OPT_NONATOMIC (1 << 0) diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 27614ab50fb..92593526725 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -1325,6 +1325,9 @@ typedef struct ModifyTableState /* Flags showing which subcommands are present INS/UPD/DEL/DO NOTHING */ int mt_merge_subcommands; + /* For MERGE, the action currently being executed */ + MergeActionState *mt_merge_action; + /* tuple counters for MERGE */ double mt_merge_inserted; double mt_merge_updated; diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 70a21df0fee..7b57fddf2d0 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1939,6 +1939,7 @@ typedef struct MergeStmt Node *sourceRelation; /* source relation */ Node *joinCondition; /* join condition between source and target */ List *mergeWhenClauses; /* list of MergeWhenClause(es) */ + List *returningList; /* list of expressions to return */ WithClause *withClause; /* WITH clause */ } MergeStmt; diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 4a154606d2b..8df8884001d 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -572,6 +572,27 @@ typedef struct WindowFunc } WindowFunc; /* + * MergeSupportFunc + * + * A MergeSupportFunc is a merge support function expression that can only + * appear in the RETURNING list of a MERGE command. It returns information + * about the currently executing merge action. + * + * Currently, the only supported function is MERGE_ACTION(), which returns the + * command executed ("INSERT", "UPDATE", or "DELETE"). + */ +typedef struct MergeSupportFunc +{ + Expr xpr; + /* type Oid of result */ + Oid msftype; + /* OID of collation, or InvalidOid if none */ + Oid msfcollid; + /* token location, or -1 if unknown */ + int location; +} MergeSupportFunc; + +/* * SubscriptingRef: describes a subscripting operation over a container * (array, etc). * diff --git a/src/include/optimizer/paramassign.h b/src/include/optimizer/paramassign.h index 501f728d714..4026b74fab4 100644 --- a/src/include/optimizer/paramassign.h +++ b/src/include/optimizer/paramassign.h @@ -20,6 +20,8 @@ extern Param *replace_outer_placeholdervar(PlannerInfo *root, PlaceHolderVar *phv); extern Param *replace_outer_agg(PlannerInfo *root, Aggref *agg); extern Param *replace_outer_grouping(PlannerInfo *root, GroupingFunc *grp); +extern Param *replace_outer_merge_support(PlannerInfo *root, + MergeSupportFunc *msf); extern Param *replace_nestloop_param_var(PlannerInfo *root, Var *var); extern Param *replace_nestloop_param_placeholdervar(PlannerInfo *root, PlaceHolderVar *phv); diff --git a/src/include/parser/analyze.h b/src/include/parser/analyze.h index 5ecdd109466..28b66fccb43 100644 --- a/src/include/parser/analyze.h +++ b/src/include/parser/analyze.h @@ -44,6 +44,8 @@ extern List *transformInsertRow(ParseState *pstate, List *exprlist, bool strip_indirection); extern List *transformUpdateTargetList(ParseState *pstate, List *origTlist); +extern List *transformReturningList(ParseState *pstate, List *returningList, + ParseExprKind exprKind); extern Query *transformTopLevelStmt(ParseState *pstate, RawStmt *parseTree); extern Query *transformStmt(ParseState *pstate, Node *parseTree); diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 2331acac091..099353469b5 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -266,6 +266,7 @@ PG_KEYWORD("matched", MATCHED, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("materialized", MATERIALIZED, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("maxvalue", MAXVALUE, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("merge", MERGE, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("merge_action", MERGE_ACTION, COL_NAME_KEYWORD, BARE_LABEL) PG_KEYWORD("method", METHOD, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("minute", MINUTE_P, UNRESERVED_KEYWORD, AS_LABEL) PG_KEYWORD("minvalue", MINVALUE, UNRESERVED_KEYWORD, BARE_LABEL) diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h index 99d65157365..5b781d87a9d 100644 --- a/src/include/parser/parse_node.h +++ b/src/include/parser/parse_node.h @@ -61,7 +61,8 @@ typedef enum ParseExprKind EXPR_KIND_DISTINCT_ON, /* DISTINCT ON */ EXPR_KIND_LIMIT, /* LIMIT */ EXPR_KIND_OFFSET, /* OFFSET */ - EXPR_KIND_RETURNING, /* RETURNING */ + EXPR_KIND_RETURNING, /* RETURNING in INSERT/UPDATE/DELETE */ + EXPR_KIND_MERGE_RETURNING, /* RETURNING in MERGE */ EXPR_KIND_VALUES, /* VALUES */ EXPR_KIND_VALUES_SINGLE, /* single-row VALUES (in INSERT only) */ EXPR_KIND_CHECK_CONSTRAINT, /* CHECK constraint for a table */ diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index ed51694428a..6947575b949 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -4267,9 +4267,9 @@ exec_stmt_execsql(PLpgSQL_execstate *estate, /* * If we have INTO, then we only need one row back ... but if we have INTO * STRICT or extra check too_many_rows, ask for two rows, so that we can - * verify the statement returns only one. INSERT/UPDATE/DELETE are always - * treated strictly. Without INTO, just run the statement to completion - * (tcount = 0). + * verify the statement returns only one. INSERT/UPDATE/DELETE/MERGE are + * always treated strictly. Without INTO, just run the statement to + * completion (tcount = 0). * * We could just ask for two rows always when using INTO, but there are * some cases where demanding the extra row costs significant time, eg by @@ -4307,10 +4307,11 @@ exec_stmt_execsql(PLpgSQL_execstate *estate, case SPI_OK_INSERT: case SPI_OK_UPDATE: case SPI_OK_DELETE: + case SPI_OK_MERGE: case SPI_OK_INSERT_RETURNING: case SPI_OK_UPDATE_RETURNING: case SPI_OK_DELETE_RETURNING: - case SPI_OK_MERGE: + case SPI_OK_MERGE_RETURNING: Assert(stmt->mod_stmt); exec_set_found(estate, (SPI_processed != 0)); break; @@ -4489,10 +4490,11 @@ exec_stmt_dynexecute(PLpgSQL_execstate *estate, case SPI_OK_INSERT: case SPI_OK_UPDATE: case SPI_OK_DELETE: + case SPI_OK_MERGE: case SPI_OK_INSERT_RETURNING: case SPI_OK_UPDATE_RETURNING: case SPI_OK_DELETE_RETURNING: - case SPI_OK_MERGE: + case SPI_OK_MERGE_RETURNING: case SPI_OK_UTILITY: case SPI_OK_REWRITTEN: break; diff --git a/src/pl/tcl/pltcl.c b/src/pl/tcl/pltcl.c index 6187e157817..18d14a2b982 100644 --- a/src/pl/tcl/pltcl.c +++ b/src/pl/tcl/pltcl.c @@ -2456,6 +2456,7 @@ pltcl_process_SPI_result(Tcl_Interp *interp, case SPI_OK_INSERT_RETURNING: case SPI_OK_DELETE_RETURNING: case SPI_OK_UPDATE_RETURNING: + case SPI_OK_MERGE_RETURNING: /* * Process the tuples we got diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out index 1a6f6ad43d1..07561f02407 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -124,20 +124,20 @@ ON tid = tid WHEN MATCHED THEN DO NOTHING; ERROR: name "target" specified more than once DETAIL: The name is used both as MERGE target table and data source. --- used in a CTE +-- used in a CTE without RETURNING WITH foo AS ( MERGE INTO target USING source ON (true) WHEN MATCHED THEN DELETE ) SELECT * FROM foo; -ERROR: MERGE not supported in WITH query -LINE 1: WITH foo AS ( - ^ --- used in COPY +ERROR: WITH query "foo" does not have a RETURNING clause +LINE 4: ) SELECT * FROM foo; + ^ +-- used in COPY without RETURNING COPY ( MERGE INTO target USING source ON (true) WHEN MATCHED THEN DELETE ) TO stdout; -ERROR: MERGE not supported in COPY +ERROR: COPY query must have a RETURNING clause -- unsupported relation types -- materialized view CREATE MATERIALIZED VIEW mv AS SELECT * FROM target; @@ -1255,7 +1255,7 @@ BEGIN; MERGE INTO sq_target USING v ON tid = sid -WHEN MATCHED AND tid > 2 THEN +WHEN MATCHED AND tid >= 2 THEN UPDATE SET balance = balance + delta WHEN NOT MATCHED THEN INSERT (balance, tid) VALUES (balance + delta, sid) @@ -1270,7 +1270,7 @@ INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10); MERGE INTO sq_target t USING v ON tid = sid -WHEN MATCHED AND tid > 2 THEN +WHEN MATCHED AND tid >= 2 THEN UPDATE SET balance = t.balance + delta WHEN NOT MATCHED THEN INSERT (balance, tid) VALUES (balance + delta, sid) @@ -1294,7 +1294,7 @@ WITH targq AS ( MERGE INTO sq_target t USING v ON tid = sid -WHEN MATCHED AND tid > 2 THEN +WHEN MATCHED AND tid >= 2 THEN UPDATE SET balance = t.balance + delta WHEN NOT MATCHED THEN INSERT (balance, tid) VALUES (balance + delta, sid) @@ -1302,21 +1302,207 @@ WHEN MATCHED AND tid < 2 THEN DELETE; ROLLBACK; -- RETURNING +SELECT * FROM sq_source ORDER BY sid; + delta | sid | balance +-------+-----+--------- + 10 | 1 | 0 + 20 | 2 | 0 + 40 | 4 | 0 +(3 rows) + +SELECT * FROM sq_target ORDER BY tid; + tid | balance +-----+--------- + 1 | 100 + 2 | 200 + 3 | 300 +(3 rows) + BEGIN; -INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10); +CREATE TABLE merge_actions(action text, abbrev text); +INSERT INTO merge_actions VALUES ('INSERT', 'ins'), ('UPDATE', 'upd'), ('DELETE', 'del'); MERGE INTO sq_target t -USING v +USING sq_source s ON tid = sid -WHEN MATCHED AND tid > 2 THEN +WHEN MATCHED AND tid >= 2 THEN UPDATE SET balance = t.balance + delta WHEN NOT MATCHED THEN - INSERT (balance, tid) VALUES (balance + delta, sid) + INSERT (balance, tid) VALUES (balance + delta, sid) WHEN MATCHED AND tid < 2 THEN - DELETE -RETURNING *; -ERROR: syntax error at or near "RETURNING" -LINE 10: RETURNING *; - ^ + DELETE +RETURNING (SELECT abbrev FROM merge_actions + WHERE action = merge_action()) AS action, + t.*, + CASE merge_action() + WHEN 'INSERT' THEN 'Inserted '||t + WHEN 'UPDATE' THEN 'Added '||delta||' to balance' + WHEN 'DELETE' THEN 'Removed '||t + END AS description; + action | tid | balance | description +--------+-----+---------+--------------------- + del | 1 | 100 | Removed (1,100) + upd | 2 | 220 | Added 20 to balance + ins | 4 | 40 | Inserted (4,40) +(3 rows) + +ROLLBACK; +-- error when using merge_action() outside MERGE +SELECT merge_action() FROM sq_target; +ERROR: MERGE_ACTION() can only be used in the RETURNING list of a MERGE command +LINE 1: SELECT merge_action() FROM sq_target; + ^ +UPDATE sq_target SET balance = balance + 1 RETURNING merge_action(); +ERROR: MERGE_ACTION() can only be used in the RETURNING list of a MERGE command +LINE 1: ...ATE sq_target SET balance = balance + 1 RETURNING merge_acti... + ^ +-- RETURNING in CTEs +CREATE TABLE sq_target_merge_log (tid integer NOT NULL, last_change text); +INSERT INTO sq_target_merge_log VALUES (1, 'Original value'); +BEGIN; +WITH m AS ( + MERGE INTO sq_target t + USING sq_source s + ON tid = sid + WHEN MATCHED AND tid >= 2 THEN + UPDATE SET balance = t.balance + delta + WHEN NOT MATCHED THEN + INSERT (balance, tid) VALUES (balance + delta, sid) + WHEN MATCHED AND tid < 2 THEN + DELETE + RETURNING merge_action() AS action, t.*, + CASE merge_action() + WHEN 'INSERT' THEN 'Inserted '||t + WHEN 'UPDATE' THEN 'Added '||delta||' to balance' + WHEN 'DELETE' THEN 'Removed '||t + END AS description +), m2 AS ( + MERGE INTO sq_target_merge_log l + USING m + ON l.tid = m.tid + WHEN MATCHED THEN + UPDATE SET last_change = description + WHEN NOT MATCHED THEN + INSERT VALUES (m.tid, description) + RETURNING action, merge_action() AS log_action, l.* +) +SELECT * FROM m2; + action | log_action | tid | last_change +--------+------------+-----+--------------------- + DELETE | UPDATE | 1 | Removed (1,100) + UPDATE | INSERT | 2 | Added 20 to balance + INSERT | INSERT | 4 | Inserted (4,40) +(3 rows) + +SELECT * FROM sq_target_merge_log ORDER BY tid; + tid | last_change +-----+--------------------- + 1 | Removed (1,100) + 2 | Added 20 to balance + 4 | Inserted (4,40) +(3 rows) + +ROLLBACK; +-- COPY (MERGE ... RETURNING) TO ... +BEGIN; +COPY ( + MERGE INTO sq_target t + USING sq_source s + ON tid = sid + WHEN MATCHED AND tid >= 2 THEN + UPDATE SET balance = t.balance + delta + WHEN NOT MATCHED THEN + INSERT (balance, tid) VALUES (balance + delta, sid) + WHEN MATCHED AND tid < 2 THEN + DELETE + RETURNING merge_action(), t.* +) TO stdout; +DELETE 1 100 +UPDATE 2 220 +INSERT 4 40 +ROLLBACK; +-- SQL function with MERGE ... RETURNING +BEGIN; +CREATE FUNCTION merge_into_sq_target(sid int, balance int, delta int, + OUT action text, OUT tid int, OUT new_balance int) +LANGUAGE sql AS +$$ + MERGE INTO sq_target t + USING (VALUES ($1, $2, $3)) AS v(sid, balance, delta) + ON tid = v.sid + WHEN MATCHED AND tid >= 2 THEN + UPDATE SET balance = t.balance + v.delta + WHEN NOT MATCHED THEN + INSERT (balance, tid) VALUES (v.balance + v.delta, v.sid) + WHEN MATCHED AND tid < 2 THEN + DELETE + RETURNING merge_action(), t.*; +$$; +SELECT m.* +FROM (VALUES (1, 0, 0), (3, 0, 20), (4, 100, 10)) AS v(sid, balance, delta), +LATERAL (SELECT action, tid, new_balance FROM merge_into_sq_target(sid, balance, delta)) m; + action | tid | new_balance +--------+-----+------------- + DELETE | 1 | 100 + UPDATE | 3 | 320 + INSERT | 4 | 110 +(3 rows) + +ROLLBACK; +-- SQL SRF with MERGE ... RETURNING +BEGIN; +CREATE FUNCTION merge_sq_source_into_sq_target() +RETURNS TABLE (action text, tid int, balance int) +LANGUAGE sql AS +$$ + MERGE INTO sq_target t + USING sq_source s + ON tid = sid + WHEN MATCHED AND tid >= 2 THEN + UPDATE SET balance = t.balance + delta + WHEN NOT MATCHED THEN + INSERT (balance, tid) VALUES (balance + delta, sid) + WHEN MATCHED AND tid < 2 THEN + DELETE + RETURNING merge_action(), t.*; +$$; +SELECT * FROM merge_sq_source_into_sq_target(); + action | tid | balance +--------+-----+--------- + DELETE | 1 | 100 + UPDATE | 2 | 220 + INSERT | 4 | 40 +(3 rows) + +ROLLBACK; +-- PL/pgSQL function with MERGE ... RETURNING ... INTO +BEGIN; +CREATE FUNCTION merge_into_sq_target(sid int, balance int, delta int, + OUT r_action text, OUT r_tid int, OUT r_balance int) +LANGUAGE plpgsql AS +$$ +BEGIN + MERGE INTO sq_target t + USING (VALUES ($1, $2, $3)) AS v(sid, balance, delta) + ON tid = v.sid + WHEN MATCHED AND tid >= 2 THEN + UPDATE SET balance = t.balance + v.delta + WHEN NOT MATCHED THEN + INSERT (balance, tid) VALUES (v.balance + v.delta, v.sid) + WHEN MATCHED AND tid < 2 THEN + DELETE + RETURNING merge_action(), t.* INTO r_action, r_tid, r_balance; +END; +$$; +SELECT m.* +FROM (VALUES (1, 0, 0), (3, 0, 20), (4, 100, 10)) AS v(sid, balance, delta), +LATERAL (SELECT r_action, r_tid, r_balance FROM merge_into_sq_target(sid, balance, delta)) m; + r_action | r_tid | r_balance +----------+-------+----------- + DELETE | 1 | 100 + UPDATE | 3 | 320 + INSERT | 4 | 110 +(3 rows) + ROLLBACK; -- EXPLAIN CREATE TABLE ex_mtarget (a int, b int) @@ -1563,7 +1749,7 @@ SELECT * FROM sq_target WHERE tid = 1; (1 row) ROLLBACK; -DROP TABLE sq_target, sq_source CASCADE; +DROP TABLE sq_target, sq_target_merge_log, sq_source CASCADE; NOTICE: drop cascades to view v CREATE TABLE pa_target (tid integer, balance float, val text) PARTITION BY LIST (tid); @@ -1689,6 +1875,32 @@ SELECT * FROM pa_target ORDER BY tid; (14 rows) ROLLBACK; +-- update partition key to partition not initially scanned +BEGIN; +MERGE INTO pa_target t + USING pa_source s + ON t.tid = s.sid AND t.tid = 1 + WHEN MATCHED THEN + UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge' + RETURNING merge_action(), t.*; + merge_action | tid | balance | val +--------------+-----+---------+-------------------------- + UPDATE | 2 | 110 | initial updated by merge +(1 row) + +SELECT * FROM pa_target ORDER BY tid; + tid | balance | val +-----+---------+-------------------------- + 2 | 110 | initial updated by merge + 3 | 300 | initial + 5 | 500 | initial + 7 | 700 | initial + 9 | 900 | initial + 11 | 1100 | initial + 13 | 1300 | initial +(7 rows) + +ROLLBACK; DROP TABLE pa_target CASCADE; -- The target table is partitioned in the same way, but this time by attaching -- partitions which have columns in different order, dropped columns etc. @@ -1938,7 +2150,21 @@ MERGE INTO pa_target t WHEN MATCHED THEN UPDATE SET balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN - INSERT VALUES (slogts::timestamp, sid, delta, 'inserted by merge'); + INSERT VALUES (slogts::timestamp, sid, delta, 'inserted by merge') + RETURNING merge_action(), t.*; + merge_action | logts | tid | balance | val +--------------+--------------------------+-----+---------+-------------------------- + UPDATE | Tue Jan 31 00:00:00 2017 | 1 | 110 | initial updated by merge + UPDATE | Tue Feb 28 00:00:00 2017 | 2 | 220 | initial updated by merge + INSERT | Sun Jan 15 00:00:00 2017 | 3 | 30 | inserted by merge + UPDATE | Tue Jan 31 00:00:00 2017 | 4 | 440 | initial updated by merge + UPDATE | Tue Feb 28 00:00:00 2017 | 5 | 550 | initial updated by merge + INSERT | Sun Jan 15 00:00:00 2017 | 6 | 60 | inserted by merge + UPDATE | Tue Jan 31 00:00:00 2017 | 7 | 770 | initial updated by merge + UPDATE | Tue Feb 28 00:00:00 2017 | 8 | 880 | initial updated by merge + INSERT | Sun Jan 15 00:00:00 2017 | 9 | 90 | inserted by merge +(9 rows) + SELECT * FROM pa_target ORDER BY tid; logts | tid | balance | val --------------------------+-----+---------+-------------------------- diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 4538f0c37d5..d507a2c8ca5 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -2302,6 +2302,35 @@ WHEN MATCHED THEN UPDATE SET dnotes = dnotes || ' notes added by merge8 ' WHEN NOT MATCHED THEN INSERT VALUES (13, 44, 1, 'regress_rls_bob', 'new manga'); +SELECT * FROM document WHERE did = 13; + did | cid | dlevel | dauthor | dtitle | dnotes +-----+-----+--------+---------+--------+-------- +(0 rows) + +-- but not OK if RETURNING is used +MERGE INTO document d +USING (SELECT 14 as sdid) s +ON did = s.sdid +WHEN MATCHED THEN + UPDATE SET dnotes = dnotes || ' notes added by merge9 ' +WHEN NOT MATCHED THEN + INSERT VALUES (14, 44, 1, 'regress_rls_bob', 'new manga') +RETURNING *; +ERROR: new row violates row-level security policy for table "document" +-- but OK if new row is visible +MERGE INTO document d +USING (SELECT 14 as sdid) s +ON did = s.sdid +WHEN MATCHED THEN + UPDATE SET dnotes = dnotes || ' notes added by merge10 ' +WHEN NOT MATCHED THEN + INSERT VALUES (14, 11, 1, 'regress_rls_bob', 'new novel') +RETURNING *; + sdid | did | cid | dlevel | dauthor | dtitle | dnotes +------+-----+-----+--------+-----------------+-----------+-------- + 14 | 14 | 11 | 1 | regress_rls_bob | new novel | +(1 row) + RESET SESSION AUTHORIZATION; -- drop the restrictive SELECT policy so that we can look at the -- final state of the table @@ -2325,7 +2354,8 @@ SELECT * FROM document; 12 | 11 | 1 | regress_rls_bob | another novel | 1 | 11 | 1 | regress_rls_bob | my first novel | notes added by merge2 notes added by merge3 notes added by merge4 notes added by merge7 13 | 44 | 1 | regress_rls_bob | new manga | -(15 rows) + 14 | 11 | 1 | regress_rls_bob | new novel | +(16 rows) -- -- ROLE/GROUP diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 0cd2c64fca1..84e359f6ed9 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -3638,7 +3638,7 @@ MERGE INTO rule_merge1 t USING (SELECT 1 AS a) s -- test deparsing CREATE TABLE sf_target(id int, data text, filling int[]); CREATE FUNCTION merge_sf_test() - RETURNS void + RETURNS TABLE(action text, a int, b text, id int, data text, filling int[]) LANGUAGE sql BEGIN ATOMIC MERGE INTO sf_target t @@ -3675,11 +3675,13 @@ WHEN NOT MATCHED VALUES (s.a, s.b, DEFAULT) WHEN NOT MATCHED THEN INSERT (filling[1], id) - VALUES (s.a, s.a); + VALUES (s.a, s.a) +RETURNING + merge_action() AS action, *; END; \sf merge_sf_test CREATE OR REPLACE FUNCTION public.merge_sf_test() - RETURNS void + RETURNS TABLE(action text, a integer, b text, id integer, data text, filling integer[]) LANGUAGE sql BEGIN ATOMIC MERGE INTO sf_target t @@ -3716,7 +3718,13 @@ BEGIN ATOMIC VALUES (s.a, s.b, DEFAULT) WHEN NOT MATCHED THEN INSERT (filling[1], id) - VALUES (s.a, s.a); + VALUES (s.a, s.a) + RETURNING MERGE_ACTION() AS action, + s.a, + s.b, + t.id, + t.data, + t.filling; END DROP FUNCTION merge_sf_test; DROP TABLE sf_target; diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out index 794cf9cf930..1062c341d8e 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -479,7 +479,15 @@ MERGE INTO rw_view1 t (2, 'ROW 2'), (3, 'ROW 3')) AS v(a,b) ON t.a = v.a WHEN MATCHED AND t.a <= 1 THEN UPDATE SET b = v.b WHEN MATCHED THEN DELETE - WHEN NOT MATCHED AND a > 0 THEN INSERT (a) VALUES (v.a); + WHEN NOT MATCHED AND a > 0 THEN INSERT (a) VALUES (v.a) + RETURNING merge_action(), v.*, t.*; + merge_action | a | b | a | b +--------------+---+-------+---+------------- + UPDATE | 1 | ROW 1 | 1 | ROW 1 + DELETE | 3 | ROW 3 | 3 | Row 3 + INSERT | 2 | ROW 2 | 2 | Unspecified +(3 rows) + SELECT * FROM base_tbl ORDER BY a; a | b ----+------------- @@ -631,7 +639,15 @@ MERGE INTO rw_view2 t USING (VALUES (3, 'R3'), (4, 'R4'), (5, 'R5')) AS v(a,b) ON aaa = v.a WHEN MATCHED AND aaa = 3 THEN DELETE WHEN MATCHED THEN UPDATE SET bbb = v.b - WHEN NOT MATCHED THEN INSERT (aaa) VALUES (v.a); + WHEN NOT MATCHED THEN INSERT (aaa) VALUES (v.a) + RETURNING merge_action(), v.*, t.*; + merge_action | a | b | aaa | bbb +--------------+---+----+-----+------------- + DELETE | 3 | R3 | 3 | Row 3 + UPDATE | 4 | R4 | 4 | R4 + INSERT | 5 | R5 | 5 | Unspecified +(3 rows) + SELECT * FROM rw_view2 ORDER BY aaa; aaa | bbb -----+------------- @@ -1071,7 +1087,15 @@ MERGE INTO rw_view2 t USING (SELECT x, 'R'||x FROM generate_series(0,3) x) AS s(a,b) ON t.a = s.a WHEN MATCHED AND t.a <= 1 THEN DELETE WHEN MATCHED THEN UPDATE SET b = s.b - WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b); + WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b) + RETURNING merge_action(), s.*, t.*; + merge_action | a | b | a | b +--------------+---+----+---+------- + DELETE | 1 | R1 | 1 | Row 1 + UPDATE | 2 | R2 | 2 | R2 + INSERT | 3 | R3 | 3 | R3 +(3 rows) + SELECT * FROM base_tbl ORDER BY a; a | b ----+-------- diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out index 7d796ea69cd..6de347b528d 100644 --- a/src/test/regress/expected/with.out +++ b/src/test/regress/expected/with.out @@ -3493,6 +3493,16 @@ SELECT * FROM t; ERROR: WITH query "t" does not have a RETURNING clause LINE 4: SELECT * FROM t; ^ +-- RETURNING tries to return its own output +WITH RECURSIVE t(action, a) AS ( + MERGE INTO y USING (VALUES (11)) v(a) ON y.a = v.a + WHEN NOT MATCHED THEN INSERT VALUES (v.a) + RETURNING merge_action(), (SELECT a FROM t) +) +SELECT * FROM t; +ERROR: recursive query "t" must not contain data-modifying statements +LINE 1: WITH RECURSIVE t(action, a) AS ( + ^ -- data-modifying WITH allowed only at the top level SELECT * FROM ( WITH t AS (UPDATE y SET a=a+1 RETURNING *) diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql index dbbba2a1cdd..875cf6f93d7 100644 --- a/src/test/regress/sql/merge.sql +++ b/src/test/regress/sql/merge.sql @@ -88,12 +88,12 @@ MERGE INTO target USING target ON tid = tid WHEN MATCHED THEN DO NOTHING; --- used in a CTE +-- used in a CTE without RETURNING WITH foo AS ( MERGE INTO target USING source ON (true) WHEN MATCHED THEN DELETE ) SELECT * FROM foo; --- used in COPY +-- used in COPY without RETURNING COPY ( MERGE INTO target USING source ON (true) WHEN MATCHED THEN DELETE @@ -817,7 +817,7 @@ BEGIN; MERGE INTO sq_target USING v ON tid = sid -WHEN MATCHED AND tid > 2 THEN +WHEN MATCHED AND tid >= 2 THEN UPDATE SET balance = balance + delta WHEN NOT MATCHED THEN INSERT (balance, tid) VALUES (balance + delta, sid) @@ -830,7 +830,7 @@ INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10); MERGE INTO sq_target t USING v ON tid = sid -WHEN MATCHED AND tid > 2 THEN +WHEN MATCHED AND tid >= 2 THEN UPDATE SET balance = t.balance + delta WHEN NOT MATCHED THEN INSERT (balance, tid) VALUES (balance + delta, sid) @@ -848,7 +848,7 @@ WITH targq AS ( MERGE INTO sq_target t USING v ON tid = sid -WHEN MATCHED AND tid > 2 THEN +WHEN MATCHED AND tid >= 2 THEN UPDATE SET balance = t.balance + delta WHEN NOT MATCHED THEN INSERT (balance, tid) VALUES (balance + delta, sid) @@ -857,18 +857,149 @@ WHEN MATCHED AND tid < 2 THEN ROLLBACK; -- RETURNING +SELECT * FROM sq_source ORDER BY sid; +SELECT * FROM sq_target ORDER BY tid; + BEGIN; -INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10); +CREATE TABLE merge_actions(action text, abbrev text); +INSERT INTO merge_actions VALUES ('INSERT', 'ins'), ('UPDATE', 'upd'), ('DELETE', 'del'); MERGE INTO sq_target t -USING v +USING sq_source s ON tid = sid -WHEN MATCHED AND tid > 2 THEN +WHEN MATCHED AND tid >= 2 THEN UPDATE SET balance = t.balance + delta WHEN NOT MATCHED THEN - INSERT (balance, tid) VALUES (balance + delta, sid) + INSERT (balance, tid) VALUES (balance + delta, sid) WHEN MATCHED AND tid < 2 THEN - DELETE -RETURNING *; + DELETE +RETURNING (SELECT abbrev FROM merge_actions + WHERE action = merge_action()) AS action, + t.*, + CASE merge_action() + WHEN 'INSERT' THEN 'Inserted '||t + WHEN 'UPDATE' THEN 'Added '||delta||' to balance' + WHEN 'DELETE' THEN 'Removed '||t + END AS description; +ROLLBACK; + +-- error when using merge_action() outside MERGE +SELECT merge_action() FROM sq_target; +UPDATE sq_target SET balance = balance + 1 RETURNING merge_action(); + +-- RETURNING in CTEs +CREATE TABLE sq_target_merge_log (tid integer NOT NULL, last_change text); +INSERT INTO sq_target_merge_log VALUES (1, 'Original value'); +BEGIN; +WITH m AS ( + MERGE INTO sq_target t + USING sq_source s + ON tid = sid + WHEN MATCHED AND tid >= 2 THEN + UPDATE SET balance = t.balance + delta + WHEN NOT MATCHED THEN + INSERT (balance, tid) VALUES (balance + delta, sid) + WHEN MATCHED AND tid < 2 THEN + DELETE + RETURNING merge_action() AS action, t.*, + CASE merge_action() + WHEN 'INSERT' THEN 'Inserted '||t + WHEN 'UPDATE' THEN 'Added '||delta||' to balance' + WHEN 'DELETE' THEN 'Removed '||t + END AS description +), m2 AS ( + MERGE INTO sq_target_merge_log l + USING m + ON l.tid = m.tid + WHEN MATCHED THEN + UPDATE SET last_change = description + WHEN NOT MATCHED THEN + INSERT VALUES (m.tid, description) + RETURNING action, merge_action() AS log_action, l.* +) +SELECT * FROM m2; +SELECT * FROM sq_target_merge_log ORDER BY tid; +ROLLBACK; + +-- COPY (MERGE ... RETURNING) TO ... +BEGIN; +COPY ( + MERGE INTO sq_target t + USING sq_source s + ON tid = sid + WHEN MATCHED AND tid >= 2 THEN + UPDATE SET balance = t.balance + delta + WHEN NOT MATCHED THEN + INSERT (balance, tid) VALUES (balance + delta, sid) + WHEN MATCHED AND tid < 2 THEN + DELETE + RETURNING merge_action(), t.* +) TO stdout; +ROLLBACK; + +-- SQL function with MERGE ... RETURNING +BEGIN; +CREATE FUNCTION merge_into_sq_target(sid int, balance int, delta int, + OUT action text, OUT tid int, OUT new_balance int) +LANGUAGE sql AS +$$ + MERGE INTO sq_target t + USING (VALUES ($1, $2, $3)) AS v(sid, balance, delta) + ON tid = v.sid + WHEN MATCHED AND tid >= 2 THEN + UPDATE SET balance = t.balance + v.delta + WHEN NOT MATCHED THEN + INSERT (balance, tid) VALUES (v.balance + v.delta, v.sid) + WHEN MATCHED AND tid < 2 THEN + DELETE + RETURNING merge_action(), t.*; +$$; +SELECT m.* +FROM (VALUES (1, 0, 0), (3, 0, 20), (4, 100, 10)) AS v(sid, balance, delta), +LATERAL (SELECT action, tid, new_balance FROM merge_into_sq_target(sid, balance, delta)) m; +ROLLBACK; + +-- SQL SRF with MERGE ... RETURNING +BEGIN; +CREATE FUNCTION merge_sq_source_into_sq_target() +RETURNS TABLE (action text, tid int, balance int) +LANGUAGE sql AS +$$ + MERGE INTO sq_target t + USING sq_source s + ON tid = sid + WHEN MATCHED AND tid >= 2 THEN + UPDATE SET balance = t.balance + delta + WHEN NOT MATCHED THEN + INSERT (balance, tid) VALUES (balance + delta, sid) + WHEN MATCHED AND tid < 2 THEN + DELETE + RETURNING merge_action(), t.*; +$$; +SELECT * FROM merge_sq_source_into_sq_target(); +ROLLBACK; + +-- PL/pgSQL function with MERGE ... RETURNING ... INTO +BEGIN; +CREATE FUNCTION merge_into_sq_target(sid int, balance int, delta int, + OUT r_action text, OUT r_tid int, OUT r_balance int) +LANGUAGE plpgsql AS +$$ +BEGIN + MERGE INTO sq_target t + USING (VALUES ($1, $2, $3)) AS v(sid, balance, delta) + ON tid = v.sid + WHEN MATCHED AND tid >= 2 THEN + UPDATE SET balance = t.balance + v.delta + WHEN NOT MATCHED THEN + INSERT (balance, tid) VALUES (v.balance + v.delta, v.sid) + WHEN MATCHED AND tid < 2 THEN + DELETE + RETURNING merge_action(), t.* INTO r_action, r_tid, r_balance; +END; +$$; +SELECT m.* +FROM (VALUES (1, 0, 0), (3, 0, 20), (4, 100, 10)) AS v(sid, balance, delta), +LATERAL (SELECT r_action, r_tid, r_balance FROM merge_into_sq_target(sid, balance, delta)) m; ROLLBACK; -- EXPLAIN @@ -984,7 +1115,7 @@ WHEN MATCHED THEN SELECT * FROM sq_target WHERE tid = 1; ROLLBACK; -DROP TABLE sq_target, sq_source CASCADE; +DROP TABLE sq_target, sq_target_merge_log, sq_source CASCADE; CREATE TABLE pa_target (tid integer, balance float, val text) PARTITION BY LIST (tid); @@ -1051,6 +1182,17 @@ SELECT merge_func(); SELECT * FROM pa_target ORDER BY tid; ROLLBACK; +-- update partition key to partition not initially scanned +BEGIN; +MERGE INTO pa_target t + USING pa_source s + ON t.tid = s.sid AND t.tid = 1 + WHEN MATCHED THEN + UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge' + RETURNING merge_action(), t.*; +SELECT * FROM pa_target ORDER BY tid; +ROLLBACK; + DROP TABLE pa_target CASCADE; -- The target table is partitioned in the same way, but this time by attaching @@ -1227,7 +1369,8 @@ MERGE INTO pa_target t WHEN MATCHED THEN UPDATE SET balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN - INSERT VALUES (slogts::timestamp, sid, delta, 'inserted by merge'); + INSERT VALUES (slogts::timestamp, sid, delta, 'inserted by merge') + RETURNING merge_action(), t.*; SELECT * FROM pa_target ORDER BY tid; ROLLBACK; diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql index dec7340538c..1d5ed0a647b 100644 --- a/src/test/regress/sql/rowsecurity.sql +++ b/src/test/regress/sql/rowsecurity.sql @@ -992,6 +992,27 @@ WHEN MATCHED THEN UPDATE SET dnotes = dnotes || ' notes added by merge8 ' WHEN NOT MATCHED THEN INSERT VALUES (13, 44, 1, 'regress_rls_bob', 'new manga'); +SELECT * FROM document WHERE did = 13; + +-- but not OK if RETURNING is used +MERGE INTO document d +USING (SELECT 14 as sdid) s +ON did = s.sdid +WHEN MATCHED THEN + UPDATE SET dnotes = dnotes || ' notes added by merge9 ' +WHEN NOT MATCHED THEN + INSERT VALUES (14, 44, 1, 'regress_rls_bob', 'new manga') +RETURNING *; + +-- but OK if new row is visible +MERGE INTO document d +USING (SELECT 14 as sdid) s +ON did = s.sdid +WHEN MATCHED THEN + UPDATE SET dnotes = dnotes || ' notes added by merge10 ' +WHEN NOT MATCHED THEN + INSERT VALUES (14, 11, 1, 'regress_rls_bob', 'new novel') +RETURNING *; RESET SESSION AUTHORIZATION; -- drop the restrictive SELECT policy so that we can look at the diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql index 6924012a10c..27340bacbc5 100644 --- a/src/test/regress/sql/rules.sql +++ b/src/test/regress/sql/rules.sql @@ -1294,7 +1294,7 @@ MERGE INTO rule_merge1 t USING (SELECT 1 AS a) s CREATE TABLE sf_target(id int, data text, filling int[]); CREATE FUNCTION merge_sf_test() - RETURNS void + RETURNS TABLE(action text, a int, b text, id int, data text, filling int[]) LANGUAGE sql BEGIN ATOMIC MERGE INTO sf_target t @@ -1331,7 +1331,9 @@ WHEN NOT MATCHED VALUES (s.a, s.b, DEFAULT) WHEN NOT MATCHED THEN INSERT (filling[1], id) - VALUES (s.a, s.a); + VALUES (s.a, s.a) +RETURNING + merge_action() AS action, *; END; \sf merge_sf_test diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql index ae11e46da28..afdf331fed6 100644 --- a/src/test/regress/sql/updatable_views.sql +++ b/src/test/regress/sql/updatable_views.sql @@ -175,7 +175,8 @@ MERGE INTO rw_view1 t (2, 'ROW 2'), (3, 'ROW 3')) AS v(a,b) ON t.a = v.a WHEN MATCHED AND t.a <= 1 THEN UPDATE SET b = v.b WHEN MATCHED THEN DELETE - WHEN NOT MATCHED AND a > 0 THEN INSERT (a) VALUES (v.a); + WHEN NOT MATCHED AND a > 0 THEN INSERT (a) VALUES (v.a) + RETURNING merge_action(), v.*, t.*; SELECT * FROM base_tbl ORDER BY a; EXPLAIN (costs off) UPDATE rw_view1 SET a=6 WHERE a=5; @@ -246,7 +247,8 @@ MERGE INTO rw_view2 t USING (VALUES (3, 'R3'), (4, 'R4'), (5, 'R5')) AS v(a,b) ON aaa = v.a WHEN MATCHED AND aaa = 3 THEN DELETE WHEN MATCHED THEN UPDATE SET bbb = v.b - WHEN NOT MATCHED THEN INSERT (aaa) VALUES (v.a); + WHEN NOT MATCHED THEN INSERT (aaa) VALUES (v.a) + RETURNING merge_action(), v.*, t.*; SELECT * FROM rw_view2 ORDER BY aaa; EXPLAIN (costs off) UPDATE rw_view2 SET aaa=5 WHERE aaa=4; @@ -458,7 +460,8 @@ MERGE INTO rw_view2 t USING (SELECT x, 'R'||x FROM generate_series(0,3) x) AS s(a,b) ON t.a = s.a WHEN MATCHED AND t.a <= 1 THEN DELETE WHEN MATCHED THEN UPDATE SET b = s.b - WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b); + WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b) + RETURNING merge_action(), s.*, t.*; SELECT * FROM base_tbl ORDER BY a; EXPLAIN (costs off) UPDATE rw_view2 SET a=3 WHERE a=2; diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql index f8a213e3570..037bc0a511b 100644 --- a/src/test/regress/sql/with.sql +++ b/src/test/regress/sql/with.sql @@ -1591,6 +1591,14 @@ WITH t AS ( ) SELECT * FROM t; +-- RETURNING tries to return its own output +WITH RECURSIVE t(action, a) AS ( + MERGE INTO y USING (VALUES (11)) v(a) ON y.a = v.a + WHEN NOT MATCHED THEN INSERT VALUES (v.a) + RETURNING merge_action(), (SELECT a FROM t) +) +SELECT * FROM t; + -- data-modifying WITH allowed only at the top level SELECT * FROM ( WITH t AS (UPDATE y SET a=a+1 RETURNING *) diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 9ba7a9a56c0..6ca93b1e478 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -1578,6 +1578,7 @@ MergeJoinState MergePath MergeScanSelCache MergeStmt +MergeSupportFunc MergeWhenClause MetaCommand MinMaxAggInfo |