diff options
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 |