On Fri, 30 Dec 2022 at 16:56, Dean Rasheed <dean.a.rash...@gmail.com> wrote: > > Attached is a WIP patch. >
Updated patch attached, now with updated docs and some other minor tidying up. Regards, Dean
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml new file mode 100644 index b87ad5c..1482ede --- 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/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c new file mode 100644 index 4cec12a..ed538e5 --- a/src/backend/optimizer/prep/prepjointree.c +++ b/src/backend/optimizer/prep/prepjointree.c @@ -142,7 +142,6 @@ transform_MERGE_to_join(Query *parse) { RangeTblEntry *joinrte; JoinExpr *joinexpr; - JoinType jointype; int joinrti; List *vars; @@ -152,20 +151,10 @@ transform_MERGE_to_join(Query *parse) /* XXX probably bogus */ 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. - */ - if (parse->mergeUseOuterJoin) - jointype = JOIN_RIGHT; - else - jointype = JOIN_INNER; - /* Manufacture a join RTE to use. */ joinrte = makeNode(RangeTblEntry); joinrte->rtekind = RTE_JOIN; - joinrte->jointype = jointype; + joinrte->jointype = parse->mergeJoinType; joinrte->joinmergedcols = 0; joinrte->joinaliasvars = vars; joinrte->joinleftcols = NIL; /* MERGE does not allow JOIN USING */ @@ -189,7 +178,7 @@ transform_MERGE_to_join(Query *parse) * Create a JOIN between the target and the source relation. */ joinexpr = makeNode(JoinExpr); - joinexpr->jointype = jointype; + joinexpr->jointype = parse->mergeJoinType; joinexpr->isNatural = false; joinexpr->larg = (Node *) makeNode(RangeTblRef); ((RangeTblRef *) joinexpr->larg)->rtindex = parse->resultRelation; diff --git a/src/backend/optimizer/prep/preptlist.c b/src/backend/optimizer/prep/preptlist.c new file mode 100644 index 137b283..1a4e13e --- a/src/backend/optimizer/prep/preptlist.c +++ b/src/backend/optimizer/prep/preptlist.c @@ -157,15 +157,14 @@ preprocess_targetlist(PlannerInfo *root) /* * Add resjunk entries for any Vars used in each action's * targetlist and WHEN condition that belong to relations other - * than target. Note that aggregates, window functions and - * placeholder vars are not possible anywhere in MERGE's WHEN - * clauses. (PHVs may be added later, but they don't concern us - * here.) + * than target. Note that aggregates and window functions are not + * possible anywhere in MERGE's WHEN clauses, but PlaceHolderVars + * may have been added by subquery pullup. */ vars = pull_var_clause((Node *) list_concat_copy((List *) action->qual, action->targetList), - 0); + PVC_INCLUDE_PLACEHOLDERS); foreach(l2, vars) { Var *var = (Var *) lfirst(l2); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y new file mode 100644 index 63b4baa..95531ab --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -273,6 +273,7 @@ static Node *makeRecursiveViewSelect(cha struct SelectLimit *selectlimit; SetQuantifier setquantifier; struct GroupClause *groupclause; + MergeMatchKind mergematch; MergeWhenClause *mergewhen; struct KeyActions *keyactions; struct KeyAction *keyaction; @@ -512,6 +513,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 @@ -740,11 +742,11 @@ static Node *makeRecursiveViewSelect(cha SAVEPOINT 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 @@ -12260,49 +12262,59 @@ merge_when_list: ; 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; } @@ -16961,6 +16973,7 @@ unreserved_keyword: | SIMPLE | SKIP | SNAPSHOT + | SOURCE | SQL_P | STABLE | STANDALONE_P @@ -16979,6 +16992,7 @@ unreserved_keyword: | SYSTEM_P | TABLES | TABLESPACE + | TARGET | TEMP | TEMPLATE | TEMPORARY @@ -17555,6 +17569,7 @@ bare_label_keyword: | SMALLINT | SNAPSHOT | SOME + | SOURCE | SQL_P | STABLE | STANDALONE_P @@ -17578,6 +17593,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 3844f2b..997b7e2 --- 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 MATCH 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 /* mergeWhenClause->matchKind == 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,7 +112,9 @@ transformMergeStmt(ParseState *pstate, M Query *qry = makeNode(Query); ListCell *l; AclMode targetPerms = ACL_NO_RIGHTS; - bool is_terminal[2]; + bool is_terminal[3]; + bool left_join; + bool right_join; Index sourceRTI; List *mergeActionList; Node *joinExpr; @@ -127,10 +143,12 @@ transformMergeStmt(ParseState *pstate, M */ is_terminal[0] = false; is_terminal[1] = false; + is_terminal[2] = false; + left_join = false; + right_join = 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,14 +173,33 @@ transformMergeStmt(ParseState *pstate, M /* * Check for unreachable WHEN clauses */ - if (mergeWhenClause->condition == NULL) - is_terminal[when_type] = true; - else 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[mergeWhenClause->matchKind] = true; + + /* Is left/right/full outer join required? */ + if (mergeWhenClause->commandType != CMD_NOTHING) + { + if (mergeWhenClause->matchKind == MERGE_WHEN_NOT_MATCHED_BY_SOURCE) + left_join = true; + if (mergeWhenClause->matchKind == MERGE_WHEN_NOT_MATCHED_BY_TARGET) + right_join = true; + } } + /* Join type required */ + if (left_join && right_join) + qry->mergeJoinType = JOIN_FULL; + else if (left_join) + qry->mergeJoinType = JOIN_LEFT; + else if (right_join) + qry->mergeJoinType = JOIN_RIGHT; + else + qry->mergeJoinType = JOIN_INNER; + /* * Set up the MERGE target table. The target table is added to the * namespace below and to joinlist in transform_MERGE_to_join, so don't @@ -251,15 +288,19 @@ transformMergeStmt(ParseState *pstate, M foreach(l, stmt->mergeWhenClauses) { MergeWhenClause *mergeWhenClause = lfirst_node(MergeWhenClause, l); + MergeMatchKind matchKind = mergeWhenClause->matchKind; MergeAction *action; 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; + /* + * For planning and execution purposes, MATCHED and NOT MATCHED BY + * SOURCE actions are handled the same, since they each must match + * target rows. Thus we need only note whether the action is NOT + * MATCHED [BY TARGET]. + */ + action->matched = matchKind != MERGE_WHEN_NOT_MATCHED_BY_TARGET; /* * Set namespace for the specific action. This must be done before @@ -280,6 +321,50 @@ transformMergeStmt(ParseState *pstate, M EXPR_KIND_MERGE_WHEN, "WHEN"); /* + * If we are doing a left or full join that includes target-only rows + * (NOT MATCHED BY SOURCE), add additional WHEN conditions to each + * action to test if the source row matches. + * + * This distinguishes NOT MATCHED BY SOURCE actions (identified by a + * "source IS NOT DISTINCT FROM NULL" clause) from MATCHED actions + * (identified by a "source IS DISTINCT FROM NULL" clause). + * + * Additionally, a "source IS DISTINCT FROM NULL" clause is required + * for 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 (left_join) + { + bool src_null; + NullTest *ntest; + + /* Make a "source IS [NOT] DISTINCT FROM NULL" clause */ + src_null = matchKind == MERGE_WHEN_NOT_MATCHED_BY_SOURCE; + + ntest = makeNode(NullTest); + + ntest->arg = (Expr *) makeVar(sourceRTI, + InvalidAttrNumber, + RECORDOID, + -1, + InvalidOid, + 0); + ntest->nulltesttype = src_null ? IS_NULL : IS_NOT_NULL; + ntest->argisrow = false; + ntest->location = -1; + + /* Combine it with the action's WHEN condition */ + if (action->qual == NULL) + action->qual = (Node *) ntest; + else + action->qual = + (Node *) makeBoolExpr(AND_EXPR, + list_make2(ntest, action->qual), + -1); + } + + /* * Transform target lists for each INSERT and UPDATE action stmt */ switch (action->commandType) diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h new file mode 100644 index 34bc640..e9636c8 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -159,7 +159,7 @@ typedef struct Query * also USING clause for MERGE */ List *mergeActionList; /* list of actions for MERGE (only) */ - bool mergeUseOuterJoin; /* whether to use outer join */ + JoinType mergeJoinType; /* MERGE target to source join type */ List *targetList; /* target list (of TargetEntry) */ @@ -1586,6 +1586,14 @@ typedef struct CommonTableExpr ((Query *) (cte)->ctequery)->targetList : \ ((Query *) (cte)->ctequery)->returningList) +/* MATCHED/NOT MATCHED specification for MergeWhenClause */ +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 @@ -1595,7 +1603,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) */ @@ -1611,7 +1619,8 @@ typedef struct MergeWhenClause typedef struct MergeAction { NodeTag type; - bool matched; /* true=MATCHED, false=NOT MATCHED */ + bool matched; /* true if target matches (input matchKind is + * MATCHED or NOT MATCHED BY SOURCE) */ CmdType commandType; /* INSERT/UPDATE/DELETE/DO NOTHING */ OverridingKind override; /* OVERRIDING clause */ Node *qual; /* transformed WHEN conditions */ diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h new file mode 100644 index 957ee18..fd94b0b --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -391,6 +391,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) @@ -414,6 +415,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 6c8a18f..e99623a --- 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 not matched +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 @@ -1436,6 +1522,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/sql/merge.sql b/src/test/regress/sql/merge.sql new file mode 100644 index 98fe104..c641214 --- 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 not matched +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 @@ -916,6 +970,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