Subject: Re: MERGE ... WHEN NOT MATCHED BY SOURCE

From: Dean Rasheed <[email-masked]>
Date: Wed, 13 Mar 2024 14:32:11 +0000

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

---

From: Dean Rasheed <[email-masked]>
Date: Mon, 18 Mar 2024 08:59:28 +0000

> Rebased version attached.

Rebased version attached, on top of c649fa24a4 (MERGE ... RETURNING support).

Aside from some cosmetic stuff, I've updated several tests to test
this together with RETURNING.

The updated isolation test tests the new interesting case where a
concurrent update causes a matched case to become not matched, and
there are both NOT MATCHED BY SOURCE and NOT MATCHED BY TARGET actions
to execute, and RETURNING is specified so that it is forced to defer
the NOT MATCHED BY TARGET action until the next invocation of
ExecModifyTable(), in order to return the rows from both not matched
actions.

I also tried to tidy up ExecMergeMatched() a little --- since we know
that it's only ever called with matched = true, it's simpler to just
Assert that at the top, and then only touch it in the few cases where
it needs to be changed to false.

A lot of the updates are comment updates, to try to make it clearer
how concurrent updates are handled, since that's a little more complex
with this patch.

Regards,
Dean
index 44e5ec0..16f0aa9
@@ -34,7 +34,8 @@ USING <replaceable class="parameter">dat

 
@@ -73,7 +74,9 @@ DELETE
@@ -268,18 +271,40 @@ DELETE
@@ -299,7 +324,10 @@ DELETE
@@ -423,8 +451,10 @@ DELETE
@@ -535,8 +565,9 @@ MERGE <replaceable class="parameter">tot
@@ -615,7 +646,8 @@ MERGE <replaceable class="parameter">tot
@@ -701,6 +733,23 @@ RETURNING merge_action(), w.*;
 
+  <para>
 
  <refsect1>
@@ -709,7 +758,9 @@ RETURNING merge_action(), w.*;
     This command conforms to the <acronym>SQL</acronym> standard.
   </para>
    <para>
-    The <literal>WITH</literal> clause, <literal>DO NOTHING</literal> action,
+    The <literal>WITH</literal> clause, <literal>BY SOURCE</literal> and
+    <literal>BY TARGET</literal> qualifiers to
+    <literal>WHEN NOT MATCHED</literal>, <literal>DO NOTHING</literal> action,
     and <literal>RETURNING</literal> clause are extensions to the
     <acronym>SQL</acronym> standard.
   </para>
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
new file mode 100644
index 7eb1f7d..4d7c92d
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1251,8 +1251,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;
 
 	/*
index 64fcb01..f511092
@@ -880,6 +880,7 @@ ExecInitPartitionInfo(ModifyTableState *
 
 		if (part_attmap == NULL)
 			part_attmap =
@@ -890,23 +891,31 @@ ExecInitPartitionInfo(ModifyTableState *
 		if (unlikely(!leaf_part_rri->ri_projectNewInfoValid))
 			ExecInitMergeTupleSlots(mtstate, leaf_part_rri);

 
 
index 4abfe82..ff76830
@@ -2714,48 +2716,61 @@ ExecMerge(ModifyTableContext *context, R
 	bool		matched;
 
 	/*-----
@@ -2768,33 +2783,52 @@ ExecMerge(ModifyTableContext *context, R
 	 * "matched" to false, indicating that it no longer matches).
 	 */
 	if (!matched)
-		rslot = ExecMergeNotMatched(context, resultRelInfo, canSetTag);
+	{
+		/*
+		 * If a concurrent update turned a MATCHED case into a NOT MATCHED
+		 * case, and we have both WHEN NOT MATCHED BY SOURCE and WHEN NOT
+		 * MATCHED [BY TARGET] actions, and there is a RETURNING clause,
+		 * ExecMergeMatched() may have already executed a WHEN NOT MATCHED BY
+		 * SOURCE action, and computed the row to return.  If so, we cannot
+		 * execute the WHEN NOT MATCHED [BY TARGET] action now, so mark it as
+		 * pending (to be processed on the next call to ExecModifyTable()).
+		 * Otherwise, just process the action now.
+		 */
+		if (rslot == NULL)
+			rslot = ExecMergeNotMatched(context, resultRelInfo, canSetTag);
+		else
+			context->mtstate->mt_merge_pending_not_matched = context->planSlot;
+	}
 
 	return rslot;
 }
 
 /*
- * Check and execute the first qualifying MATCHED action.  If the target
+ * Check and execute the first qualifying MATCHED or NOT MATCHED BY SOURCE
+ * action, depending on whether the join quals are satisfied.  If the target
  * relation is a table, the current target tuple is identified by tupleid.
  * Otherwise, if the target relation is a view, oldtuple is the current target
  * tuple from the view.
  *
- * We start from the first WHEN MATCHED action and check if the WHEN quals
- * pass, if any. If the WHEN quals for the first action do not pass, we
- * check the second, then the third and so on. If we reach to the end, no
- * action is taken and "matched" is set to true, indicating that no further
- * action is required for this tuple.
+ * We start from the first WHEN MATCHED or WHEN NOT MATCHED BY SOURCE action
+ * and check if the WHEN quals pass, if any. If the WHEN quals for the first
+ * action do not pass, we check the second, then the third and so on. If we
+ * reach the end without finding a qualifying action, we return NULL.
+ * Otherwise, we execute the qualifying action and return its RETURNING
+ * result, if any, or NULL.
  *
- * If we do find a qualifying action, then we attempt to execute the action.
+ * On entry, "*matched" is assumed to be true.  If a concurrent update or
+ * delete is detected that causes the join quals to no longer pass, we set it
+ * to false, indicating that the caller should process any NOT MATCHED [BY
+ * TARGET] actions.
  *
- * If the tuple is concurrently updated, EvalPlanQual is run with the updated
- * tuple to recheck the join quals. Note that the additional quals associated
- * with individual actions are evaluated by this routine via ExecQual, while
- * EvalPlanQual checks for the join quals. If EvalPlanQual tells us that the
- * updated tuple still passes the join quals, then we restart from the first
- * action to look for a qualifying action. Otherwise, "matched" is set to
- * false -- meaning that a NOT MATCHED action must now be executed for the
- * current source tuple.
+ * After a concurrent update, we restart from the first action to look for a
+ * new qualifying action to execute. If the join quals originally passed, and
+ * the concurrent update caused them to no longer pass, then we switch from
+ * the MATCHED to the NOT MATCHED BY SOURCE list of actions before restarting
+ * (and setting "*matched" to false).  As a result we may execute a WHEN NOT
+ * MATCHED BY SOURCE action, and set "*matched" to false, causing the caller
+ * to also execute a WHEN NOT MATCHED [BY TARGET] action.
  */
 static TupleTableSlot *
 ExecMergeMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
@@ -2802,6 +2836,8 @@ ExecMergeMatched(ModifyTableContext *con
 				 bool *matched)
 {
 	ModifyTableState *mtstate = context->mtstate;
+	List	  **mergeActions = resultRelInfo->ri_MergeActions;
+	List	   *actionStates;
 	TupleTableSlot *newslot = NULL;
 	TupleTableSlot *rslot = NULL;
 	EState	   *estate = context->estate;
@@ -2810,54 +2846,58 @@ ExecMergeMatched(ModifyTableContext *con
 	EPQState   *epqstate = &mtstate->mt_epqstate;
 	ListCell   *l;
 
+	/* Expect matched to be true on entry */
+	Assert(*matched == true);
+
 	/*
-	 * If there are no WHEN MATCHED actions, we are done.
+	 * If there are no WHEN MATCHED or WHEN NOT MATCHED BY SOURCE actions, we
+	 * are done.
 	 */
-	if (resultRelInfo->ri_matchedMergeAction == NIL)
-	{
-		*matched = true;
+	if (mergeActions[MERGE_WHEN_MATCHED] == NIL &&
+		mergeActions[MERGE_WHEN_NOT_MATCHED_BY_SOURCE] == NIL)
 		return NULL;
-	}
 
 	/*
 
 	/*
 
 	/*
+	 * BY SOURCE actions --- see transform_MERGE_to_join().  In that case, we
+	 * need only consider MATCHED actions here.
 
-	foreach(l, resultRelInfo->ri_matchedMergeAction)
+lmerge_matched:
+
+	foreach(l, actionStates)
 	{
 		MergeActionState *relaction = (MergeActionState *) lfirst(l);
 		CmdType		commandType = relaction->mas_action->commandType;
@@ -2912,10 +2952,8 @@ lmerge_matched:
 										tupleid, NULL, newslot, &result))
 				{
 					if (result == TM_Ok)
-					{
-						*matched = true;
 						return NULL;	/* "do nothing" */
-					}
+
 					break;		/* concurrent update/delete */
 				}
 
@@ -2925,10 +2963,7 @@ lmerge_matched:
 				{
 					if (!ExecIRUpdateTriggers(estate, resultRelInfo,
 											  oldtuple, newslot))
-					{
-						*matched = true;
 						return NULL;	/* "do nothing" */
-					}
 				}
 				else
 				{
@@ -2948,7 +2983,6 @@ lmerge_matched:
 					if (updateCxt.crossPartUpdate)
 					{
 						mtstate->mt_merge_updated += 1;
-						*matched = true;
 						return context->cpUpdateReturningSlot;
 					}
 				}
@@ -2967,10 +3001,8 @@ lmerge_matched:
 										NULL, NULL, &result))
 				{
 					if (result == TM_Ok)
-					{
-						*matched = true;
 						return NULL;	/* "do nothing" */
-					}
+
 					break;		/* concurrent update/delete */
 				}
 
@@ -2980,10 +3012,7 @@ lmerge_matched:
 				{
 					if (!ExecIRDeleteTriggers(estate, resultRelInfo,
 											  oldtuple))
-					{
-						*matched = true;
 						return NULL;	/* "do nothing" */
-					}
 				}
 				else
 					result = ExecDeleteAct(context, resultRelInfo, tupleid,
@@ -3003,7 +3032,7 @@ lmerge_matched:
 				break;
 
 			default:
 
 		switch (result)
@@ -3060,14 +3089,15 @@ lmerge_matched:
 							 errmsg("could not serialize access due to concurrent delete")));
 
 				/*
-				 * If the tuple was already deleted, return to let caller
-				 * handle it under NOT MATCHED clauses.
+				 * If the tuple was already deleted, set matched to false to
+				 * let caller handle it under NOT MATCHED [BY TARGET] clauses.
 				 */
 				*matched = false;
 				return NULL;
 
 			case TM_Updated:
 				{
+					bool		was_matched;
 					Relation	resultRelationDesc;
 					TupleTableSlot *epqslot,
 							   *inputslot;
@@ -3075,19 +3105,23 @@ lmerge_matched:
 
 					/*
+					 * already processing a NOT MATCHED BY SOURCE action, we
+					 * skip this (cannot switch from NOT MATCHED BY SOURCE to
+					 * MATCHED).

 					result = table_tuple_lock(resultRelationDesc, tupleid,
 											  estate->es_snapshot,
@@ -3098,34 +3132,9 @@ lmerge_matched:
 					switch (result)
 					{
 						case TM_Ok:
-							epqslot = EvalPlanQual(epqstate,
-												   resultRelationDesc,
-												   resultRelInfo->ri_RangeTableIndex,
-												   inputslot);
 
 							/*
-								*matched = false;
-								return NULL;
-								*matched = false;
-								return NULL;
@@ -3136,26 +3145,72 @@ lmerge_matched:
 
 							/*
+								 * more to do.
+									return NULL;
+									*matched = false;
+								if (*matched)
+									*matched = ExecQual(resultRelInfo->ri_MergeJoinCondition,
+								if (!*matched)
 
 						case TM_Deleted:
 
 							/*
 							 * tuple already deleted; tell caller to run NOT
-							 * MATCHED actions
+							 * MATCHED [BY TARGET] actions
 							 */
 							*matched = false;
 							return NULL;
@@ -3239,13 +3294,11 @@ lmerge_matched:
 	/*
 	 * Successfully executed an action or no qualifying action was found.
 	 */
-	*matched = true;
-
 	return rslot;
 }
 
 /*
- * Execute the first qualifying NOT MATCHED action.
+ * Execute the first qualifying NOT MATCHED [BY TARGET] action.
  */
 static TupleTableSlot *
 ExecMergeNotMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo,
@@ -3253,7 +3306,7 @@ ExecMergeNotMatched(ModifyTableContext *
 {
 	ModifyTableState *mtstate = context->mtstate;
 	ExprContext *econtext = mtstate->ps.ps_ExprContext;
-	List	   *actionStates = NIL;
+	List	   *actionStates;
 	TupleTableSlot *rslot = NULL;
 	ListCell   *l;
 
@@ -3266,7 +3319,7 @@ ExecMergeNotMatched(ModifyTableContext *
 
 	/*
 	 * Make source tuple available to ExecQual and ExecProject. We don't need
@@ -3360,9 +3413,11 @@ ExecInitMerge(ModifyTableState *mtstate,
 	foreach(lc, node->mergeActionLists)
 	{
 		List	   *mergeActionList = lfirst(lc);
+		Node	   *joinCondition;
 		TupleDesc	relationDesc;
 		ListCell   *l;
 
@@ -3371,13 +3426,16 @@ ExecInitMerge(ModifyTableState *mtstate,

 			/*
 			 * Build action merge state for this rel.  (For partitions,
@@ -3389,15 +3447,12 @@ ExecInitMerge(ModifyTableState *mtstate,
 													  &mtstate->ps);
 
 			/*
 
 			switch (action->commandType)
 			{
@@ -3754,6 +3809,31 @@ ExecModifyTable(PlanState *pstate)
 		if (pstate->ps_ExprContext)
 			ResetExprContext(pstate->ps_ExprContext);
 
+		/*
+		 * If there is a pending MERGE ... WHEN NOT MATCHED [BY TARGET] action
+		 * to execute, do so now --- see the comments in ExecMerge().
+		 */
+		if (node->mt_merge_pending_not_matched != NULL)
+		{
+			context.planSlot = node->mt_merge_pending_not_matched;
+
+			slot = ExecMergeNotMatched(&context, node->resultRelInfo,
+									   node->canSetTag);
+
+			/* Clear the pending action */
+			node->mt_merge_pending_not_matched = NULL;
+
+			/*
+			 * If we got a RETURNING result, return it to the caller.  We'll
+			 * continue the work on next call.
+			 */
+			if (slot)
+				return slot;
+
+			continue;			/* continue with the next tuple */
+		}
+
+		/* Fetch the next row from subplan */
 		context.planSlot = ExecProcNode(subplanstate);
 
 		/* No more tuples to process? */
@@ -4139,6 +4219,7 @@ ExecInitModifyTable(ModifyTable *node, E
 	mtstate->resultRelInfo = (ResultRelInfo *)
 		palloc(nrels * sizeof(ResultRelInfo));
 
+	mtstate->mt_merge_pending_not_matched = NULL;
 	mtstate->mt_merge_inserted = 0;
 	mtstate->mt_merge_updated = 0;
 	mtstate->mt_merge_deleted = 0;
index 5b70280..4e0cd63
@@ -2596,6 +2596,8 @@ query_tree_walker_impl(Query *query,
@@ -3621,6 +3623,7 @@ query_tree_mutator_impl(Query *query,

 
 	/*
index 5564826..6318b18

 
 
 			/*

 					/*

 

 
 	/*

 /*
 
 		/*

 	/*

 	return pathnode;
 }
index 39a801a..dd268bc

 
@@ -12394,50 +12396,66 @@ merge_when_list:
 
+/*
 
-					$$ = (Node *) $5;
+					$$ = (Node *) $4;
 				}
 
-					$$ = (Node *) $5;
+					$$ = (Node *) $4;
 				}
 
-					$$ = (Node *) $6;
+					$$ = (Node *) $4;
 				}
 
-					m->matched = true;
+					m->matchKind = $1;
 
 					$$ = (Node *) m;
 				}
 
-					m->matched = false;
+					m->matchKind = $1;
 
 					$$ = (Node *) m;
 				}
 		;
 
+merge_when_tgt_matched:
@@ -17404,6 +17422,7 @@ unreserved_keyword:
@@ -17422,6 +17441,7 @@ unreserved_keyword:
@@ -18019,6 +18039,7 @@ bare_label_keyword:
@@ -18042,6 +18063,7 @@ bare_label_keyword:
index 04ed5e6..bce11d5

 
 		/*
 
 	/*
 
 	/*
 
 	/* Transform the RETURNING list, if any */
 	qry->returningList = transformReturningList(pstate, stmt->returningList,
@@ -260,11 +272,7 @@ transformMergeStmt(ParseState *pstate, M

 		/*
index f2893d4..4d36375
@@ -7093,6 +7093,7 @@ get_merge_query_def(Query *query, depars
 
@@ -7118,7 +7119,26 @@ get_merge_query_def(Query *query, depars
 
 	/* Print each merge action */
 	foreach(lc, query->mergeActionList)
@@ -7127,7 +7147,24 @@ get_merge_query_def(Query *query, depars

 		if (action->qual)
 		{
index 56d723d..c6977a6
@@ -4335,17 +4335,35 @@ psql_completion(const char *text, int st

 
 
index 9259352..c2d047d

 	/* partition check expression state (NULL if not set up yet) */
 	ExprState  *ri_PartitionCheckExpr;
@@ -1328,6 +1330,13 @@ typedef struct ModifyTableState
 	/* For MERGE, the action currently being executed */
 	MergeActionState *mt_merge_action;
 
+	/*
+	 * For MERGE, if there is a pending NOT MATCHED [BY TARGET] action to be
+	 * performed, this will be the last tuple read from the subplan; otherwise
+	 * it will be NULL --- see the comments in ExecMerge().
+	 */
+	TupleTableSlot *mt_merge_pending_not_matched;
+
 	/* tuple counters for MERGE */
 	double		mt_merge_inserted;
 	double		mt_merge_updated;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
new file mode 100644
index 7b57fdd..6b47eb7
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -176,8 +176,6 @@ typedef struct Query
 								 * also USING clause for MERGE */

 	/*
 	 * rtable index of target relation for MERGE to pull data. Initially, this
@@ -187,6 +185,9 @@ typedef struct Query
 	 */
 	int			mergeTargetRelation pg_node_attr(query_jumble_ignore);

 	/* OVERRIDING clause */
@@ -1677,7 +1678,7 @@ typedef struct CommonTableExpr
 
 /*
 
index 8df8884..3b7b8de
@@ -1753,10 +1753,18 @@ typedef struct BooleanTest
index 0993534..45229ed
@@ -404,6 +404,7 @@ PG_KEYWORD("skip", SKIP, UNRESERVED_KEYW
@@ -427,6 +428,7 @@ PG_KEYWORD("table", TABLE, RESERVED_KEYW
index f5f7e3b..3063c0c
@@ -37,13 +37,22 @@ step merge2a:
+	UPDATE set key = t.key + 1, val = t.val || ' source not matched by merge2a'
+  RETURNING merge_action(), t.*;
+
+merge_action|key|val                                                   
+------------+---+------------------------------------------------------
+UPDATE      |  3|setup1 updated by merge1 source not matched by merge2a
+INSERT      |  1|merge2a                                               
+(2 rows)

 step c2: COMMIT;
@@ -86,15 +95,24 @@ step merge2a:
   WHEN NOT MATCHED THEN
 	INSERT VALUES (s.key, s.val)
   WHEN MATCHED THEN
-	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
+  WHEN NOT MATCHED BY SOURCE THEN
+	UPDATE set key = t.key + 1, val = t.val || ' source not matched by merge2a'
+  RETURNING merge_action(), t.*;
  <waiting ...>
 step c1: COMMIT;
 step merge2a: <... completed>
+merge_action|key|val                                                   
+------------+---+------------------------------------------------------
+UPDATE      |  3|setup1 updated by merge1 source not matched by merge2a
+INSERT      |  1|merge2a                                               
+(2 rows)
+
 step select2: SELECT * FROM target;
-key|val                     
----+------------------------
-  2|setup1 updated by merge1
-  1|merge2a                 
+key|val                                                   
+---+------------------------------------------------------
+  3|setup1 updated by merge1 source not matched by merge2a
+  1|merge2a                                               
 (2 rows)
 
 step c2: COMMIT;
@@ -116,10 +134,18 @@ step merge2a:
   WHEN NOT MATCHED THEN
 	INSERT VALUES (s.key, s.val)
   WHEN MATCHED THEN
-	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
+  WHEN NOT MATCHED BY SOURCE THEN
+	UPDATE set key = t.key + 1, val = t.val || ' source not matched by merge2a'
+  RETURNING merge_action(), t.*;
  <waiting ...>
 step a1: ABORT;
 step merge2a: <... completed>
+merge_action|key|val                      
+------------+---+-------------------------
+UPDATE      |  2|setup1 updated by merge2a
+(1 row)
+
 step select2: SELECT * FROM target;
 key|val                      
 ---+-------------------------
@@ -205,15 +231,24 @@ step pa_merge2a:
   WHEN NOT MATCHED THEN
 	INSERT VALUES (s.key, s.val)
   WHEN MATCHED THEN
-	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
+  WHEN NOT MATCHED BY SOURCE THEN
+	UPDATE set key = t.key + 1, val = t.val || ' source not matched by pa_merge2a'
+  RETURNING merge_action(), t.*;
  <waiting ...>
 step c1: COMMIT;
 step pa_merge2a: <... completed>
+merge_action|key|val                                               
+------------+---+--------------------------------------------------
+UPDATE      |  2|initial updated by pa_merge1 updated by pa_merge2a
+UPDATE      |  3|initial source not matched by pa_merge2a          
+(2 rows)
+
 step pa_select2: SELECT * FROM pa_target;
 key|val                                               
 ---+--------------------------------------------------
-  2|initial                                           
   2|initial updated by pa_merge1 updated by pa_merge2a
+  3|initial source not matched by pa_merge2a          
 (2 rows)
 
 step c2: COMMIT;
@@ -235,7 +270,10 @@ step pa_merge2a:
   WHEN NOT MATCHED THEN
 	INSERT VALUES (s.key, s.val)
   WHEN MATCHED THEN
-	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
+  WHEN NOT MATCHED BY SOURCE THEN
+	UPDATE set key = t.key + 1, val = t.val || ' source not matched by pa_merge2a'
+  RETURNING merge_action(), t.*;
  <waiting ...>
 step c1: COMMIT;
 step pa_merge2a: <... completed>
@@ -262,14 +300,24 @@ step pa_merge2a:
   WHEN NOT MATCHED THEN
 	INSERT VALUES (s.key, s.val)
   WHEN MATCHED THEN
-	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
+  WHEN NOT MATCHED BY SOURCE THEN
+	UPDATE set key = t.key + 1, val = t.val || ' source not matched by pa_merge2a'
+  RETURNING merge_action(), t.*;
+
+merge_action|key|val                                                          
+------------+---+-------------------------------------------------------------
+UPDATE      |  3|initial source not matched by pa_merge2a                     
+UPDATE      |  3|initial updated by pa_merge2 source not matched by pa_merge2a
+INSERT      |  1|pa_merge2a                                                   
+(3 rows)

 step c2: COMMIT;
index 3ccd466..a33dcdb
@@ -92,7 +92,10 @@ step "merge2a"
+	UPDATE set key = t.key + 1, val = t.val || ' source not matched by merge2a'
+  RETURNING merge_action(), t.*;
@@ -122,7 +125,10 @@ step "pa_merge2a"
+	UPDATE set key = t.key + 1, val = t.val || ' source not matched by pa_merge2a'
+  RETURNING merge_action(), t.*;
index 07561f0..59035a7
@@ -270,6 +288,31 @@ SELECT * FROM target ORDER BY tid;
 (4 rows)
 
 ROLLBACK;
+-- DELETE/INSERT not matched by source/target
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED BY SOURCE THEN
+	DELETE
+WHEN NOT MATCHED BY TARGET THEN
+	INSERT VALUES (s.sid, s.delta)
+RETURNING merge_action(), t.*;
+ merge_action | tid | balance 
+--------------+-----+---------
+ DELETE       |   1 |      10
+ DELETE       |   2 |      20
+ DELETE       |   3 |      30
+ INSERT       |   4 |      40
+(4 rows)
+
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   4 |      40
+(1 row)
+
+ROLLBACK;
 -- index plans
 INSERT INTO target SELECT generate_series(1000,2500), 0;
 ALTER TABLE target ADD PRIMARY KEY (tid);
@@ -737,6 +780,19 @@ SELECT * FROM wq_target;
    1 |      -1
 (1 row)
 
@@ -922,6 +978,54 @@ SELECT * FROM target ORDER BY tid;
 (3 rows)
 
 ROLLBACK;
+RETURNING merge_action(), t.*;
+ merge_action | tid | balance 
+--------------+-----+---------
+ UPDATE       |   3 |      10
+ INSERT       |   4 |      40
+ DELETE       |   2 |      20
+ UPDATE       |   1 |       0
@@ -1636,6 +1740,50 @@ WHEN NOT MATCHED AND s.a < 20 THEN
 
@@ -1765,7 +1913,7 @@ CREATE TABLE pa_source (sid integer, del
@@ -1774,10 +1922,12 @@ MERGE INTO pa_target t
@@ -1792,7 +1942,8 @@ SELECT * FROM pa_target ORDER BY tid;
 
 ROLLBACK;
@@ -1803,31 +1954,34 @@ MERGE INTO pa_target t
 
 ROLLBACK;
 -- try updating the partition key column
@@ -1842,7 +1996,9 @@ MERGE INTO pa_target t
   WHEN MATCHED THEN
     UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge'
   WHEN NOT MATCHED THEN
-    INSERT VALUES (sid, delta, 'inserted by merge');
+    INSERT VALUES (sid, delta, 'inserted by merge')
+  WHEN NOT MATCHED BY SOURCE THEN
+    UPDATE SET tid = 1, val = val || ' not matched by source';
 IF FOUND THEN
   GET DIAGNOSTICS result := ROW_COUNT;
 END IF;
@@ -1852,27 +2008,28 @@ $$;
 SELECT merge_func();
  merge_func 
 ------------
-         14
+         15
 (1 row)

 ROLLBACK;
 -- update partition key to partition not initially scanned
@@ -1898,7 +2055,8 @@ SELECT * FROM pa_target ORDER BY tid;
    9 |     900 | initial
   11 |    1100 | initial
   13 |    1300 | initial
-(7 rows)
+  15 |    1500 | initial
+(8 rows)
 
 ROLLBACK;
@@ -1920,7 +2078,7 @@ ALTER TABLE pa_target ATTACH PARTITION p
@@ -1933,15 +2091,17 @@ MERGE INTO pa_target t
@@ -1956,7 +2116,8 @@ SELECT * FROM pa_target ORDER BY tid;
 
 ROLLBACK;
@@ -1968,29 +2129,32 @@ MERGE INTO pa_target t
 
 ROLLBACK;
@@ -2005,30 +2169,33 @@ MERGE INTO pa_target t
 
 ROLLBACK;
@@ -2047,15 +2214,17 @@ MERGE INTO pa_target t
@@ -2070,7 +2239,8 @@ SELECT * FROM pa_target ORDER BY tid;
 
 ROLLBACK;
@@ -2089,19 +2259,22 @@ MERGE INTO pa_target t
 
 ROLLBACK;
 -- test RLS enforcement
index 84e359f..35d4eb6
@@ -3726,7 +3726,39 @@ BEGIN ATOMIC
      t.data,
      t.filling;
index 1062c34..3913674
@@ -499,6 +499,32 @@ SELECT * FROM base_tbl ORDER BY a;
   5 | Unspecified
 (6 rows)
 
+MERGE INTO rw_view1 t
+  USING (VALUES (0, 'R0'), (1, 'R1'),
+                (2, 'R2'), (3, 'R3')) AS v(a,b) ON t.a = v.a
+  WHEN MATCHED AND t.a <= 1 THEN UPDATE SET b = v.b
+  WHEN MATCHED THEN DELETE
+  WHEN NOT MATCHED BY SOURCE THEN DELETE
+  WHEN NOT MATCHED AND a > 0 THEN INSERT (a) VALUES (v.a)
+  RETURNING merge_action(), v.*, t.*;
+ merge_action | a | b  | a |      b      
+--------------+---+----+---+-------------
+ UPDATE       | 1 | R1 | 1 | R1
+ DELETE       |   |    | 5 | Unspecified
+ DELETE       | 2 | R2 | 2 | Unspecified
+ INSERT       | 3 | R3 | 3 | Unspecified
+(4 rows)
+
+SELECT * FROM base_tbl ORDER BY a;
+ a  |      b      
+----+-------------
+ -2 | Row -2
+ -1 | Row -1
+  0 | Row 0
+  1 | R1
+  3 | Unspecified
+(5 rows)
+
 EXPLAIN (costs off) UPDATE rw_view1 SET a=6 WHERE a=5;
                     QUERY PLAN                    
 --------------------------------------------------
@@ -545,6 +571,23 @@ MERGE INTO rw_view1 t
 EXPLAIN (costs off)
 MERGE INTO rw_view1 t
   USING (SELECT * FROM generate_series(1,5)) AS s(a) ON t.a = s.a
+  WHEN NOT MATCHED BY SOURCE THEN DELETE;
+                            QUERY PLAN                             
+-------------------------------------------------------------------
+ Merge on base_tbl
+   ->  Hash Left Join
+         Hash Cond: (base_tbl.a = generate_series.generate_series)
+         ->  Bitmap Heap Scan on base_tbl
+               Recheck Cond: (a > 0)
+               ->  Bitmap Index Scan on base_tbl_pkey
+                     Index Cond: (a > 0)
+         ->  Hash
+               ->  Function Scan on generate_series
+(9 rows)
+
+EXPLAIN (costs off)
+MERGE INTO rw_view1 t
+  USING (SELECT * FROM generate_series(1,5)) AS s(a) ON t.a = s.a
   WHEN NOT MATCHED THEN INSERT (a) VALUES (s.a);
                             QUERY PLAN                             
 -------------------------------------------------------------------
@@ -656,6 +699,29 @@ SELECT * FROM rw_view2 ORDER BY aaa;
    5 | Unspecified
 (3 rows)
 
+MERGE INTO rw_view2 t
+  USING (VALUES (4, 'r4'), (5, 'r5'), (6, 'r6')) AS v(a,b) ON aaa = v.a
+  WHEN MATCHED AND aaa = 4 THEN DELETE
+  WHEN MATCHED THEN UPDATE SET bbb = v.b
+  WHEN NOT MATCHED THEN INSERT (aaa) VALUES (v.a)
+  WHEN NOT MATCHED BY SOURCE THEN UPDATE SET bbb = 'Not matched by source'
+  RETURNING merge_action(), v.*, t.*;
+ merge_action | a | b  | aaa |          bbb          
+--------------+---+----+-----+-----------------------
+ UPDATE       |   |    |   1 | Not matched by source
+ DELETE       | 4 | r4 |   4 | R4
+ UPDATE       | 5 | r5 |   5 | r5
+ INSERT       | 6 | r6 |   6 | Unspecified
+(4 rows)
+
+SELECT * FROM rw_view2 ORDER BY aaa;
+ aaa |          bbb          
+-----+-----------------------
+   1 | Not matched by source
+   5 | r5
+   6 | Unspecified
+(3 rows)
+
 EXPLAIN (costs off) UPDATE rw_view2 SET aaa=5 WHERE aaa=4;
                        QUERY PLAN                       
 --------------------------------------------------------
@@ -1106,6 +1172,30 @@ SELECT * FROM base_tbl ORDER BY a;
   3 | R3
 (5 rows)
 
+MERGE INTO rw_view2 t
+  USING (SELECT x, 'r'||x FROM generate_series(0,2) x) AS s(a,b) ON t.a = s.a
+  WHEN MATCHED THEN UPDATE SET b = s.b
+  WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b)
+  WHEN NOT MATCHED BY SOURCE THEN UPDATE SET b = 'Not matched by source'
+  RETURNING merge_action(), s.*, t.*;
+ merge_action | a | b  | a |           b           
+--------------+---+----+---+-----------------------
+ UPDATE       | 2 | r2 | 2 | r2
+ UPDATE       |   |    | 3 | Not matched by source
+ INSERT       | 1 | r1 | 1 | r1
+(3 rows)
+
+SELECT * FROM base_tbl ORDER BY a;
+ a  |           b           
+----+-----------------------
+ -2 | Row -2
+ -1 | Row -1
+  0 | Row 0
+  1 | r1
+  2 | r2
+  3 | Not matched by source
+(6 rows)
+
 EXPLAIN (costs off) UPDATE rw_view2 SET a=3 WHERE a=2;
                         QUERY PLAN                        
 ----------------------------------------------------------
diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql
new file mode 100644
index 875cf6f..3d5d854
--- a/src/test/regress/sql/merge.sql
+++ b/src/test/regress/sql/merge.sql
@@ -53,6 +53,12 @@ USING source AS s
 ON t.tid = s.sid
 WHEN MATCHED THEN
 	INSERT DEFAULT VALUES;
+-- NOT MATCHED BY SOURCE/INSERT error
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED BY SOURCE THEN
+	INSERT DEFAULT VALUES;
 -- incorrectly specifying INTO target
 MERGE INTO target t
 USING source AS s
@@ -77,6 +83,12 @@ USING source AS s
 ON t.tid = s.sid
 WHEN NOT MATCHED THEN
 	UPDATE SET balance = 0;
+-- NOT MATCHED BY TARGET/UPDATE
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED BY TARGET THEN
+	UPDATE SET balance = 0;
 -- UPDATE tablename
 MERGE INTO target t
 USING source AS s
@@ -214,6 +226,19 @@ WHEN NOT MATCHED THEN
 SELECT * FROM target ORDER BY tid;
 ROLLBACK;
 
+RETURNING merge_action(), t.*;
@@ -498,6 +523,17 @@ WHEN NOT MATCHED AND s.balance = 100 THE
 
@@ -625,6 +661,26 @@ WHEN NOT MATCHED THEN
 
+RETURNING merge_action(), t.*;
@@ -1061,6 +1117,20 @@ WHEN MATCHED AND t.a >= 30 AND t.a <= 40
 
@@ -1133,7 +1203,7 @@ CREATE TABLE pa_source (sid integer, del
 
 -- try simple MERGE
 BEGIN;
@@ -1143,8 +1213,10 @@ MERGE INTO pa_target t
   WHEN MATCHED THEN
 
@@ -1155,8 +1227,10 @@ MERGE INTO pa_target t
 
@@ -1171,7 +1245,9 @@ MERGE INTO pa_target t
@@ -1179,7 +1255,7 @@ RETURN result;
 
 -- update partition key to partition not initially scanned
@@ -1216,7 +1292,7 @@ ALTER TABLE pa_target ATTACH PARTITION p
 ALTER TABLE pa_target ATTACH PARTITION part4 DEFAULT;

 -- try simple MERGE
 BEGIN;
@@ -1230,12 +1306,14 @@ MERGE INTO pa_target t
   WHEN MATCHED THEN
 
@@ -1247,8 +1325,10 @@ MERGE INTO pa_target t
 
@@ -1263,12 +1343,14 @@ MERGE INTO pa_target t
 
@@ -1287,12 +1369,14 @@ MERGE INTO pa_target t
 
@@ -1311,12 +1395,14 @@ MERGE INTO pa_target t
 
 -- test RLS enforcement
diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql
new file mode 100644
index 27340ba..4a5fa50
--- a/src/test/regress/sql/rules.sql
+++ b/src/test/regress/sql/rules.sql
@@ -1338,7 +1338,26 @@ END;
 
 \sf merge_sf_test

 --
diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql
new file mode 100644
index afdf331..e0ab923
--- a/src/test/regress/sql/updatable_views.sql
+++ b/src/test/regress/sql/updatable_views.sql
@@ -179,6 +179,16 @@ MERGE INTO rw_view1 t
   RETURNING merge_action(), v.*, t.*;
 SELECT * FROM base_tbl ORDER BY a;
 
+MERGE INTO rw_view1 t
+  WHEN NOT MATCHED AND a > 0 THEN INSERT (a) VALUES (v.a)
+  RETURNING merge_action(), v.*, t.*;
 
@@ -194,6 +204,11 @@ MERGE INTO rw_view1 t
 EXPLAIN (costs off)
 MERGE INTO rw_view1 t
 
 -- it's still updatable if we add a DO ALSO rule
@@ -251,6 +266,15 @@ MERGE INTO rw_view2 t
   RETURNING merge_action(), v.*, t.*;
 SELECT * FROM rw_view2 ORDER BY aaa;
 
+MERGE INTO rw_view2 t
+  USING (VALUES (4, 'r4'), (5, 'r5'), (6, 'r6')) AS v(a,b) ON aaa = v.a
+  WHEN MATCHED AND aaa = 4 THEN DELETE
+  WHEN MATCHED THEN UPDATE SET bbb = v.b
+  WHEN NOT MATCHED THEN INSERT (aaa) VALUES (v.a)
+  WHEN NOT MATCHED BY SOURCE THEN UPDATE SET bbb = 'Not matched by source'
+  RETURNING merge_action(), v.*, t.*;
+SELECT * FROM rw_view2 ORDER BY aaa;
+
 EXPLAIN (costs off) UPDATE rw_view2 SET aaa=5 WHERE aaa=4;
 EXPLAIN (costs off) DELETE FROM rw_view2 WHERE aaa=4;
 
@@ -464,6 +488,14 @@ MERGE INTO rw_view2 t
   RETURNING merge_action(), s.*, t.*;
 SELECT * FROM base_tbl ORDER BY a;
 
+MERGE INTO rw_view2 t
+  USING (SELECT x, 'r'||x FROM generate_series(0,2) x) AS s(a,b) ON t.a = s.a
+  WHEN MATCHED THEN UPDATE SET b = s.b
+  WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b)
+  WHEN NOT MATCHED BY SOURCE THEN UPDATE SET b = 'Not matched by source'
+  RETURNING merge_action(), s.*, t.*;
+SELECT * FROM base_tbl ORDER BY a;
+
 EXPLAIN (costs off) UPDATE rw_view2 SET a=3 WHERE a=2;
 EXPLAIN (costs off) DELETE FROM rw_view2 WHERE a=2;
 
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
new file mode 100644
index 6ca93b1..f7b70db
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1575,6 +1575,7 @@ MergeAppendState
 MergeJoin
 MergeJoinClause
 MergeJoinState
+MergeMatchKind
 MergePath
 MergeScanSelCache
 MergeStmt

---

From: Dean Rasheed <[email-masked]>
Date: Thu, 21 Mar 2024 09:35:18 +0000

> Rebased version attached, on top of c649fa24a4 (MERGE ... RETURNING support).

Trivial rebase forced by 6185c9737c.

Regards,
Dean

 
@@ -73,7 +74,9 @@ DELETE
 
+  <para>
 
  <refsect1>
 
 	/*
 
 		if (part_attmap == NULL)
 			part_attmap =

 

 	/*-----
 
 	return rslot;
 }
 
 /*

 	/*
 
 	/*
 
 	/*

 
 
 			default:
 
 		switch (result)
 
 				/*
 
 			case TM_Updated:
 				{
+					bool		was_matched;
 
 					/*

 
 							/*
 
 							/*
 
 						case TM_Deleted:
 
 							/*
 
 /*

 	/*

 
 			/*
 
 			/*

+		/*

index 9f1553b..7d37226
@@ -2701,6 +2701,8 @@ query_tree_walker_impl(Query *query,
@@ -3752,6 +3754,7 @@ query_tree_mutator_impl(Query *query,
index 5f479fc..3b77886
@@ -312,7 +312,8 @@ static ModifyTable *make_modifytable(Pla
 
@@ -2836,6 +2837,7 @@ create_modifytable_plan(PlannerInfo *roo
 							best_path->rowMarks,
 							best_path->onconflict,
 							best_path->mergeActionLists,
+							best_path->mergeJoinConditions,
 							best_path->epqParam);
 
@@ -7031,7 +7033,8 @@ make_modifytable(PlannerInfo *root, Plan
@@ -7101,6 +7104,7 @@ make_modifytable(PlannerInfo *root, Plan
 
 	/*

 
 
 			/*

 					/*

 

 
 	/*

 /*
 
 		/*

 	/*
index 0a7e5c2..49f4c97
@@ -3714,6 +3714,7 @@ create_lockrows_path(PlannerInfo *root,
@@ -3725,7 +3726,8 @@ create_modifytable_path(PlannerInfo *roo
 
@@ -3793,6 +3795,7 @@ create_modifytable_path(PlannerInfo *roo
 
 	return pathnode;
 }
index c247eef..bc9a793

@@ -769,11 +771,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 STRING_P STRIP_P
 	SUBSCRIPTION SUBSTRING SUPPORT SYMMETRIC SYSID SYSTEM_P SYSTEM_USER
 
@@ -12403,50 +12405,66 @@ merge_when_list:
 
+/*
 
-					$$ = (Node *) $5;
+					$$ = (Node *) $4;
 				}
 
-					$$ = (Node *) $5;
+					$$ = (Node *) $4;
 				}
 
-					$$ = (Node *) $6;
+					$$ = (Node *) $4;
 				}
 
-					m->matched = true;
+					m->matchKind = $1;
 
 					$$ = (Node *) m;
 				}
 
-					m->matched = false;
+					m->matchKind = $1;
 
 					$$ = (Node *) m;
 				}
 		;
 
+merge_when_tgt_matched:
@@ -17554,6 +17572,7 @@ unreserved_keyword:
@@ -17573,6 +17592,7 @@ unreserved_keyword:
@@ -18183,6 +18203,7 @@ bare_label_keyword:
@@ -18207,6 +18228,7 @@ bare_label_keyword:

 
 		/*
 
 	/*
 
 	/*

 
 		/*
index 4a7402e..0832d45
@@ -7109,6 +7109,7 @@ get_merge_query_def(Query *query, depars
 
@@ -7134,7 +7135,26 @@ get_merge_query_def(Query *query, depars
 
 	/* Print each merge action */
 	foreach(lc, query->mergeActionList)
@@ -7143,7 +7163,24 @@ get_merge_query_def(Query *query, depars

 		if (action->qual)
 		{

 
 
index 1774c56..88cc936

@@ -1401,6 +1403,13 @@ typedef struct ModifyTableState
 
+	/*
index 9b709f0..8f81826

 	/*

 	/* OVERRIDING clause */
 
 /*
index 7f3db51..e025679
 
index 376f67e..aa727e7
@@ -1875,10 +1875,18 @@ typedef struct BooleanTest
index 99c2f95..527ef0b
index 3941ef1..cf7836d
@@ -413,6 +413,7 @@ PG_KEYWORD("skip", SKIP, UNRESERVED_KEYW
@@ -437,6 +438,7 @@ PG_KEYWORD("table", TABLE, RESERVED_KEYW

 step c2: COMMIT;
 
 step c2: COMMIT;
 
 step c2: COMMIT;

 step c2: COMMIT;
index cec7f11..eddc1f4
 
 ROLLBACK;

 ROLLBACK;

 ROLLBACK;
 
 ROLLBACK;

 ROLLBACK;
 
 ROLLBACK;
 
 ROLLBACK;
 
 ROLLBACK;
 
 ROLLBACK;
 
 ROLLBACK;
 
 ROLLBACK;
 -- test RLS enforcement
index 713bf84..1d1f568
 
+MERGE INTO rw_view1 t
 
+MERGE INTO rw_view2 t
 
+MERGE INTO rw_view2 t

 

 -- try simple MERGE
 BEGIN;

 

 -- try simple MERGE
 BEGIN;

 

 -- test RLS enforcement
 
 \sf merge_sf_test

 
+MERGE INTO rw_view1 t

 
+MERGE INTO rw_view2 t

+MERGE INTO rw_view2 t
 
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
new file mode 100644
index 3b8cec5..2beabb8
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1588,6 +1588,7 @@ MergeAppendState
 MergeJoin
 MergeJoinClause
 MergeJoinState
+MergeMatchKind
 MergePath
 MergeScanSelCache
 MergeStmt