Rebased version attached.
Regards,
Dean
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
new file mode 100644
index f8f83d4..380d0c9
--- 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
new file mode 100644
index e745fbd..6845f14
--- a/doc/src/sgml/ref/merge.sgml
+++ b/doc/src/sgml/ref/merge.sgml
@@ -33,7 +33,8 @@ USING <replaceable class="parameter">dat
<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>
@@ -72,7 +73,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>
@@ -254,18 +257,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>
- 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 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>
+ 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>.
@@ -285,7 +310,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>
@@ -409,8 +437,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>
@@ -484,8 +514,9 @@ MERGE <replaceable class="parameter">tot
<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>
@@ -564,7 +595,8 @@ MERGE <replaceable class="parameter">tot
<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
@@ -655,6 +687,23 @@ WHEN MATCHED THEN
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>
@@ -663,7 +712,9 @@ WHEN MATCHED THEN
This command conforms to the <acronym>SQL</acronym> standard.
</para>
<para>
- The <literal>WITH</literal> clause and <literal>DO NOTHING</literal>
+ The <literal>WITH</literal> clause, <literal>BY SOURCE</literal> and
+ <literal>BY TARGET</literal> qualifiers to
+ <literal>WHEN NOT MATCHED</literal>, and <literal>DO NOTHING</literal>
action are extensions to the <acronym>SQL</acronym> standard.
</para>
</refsect1>
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
new file mode 100644
index 940499c..24cb152
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1245,8 +1245,10 @@ InitResultRelInfo(ResultRelInfo *resultR
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
new file mode 100644
index 8ca512d..0d83b2f
--- a/src/backend/executor/execPartition.c
+++ b/src/backend/executor/execPartition.c
@@ -878,6 +878,7 @@ ExecInitPartitionInfo(ModifyTableState *
List *firstMergeActionList = linitial(node->mergeActionLists);
ListCell *lc;
ExprContext *econtext = mtstate->ps.ps_ExprContext;
+ Node *joinCondition;
if (part_attmap == NULL)
part_attmap =
@@ -888,23 +889,31 @@ ExecInitPartitionInfo(ModifyTableState *
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
new file mode 100644
index 9351fbc..7e19bae
--- 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.
@@ -2716,48 +2718,61 @@ ExecMerge(ModifyTableContext *context, R
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
+ * additional quals attached to the current WHEN MATCHED or WHEN NOT
+ * MATCHED BY SOURCE action, but still satisfies the join quals.
*
- * 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.
+ * 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?
*
- * 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.
+ * 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)
@@ -2777,85 +2792,103 @@ ExecMerge(ModifyTableContext *context, R
}
/*
- * 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 we return 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 to the end, no action is taken and we return true, indicating that no
+ * further action is required for this tuple.
*
* If we do find a qualifying action, then we attempt to execute the action.
*
- * 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, we return false --
- * meaning that a NOT MATCHED action must now be executed for the current
- * source tuple.
+ * If the tuple is concurrently updated, and we were dealing with the MATCHED
+ * case, EvalPlanQual is run with the updated tuple to recheck the join quals.
+ * If the join quals no longer pass, we switch from the MATCHED case to the
+ * WHEN NOT MATCHED BY SOURCE case. Then we restart from the first action to
+ * look for a qualifying action. If we switched from matched to not matched,
+ * then we return false, indicating that a NOT MATCHED [BY TARGET] action must
+ * now be executed for the current source tuple (possibly in addition to any
+ * NOT MATCHED BY SOURCE action executed here).
*/
static bool
ExecMergeMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
ItemPointer tupleid, HeapTuple oldtuple, bool canSetTag)
{
ModifyTableState *mtstate = context->mtstate;
+ List **mergeActions = resultRelInfo->ri_MergeActions;
+ List *actionStates;
TupleTableSlot *newslot;
EState *estate = context->estate;
ExprContext *econtext = mtstate->ps.ps_ExprContext;
bool isNull;
EPQState *epqstate = &mtstate->mt_epqstate;
+ bool epq_ok;
ListCell *l;
/*
- * 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)
+ if (mergeActions[MERGE_WHEN_MATCHED] == NIL &&
+ mergeActions[MERGE_WHEN_NOT_MATCHED_BY_SOURCE] == NIL)
return true;
/*
* 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, the
+ * join subplan will only output MATCHED and NOT MATCHED [BY TARGET] rows,
+ * and we only need to process 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)
+ /*
+ * A concurrent update may change the target tuple so that a MATCHED row
+ * becomes NOT MATCHED BY SOURCE (but not vice versa). If this happens,
+ * we set epq_ok = false, process the NOT MATCHED BY SOURCE actions, and
+ * then return false to indicate that the caller must also process any NOT
+ * MATCHED [BY TARGET] actions.
+ */
+ epq_ok = true;
+
+lmerge_matched:
+
+ foreach(l, actionStates)
{
MergeActionState *relaction = (MergeActionState *) lfirst(l);
CmdType commandType = relaction->mas_action->commandType;
@@ -2910,7 +2943,7 @@ lmerge_matched:
tupleid, NULL, newslot, &result))
{
if (result == TM_Ok)
- return true; /* "do nothing" */
+ return epq_ok; /* "do nothing" */
break; /* concurrent update/delete */
}
@@ -2920,7 +2953,7 @@ lmerge_matched:
{
if (!ExecIRUpdateTriggers(estate, resultRelInfo,
oldtuple, newslot))
- return true; /* "do nothing" */
+ return epq_ok; /* "do nothing" */
}
else
{
@@ -2938,7 +2971,7 @@ lmerge_matched:
if (updateCxt.crossPartUpdate)
{
mtstate->mt_merge_updated += 1;
- return true;
+ return epq_ok;
}
}
@@ -2956,7 +2989,7 @@ lmerge_matched:
NULL, NULL, &result))
{
if (result == TM_Ok)
- return true; /* "do nothing" */
+ return epq_ok; /* "do nothing" */
break; /* concurrent update/delete */
}
@@ -2966,7 +2999,7 @@ lmerge_matched:
{
if (!ExecIRDeleteTriggers(estate, resultRelInfo,
oldtuple))
- return true; /* "do nothing" */
+ return epq_ok; /* "do nothing" */
}
else
result = ExecDeleteAct(context, resultRelInfo, tupleid,
@@ -2986,7 +3019,7 @@ lmerge_matched:
break;
default:
- elog(ERROR, "unknown action in MERGE WHEN MATCHED clause");
+ elog(ERROR, "unknown action in MERGE WHEN clause");
}
switch (result)
@@ -3044,12 +3077,13 @@ lmerge_matched:
/*
* If the tuple was already deleted, return to let caller
- * handle it under NOT MATCHED clauses.
+ * handle it under NOT MATCHED [BY TARGET] clauses.
*/
return false;
case TM_Updated:
{
+ bool was_matched;
Relation resultRelationDesc;
TupleTableSlot *epqslot,
*inputslot;
@@ -3057,19 +3091,22 @@ 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, this
+ * is not necessary.
*/
+ 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,
@@ -3080,28 +3117,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))
- return false;
-
- (void) ExecGetJunkAttribute(epqslot,
- resultRelInfo->ri_RowIdAttNo,
- &isNull);
- if (isNull)
- return false;
/*
- * 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
@@ -3112,26 +3130,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 for us to do.
+ */
+ if (TupIsNull(epqslot))
+ return true;
+
+ /*
+ * 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)
+ epq_ok = 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 (epq_ok)
+ epq_ok = ExecQual(resultRelInfo->ri_MergeJoinCondition,
+ econtext);
+
+ /* Switch lists, if necessary */
+ if (!epq_ok)
+ 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
*/
return false;
@@ -3189,11 +3253,11 @@ lmerge_matched:
/*
* Successfully executed an action or no qualifying action was found.
*/
- return true;
+ return epq_ok;
}
/*
- * Execute the first qualifying NOT MATCHED action.
+ * Execute the first qualifying NOT MATCHED [BY TARGET] action.
*/
static void
ExecMergeNotMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
@@ -3201,7 +3265,7 @@ ExecMergeNotMatched(ModifyTableContext *
{
ModifyTableState *mtstate = context->mtstate;
ExprContext *econtext = mtstate->ps.ps_ExprContext;
- List *actionStates = NIL;
+ List *actionStates;
ListCell *l;
/*
@@ -3213,7 +3277,7 @@ ExecMergeNotMatched(ModifyTableContext *
* 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
@@ -3305,9 +3369,11 @@ ExecInitMerge(ModifyTableState *mtstate,
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);
@@ -3316,13 +3382,16 @@ ExecInitMerge(ModifyTableState *mtstate,
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,
@@ -3334,15 +3403,12 @@ ExecInitMerge(ModifyTableState *mtstate,
&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)
{
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
new file mode 100644
index 6ba8e73..1900156
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -2583,6 +2583,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))
@@ -3607,6 +3609,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
new file mode 100644
index 610f4a5..1d36665
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -311,7 +311,8 @@ static ModifyTable *make_modifytable(Pla
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);
@@ -2835,6 +2836,7 @@ create_modifytable_plan(PlannerInfo *roo
best_path->rowMarks,
best_path->onconflict,
best_path->mergeActionLists,
+ best_path->mergeJoinConditions,
best_path->epqParam);
copy_generic_path_info(&plan->plan, &best_path->path);
@@ -7014,7 +7016,8 @@ make_modifytable(PlannerInfo *root, Plan
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;
@@ -7084,6 +7087,7 @@ make_modifytable(PlannerInfo *root, Plan
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
new file mode 100644
index 443ab08..99c0603
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -905,6 +905,9 @@ subquery_planner(PlannerGlobal *glob, Qu
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);
@@ -1787,6 +1790,7 @@ grouping_planner(PlannerInfo *root, doub
List *withCheckOptionLists = NIL;
List *returningLists = NIL;
List *mergeActionLists = NIL;
+ List *mergeJoinConditions = NIL;
List *rowMarks;
if (bms_membership(root->all_result_relids) == BMS_MULTIPLE)
@@ -1893,6 +1897,19 @@ grouping_planner(PlannerInfo *root, doub
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)
@@ -1917,6 +1934,8 @@ grouping_planner(PlannerInfo *root, doub
returningLists = list_make1(parse->returningList);
if (parse->mergeActionList)
mergeActionLists = list_make1(parse->mergeActionList);
+ if (parse->commandType == CMD_MERGE)
+ mergeJoinConditions = list_make1(parse->mergeJoinCondition);
}
}
else
@@ -1932,6 +1951,8 @@ grouping_planner(PlannerInfo *root, doub
returningLists = list_make1(parse->returningList);
if (parse->mergeActionList)
mergeActionLists = list_make1(parse->mergeActionList);
+ if (parse->commandType == CMD_MERGE)
+ mergeJoinConditions = list_make1(parse->mergeJoinCondition);
}
/*
@@ -1959,6 +1980,7 @@ grouping_planner(PlannerInfo *root, doub
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
new file mode 100644
index 42603db..37abcb4
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -1143,7 +1143,9 @@ set_plan_refs(PlannerInfo *root, Plan *p
*/
if (splan->mergeActionLists != NIL)
{
+ List *newMJC = NIL;
ListCell *lca,
+ *lcj,
*lcr;
/*
@@ -1164,10 +1166,12 @@ set_plan_refs(PlannerInfo *root, Plan *p
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 *p
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
new file mode 100644
index 300691c..2d8956d
--- 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;
@@ -204,16 +222,16 @@ transform_MERGE_to_join(Query *parse)
* trigger-updatable view, it will be the expanded view subquery that we
* need to pull data from.
*/
+ 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 +251,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 +2200,8 @@ perform_pullup_replace_vars(PlannerInfo
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
new file mode 100644
index 7698bfa..931b9c0
--- 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
new file mode 100644
index 246cd8f..e73b883
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -3702,6 +3702,7 @@ create_lockrows_path(PlannerInfo *root,
* '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,
@@ -3713,7 +3714,8 @@ create_modifytable_path(PlannerInfo *roo
List *updateColnosLists,
List *withCheckOptionLists, List *returningLists,
List *rowMarks, OnConflictExpr *onconflict,
- List *mergeActionLists, int epqParam)
+ List *mergeActionLists, List *mergeJoinConditions,
+ int epqParam)
{
ModifyTablePath *pathnode = makeNode(ModifyTablePath);
@@ -3781,6 +3783,7 @@ create_modifytable_path(PlannerInfo *roo
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
new file mode 100644
index c6e2f67..1cd1f21
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -275,6 +275,7 @@ static Node *makeRecursiveViewSelect(cha
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(cha
%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
@@ -760,11 +762,11 @@ static Node *makeRecursiveViewSelect(cha
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 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
@@ -12392,50 +12394,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; }
@@ -17394,6 +17412,7 @@ unreserved_keyword:
| SIMPLE
| SKIP
| SNAPSHOT
+ | SOURCE
| SQL_P
| STABLE
| STANDALONE_P
@@ -17412,6 +17431,7 @@ unreserved_keyword:
| SYSTEM_P
| TABLES
| TABLESPACE
+ | TARGET
| TEMP
| TEMPLATE
| TEMPORARY
@@ -18007,6 +18027,7 @@ bare_label_keyword:
| SMALLINT
| SNAPSHOT
| SOME
+ | SOURCE
| SQL_P
| STABLE
| STANDALONE_P
@@ -18030,6 +18051,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
new file mode 100644
index a7d8ba7..272cef5
--- a/src/backend/parser/parse_merge.c
+++ b/src/backend/parser/parse_merge.c
@@ -40,9 +40,9 @@ static void setNamespaceVisibilityForRTE
* 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 *pst
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 *pst
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, M
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, M
/*
* 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, M
/*
* 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, M
* 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);
/*
* We now have a good query shape, so now look at the WHEN conditions and
@@ -256,11 +268,7 @@ transformMergeStmt(ParseState *pstate, M
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
new file mode 100644
index 2a1ee69..3c64273
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -7090,6 +7090,7 @@ get_merge_query_def(Query *query, depars
StringInfo buf = context->buf;
RangeTblEntry *rte;
ListCell *lc;
+ bool haveNotMatchedBySource;
/* Insert the WITH clause if given */
get_with_clause(query, context);
@@ -7115,7 +7116,26 @@ get_merge_query_def(Query *query, depars
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)
@@ -7124,7 +7144,24 @@ get_merge_query_def(Query *query, depars
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
new file mode 100644
index 73133ce..ed2cb19
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -4333,17 +4333,35 @@ psql_completion(const char *text, int st
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/nodes/execnodes.h b/src/include/nodes/execnodes.h
new file mode 100644
index 444a5f0..9f65cfb
--- 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, join condition-checking expr state */
+ ExprState *ri_MergeJoinCondition;
/* partition check expression state (NULL if not set up yet) */
ExprState *ri_PartitionCheckExpr;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
new file mode 100644
index 2380821..7c553a6
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -175,8 +175,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
@@ -186,6 +184,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 */
@@ -1676,7 +1677,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
new file mode 100644
index 534692b..5ccfa5f
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -2367,6 +2367,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
new file mode 100644
index b4ef6bc..ebefd65
--- 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
new file mode 100644
index 4a15460..f7eebd8
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1732,10 +1732,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
new file mode 100644
index c43d97b..1b3dd52
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -283,7 +283,8 @@ extern ModifyTablePath *create_modifytab
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
new file mode 100644
index 2331aca..b7d5515
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -403,6 +403,7 @@ PG_KEYWORD("skip", SKIP, UNRESERVED_KEYW
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)
@@ -426,6 +427,7 @@ PG_KEYWORD("table", TABLE, RESERVED_KEYW
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
new file mode 100644
index f5f7e3b..fe3689f
--- a/src/test/isolation/expected/merge-update.out
+++ b/src/test/isolation/expected/merge-update.out
@@ -37,13 +37,15 @@ 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';
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 +88,17 @@ 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';
<waiting ...>
step c1: COMMIT;
step merge2a: <... completed>
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,7 +120,9 @@ 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';
<waiting ...>
step a1: ABORT;
step merge2a: <... completed>
@@ -205,15 +211,17 @@ 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';
<waiting ...>
step c1: COMMIT;
step pa_merge2a: <... completed>
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 +243,9 @@ 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';
<waiting ...>
step c1: COMMIT;
step pa_merge2a: <... completed>
@@ -262,14 +272,16 @@ 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';
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
new file mode 100644
index 3ccd466..657b4ac
--- a/src/test/isolation/specs/merge-update.spec
+++ b/src/test/isolation/specs/merge-update.spec
@@ -92,7 +92,9 @@ 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';
}
step "merge2b"
{
@@ -122,7 +124,9 @@ 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';
}
# 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
new file mode 100644
index e3ebf46..17c0cba
--- 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,22 @@ 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);
+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 +771,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 +969,45 @@ 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;
+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
+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
@@ -1450,6 +1536,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
@@ -1529,7 +1659,7 @@ CREATE TABLE pa_source (sid integer, del
-- 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
@@ -1538,10 +1668,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
@@ -1556,7 +1688,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
@@ -1567,31 +1700,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
@@ -1606,7 +1742,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;
@@ -1616,27 +1754,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;
DROP TABLE pa_target CASCADE;
@@ -1658,7 +1797,7 @@ ALTER TABLE pa_target ATTACH PARTITION p
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 $$
@@ -1671,15 +1810,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
@@ -1694,7 +1835,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
@@ -1706,29 +1848,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
@@ -1743,30 +1888,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
@@ -1785,15 +1933,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
@@ -1808,7 +1958,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
@@ -1827,19 +1978,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
new file mode 100644
index 0cd2c64..4e45941
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -3718,7 +3718,39 @@ BEGIN ATOMIC
THEN INSERT (filling[1], id)
VALUES (s.a, s.a);
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
new file mode 100644
index 794cf9c..3a43cce
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -491,6 +491,23 @@ 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);
+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
--------------------------------------------------
@@ -537,6 +554,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
-------------------------------------------------------------------
@@ -640,6 +674,20 @@ 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';
+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
--------------------------------------------------------
@@ -1082,6 +1130,22 @@ 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';
+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
new file mode 100644
index 79a1868..a4607a8
--- 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,18 @@ 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);
+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 +522,17 @@ WHEN NOT MATCHED AND s.balance = 100 THE
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 +660,25 @@ 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;
+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
@@ -930,6 +984,20 @@ WHEN MATCHED AND t.a >= 30 AND t.a <= 40
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
@@ -985,7 +1053,7 @@ CREATE TABLE pa_source (sid integer, del
-- 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;
@@ -995,8 +1063,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
@@ -1007,8 +1077,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
@@ -1023,7 +1095,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;
@@ -1031,7 +1105,7 @@ RETURN result;
END;
$$;
SELECT merge_func();
-SELECT * FROM pa_target ORDER BY tid;
+SELECT * FROM pa_target ORDER BY tid, val;
ROLLBACK;
DROP TABLE pa_target CASCADE;
@@ -1057,7 +1131,7 @@ ALTER TABLE pa_target ATTACH PARTITION p
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;
@@ -1071,12 +1145,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
@@ -1088,8 +1164,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
@@ -1104,12 +1182,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
@@ -1128,12 +1208,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
@@ -1152,12 +1234,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
new file mode 100644
index 6924012..198b4cd
--- a/src/test/regress/sql/rules.sql
+++ b/src/test/regress/sql/rules.sql
@@ -1336,7 +1336,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
new file mode 100644
index ae11e46..251eabf
--- a/src/test/regress/sql/updatable_views.sql
+++ b/src/test/regress/sql/updatable_views.sql
@@ -178,6 +178,15 @@ MERGE INTO rw_view1 t
WHEN NOT MATCHED AND a > 0 THEN INSERT (a) VALUES (v.a);
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);
+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;
@@ -193,6 +202,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
@@ -249,6 +263,14 @@ MERGE INTO rw_view2 t
WHEN NOT MATCHED THEN INSERT (aaa) VALUES (v.a);
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';
+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;
@@ -461,6 +483,13 @@ MERGE INTO rw_view2 t
WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b);
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';
+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
new file mode 100644
index aa7a25b..08b72c5
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1574,6 +1574,7 @@ MergeAppendState
MergeJoin
MergeJoinClause
MergeJoinState
+MergeMatchKind
MergePath
MergeScanSelCache
MergeStmt
