TRIAL-on-conflict-do-update-wip.patch

From 36c4018ccae3be007bb1b5754d5e9eb59b2fe1bb Mon Sep 17 00:00:00 2001
From: Amul Sul <sulamul@gmail.com>
Date: Wed, 7 Mar 2018 15:31:41 +0530
Subject: [PATCH] Isolation trial on confict do update wip

---
 .../isolation/expected/partition-key-update-6.out  | 60 ++++++++++++++++++++++
 .../isolation/specs/partition-key-update-6.spec    | 44 ++++++++++++++++
 2 files changed, 104 insertions(+)
 create mode 100644 src/test/isolation/expected/partition-key-update-6.out
 create mode 100644 src/test/isolation/specs/partition-key-update-6.spec

diff --git a/src/test/isolation/expected/partition-key-update-6.out b/src/test/isolation/expected/partition-key-update-6.out
new file mode 100644
index 0000000000..7d4c7282c1
--- /dev/null
+++ b/src/test/isolation/expected/partition-key-update-6.out
@@ -0,0 +1,60 @@
+Parsed test spec with 3 sessions
+
+starting permutation: s1u s2insert s3insert s1c s2c s3c s3select
+step s1u: UPDATE foo SET a=2, b=b ||' -> moved by session-1' WHERE a=1;
+step s2insert: INSERT INTO foo1 VALUES(1, 'session-2 insert') ON CONFLICT (a) DO UPDATE set b = foo1.b || ' -> updated by session-2 insert'; <waiting ...>
+step s3insert: INSERT INTO foo2 VALUES(2, 'session-3 insert') ON CONFLICT (a) DO UPDATE set b = foo2.b || ' -> updated by session-3 insert'; <waiting ...>
+step s1c: COMMIT;
+step s2insert: <... completed>
+step s3insert: <... completed>
+step s2c: COMMIT;
+step s3c: COMMIT;
+step s3select: SELECT * FROM foo;
+a              b              
+
+1              session-2 insert
+2              initial tuple -> moved by session-1 -> updated by session-3 insert
+
+starting permutation: s1u s3insert s2insert s1c s3c s2c s3select
+step s1u: UPDATE foo SET a=2, b=b ||' -> moved by session-1' WHERE a=1;
+step s3insert: INSERT INTO foo2 VALUES(2, 'session-3 insert') ON CONFLICT (a) DO UPDATE set b = foo2.b || ' -> updated by session-3 insert'; <waiting ...>
+step s2insert: INSERT INTO foo1 VALUES(1, 'session-2 insert') ON CONFLICT (a) DO UPDATE set b = foo1.b || ' -> updated by session-2 insert'; <waiting ...>
+step s1c: COMMIT;
+step s3insert: <... completed>
+step s2insert: <... completed>
+step s3c: COMMIT;
+step s2c: COMMIT;
+step s3select: SELECT * FROM foo;
+a              b              
+
+1              session-2 insert
+2              initial tuple -> moved by session-1 -> updated by session-3 insert
+
+starting permutation: s2insert s1u s2c s3insert s1c s3c s3select
+step s2insert: INSERT INTO foo1 VALUES(1, 'session-2 insert') ON CONFLICT (a) DO UPDATE set b = foo1.b || ' -> updated by session-2 insert';
+step s1u: UPDATE foo SET a=2, b=b ||' -> moved by session-1' WHERE a=1; <waiting ...>
+step s2c: COMMIT;
+step s1u: <... completed>
+step s3insert: INSERT INTO foo2 VALUES(2, 'session-3 insert') ON CONFLICT (a) DO UPDATE set b = foo2.b || ' -> updated by session-3 insert'; <waiting ...>
+step s1c: COMMIT;
+step s3insert: <... completed>
+step s3c: COMMIT;
+step s3select: SELECT * FROM foo;
+a              b              
+
+2              initial tuple -> moved by session-1 -> updated by session-3 insert
+
+starting permutation: s3insert s1u s3c s2insert s1c s2c s3select
+step s3insert: INSERT INTO foo2 VALUES(2, 'session-3 insert') ON CONFLICT (a) DO UPDATE set b = foo2.b || ' -> updated by session-3 insert';
+step s1u: UPDATE foo SET a=2, b=b ||' -> moved by session-1' WHERE a=1; <waiting ...>
+step s3c: COMMIT;
+step s1u: <... completed>
+error in steps s3c s1u: ERROR:  duplicate key value violates unique constraint "foo2_pkey"
+step s2insert: INSERT INTO foo1 VALUES(1, 'session-2 insert') ON CONFLICT (a) DO UPDATE set b = foo1.b || ' -> updated by session-2 insert';
+step s1c: COMMIT;
+step s2c: COMMIT;
+step s3select: SELECT * FROM foo;
+a              b              
+
+1              initial tuple -> updated by session-2 insert
+2              session-3 insert
diff --git a/src/test/isolation/specs/partition-key-update-6.spec b/src/test/isolation/specs/partition-key-update-6.spec
new file mode 100644
index 0000000000..39ff344cfd
--- /dev/null
+++ b/src/test/isolation/specs/partition-key-update-6.spec
@@ -0,0 +1,44 @@
+# INSERT...ON CONFLICT DO UPDATE test
+#
+# This test tries to expose problems with the interaction between concurrent
+# sessions.
+#
+# FIXME: Since ON CONFLICT DO UPDATE not supported for the partitioned table,
+#        INSERT...ON CONFLICT DO UPDATE query executed for child relations.
+setup
+{
+  CREATE TABLE foo (a int primary key, b text) PARTITION BY LIST(a);
+  CREATE TABLE foo1 PARTITION OF foo FOR VALUES IN (1);
+  CREATE TABLE foo2 PARTITION OF foo FOR VALUES IN (2);
+  INSERT INTO foo VALUES (1, 'initial tuple');
+}
+
+teardown
+{
+  DROP TABLE foo;
+}
+
+session "s1"
+setup		{ BEGIN; }
+step "s1u"	{ UPDATE foo SET a=2, b=b ||' -> moved by session-1' WHERE a=1; }
+step "s1c"	{ COMMIT; }
+
+session "s2"
+setup { BEGIN ISOLATION LEVEL READ COMMITTED; }
+step "s2insert" { INSERT INTO foo1 VALUES(1, 'session-2 insert') ON CONFLICT (a) DO UPDATE set b = foo1.b || ' -> updated by session-2 insert'; }
+step "s2c" { COMMIT; }
+
+session "s3"
+setup { BEGIN ISOLATION LEVEL READ COMMITTED; }
+step "s3insert" { INSERT INTO foo2 VALUES(2, 'session-3 insert') ON CONFLICT (a) DO UPDATE set b = foo2.b || ' -> updated by session-3 insert'; }
+step "s3select" { SELECT * FROM foo; }
+step "s3c" { COMMIT; }
+
+# One session (session 2) block-waits on another (session 1) to determine if it
+# should proceed with an insert or update.  Notably, this entails updating a
+# tuple while there is no version of that tuple visible to the updating
+# session's snapshot.  This is permitted only in READ COMMITTED mode.
+permutation "s1u" "s2insert" "s3insert" "s1c" "s2c" "s3c" "s3select"
+permutation "s1u" "s3insert" "s2insert" "s1c" "s3c" "s2c" "s3select"
+permutation "s2insert" "s1u" "s2c" "s3insert" "s1c" "s3c" "s3select"
+permutation "s3insert" "s1u" "s3c" "s2insert" "s1c" "s2c" "s3select"
-- 
2.14.1