diff options
33 files changed, 1226 insertions, 360 deletions
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml index f8f83d463d4..380d0c9e805 100644 --- a/doc/src/sgml/mvcc.sgml +++ b/doc/src/sgml/mvcc.sgml @@ -394,10 +394,14 @@ conditions for each action are re-evaluated on the updated version of the row, starting from the first action, even if the action that had originally matched appears later in the list of actions. - On the other hand, if the row is concurrently updated or deleted so - that the join condition fails, then <command>MERGE</command> will - evaluate the condition's <literal>NOT MATCHED</literal> actions next, - and execute the first one that succeeds. + On the other hand, if the row is concurrently updated so that the join + condition fails, then <command>MERGE</command> will evaluate the + command's <literal>NOT MATCHED BY SOURCE</literal> and + <literal>NOT MATCHED [BY TARGET]</literal> actions next, and execute + the first one of each kind that succeeds. + If the row is concurrently deleted, then <command>MERGE</command> + will evaluate the command's <literal>NOT MATCHED [BY TARGET]</literal> + actions, and execute the first one that succeeds. If <command>MERGE</command> attempts an <command>INSERT</command> and a unique index is present and a duplicate row is concurrently inserted, then a uniqueness violation error is raised; diff --git a/doc/src/sgml/ref/merge.sgml b/doc/src/sgml/ref/merge.sgml index 44e5ec080d2..f63df90c162 100644 --- a/doc/src/sgml/ref/merge.sgml +++ b/doc/src/sgml/ref/merge.sgml @@ -34,7 +34,8 @@ USING <replaceable class="parameter">data_source</replaceable> ON <replaceable c <phrase>and <replaceable class="parameter">when_clause</replaceable> is:</phrase> { WHEN MATCHED [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_update</replaceable> | <replaceable class="parameter">merge_delete</replaceable> | DO NOTHING } | - WHEN NOT MATCHED [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_insert</replaceable> | DO NOTHING } } + WHEN NOT MATCHED BY SOURCE [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_update</replaceable> | <replaceable class="parameter">merge_delete</replaceable> | DO NOTHING } | + WHEN NOT MATCHED [ BY TARGET ] [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_insert</replaceable> | DO NOTHING } } <phrase>and <replaceable class="parameter">merge_insert</replaceable> is:</phrase> @@ -73,7 +74,9 @@ DELETE from <replaceable class="parameter">data_source</replaceable> to the target table producing zero or more candidate change rows. For each candidate change - row, the status of <literal>MATCHED</literal> or <literal>NOT MATCHED</literal> + row, the status of <literal>MATCHED</literal>, + <literal>NOT MATCHED BY SOURCE</literal>, + or <literal>NOT MATCHED [BY TARGET]</literal> is set just once, after which <literal>WHEN</literal> clauses are evaluated in the order specified. For each candidate change row, the first clause to evaluate as true is executed. No more than one <literal>WHEN</literal> @@ -257,6 +260,16 @@ DELETE only reference the target table's columns can affect which action is taken, often in surprising ways. </para> + <para> + If both <literal>WHEN NOT MATCHED BY SOURCE</literal> and + <literal>WHEN NOT MATCHED [BY TARGET]</literal> clauses are specified, + the <command>MERGE</command> command will perform a <literal>FULL</literal> + join between <replaceable class="parameter">data_source</replaceable> + and the target table. For this to work, at least one + <replaceable class="parameter">join_condition</replaceable> subexpression + must use an operator that can support a hash join, or all of the + subexpressions must use operators that can support a merge join. + </para> </warning> </listitem> </varlistentry> @@ -268,18 +281,40 @@ DELETE At least one <literal>WHEN</literal> clause is required. </para> <para> + The <literal>WHEN</literal> clause may specify <literal>WHEN MATCHED</literal>, + <literal>WHEN NOT MATCHED BY SOURCE</literal>, or + <literal>WHEN NOT MATCHED [BY TARGET]</literal>. + Note that the <acronym>SQL</acronym> standard only defines + <literal>WHEN MATCHED</literal> and <literal>WHEN NOT MATCHED</literal> + (which is defined to mean no matching target row). + <literal>WHEN NOT MATCHED BY SOURCE</literal> is an extension to the + <acronym>SQL</acronym> standard, as is the option to append + <literal>BY TARGET</literal> to <literal>WHEN NOT MATCHED</literal>, to + make its meaning more explicit. + </para> + <para> If the <literal>WHEN</literal> clause specifies <literal>WHEN MATCHED</literal> and the candidate change row matches a row in the - target table, - the <literal>WHEN</literal> clause is executed if the + <replaceable class="parameter">data_source</replaceable> to a row in the + target table, the <literal>WHEN</literal> clause is executed if the + <replaceable class="parameter">condition</replaceable> is + absent or it evaluates to <literal>true</literal>. + </para> + <para> + If the <literal>WHEN</literal> clause specifies + <literal>WHEN NOT MATCHED BY SOURCE</literal> and the candidate change + row represents a row in the target table that does not match a row in the + <replaceable class="parameter">data_source</replaceable>, the + <literal>WHEN</literal> clause is executed if the <replaceable class="parameter">condition</replaceable> is absent or it evaluates to <literal>true</literal>. </para> <para> - Conversely, if the <literal>WHEN</literal> clause specifies - <literal>WHEN NOT MATCHED</literal> - and the candidate change row does not match a row in the - target table, + If the <literal>WHEN</literal> clause specifies + <literal>WHEN NOT MATCHED [BY TARGET]</literal> and the candidate change + row represents a row in the + <replaceable class="parameter">data_source</replaceable> that does not + match a row in the target table, the <literal>WHEN</literal> clause is executed if the <replaceable class="parameter">condition</replaceable> is absent or it evaluates to <literal>true</literal>. @@ -299,7 +334,10 @@ DELETE <para> A condition on a <literal>WHEN MATCHED</literal> clause can refer to columns in both the source and the target relations. A condition on a - <literal>WHEN NOT MATCHED</literal> clause can only refer to columns from + <literal>WHEN NOT MATCHED BY SOURCE</literal> clause can only refer to + columns from the target relation, since by definition there is no matching + source row. A condition on a <literal>WHEN NOT MATCHED [BY TARGET]</literal> + clause can only refer to columns from the source relation, since by definition there is no matching target row. Only the system attributes from the target table are accessible. </para> @@ -423,8 +461,10 @@ DELETE <literal>WHEN MATCHED</literal> clause, the expression can use values from the original row in the target table, and values from the <replaceable class="parameter">data_source</replaceable> row. - If used in a <literal>WHEN NOT MATCHED</literal> clause, the - expression can use values from the + If used in a <literal>WHEN NOT MATCHED BY SOURCE</literal> clause, the + expression can only use values from the original row in the target table. + If used in a <literal>WHEN NOT MATCHED [BY TARGET]</literal> clause, the + expression can only use values from the <replaceable class="parameter">data_source</replaceable> row. </para> </listitem> @@ -449,9 +489,12 @@ DELETE sub-query must yield no more than one row when executed. If it yields one row, its column values are assigned to the target columns; if it yields no rows, NULL values are assigned to the target columns. - The sub-query can refer to values from the original row in the target table, - and values from the <replaceable class="parameter">data_source</replaceable> - row. + If used in a <literal>WHEN MATCHED</literal> clause, the sub-query can + refer to values from the original row in the target table, and values + from the <replaceable class="parameter">data_source</replaceable> row. + If used in a <literal>WHEN NOT MATCHED BY SOURCE</literal> clause, the + sub-query can only refer to values from the original row in the target + table. </para> </listitem> </varlistentry> @@ -535,8 +578,9 @@ MERGE <replaceable class="parameter">total_count</replaceable> <orderedlist> <listitem> <para> - Evaluate whether each row is <literal>MATCHED</literal> or - <literal>NOT MATCHED</literal>. + Evaluate whether each row is <literal>MATCHED</literal>, + <literal>NOT MATCHED BY SOURCE</literal>, or + <literal>NOT MATCHED [BY TARGET]</literal>. </para> </listitem> <listitem> @@ -615,7 +659,8 @@ MERGE <replaceable class="parameter">total_count</replaceable> <para> If a <literal>WHEN</literal> clause omits an <literal>AND</literal> sub-clause, it becomes the final reachable clause of that - kind (<literal>MATCHED</literal> or <literal>NOT MATCHED</literal>). + kind (<literal>MATCHED</literal>, <literal>NOT MATCHED BY SOURCE</literal>, + or <literal>NOT MATCHED [BY TARGET]</literal>). If a later <literal>WHEN</literal> clause of that kind is specified it would be provably unreachable and an error is raised. If no final reachable clause is specified of either kind, it is @@ -701,6 +746,23 @@ RETURNING merge_action(), w.*; temporary table recently loaded into the database. </para> + <para> + Update <literal>wines</literal> based on a replacement wine list, inserting + rows for any new stock, updating modified stock entries, and deleting any + wines not present in the new list. +<programlisting> +MERGE INTO wines w +USING new_wine_list s +ON s.winename = w.winename +WHEN NOT MATCHED BY TARGET THEN + INSERT VALUES(s.winename, s.stock) +WHEN MATCHED AND w.stock != s.stock THEN + UPDATE SET stock = s.stock +WHEN NOT MATCHED BY SOURCE THEN + DELETE; +</programlisting> + </para> + </refsect1> <refsect1> @@ -709,7 +771,9 @@ RETURNING merge_action(), w.*; This command conforms to the <acronym>SQL</acronym> standard. </para> <para> - The <literal>WITH</literal> clause, <literal>DO NOTHING</literal> action, + The <literal>WITH</literal> clause, <literal>BY SOURCE</literal> and + <literal>BY TARGET</literal> qualifiers to + <literal>WHEN NOT MATCHED</literal>, <literal>DO NOTHING</literal> action, and <literal>RETURNING</literal> clause are extensions to the <acronym>SQL</acronym> standard. </para> diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index 7eb1f7d0209..4d7c92d63c1 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -1251,8 +1251,10 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo, resultRelInfo->ri_ReturningSlot = NULL; resultRelInfo->ri_TrigOldSlot = NULL; resultRelInfo->ri_TrigNewSlot = NULL; - resultRelInfo->ri_matchedMergeAction = NIL; - resultRelInfo->ri_notMatchedMergeAction = NIL; + resultRelInfo->ri_MergeActions[MERGE_WHEN_MATCHED] = NIL; + resultRelInfo->ri_MergeActions[MERGE_WHEN_NOT_MATCHED_BY_SOURCE] = NIL; + resultRelInfo->ri_MergeActions[MERGE_WHEN_NOT_MATCHED_BY_TARGET] = NIL; + resultRelInfo->ri_MergeJoinCondition = NULL; /* * Only ExecInitPartitionInfo() and ExecInitPartitionDispatchInfo() pass diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.c index 64fcb012dbe..bb14dcbe6fa 100644 --- a/src/backend/executor/execPartition.c +++ b/src/backend/executor/execPartition.c @@ -880,6 +880,7 @@ ExecInitPartitionInfo(ModifyTableState *mtstate, EState *estate, List *firstMergeActionList = linitial(node->mergeActionLists); ListCell *lc; ExprContext *econtext = mtstate->ps.ps_ExprContext; + Node *joinCondition; if (part_attmap == NULL) part_attmap = @@ -890,23 +891,31 @@ ExecInitPartitionInfo(ModifyTableState *mtstate, EState *estate, if (unlikely(!leaf_part_rri->ri_projectNewInfoValid)) ExecInitMergeTupleSlots(mtstate, leaf_part_rri); + /* Initialize state for join condition checking. */ + joinCondition = + map_variable_attnos(linitial(node->mergeJoinConditions), + firstVarno, 0, + part_attmap, + RelationGetForm(partrel)->reltype, + &found_whole_row); + /* We ignore the value of found_whole_row. */ + leaf_part_rri->ri_MergeJoinCondition = + ExecInitQual((List *) joinCondition, &mtstate->ps); + foreach(lc, firstMergeActionList) { /* Make a copy for this relation to be safe. */ MergeAction *action = copyObject(lfirst(lc)); MergeActionState *action_state; - List **list; /* Generate the action's state for this relation */ action_state = makeNode(MergeActionState); action_state->mas_action = action; /* And put the action in the appropriate list */ - if (action->matched) - list = &leaf_part_rri->ri_matchedMergeAction; - else - list = &leaf_part_rri->ri_notMatchedMergeAction; - *list = lappend(*list, action_state); + leaf_part_rri->ri_MergeActions[action->matchKind] = + lappend(leaf_part_rri->ri_MergeActions[action->matchKind], + action_state); switch (action->commandType) { diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index d1917f2fea7..325d380b0a9 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -24,13 +24,15 @@ * values plus row-locating info for UPDATE and MERGE cases, or just the * row-locating info for DELETE cases. * - * MERGE runs a join between the source relation and the target - * table; if any WHEN NOT MATCHED clauses are present, then the - * join is an outer join. In this case, any unmatched tuples will - * have NULL row-locating info, and only INSERT can be run. But for - * matched tuples, then row-locating info is used to determine the - * tuple to UPDATE or DELETE. When all clauses are WHEN MATCHED, - * then an inner join is used, so all tuples contain row-locating info. + * MERGE runs a join between the source relation and the target table. + * If any WHEN NOT MATCHED [BY TARGET] clauses are present, then the join + * is an outer join that might output tuples without a matching target + * tuple. In this case, any unmatched target tuples will have NULL + * row-locating info, and only INSERT can be run. But for matched target + * tuples, the row-locating info is used to determine the tuple to UPDATE + * or DELETE. When all clauses are WHEN MATCHED or WHEN NOT MATCHED BY + * SOURCE, all tuples produced by the join will include a matching target + * tuple, so all tuples contain row-locating info. * * If the query specifies RETURNING, then the ModifyTable returns a * RETURNING tuple after completing each row insert, update, or delete. @@ -2659,48 +2661,65 @@ ExecMerge(ModifyTableContext *context, ResultRelInfo *resultRelInfo, bool matched; /*----- - * If we are dealing with a WHEN MATCHED case (tupleid or oldtuple is - * valid, depending on whether the result relation is a table or a view), - * we execute the first action for which the additional WHEN MATCHED AND + * If we are dealing with a WHEN MATCHED case, tupleid or oldtuple is + * valid, depending on whether the result relation is a table or a view. + * We execute the first action for which the additional WHEN MATCHED AND * quals pass. If an action without quals is found, that action is * executed. * - * Similarly, if we are dealing with WHEN NOT MATCHED case, we look at - * the given WHEN NOT MATCHED actions in sequence until one passes. + * Similarly, in the WHEN NOT MATCHED BY SOURCE case, tupleid or oldtuple + * is valid, and we look at the given WHEN NOT MATCHED BY SOURCE actions + * in sequence until one passes. This is almost identical to the WHEN + * MATCHED case, and both cases are handled by ExecMergeMatched(). + * + * Finally, in the WHEN NOT MATCHED [BY TARGET] case, both tupleid and + * oldtuple are invalid, and we look at the given WHEN NOT MATCHED [BY + * TARGET] actions in sequence until one passes. * * Things get interesting in case of concurrent update/delete of the * target tuple. Such concurrent update/delete is detected while we are - * executing a WHEN MATCHED action. + * executing a WHEN MATCHED or WHEN NOT MATCHED BY SOURCE action. * * A concurrent update can: * - * 1. modify the target tuple so that it no longer satisfies the - * additional quals attached to the current WHEN MATCHED action + * 1. modify the target tuple so that the results from checking any + * additional quals attached to WHEN MATCHED or WHEN NOT MATCHED BY + * SOURCE actions potentially change, but the result from the join + * quals does not change. * - * In this case, we are still dealing with a WHEN MATCHED case. - * We recheck the list of WHEN MATCHED actions from the start and - * choose the first one that satisfies the new target tuple. + * In this case, we are still dealing with the same kind of match + * (MATCHED or NOT MATCHED BY SOURCE). We recheck the same list of + * actions from the start and choose the first one that satisfies the + * new target tuple. * - * 2. modify the target tuple so that the join quals no longer pass and - * hence the source tuple no longer has a match. + * 2. modify the target tuple in the WHEN MATCHED case so that the join + * quals no longer pass and hence the source and target tuples no + * longer match. * - * In this case, the source tuple no longer matches the target tuple, - * so we now instead find a qualifying WHEN NOT MATCHED action to - * execute. + * In this case, we are now dealing with a NOT MATCHED case, and we + * process both WHEN NOT MATCHED BY SOURCE and WHEN NOT MATCHED [BY + * TARGET] actions. First ExecMergeMatched() processes the list of + * WHEN NOT MATCHED BY SOURCE actions in sequence until one passes, + * then ExecMergeNotMatched() processes any WHEN NOT MATCHED [BY + * TARGET] actions in sequence until one passes. Thus we may execute + * two actions; one of each kind. * - * XXX Hmmm, what if the updated tuple would now match one that was - * considered NOT MATCHED so far? + * Thus we support concurrent updates that turn MATCHED candidate rows + * into NOT MATCHED rows. However, we do not attempt to support cases + * that would turn NOT MATCHED rows into MATCHED rows, or which would + * cause a target row to match a different source row. * - * A concurrent delete changes a WHEN MATCHED case to WHEN NOT MATCHED. + * A concurrent delete changes a WHEN MATCHED case to WHEN NOT MATCHED + * [BY TARGET]. * - * ExecMergeMatched takes care of following the update chain and - * re-finding the qualifying WHEN MATCHED action, as long as the updated - * target tuple still satisfies the join quals, i.e., it remains a WHEN - * MATCHED case. If the tuple gets deleted or the join quals fail, it - * returns and we try ExecMergeNotMatched. Given that ExecMergeMatched - * always make progress by following the update chain and we never switch - * from ExecMergeNotMatched to ExecMergeMatched, there is no risk of a - * livelock. + * ExecMergeMatched() takes care of following the update chain and + * re-finding the qualifying WHEN MATCHED or WHEN NOT MATCHED BY SOURCE + * action, as long as the target tuple still exists. If the target tuple + * gets deleted or a concurrent update causes the join quals to fail, it + * returns a matched status of false and we call ExecMergeNotMatched(). + * Given that ExecMergeMatched() always makes progress by following the + * update chain and we never switch from ExecMergeNotMatched() to + * ExecMergeMatched(), there is no risk of a livelock. */ matched = tupleid != NULL || oldtuple != NULL; if (matched) @@ -2713,33 +2732,52 @@ ExecMerge(ModifyTableContext *context, ResultRelInfo *resultRelInfo, * "matched" to false, indicating that it no longer matches). */ if (!matched) - rslot = ExecMergeNotMatched(context, resultRelInfo, canSetTag); + { + /* + * If a concurrent update turned a MATCHED case into a NOT MATCHED + * case, and we have both WHEN NOT MATCHED BY SOURCE and WHEN NOT + * MATCHED [BY TARGET] actions, and there is a RETURNING clause, + * ExecMergeMatched() may have already executed a WHEN NOT MATCHED BY + * SOURCE action, and computed the row to return. If so, we cannot + * execute a WHEN NOT MATCHED [BY TARGET] action now, so mark it as + * pending (to be processed on the next call to ExecModifyTable()). + * Otherwise, just process the action now. + */ + if (rslot == NULL) + rslot = ExecMergeNotMatched(context, resultRelInfo, canSetTag); + else + context->mtstate->mt_merge_pending_not_matched = context->planSlot; + } return rslot; } /* - * Check and execute the first qualifying MATCHED action. If the target + * Check and execute the first qualifying MATCHED or NOT MATCHED BY SOURCE + * action, depending on whether the join quals are satisfied. If the target * relation is a table, the current target tuple is identified by tupleid. * Otherwise, if the target relation is a view, oldtuple is the current target * tuple from the view. * - * 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 "matched" is set to true, indicating that no further - * action is required for this tuple. + * We start from the first WHEN MATCHED or WHEN NOT MATCHED BY SOURCE 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 the end without finding a qualifying action, we return NULL. + * Otherwise, we execute the qualifying action and return its RETURNING + * result, if any, or NULL. * - * If we do find a qualifying action, then we attempt to execute the action. + * On entry, "*matched" is assumed to be true. If a concurrent update or + * delete is detected that causes the join quals to no longer pass, we set it + * to false, indicating that the caller should process any NOT MATCHED [BY + * TARGET] actions. * - * If the tuple is concurrently updated, EvalPlanQual is run with the updated - * tuple to recheck the join quals. Note that the additional quals associated - * 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, "matched" is set to - * false -- meaning that a NOT MATCHED action must now be executed for the - * current source tuple. + * After a concurrent update, we restart from the first action to look for a + * new qualifying action to execute. If the join quals originally passed, and + * the concurrent update caused them to no longer pass, then we switch from + * the MATCHED to the NOT MATCHED BY SOURCE list of actions before restarting + * (and setting "*matched" to false). As a result we may execute a WHEN NOT + * MATCHED BY SOURCE action, and set "*matched" to false, causing the caller + * to also execute a WHEN NOT MATCHED [BY TARGET] action. */ static TupleTableSlot * ExecMergeMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo, @@ -2747,6 +2785,8 @@ ExecMergeMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo, bool *matched) { ModifyTableState *mtstate = context->mtstate; + List **mergeActions = resultRelInfo->ri_MergeActions; + List *actionStates; TupleTableSlot *newslot = NULL; TupleTableSlot *rslot = NULL; EState *estate = context->estate; @@ -2755,54 +2795,58 @@ ExecMergeMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo, EPQState *epqstate = &mtstate->mt_epqstate; ListCell *l; + /* Expect matched to be true on entry */ + Assert(*matched); + /* - * If there are no WHEN MATCHED actions, we are done. + * If there are no WHEN MATCHED or WHEN NOT MATCHED BY SOURCE actions, we + * are done. */ - if (resultRelInfo->ri_matchedMergeAction == NIL) - { - *matched = true; + if (mergeActions[MERGE_WHEN_MATCHED] == NIL && + mergeActions[MERGE_WHEN_NOT_MATCHED_BY_SOURCE] == NIL) return NULL; - } /* * Make tuple and any needed join variables available to ExecQual and * ExecProject. The target's existing tuple is installed in the scantuple. - * Again, this target relation's slot is required only in the case of a - * MATCHED tuple and UPDATE/DELETE actions. + * This target relation's slot is required only in the case of a MATCHED + * or NOT MATCHED BY SOURCE tuple and UPDATE/DELETE actions. */ econtext->ecxt_scantuple = resultRelInfo->ri_oldTupleSlot; econtext->ecxt_innertuple = context->planSlot; econtext->ecxt_outertuple = NULL; /* - * This routine is only invoked for matched rows, so we should either have - * the tupleid of the target row, or an old tuple from the target wholerow - * junk attr. + * This routine is only invoked for matched target rows, so we should + * either have the tupleid of the target row, or an old tuple from the + * target wholerow junk attr. */ Assert(tupleid != NULL || oldtuple != NULL); if (oldtuple != NULL) ExecForceStoreHeapTuple(oldtuple, resultRelInfo->ri_oldTupleSlot, false); - -lmerge_matched: + else if (!table_tuple_fetch_row_version(resultRelInfo->ri_RelationDesc, + tupleid, + SnapshotAny, + resultRelInfo->ri_oldTupleSlot)) + elog(ERROR, "failed to fetch the target tuple"); /* - * If passed a tupleid, use it to fetch the old target row. + * Test the join condition. If it's satisfied, perform a MATCHED action. + * Otherwise, perform a NOT MATCHED BY SOURCE action. * - * We use SnapshotAny for this because we might get called again after - * EvalPlanQual returns us a new tuple, which may not be visible to our - * MVCC snapshot. + * Note that this join condition will be NULL if there are no NOT MATCHED + * BY SOURCE actions --- see transform_MERGE_to_join(). In that case, we + * need only consider MATCHED actions here. */ - if (tupleid != NULL) - { - if (!table_tuple_fetch_row_version(resultRelInfo->ri_RelationDesc, - tupleid, - SnapshotAny, - resultRelInfo->ri_oldTupleSlot)) - elog(ERROR, "failed to fetch the target tuple"); - } + if (ExecQual(resultRelInfo->ri_MergeJoinCondition, econtext)) + actionStates = mergeActions[MERGE_WHEN_MATCHED]; + else + actionStates = mergeActions[MERGE_WHEN_NOT_MATCHED_BY_SOURCE]; - foreach(l, resultRelInfo->ri_matchedMergeAction) +lmerge_matched: + + foreach(l, actionStates) { MergeActionState *relaction = (MergeActionState *) lfirst(l); CmdType commandType = relaction->mas_action->commandType; @@ -2857,10 +2901,8 @@ lmerge_matched: tupleid, NULL, newslot, &result)) { if (result == TM_Ok) - { - *matched = true; return NULL; /* "do nothing" */ - } + break; /* concurrent update/delete */ } @@ -2870,10 +2912,7 @@ lmerge_matched: { if (!ExecIRUpdateTriggers(estate, resultRelInfo, oldtuple, newslot)) - { - *matched = true; return NULL; /* "do nothing" */ - } } else { @@ -2894,7 +2933,6 @@ lmerge_matched: if (updateCxt.crossPartUpdate) { mtstate->mt_merge_updated += 1; - *matched = true; return context->cpUpdateReturningSlot; } } @@ -2914,10 +2952,8 @@ lmerge_matched: NULL, NULL, &result)) { if (result == TM_Ok) - { - *matched = true; return NULL; /* "do nothing" */ - } + break; /* concurrent update/delete */ } @@ -2927,10 +2963,7 @@ lmerge_matched: { if (!ExecIRDeleteTriggers(estate, resultRelInfo, oldtuple)) - { - *matched = true; return NULL; /* "do nothing" */ - } } else result = ExecDeleteAct(context, resultRelInfo, tupleid, @@ -2950,7 +2983,7 @@ lmerge_matched: break; default: - elog(ERROR, "unknown action in MERGE WHEN MATCHED clause"); + elog(ERROR, "unknown action in MERGE WHEN clause"); } switch (result) @@ -3007,14 +3040,15 @@ lmerge_matched: errmsg("could not serialize access due to concurrent delete"))); /* - * If the tuple was already deleted, return to let caller - * handle it under NOT MATCHED clauses. + * If the tuple was already deleted, set matched to false to + * let caller handle it under NOT MATCHED [BY TARGET] clauses. */ *matched = false; return NULL; case TM_Updated: { + bool was_matched; Relation resultRelationDesc; TupleTableSlot *epqslot, *inputslot; @@ -3022,19 +3056,23 @@ lmerge_matched: /* * The target tuple was concurrently updated by some other - * transaction. Run EvalPlanQual() with the new version of - * the tuple. If it does not return a tuple, then we - * switch to the NOT MATCHED list of actions. If it does - * return a tuple and the join qual is still satisfied, - * then we just need to recheck the MATCHED actions, - * starting from the top, and execute the first qualifying - * action. + * transaction. If we are currently processing a MATCHED + * action, use EvalPlanQual() with the new version of the + * tuple and recheck the join qual, to detect a change + * from the MATCHED to the NOT MATCHED cases. If we are + * already processing a NOT MATCHED BY SOURCE action, we + * skip this (cannot switch from NOT MATCHED BY SOURCE to + * MATCHED). */ + was_matched = relaction->mas_action->matchKind == MERGE_WHEN_MATCHED; resultRelationDesc = resultRelInfo->ri_RelationDesc; lockmode = ExecUpdateLockMode(estate, resultRelInfo); - inputslot = EvalPlanQualSlot(epqstate, resultRelationDesc, - resultRelInfo->ri_RangeTableIndex); + if (was_matched) + inputslot = EvalPlanQualSlot(epqstate, resultRelationDesc, + resultRelInfo->ri_RangeTableIndex); + else + inputslot = resultRelInfo->ri_oldTupleSlot; result = table_tuple_lock(resultRelationDesc, tupleid, estate->es_snapshot, @@ -3045,34 +3083,9 @@ lmerge_matched: switch (result) { case TM_Ok: - epqslot = EvalPlanQual(epqstate, - resultRelationDesc, - resultRelInfo->ri_RangeTableIndex, - inputslot); /* - * If we got no tuple, or the tuple we get has a - * NULL ctid, go back to caller: this one is not a - * MATCHED tuple anymore, so they can retry with - * NOT MATCHED actions. - */ - if (TupIsNull(epqslot)) - { - *matched = false; - return NULL; - } - - (void) ExecGetJunkAttribute(epqslot, - resultRelInfo->ri_RowIdAttNo, - &isNull); - if (isNull) - { - *matched = false; - return NULL; - } - - /* - * When a tuple was updated and migrated to + * If the tuple was updated and migrated to * another partition concurrently, the current * MERGE implementation can't follow. There's * probably a better way to handle this case, but @@ -3083,26 +3096,72 @@ lmerge_matched: if (ItemPointerIndicatesMovedPartitions(&context->tmfd.ctid)) ereport(ERROR, (errcode(ERRCODE_T_R_SERIALIZATION_FAILURE), - errmsg("tuple to be deleted was already moved to another partition due to concurrent update"))); + errmsg("tuple to be merged was already moved to another partition due to concurrent update"))); /* - * A non-NULL ctid means that we are still dealing - * with MATCHED case. Restart the loop so that we - * apply all the MATCHED rules again, to ensure - * that the first qualifying WHEN MATCHED action - * is executed. - * - * Update tupleid to that of the new tuple, for - * the refetch we do at the top. + * If this was a MATCHED case, use EvalPlanQual() + * to recheck the join condition. + */ + if (was_matched) + { + epqslot = EvalPlanQual(epqstate, + resultRelationDesc, + resultRelInfo->ri_RangeTableIndex, + inputslot); + + /* + * If the subplan didn't return a tuple, then + * we must be dealing with an inner join for + * which the join condition no longer matches. + * This can only happen if there are no NOT + * MATCHED actions, and so there is nothing + * more to do. + */ + if (TupIsNull(epqslot)) + return NULL; + + /* + * If we got a NULL ctid from the subplan, the + * join quals no longer pass and we switch to + * the NOT MATCHED BY SOURCE case. + */ + (void) ExecGetJunkAttribute(epqslot, + resultRelInfo->ri_RowIdAttNo, + &isNull); + if (isNull) + *matched = false; + + /* + * Otherwise, recheck the join quals to see if + * we need to switch to the NOT MATCHED BY + * SOURCE case. + */ + if (!table_tuple_fetch_row_version(resultRelationDesc, + &context->tmfd.ctid, + SnapshotAny, + resultRelInfo->ri_oldTupleSlot)) + elog(ERROR, "failed to fetch the target tuple"); + + if (*matched) + *matched = ExecQual(resultRelInfo->ri_MergeJoinCondition, + econtext); + + /* Switch lists, if necessary */ + if (!*matched) + actionStates = mergeActions[MERGE_WHEN_NOT_MATCHED_BY_SOURCE]; + } + + /* + * Loop back and process the MATCHED or NOT + * MATCHED BY SOURCE actions from the start. */ - ItemPointerCopy(&context->tmfd.ctid, tupleid); goto lmerge_matched; case TM_Deleted: /* * tuple already deleted; tell caller to run NOT - * MATCHED actions + * MATCHED [BY TARGET] actions */ *matched = false; return NULL; @@ -3186,13 +3245,11 @@ lmerge_matched: /* * Successfully executed an action or no qualifying action was found. */ - *matched = true; - return rslot; } /* - * Execute the first qualifying NOT MATCHED action. + * Execute the first qualifying NOT MATCHED [BY TARGET] action. */ static TupleTableSlot * ExecMergeNotMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo, @@ -3200,7 +3257,7 @@ ExecMergeNotMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo, { ModifyTableState *mtstate = context->mtstate; ExprContext *econtext = mtstate->ps.ps_ExprContext; - List *actionStates = NIL; + List *actionStates; TupleTableSlot *rslot = NULL; ListCell *l; @@ -3213,7 +3270,7 @@ ExecMergeNotMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo, * XXX does this mean that we can avoid creating copies of actionStates on * partitioned tables, for not-matched actions? */ - actionStates = resultRelInfo->ri_notMatchedMergeAction; + actionStates = resultRelInfo->ri_MergeActions[MERGE_WHEN_NOT_MATCHED_BY_TARGET]; /* * Make source tuple available to ExecQual and ExecProject. We don't need @@ -3307,9 +3364,11 @@ ExecInitMerge(ModifyTableState *mtstate, EState *estate) foreach(lc, node->mergeActionLists) { List *mergeActionList = lfirst(lc); + Node *joinCondition; TupleDesc relationDesc; ListCell *l; + joinCondition = (Node *) list_nth(node->mergeJoinConditions, i); resultRelInfo = mtstate->resultRelInfo + i; i++; relationDesc = RelationGetDescr(resultRelInfo->ri_RelationDesc); @@ -3318,13 +3377,16 @@ ExecInitMerge(ModifyTableState *mtstate, EState *estate) if (unlikely(!resultRelInfo->ri_projectNewInfoValid)) ExecInitMergeTupleSlots(mtstate, resultRelInfo); + /* initialize state for join condition checking */ + resultRelInfo->ri_MergeJoinCondition = + ExecInitQual((List *) joinCondition, &mtstate->ps); + foreach(l, mergeActionList) { MergeAction *action = (MergeAction *) lfirst(l); MergeActionState *action_state; TupleTableSlot *tgtslot; TupleDesc tgtdesc; - List **list; /* * Build action merge state for this rel. (For partitions, @@ -3336,15 +3398,12 @@ ExecInitMerge(ModifyTableState *mtstate, EState *estate) &mtstate->ps); /* - * We create two lists - one for WHEN MATCHED actions and one for - * WHEN NOT MATCHED actions - and stick the MergeActionState into - * the appropriate list. + * We create three lists - one for each MergeMatchKind - and stick + * the MergeActionState into the appropriate list. */ - if (action_state->mas_action->matched) - list = &resultRelInfo->ri_matchedMergeAction; - else - list = &resultRelInfo->ri_notMatchedMergeAction; - *list = lappend(*list, action_state); + resultRelInfo->ri_MergeActions[action->matchKind] = + lappend(resultRelInfo->ri_MergeActions[action->matchKind], + action_state); switch (action->commandType) { @@ -3701,6 +3760,31 @@ ExecModifyTable(PlanState *pstate) if (pstate->ps_ExprContext) ResetExprContext(pstate->ps_ExprContext); + /* + * If there is a pending MERGE ... WHEN NOT MATCHED [BY TARGET] action + * to execute, do so now --- see the comments in ExecMerge(). + */ + if (node->mt_merge_pending_not_matched != NULL) + { + context.planSlot = node->mt_merge_pending_not_matched; + + slot = ExecMergeNotMatched(&context, node->resultRelInfo, + node->canSetTag); + + /* Clear the pending action */ + node->mt_merge_pending_not_matched = NULL; + + /* + * 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 */ + } + + /* Fetch the next row from subplan */ context.planSlot = ExecProcNode(subplanstate); /* No more tuples to process? */ @@ -4092,6 +4176,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) mtstate->resultRelInfo = (ResultRelInfo *) palloc(nrels * sizeof(ResultRelInfo)); + mtstate->mt_merge_pending_not_matched = NULL; mtstate->mt_merge_inserted = 0; mtstate->mt_merge_updated = 0; mtstate->mt_merge_deleted = 0; diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index 9f1553bccfa..7d37226bd9c 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -2701,6 +2701,8 @@ query_tree_walker_impl(Query *query, return true; if (WALK(query->mergeActionList)) return true; + if (WALK(query->mergeJoinCondition)) + return true; if (WALK(query->returningList)) return true; if (WALK(query->jointree)) @@ -3752,6 +3754,7 @@ query_tree_mutator_impl(Query *query, MUTATE(query->withCheckOptions, query->withCheckOptions, List *); MUTATE(query->onConflict, query->onConflict, OnConflictExpr *); MUTATE(query->mergeActionList, query->mergeActionList, List *); + MUTATE(query->mergeJoinCondition, query->mergeJoinCondition, Node *); MUTATE(query->returningList, query->returningList, List *); MUTATE(query->jointree, query->jointree, FromExpr *); MUTATE(query->setOperations, query->setOperations, Node *); diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 5f479fc56c1..3b778865674 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -312,7 +312,8 @@ static ModifyTable *make_modifytable(PlannerInfo *root, Plan *subplan, List *updateColnosLists, List *withCheckOptionLists, List *returningLists, List *rowMarks, OnConflictExpr *onconflict, - List *mergeActionLists, int epqParam); + List *mergeActionLists, List *mergeJoinConditions, + int epqParam); static GatherMerge *create_gather_merge_plan(PlannerInfo *root, GatherMergePath *best_path); @@ -2836,6 +2837,7 @@ create_modifytable_plan(PlannerInfo *root, ModifyTablePath *best_path) best_path->rowMarks, best_path->onconflict, best_path->mergeActionLists, + best_path->mergeJoinConditions, best_path->epqParam); copy_generic_path_info(&plan->plan, &best_path->path); @@ -7031,7 +7033,8 @@ make_modifytable(PlannerInfo *root, Plan *subplan, List *updateColnosLists, List *withCheckOptionLists, List *returningLists, List *rowMarks, OnConflictExpr *onconflict, - List *mergeActionLists, int epqParam) + List *mergeActionLists, List *mergeJoinConditions, + int epqParam) { ModifyTable *node = makeNode(ModifyTable); List *fdw_private_list; @@ -7101,6 +7104,7 @@ make_modifytable(PlannerInfo *root, Plan *subplan, node->returningLists = returningLists; node->rowMarks = rowMarks; node->mergeActionLists = mergeActionLists; + node->mergeJoinConditions = mergeJoinConditions; node->epqParam = epqParam; /* diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 38d070fa004..0e34873d6a8 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -911,6 +911,9 @@ subquery_planner(PlannerGlobal *glob, Query *parse, EXPRKIND_QUAL); } + parse->mergeJoinCondition = + preprocess_expression(root, parse->mergeJoinCondition, EXPRKIND_QUAL); + root->append_rel_list = (List *) preprocess_expression(root, (Node *) root->append_rel_list, EXPRKIND_APPINFO); @@ -1805,6 +1808,7 @@ grouping_planner(PlannerInfo *root, double tuple_fraction) List *withCheckOptionLists = NIL; List *returningLists = NIL; List *mergeActionLists = NIL; + List *mergeJoinConditions = NIL; List *rowMarks; if (bms_membership(root->all_result_relids) == BMS_MULTIPLE) @@ -1911,6 +1915,19 @@ grouping_planner(PlannerInfo *root, double tuple_fraction) mergeActionLists = lappend(mergeActionLists, mergeActionList); } + if (parse->commandType == CMD_MERGE) + { + Node *mergeJoinCondition = parse->mergeJoinCondition; + + if (this_result_rel != top_result_rel) + mergeJoinCondition = + adjust_appendrel_attrs_multilevel(root, + mergeJoinCondition, + this_result_rel, + top_result_rel); + mergeJoinConditions = lappend(mergeJoinConditions, + mergeJoinCondition); + } } if (resultRelations == NIL) @@ -1935,6 +1952,8 @@ grouping_planner(PlannerInfo *root, double tuple_fraction) returningLists = list_make1(parse->returningList); if (parse->mergeActionList) mergeActionLists = list_make1(parse->mergeActionList); + if (parse->commandType == CMD_MERGE) + mergeJoinConditions = list_make1(parse->mergeJoinCondition); } } else @@ -1950,6 +1969,8 @@ grouping_planner(PlannerInfo *root, double tuple_fraction) returningLists = list_make1(parse->returningList); if (parse->mergeActionList) mergeActionLists = list_make1(parse->mergeActionList); + if (parse->commandType == CMD_MERGE) + mergeJoinConditions = list_make1(parse->mergeJoinCondition); } /* @@ -1977,6 +1998,7 @@ grouping_planner(PlannerInfo *root, double tuple_fraction) rowMarks, parse->onConflict, mergeActionLists, + mergeJoinConditions, assign_special_exec_param(root)); } diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index 42603dbc7c2..37abcb4701f 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -1143,7 +1143,9 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset) */ if (splan->mergeActionLists != NIL) { + List *newMJC = NIL; ListCell *lca, + *lcj, *lcr; /* @@ -1164,10 +1166,12 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset) itlist = build_tlist_index(subplan->targetlist); - forboth(lca, splan->mergeActionLists, - lcr, splan->resultRelations) + forthree(lca, splan->mergeActionLists, + lcj, splan->mergeJoinConditions, + lcr, splan->resultRelations) { List *mergeActionList = lfirst(lca); + Node *mergeJoinCondition = lfirst(lcj); Index resultrel = lfirst_int(lcr); foreach(l, mergeActionList) @@ -1192,7 +1196,19 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset) NRM_EQUAL, NUM_EXEC_QUAL(plan)); } + + /* Fix join condition too. */ + mergeJoinCondition = (Node *) + fix_join_expr(root, + (List *) mergeJoinCondition, + NULL, itlist, + resultrel, + rtoffset, + NRM_EQUAL, + NUM_EXEC_QUAL(plan)); + newMJC = lappend(newMJC, mergeJoinCondition); } + splan->mergeJoinConditions = newMJC; } splan->nominalRelation += rtoffset; diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c index 300691cc4db..4badb6ff587 100644 --- a/src/backend/optimizer/prep/prepjointree.c +++ b/src/backend/optimizer/prep/prepjointree.c @@ -153,9 +153,11 @@ transform_MERGE_to_join(Query *parse) { RangeTblEntry *joinrte; JoinExpr *joinexpr; + bool have_action[3]; JoinType jointype; int joinrti; List *vars; + RangeTblRef *rtr; if (parse->commandType != CMD_MERGE) return; @@ -164,11 +166,27 @@ transform_MERGE_to_join(Query *parse) vars = NIL; /* - * When any WHEN NOT MATCHED THEN INSERT clauses exist, we need to use an - * outer join so that we process all unmatched tuples from the source - * relation. If none exist, we can use an inner join. + * Work out what kind of join is required. If there any WHEN NOT MATCHED + * BY SOURCE/TARGET actions, an outer join is required so that we process + * all unmatched tuples from the source and/or target relations. + * Otherwise, we can use an inner join. */ - if (parse->mergeUseOuterJoin) + have_action[MERGE_WHEN_MATCHED] = false; + have_action[MERGE_WHEN_NOT_MATCHED_BY_SOURCE] = false; + have_action[MERGE_WHEN_NOT_MATCHED_BY_TARGET] = false; + + foreach_node(MergeAction, action, parse->mergeActionList) + { + if (action->commandType != CMD_NOTHING) + have_action[action->matchKind] = true; + } + + if (have_action[MERGE_WHEN_NOT_MATCHED_BY_SOURCE] && + have_action[MERGE_WHEN_NOT_MATCHED_BY_TARGET]) + jointype = JOIN_FULL; + else if (have_action[MERGE_WHEN_NOT_MATCHED_BY_SOURCE]) + jointype = JOIN_LEFT; + else if (have_action[MERGE_WHEN_NOT_MATCHED_BY_TARGET]) jointype = JOIN_RIGHT; else jointype = JOIN_INNER; @@ -203,17 +221,21 @@ transform_MERGE_to_join(Query *parse) * regular table, this will equal parse->resultRelation, but for a * trigger-updatable view, it will be the expanded view subquery that we * need to pull data from. + * + * The source relation is in parse->jointree->fromlist, but any quals in + * parse->jointree->quals are restrictions on the target relation (if the + * target relation is an auto-updatable view). */ + rtr = makeNode(RangeTblRef); + rtr->rtindex = parse->mergeTargetRelation; joinexpr = makeNode(JoinExpr); joinexpr->jointype = jointype; joinexpr->isNatural = false; - joinexpr->larg = (Node *) makeNode(RangeTblRef); - ((RangeTblRef *) joinexpr->larg)->rtindex = parse->mergeTargetRelation; - joinexpr->rarg = linitial(parse->jointree->fromlist); /* original join */ + joinexpr->larg = (Node *) makeFromExpr(list_make1(rtr), parse->jointree->quals); + joinexpr->rarg = linitial(parse->jointree->fromlist); /* source rel */ joinexpr->usingClause = NIL; joinexpr->join_using_alias = NULL; - /* The quals are removed from the jointree and into this specific join */ - joinexpr->quals = parse->jointree->quals; + joinexpr->quals = parse->mergeJoinCondition; joinexpr->alias = NULL; joinexpr->rtindex = joinrti; @@ -233,6 +255,15 @@ transform_MERGE_to_join(Query *parse) add_nulling_relids((Node *) parse->targetList, bms_make_singleton(parse->mergeTargetRelation), bms_make_singleton(joinrti)); + + /* + * If there are any WHEN NOT MATCHED BY SOURCE actions, the executor will + * use the join condition to distinguish between MATCHED and NOT MATCHED + * BY SOURCE cases. Otherwise, it's no longer needed, and we set it to + * NULL, saving cycles during planning and execution. + */ + if (!have_action[MERGE_WHEN_NOT_MATCHED_BY_SOURCE]) + parse->mergeJoinCondition = NULL; } /* @@ -2173,6 +2204,8 @@ perform_pullup_replace_vars(PlannerInfo *root, pullup_replace_vars((Node *) action->targetList, rvcontext); } } + parse->mergeJoinCondition = pullup_replace_vars(parse->mergeJoinCondition, + rvcontext); replace_vars_in_jointree((Node *) parse->jointree, rvcontext); Assert(parse->setOperations == NULL); parse->havingQual = pullup_replace_vars(parse->havingQual, rvcontext); diff --git a/src/backend/optimizer/prep/preptlist.c b/src/backend/optimizer/prep/preptlist.c index 7698bfa1a58..931b9c09bda 100644 --- a/src/backend/optimizer/prep/preptlist.c +++ b/src/backend/optimizer/prep/preptlist.c @@ -134,6 +134,7 @@ preprocess_targetlist(PlannerInfo *root) if (command_type == CMD_MERGE) { ListCell *l; + List *vars; /* * For MERGE, handle targetlist of each MergeAction separately. Give @@ -144,7 +145,6 @@ preprocess_targetlist(PlannerInfo *root) foreach(l, parse->mergeActionList) { MergeAction *action = (MergeAction *) lfirst(l); - List *vars; ListCell *l2; if (action->commandType == CMD_INSERT) @@ -182,6 +182,30 @@ preprocess_targetlist(PlannerInfo *root) } list_free(vars); } + + /* + * Add resjunk entries for any Vars and PlaceHolderVars used in the + * join condition that belong to relations other than the target. We + * don't expect to see any aggregates or window functions here. + */ + vars = pull_var_clause(parse->mergeJoinCondition, + PVC_INCLUDE_PLACEHOLDERS); + foreach(l, vars) + { + Var *var = (Var *) lfirst(l); + TargetEntry *tle; + + if (IsA(var, Var) && var->varno == result_relation) + continue; /* don't need it */ + + if (tlist_member((Expr *) var, tlist)) + continue; /* already got it */ + + tle = makeTargetEntry((Expr *) var, + list_length(tlist) + 1, + NULL, true); + tlist = lappend(tlist, tle); + } } /* diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index e4a5ae83e4a..3cf1dac0873 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -3716,6 +3716,7 @@ create_lockrows_path(PlannerInfo *root, RelOptInfo *rel, * 'onconflict' is the ON CONFLICT clause, or NULL * 'epqParam' is the ID of Param for EvalPlanQual re-eval * 'mergeActionLists' is a list of lists of MERGE actions (one per rel) + * 'mergeJoinConditions' is a list of join conditions for MERGE (one per rel) */ ModifyTablePath * create_modifytable_path(PlannerInfo *root, RelOptInfo *rel, @@ -3727,7 +3728,8 @@ create_modifytable_path(PlannerInfo *root, RelOptInfo *rel, List *updateColnosLists, List *withCheckOptionLists, List *returningLists, List *rowMarks, OnConflictExpr *onconflict, - List *mergeActionLists, int epqParam) + List *mergeActionLists, List *mergeJoinConditions, + int epqParam) { ModifyTablePath *pathnode = makeNode(ModifyTablePath); @@ -3795,6 +3797,7 @@ create_modifytable_path(PlannerInfo *root, RelOptInfo *rel, pathnode->onconflict = onconflict; pathnode->epqParam = epqParam; pathnode->mergeActionLists = mergeActionLists; + pathnode->mergeJoinConditions = mergeJoinConditions; return pathnode; } diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index c1b0cff1c9e..682748eb4b0 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -275,6 +275,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); struct SelectLimit *selectlimit; SetQuantifier setquantifier; struct GroupClause *groupclause; + MergeMatchKind mergematch; MergeWhenClause *mergewhen; struct KeyActions *keyactions; struct KeyAction *keyaction; @@ -516,6 +517,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <onconflict> opt_on_conflict %type <mergewhen> merge_insert merge_update merge_delete +%type <mergematch> merge_when_tgt_matched merge_when_tgt_not_matched %type <node> merge_when_clause opt_merge_when_condition %type <list> merge_when_list @@ -770,11 +772,11 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); SAVEPOINT SCALAR SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES SERIALIZABLE SERVER SESSION SESSION_USER SET SETS SETOF SHARE SHOW - SIMILAR SIMPLE SKIP SMALLINT SNAPSHOT SOME SQL_P STABLE STANDALONE_P + SIMILAR SIMPLE SKIP SMALLINT SNAPSHOT SOME SOURCE SQL_P STABLE STANDALONE_P START STATEMENT STATISTICS STDIN STDOUT STORAGE STORED STRICT_P STRING_P STRIP_P SUBSCRIPTION SUBSTRING SUPPORT SYMMETRIC SYSID SYSTEM_P SYSTEM_USER - TABLE TABLES TABLESAMPLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN + TABLE TABLES TABLESAMPLE TABLESPACE TARGET TEMP TEMPLATE TEMPORARY TEXT_P THEN TIES TIME TIMESTAMP TO TRAILING TRANSACTION TRANSFORM TREAT TRIGGER TRIM TRUE_P TRUNCATE TRUSTED TYPE_P TYPES_P @@ -12424,50 +12426,66 @@ merge_when_list: | merge_when_list merge_when_clause { $$ = lappend($1,$2); } ; +/* + * A WHEN clause may be WHEN MATCHED, WHEN NOT MATCHED BY SOURCE, or WHEN NOT + * MATCHED [BY TARGET]. The first two cases match target tuples, and support + * UPDATE/DELETE/DO NOTHING actions. The third case does not match target + * tuples, and only supports INSERT/DO NOTHING actions. + */ merge_when_clause: - WHEN MATCHED opt_merge_when_condition THEN merge_update + merge_when_tgt_matched opt_merge_when_condition THEN merge_update { - $5->matched = true; - $5->condition = $3; + $4->matchKind = $1; + $4->condition = $2; - $$ = (Node *) $5; + $$ = (Node *) $4; } - | WHEN MATCHED opt_merge_when_condition THEN merge_delete + | merge_when_tgt_matched opt_merge_when_condition THEN merge_delete { - $5->matched = true; - $5->condition = $3; + $4->matchKind = $1; + $4->condition = $2; - $$ = (Node *) $5; + $$ = (Node *) $4; } - | WHEN NOT MATCHED opt_merge_when_condition THEN merge_insert + | merge_when_tgt_not_matched opt_merge_when_condition THEN merge_insert { - $6->matched = false; - $6->condition = $4; + $4->matchKind = $1; + $4->condition = $2; - $$ = (Node *) $6; + $$ = (Node *) $4; } - | WHEN MATCHED opt_merge_when_condition THEN DO NOTHING + | merge_when_tgt_matched opt_merge_when_condition THEN DO NOTHING { MergeWhenClause *m = makeNode(MergeWhenClause); - m->matched = true; + m->matchKind = $1; m->commandType = CMD_NOTHING; - m->condition = $3; + m->condition = $2; $$ = (Node *) m; } - | WHEN NOT MATCHED opt_merge_when_condition THEN DO NOTHING + | merge_when_tgt_not_matched opt_merge_when_condition THEN DO NOTHING { MergeWhenClause *m = makeNode(MergeWhenClause); - m->matched = false; + m->matchKind = $1; m->commandType = CMD_NOTHING; - m->condition = $4; + m->condition = $2; $$ = (Node *) m; } ; +merge_when_tgt_matched: + WHEN MATCHED { $$ = MERGE_WHEN_MATCHED; } + | WHEN NOT MATCHED BY SOURCE { $$ = MERGE_WHEN_NOT_MATCHED_BY_SOURCE; } + ; + +merge_when_tgt_not_matched: + WHEN NOT MATCHED { $$ = MERGE_WHEN_NOT_MATCHED_BY_TARGET; } + | WHEN NOT MATCHED BY TARGET { $$ = MERGE_WHEN_NOT_MATCHED_BY_TARGET; } + ; + opt_merge_when_condition: AND a_expr { $$ = $2; } | { $$ = NULL; } @@ -17576,6 +17594,7 @@ unreserved_keyword: | SIMPLE | SKIP | SNAPSHOT + | SOURCE | SQL_P | STABLE | STANDALONE_P @@ -17595,6 +17614,7 @@ unreserved_keyword: | SYSTEM_P | TABLES | TABLESPACE + | TARGET | TEMP | TEMPLATE | TEMPORARY @@ -18206,6 +18226,7 @@ bare_label_keyword: | SMALLINT | SNAPSHOT | SOME + | SOURCE | SQL_P | STABLE | STANDALONE_P @@ -18230,6 +18251,7 @@ bare_label_keyword: | TABLES | TABLESAMPLE | TABLESPACE + | TARGET | TEMP | TEMPLATE | TEMPORARY diff --git a/src/backend/parser/parse_merge.c b/src/backend/parser/parse_merge.c index 04ed5e66dda..bce11d59561 100644 --- a/src/backend/parser/parse_merge.c +++ b/src/backend/parser/parse_merge.c @@ -40,9 +40,9 @@ static void setNamespaceVisibilityForRTE(List *namespace, RangeTblEntry *rte, * Make appropriate changes to the namespace visibility while transforming * individual action's quals and targetlist expressions. In particular, for * INSERT actions we must only see the source relation (since INSERT action is - * invoked for NOT MATCHED tuples and hence there is no target tuple to deal - * with). On the other hand, UPDATE and DELETE actions can see both source and - * target relations. + * invoked for NOT MATCHED [BY TARGET] tuples and hence there is no target + * tuple to deal with). On the other hand, UPDATE and DELETE actions can see + * both source and target relations, unless invoked for NOT MATCHED BY SOURCE. * * Also, since the internal join node can hide the source and target * relations, we must explicitly make the respective relation as visible so @@ -58,7 +58,7 @@ setNamespaceForMergeWhen(ParseState *pstate, MergeWhenClause *mergeWhenClause, targetRelRTE = rt_fetch(targetRTI, pstate->p_rtable); sourceRelRTE = rt_fetch(sourceRTI, pstate->p_rtable); - if (mergeWhenClause->matched) + if (mergeWhenClause->matchKind == MERGE_WHEN_MATCHED) { Assert(mergeWhenClause->commandType == CMD_UPDATE || mergeWhenClause->commandType == CMD_DELETE || @@ -70,11 +70,25 @@ setNamespaceForMergeWhen(ParseState *pstate, MergeWhenClause *mergeWhenClause, setNamespaceVisibilityForRTE(pstate->p_namespace, sourceRelRTE, true, true); } - else + else if (mergeWhenClause->matchKind == MERGE_WHEN_NOT_MATCHED_BY_SOURCE) { /* - * NOT MATCHED actions can't see target relation, but they can see - * source relation. + * NOT MATCHED BY SOURCE actions can see the target relation, but they + * can't see the source relation. + */ + Assert(mergeWhenClause->commandType == CMD_UPDATE || + mergeWhenClause->commandType == CMD_DELETE || + mergeWhenClause->commandType == CMD_NOTHING); + setNamespaceVisibilityForRTE(pstate->p_namespace, + targetRelRTE, true, true); + setNamespaceVisibilityForRTE(pstate->p_namespace, + sourceRelRTE, false, false); + } + else /* MERGE_WHEN_NOT_MATCHED_BY_TARGET */ + { + /* + * NOT MATCHED [BY TARGET] actions can't see target relation, but they + * can see source relation. */ Assert(mergeWhenClause->commandType == CMD_INSERT || mergeWhenClause->commandType == CMD_NOTHING); @@ -95,10 +109,9 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt) Query *qry = makeNode(Query); ListCell *l; AclMode targetPerms = ACL_NO_RIGHTS; - bool is_terminal[2]; + bool is_terminal[3]; Index sourceRTI; List *mergeActionList; - Node *joinExpr; ParseNamespaceItem *nsitem; /* There can't be any outer WITH to worry about */ @@ -122,12 +135,12 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt) /* * Check WHEN clauses for permissions and sanity */ - is_terminal[0] = false; - is_terminal[1] = false; + is_terminal[MERGE_WHEN_MATCHED] = false; + is_terminal[MERGE_WHEN_NOT_MATCHED_BY_SOURCE] = false; + is_terminal[MERGE_WHEN_NOT_MATCHED_BY_TARGET] = false; foreach(l, stmt->mergeWhenClauses) { MergeWhenClause *mergeWhenClause = (MergeWhenClause *) lfirst(l); - int when_type = (mergeWhenClause->matched ? 0 : 1); /* * Collect permissions to check, according to action types. We require @@ -157,12 +170,12 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt) /* * Check for unreachable WHEN clauses */ - if (is_terminal[when_type]) + if (is_terminal[mergeWhenClause->matchKind]) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("unreachable WHEN clause specified after unconditional WHEN clause"))); if (mergeWhenClause->condition == NULL) - is_terminal[when_type] = true; + is_terminal[mergeWhenClause->matchKind] = true; } /* @@ -223,16 +236,15 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt) * side, so add that to the namespace. */ addNSItemToQuery(pstate, pstate->p_target_nsitem, false, true, true); - joinExpr = transformExpr(pstate, stmt->joinCondition, - EXPR_KIND_JOIN_ON); + qry->mergeJoinCondition = transformExpr(pstate, stmt->joinCondition, + EXPR_KIND_JOIN_ON); /* * Create the temporary query's jointree using the joinlist we built using - * just the source relation; the target relation is not included. The - * quals we use are the join conditions to the merge target. The join + * just the source relation; the target relation is not included. The join * will be constructed fully by transform_MERGE_to_join. */ - qry->jointree = makeFromExpr(pstate->p_joinlist, joinExpr); + qry->jointree = makeFromExpr(pstate->p_joinlist, NULL); /* Transform the RETURNING list, if any */ qry->returningList = transformReturningList(pstate, stmt->returningList, @@ -260,11 +272,7 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt) action = makeNode(MergeAction); action->commandType = mergeWhenClause->commandType; - action->matched = mergeWhenClause->matched; - - /* Use an outer join if any INSERT actions exist in the command. */ - if (action->commandType == CMD_INSERT) - qry->mergeUseOuterJoin = true; + action->matchKind = mergeWhenClause->matchKind; /* * Set namespace for the specific action. This must be done before diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index a51717e36ce..0f7f40c50f6 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -7112,6 +7112,7 @@ get_merge_query_def(Query *query, deparse_context *context, StringInfo buf = context->buf; RangeTblEntry *rte; ListCell *lc; + bool haveNotMatchedBySource; /* Insert the WITH clause if given */ get_with_clause(query, context); @@ -7137,7 +7138,26 @@ get_merge_query_def(Query *query, deparse_context *context, get_from_clause(query, " USING ", context); appendContextKeyword(context, " ON ", -PRETTYINDENT_STD, PRETTYINDENT_STD, 2); - get_rule_expr(query->jointree->quals, context, false); + get_rule_expr(query->mergeJoinCondition, context, false); + + /* + * Test for any NOT MATCHED BY SOURCE actions. If there are none, then + * any NOT MATCHED BY TARGET actions are output as "WHEN NOT MATCHED", per + * SQL standard. Otherwise, we have a non-SQL-standard query, so output + * "BY SOURCE" / "BY TARGET" qualifiers for all NOT MATCHED actions, to be + * more explicit. + */ + haveNotMatchedBySource = false; + foreach(lc, query->mergeActionList) + { + MergeAction *action = lfirst_node(MergeAction, lc); + + if (action->matchKind == MERGE_WHEN_NOT_MATCHED_BY_SOURCE) + { + haveNotMatchedBySource = true; + break; + } + } /* Print each merge action */ foreach(lc, query->mergeActionList) @@ -7146,7 +7166,24 @@ get_merge_query_def(Query *query, deparse_context *context, appendContextKeyword(context, " WHEN ", -PRETTYINDENT_STD, PRETTYINDENT_STD, 2); - appendStringInfo(buf, "%sMATCHED", action->matched ? "" : "NOT "); + switch (action->matchKind) + { + case MERGE_WHEN_MATCHED: + appendStringInfo(buf, "MATCHED"); + break; + case MERGE_WHEN_NOT_MATCHED_BY_SOURCE: + appendStringInfo(buf, "NOT MATCHED BY SOURCE"); + break; + case MERGE_WHEN_NOT_MATCHED_BY_TARGET: + if (haveNotMatchedBySource) + appendStringInfo(buf, "NOT MATCHED BY TARGET"); + else + appendStringInfo(buf, "NOT MATCHED"); + break; + default: + elog(ERROR, "unrecognized matchKind: %d", + (int) action->matchKind); + } if (action->qual) { diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index f121216ddc4..fc6865fc703 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -4336,17 +4336,35 @@ psql_completion(const char *text, int start, int end) TailMatches("USING", MatchAny, MatchAny, "ON", MatchAny, MatchAny, MatchAny, "WHEN")) COMPLETE_WITH("MATCHED", "NOT MATCHED"); - /* Complete ... WHEN [NOT] MATCHED with THEN/AND */ + /* + * Complete ... WHEN MATCHED and WHEN NOT MATCHED BY SOURCE|TARGET with + * THEN/AND + */ else if (TailMatches("WHEN", "MATCHED") || - TailMatches("WHEN", "NOT", "MATCHED")) + TailMatches("WHEN", "NOT", "MATCHED", "BY", "SOURCE|TARGET")) COMPLETE_WITH("THEN", "AND"); - /* Complete ... WHEN MATCHED THEN with UPDATE SET/DELETE/DO NOTHING */ - else if (TailMatches("WHEN", "MATCHED", "THEN")) + /* Complete ... WHEN NOT MATCHED with BY/THEN/AND */ + else if (TailMatches("WHEN", "NOT", "MATCHED")) + COMPLETE_WITH("BY", "THEN", "AND"); + + /* Complete ... WHEN NOT MATCHED BY with SOURCE/TARGET */ + else if (TailMatches("WHEN", "NOT", "MATCHED", "BY")) + COMPLETE_WITH("SOURCE", "TARGET"); + + /* + * Complete ... WHEN MATCHED THEN and WHEN NOT MATCHED BY SOURCE THEN with + * UPDATE SET/DELETE/DO NOTHING + */ + else if (TailMatches("WHEN", "MATCHED", "THEN") || + TailMatches("WHEN", "NOT", "MATCHED", "BY", "SOURCE", "THEN")) COMPLETE_WITH("UPDATE SET", "DELETE", "DO NOTHING"); - /* Complete ... WHEN NOT MATCHED THEN with INSERT/DO NOTHING */ - else if (TailMatches("WHEN", "NOT", "MATCHED", "THEN")) + /* + * Complete ... WHEN NOT MATCHED [BY TARGET] THEN with INSERT/DO NOTHING + */ + else if (TailMatches("WHEN", "NOT", "MATCHED", "THEN") || + TailMatches("WHEN", "NOT", "MATCHED", "BY", "TARGET", "THEN")) COMPLETE_WITH("INSERT", "DO NOTHING"); /* NOTIFY --- can be inside EXPLAIN, RULE, etc */ diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 7d8e002c776..0303973822e 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -57,6 +57,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202403291 +#define CATALOG_VERSION_NO 202403301 #endif diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 1774c56ae31..e7ff8e4992f 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -544,9 +544,11 @@ typedef struct ResultRelInfo /* ON CONFLICT evaluation state */ OnConflictSetState *ri_onConflict; - /* for MERGE, lists of MergeActionState */ - List *ri_matchedMergeAction; - List *ri_notMatchedMergeAction; + /* for MERGE, lists of MergeActionState (one per MergeMatchKind) */ + List *ri_MergeActions[3]; + + /* for MERGE, expr state for checking the join condition */ + ExprState *ri_MergeJoinCondition; /* partition check expression state (NULL if not set up yet) */ ExprState *ri_PartitionCheckExpr; @@ -1401,6 +1403,13 @@ typedef struct ModifyTableState /* For MERGE, the action currently being executed */ MergeActionState *mt_merge_action; + /* + * For MERGE, if there is a pending NOT MATCHED [BY TARGET] action to be + * performed, this will be the last tuple read from the subplan; otherwise + * it will be NULL --- see the comments in ExecMerge(). + */ + TupleTableSlot *mt_merge_pending_not_matched; + /* 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 b89baef95d3..a690ebc6e51 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -176,8 +176,6 @@ typedef struct Query * also USING clause for MERGE */ List *mergeActionList; /* list of actions for MERGE (only) */ - /* whether to use outer join */ - bool mergeUseOuterJoin pg_node_attr(query_jumble_ignore); /* * rtable index of target relation for MERGE to pull data. Initially, this @@ -187,6 +185,9 @@ typedef struct Query */ int mergeTargetRelation pg_node_attr(query_jumble_ignore); + /* join condition between source and target for MERGE */ + Node *mergeJoinCondition; + List *targetList; /* target list (of TargetEntry) */ /* OVERRIDING clause */ @@ -1705,7 +1706,7 @@ typedef struct CommonTableExpr typedef struct MergeWhenClause { NodeTag type; - bool matched; /* true=MATCHED, false=NOT MATCHED */ + MergeMatchKind matchKind; /* MATCHED/NOT MATCHED BY SOURCE/TARGET */ CmdType commandType; /* INSERT/UPDATE/DELETE/DO NOTHING */ OverridingKind override; /* OVERRIDING clause */ Node *condition; /* WHEN conditions (raw parser) */ diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index 595eec2cbbd..0ab25d9ce7b 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -2372,6 +2372,8 @@ typedef struct ModifyTablePath int epqParam; /* ID of Param for EvalPlanQual re-eval */ List *mergeActionLists; /* per-target-table lists of actions for * MERGE */ + List *mergeJoinConditions; /* per-target-table join conditions + * for MERGE */ } ModifyTablePath; /* diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index 7f3db5105db..e025679f890 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -251,6 +251,8 @@ typedef struct ModifyTable List *exclRelTlist; /* tlist of the EXCLUDED pseudo relation */ List *mergeActionLists; /* per-target-table lists of actions for * MERGE */ + List *mergeJoinConditions; /* per-target-table join conditions + * for MERGE */ } ModifyTable; struct PartitionPruneInfo; /* forward reference to struct below */ diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 376f67e6a5f..aa727e722cc 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1875,10 +1875,18 @@ typedef struct BooleanTest * * Transformed representation of a WHEN clause in a MERGE statement */ + +typedef enum MergeMatchKind +{ + MERGE_WHEN_MATCHED, + MERGE_WHEN_NOT_MATCHED_BY_SOURCE, + MERGE_WHEN_NOT_MATCHED_BY_TARGET +} MergeMatchKind; + typedef struct MergeAction { NodeTag type; - bool matched; /* true=MATCHED, false=NOT MATCHED */ + MergeMatchKind matchKind; /* MATCHED/NOT MATCHED BY SOURCE/TARGET */ CmdType commandType; /* INSERT/UPDATE/DELETE/DO NOTHING */ /* OVERRIDING clause */ OverridingKind override pg_node_attr(query_jumble_ignore); diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h index 7cc481b8c2d..c5c4756b0fc 100644 --- a/src/include/optimizer/pathnode.h +++ b/src/include/optimizer/pathnode.h @@ -283,7 +283,8 @@ extern ModifyTablePath *create_modifytable_path(PlannerInfo *root, List *updateColnosLists, List *withCheckOptionLists, List *returningLists, List *rowMarks, OnConflictExpr *onconflict, - List *mergeActionLists, int epqParam); + List *mergeActionLists, List *mergeJoinConditions, + int epqParam); extern LimitPath *create_limit_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath, Node *limitOffset, Node *limitCount, diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 57514d064b7..6c959e85d54 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -414,6 +414,7 @@ PG_KEYWORD("skip", SKIP, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("smallint", SMALLINT, COL_NAME_KEYWORD, BARE_LABEL) PG_KEYWORD("snapshot", SNAPSHOT, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("some", SOME, RESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("source", SOURCE, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("sql", SQL_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("stable", STABLE, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("standalone", STANDALONE_P, UNRESERVED_KEYWORD, BARE_LABEL) @@ -438,6 +439,7 @@ PG_KEYWORD("table", TABLE, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("tables", TABLES, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("tablesample", TABLESAMPLE, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL) PG_KEYWORD("tablespace", TABLESPACE, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("target", TARGET, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("temp", TEMP, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("template", TEMPLATE, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("temporary", TEMPORARY, UNRESERVED_KEYWORD, BARE_LABEL) diff --git a/src/test/isolation/expected/merge-update.out b/src/test/isolation/expected/merge-update.out index f5f7e3ba19b..3063c0c6ab1 100644 --- a/src/test/isolation/expected/merge-update.out +++ b/src/test/isolation/expected/merge-update.out @@ -37,13 +37,22 @@ step merge2a: WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN - UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; + UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val + WHEN NOT MATCHED BY SOURCE THEN + UPDATE set key = t.key + 1, val = t.val || ' source not matched by merge2a' + RETURNING merge_action(), t.*; + +merge_action|key|val +------------+---+------------------------------------------------------ +UPDATE | 3|setup1 updated by merge1 source not matched by merge2a +INSERT | 1|merge2a +(2 rows) step select2: SELECT * FROM target; -key|val ----+------------------------ - 2|setup1 updated by merge1 - 1|merge2a +key|val +---+------------------------------------------------------ + 3|setup1 updated by merge1 source not matched by merge2a + 1|merge2a (2 rows) step c2: COMMIT; @@ -86,15 +95,24 @@ step merge2a: WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN - UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; + UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val + WHEN NOT MATCHED BY SOURCE THEN + UPDATE set key = t.key + 1, val = t.val || ' source not matched by merge2a' + RETURNING merge_action(), t.*; <waiting ...> step c1: COMMIT; step merge2a: <... completed> +merge_action|key|val +------------+---+------------------------------------------------------ +UPDATE | 3|setup1 updated by merge1 source not matched by merge2a +INSERT | 1|merge2a +(2 rows) + step select2: SELECT * FROM target; -key|val ----+------------------------ - 2|setup1 updated by merge1 - 1|merge2a +key|val +---+------------------------------------------------------ + 3|setup1 updated by merge1 source not matched by merge2a + 1|merge2a (2 rows) step c2: COMMIT; @@ -116,10 +134,18 @@ step merge2a: WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN - UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; + UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val + WHEN NOT MATCHED BY SOURCE THEN + UPDATE set key = t.key + 1, val = t.val || ' source not matched by merge2a' + RETURNING merge_action(), t.*; <waiting ...> step a1: ABORT; step merge2a: <... completed> +merge_action|key|val +------------+---+------------------------- +UPDATE | 2|setup1 updated by merge2a +(1 row) + step select2: SELECT * FROM target; key|val ---+------------------------- @@ -205,15 +231,24 @@ step pa_merge2a: WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN - UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; + UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val + WHEN NOT MATCHED BY SOURCE THEN + UPDATE set key = t.key + 1, val = t.val || ' source not matched by pa_merge2a' + RETURNING merge_action(), t.*; <waiting ...> step c1: COMMIT; step pa_merge2a: <... completed> +merge_action|key|val +------------+---+-------------------------------------------------- +UPDATE | 2|initial updated by pa_merge1 updated by pa_merge2a +UPDATE | 3|initial source not matched by pa_merge2a +(2 rows) + step pa_select2: SELECT * FROM pa_target; key|val ---+-------------------------------------------------- - 2|initial 2|initial updated by pa_merge1 updated by pa_merge2a + 3|initial source not matched by pa_merge2a (2 rows) step c2: COMMIT; @@ -235,7 +270,10 @@ step pa_merge2a: WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN - UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; + UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val + WHEN NOT MATCHED BY SOURCE THEN + UPDATE set key = t.key + 1, val = t.val || ' source not matched by pa_merge2a' + RETURNING merge_action(), t.*; <waiting ...> step c1: COMMIT; step pa_merge2a: <... completed> @@ -262,14 +300,24 @@ step pa_merge2a: WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN - UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; + UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val + WHEN NOT MATCHED BY SOURCE THEN + UPDATE set key = t.key + 1, val = t.val || ' source not matched by pa_merge2a' + RETURNING merge_action(), t.*; + +merge_action|key|val +------------+---+------------------------------------------------------------- +UPDATE | 3|initial source not matched by pa_merge2a +UPDATE | 3|initial updated by pa_merge2 source not matched by pa_merge2a +INSERT | 1|pa_merge2a +(3 rows) step pa_select2: SELECT * FROM pa_target; -key|val ----+---------------------------- - 1|pa_merge2a - 2|initial - 2|initial updated by pa_merge2 +key|val +---+------------------------------------------------------------- + 1|pa_merge2a + 3|initial source not matched by pa_merge2a + 3|initial updated by pa_merge2 source not matched by pa_merge2a (3 rows) step c2: COMMIT; diff --git a/src/test/isolation/specs/merge-update.spec b/src/test/isolation/specs/merge-update.spec index 3ccd4664498..a33dcdba537 100644 --- a/src/test/isolation/specs/merge-update.spec +++ b/src/test/isolation/specs/merge-update.spec @@ -92,7 +92,10 @@ step "merge2a" WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN - UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; + UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val + WHEN NOT MATCHED BY SOURCE THEN + UPDATE set key = t.key + 1, val = t.val || ' source not matched by merge2a' + RETURNING merge_action(), t.*; } step "merge2b" { @@ -122,7 +125,10 @@ step "pa_merge2a" WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN - UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; + UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val + WHEN NOT MATCHED BY SOURCE THEN + UPDATE set key = t.key + 1, val = t.val || ' source not matched by pa_merge2a' + RETURNING merge_action(), t.*; } # MERGE proceeds only if 'val' unchanged step "pa_merge2b_when" diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out index cec7f11f9f5..eddc1f40c60 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -72,6 +72,15 @@ WHEN MATCHED THEN ERROR: syntax error at or near "INSERT" LINE 5: INSERT DEFAULT VALUES; ^ +-- NOT MATCHED BY SOURCE/INSERT error +MERGE INTO target t +USING source AS s +ON t.tid = s.sid +WHEN NOT MATCHED BY SOURCE THEN + INSERT DEFAULT VALUES; +ERROR: syntax error at or near "INSERT" +LINE 5: INSERT DEFAULT VALUES; + ^ -- incorrectly specifying INTO target MERGE INTO target t USING source AS s @@ -108,6 +117,15 @@ WHEN NOT MATCHED THEN ERROR: syntax error at or near "UPDATE" LINE 5: UPDATE SET balance = 0; ^ +-- NOT MATCHED BY TARGET/UPDATE +MERGE INTO target t +USING source AS s +ON t.tid = s.sid +WHEN NOT MATCHED BY TARGET THEN + UPDATE SET balance = 0; +ERROR: syntax error at or near "UPDATE" +LINE 5: UPDATE SET balance = 0; + ^ -- UPDATE tablename MERGE INTO target t USING source AS s @@ -270,6 +288,31 @@ SELECT * FROM target ORDER BY tid; (4 rows) ROLLBACK; +-- DELETE/INSERT not matched by source/target +BEGIN; +MERGE INTO target t +USING source AS s +ON t.tid = s.sid +WHEN NOT MATCHED BY SOURCE THEN + DELETE +WHEN NOT MATCHED BY TARGET THEN + INSERT VALUES (s.sid, s.delta) +RETURNING merge_action(), t.*; + merge_action | tid | balance +--------------+-----+--------- + DELETE | 1 | 10 + DELETE | 2 | 20 + DELETE | 3 | 30 + INSERT | 4 | 40 +(4 rows) + +SELECT * FROM target ORDER BY tid; + tid | balance +-----+--------- + 4 | 40 +(1 row) + +ROLLBACK; -- index plans INSERT INTO target SELECT generate_series(1000,2500), 0; ALTER TABLE target ADD PRIMARY KEY (tid); @@ -737,6 +780,19 @@ SELECT * FROM wq_target; 1 | -1 (1 row) +-- conditions in NOT MATCHED BY SOURCE clause can only refer to target columns +MERGE INTO wq_target t +USING wq_source s ON t.tid = s.sid +WHEN NOT MATCHED BY SOURCE AND s.balance = 100 THEN + DELETE; +ERROR: invalid reference to FROM-clause entry for table "s" +LINE 3: WHEN NOT MATCHED BY SOURCE AND s.balance = 100 THEN + ^ +DETAIL: There is an entry for table "s", but it cannot be referenced from this part of the query. +MERGE INTO wq_target t +USING wq_source s ON t.tid = s.sid +WHEN NOT MATCHED BY SOURCE AND t.balance = 100 THEN + DELETE; -- conditions in MATCHED clause can refer to both source and target SELECT * FROM wq_source; balance | sid @@ -922,6 +978,54 @@ SELECT * FROM target ORDER BY tid; (3 rows) ROLLBACK; +-- UPSERT with UPDATE/DELETE when not matched by source +BEGIN; +DELETE FROM SOURCE WHERE sid = 2; +MERGE INTO target t +USING source AS s +ON t.tid = s.sid +WHEN MATCHED AND t.balance > s.delta THEN + UPDATE SET balance = t.balance - s.delta +WHEN MATCHED THEN + UPDATE SET balance = 0 +WHEN NOT MATCHED THEN + INSERT VALUES (s.sid, s.delta) +WHEN NOT MATCHED BY SOURCE AND tid = 1 THEN + UPDATE SET balance = 0 +WHEN NOT MATCHED BY SOURCE THEN + DELETE +RETURNING merge_action(), t.*; +NOTICE: BEFORE INSERT STATEMENT trigger +NOTICE: BEFORE UPDATE STATEMENT trigger +NOTICE: BEFORE DELETE STATEMENT trigger +NOTICE: BEFORE UPDATE ROW trigger row: (3,30) -> (3,10) +NOTICE: BEFORE INSERT ROW trigger row: (4,40) +NOTICE: BEFORE DELETE ROW trigger row: (2,20) +NOTICE: BEFORE UPDATE ROW trigger row: (1,10) -> (1,0) +NOTICE: AFTER UPDATE ROW trigger row: (3,30) -> (3,10) +NOTICE: AFTER INSERT ROW trigger row: (4,40) +NOTICE: AFTER DELETE ROW trigger row: (2,20) +NOTICE: AFTER UPDATE ROW trigger row: (1,10) -> (1,0) +NOTICE: AFTER DELETE STATEMENT trigger +NOTICE: AFTER UPDATE STATEMENT trigger +NOTICE: AFTER INSERT STATEMENT trigger + merge_action | tid | balance +--------------+-----+--------- + UPDATE | 3 | 10 + INSERT | 4 | 40 + DELETE | 2 | 20 + UPDATE | 1 | 0 +(4 rows) + +SELECT * FROM target ORDER BY tid; + tid | balance +-----+--------- + 1 | 0 + 3 | 10 + 4 | 40 +(3 rows) + +ROLLBACK; -- Test behavior of triggers that turn UPDATE/DELETE into no-ops create or replace function skip_merge_op() returns trigger language plpgsql as @@ -1636,6 +1740,50 @@ WHEN NOT MATCHED AND s.a < 20 THEN -> Seq Scan on ex_mtarget t (actual rows=49 loops=1) (12 rows) +-- not matched by source +SELECT explain_merge(' +MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a +WHEN NOT MATCHED BY SOURCE and t.a < 10 THEN + DELETE'); + explain_merge +---------------------------------------------------------------------- + Merge on ex_mtarget t (actual rows=0 loops=1) + Tuples: skipped=54 + -> Merge Left Join (actual rows=54 loops=1) + Merge Cond: (t.a = s.a) + -> Sort (actual rows=54 loops=1) + Sort Key: t.a + Sort Method: quicksort Memory: xxx + -> Seq Scan on ex_mtarget t (actual rows=54 loops=1) + -> Sort (actual rows=100 loops=1) + Sort Key: s.a + Sort Method: quicksort Memory: xxx + -> Seq Scan on ex_msource s (actual rows=100 loops=1) +(12 rows) + +-- not matched by source and target +SELECT explain_merge(' +MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a +WHEN NOT MATCHED BY SOURCE AND t.a < 10 THEN + DELETE +WHEN NOT MATCHED BY TARGET AND s.a < 20 THEN + INSERT VALUES (a, b)'); + explain_merge +---------------------------------------------------------------------- + Merge on ex_mtarget t (actual rows=0 loops=1) + Tuples: skipped=100 + -> Merge Full Join (actual rows=100 loops=1) + Merge Cond: (t.a = s.a) + -> Sort (actual rows=54 loops=1) + Sort Key: t.a + Sort Method: quicksort Memory: xxx + -> Seq Scan on ex_mtarget t (actual rows=54 loops=1) + -> Sort (actual rows=100 loops=1) + Sort Key: s.a + Sort Method: quicksort Memory: xxx + -> Seq Scan on ex_msource s (actual rows=100 loops=1) +(12 rows) + -- nothing SELECT explain_merge(' MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a AND t.a < -1000 @@ -1765,7 +1913,7 @@ CREATE TABLE pa_source (sid integer, delta float); -- insert many rows to the source table INSERT INTO pa_source SELECT id, id * 10 FROM generate_series(1,14) AS id; -- insert a few rows in the target table (odd numbered tid) -INSERT INTO pa_target SELECT id, id * 100, 'initial' FROM generate_series(1,14,2) AS id; +INSERT INTO pa_target SELECT id, id * 100, 'initial' FROM generate_series(1,15,2) AS id; -- try simple MERGE BEGIN; MERGE INTO pa_target t @@ -1774,10 +1922,12 @@ MERGE INTO pa_target t WHEN MATCHED THEN UPDATE SET balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN - INSERT VALUES (sid, delta, 'inserted by merge'); -SELECT * FROM pa_target ORDER BY tid; - tid | balance | val ------+---------+-------------------------- + INSERT VALUES (sid, delta, 'inserted by merge') + WHEN NOT MATCHED BY SOURCE THEN + UPDATE SET val = val || ' not matched by source'; +SELECT * FROM pa_target ORDER BY tid, val; + tid | balance | val +-----+---------+------------------------------- 1 | 110 | initial updated by merge 2 | 20 | inserted by merge 3 | 330 | initial updated by merge @@ -1792,7 +1942,8 @@ SELECT * FROM pa_target ORDER BY tid; 12 | 120 | inserted by merge 13 | 1430 | initial updated by merge 14 | 140 | inserted by merge -(14 rows) + 15 | 1500 | initial not matched by source +(15 rows) ROLLBACK; -- same with a constant qual @@ -1803,31 +1954,34 @@ MERGE INTO pa_target t WHEN MATCHED THEN UPDATE SET balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN - INSERT VALUES (sid, delta, 'inserted by merge'); -SELECT * FROM pa_target ORDER BY tid; - tid | balance | val ------+---------+-------------------------- + INSERT VALUES (sid, delta, 'inserted by merge') + WHEN NOT MATCHED BY SOURCE THEN + UPDATE SET val = val || ' not matched by source'; +SELECT * FROM pa_target ORDER BY tid, val; + tid | balance | val +-----+---------+------------------------------- 1 | 110 | initial updated by merge 2 | 20 | inserted by merge + 3 | 300 | initial not matched by source 3 | 30 | inserted by merge - 3 | 300 | initial 4 | 40 | inserted by merge - 5 | 500 | initial + 5 | 500 | initial not matched by source 5 | 50 | inserted by merge 6 | 60 | inserted by merge - 7 | 700 | initial + 7 | 700 | initial not matched by source 7 | 70 | inserted by merge 8 | 80 | inserted by merge + 9 | 900 | initial not matched by source 9 | 90 | inserted by merge - 9 | 900 | initial 10 | 100 | inserted by merge - 11 | 1100 | initial + 11 | 1100 | initial not matched by source 11 | 110 | inserted by merge 12 | 120 | inserted by merge - 13 | 1300 | initial + 13 | 1300 | initial not matched by source 13 | 130 | inserted by merge 14 | 140 | inserted by merge -(20 rows) + 15 | 1500 | initial not matched by source +(21 rows) ROLLBACK; -- try updating the partition key column @@ -1842,7 +1996,9 @@ MERGE INTO pa_target t WHEN MATCHED THEN UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN - INSERT VALUES (sid, delta, 'inserted by merge'); + INSERT VALUES (sid, delta, 'inserted by merge') + WHEN NOT MATCHED BY SOURCE THEN + UPDATE SET tid = 1, val = val || ' not matched by source'; IF FOUND THEN GET DIAGNOSTICS result := ROW_COUNT; END IF; @@ -1852,27 +2008,28 @@ $$; SELECT merge_func(); merge_func ------------ - 14 + 15 (1 row) -SELECT * FROM pa_target ORDER BY tid; - tid | balance | val ------+---------+-------------------------- +SELECT * FROM pa_target ORDER BY tid, val; + tid | balance | val +-----+---------+------------------------------- + 1 | 1500 | initial not matched by source 2 | 110 | initial updated by merge 2 | 20 | inserted by merge - 4 | 40 | inserted by merge 4 | 330 | initial updated by merge + 4 | 40 | inserted by merge 6 | 550 | initial updated by merge 6 | 60 | inserted by merge - 8 | 80 | inserted by merge 8 | 770 | initial updated by merge + 8 | 80 | inserted by merge 10 | 990 | initial updated by merge 10 | 100 | inserted by merge 12 | 1210 | initial updated by merge 12 | 120 | inserted by merge 14 | 1430 | initial updated by merge 14 | 140 | inserted by merge -(14 rows) +(15 rows) ROLLBACK; -- update partition key to partition not initially scanned @@ -1898,7 +2055,8 @@ SELECT * FROM pa_target ORDER BY tid; 9 | 900 | initial 11 | 1100 | initial 13 | 1300 | initial -(7 rows) + 15 | 1500 | initial +(8 rows) ROLLBACK; DROP TABLE pa_target CASCADE; @@ -1920,7 +2078,7 @@ ALTER TABLE pa_target ATTACH PARTITION part2 FOR VALUES IN (2,5,6); ALTER TABLE pa_target ATTACH PARTITION part3 FOR VALUES IN (3,8,9); ALTER TABLE pa_target ATTACH PARTITION part4 DEFAULT; -- insert a few rows in the target table (odd numbered tid) -INSERT INTO pa_target SELECT id, id * 100, 'initial' FROM generate_series(1,14,2) AS id; +INSERT INTO pa_target SELECT id, id * 100, 'initial' FROM generate_series(1,15,2) AS id; -- try simple MERGE BEGIN; DO $$ @@ -1933,15 +2091,17 @@ MERGE INTO pa_target t WHEN MATCHED THEN UPDATE SET balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN - INSERT VALUES (sid, delta, 'inserted by merge'); + INSERT VALUES (sid, delta, 'inserted by merge') + WHEN NOT MATCHED BY SOURCE THEN + UPDATE SET val = val || ' not matched by source'; GET DIAGNOSTICS result := ROW_COUNT; RAISE NOTICE 'ROW_COUNT = %', result; END; $$; -NOTICE: ROW_COUNT = 14 -SELECT * FROM pa_target ORDER BY tid; - tid | balance | val ------+---------+-------------------------- +NOTICE: ROW_COUNT = 15 +SELECT * FROM pa_target ORDER BY tid, val; + tid | balance | val +-----+---------+------------------------------- 1 | 110 | initial updated by merge 2 | 20 | inserted by merge 3 | 330 | initial updated by merge @@ -1956,7 +2116,8 @@ SELECT * FROM pa_target ORDER BY tid; 12 | 120 | inserted by merge 13 | 1430 | initial updated by merge 14 | 140 | inserted by merge -(14 rows) + 15 | 1500 | initial not matched by source +(15 rows) ROLLBACK; -- same with a constant qual @@ -1968,29 +2129,32 @@ MERGE INTO pa_target t WHEN MATCHED THEN UPDATE SET balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN - INSERT VALUES (sid, delta, 'inserted by merge'); -SELECT * FROM pa_target ORDER BY tid; - tid | balance | val ------+---------+-------------------------- + INSERT VALUES (sid, delta, 'inserted by merge') + WHEN NOT MATCHED BY SOURCE THEN + UPDATE SET val = val || ' not matched by source'; +SELECT * FROM pa_target ORDER BY tid, val; + tid | balance | val +-----+---------+------------------------------- 1 | 110 | initial updated by merge 2 | 20 | inserted by merge + 3 | 300 | initial not matched by source 3 | 30 | inserted by merge - 3 | 300 | initial 4 | 40 | inserted by merge 6 | 60 | inserted by merge - 7 | 700 | initial + 7 | 700 | initial not matched by source 7 | 70 | inserted by merge 8 | 80 | inserted by merge - 9 | 900 | initial + 9 | 900 | initial not matched by source 9 | 90 | inserted by merge 10 | 100 | inserted by merge + 11 | 1100 | initial not matched by source 11 | 110 | inserted by merge - 11 | 1100 | initial 12 | 120 | inserted by merge - 13 | 1300 | initial + 13 | 1300 | initial not matched by source 13 | 130 | inserted by merge 14 | 140 | inserted by merge -(18 rows) + 15 | 1500 | initial not matched by source +(19 rows) ROLLBACK; -- try updating the partition key column @@ -2005,30 +2169,33 @@ MERGE INTO pa_target t WHEN MATCHED THEN UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN - INSERT VALUES (sid, delta, 'inserted by merge'); + INSERT VALUES (sid, delta, 'inserted by merge') + WHEN NOT MATCHED BY SOURCE THEN + UPDATE SET tid = 1, val = val || ' not matched by source'; GET DIAGNOSTICS result := ROW_COUNT; RAISE NOTICE 'ROW_COUNT = %', result; END; $$; -NOTICE: ROW_COUNT = 14 -SELECT * FROM pa_target ORDER BY tid; - tid | balance | val ------+---------+-------------------------- +NOTICE: ROW_COUNT = 15 +SELECT * FROM pa_target ORDER BY tid, val; + tid | balance | val +-----+---------+------------------------------- + 1 | 1500 | initial not matched by source 2 | 110 | initial updated by merge 2 | 20 | inserted by merge - 4 | 40 | inserted by merge 4 | 330 | initial updated by merge + 4 | 40 | inserted by merge 6 | 550 | initial updated by merge 6 | 60 | inserted by merge - 8 | 80 | inserted by merge 8 | 770 | initial updated by merge + 8 | 80 | inserted by merge 10 | 990 | initial updated by merge 10 | 100 | inserted by merge 12 | 1210 | initial updated by merge 12 | 120 | inserted by merge 14 | 1430 | initial updated by merge 14 | 140 | inserted by merge -(14 rows) +(15 rows) ROLLBACK; -- as above, but blocked by BEFORE DELETE ROW trigger @@ -2047,15 +2214,17 @@ MERGE INTO pa_target t WHEN MATCHED THEN UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN - INSERT VALUES (sid, delta, 'inserted by merge'); + INSERT VALUES (sid, delta, 'inserted by merge') + WHEN NOT MATCHED BY SOURCE THEN + UPDATE SET val = val || ' not matched by source'; GET DIAGNOSTICS result := ROW_COUNT; RAISE NOTICE 'ROW_COUNT = %', result; END; $$; -NOTICE: ROW_COUNT = 10 -SELECT * FROM pa_target ORDER BY tid; - tid | balance | val ------+---------+-------------------------- +NOTICE: ROW_COUNT = 11 +SELECT * FROM pa_target ORDER BY tid, val; + tid | balance | val +-----+---------+------------------------------- 1 | 100 | initial 2 | 20 | inserted by merge 3 | 300 | initial @@ -2070,7 +2239,8 @@ SELECT * FROM pa_target ORDER BY tid; 12 | 120 | inserted by merge 14 | 1430 | initial updated by merge 14 | 140 | inserted by merge -(14 rows) + 15 | 1500 | initial not matched by source +(15 rows) ROLLBACK; -- as above, but blocked by BEFORE INSERT ROW trigger @@ -2089,19 +2259,22 @@ MERGE INTO pa_target t WHEN MATCHED THEN UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN - INSERT VALUES (sid, delta, 'inserted by merge'); + INSERT VALUES (sid, delta, 'inserted by merge') + WHEN NOT MATCHED BY SOURCE THEN + UPDATE SET val = val || ' not matched by source'; GET DIAGNOSTICS result := ROW_COUNT; RAISE NOTICE 'ROW_COUNT = %', result; END; $$; -NOTICE: ROW_COUNT = 3 -SELECT * FROM pa_target ORDER BY tid; - tid | balance | val ------+---------+-------------------------- +NOTICE: ROW_COUNT = 4 +SELECT * FROM pa_target ORDER BY tid, val; + tid | balance | val +-----+---------+------------------------------- 6 | 550 | initial updated by merge 12 | 1210 | initial updated by merge 14 | 1430 | initial updated by merge -(3 rows) + 15 | 1500 | initial not matched by source +(4 rows) ROLLBACK; -- test RLS enforcement diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index f53c3036a64..5e45ce64f7a 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -3728,7 +3728,39 @@ BEGIN ATOMIC t.data, t.filling; END +CREATE FUNCTION merge_sf_test2() + RETURNS void + LANGUAGE sql +BEGIN ATOMIC + MERGE INTO sf_target t + USING rule_merge1 s + ON (s.a = t.id) +WHEN NOT MATCHED + THEN INSERT (data, id) + VALUES (s.a, s.a) +WHEN MATCHED + THEN UPDATE SET data = s.b +WHEN NOT MATCHED BY SOURCE + THEN DELETE; +END; +\sf merge_sf_test2 +CREATE OR REPLACE FUNCTION public.merge_sf_test2() + RETURNS void + LANGUAGE sql +BEGIN ATOMIC + MERGE INTO sf_target t + USING rule_merge1 s + ON (s.a = t.id) + WHEN NOT MATCHED BY TARGET + THEN INSERT (data, id) + VALUES (s.a, s.a) + WHEN MATCHED + THEN UPDATE SET data = s.b + WHEN NOT MATCHED BY SOURCE + THEN DELETE; +END DROP FUNCTION merge_sf_test; +DROP FUNCTION merge_sf_test2; DROP TABLE sf_target; -- -- Test enabling/disabling diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out index 713bf84c702..1d1f568bc47 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -499,6 +499,32 @@ SELECT * FROM base_tbl ORDER BY a; 5 | Unspecified (6 rows) +MERGE INTO rw_view1 t + USING (VALUES (0, 'R0'), (1, 'R1'), + (2, 'R2'), (3, 'R3')) 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 BY SOURCE THEN DELETE + 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 | R1 | 1 | R1 + DELETE | | | 5 | Unspecified + DELETE | 2 | R2 | 2 | Unspecified + INSERT | 3 | R3 | 3 | Unspecified +(4 rows) + +SELECT * FROM base_tbl ORDER BY a; + a | b +----+------------- + -2 | Row -2 + -1 | Row -1 + 0 | Row 0 + 1 | R1 + 3 | Unspecified +(5 rows) + EXPLAIN (costs off) UPDATE rw_view1 SET a=6 WHERE a=5; QUERY PLAN -------------------------------------------------- @@ -545,6 +571,23 @@ MERGE INTO rw_view1 t EXPLAIN (costs off) MERGE INTO rw_view1 t USING (SELECT * FROM generate_series(1,5)) AS s(a) ON t.a = s.a + WHEN NOT MATCHED BY SOURCE THEN DELETE; + QUERY PLAN +------------------------------------------------------------------- + Merge on base_tbl + -> Hash Left Join + Hash Cond: (base_tbl.a = generate_series.generate_series) + -> Bitmap Heap Scan on base_tbl + Recheck Cond: (a > 0) + -> Bitmap Index Scan on base_tbl_pkey + Index Cond: (a > 0) + -> Hash + -> Function Scan on generate_series +(9 rows) + +EXPLAIN (costs off) +MERGE INTO rw_view1 t + USING (SELECT * FROM generate_series(1,5)) AS s(a) ON t.a = s.a WHEN NOT MATCHED THEN INSERT (a) VALUES (s.a); QUERY PLAN ------------------------------------------------------------------- @@ -656,6 +699,29 @@ SELECT * FROM rw_view2 ORDER BY aaa; 5 | Unspecified (3 rows) +MERGE INTO rw_view2 t + USING (VALUES (4, 'r4'), (5, 'r5'), (6, 'r6')) AS v(a,b) ON aaa = v.a + WHEN MATCHED AND aaa = 4 THEN DELETE + WHEN MATCHED THEN UPDATE SET bbb = v.b + WHEN NOT MATCHED THEN INSERT (aaa) VALUES (v.a) + WHEN NOT MATCHED BY SOURCE THEN UPDATE SET bbb = 'Not matched by source' + RETURNING merge_action(), v.*, t.*; + merge_action | a | b | aaa | bbb +--------------+---+----+-----+----------------------- + UPDATE | | | 1 | Not matched by source + DELETE | 4 | r4 | 4 | R4 + UPDATE | 5 | r5 | 5 | r5 + INSERT | 6 | r6 | 6 | Unspecified +(4 rows) + +SELECT * FROM rw_view2 ORDER BY aaa; + aaa | bbb +-----+----------------------- + 1 | Not matched by source + 5 | r5 + 6 | Unspecified +(3 rows) + EXPLAIN (costs off) UPDATE rw_view2 SET aaa=5 WHERE aaa=4; QUERY PLAN -------------------------------------------------------- @@ -1106,6 +1172,30 @@ SELECT * FROM base_tbl ORDER BY a; 3 | R3 (5 rows) +MERGE INTO rw_view2 t + USING (SELECT x, 'r'||x FROM generate_series(0,2) x) AS s(a,b) ON t.a = s.a + 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 BY SOURCE THEN UPDATE SET b = 'Not matched by source' + RETURNING merge_action(), s.*, t.*; + merge_action | a | b | a | b +--------------+---+----+---+----------------------- + UPDATE | 2 | r2 | 2 | r2 + UPDATE | | | 3 | Not matched by source + INSERT | 1 | r1 | 1 | r1 +(3 rows) + +SELECT * FROM base_tbl ORDER BY a; + a | b +----+----------------------- + -2 | Row -2 + -1 | Row -1 + 0 | Row 0 + 1 | r1 + 2 | r2 + 3 | Not matched by source +(6 rows) + EXPLAIN (costs off) UPDATE rw_view2 SET a=3 WHERE a=2; QUERY PLAN ---------------------------------------------------------- diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql index 875cf6f93d7..3d5d854878b 100644 --- a/src/test/regress/sql/merge.sql +++ b/src/test/regress/sql/merge.sql @@ -53,6 +53,12 @@ USING source AS s ON t.tid = s.sid WHEN MATCHED THEN INSERT DEFAULT VALUES; +-- NOT MATCHED BY SOURCE/INSERT error +MERGE INTO target t +USING source AS s +ON t.tid = s.sid +WHEN NOT MATCHED BY SOURCE THEN + INSERT DEFAULT VALUES; -- incorrectly specifying INTO target MERGE INTO target t USING source AS s @@ -77,6 +83,12 @@ USING source AS s ON t.tid = s.sid WHEN NOT MATCHED THEN UPDATE SET balance = 0; +-- NOT MATCHED BY TARGET/UPDATE +MERGE INTO target t +USING source AS s +ON t.tid = s.sid +WHEN NOT MATCHED BY TARGET THEN + UPDATE SET balance = 0; -- UPDATE tablename MERGE INTO target t USING source AS s @@ -214,6 +226,19 @@ WHEN NOT MATCHED THEN SELECT * FROM target ORDER BY tid; ROLLBACK; +-- DELETE/INSERT not matched by source/target +BEGIN; +MERGE INTO target t +USING source AS s +ON t.tid = s.sid +WHEN NOT MATCHED BY SOURCE THEN + DELETE +WHEN NOT MATCHED BY TARGET THEN + INSERT VALUES (s.sid, s.delta) +RETURNING merge_action(), t.*; +SELECT * FROM target ORDER BY tid; +ROLLBACK; + -- index plans INSERT INTO target SELECT generate_series(1000,2500), 0; ALTER TABLE target ADD PRIMARY KEY (tid); @@ -498,6 +523,17 @@ WHEN NOT MATCHED AND s.balance = 100 THEN INSERT (tid) VALUES (s.sid); SELECT * FROM wq_target; +-- conditions in NOT MATCHED BY SOURCE clause can only refer to target columns +MERGE INTO wq_target t +USING wq_source s ON t.tid = s.sid +WHEN NOT MATCHED BY SOURCE AND s.balance = 100 THEN + DELETE; + +MERGE INTO wq_target t +USING wq_source s ON t.tid = s.sid +WHEN NOT MATCHED BY SOURCE AND t.balance = 100 THEN + DELETE; + -- conditions in MATCHED clause can refer to both source and target SELECT * FROM wq_source; MERGE INTO wq_target t @@ -625,6 +661,26 @@ WHEN NOT MATCHED THEN SELECT * FROM target ORDER BY tid; ROLLBACK; +-- UPSERT with UPDATE/DELETE when not matched by source +BEGIN; +DELETE FROM SOURCE WHERE sid = 2; +MERGE INTO target t +USING source AS s +ON t.tid = s.sid +WHEN MATCHED AND t.balance > s.delta THEN + UPDATE SET balance = t.balance - s.delta +WHEN MATCHED THEN + UPDATE SET balance = 0 +WHEN NOT MATCHED THEN + INSERT VALUES (s.sid, s.delta) +WHEN NOT MATCHED BY SOURCE AND tid = 1 THEN + UPDATE SET balance = 0 +WHEN NOT MATCHED BY SOURCE THEN + DELETE +RETURNING merge_action(), t.*; +SELECT * FROM target ORDER BY tid; +ROLLBACK; + -- Test behavior of triggers that turn UPDATE/DELETE into no-ops create or replace function skip_merge_op() returns trigger language plpgsql as @@ -1061,6 +1117,20 @@ WHEN MATCHED AND t.a >= 30 AND t.a <= 40 THEN WHEN NOT MATCHED AND s.a < 20 THEN INSERT VALUES (a, b)'); +-- not matched by source +SELECT explain_merge(' +MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a +WHEN NOT MATCHED BY SOURCE and t.a < 10 THEN + DELETE'); + +-- not matched by source and target +SELECT explain_merge(' +MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a +WHEN NOT MATCHED BY SOURCE AND t.a < 10 THEN + DELETE +WHEN NOT MATCHED BY TARGET AND s.a < 20 THEN + INSERT VALUES (a, b)'); + -- nothing SELECT explain_merge(' MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a AND t.a < -1000 @@ -1133,7 +1203,7 @@ CREATE TABLE pa_source (sid integer, delta float); -- insert many rows to the source table INSERT INTO pa_source SELECT id, id * 10 FROM generate_series(1,14) AS id; -- insert a few rows in the target table (odd numbered tid) -INSERT INTO pa_target SELECT id, id * 100, 'initial' FROM generate_series(1,14,2) AS id; +INSERT INTO pa_target SELECT id, id * 100, 'initial' FROM generate_series(1,15,2) AS id; -- try simple MERGE BEGIN; @@ -1143,8 +1213,10 @@ MERGE INTO pa_target t WHEN MATCHED THEN UPDATE SET balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN - INSERT VALUES (sid, delta, 'inserted by merge'); -SELECT * FROM pa_target ORDER BY tid; + INSERT VALUES (sid, delta, 'inserted by merge') + WHEN NOT MATCHED BY SOURCE THEN + UPDATE SET val = val || ' not matched by source'; +SELECT * FROM pa_target ORDER BY tid, val; ROLLBACK; -- same with a constant qual @@ -1155,8 +1227,10 @@ MERGE INTO pa_target t WHEN MATCHED THEN UPDATE SET balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN - INSERT VALUES (sid, delta, 'inserted by merge'); -SELECT * FROM pa_target ORDER BY tid; + INSERT VALUES (sid, delta, 'inserted by merge') + WHEN NOT MATCHED BY SOURCE THEN + UPDATE SET val = val || ' not matched by source'; +SELECT * FROM pa_target ORDER BY tid, val; ROLLBACK; -- try updating the partition key column @@ -1171,7 +1245,9 @@ MERGE INTO pa_target t WHEN MATCHED THEN UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN - INSERT VALUES (sid, delta, 'inserted by merge'); + INSERT VALUES (sid, delta, 'inserted by merge') + WHEN NOT MATCHED BY SOURCE THEN + UPDATE SET tid = 1, val = val || ' not matched by source'; IF FOUND THEN GET DIAGNOSTICS result := ROW_COUNT; END IF; @@ -1179,7 +1255,7 @@ RETURN result; END; $$; SELECT merge_func(); -SELECT * FROM pa_target ORDER BY tid; +SELECT * FROM pa_target ORDER BY tid, val; ROLLBACK; -- update partition key to partition not initially scanned @@ -1216,7 +1292,7 @@ ALTER TABLE pa_target ATTACH PARTITION part3 FOR VALUES IN (3,8,9); ALTER TABLE pa_target ATTACH PARTITION part4 DEFAULT; -- insert a few rows in the target table (odd numbered tid) -INSERT INTO pa_target SELECT id, id * 100, 'initial' FROM generate_series(1,14,2) AS id; +INSERT INTO pa_target SELECT id, id * 100, 'initial' FROM generate_series(1,15,2) AS id; -- try simple MERGE BEGIN; @@ -1230,12 +1306,14 @@ MERGE INTO pa_target t WHEN MATCHED THEN UPDATE SET balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN - INSERT VALUES (sid, delta, 'inserted by merge'); + INSERT VALUES (sid, delta, 'inserted by merge') + WHEN NOT MATCHED BY SOURCE THEN + UPDATE SET val = val || ' not matched by source'; GET DIAGNOSTICS result := ROW_COUNT; RAISE NOTICE 'ROW_COUNT = %', result; END; $$; -SELECT * FROM pa_target ORDER BY tid; +SELECT * FROM pa_target ORDER BY tid, val; ROLLBACK; -- same with a constant qual @@ -1247,8 +1325,10 @@ MERGE INTO pa_target t WHEN MATCHED THEN UPDATE SET balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN - INSERT VALUES (sid, delta, 'inserted by merge'); -SELECT * FROM pa_target ORDER BY tid; + INSERT VALUES (sid, delta, 'inserted by merge') + WHEN NOT MATCHED BY SOURCE THEN + UPDATE SET val = val || ' not matched by source'; +SELECT * FROM pa_target ORDER BY tid, val; ROLLBACK; -- try updating the partition key column @@ -1263,12 +1343,14 @@ MERGE INTO pa_target t WHEN MATCHED THEN UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN - INSERT VALUES (sid, delta, 'inserted by merge'); + INSERT VALUES (sid, delta, 'inserted by merge') + WHEN NOT MATCHED BY SOURCE THEN + UPDATE SET tid = 1, val = val || ' not matched by source'; GET DIAGNOSTICS result := ROW_COUNT; RAISE NOTICE 'ROW_COUNT = %', result; END; $$; -SELECT * FROM pa_target ORDER BY tid; +SELECT * FROM pa_target ORDER BY tid, val; ROLLBACK; -- as above, but blocked by BEFORE DELETE ROW trigger @@ -1287,12 +1369,14 @@ MERGE INTO pa_target t WHEN MATCHED THEN UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN - INSERT VALUES (sid, delta, 'inserted by merge'); + INSERT VALUES (sid, delta, 'inserted by merge') + WHEN NOT MATCHED BY SOURCE THEN + UPDATE SET val = val || ' not matched by source'; GET DIAGNOSTICS result := ROW_COUNT; RAISE NOTICE 'ROW_COUNT = %', result; END; $$; -SELECT * FROM pa_target ORDER BY tid; +SELECT * FROM pa_target ORDER BY tid, val; ROLLBACK; -- as above, but blocked by BEFORE INSERT ROW trigger @@ -1311,12 +1395,14 @@ MERGE INTO pa_target t WHEN MATCHED THEN UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge' WHEN NOT MATCHED THEN - INSERT VALUES (sid, delta, 'inserted by merge'); + INSERT VALUES (sid, delta, 'inserted by merge') + WHEN NOT MATCHED BY SOURCE THEN + UPDATE SET val = val || ' not matched by source'; GET DIAGNOSTICS result := ROW_COUNT; RAISE NOTICE 'ROW_COUNT = %', result; END; $$; -SELECT * FROM pa_target ORDER BY tid; +SELECT * FROM pa_target ORDER BY tid, val; ROLLBACK; -- test RLS enforcement diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql index 27340bacbc5..4a5fa505855 100644 --- a/src/test/regress/sql/rules.sql +++ b/src/test/regress/sql/rules.sql @@ -1338,7 +1338,26 @@ END; \sf merge_sf_test +CREATE FUNCTION merge_sf_test2() + RETURNS void + LANGUAGE sql +BEGIN ATOMIC + MERGE INTO sf_target t + USING rule_merge1 s + ON (s.a = t.id) +WHEN NOT MATCHED + THEN INSERT (data, id) + VALUES (s.a, s.a) +WHEN MATCHED + THEN UPDATE SET data = s.b +WHEN NOT MATCHED BY SOURCE + THEN DELETE; +END; + +\sf merge_sf_test2 + DROP FUNCTION merge_sf_test; +DROP FUNCTION merge_sf_test2; DROP TABLE sf_target; -- diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql index afdf331fed6..e0ab923d5d1 100644 --- a/src/test/regress/sql/updatable_views.sql +++ b/src/test/regress/sql/updatable_views.sql @@ -179,6 +179,16 @@ MERGE INTO rw_view1 t RETURNING merge_action(), v.*, t.*; SELECT * FROM base_tbl ORDER BY a; +MERGE INTO rw_view1 t + USING (VALUES (0, 'R0'), (1, 'R1'), + (2, 'R2'), (3, 'R3')) 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 BY SOURCE THEN DELETE + 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; EXPLAIN (costs off) DELETE FROM rw_view1 WHERE a=5; @@ -194,6 +204,11 @@ MERGE INTO rw_view1 t EXPLAIN (costs off) MERGE INTO rw_view1 t USING (SELECT * FROM generate_series(1,5)) AS s(a) ON t.a = s.a + WHEN NOT MATCHED BY SOURCE THEN DELETE; + +EXPLAIN (costs off) +MERGE INTO rw_view1 t + USING (SELECT * FROM generate_series(1,5)) AS s(a) ON t.a = s.a WHEN NOT MATCHED THEN INSERT (a) VALUES (s.a); -- it's still updatable if we add a DO ALSO rule @@ -251,6 +266,15 @@ MERGE INTO rw_view2 t RETURNING merge_action(), v.*, t.*; SELECT * FROM rw_view2 ORDER BY aaa; +MERGE INTO rw_view2 t + USING (VALUES (4, 'r4'), (5, 'r5'), (6, 'r6')) AS v(a,b) ON aaa = v.a + WHEN MATCHED AND aaa = 4 THEN DELETE + WHEN MATCHED THEN UPDATE SET bbb = v.b + WHEN NOT MATCHED THEN INSERT (aaa) VALUES (v.a) + WHEN NOT MATCHED BY SOURCE THEN UPDATE SET bbb = 'Not matched by source' + RETURNING merge_action(), v.*, t.*; +SELECT * FROM rw_view2 ORDER BY aaa; + EXPLAIN (costs off) UPDATE rw_view2 SET aaa=5 WHERE aaa=4; EXPLAIN (costs off) DELETE FROM rw_view2 WHERE aaa=4; @@ -464,6 +488,14 @@ MERGE INTO rw_view2 t RETURNING merge_action(), s.*, t.*; SELECT * FROM base_tbl ORDER BY a; +MERGE INTO rw_view2 t + USING (SELECT x, 'r'||x FROM generate_series(0,2) x) AS s(a,b) ON t.a = s.a + 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 BY SOURCE THEN UPDATE SET b = 'Not matched by source' + RETURNING merge_action(), s.*, t.*; +SELECT * FROM base_tbl ORDER BY a; + EXPLAIN (costs off) UPDATE rw_view2 SET a=3 WHERE a=2; EXPLAIN (costs off) DELETE FROM rw_view2 WHERE a=2; diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index cfa9d5aaeac..a8d7bed411f 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -1588,6 +1588,7 @@ MergeAppendState MergeJoin MergeJoinClause MergeJoinState +MergeMatchKind MergePath MergeScanSelCache MergeStmt |