Add support for MERGE SQL command

MERGE performs actions that modify rows in the target table using a
source table or query. MERGE provides a single SQL statement that can
conditionally INSERT/UPDATE/DELETE rows -- a task that would otherwise
require multiple PL statements.  For example,

MERGE INTO target AS 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
  DELETE
WHEN NOT MATCHED AND s.delta > 0 THEN
  INSERT VALUES (s.sid, s.delta)
WHEN NOT MATCHED THEN
  DO NOTHING;

MERGE works with regular tables, partitioned tables and inheritance
hierarchies, including column and row security enforcement, as well as
support for row and statement triggers and transition tables therein.

MERGE is optimized for OLTP and is parameterizable, though also useful
for large scale ETL/ELT. MERGE is not intended to be used in preference
to existing single SQL commands for INSERT, UPDATE or DELETE since there
is some overhead.  MERGE can be used from PL/pgSQL.

MERGE does not support targetting updatable views or foreign tables, and
RETURNING clauses are not allowed either.  These limitations are likely
fixable with sufficient effort.  Rewrite rules are also not supported,
but it's not clear that we'd want to support them.

Author: Pavan Deolasee <pavan.deolasee@gmail.com>
Author: Álvaro Herrera <alvherre@alvh.no-ip.org>
Author: Amit Langote <amitlangote09@gmail.com>
Author: Simon Riggs <simon.riggs@enterprisedb.com>
Reviewed-by: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Reviewed-by: Andres Freund <andres@anarazel.de> (earlier versions)
Reviewed-by: Peter Geoghegan <pg@bowt.ie> (earlier versions)
Reviewed-by: Robert Haas <robertmhaas@gmail.com> (earlier versions)
Reviewed-by: Japin Li <japinli@hotmail.com>
Reviewed-by: Justin Pryzby <pryzby@telsasoft.com>
Reviewed-by: Tomas Vondra <tomas.vondra@enterprisedb.com>
Reviewed-by: Zhihong Yu <zyu@yugabyte.com>
Discussion: https://postgr.es/m/CANP8+jKitBSrB7oTgT9CY2i1ObfOt36z0XMraQc+Xrz8QB0nXA@mail.gmail.com
Discussion: https://postgr.es/m/CAH2-WzkJdBuxj9PO=2QaO9-3h3xGbQPZ34kJH=HukRekwM-GZg@mail.gmail.com
Discussion: https://postgr.es/m/20201231134736.GA25392@alvherre.pgsql

9 files changed, 979 insertions, 0 deletions

diff --git a/src/test/isolation/expected/merge-delete.out b/src/test/isolation/expected/merge-delete.out
new file mode 100644
index 0000000000..b2befa8e16
--- /dev/null
+++ b/src/test/isolation/expected/merge-delete.out
@@ -0,0 +1,117 @@
+Parsed test spec with 2 sessions
+
+starting permutation: delete c1 select2 c2
+step delete: DELETE FROM target t WHERE t.key = 1;
+step c1: COMMIT;
+step select2: SELECT * FROM target;
+key|val
+---+---
+(0 rows)
+
+step c2: COMMIT;
+
+starting permutation: merge_delete c1 select2 c2
+step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
+step c1: COMMIT;
+step select2: SELECT * FROM target;
+key|val
+---+---
+(0 rows)
+
+step c2: COMMIT;
+
+starting permutation: delete c1 update1 select2 c2
+step delete: DELETE FROM target t WHERE t.key = 1;
+step c1: COMMIT;
+step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1;
+step select2: SELECT * FROM target;
+key|val
+---+---
+(0 rows)
+
+step c2: COMMIT;
+
+starting permutation: merge_delete c1 update1 select2 c2
+step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
+step c1: COMMIT;
+step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1;
+step select2: SELECT * FROM target;
+key|val
+---+---
+(0 rows)
+
+step c2: COMMIT;
+
+starting permutation: delete c1 merge2 select2 c2
+step delete: DELETE FROM target t WHERE t.key = 1;
+step c1: COMMIT;
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key 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;
+step select2: SELECT * FROM target;
+key|val    
+---+-------
+  1|merge2a
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: merge_delete c1 merge2 select2 c2
+step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
+step c1: COMMIT;
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key 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;
+step select2: SELECT * FROM target;
+key|val    
+---+-------
+  1|merge2a
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete update1 c1 select2 c2
+step delete: DELETE FROM target t WHERE t.key = 1;
+step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1; <waiting ...>
+step c1: COMMIT;
+step update1: <... completed>
+step select2: SELECT * FROM target;
+key|val
+---+---
+(0 rows)
+
+step c2: COMMIT;
+
+starting permutation: merge_delete update1 c1 select2 c2
+step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
+step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1; <waiting ...>
+step c1: COMMIT;
+step update1: <... completed>
+step select2: SELECT * FROM target;
+key|val
+---+---
+(0 rows)
+
+step c2: COMMIT;
+
+starting permutation: delete merge2 c1 select2 c2
+step delete: DELETE FROM target t WHERE t.key = 1;
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key 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; <waiting ...>
+step c1: COMMIT;
+step merge2: <... completed>
+step select2: SELECT * FROM target;
+key|val    
+---+-------
+  1|merge2a
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: merge_delete merge2 c1 select2 c2
+step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key 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; <waiting ...>
+step c1: COMMIT;
+step merge2: <... completed>
+step select2: SELECT * FROM target;
+key|val    
+---+-------
+  1|merge2a
+(1 row)
+
+step c2: COMMIT;
diff --git a/src/test/isolation/expected/merge-insert-update.out b/src/test/isolation/expected/merge-insert-update.out
new file mode 100644
index 0000000000..ee8b3c4e9f
--- /dev/null
+++ b/src/test/isolation/expected/merge-insert-update.out
@@ -0,0 +1,94 @@
+Parsed test spec with 2 sessions
+
+starting permutation: merge1 c1 select2 c2
+step merge1: MERGE INTO target t USING (SELECT 1 as key, 'merge1' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge1';
+step c1: COMMIT;
+step select2: SELECT * FROM target;
+key|val   
+---+------
+  1|merge1
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: merge1 c1 merge2 select2 c2
+step merge1: MERGE INTO target t USING (SELECT 1 as key, 'merge1' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge1';
+step c1: COMMIT;
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge2';
+step select2: SELECT * FROM target;
+key|val                     
+---+------------------------
+  1|merge1 updated by merge2
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: insert1 merge2 c1 select2 c2
+step insert1: INSERT INTO target VALUES (1, 'insert1');
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge2'; <waiting ...>
+step c1: COMMIT;
+step merge2: <... completed>
+ERROR:  duplicate key value violates unique constraint "target_pkey"
+step select2: SELECT * FROM target;
+ERROR:  current transaction is aborted, commands ignored until end of transaction block
+step c2: COMMIT;
+
+starting permutation: merge1 merge2 c1 select2 c2
+step merge1: MERGE INTO target t USING (SELECT 1 as key, 'merge1' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge1';
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge2'; <waiting ...>
+step c1: COMMIT;
+step merge2: <... completed>
+ERROR:  duplicate key value violates unique constraint "target_pkey"
+step select2: SELECT * FROM target;
+ERROR:  current transaction is aborted, commands ignored until end of transaction block
+step c2: COMMIT;
+
+starting permutation: merge1 merge2 a1 select2 c2
+step merge1: MERGE INTO target t USING (SELECT 1 as key, 'merge1' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge1';
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge2'; <waiting ...>
+step a1: ABORT;
+step merge2: <... completed>
+step select2: SELECT * FROM target;
+key|val   
+---+------
+  1|merge2
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete1 insert1 c1 merge2 select2 c2
+step delete1: DELETE FROM target WHERE key = 1;
+step insert1: INSERT INTO target VALUES (1, 'insert1');
+step c1: COMMIT;
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge2';
+step select2: SELECT * FROM target;
+key|val                      
+---+-------------------------
+  1|insert1 updated by merge2
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete1 insert1 merge2 c1 select2 c2
+step delete1: DELETE FROM target WHERE key = 1;
+step insert1: INSERT INTO target VALUES (1, 'insert1');
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge2'; <waiting ...>
+step c1: COMMIT;
+step merge2: <... completed>
+ERROR:  duplicate key value violates unique constraint "target_pkey"
+step select2: SELECT * FROM target;
+ERROR:  current transaction is aborted, commands ignored until end of transaction block
+step c2: COMMIT;
+
+starting permutation: delete1 insert1 merge2i c1 select2 c2
+step delete1: DELETE FROM target WHERE key = 1;
+step insert1: INSERT INTO target VALUES (1, 'insert1');
+step merge2i: MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge2';
+step c1: COMMIT;
+step select2: SELECT * FROM target;
+key|val    
+---+-------
+  1|insert1
+(1 row)
+
+step c2: COMMIT;
diff --git a/src/test/isolation/expected/merge-match-recheck.out b/src/test/isolation/expected/merge-match-recheck.out
new file mode 100644
index 0000000000..8183f52ce0
--- /dev/null
+++ b/src/test/isolation/expected/merge-match-recheck.out
@@ -0,0 +1,116 @@
+Parsed test spec with 2 sessions
+
+starting permutation: update1 merge_status c2 select1 c1
+step update1: UPDATE target t SET balance = balance + 10, val = t.val || ' updated by update1' WHERE t.key = 1;
+step merge_status: 
+  MERGE INTO target t
+  USING (SELECT 1 as key) s
+  ON s.key = t.key
+  WHEN MATCHED AND status = 's1' THEN
+	UPDATE SET status = 's2', val = t.val || ' when1'
+  WHEN MATCHED AND status = 's2' THEN
+	UPDATE SET status = 's3', val = t.val || ' when2'
+  WHEN MATCHED AND status = 's3' THEN
+	UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+step merge_status: <... completed>
+step select1: SELECT * FROM target;
+key|balance|status|val                           
+---+-------+------+------------------------------
+  1|    170|s2    |setup updated by update1 when1
+(1 row)
+
+step c1: COMMIT;
+
+starting permutation: update2 merge_status c2 select1 c1
+step update2: UPDATE target t SET status = 's2', val = t.val || ' updated by update2' WHERE t.key = 1;
+step merge_status: 
+  MERGE INTO target t
+  USING (SELECT 1 as key) s
+  ON s.key = t.key
+  WHEN MATCHED AND status = 's1' THEN
+	UPDATE SET status = 's2', val = t.val || ' when1'
+  WHEN MATCHED AND status = 's2' THEN
+	UPDATE SET status = 's3', val = t.val || ' when2'
+  WHEN MATCHED AND status = 's3' THEN
+	UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+step merge_status: <... completed>
+step select1: SELECT * FROM target;
+key|balance|status|val                           
+---+-------+------+------------------------------
+  1|    160|s3    |setup updated by update2 when2
+(1 row)
+
+step c1: COMMIT;
+
+starting permutation: update3 merge_status c2 select1 c1
+step update3: UPDATE target t SET status = 's3', val = t.val || ' updated by update3' WHERE t.key = 1;
+step merge_status: 
+  MERGE INTO target t
+  USING (SELECT 1 as key) s
+  ON s.key = t.key
+  WHEN MATCHED AND status = 's1' THEN
+	UPDATE SET status = 's2', val = t.val || ' when1'
+  WHEN MATCHED AND status = 's2' THEN
+	UPDATE SET status = 's3', val = t.val || ' when2'
+  WHEN MATCHED AND status = 's3' THEN
+	UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+step merge_status: <... completed>
+step select1: SELECT * FROM target;
+key|balance|status|val                           
+---+-------+------+------------------------------
+  1|    160|s4    |setup updated by update3 when3
+(1 row)
+
+step c1: COMMIT;
+
+starting permutation: update5 merge_status c2 select1 c1
+step update5: UPDATE target t SET status = 's5', val = t.val || ' updated by update5' WHERE t.key = 1;
+step merge_status: 
+  MERGE INTO target t
+  USING (SELECT 1 as key) s
+  ON s.key = t.key
+  WHEN MATCHED AND status = 's1' THEN
+	UPDATE SET status = 's2', val = t.val || ' when1'
+  WHEN MATCHED AND status = 's2' THEN
+	UPDATE SET status = 's3', val = t.val || ' when2'
+  WHEN MATCHED AND status = 's3' THEN
+	UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+step merge_status: <... completed>
+step select1: SELECT * FROM target;
+key|balance|status|val                     
+---+-------+------+------------------------
+  1|    160|s5    |setup updated by update5
+(1 row)
+
+step c1: COMMIT;
+
+starting permutation: update_bal1 merge_bal c2 select1 c1
+step update_bal1: UPDATE target t SET balance = 50, val = t.val || ' updated by update_bal1' WHERE t.key = 1;
+step merge_bal: 
+  MERGE INTO target t
+  USING (SELECT 1 as key) s
+  ON s.key = t.key
+  WHEN MATCHED AND balance < 100 THEN
+	UPDATE SET balance = balance * 2, val = t.val || ' when1'
+  WHEN MATCHED AND balance < 200 THEN
+	UPDATE SET balance = balance * 4, val = t.val || ' when2'
+  WHEN MATCHED AND balance < 300 THEN
+	UPDATE SET balance = balance * 8, val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+step merge_bal: <... completed>
+step select1: SELECT * FROM target;
+key|balance|status|val                               
+---+-------+------+----------------------------------
+  1|    100|s1    |setup updated by update_bal1 when1
+(1 row)
+
+step c1: COMMIT;
diff --git a/src/test/isolation/expected/merge-update.out b/src/test/isolation/expected/merge-update.out
new file mode 100644
index 0000000000..55b1f908fd
--- /dev/null
+++ b/src/test/isolation/expected/merge-update.out
@@ -0,0 +1,314 @@
+Parsed test spec with 2 sessions
+
+starting permutation: merge1 c1 select2 c2
+step merge1: 
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge1' as val) s
+  ON s.key = t.key
+  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;
+
+step c1: COMMIT;
+step select2: SELECT * FROM target;
+key|val                     
+---+------------------------
+  2|setup1 updated by merge1
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: merge1 c1 merge2a select2 c2
+step merge1: 
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge1' as val) s
+  ON s.key = t.key
+  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;
+
+step c1: COMMIT;
+step merge2a: 
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge2a' as val) s
+  ON s.key = t.key
+  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;
+
+step select2: SELECT * FROM target;
+key|val                     
+---+------------------------
+  2|setup1 updated by merge1
+  1|merge2a                 
+(2 rows)
+
+step c2: COMMIT;
+
+starting permutation: merge1 merge2a c1 select2 c2
+step merge1: 
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge1' as val) s
+  ON s.key = t.key
+  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;
+
+step merge2a: 
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge2a' as val) s
+  ON s.key = t.key
+  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;
+ <waiting ...>
+step c1: COMMIT;
+step merge2a: <... completed>
+step select2: SELECT * FROM target;
+key|val                     
+---+------------------------
+  2|setup1 updated by merge1
+  1|merge2a                 
+(2 rows)
+
+step c2: COMMIT;
+
+starting permutation: merge1 merge2a a1 select2 c2
+step merge1: 
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge1' as val) s
+  ON s.key = t.key
+  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;
+
+step merge2a: 
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge2a' as val) s
+  ON s.key = t.key
+  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;
+ <waiting ...>
+step a1: ABORT;
+step merge2a: <... completed>
+step select2: SELECT * FROM target;
+key|val                      
+---+-------------------------
+  2|setup1 updated by merge2a
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: merge1 merge2b c1 select2 c2
+step merge1: 
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge1' as val) s
+  ON s.key = t.key
+  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;
+
+step merge2b: 
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge2b' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED AND t.key < 2 THEN
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+ <waiting ...>
+step c1: COMMIT;
+step merge2b: <... completed>
+step select2: SELECT * FROM target;
+key|val                     
+---+------------------------
+  2|setup1 updated by merge1
+  1|merge2b                 
+(2 rows)
+
+step c2: COMMIT;
+
+starting permutation: merge1 merge2c c1 select2 c2
+step merge1: 
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge1' as val) s
+  ON s.key = t.key
+  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;
+
+step merge2c: 
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge2c' as val) s
+  ON s.key = t.key AND t.key < 2
+  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;
+ <waiting ...>
+step c1: COMMIT;
+step merge2c: <... completed>
+step select2: SELECT * FROM target;
+key|val                     
+---+------------------------
+  2|setup1 updated by merge1
+  1|merge2c                 
+(2 rows)
+
+step c2: COMMIT;
+
+starting permutation: pa_merge1 pa_merge2a c1 pa_select2 c2
+step pa_merge1: 
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge1' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+    UPDATE set val = t.val || ' updated by ' || s.val;
+
+step pa_merge2a: 
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge2a' as val) s
+  ON s.key = t.key
+  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;
+ <waiting ...>
+step c1: COMMIT;
+step pa_merge2a: <... completed>
+step pa_select2: SELECT * FROM pa_target;
+key|val                                               
+---+--------------------------------------------------
+  2|initial                                           
+  2|initial updated by pa_merge1 updated by pa_merge2a
+(2 rows)
+
+step c2: COMMIT;
+
+starting permutation: pa_merge2 pa_merge2a c1 pa_select2 c2
+step pa_merge2: 
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge2' as val) s
+  ON s.key = t.key
+  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;
+
+step pa_merge2a: 
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge2a' as val) s
+  ON s.key = t.key
+  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;
+ <waiting ...>
+step c1: COMMIT;
+step pa_merge2a: <... completed>
+ERROR:  tuple to be locked was already moved to another partition due to concurrent update
+step pa_select2: SELECT * FROM pa_target;
+ERROR:  current transaction is aborted, commands ignored until end of transaction block
+step c2: COMMIT;
+
+starting permutation: pa_merge2 c1 pa_merge2a pa_select2 c2
+step pa_merge2: 
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge2' as val) s
+  ON s.key = t.key
+  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;
+
+step c1: COMMIT;
+step pa_merge2a: 
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge2a' as val) s
+  ON s.key = t.key
+  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;
+
+step pa_select2: SELECT * FROM pa_target;
+key|val                         
+---+----------------------------
+  1|pa_merge2a                  
+  2|initial                     
+  2|initial updated by pa_merge2
+(3 rows)
+
+step c2: COMMIT;
+
+starting permutation: pa_merge3 pa_merge2b_when c1 pa_select2 c2
+step pa_merge3: 
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge2' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+    UPDATE set val = 'prefix ' || t.val;
+
+step pa_merge2b_when: 
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge2b_when' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED AND t.val like 'initial%' THEN
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+ <waiting ...>
+step c1: COMMIT;
+step pa_merge2b_when: <... completed>
+step pa_select2: SELECT * FROM pa_target;
+key|val           
+---+--------------
+  1|prefix initial
+  2|initial       
+(2 rows)
+
+step c2: COMMIT;
+
+starting permutation: pa_merge1 pa_merge2b_when c1 pa_select2 c2
+step pa_merge1: 
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge1' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+    UPDATE set val = t.val || ' updated by ' || s.val;
+
+step pa_merge2b_when: 
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge2b_when' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED AND t.val like 'initial%' THEN
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+ <waiting ...>
+step c1: COMMIT;
+step pa_merge2b_when: <... completed>
+step pa_select2: SELECT * FROM pa_target;
+key|val                                                    
+---+-------------------------------------------------------
+  2|initial                                                
+  2|initial updated by pa_merge1 updated by pa_merge2b_when
+(2 rows)
+
+step c2: COMMIT;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 8e87098150..00749a40bd 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -45,6 +45,10 @@ test: insert-conflict-do-update
 test: insert-conflict-do-update-2
 test: insert-conflict-do-update-3
 test: insert-conflict-specconflict
+test: merge-insert-update
+test: merge-delete
+test: merge-update
+test: merge-match-recheck
 test: delete-abort-savept
 test: delete-abort-savept-2
 test: aborted-keyrevoke
diff --git a/src/test/isolation/specs/merge-delete.spec b/src/test/isolation/specs/merge-delete.spec
new file mode 100644
index 0000000000..0e7053270e
--- /dev/null
+++ b/src/test/isolation/specs/merge-delete.spec
@@ -0,0 +1,50 @@
+# MERGE DELETE
+#
+# This test looks at the interactions involving concurrent deletes
+# comparing the behavior of MERGE, DELETE and UPDATE
+
+setup
+{
+  CREATE TABLE target (key int primary key, val text);
+  INSERT INTO target VALUES (1, 'setup1');
+}
+
+teardown
+{
+  DROP TABLE target;
+}
+
+session "s1"
+setup
+{
+  BEGIN ISOLATION LEVEL READ COMMITTED;
+}
+step "delete" { DELETE FROM target t WHERE t.key = 1; }
+step "merge_delete" { MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE; }
+step "c1" { COMMIT; }
+
+session "s2"
+setup
+{
+  BEGIN ISOLATION LEVEL READ COMMITTED;
+}
+step "update1" { UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1; }
+step "merge2" { MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key 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; }
+step "select2" { SELECT * FROM target; }
+step "c2" { COMMIT; }
+
+# Basic effects
+permutation "delete" "c1" "select2" "c2"
+permutation "merge_delete" "c1" "select2" "c2"
+
+# One after the other, no concurrency
+permutation "delete" "c1" "update1" "select2" "c2"
+permutation "merge_delete" "c1" "update1" "select2" "c2"
+permutation "delete" "c1" "merge2" "select2" "c2"
+permutation "merge_delete" "c1" "merge2" "select2" "c2"
+
+# Now with concurrency
+permutation "delete" "update1" "c1" "select2" "c2"
+permutation "merge_delete" "update1" "c1" "select2" "c2"
+permutation "delete" "merge2" "c1" "select2" "c2"
+permutation "merge_delete" "merge2" "c1" "select2" "c2"
diff --git a/src/test/isolation/specs/merge-insert-update.spec b/src/test/isolation/specs/merge-insert-update.spec
new file mode 100644
index 0000000000..1bf1ed461d
--- /dev/null
+++ b/src/test/isolation/specs/merge-insert-update.spec
@@ -0,0 +1,51 @@
+# MERGE INSERT UPDATE
+#
+# This looks at how we handle concurrent INSERTs, illustrating how the
+# behavior differs from INSERT ... ON CONFLICT
+
+setup
+{
+  CREATE TABLE target (key int primary key, val text);
+}
+
+teardown
+{
+  DROP TABLE target;
+}
+
+session "s1"
+setup
+{
+  BEGIN ISOLATION LEVEL READ COMMITTED;
+}
+step "merge1" { MERGE INTO target t USING (SELECT 1 as key, 'merge1' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge1'; }
+step "delete1" { DELETE FROM target WHERE key = 1; }
+step "insert1" { INSERT INTO target VALUES (1, 'insert1'); }
+step "c1" { COMMIT; }
+step "a1" { ABORT; }
+
+session "s2"
+setup
+{
+  BEGIN ISOLATION LEVEL READ COMMITTED;
+}
+step "merge2" { MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge2'; }
+
+step "merge2i" { MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge2'; }
+
+step "select2" { SELECT * FROM target; }
+step "c2" { COMMIT; }
+
+# Basic effects
+permutation "merge1" "c1" "select2" "c2"
+permutation "merge1" "c1" "merge2" "select2" "c2"
+
+# check concurrent inserts
+permutation "insert1" "merge2" "c1" "select2" "c2"
+permutation "merge1" "merge2" "c1" "select2" "c2"
+permutation "merge1" "merge2" "a1" "select2" "c2"
+
+# check how we handle when visible row has been concurrently deleted, then same key re-inserted
+permutation "delete1" "insert1" "c1" "merge2" "select2" "c2"
+permutation "delete1" "insert1" "merge2" "c1" "select2" "c2"
+permutation "delete1" "insert1" "merge2i" "c1" "select2" "c2"
diff --git a/src/test/isolation/specs/merge-match-recheck.spec b/src/test/isolation/specs/merge-match-recheck.spec
new file mode 100644
index 0000000000..d56400a6a2
--- /dev/null
+++ b/src/test/isolation/specs/merge-match-recheck.spec
@@ -0,0 +1,77 @@
+# MERGE MATCHED RECHECK
+#
+# This test looks at what happens when we have complex
+# WHEN MATCHED AND conditions and a concurrent UPDATE causes a
+# recheck of the AND condition on the new row
+
+setup
+{
+  CREATE TABLE target (key int primary key, balance integer, status text, val text);
+  INSERT INTO target VALUES (1, 160, 's1', 'setup');
+}
+
+teardown
+{
+  DROP TABLE target;
+}
+
+session "s1"
+setup
+{
+  BEGIN ISOLATION LEVEL READ COMMITTED;
+}
+step "merge_status"
+{
+  MERGE INTO target t
+  USING (SELECT 1 as key) s
+  ON s.key = t.key
+  WHEN MATCHED AND status = 's1' THEN
+	UPDATE SET status = 's2', val = t.val || ' when1'
+  WHEN MATCHED AND status = 's2' THEN
+	UPDATE SET status = 's3', val = t.val || ' when2'
+  WHEN MATCHED AND status = 's3' THEN
+	UPDATE SET status = 's4', val = t.val || ' when3';
+}
+
+step "merge_bal"
+{
+  MERGE INTO target t
+  USING (SELECT 1 as key) s
+  ON s.key = t.key
+  WHEN MATCHED AND balance < 100 THEN
+	UPDATE SET balance = balance * 2, val = t.val || ' when1'
+  WHEN MATCHED AND balance < 200 THEN
+	UPDATE SET balance = balance * 4, val = t.val || ' when2'
+  WHEN MATCHED AND balance < 300 THEN
+	UPDATE SET balance = balance * 8, val = t.val || ' when3';
+}
+
+step "select1" { SELECT * FROM target; }
+step "c1" { COMMIT; }
+
+session "s2"
+setup
+{
+  BEGIN ISOLATION LEVEL READ COMMITTED;
+}
+step "update1" { UPDATE target t SET balance = balance + 10, val = t.val || ' updated by update1' WHERE t.key = 1; }
+step "update2" { UPDATE target t SET status = 's2', val = t.val || ' updated by update2' WHERE t.key = 1; }
+step "update3" { UPDATE target t SET status = 's3', val = t.val || ' updated by update3' WHERE t.key = 1; }
+step "update5" { UPDATE target t SET status = 's5', val = t.val || ' updated by update5' WHERE t.key = 1; }
+step "update_bal1" { UPDATE target t SET balance = 50, val = t.val || ' updated by update_bal1' WHERE t.key = 1; }
+step "c2" { COMMIT; }
+
+# merge_status sees concurrently updated row and rechecks WHEN conditions, but recheck passes and final status = 's2'
+permutation "update1" "merge_status" "c2" "select1" "c1"
+
+# merge_status sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final status = 's3' not 's2'
+permutation "update2" "merge_status" "c2" "select1" "c1"
+
+# merge_status sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final status = 's4' not 's2'
+permutation "update3" "merge_status" "c2" "select1" "c1"
+
+# merge_status sees concurrently updated row and rechecks WHEN conditions, recheck fails, but we skip update and MERGE does nothing
+permutation "update5" "merge_status" "c2" "select1" "c1"
+
+# merge_bal sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final balance = 100 not 640
+permutation "update_bal1" "merge_bal" "c2" "select1" "c1"
diff --git a/src/test/isolation/specs/merge-update.spec b/src/test/isolation/specs/merge-update.spec
new file mode 100644
index 0000000000..e8d01666fe
--- /dev/null
+++ b/src/test/isolation/specs/merge-update.spec
@@ -0,0 +1,156 @@
+# MERGE UPDATE
+#
+# This test exercises atypical cases
+# 1. UPDATEs of PKs that change the join in the ON clause
+# 2. UPDATEs with WHEN conditions that would fail after concurrent update
+# 3. UPDATEs with extra ON conditions that would fail after concurrent update
+
+setup
+{
+  CREATE TABLE target (key int primary key, val text);
+  INSERT INTO target VALUES (1, 'setup1');
+
+  CREATE TABLE pa_target (key integer, val text)
+	  PARTITION BY LIST (key);
+  CREATE TABLE part1 (key integer, val text);
+  CREATE TABLE part2 (val text, key integer);
+  CREATE TABLE part3 (key integer, val text);
+
+  ALTER TABLE pa_target ATTACH PARTITION part1 FOR VALUES IN (1,4);
+  ALTER TABLE pa_target ATTACH PARTITION part2 FOR VALUES IN (2,5,6);
+  ALTER TABLE pa_target ATTACH PARTITION part3 DEFAULT;
+
+  INSERT INTO pa_target VALUES (1, 'initial');
+  INSERT INTO pa_target VALUES (2, 'initial');
+}
+
+teardown
+{
+  DROP TABLE target;
+  DROP TABLE pa_target CASCADE;
+}
+
+session "s1"
+setup
+{
+  BEGIN ISOLATION LEVEL READ COMMITTED;
+}
+step "merge1"
+{
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge1' as val) s
+  ON s.key = t.key
+  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;
+}
+step "pa_merge1"
+{
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge1' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+    UPDATE set val = t.val || ' updated by ' || s.val;
+}
+step "pa_merge2"
+{
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge2' as val) s
+  ON s.key = t.key
+  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;
+}
+step "pa_merge3"
+{
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge2' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+    UPDATE set val = 'prefix ' || t.val;
+}
+step "c1" { COMMIT; }
+step "a1" { ABORT; }
+
+session "s2"
+setup
+{
+  BEGIN ISOLATION LEVEL READ COMMITTED;
+}
+step "merge2a"
+{
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge2a' as val) s
+  ON s.key = t.key
+  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;
+}
+step "merge2b"
+{
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge2b' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED AND t.key < 2 THEN
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+}
+step "merge2c"
+{
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge2c' as val) s
+  ON s.key = t.key AND t.key < 2
+  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;
+}
+step "pa_merge2a"
+{
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge2a' as val) s
+  ON s.key = t.key
+  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;
+}
+# MERGE proceeds only if 'val' unchanged
+step "pa_merge2b_when"
+{
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge2b_when' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED AND t.val like 'initial%' THEN
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+}
+step "select2" { SELECT * FROM target; }
+step "pa_select2" { SELECT * FROM pa_target; }
+step "c2" { COMMIT; }
+
+# Basic effects
+permutation "merge1" "c1" "select2" "c2"
+
+# One after the other, no concurrency
+permutation "merge1" "c1" "merge2a" "select2" "c2"
+
+# Now with concurrency
+permutation "merge1" "merge2a" "c1" "select2" "c2"
+permutation "merge1" "merge2a" "a1" "select2" "c2"
+permutation "merge1" "merge2b" "c1" "select2" "c2"
+permutation "merge1" "merge2c" "c1" "select2" "c2"
+permutation "pa_merge1" "pa_merge2a" "c1" "pa_select2" "c2"
+permutation "pa_merge2" "pa_merge2a" "c1" "pa_select2" "c2" # fails
+permutation "pa_merge2" "c1" "pa_merge2a" "pa_select2" "c2" # succeeds
+permutation "pa_merge3" "pa_merge2b_when" "c1" "pa_select2" "c2" # WHEN not satisfied by updated tuple
+permutation "pa_merge1" "pa_merge2b_when" "c1" "pa_select2" "c2" # WHEN satisfied by updated tuple