Add support for MERGE ... WHEN NOT MATCHED BY SOURCE.

This allows MERGE commands to include WHEN NOT MATCHED BY SOURCE
actions, which operate on rows that exist in the target relation, but
not in the data source. These actions can execute UPDATE, DELETE, or
DO NOTHING sub-commands.

This is in contrast to already-supported WHEN NOT MATCHED actions,
which operate on rows that exist in the data source, but not in the
target relation. To make this distinction clearer, such actions may
now be written as WHEN NOT MATCHED BY TARGET.

Writing WHEN NOT MATCHED without specifying BY SOURCE or BY TARGET is
equivalent to writing WHEN NOT MATCHED BY TARGET.

Dean Rasheed, reviewed by Alvaro Herrera, Ted Yu and Vik Fearing.

Discussion: https://postgr.es/m/CAEZATCWqnKGc57Y_JanUBHQXNKcXd7r=0R4NEZUVwP+syRkWbA@mail.gmail.com

2 files changed, 76 insertions, 22 deletions

diff --git a/src/test/isolation/expected/merge-update.out b/src/test/isolation/expected/merge-update.out
index f5f7e3ba19b..3063c0c6ab1 100644
--- a/src/test/isolation/expected/merge-update.out
+++ b/src/test/isolation/expected/merge-update.out
@@ -37,13 +37,22 @@ step merge2a:
   WHEN NOT MATCHED THEN
 	INSERT VALUES (s.key, s.val)
   WHEN MATCHED THEN
-	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
+  WHEN NOT MATCHED BY SOURCE THEN
+	UPDATE set key = t.key + 1, val = t.val || ' source not matched by merge2a'
+  RETURNING merge_action(), t.*;
+
+merge_action|key|val                                                   
+------------+---+------------------------------------------------------
+UPDATE      |  3|setup1 updated by merge1 source not matched by merge2a
+INSERT      |  1|merge2a                                               
+(2 rows)
 
 step select2: SELECT * FROM target;
-key|val                     
----+------------------------
-  2|setup1 updated by merge1
-  1|merge2a                 
+key|val                                                   
+---+------------------------------------------------------
+  3|setup1 updated by merge1 source not matched by merge2a
+  1|merge2a                                               
 (2 rows)
 
 step c2: COMMIT;
@@ -86,15 +95,24 @@ step merge2a:
   WHEN NOT MATCHED THEN
 	INSERT VALUES (s.key, s.val)
   WHEN MATCHED THEN
-	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
+  WHEN NOT MATCHED BY SOURCE THEN
+	UPDATE set key = t.key + 1, val = t.val || ' source not matched by merge2a'
+  RETURNING merge_action(), t.*;
  <waiting ...>
 step c1: COMMIT;
 step merge2a: <... completed>
+merge_action|key|val                                                   
+------------+---+------------------------------------------------------
+UPDATE      |  3|setup1 updated by merge1 source not matched by merge2a
+INSERT      |  1|merge2a                                               
+(2 rows)
+
 step select2: SELECT * FROM target;
-key|val                     
----+------------------------
-  2|setup1 updated by merge1
-  1|merge2a                 
+key|val                                                   
+---+------------------------------------------------------
+  3|setup1 updated by merge1 source not matched by merge2a
+  1|merge2a                                               
 (2 rows)
 
 step c2: COMMIT;
@@ -116,10 +134,18 @@ step merge2a:
   WHEN NOT MATCHED THEN
 	INSERT VALUES (s.key, s.val)
   WHEN MATCHED THEN
-	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
+  WHEN NOT MATCHED BY SOURCE THEN
+	UPDATE set key = t.key + 1, val = t.val || ' source not matched by merge2a'
+  RETURNING merge_action(), t.*;
  <waiting ...>
 step a1: ABORT;
 step merge2a: <... completed>
+merge_action|key|val                      
+------------+---+-------------------------
+UPDATE      |  2|setup1 updated by merge2a
+(1 row)
+
 step select2: SELECT * FROM target;
 key|val                      
 ---+-------------------------
@@ -205,15 +231,24 @@ step pa_merge2a:
   WHEN NOT MATCHED THEN
 	INSERT VALUES (s.key, s.val)
   WHEN MATCHED THEN
-	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
+  WHEN NOT MATCHED BY SOURCE THEN
+	UPDATE set key = t.key + 1, val = t.val || ' source not matched by pa_merge2a'
+  RETURNING merge_action(), t.*;
  <waiting ...>
 step c1: COMMIT;
 step pa_merge2a: <... completed>
+merge_action|key|val                                               
+------------+---+--------------------------------------------------
+UPDATE      |  2|initial updated by pa_merge1 updated by pa_merge2a
+UPDATE      |  3|initial source not matched by pa_merge2a          
+(2 rows)
+
 step pa_select2: SELECT * FROM pa_target;
 key|val                                               
 ---+--------------------------------------------------
-  2|initial                                           
   2|initial updated by pa_merge1 updated by pa_merge2a
+  3|initial source not matched by pa_merge2a          
 (2 rows)
 
 step c2: COMMIT;
@@ -235,7 +270,10 @@ step pa_merge2a:
   WHEN NOT MATCHED THEN
 	INSERT VALUES (s.key, s.val)
   WHEN MATCHED THEN
-	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
+  WHEN NOT MATCHED BY SOURCE THEN
+	UPDATE set key = t.key + 1, val = t.val || ' source not matched by pa_merge2a'
+  RETURNING merge_action(), t.*;
  <waiting ...>
 step c1: COMMIT;
 step pa_merge2a: <... completed>
@@ -262,14 +300,24 @@ step pa_merge2a:
   WHEN NOT MATCHED THEN
 	INSERT VALUES (s.key, s.val)
   WHEN MATCHED THEN
-	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
+  WHEN NOT MATCHED BY SOURCE THEN
+	UPDATE set key = t.key + 1, val = t.val || ' source not matched by pa_merge2a'
+  RETURNING merge_action(), t.*;
+
+merge_action|key|val                                                          
+------------+---+-------------------------------------------------------------
+UPDATE      |  3|initial source not matched by pa_merge2a                     
+UPDATE      |  3|initial updated by pa_merge2 source not matched by pa_merge2a
+INSERT      |  1|pa_merge2a                                                   
+(3 rows)
 
 step pa_select2: SELECT * FROM pa_target;
-key|val                         
----+----------------------------
-  1|pa_merge2a                  
-  2|initial                     
-  2|initial updated by pa_merge2
+key|val                                                          
+---+-------------------------------------------------------------
+  1|pa_merge2a                                                   
+  3|initial source not matched by pa_merge2a                     
+  3|initial updated by pa_merge2 source not matched by pa_merge2a
 (3 rows)
 
 step c2: COMMIT;
diff --git a/src/test/isolation/specs/merge-update.spec b/src/test/isolation/specs/merge-update.spec
index 3ccd4664498..a33dcdba537 100644
--- a/src/test/isolation/specs/merge-update.spec
+++ b/src/test/isolation/specs/merge-update.spec
@@ -92,7 +92,10 @@ step "merge2a"
   WHEN NOT MATCHED THEN
 	INSERT VALUES (s.key, s.val)
   WHEN MATCHED THEN
-	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
+  WHEN NOT MATCHED BY SOURCE THEN
+	UPDATE set key = t.key + 1, val = t.val || ' source not matched by merge2a'
+  RETURNING merge_action(), t.*;
 }
 step "merge2b"
 {
@@ -122,7 +125,10 @@ step "pa_merge2a"
   WHEN NOT MATCHED THEN
 	INSERT VALUES (s.key, s.val)
   WHEN MATCHED THEN
-	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val
+  WHEN NOT MATCHED BY SOURCE THEN
+	UPDATE set key = t.key + 1, val = t.val || ' source not matched by pa_merge2a'
+  RETURNING merge_action(), t.*;
 }
 # MERGE proceeds only if 'val' unchanged
 step "pa_merge2b_when"