From 5349cab98943c9b705d3bea17afe358b2ce28c9e Mon Sep 17 00:00:00 2001
From: Thomas Munro <thomas.munro@gmail.com>
Date: Thu, 21 May 2020 13:35:55 +1200
Subject: [PATCH] Add new SERIALIZABLE vs UCV test.

Discussion: https://postgr.es/m/CAMTXbE-sq9JoihvG-ccC70jpjMr%2BDWmnYUj%2BVdnFRFSRuaaLZQ%40mail.gmail.com
---
 .../expected/read-write-unique-5.out          | 35 ++++++++++++
 .../isolation/specs/read-write-unique-5.spec  | 55 +++++++++++++++++++
 2 files changed, 90 insertions(+)
 create mode 100644 src/test/isolation/expected/read-write-unique-5.out
 create mode 100644 src/test/isolation/specs/read-write-unique-5.spec

diff --git a/src/test/isolation/expected/read-write-unique-5.out b/src/test/isolation/expected/read-write-unique-5.out
new file mode 100644
index 0000000000..125c378b66
--- /dev/null
+++ b/src/test/isolation/expected/read-write-unique-5.out
@@ -0,0 +1,35 @@
+Parsed test spec with 2 sessions
+
+starting permutation: r1 r2 w1 w2 c1 c2
+step r1: 
+  SELECT version FROM t
+   WHERE NOT EXISTS (SELECT 1 FROM t t2
+                      WHERE t.item_id = t2.item_id
+                        AND t.created_at < t2.created_at)
+     AND item_id = 10;
+
+version        
+
+2              
+step r2: 
+  SELECT version FROM t
+   WHERE NOT EXISTS (SELECT 1 FROM t t2
+                      WHERE t.item_id = t2.item_id
+                        AND t.created_at < t2.created_at)
+     AND item_id = 10;
+
+version        
+
+2              
+step w1: 
+  INSERT INTO t (item_id, version, created_at)
+  VALUES (10, 3, now());
+
+step w2: 
+  INSERT INTO t (item_id, version, created_at)
+  VALUES (10, 3, now());
+ <waiting ...>
+step c1: COMMIT;
+step w2: <... completed>
+error in steps c1 w2: ERROR:  duplicate key value violates unique constraint "t_item_id_version_key"
+step c2: COMMIT;
diff --git a/src/test/isolation/specs/read-write-unique-5.spec b/src/test/isolation/specs/read-write-unique-5.spec
new file mode 100644
index 0000000000..f8a7cbe2b3
--- /dev/null
+++ b/src/test/isolation/specs/read-write-unique-5.spec
@@ -0,0 +1,55 @@
+# Read-write-unique test.
+# From bug report:
+# https://postgr.es/m/CAGPCyEZG76zjv7S31v_xPeLNRuzj-m%3DY2GOY7PEzu7vhB%3DyQog%40mail.gmail.com
+
+setup
+{
+CREATE TABLE t (
+    item_id INT NOT NULL,
+    version INT NOT NULL,
+    created_at TIMESTAMPTZ NOT NULL,
+    UNIQUE (item_id, version),
+    UNIQUE (item_id, created_at)
+);
+INSERT INTO t (item_id, version, created_at) VALUES
+    (10, 1, now() - INTERVAL '2 SECOND'),
+    (10, 2, now() - INTERVAL '1 SECOND');
+}
+
+teardown
+{
+  DROP TABLE t;
+}
+
+session "s1"
+setup { BEGIN ISOLATION LEVEL SERIALIZABLE; }
+step "r1" { 
+  SELECT version FROM t
+   WHERE NOT EXISTS (SELECT 1 FROM t t2
+                      WHERE t.item_id = t2.item_id
+                        AND t.created_at < t2.created_at)
+     AND item_id = 10;
+}
+step "w1" {
+  INSERT INTO t (item_id, version, created_at)
+  VALUES (10, 3, now());
+}
+step "c1" { COMMIT; }
+
+session "s2"
+setup { BEGIN ISOLATION LEVEL SERIALIZABLE; }
+step "r2" { 
+  SELECT version FROM t
+   WHERE NOT EXISTS (SELECT 1 FROM t t2
+                      WHERE t.item_id = t2.item_id
+                        AND t.created_at < t2.created_at)
+     AND item_id = 10;
+}
+step "w2" {
+  INSERT INTO t (item_id, version, created_at)
+  VALUES (10, 3, now());
+}
+step "c2" { COMMIT; }
+
+# XXX This should ideally detect serialization failure before UCV
+permutation "r1" "r2" "w1" "w2" "c1" "c2"
-- 
2.20.1