v3-0001-Test-additional-speculative-conflict-scenarios.patch

From 1bcd51c17f46d9b7389638131c6870a2baf8328a Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplageman@gmail.com>
Date: Tue, 6 Aug 2019 11:49:40 -0700
Subject: [PATCH v3] Test additional speculative conflict scenarios

Previously, the speculative insert tests did not cover the case when a
tuple t is inserted into a table with a unique index on a column but
before it can insert into the index, a concurrent transaction has
inserted a conflicting value into the index and the insertion of tuple t
must be aborted.

The basic permutation is one session successfully inserts into the table
and an associated unique index while a concurrent session successfully
inserts into the table but discovers a conflict before inserting into
the index and must abort the insertion.

Several variants on this include:
- swap which session is successful
- first session insert transaction does not commit, so second session
must wait on a transaction lock
- first session insert does not "complete", so second session must wait
on a speculative insertion lock

Also, refactor the existing TOAST table upsert test to be in the same
spec and reuse the steps.

Co-authored-by: Ashwin Agrawal <aagrawal@pivotal.io>
Co-authored-by: Taylor Vesely <tvesely@pivotal.io>
Co-authored-by: Andres Freund <andres@anarazel.de>
---
 .../expected/insert-conflict-specconflict.out | 237 +++++++++++++++---
 .../expected/insert-conflict-toast.out        |  15 --
 src/test/isolation/isolation_schedule         |   1 -
 .../specs/insert-conflict-specconflict.spec   | 102 +++++++-
 .../specs/insert-conflict-toast.spec          |  51 ----
 5 files changed, 295 insertions(+), 111 deletions(-)
 delete mode 100644 src/test/isolation/expected/insert-conflict-toast.out
 delete mode 100644 src/test/isolation/specs/insert-conflict-toast.spec

diff --git a/src/test/isolation/expected/insert-conflict-specconflict.out b/src/test/isolation/expected/insert-conflict-specconflict.out
index 20cc421b87..741d553402 100644
--- a/src/test/isolation/expected/insert-conflict-specconflict.out
+++ b/src/test/isolation/expected/insert-conflict-specconflict.out
@@ -13,11 +13,11 @@ pg_advisory_locksess           lock
 step controller_show: SELECT * FROM upserttest;
 key            data           
 
-s1: NOTICE:  called for k1
-s1: NOTICE:  blocking 3
+s1: NOTICE:  blurt_and_lock() called for k1
+s1: NOTICE:  acquiring advisory lock on 3
 step s1_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s1') ON CONFLICT (blurt_and_lock(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; <waiting ...>
-s2: NOTICE:  called for k1
-s2: NOTICE:  blocking 3
+s2: NOTICE:  blurt_and_lock() called for k1
+s2: NOTICE:  acquiring advisory lock on 3
 step s2_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s2') ON CONFLICT (blurt_and_lock(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s2'; <waiting ...>
 step controller_show: SELECT * FROM upserttest;
 key            data           
@@ -34,14 +34,14 @@ step controller_unlock_1_3: SELECT pg_advisory_unlock(1, 3);
 pg_advisory_unlock
 
 t              
-s1: NOTICE:  called for k1
-s1: NOTICE:  blocking 2
+s1: NOTICE:  blurt_and_lock() called for k1
+s1: NOTICE:  acquiring advisory lock on 2
 step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3);
 pg_advisory_unlock
 
 t              
-s2: NOTICE:  called for k1
-s2: NOTICE:  blocking 2
+s2: NOTICE:  blurt_and_lock() called for k1
+s2: NOTICE:  acquiring advisory lock on 2
 step controller_show: SELECT * FROM upserttest;
 key            data           
 
@@ -58,10 +58,10 @@ step controller_unlock_1_2: SELECT pg_advisory_unlock(1, 2);
 pg_advisory_unlock
 
 t              
-s1: NOTICE:  called for k1
-s1: NOTICE:  blocking 2
-s1: NOTICE:  called for k1
-s1: NOTICE:  blocking 2
+s1: NOTICE:  blurt_and_lock() called for k1
+s1: NOTICE:  acquiring advisory lock on 2
+s1: NOTICE:  blurt_and_lock() called for k1
+s1: NOTICE:  acquiring advisory lock on 2
 step s1_upsert: <... completed>
 step controller_show: SELECT * FROM upserttest;
 key            data           
@@ -81,11 +81,11 @@ pg_advisory_locksess           lock
 step controller_show: SELECT * FROM upserttest;
 key            data           
 
-s1: NOTICE:  called for k1
-s1: NOTICE:  blocking 3
+s1: NOTICE:  blurt_and_lock() called for k1
+s1: NOTICE:  acquiring advisory lock on 3
 step s1_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s1') ON CONFLICT (blurt_and_lock(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; <waiting ...>
-s2: NOTICE:  called for k1
-s2: NOTICE:  blocking 3
+s2: NOTICE:  blurt_and_lock() called for k1
+s2: NOTICE:  acquiring advisory lock on 3
 step s2_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s2') ON CONFLICT (blurt_and_lock(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s2'; <waiting ...>
 step controller_show: SELECT * FROM upserttest;
 key            data           
@@ -102,14 +102,14 @@ step controller_unlock_1_3: SELECT pg_advisory_unlock(1, 3);
 pg_advisory_unlock
 
 t              
-s1: NOTICE:  called for k1
-s1: NOTICE:  blocking 2
+s1: NOTICE:  blurt_and_lock() called for k1
+s1: NOTICE:  acquiring advisory lock on 2
 step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3);
 pg_advisory_unlock
 
 t              
-s2: NOTICE:  called for k1
-s2: NOTICE:  blocking 2
+s2: NOTICE:  blurt_and_lock() called for k1
+s2: NOTICE:  acquiring advisory lock on 2
 step controller_show: SELECT * FROM upserttest;
 key            data           
 
@@ -126,16 +126,84 @@ step controller_unlock_2_2: SELECT pg_advisory_unlock(2, 2);
 pg_advisory_unlock
 
 t              
-s2: NOTICE:  called for k1
-s2: NOTICE:  blocking 2
-s2: NOTICE:  called for k1
-s2: NOTICE:  blocking 2
+s2: NOTICE:  blurt_and_lock() called for k1
+s2: NOTICE:  acquiring advisory lock on 2
+s2: NOTICE:  blurt_and_lock() called for k1
+s2: NOTICE:  acquiring advisory lock on 2
 step s2_upsert: <... completed>
 step controller_show: SELECT * FROM upserttest;
 key            data           
 
 k1             inserted s1 with conflict update s2
 
+starting permutation: controller_locks controller_show s1_insert_toast s2_insert_toast controller_show controller_unlock_1_1 controller_unlock_2_1 controller_unlock_1_3 controller_unlock_2_3 controller_show controller_unlock_1_2 controller_show_count controller_unlock_2_2 controller_show_count
+step controller_locks: SELECT pg_advisory_lock(sess, lock), sess, lock FROM generate_series(1, 2) a(sess), generate_series(1,3) b(lock);
+pg_advisory_locksess           lock           
+
+               1              1              
+               1              2              
+               1              3              
+               2              1              
+               2              2              
+               2              3              
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+s1: NOTICE:  blurt_and_lock() called for k2
+s1: NOTICE:  acquiring advisory lock on 3
+step s1_insert_toast: INSERT INTO upserttest VALUES('k2', ctoast_large_val()) ON CONFLICT DO NOTHING; <waiting ...>
+s2: NOTICE:  blurt_and_lock() called for k2
+s2: NOTICE:  acquiring advisory lock on 3
+step s2_insert_toast: INSERT INTO upserttest VALUES('k2', ctoast_large_val()) ON CONFLICT DO NOTHING; <waiting ...>
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+step controller_unlock_1_1: SELECT pg_advisory_unlock(1, 1);
+pg_advisory_unlock
+
+t              
+step controller_unlock_2_1: SELECT pg_advisory_unlock(2, 1);
+pg_advisory_unlock
+
+t              
+step controller_unlock_1_3: SELECT pg_advisory_unlock(1, 3);
+pg_advisory_unlock
+
+t              
+s1: NOTICE:  blurt_and_lock() called for k2
+s1: NOTICE:  acquiring advisory lock on 2
+step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3);
+pg_advisory_unlock
+
+t              
+s2: NOTICE:  blurt_and_lock() called for k2
+s2: NOTICE:  acquiring advisory lock on 2
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+step controller_unlock_1_2: SELECT pg_advisory_unlock(1, 2);
+pg_advisory_unlock
+
+t              
+step s1_insert_toast: <... completed>
+step controller_show_count: SELECT COUNT(*) FROM upserttest;
+count          
+
+1              
+step controller_unlock_2_2: SELECT pg_advisory_unlock(2, 2);
+pg_advisory_unlock
+
+t              
+s2: NOTICE:  blurt_and_lock() called for k2
+s2: NOTICE:  acquiring advisory lock on 2
+s2: NOTICE:  blurt_and_lock() called for k2
+s2: NOTICE:  acquiring advisory lock on 2
+step s2_insert_toast: <... completed>
+step controller_show_count: SELECT COUNT(*) FROM upserttest;
+count          
+
+1              
+
 starting permutation: controller_locks controller_show s1_begin s2_begin s1_upsert s2_upsert controller_show controller_unlock_1_1 controller_unlock_2_1 controller_unlock_1_3 controller_unlock_2_3 controller_show controller_unlock_1_2 controller_show controller_unlock_2_2 controller_show s1_commit controller_show s2_commit controller_show
 step controller_locks: SELECT pg_advisory_lock(sess, lock), sess, lock FROM generate_series(1, 2) a(sess), generate_series(1,3) b(lock);
 pg_advisory_locksess           lock           
@@ -151,11 +219,11 @@ key            data
 
 step s1_begin: BEGIN;
 step s2_begin: BEGIN;
-s1: NOTICE:  called for k1
-s1: NOTICE:  blocking 3
+s1: NOTICE:  blurt_and_lock() called for k1
+s1: NOTICE:  acquiring advisory lock on 3
 step s1_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s1') ON CONFLICT (blurt_and_lock(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; <waiting ...>
-s2: NOTICE:  called for k1
-s2: NOTICE:  blocking 3
+s2: NOTICE:  blurt_and_lock() called for k1
+s2: NOTICE:  acquiring advisory lock on 3
 step s2_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s2') ON CONFLICT (blurt_and_lock(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s2'; <waiting ...>
 step controller_show: SELECT * FROM upserttest;
 key            data           
@@ -172,14 +240,14 @@ step controller_unlock_1_3: SELECT pg_advisory_unlock(1, 3);
 pg_advisory_unlock
 
 t              
-s1: NOTICE:  called for k1
-s1: NOTICE:  blocking 2
+s1: NOTICE:  blurt_and_lock() called for k1
+s1: NOTICE:  acquiring advisory lock on 2
 step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3);
 pg_advisory_unlock
 
 t              
-s2: NOTICE:  called for k1
-s2: NOTICE:  blocking 2
+s2: NOTICE:  blurt_and_lock() called for k1
+s2: NOTICE:  acquiring advisory lock on 2
 step controller_show: SELECT * FROM upserttest;
 key            data           
 
@@ -195,16 +263,16 @@ step controller_unlock_2_2: SELECT pg_advisory_unlock(2, 2);
 pg_advisory_unlock
 
 t              
-s2: NOTICE:  called for k1
-s2: NOTICE:  blocking 2
-s2: NOTICE:  called for k1
-s2: NOTICE:  blocking 2
+s2: NOTICE:  blurt_and_lock() called for k1
+s2: NOTICE:  acquiring advisory lock on 2
+s2: NOTICE:  blurt_and_lock() called for k1
+s2: NOTICE:  acquiring advisory lock on 2
 step controller_show: SELECT * FROM upserttest;
 key            data           
 
 step s1_commit: COMMIT;
-s2: NOTICE:  called for k1
-s2: NOTICE:  blocking 2
+s2: NOTICE:  blurt_and_lock() called for k1
+s2: NOTICE:  acquiring advisory lock on 2
 step s2_upsert: <... completed>
 step controller_show: SELECT * FROM upserttest;
 key            data           
@@ -215,3 +283,96 @@ step controller_show: SELECT * FROM upserttest;
 key            data           
 
 k1             inserted s1 with conflict update s2
+
+starting permutation: s1_create_non_unique_index s1_confirm_index_order controller_locks controller_show s1_upsert s2_upsert controller_show controller_unlock_1_1 controller_unlock_2_1 controller_unlock_1_3 controller_unlock_2_3 controller_show controller_lock_2_4 controller_unlock_2_2 controller_show controller_unlock_1_2 controller_print_speculative_locks controller_unlock_2_4 controller_show
+step s1_create_non_unique_index: CREATE INDEX upserttest_key_idx ON upserttest((blurt_and_lock2(key)));
+step s1_confirm_index_order: SELECT 'upserttest_key_uniq_idx'::regclass::int8 < 'upserttest_key_idx'::regclass::int8;
+?column?       
+
+t              
+step controller_locks: SELECT pg_advisory_lock(sess, lock), sess, lock FROM generate_series(1, 2) a(sess), generate_series(1,3) b(lock);
+pg_advisory_locksess           lock           
+
+               1              1              
+               1              2              
+               1              3              
+               2              1              
+               2              2              
+               2              3              
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+s1: NOTICE:  blurt_and_lock() called for k1
+s1: NOTICE:  acquiring advisory lock on 3
+step s1_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s1') ON CONFLICT (blurt_and_lock(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; <waiting ...>
+s2: NOTICE:  blurt_and_lock() called for k1
+s2: NOTICE:  acquiring advisory lock on 3
+step s2_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s2') ON CONFLICT (blurt_and_lock(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s2'; <waiting ...>
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+step controller_unlock_1_1: SELECT pg_advisory_unlock(1, 1);
+pg_advisory_unlock
+
+t              
+step controller_unlock_2_1: SELECT pg_advisory_unlock(2, 1);
+pg_advisory_unlock
+
+t              
+step controller_unlock_1_3: SELECT pg_advisory_unlock(1, 3);
+pg_advisory_unlock
+
+t              
+s1: NOTICE:  blurt_and_lock() called for k1
+s1: NOTICE:  acquiring advisory lock on 2
+step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3);
+pg_advisory_unlock
+
+t              
+s2: NOTICE:  blurt_and_lock() called for k1
+s2: NOTICE:  acquiring advisory lock on 2
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+step controller_lock_2_4: SELECT pg_advisory_lock(2, 4);
+pg_advisory_lock
+
+               
+step controller_unlock_2_2: SELECT pg_advisory_unlock(2, 2);
+pg_advisory_unlock
+
+t              
+s2: NOTICE:  blurt_and_lock2() called for k1
+s2: NOTICE:  acquiring advisory lock on 4
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+step controller_unlock_1_2: SELECT pg_advisory_unlock(1, 2);
+pg_advisory_unlock
+
+t              
+s1: NOTICE:  blurt_and_lock2() called for k1
+s1: NOTICE:  acquiring advisory lock on 4
+s1: NOTICE:  blurt_and_lock() called for k1
+s1: NOTICE:  acquiring advisory lock on 2
+s1: NOTICE:  blurt_and_lock() called for k1
+s1: NOTICE:  acquiring advisory lock on 2
+step controller_print_speculative_locks: SELECT locktype, mode, granted FROM pg_locks WHERE locktype='speculative token' ORDER BY granted;
+locktype       mode           granted        
+
+speculative tokenShareLock      f              
+speculative tokenExclusiveLock  t              
+step controller_unlock_2_4: SELECT pg_advisory_unlock(2, 4);
+pg_advisory_unlock
+
+t              
+s1: NOTICE:  blurt_and_lock() called for k1
+s1: NOTICE:  acquiring advisory lock on 2
+s1: NOTICE:  blurt_and_lock() called for k1
+s1: NOTICE:  acquiring advisory lock on 2
+step s1_upsert: <... completed>
+step s2_upsert: <... completed>
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+k1             inserted s2 with conflict update s1
diff --git a/src/test/isolation/expected/insert-conflict-toast.out b/src/test/isolation/expected/insert-conflict-toast.out
deleted file mode 100644
index e86b98020f..0000000000
--- a/src/test/isolation/expected/insert-conflict-toast.out
+++ /dev/null
@@ -1,15 +0,0 @@
-Parsed test spec with 3 sessions
-
-starting permutation: s2insert s3insert s1commit
-pg_advisory_xact_lock
-
-               
-step s2insert: 
-  INSERT INTO ctoast (key, val) VALUES (1, ctoast_large_val()) ON CONFLICT DO NOTHING;
- <waiting ...>
-step s3insert: 
-  INSERT INTO ctoast (key, val) VALUES (1, ctoast_large_val()) ON CONFLICT DO NOTHING;
- <waiting ...>
-step s1commit: COMMIT;
-step s2insert: <... completed>
-step s3insert: <... completed>
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 69ae227953..81928a5922 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -37,7 +37,6 @@ test: insert-conflict-do-nothing-2
 test: insert-conflict-do-update
 test: insert-conflict-do-update-2
 test: insert-conflict-do-update-3
-test: insert-conflict-toast
 test: insert-conflict-specconflict
 test: delete-abort-savept
 test: delete-abort-savept-2
diff --git a/src/test/isolation/specs/insert-conflict-specconflict.spec b/src/test/isolation/specs/insert-conflict-specconflict.spec
index 3a70484fc2..a404a4a5c7 100644
--- a/src/test/isolation/specs/insert-conflict-specconflict.spec
+++ b/src/test/isolation/specs/insert-conflict-specconflict.spec
@@ -10,22 +10,32 @@ setup
 {
      CREATE OR REPLACE FUNCTION blurt_and_lock(text) RETURNS text IMMUTABLE LANGUAGE plpgsql AS $$
      BEGIN
-        RAISE NOTICE 'called for %', $1;
+        RAISE NOTICE 'blurt_and_lock() called for %', $1;
 
 	-- depending on lock state, wait for lock 2 or 3
         IF pg_try_advisory_xact_lock(current_setting('spec.session')::int, 1) THEN
-            RAISE NOTICE 'blocking 2';
+            RAISE NOTICE 'acquiring advisory lock on 2';
             PERFORM pg_advisory_xact_lock(current_setting('spec.session')::int, 2);
         ELSE
-            RAISE NOTICE 'blocking 3';
+            RAISE NOTICE 'acquiring advisory lock on 3';
             PERFORM pg_advisory_xact_lock(current_setting('spec.session')::int, 3);
         END IF;
     RETURN $1;
     END;$$;
 
+    CREATE OR REPLACE FUNCTION blurt_and_lock2(text) RETURNS text IMMUTABLE LANGUAGE plpgsql AS $$
+    BEGIN
+        RAISE NOTICE 'blurt_and_lock2() called for %', $1;
+		RAISE NOTICE 'acquiring advisory lock on 4';
+        PERFORM pg_advisory_xact_lock(current_setting('spec.session')::int, 4);
+    RETURN $1;
+    END;$$;
+
+    CREATE OR REPLACE FUNCTION ctoast_large_val() RETURNS TEXT LANGUAGE SQL AS 'select array_agg(md5(g::text))::text from generate_series(1, 256) g';
+
     CREATE TABLE upserttest(key text, data text);
 
-    CREATE UNIQUE INDEX ON upserttest((blurt_and_lock(key)));
+    CREATE UNIQUE INDEX upserttest_key_uniq_idx ON upserttest((blurt_and_lock(key)));
 }
 
 teardown
@@ -45,7 +55,11 @@ step "controller_unlock_1_2" { SELECT pg_advisory_unlock(1, 2); }
 step "controller_unlock_2_2" { SELECT pg_advisory_unlock(2, 2); }
 step "controller_unlock_1_3" { SELECT pg_advisory_unlock(1, 3); }
 step "controller_unlock_2_3" { SELECT pg_advisory_unlock(2, 3); }
+step "controller_lock_2_4" { SELECT pg_advisory_lock(2, 4); }
+step "controller_unlock_2_4" { SELECT pg_advisory_unlock(2, 4); }
 step "controller_show" {SELECT * FROM upserttest; }
+step "controller_show_count" {SELECT COUNT(*) FROM upserttest; }
+step "controller_print_speculative_locks" { SELECT locktype, mode, granted FROM pg_locks WHERE locktype='speculative token' ORDER BY granted; }
 
 session "s1"
 setup
@@ -54,7 +68,10 @@ setup
   SET spec.session = 1;
 }
 step "s1_begin"  { BEGIN; }
+step "s1_create_non_unique_index" { CREATE INDEX upserttest_key_idx ON upserttest((blurt_and_lock2(key))); }
+step "s1_confirm_index_order" { SELECT 'upserttest_key_uniq_idx'::regclass::int8 < 'upserttest_key_idx'::regclass::int8; }
 step "s1_upsert" { INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s1') ON CONFLICT (blurt_and_lock(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; }
+step "s1_insert_toast" { INSERT INTO upserttest VALUES('k2', ctoast_large_val()) ON CONFLICT DO NOTHING; }
 step "s1_commit"  { COMMIT; }
 
 session "s2"
@@ -65,6 +82,7 @@ setup
 }
 step "s2_begin"  { BEGIN; }
 step "s2_upsert" { INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s2') ON CONFLICT (blurt_and_lock(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s2'; }
+step "s2_insert_toast" { INSERT INTO upserttest VALUES('k2', ctoast_large_val()) ON CONFLICT DO NOTHING; }
 step "s2_commit"  { COMMIT; }
 
 # Test that speculative locks are correctly acquired and released, s2
@@ -92,8 +110,8 @@ permutation
    # This should now show a successful UPSERT
    "controller_show"
 
-# Test that speculative locks are correctly acquired and released, s2
-# inserts, s1 updates.
+# Test that speculative locks are correctly acquired and released, s1
+# inserts, s2 updates.
 permutation
    # acquire a number of locks, to control execution flow - the
    # blurt_and_lock function acquires advisory locks that allow us to
@@ -117,6 +135,31 @@ permutation
    # This should now show a successful UPSERT
    "controller_show"
 
+# Test that speculatively inserted toast rows do not cause conflicts.
+# s1 inserts successfully, s2 does not.
+permutation
+   # acquire a number of locks, to control execution flow - the
+   # blurt_and_lock function acquires advisory locks that allow us to
+   # continue after a) the optimistic conflict probe b) after the
+   # insertion of the speculative tuple.
+   "controller_locks"
+   "controller_show"
+   "s1_insert_toast" "s2_insert_toast"
+   "controller_show"
+   # Switch both sessions to wait on the other lock next time (the speculative insertion)
+   "controller_unlock_1_1" "controller_unlock_2_1"
+   # Allow both sessions to continue
+   "controller_unlock_1_3" "controller_unlock_2_3"
+   "controller_show"
+   # Allow the first session to finish insertion
+   "controller_unlock_1_2"
+   # This should now show that 1 additional tuple was inserted successfully
+   "controller_show_count"
+   # Allow the second session to finish insertion and kill the speculatively inserted tuple
+   "controller_unlock_2_2"
+   # This should show the same number of tuples as before s2 inserted
+   "controller_show_count"
+
 # Test that speculative locks are correctly acquired and released, s2
 # inserts, s1 updates.  With the added complication that transactions
 # don't immediately commit.
@@ -147,3 +190,50 @@ permutation
    "controller_show"
    "s2_commit"
    "controller_show"
+
+# Test that speculative wait is performed if a session sees a speculatively
+# inserted tuple. A speculatively inserted tuple is one which has been inserted
+# both into the table and the unique index but has yet to *complete* the
+# speculative insertion
+permutation
+   # acquire a number of advisory locks to control execution flow - the
+   # blurt_and_lock function acquires advisory locks that allow us to
+   # continue after a) the optimistic conflict probe and b) after the
+   # insertion of the speculative tuple.
+   # blurt_and_lock2 acquires an advisory lock which allows us to pause
+   # execution c) before completing the speculative insertion
+
+   # create the second index here to avoid affecting the other
+   # permutations.
+   "s1_create_non_unique_index"
+   # confirm that the insertion into the unique index will happen first
+   "s1_confirm_index_order"
+   "controller_locks"
+   "controller_show"
+   # Both sessions wait on advisory locks
+   "s1_upsert" "s2_upsert"
+   "controller_show"
+   # Switch both sessions to wait on the other lock next time (the speculative insertion)
+   "controller_unlock_1_1" "controller_unlock_2_1"
+   # Allow both sessions to do the optimistic conflict probe and do the
+   # speculative insertion into the table
+   # They will then be waiting on another advisory lock when they attempt to
+   # update the index
+   "controller_unlock_1_3" "controller_unlock_2_3"
+   "controller_show"
+   # take lock to block second session after inserting in unique index but
+   # before completing the speculative insert
+   "controller_lock_2_4"
+   # Allow the second session to move forward
+   "controller_unlock_2_2"
+   # This should still not show a successful insertion
+   "controller_show"
+   # Allow the first session to continue, it should perform speculative wait
+   "controller_unlock_1_2"
+   # Should report s1 is waiting on speculative lock
+   "controller_print_speculative_locks"
+   # Allow s2 to insert into the non-unique index and complete
+   # s1 will no longer wait and will proceed to update
+   "controller_unlock_2_4"
+   # This should now show a successful UPSERT
+   "controller_show"
diff --git a/src/test/isolation/specs/insert-conflict-toast.spec b/src/test/isolation/specs/insert-conflict-toast.spec
deleted file mode 100644
index c5e39ef9e3..0000000000
--- a/src/test/isolation/specs/insert-conflict-toast.spec
+++ /dev/null
@@ -1,51 +0,0 @@
-# INSERT...ON CONFLICT test on table with TOAST
-#
-# This test verifies that speculatively inserted toast rows do not
-# cause conflicts. It does so by using expression index over a
-# function which acquires an advisory lock, triggering two index
-# insertions to happen almost at the same time. This is not guaranteed
-# to lead to a failed speculative insertion, but makes one quite
-# likely.
-
-setup
-{
-  CREATE TABLE ctoast (key int primary key, val text);
-  CREATE OR REPLACE FUNCTION ctoast_lock_func(int) RETURNS INT IMMUTABLE LANGUAGE SQL AS 'select pg_advisory_xact_lock_shared(1); select $1;';
-  CREATE OR REPLACE FUNCTION ctoast_large_val() RETURNS TEXT LANGUAGE SQL AS 'select array_agg(md5(g::text))::text from generate_series(1, 256) g';
-  CREATE UNIQUE INDEX ctoast_lock_idx ON ctoast (ctoast_lock_func(key));
-}
-
-teardown
-{
-  DROP TABLE ctoast;
-  DROP FUNCTION ctoast_lock_func(int);
-  DROP FUNCTION ctoast_large_val();
-}
-
-session "s1"
-setup
-{
-  BEGIN ISOLATION LEVEL READ COMMITTED;
-  SELECT pg_advisory_xact_lock(1);
-}
-step "s1commit" { COMMIT; }
-
-session "s2"
-setup
-{
-  SET default_transaction_isolation = 'read committed';
-}
-step "s2insert" {
-  INSERT INTO ctoast (key, val) VALUES (1, ctoast_large_val()) ON CONFLICT DO NOTHING;
-}
-
-session "s3"
-setup
-{
-  SET default_transaction_isolation = 'read committed';
-}
-step "s3insert" {
-  INSERT INTO ctoast (key, val) VALUES (1, ctoast_large_val()) ON CONFLICT DO NOTHING;
-}
-
-permutation "s2insert" "s3insert" "s1commit"
-- 
2.22.0