On Tue, 21 Mar 2023 at 12:26, Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote:
>
> On 2023-Mar-21, Dean Rasheed wrote:
>
> > Looking at it with fresh eyes though, I realise that I could have just
> > written
> >
> > action->qual = make_and_qual((Node *) ntest, action->qual);
> >
> > which is equivalent, but more concise.
>
> Nice.
>
> I have no further observations about this patch.
>
Looking at this one afresh, it seems that the change to make Vars
outer-join aware broke it -- the Var in the qual to test whether the
source row is null needs to be marked as nullable by the join added by
transform_MERGE_to_join(). That's something that needs to be done in
transform_MERGE_to_join(), so it makes more sense to add the new qual
there rather than in transformMergeStmt().
Also, now that MERGE has ruleutils support, it's clear that adding the
qual in transformMergeStmt() isn't right anyway, since it would then
appear in the deparsed output.
So attached is an updated patch doing that, which seems neater all
round, since adding the qual is closely related to the join-type
choice, which is now a decision taken entirely in
transform_MERGE_to_join(). This requires a new "mergeSourceRelation"
field on the Query structure, but as before, it does away with the
"mergeUseOuterJoin" field.
I've also updated the ruleutils support. In the absence of any WHEN
NOT MATCHED BY SOURCE actions, this will output not-matched actions
simply as "WHEN NOT MATCHED" for backwards compatibility, and to be
SQL-standard-compliant. If there are any WHEN NOT MATCHED BY SOURCE
actions though, I think it's preferable to output explicit "BY SOURCE"
and "BY TARGET" qualifiers for all not-matched actions, to make the
meaning clearer.
Regards,
Dean
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
new file mode 100644
index f8f83d4..6ef0c2b
--- a/doc/src/sgml/mvcc.sgml
+++ b/doc/src/sgml/mvcc.sgml
@@ -396,8 +396,8 @@
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.
+ evaluate the condition's <literal>NOT MATCHED [BY TARGET]</literal>
+ actions next, 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 0995fe0..8ef121a
--- 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>
@@ -70,7 +71,9 @@ DELETE
from <replaceable class="parameter">data_source</replaceable> to
<replaceable class="parameter">target_table_name</replaceable>
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>
@@ -226,16 +229,37 @@ 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
+ and the candidate change row matches a row in the source to a row in the
<replaceable class="parameter">target_table_name</replaceable>,
the <literal>WHEN</literal> clause is executed if the
<replaceable class="parameter">condition</replaceable> is
absent or it evaluates to <literal>true</literal>.
</para>
<para>
- Conversely, if the <literal>WHEN</literal> clause specifies
- <literal>WHEN NOT MATCHED</literal>
+ If the <literal>WHEN</literal> clause specifies
+ <literal>WHEN NOT MATCHED BY SOURCE</literal> and the candidate change
+ row represents a row in the
+ <replaceable class="parameter">target_table_name</replaceable> that does
+ not match a source row, 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 does not match a row in the
<replaceable class="parameter">target_table_name</replaceable>,
the <literal>WHEN</literal> clause is executed if the
@@ -257,7 +281,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>
@@ -382,8 +409,10 @@ DELETE
<literal>WHEN MATCHED</literal> clause, the expression can use values
from the original row in the target table, and values from the
<literal>data_source</literal> row.
- If used in a <literal>WHEN NOT MATCHED</literal> clause, the
- expression can use values from the <literal>data_source</literal>.
+ 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 <literal>data_source</literal>.
</para>
</listitem>
</varlistentry>
@@ -452,8 +481,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>
@@ -528,7 +558,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
@@ -619,6 +650,23 @@ WHEN MATCHED THEN
temporary table recently loaded into the database.
</para>
+ <para>
+ Update <literal>wine</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>
@@ -627,7 +675,10 @@ WHEN MATCHED THEN
This command conforms to the <acronym>SQL</acronym> standard.
</para>
<para>
- The WITH clause and <literal>DO NOTHING</literal> action are extensions to
+ The WITH 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/execPartition.c b/src/backend/executor/execPartition.c
new file mode 100644
index eb8a87f..a1add5a
--- a/src/backend/executor/execPartition.c
+++ b/src/backend/executor/execPartition.c
@@ -902,8 +902,16 @@ ExecInitPartitionInfo(ModifyTableState *
action_state = makeNode(MergeActionState);
action_state->mas_action = action;
- /* And put the action in the appropriate list */
- if (action->matched)
+ /*
+ * Put the action in the appropriate list.
+ *
+ * Note that the executor treats WHEN NOT MATCHED BY SOURCE
+ * actions in exactly the same way as WHEN MATCHED actions, since
+ * they both match the target (see ExecMerge). Thus both types go
+ * in the "matched" list. Only WHEN NOT MATCHED BY TARGET actions
+ * go in the "not matched" list.
+ */
+ if (action->matchKind != MERGE_WHEN_NOT_MATCHED_BY_TARGET)
list = &leaf_part_rri->ri_matchedMergeAction;
else
list = &leaf_part_rri->ri_notMatchedMergeAction;
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
new file mode 100644
index 2a5fec8..314dbaf
--- 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.
@@ -2715,6 +2717,17 @@ ExecMerge(ModifyTableContext *context, R
bool matched;
/*-----
+ * Note that as far as the executor is concerned, WHEN NOT MATCHED BY
+ * SOURCE actions are treated exactly the same as WHEN MATCHED actions,
+ * since both match target tuples. They are distinguished from one
+ * another by a qual that tests if the source tuple is NULL, but the
+ * executor knows nothing about the contents of the merge action quals.
+ * Thus WHEN MATCHED and WHEN NOT MATCHED BY SOURCE actions are stored
+ * together in the same "matched" list. Thus, in the dicussion that
+ * follows "MATCHED" means "matched by target", and should be taken to
+ * include both WHEN MATCHED and WHEN NOT MATCHED BY SOURCE, while "NOT
+ * MATCHED" means NOT MATCHED BY TARGET.
+ *
* If we are dealing with a WHEN MATCHED case (tupleid is valid), we
* execute the first action for which the additional WHEN MATCHED AND
* quals pass. If an action without quals is found, that action is
@@ -3044,9 +3057,13 @@ lmerge_matched:
* is executed.
*
* Update tupleid to that of the new tuple, for
- * the refetch we do at the top.
+ * the refetch we do at the top, and install the
+ * updated plan slot in ecxt_innertuple in case
+ * the source tuple is now NULL (a change from
+ * MATCHED to NOT MATCHED BY SOURCE).
*/
ItemPointerCopy(&context->tmfd.ctid, tupleid);
+ econtext->ecxt_innertuple = epqslot;
goto lmerge_matched;
case TM_Deleted:
@@ -3251,8 +3268,14 @@ ExecInitMerge(ModifyTableState *mtstate,
* We create two lists - one for WHEN MATCHED actions and one for
* WHEN NOT MATCHED actions - and stick the MergeActionState into
* the appropriate list.
+ *
+ * Note that the executor treats WHEN NOT MATCHED BY SOURCE
+ * actions in exactly the same way as WHEN MATCHED actions, since
+ * they both match the target (see ExecMerge). Thus both types go
+ * in the "matched" list. Only WHEN NOT MATCHED BY TARGET actions
+ * go in the "not matched" list.
*/
- if (action_state->mas_action->matched)
+ if (action->matchKind != MERGE_WHEN_NOT_MATCHED_BY_TARGET)
list = &resultRelInfo->ri_matchedMergeAction;
else
list = &resultRelInfo->ri_notMatchedMergeAction;
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
new file mode 100644
index 73ff407..be45646
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -153,6 +153,9 @@ transform_MERGE_to_join(Query *parse)
{
RangeTblEntry *joinrte;
JoinExpr *joinexpr;
+ ListCell *lc;
+ bool src_only_tuples;
+ bool tgt_only_tuples;
JoinType jointype;
int joinrti;
List *vars;
@@ -164,12 +167,32 @@ 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)
+ src_only_tuples = false;
+ tgt_only_tuples = false;
+ foreach(lc, parse->mergeActionList)
+ {
+ MergeAction *action = lfirst_node(MergeAction, lc);
+
+ if (action->commandType != CMD_NOTHING)
+ {
+ if (action->matchKind == MERGE_WHEN_NOT_MATCHED_BY_SOURCE)
+ tgt_only_tuples = true;
+ if (action->matchKind == MERGE_WHEN_NOT_MATCHED_BY_TARGET)
+ src_only_tuples = true;
+ }
+ }
+
+ if (src_only_tuples && tgt_only_tuples)
+ jointype = JOIN_FULL;
+ else if (src_only_tuples)
jointype = JOIN_RIGHT;
+ else if (tgt_only_tuples)
+ jointype = JOIN_LEFT;
else
jointype = JOIN_INNER;
@@ -215,6 +238,51 @@ transform_MERGE_to_join(Query *parse)
/* Make the new join be the sole entry in the query's jointree */
parse->jointree->fromlist = list_make1(joinexpr);
parse->jointree->quals = NULL;
+
+ /*
+ * If there any WHEN NOT MATCHED BY SOURCE actions that require unmatched
+ * tuples from the target relation to be processed, add additional WHEN
+ * conditions to every action to check whether tuples from the source
+ * match or not, as necessary.
+ *
+ * This distinguishes WHEN NOT MATCHED BY SOURCE actions (identified by a
+ * "source IS NOT DISTINCT FROM NULL" clause) from WHEN MATCHED actions
+ * (identified by a "source IS DISTINCT FROM NULL" clause).
+ *
+ * Additionally, a "source IS DISTINCT FROM NULL" clause is required for
+ * WHEN NOT MATCHED [BY TARGET] actions in case the executor attempts to
+ * invoke such an action for a concurrently deleted target row that ends
+ * up matching neither source nor target.
+ */
+ if (tgt_only_tuples)
+ {
+ foreach(lc, parse->mergeActionList)
+ {
+ MergeAction *action = lfirst_node(MergeAction, lc);
+ bool src_null;
+ Var *var;
+ NullTest *ntest;
+
+ /* Make a "source IS [NOT] DISTINCT FROM NULL" clause */
+ src_null = action->matchKind == MERGE_WHEN_NOT_MATCHED_BY_SOURCE;
+
+ var = makeWholeRowVar(rt_fetch(parse->mergeSourceRelation,
+ parse->rtable),
+ parse->mergeSourceRelation, 0, false);
+
+ /* source wholerow Var is nullable by the new join */
+ var->varnullingrels = bms_make_singleton(joinrti);
+
+ ntest = makeNode(NullTest);
+ ntest->arg = (Expr *) var;
+ ntest->nulltesttype = src_null ? IS_NULL : IS_NOT_NULL;
+ ntest->argisrow = false;
+ ntest->location = -1;
+
+ /* Combine it with the action's WHEN condition */
+ action->qual = make_and_qual((Node *) ntest, action->qual);
+ }
+ }
}
/*
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
new file mode 100644
index 39ab7ea..2f152f0
--- 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;
@@ -514,6 +515,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
@@ -12267,50 +12269,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; }
@@ -17192,6 +17210,7 @@ unreserved_keyword:
| SIMPLE
| SKIP
| SNAPSHOT
+ | SOURCE
| SQL_P
| STABLE
| STANDALONE_P
@@ -17210,6 +17229,7 @@ unreserved_keyword:
| SYSTEM_P
| TABLES
| TABLESPACE
+ | TARGET
| TEMP
| TEMPLATE
| TEMPORARY
@@ -17800,6 +17820,7 @@ bare_label_keyword:
| SMALLINT
| SNAPSHOT
| SOME
+ | SOURCE
| SQL_P
| STABLE
| STANDALONE_P
@@ -17823,6 +17844,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 91b1156..728b533
--- a/src/backend/parser/parse_merge.c
+++ b/src/backend/parser/parse_merge.c
@@ -43,9 +43,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
@@ -61,7 +61,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 ||
@@ -73,11 +73,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);
@@ -98,8 +112,7 @@ transformMergeStmt(ParseState *pstate, M
Query *qry = makeNode(Query);
ListCell *l;
AclMode targetPerms = ACL_NO_RIGHTS;
- bool is_terminal[2];
- Index sourceRTI;
+ bool is_terminal[3];
List *mergeActionList;
Node *joinExpr;
ParseNamespaceItem *nsitem;
@@ -125,12 +138,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 action types so we can check target permissions
@@ -155,12 +168,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;
}
/*
@@ -193,8 +206,8 @@ transformMergeStmt(ParseState *pstate, M
/* Now transform the source relation to produce the source RTE. */
transformFromClause(pstate,
list_make1(stmt->sourceRelation));
- sourceRTI = list_length(pstate->p_rtable);
- nsitem = GetNSItemByRangeTablePosn(pstate, sourceRTI, 0);
+ qry->mergeSourceRelation = list_length(pstate->p_rtable);
+ nsitem = GetNSItemByRangeTablePosn(pstate, qry->mergeSourceRelation, 0);
/*
* Check that the target table doesn't conflict with the source table.
@@ -255,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
@@ -267,7 +276,7 @@ transformMergeStmt(ParseState *pstate, M
*/
setNamespaceForMergeWhen(pstate, mergeWhenClause,
qry->resultRelation,
- sourceRTI);
+ qry->mergeSourceRelation);
/*
* Transform the WHEN condition.
diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c
new file mode 100644
index 32bd2f1..fcf78c4
--- a/src/backend/rewrite/rewriteManip.c
+++ b/src/backend/rewrite/rewriteManip.c
@@ -496,9 +496,10 @@ OffsetVarNodes(Node *node, int offset, i
/*
* If we are starting at a Query, and sublevels_up is zero, then we
* must also fix rangetable indexes in the Query itself --- namely
- * resultRelation, exclRelIndex and rowMarks entries. sublevels_up
- * cannot be zero when recursing into a subquery, so there's no need
- * to have the same logic inside OffsetVarNodes_walker.
+ * resultRelation, mergeSourceRelation, exclRelIndex and rowMarks
+ * entries. sublevels_up cannot be zero when recursing into a
+ * subquery, so there's no need to have the same logic inside
+ * OffsetVarNodes_walker.
*/
if (sublevels_up == 0)
{
@@ -507,6 +508,9 @@ OffsetVarNodes(Node *node, int offset, i
if (qry->resultRelation)
qry->resultRelation += offset;
+ if (qry->mergeSourceRelation)
+ qry->mergeSourceRelation += offset;
+
if (qry->onConflict && qry->onConflict->exclRelIndex)
qry->onConflict->exclRelIndex += offset;
@@ -687,9 +691,10 @@ ChangeVarNodes(Node *node, int rt_index,
/*
* If we are starting at a Query, and sublevels_up is zero, then we
* must also fix rangetable indexes in the Query itself --- namely
- * resultRelation and rowMarks entries. sublevels_up cannot be zero
- * when recursing into a subquery, so there's no need to have the same
- * logic inside ChangeVarNodes_walker.
+ * resultRelation, mergeSourceRelation, exclRelIndex and rowMarks
+ * entries. sublevels_up cannot be zero when recursing into a
+ * subquery, so there's no need to have the same logic inside
+ * ChangeVarNodes_walker.
*/
if (sublevels_up == 0)
{
@@ -698,6 +703,9 @@ ChangeVarNodes(Node *node, int rt_index,
if (qry->resultRelation == rt_index)
qry->resultRelation = new_index;
+ if (qry->mergeSourceRelation == rt_index)
+ qry->mergeSourceRelation = new_index;
+
/* this is unlikely to ever be used, but ... */
if (qry->onConflict && qry->onConflict->exclRelIndex == rt_index)
qry->onConflict->exclRelIndex = new_index;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
new file mode 100644
index d3a973d..1d78714
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -7077,6 +7077,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);
@@ -7104,6 +7105,25 @@ get_merge_query_def(Query *query, depars
-PRETTYINDENT_STD, PRETTYINDENT_STD, 2);
get_rule_expr(query->jointree->quals, 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)
{
@@ -7111,7 +7131,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 935bb9b..6cfff24
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -4270,17 +4270,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/parsenodes.h b/src/include/nodes/parsenodes.h
new file mode 100644
index b3bec90..65c3e55
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -183,8 +183,9 @@ 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 source relation for MERGE */
+ int mergeSourceRelation pg_node_attr(query_jumble_ignore);
List *targetList; /* target list (of TargetEntry) */
@@ -1662,6 +1663,14 @@ typedef struct CommonTableExpr
((Query *) (cte)->ctequery)->targetList : \
((Query *) (cte)->ctequery)->returningList)
+/* MATCHED/NOT MATCHED specification for WHEN clauses in a MERGE statement */
+typedef enum MergeMatchKind
+{
+ MERGE_WHEN_MATCHED,
+ MERGE_WHEN_NOT_MATCHED_BY_SOURCE,
+ MERGE_WHEN_NOT_MATCHED_BY_TARGET
+} MergeMatchKind;
+
/*
* MergeWhenClause -
* raw parser representation of a WHEN clause in a MERGE statement
@@ -1671,7 +1680,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) */
@@ -1687,7 +1696,7 @@ typedef struct MergeWhenClause
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/parser/kwlist.h b/src/include/parser/kwlist.h
new file mode 100644
index f5b2e61..c34635d
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -401,6 +401,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)
@@ -424,6 +425,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/regress/expected/merge.out b/src/test/regress/expected/merge.out
new file mode 100644
index 133d421..0c35a35
--- a/src/test/regress/expected/merge.out
+++ b/src/test/regress/expected/merge.out
@@ -71,6 +71,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
@@ -107,6 +116,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
@@ -271,6 +289,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);
@@ -738,6 +772,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
@@ -923,6 +970,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
@@ -1451,6 +1537,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
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
new file mode 100644
index 7fd81e6..478e714
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -3670,7 +3670,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/sql/merge.sql b/src/test/regress/sql/merge.sql
new file mode 100644
index 4cf6db9..bd399ab
--- a/src/test/regress/sql/merge.sql
+++ b/src/test/regress/sql/merge.sql
@@ -51,6 +51,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
@@ -75,6 +81,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
@@ -213,6 +225,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);
@@ -497,6 +521,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
@@ -624,6 +659,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
@@ -929,6 +983,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
diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql
new file mode 100644
index 8b7e255..ae9141f
--- a/src/test/regress/sql/rules.sql
+++ b/src/test/regress/sql/rules.sql
@@ -1323,7 +1323,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/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
new file mode 100644
index 2608547..303387c
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1548,6 +1548,7 @@ MergeAppendState
MergeJoin
MergeJoinClause
MergeJoinState
+MergeMatchKind
MergePath
MergeScanSelCache
MergeStmt
