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 opt_on_conflict %type merge_insert merge_update merge_delete +%type merge_when_tgt_matched merge_when_tgt_not_matched %type merge_when_clause opt_merge_when_condition %type 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..f565072 --- 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 @@ -156,13 +174,32 @@ transformMergeStmt(ParseState *pstate, M * Check for unreachable WHEN clauses */ if (mergeWhenClause->condition == NULL) - is_terminal[when_type] = true; - else if (is_terminal[when_type]) + is_terminal[mergeWhenClause->matchKind] = true; + else if (is_terminal[mergeWhenClause->matchKind]) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("unreachable WHEN clause specified after unconditional WHEN clause"))); + + /* 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,12 @@ 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; + action->matched = matchKind != MERGE_WHEN_NOT_MATCHED_BY_TARGET; /* * Set namespace for the specific action. This must be done before @@ -280,6 +314,47 @@ 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 + * MATCHED and NOT MATCHED BY SOURCE action to distinguish them from + * one another. + * + * MATCHED rows are identified by a "source IS DISTINCT FROM NULL" + * clause, and NOT MATCHED BY SOURCE rows by a "source IS NOT DISTINCT + * FROM NULL" clause. + * + * Nothing is needed for NOT MATCHED BY TARGET rows, since they are + * identified in the executor by checking the target identity column. + */ + if (left_join && action->matched) + { + NullTest *ntest; + + /* Make a "source IS [NOT] DISTINCT FROM NULL" clause */ + ntest = makeNode(NullTest); + + ntest->arg = (Expr *) makeVar(sourceRTI, + InvalidAttrNumber, + RECORDOID, + -1, + InvalidOid, + 0); + ntest->nulltesttype = + matchKind == MERGE_WHEN_MATCHED ? IS_NOT_NULL : IS_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..887a5d1 --- 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; /* 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) */ 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