Adding a test for speculative insert abort case

diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 444c0c05746..bad50aab007 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -554,6 +554,8 @@ ExecInsert(ModifyTableState *mtstate,
 												   &specConflict,
 												   arbiterIndexes);
 
+			elog(WARNING, "speculative: %d", !!specConflict);
+
 			/* adjust the tuple's state accordingly */
 			table_complete_speculative(resultRelationDesc, slot,
 									   specToken, specConflict);
diff --git a/src/test/isolation/expected/speculative-conflict.out b/src/test/isolation/expected/speculative-conflict.out
new file mode 100644
index 00000000000..7b6ce4bb989
--- /dev/null
+++ b/src/test/isolation/expected/speculative-conflict.out
@@ -0,0 +1,185 @@
+Parsed test spec with 3 sessions
+
+starting permutation: 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_unlock_2_2 controller_show controller_unlock_1_2 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           
+
+               1              1              
+               1              2              
+               1              3              
+               2              1              
+               2              2              
+               2              3              
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+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 ...>
+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              
+step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3);
+pg_advisory_unlock
+
+t              
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+step controller_unlock_2_2: SELECT pg_advisory_unlock(2, 2);
+pg_advisory_unlock
+
+t              
+s2: WARNING:  speculative: 0
+step s2_upsert: <... completed>
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+k1             inserted s2    
+step controller_unlock_1_2: SELECT pg_advisory_unlock(1, 2);
+pg_advisory_unlock
+
+t              
+s1: WARNING:  speculative: 1
+step s1_upsert: <... completed>
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+k1             inserted s2 with conflict update s1
+
+starting permutation: 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_unlock_1_2 controller_show controller_unlock_2_2 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           
+
+               1              1              
+               1              2              
+               1              3              
+               2              1              
+               2              2              
+               2              3              
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+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 ...>
+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              
+step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3);
+pg_advisory_unlock
+
+t              
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+step controller_unlock_1_2: SELECT pg_advisory_unlock(1, 2);
+pg_advisory_unlock
+
+t              
+s1: WARNING:  speculative: 0
+step s1_upsert: <... completed>
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+k1             inserted s1    
+step controller_unlock_2_2: SELECT pg_advisory_unlock(2, 2);
+pg_advisory_unlock
+
+t              
+s2: WARNING:  speculative: 1
+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_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           
+
+               1              1              
+               1              2              
+               1              3              
+               2              1              
+               2              2              
+               2              3              
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+step s1_begin: BEGIN;
+step s2_begin: BEGIN;
+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 ...>
+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              
+step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3);
+pg_advisory_unlock
+
+t              
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+step controller_unlock_1_2: SELECT pg_advisory_unlock(1, 2);
+pg_advisory_unlock
+
+t              
+s1: WARNING:  speculative: 0
+step s1_upsert: <... completed>
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+step controller_unlock_2_2: SELECT pg_advisory_unlock(2, 2);
+pg_advisory_unlock
+
+t              
+s2: WARNING:  speculative: 1
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+step s1_commit: COMMIT;
+step s2_upsert: <... completed>
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+k1             inserted s1    
+step s2_commit: COMMIT;
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+k1             inserted s1 with conflict update s2
diff --git a/src/test/isolation/specs/speculative-conflict.spec b/src/test/isolation/specs/speculative-conflict.spec
new file mode 100644
index 00000000000..49de55e289a
--- /dev/null
+++ b/src/test/isolation/specs/speculative-conflict.spec
@@ -0,0 +1,129 @@
+setup
+{
+     CREATE OR REPLACE FUNCTION blurt_and_lock(text) RETURNS text IMMUTABLE LANGUAGE plpgsql AS $$
+     BEGIN
+        RAISE NOTICE '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';
+            PERFORM pg_advisory_xact_lock(current_setting('spec.session')::int, 2);
+        ELSE
+            RAISE NOTICE 'blocking 3';
+            PERFORM pg_advisory_xact_lock(current_setting('spec.session')::int, 3);
+        END IF;
+    RETURN $1;
+    END;$$;
+
+    CREATE TABLE upserttest(key text, data text);
+
+    CREATE UNIQUE INDEX ON upserttest((blurt_and_lock(key)));
+}
+
+teardown
+{
+    DROP TABLE upserttest;
+}
+
+session "controller"
+step "controller_locks" {SELECT pg_advisory_lock(sess, lock), sess, lock FROM generate_series(1, 2) a(sess), generate_series(1,3) b(lock);}
+step "controller_unlock_1_1" { SELECT pg_advisory_unlock(1, 1); }
+step "controller_unlock_2_1" { SELECT pg_advisory_unlock(2, 1); }
+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_show" {SELECT * FROM upserttest; }
+
+session "s1"
+setup { SET spec.session = 1; }
+step "s1_begin"  { BEGIN; }
+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_commit"  { COMMIT; }
+
+session "s2"
+setup { SET spec.session = 2; }
+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_commit"  { COMMIT; }
+
+# Test that speculative locks are correctly acquired and released, s2
+# inserts, s1 updates.
+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_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 continue
+   "controller_unlock_1_3" "controller_unlock_2_3"
+   "controller_show"
+   # Allow the second session to finish insertion
+   "controller_unlock_2_2"
+   # This should now show a successful insertion
+   "controller_show"
+   # Allow the first session to finish insertion
+   "controller_unlock_1_2"
+   # This should now show a successful UPSERT
+   "controller_show"
+
+# Test that speculative locks are correctly acquired and released, s2
+# inserts, s1 updates.
+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_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 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 a successful insertion
+   "controller_show"
+   # Allow the second session to finish insertion
+   "controller_unlock_2_2"
+   # This should now show a successful UPSERT
+   "controller_show"
+
+# Test that speculative locks are correctly acquired and released, s2
+# inserts, s1 updates.  With the added complication that transactions
+# don't immediately commit.
+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_begin" "s2_begin"
+   "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 continue
+   "controller_unlock_1_3" "controller_unlock_2_3"
+   "controller_show"
+   # Allow the first session to finish insertion
+   "controller_unlock_1_2"
+   # But the change isn't visible yet, nor should the second session continue
+   "controller_show"
+   # Allow the second session to finish insertion, but it's blocked
+   "controller_unlock_2_2"
+   "controller_show"
+   # But committing should unblock
+   "s1_commit"
+   "controller_show"
+   "s2_commit"
+   "controller_show"

diff --git a/src/test/isolation/specs/insert-conflict-specconflict.spec b/src/test/isolation/specs/insert-conflict-specconflict.spec
index 3a70484fc2..3c7f9c2239 100644
--- a/src/test/isolation/specs/insert-conflict-specconflict.spec
+++ b/src/test/isolation/specs/insert-conflict-specconflict.spec
@@ -70,24 +70,29 @@ step "s2_commit"  { COMMIT; }
 # Test that speculative locks are correctly acquired and released, s2
 # inserts, s1 updates.
 permutation
-   # acquire a number of locks, to control execution flow - the
+   # 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 b) after the
    # insertion of the speculative tuple.
+
    "controller_locks"
    "controller_show"
+   # Both sessions will be waiting on advisory locks
    "s1_upsert" "s2_upsert"
    "controller_show"
-   # Switch both sessions to wait on the other lock next time (the speculative insertion)
+   # Switch both sessions to wait on the other advisory lock next time
    "controller_unlock_1_1" "controller_unlock_2_1"
-   # Allow both sessions to continue
+   # 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"
-   # Allow the second session to finish insertion
+   # Allow the second session to finish insertion (complete speculative)
    "controller_unlock_2_2"
    # This should now show a successful insertion
    "controller_show"
-   # Allow the first session to finish insertion
+   # Allow the first session to finish insertion (abort speculative)
    "controller_unlock_1_2"
    # This should now show a successful UPSERT
    "controller_show"

From 24e44f1a971e8957503205376d655e22b0e5fbdc Mon Sep 17 00:00:00 2001
From: Ashwin Agrawal <aagrawal@pivotal.io>
Date: Wed, 15 May 2019 22:21:48 -0700
Subject: [PATCH] Add test to validate speculative wait is performed.

---
 .../expected/insert-conflict-specconflict.out | 69 +++++++++++++++++++
 .../specs/insert-conflict-specconflict.spec   | 44 ++++++++++++
 2 files changed, 113 insertions(+)

diff --git a/src/test/isolation/expected/insert-conflict-specconflict.out b/src/test/isolation/expected/insert-conflict-specconflict.out
index 5726bdb8e8..ca2b65ca56 100644
--- a/src/test/isolation/expected/insert-conflict-specconflict.out
+++ b/src/test/isolation/expected/insert-conflict-specconflict.out
@@ -177,3 +177,72 @@ step controller_show: SELECT * FROM upserttest;
 key            data           
 
 k1             inserted s1 with conflict update s2
+
+starting permutation: s1_create_non_unique_index 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 ON upserttest((blurt_and_lock2(key)));
+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           
+
+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 ...>
+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              
+step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3);
+pg_advisory_unlock
+
+t              
+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              
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+step controller_unlock_1_2: SELECT pg_advisory_unlock(1, 2);
+pg_advisory_unlock
+
+t              
+step controller_print_speculative_locks: SELECT locktype,classid,objid,mode,granted FROM pg_locks WHERE locktype='speculative token' order by granted;
+locktype       classid        objid          mode           granted        
+
+speculative token4              0              ShareLock      f              
+speculative token4              0              ExclusiveLock  t              
+step controller_unlock_2_4: SELECT pg_advisory_unlock(2, 4);
+pg_advisory_unlock
+
+t              
+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/specs/insert-conflict-specconflict.spec b/src/test/isolation/specs/insert-conflict-specconflict.spec
index 3a70484fc2..71e46727cf 100644
--- a/src/test/isolation/specs/insert-conflict-specconflict.spec
+++ b/src/test/isolation/specs/insert-conflict-specconflict.spec
@@ -23,6 +23,13 @@ setup
     RETURN $1;
     END;$$;
 
+    CREATE OR REPLACE FUNCTION blurt_and_lock2(text) RETURNS text IMMUTABLE LANGUAGE plpgsql AS $$
+    BEGIN
+        RAISE NOTICE 'blurt_and_lock1 called for %', $1;
+        PERFORM pg_advisory_xact_lock(current_setting('spec.session')::int, 4);
+    RETURN $1;
+    END;$$;
+
     CREATE TABLE upserttest(key text, data text);
 
     CREATE UNIQUE INDEX ON upserttest((blurt_and_lock(key)));
@@ -45,7 +52,10 @@ 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_print_speculative_locks" { SELECT locktype,classid,objid,mode,granted FROM pg_locks WHERE locktype='speculative token' order by granted; }
 
 session "s1"
 setup
@@ -54,6 +64,7 @@ setup
   SET spec.session = 1;
 }
 step "s1_begin"  { BEGIN; }
+step "s1_create_non_unique_index" { CREATE INDEX ON upserttest((blurt_and_lock2(key))); }
 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_commit"  { COMMIT; }
 
@@ -147,3 +158,36 @@ permutation
    "controller_show"
    "s2_commit"
    "controller_show"
+
+# Test that speculative wait is performed if session sees a speculatively
+# inserted tuple. Speculatively inserted tuple means tuple is inserted into the
+# table and unique index but yet to *complete* the speculative insertion
+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.
+   "s1_create_non_unique_index"
+   "controller_locks"
+   "controller_show"
+   "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 continue
+   "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, it should perform speculative wait
+   "controller_unlock_1_2"
+   # Should report waiting on speculative lock
+   "controller_print_speculative_locks"
+   "controller_unlock_2_4"
+   # This should now show a successful UPSERT
+   "controller_show"
-- 
2.19.1

diff --git a/src/test/isolation/specs/insert-conflict-specconflict.spec b/src/test/isolation/specs/insert-conflict-specconflict.spec
index 71e46727cf..4ae33319ab 100644
--- a/src/test/isolation/specs/insert-conflict-specconflict.spec
+++ b/src/test/isolation/specs/insert-conflict-specconflict.spec
@@ -159,14 +159,20 @@ permutation
    "s2_commit"
    "controller_show"
 
-# Test that speculative wait is performed if session sees a speculatively
-# inserted tuple. Speculatively inserted tuple means tuple is inserted into the
-# table and unique index but yet to *complete* the speculative insertion
+# 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 locks, to control execution flow - the
+   # 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 b) after the
+   # 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. also, s1 creates it instead of the controller because ...
    "s1_create_non_unique_index"
    "controller_locks"
    "controller_show"
@@ -184,9 +190,9 @@ permutation
    "controller_unlock_2_2"
    # This should still not show a successful insertion
    "controller_show"
-   # Allow the first session, it should perform speculative wait
+   # Allow the first session to continue, it should perform speculative wait
    "controller_unlock_1_2"
-   # Should report waiting on speculative lock
+   # Should report s1 is waiting on speculative lock
    "controller_print_speculative_locks"
    "controller_unlock_2_4"
    # This should now show a successful UPSERT

diff --git a/src/test/isolation/expected/insert-conflict-specconflict.out b/src/test/isolation/expected/insert-conflict-specconflict.out
index 5726bdb8e8..1fbe7bc4d5 100644
--- a/src/test/isolation/expected/insert-conflict-specconflict.out
+++ b/src/test/isolation/expected/insert-conflict-specconflict.out
@@ -177,3 +177,76 @@ 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           
+
+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 ...>
+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              
+step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3);
+pg_advisory_unlock
+
+t              
+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              
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+step controller_unlock_1_2: SELECT pg_advisory_unlock(1, 2);
+pg_advisory_unlock
+
+t              
+step controller_print_speculative_locks: SELECT locktype,classid,objid,mode,granted FROM pg_locks WHERE locktype='speculative token' order by granted;
+locktype       classid        objid          mode           granted        
+
+speculative token4              0              ShareLock      f              
+speculative token4              0              ExclusiveLock  t              
+step controller_unlock_2_4: SELECT pg_advisory_unlock(2, 4);
+pg_advisory_unlock
+
+t              
+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/specs/insert-conflict-specconflict.spec b/src/test/isolation/specs/insert-conflict-specconflict.spec
index 3a70484fc2..dd38d2642e 100644
--- a/src/test/isolation/specs/insert-conflict-specconflict.spec
+++ b/src/test/isolation/specs/insert-conflict-specconflict.spec
@@ -23,9 +23,16 @@ setup
     RETURN $1;
     END;$$;
 
+    CREATE OR REPLACE FUNCTION blurt_and_lock2(text) RETURNS text IMMUTABLE LANGUAGE plpgsql AS $$
+    BEGIN
+        RAISE NOTICE 'blurt_and_lock1 called for %', $1;
+        PERFORM pg_advisory_xact_lock(current_setting('spec.session')::int, 4);
+    RETURN $1;
+    END;$$;
+
     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 +52,10 @@ 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_print_speculative_locks" { SELECT locktype,classid,objid,mode,granted FROM pg_locks WHERE locktype='speculative token' order by granted; }
 
 session "s1"
 setup
@@ -54,6 +64,8 @@ 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_commit"  { COMMIT; }
 
@@ -70,49 +82,57 @@ step "s2_commit"  { COMMIT; }
 # Test that speculative locks are correctly acquired and released, s2
 # inserts, s1 updates.
 permutation
-   # acquire a number of locks, to control execution flow - the
+   # 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 b) after the
    # insertion of the speculative tuple.
    "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 continue
+   # 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"
-   # Allow the second session to finish insertion
+   # Allow the second session to finish insertion (complete speculative)
    "controller_unlock_2_2"
    # This should now show a successful insertion
    "controller_show"
-   # Allow the first session to finish insertion
+   # Allow the first session to finish insertion (abort speculative)
    "controller_unlock_1_2"
    # 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
+   # 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 b) after the
    # insertion of the speculative tuple.
    "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 continue
+   # 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"
-   # Allow the first session to finish insertion
+   # Allow the first session to finish insertion (complete speculative)
    "controller_unlock_1_2"
    # This should now show a successful insertion
    "controller_show"
-   # Allow the second session to finish insertion
+   # Allow the second session to finish insertion (abort speculative)
    "controller_unlock_2_2"
    # This should now show a successful UPSERT
    "controller_show"
@@ -121,18 +141,22 @@ permutation
 # inserts, s1 updates.  With the added complication that transactions
 # don't immediately commit.
 permutation
-   # acquire a number of locks, to control execution flow - the
+   # 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 b) after the
    # insertion of the speculative tuple.
    "controller_locks"
    "controller_show"
+   # Both sessions wait on advisory locks
    "s1_begin" "s2_begin"
    "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 continue
+   # 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"
    # Allow the first session to finish insertion
@@ -147,3 +171,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. also, s1 creates it instead of the controller because ...
+   "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/expected/insert-conflict-specconflict.out b/src/test/isolation/expected/insert-conflict-specconflict.out
index 5726bdb8e8..1fbe7bc4d5 100644
--- a/src/test/isolation/expected/insert-conflict-specconflict.out
+++ b/src/test/isolation/expected/insert-conflict-specconflict.out
@@ -177,3 +177,76 @@ 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           
+
+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 ...>
+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              
+step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3);
+pg_advisory_unlock
+
+t              
+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              
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+step controller_unlock_1_2: SELECT pg_advisory_unlock(1, 2);
+pg_advisory_unlock
+
+t              
+step controller_print_speculative_locks: SELECT locktype,classid,objid,mode,granted FROM pg_locks WHERE locktype='speculative token' order by granted;
+locktype       classid        objid          mode           granted        
+
+speculative token4              0              ShareLock      f              
+speculative token4              0              ExclusiveLock  t              
+step controller_unlock_2_4: SELECT pg_advisory_unlock(2, 4);
+pg_advisory_unlock
+
+t              
+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/specs/insert-conflict-specconflict.spec b/src/test/isolation/specs/insert-conflict-specconflict.spec
index 3a70484fc2..dd38d2642e 100644
--- a/src/test/isolation/specs/insert-conflict-specconflict.spec
+++ b/src/test/isolation/specs/insert-conflict-specconflict.spec
@@ -23,9 +23,16 @@ setup
     RETURN $1;
     END;$$;
 
+    CREATE OR REPLACE FUNCTION blurt_and_lock2(text) RETURNS text IMMUTABLE LANGUAGE plpgsql AS $$
+    BEGIN
+        RAISE NOTICE 'blurt_and_lock1 called for %', $1;
+        PERFORM pg_advisory_xact_lock(current_setting('spec.session')::int, 4);
+    RETURN $1;
+    END;$$;
+
     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 +52,10 @@ 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_print_speculative_locks" { SELECT locktype,classid,objid,mode,granted FROM pg_locks WHERE locktype='speculative token' order by granted; }
 
 session "s1"
 setup
@@ -54,6 +64,8 @@ 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_commit"  { COMMIT; }
 
@@ -70,49 +82,57 @@ step "s2_commit"  { COMMIT; }
 # Test that speculative locks are correctly acquired and released, s2
 # inserts, s1 updates.
 permutation
-   # acquire a number of locks, to control execution flow - the
+   # 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 b) after the
    # insertion of the speculative tuple.
    "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 continue
+   # 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"
-   # Allow the second session to finish insertion
+   # Allow the second session to finish insertion (complete speculative)
    "controller_unlock_2_2"
    # This should now show a successful insertion
    "controller_show"
-   # Allow the first session to finish insertion
+   # Allow the first session to finish insertion (abort speculative)
    "controller_unlock_1_2"
    # 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
+   # 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 b) after the
    # insertion of the speculative tuple.
    "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 continue
+   # 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"
-   # Allow the first session to finish insertion
+   # Allow the first session to finish insertion (complete speculative)
    "controller_unlock_1_2"
    # This should now show a successful insertion
    "controller_show"
-   # Allow the second session to finish insertion
+   # Allow the second session to finish insertion (abort speculative)
    "controller_unlock_2_2"
    # This should now show a successful UPSERT
    "controller_show"
@@ -121,18 +141,22 @@ permutation
 # inserts, s1 updates.  With the added complication that transactions
 # don't immediately commit.
 permutation
-   # acquire a number of locks, to control execution flow - the
+   # 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 b) after the
    # insertion of the speculative tuple.
    "controller_locks"
    "controller_show"
+   # Both sessions wait on advisory locks
    "s1_begin" "s2_begin"
    "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 continue
+   # 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"
    # Allow the first session to finish insertion
@@ -147,3 +171,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. also, s1 creates it instead of the controller because ...
+   "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"

From e8652d872c953ca9bc077027cc34b26b870e5b73 Mon Sep 17 00:00:00 2001
From: csteam <mplageman@pivotal.io>
Date: Wed, 5 Jun 2019 15:24:19 -0700
Subject: [PATCH v2 2/2] Add TOAST case to spec conflict tests

The spec insert-conflict-toast seems to be testing the speculative abort
case -- given two concurrent inserts inserting duplicate values to a
unique index, only one should succeed and, if the other has had a chance
to insert the tuple into the table, this tuple should be killed. The
comment in that test said that it was not deterministic, so, if making
it deterministic required adding additional pg_advisory_locks similar to
what is in the spec insert-conflict-specconflict, combining them made
sense.
---
 .../expected/insert-conflict-specconflict.out | 56 +++++++++++++++++++
 .../specs/insert-conflict-specconflict.spec   | 30 ++++++++++
 2 files changed, 86 insertions(+)

diff --git a/src/test/isolation/expected/insert-conflict-specconflict.out b/src/test/isolation/expected/insert-conflict-specconflict.out
index 2f003ca1bf..a21ed0b165 100644
--- a/src/test/isolation/expected/insert-conflict-specconflict.out
+++ b/src/test/isolation/expected/insert-conflict-specconflict.out
@@ -112,6 +112,62 @@ 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           
+
+step s1_insert_toast: INSERT INTO upserttest VALUES('k2', ctoast_large_val()) ON CONFLICT DO NOTHING; <waiting ...>
+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              
+step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3);
+pg_advisory_unlock
+
+t              
+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              
+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           
diff --git a/src/test/isolation/specs/insert-conflict-specconflict.spec b/src/test/isolation/specs/insert-conflict-specconflict.spec
index 7f29fb9d02..a8b35a0c7b 100644
--- a/src/test/isolation/specs/insert-conflict-specconflict.spec
+++ b/src/test/isolation/specs/insert-conflict-specconflict.spec
@@ -30,6 +30,8 @@ setup
     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 upserttest_key_uniq_idx ON upserttest((blurt_and_lock(key)));
@@ -55,6 +57,7 @@ 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,classid,objid,mode,granted FROM pg_locks WHERE locktype='speculative
 token' ORDER BY granted; }
 
@@ -68,6 +71,7 @@ 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"
@@ -78,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
@@ -130,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.
-- 
2.21.0

From 18958df4fd59870781fc1fe252ce78dbc64f5e0a Mon Sep 17 00:00:00 2001
From: csteam <mplageman@pivotal.io>
Date: Wed, 5 Jun 2019 14:54:33 -0700
Subject: [PATCH v2 1/2] Test speculative wait case

Add a permutation for exercising speculative wait code (from Ashwin's
patch).
Fix typo in previously added comment.
---
 .../expected/insert-conflict-specconflict.out | 72 +++++++++++++++++++
 .../specs/insert-conflict-specconflict.spec   | 68 ++++++++++++++++--
 2 files changed, 136 insertions(+), 4 deletions(-)

diff --git a/src/test/isolation/expected/insert-conflict-specconflict.out b/src/test/isolation/expected/insert-conflict-specconflict.out
index 5726bdb8e8..2f003ca1bf 100644
--- a/src/test/isolation/expected/insert-conflict-specconflict.out
+++ b/src/test/isolation/expected/insert-conflict-specconflict.out
@@ -177,3 +177,75 @@ 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           
+
+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 ...>
+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              
+step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3);
+pg_advisory_unlock
+
+t              
+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              
+step controller_show: SELECT * FROM upserttest;
+key            data           
+
+step controller_unlock_1_2: SELECT pg_advisory_unlock(1, 2);
+pg_advisory_unlock
+
+t              
+step controller_print_speculative_locks: SELECT locktype,classid,objid,mode,granted FROM pg_locks WHERE locktype='speculative
+token' ORDER BY granted;
+locktype       classid        objid          mode           granted        
+
+step controller_unlock_2_4: SELECT pg_advisory_unlock(2, 4);
+pg_advisory_unlock
+
+t              
+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/specs/insert-conflict-specconflict.spec b/src/test/isolation/specs/insert-conflict-specconflict.spec
index 3a70484fc2..7f29fb9d02 100644
--- a/src/test/isolation/specs/insert-conflict-specconflict.spec
+++ b/src/test/isolation/specs/insert-conflict-specconflict.spec
@@ -10,7 +10,7 @@ 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
@@ -23,9 +23,16 @@ setup
     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;
+        PERFORM pg_advisory_xact_lock(current_setting('spec.session')::int, 4);
+    RETURN $1;
+    END;$$;
+
     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 +52,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_print_speculative_locks" { SELECT locktype,classid,objid,mode,granted FROM pg_locks WHERE locktype='speculative
+token' ORDER BY granted; }
 
 session "s1"
 setup
@@ -54,6 +65,8 @@ 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_commit"  { COMMIT; }
 
@@ -92,8 +105,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
@@ -147,3 +160,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"
-- 
2.21.0

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

From a3a7d256928dd8b2c0ff8407981f103ac182c014 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplageman@gmail.com>
Date: Wed, 21 Aug 2019 13:47:23 -0700
Subject: [PATCH v4] 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: Andres Freund <andres@anarazel.de>
Reviewed-by: Taylor Vesely <tvesely@pivotal.io>
---
 .../expected/insert-conflict-specconflict.out | 247 ++++++++++++++----
 .../expected/insert-conflict-toast.out        |  15 --
 src/test/isolation/isolation_schedule         |   1 -
 .../specs/insert-conflict-specconflict.spec   | 114 +++++++-
 .../specs/insert-conflict-toast.spec          |  51 ----
 5 files changed, 305 insertions(+), 123 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..47f5c66539 100644
--- a/src/test/isolation/expected/insert-conflict-specconflict.out
+++ b/src/test/isolation/expected/insert-conflict-specconflict.out
@@ -13,12 +13,12 @@ pg_advisory_locksess           lock
 step controller_show: SELECT * FROM upserttest;
 key            data           
 
-s1: NOTICE:  called for k1
-s1: NOTICE:  blocking 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
-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 ...>
+s1: NOTICE:  blurt_and_lock_123() 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_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; <waiting ...>
+s2: NOTICE:  blurt_and_lock_123() 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_123(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_123() 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_123() 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_123() called for k1
+s1: NOTICE:  acquiring advisory lock on 2
+s1: NOTICE:  blurt_and_lock_123() called for k1
+s1: NOTICE:  acquiring advisory lock on 2
 step s1_upsert: <... completed>
 step controller_show: SELECT * FROM upserttest;
 key            data           
@@ -81,12 +81,12 @@ pg_advisory_locksess           lock
 step controller_show: SELECT * FROM upserttest;
 key            data           
 
-s1: NOTICE:  called for k1
-s1: NOTICE:  blocking 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
-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 ...>
+s1: NOTICE:  blurt_and_lock_123() 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_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; <waiting ...>
+s2: NOTICE:  blurt_and_lock_123() 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_123(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_123() 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_123() 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_123() called for k1
+s2: NOTICE:  acquiring advisory lock on 2
+s2: NOTICE:  blurt_and_lock_123() 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_123() 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_123() 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_123() 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_123() 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_123() called for k2
+s2: NOTICE:  acquiring advisory lock on 2
+s2: NOTICE:  blurt_and_lock_123() 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,12 +219,12 @@ key            data
 
 step s1_begin: BEGIN;
 step s2_begin: BEGIN;
-s1: NOTICE:  called for k1
-s1: NOTICE:  blocking 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
-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 ...>
+s1: NOTICE:  blurt_and_lock_123() 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_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; <waiting ...>
+s2: NOTICE:  blurt_and_lock_123() 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_123(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_123() 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_123() 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_123() called for k1
+s2: NOTICE:  acquiring advisory lock on 2
+s2: NOTICE:  blurt_and_lock_123() 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_123() 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,94 @@ 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_lock_4(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_123() 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_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; <waiting ...>
+s2: NOTICE:  blurt_and_lock_123() 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_123(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_123() 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_123() 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_lock_4() 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_lock_4() called for k1
+s1: NOTICE:  acquiring advisory lock on 4
+s1: NOTICE:  blurt_and_lock_123() called for k1
+s1: NOTICE:  acquiring advisory lock on 2
+s1: NOTICE:  blurt_and_lock_123() 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_123() 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..2696c332a8 100644
--- a/src/test/isolation/specs/insert-conflict-specconflict.spec
+++ b/src/test/isolation/specs/insert-conflict-specconflict.spec
@@ -8,24 +8,34 @@
 
 setup
 {
-     CREATE OR REPLACE FUNCTION blurt_and_lock(text) RETURNS text IMMUTABLE LANGUAGE plpgsql AS $$
+     CREATE OR REPLACE FUNCTION blurt_and_lock_123(text) RETURNS text IMMUTABLE LANGUAGE plpgsql AS $$
      BEGIN
-        RAISE NOTICE 'called for %', $1;
+        RAISE NOTICE 'blurt_and_lock_123() 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_lock_4(text) RETURNS text IMMUTABLE LANGUAGE plpgsql AS $$
+    BEGIN
+        RAISE NOTICE 'blurt_and_lock_4() 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_123(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_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_create_non_unique_index" { CREATE INDEX upserttest_key_idx ON upserttest((blurt_and_lock_4(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_123(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"
@@ -64,14 +81,15 @@ setup
   SET spec.session = 2;
 }
 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_upsert" { INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s2') ON CONFLICT (blurt_and_lock_123(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
 # inserts, s1 updates.
 permutation
    # acquire a number of locks, to control execution flow - the
-   # blurt_and_lock function acquires advisory locks that allow us to
+   # blurt_and_lock_123 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"
@@ -92,11 +110,11 @@ 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
+   # blurt_and_lock_123 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"
@@ -117,12 +135,37 @@ 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_123 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.
 permutation
    # acquire a number of locks, to control execution flow - the
-   # blurt_and_lock function acquires advisory locks that allow us to
+   # blurt_and_lock_123 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"
@@ -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_123 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_lock_4 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

diff --git i/src/test/isolation/expected/insert-conflict-specconflict.out w/src/test/isolation/expected/insert-conflict-specconflict.out
index 47f5c665396..762a5869383 100644
--- i/src/test/isolation/expected/insert-conflict-specconflict.out
+++ w/src/test/isolation/expected/insert-conflict-specconflict.out
@@ -13,10 +13,10 @@ pg_advisory_locksess           lock
 step controller_show: SELECT * FROM upserttest;
 key            data           
 
-s1: NOTICE:  blurt_and_lock_123() called for k1
+s1: NOTICE:  blurt_and_lock_123() called for k1 in session 1
 s1: NOTICE:  acquiring advisory lock on 3
 step s1_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s1') ON CONFLICT (blurt_and_lock_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; <waiting ...>
-s2: NOTICE:  blurt_and_lock_123() called for k1
+s2: NOTICE:  blurt_and_lock_123() called for k1 in session 2
 s2: NOTICE:  acquiring advisory lock on 3
 step s2_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s2') ON CONFLICT (blurt_and_lock_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s2'; <waiting ...>
 step controller_show: SELECT * FROM upserttest;
@@ -34,13 +34,13 @@ step controller_unlock_1_3: SELECT pg_advisory_unlock(1, 3);
 pg_advisory_unlock
 
 t              
-s1: NOTICE:  blurt_and_lock_123() called for k1
+s1: NOTICE:  blurt_and_lock_123() called for k1 in session 1
 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_123() called for k1
+s2: NOTICE:  blurt_and_lock_123() called for k1 in session 2
 s2: NOTICE:  acquiring advisory lock on 2
 step controller_show: SELECT * FROM upserttest;
 key            data           
@@ -58,9 +58,9 @@ step controller_unlock_1_2: SELECT pg_advisory_unlock(1, 2);
 pg_advisory_unlock
 
 t              
-s1: NOTICE:  blurt_and_lock_123() called for k1
+s1: NOTICE:  blurt_and_lock_123() called for k1 in session 1
 s1: NOTICE:  acquiring advisory lock on 2
-s1: NOTICE:  blurt_and_lock_123() called for k1
+s1: NOTICE:  blurt_and_lock_123() called for k1 in session 1
 s1: NOTICE:  acquiring advisory lock on 2
 step s1_upsert: <... completed>
 step controller_show: SELECT * FROM upserttest;
@@ -81,10 +81,10 @@ pg_advisory_locksess           lock
 step controller_show: SELECT * FROM upserttest;
 key            data           
 
-s1: NOTICE:  blurt_and_lock_123() called for k1
+s1: NOTICE:  blurt_and_lock_123() called for k1 in session 1
 s1: NOTICE:  acquiring advisory lock on 3
 step s1_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s1') ON CONFLICT (blurt_and_lock_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; <waiting ...>
-s2: NOTICE:  blurt_and_lock_123() called for k1
+s2: NOTICE:  blurt_and_lock_123() called for k1 in session 2
 s2: NOTICE:  acquiring advisory lock on 3
 step s2_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s2') ON CONFLICT (blurt_and_lock_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s2'; <waiting ...>
 step controller_show: SELECT * FROM upserttest;
@@ -102,13 +102,13 @@ step controller_unlock_1_3: SELECT pg_advisory_unlock(1, 3);
 pg_advisory_unlock
 
 t              
-s1: NOTICE:  blurt_and_lock_123() called for k1
+s1: NOTICE:  blurt_and_lock_123() called for k1 in session 1
 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_123() called for k1
+s2: NOTICE:  blurt_and_lock_123() called for k1 in session 2
 s2: NOTICE:  acquiring advisory lock on 2
 step controller_show: SELECT * FROM upserttest;
 key            data           
@@ -126,9 +126,9 @@ step controller_unlock_2_2: SELECT pg_advisory_unlock(2, 2);
 pg_advisory_unlock
 
 t              
-s2: NOTICE:  blurt_and_lock_123() called for k1
+s2: NOTICE:  blurt_and_lock_123() called for k1 in session 2
 s2: NOTICE:  acquiring advisory lock on 2
-s2: NOTICE:  blurt_and_lock_123() called for k1
+s2: NOTICE:  blurt_and_lock_123() called for k1 in session 2
 s2: NOTICE:  acquiring advisory lock on 2
 step s2_upsert: <... completed>
 step controller_show: SELECT * FROM upserttest;
@@ -149,10 +149,10 @@ pg_advisory_locksess           lock
 step controller_show: SELECT * FROM upserttest;
 key            data           
 
-s1: NOTICE:  blurt_and_lock_123() called for k2
+s1: NOTICE:  blurt_and_lock_123() called for k2 in session 1
 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_123() called for k2
+s2: NOTICE:  blurt_and_lock_123() called for k2 in session 2
 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;
@@ -170,13 +170,13 @@ step controller_unlock_1_3: SELECT pg_advisory_unlock(1, 3);
 pg_advisory_unlock
 
 t              
-s1: NOTICE:  blurt_and_lock_123() called for k2
+s1: NOTICE:  blurt_and_lock_123() called for k2 in session 1
 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_123() called for k2
+s2: NOTICE:  blurt_and_lock_123() called for k2 in session 2
 s2: NOTICE:  acquiring advisory lock on 2
 step controller_show: SELECT * FROM upserttest;
 key            data           
@@ -194,9 +194,9 @@ step controller_unlock_2_2: SELECT pg_advisory_unlock(2, 2);
 pg_advisory_unlock
 
 t              
-s2: NOTICE:  blurt_and_lock_123() called for k2
+s2: NOTICE:  blurt_and_lock_123() called for k2 in session 2
 s2: NOTICE:  acquiring advisory lock on 2
-s2: NOTICE:  blurt_and_lock_123() called for k2
+s2: NOTICE:  blurt_and_lock_123() called for k2 in session 2
 s2: NOTICE:  acquiring advisory lock on 2
 step s2_insert_toast: <... completed>
 step controller_show_count: SELECT COUNT(*) FROM upserttest;
@@ -219,10 +219,10 @@ key            data
 
 step s1_begin: BEGIN;
 step s2_begin: BEGIN;
-s1: NOTICE:  blurt_and_lock_123() called for k1
+s1: NOTICE:  blurt_and_lock_123() called for k1 in session 1
 s1: NOTICE:  acquiring advisory lock on 3
 step s1_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s1') ON CONFLICT (blurt_and_lock_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; <waiting ...>
-s2: NOTICE:  blurt_and_lock_123() called for k1
+s2: NOTICE:  blurt_and_lock_123() called for k1 in session 2
 s2: NOTICE:  acquiring advisory lock on 3
 step s2_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s2') ON CONFLICT (blurt_and_lock_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s2'; <waiting ...>
 step controller_show: SELECT * FROM upserttest;
@@ -240,13 +240,13 @@ step controller_unlock_1_3: SELECT pg_advisory_unlock(1, 3);
 pg_advisory_unlock
 
 t              
-s1: NOTICE:  blurt_and_lock_123() called for k1
+s1: NOTICE:  blurt_and_lock_123() called for k1 in session 1
 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_123() called for k1
+s2: NOTICE:  blurt_and_lock_123() called for k1 in session 2
 s2: NOTICE:  acquiring advisory lock on 2
 step controller_show: SELECT * FROM upserttest;
 key            data           
@@ -263,15 +263,15 @@ step controller_unlock_2_2: SELECT pg_advisory_unlock(2, 2);
 pg_advisory_unlock
 
 t              
-s2: NOTICE:  blurt_and_lock_123() called for k1
+s2: NOTICE:  blurt_and_lock_123() called for k1 in session 2
 s2: NOTICE:  acquiring advisory lock on 2
-s2: NOTICE:  blurt_and_lock_123() called for k1
+s2: NOTICE:  blurt_and_lock_123() called for k1 in session 2
 s2: NOTICE:  acquiring advisory lock on 2
 step controller_show: SELECT * FROM upserttest;
 key            data           
 
 step s1_commit: COMMIT;
-s2: NOTICE:  blurt_and_lock_123() called for k1
+s2: NOTICE:  blurt_and_lock_123() called for k1 in session 2
 s2: NOTICE:  acquiring advisory lock on 2
 step s2_upsert: <... completed>
 step controller_show: SELECT * FROM upserttest;
@@ -284,7 +284,7 @@ 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
+starting permutation: s1_create_non_unique_index s1_confirm_index_order controller_locks controller_show 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_lock_2_4 controller_unlock_2_2 controller_show controller_unlock_1_2 controller_print_speculative_locks controller_unlock_2_4 s2_commit controller_show
 step s1_create_non_unique_index: CREATE INDEX upserttest_key_idx ON upserttest((blurt_and_lock_4(key)));
 step s1_confirm_index_order: SELECT 'upserttest_key_uniq_idx'::regclass::int8 < 'upserttest_key_idx'::regclass::int8;
 ?column?       
@@ -302,10 +302,11 @@ pg_advisory_locksess           lock
 step controller_show: SELECT * FROM upserttest;
 key            data           
 
-s1: NOTICE:  blurt_and_lock_123() called for k1
+step s2_begin: BEGIN;
+s1: NOTICE:  blurt_and_lock_123() called for k1 in session 1
 s1: NOTICE:  acquiring advisory lock on 3
 step s1_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s1') ON CONFLICT (blurt_and_lock_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; <waiting ...>
-s2: NOTICE:  blurt_and_lock_123() called for k1
+s2: NOTICE:  blurt_and_lock_123() called for k1 in session 2
 s2: NOTICE:  acquiring advisory lock on 3
 step s2_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s2') ON CONFLICT (blurt_and_lock_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s2'; <waiting ...>
 step controller_show: SELECT * FROM upserttest;
@@ -323,13 +324,13 @@ step controller_unlock_1_3: SELECT pg_advisory_unlock(1, 3);
 pg_advisory_unlock
 
 t              
-s1: NOTICE:  blurt_and_lock_123() called for k1
+s1: NOTICE:  blurt_and_lock_123() called for k1 in session 1
 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_123() called for k1
+s2: NOTICE:  blurt_and_lock_123() called for k1 in session 2
 s2: NOTICE:  acquiring advisory lock on 2
 step controller_show: SELECT * FROM upserttest;
 key            data           
@@ -342,7 +343,7 @@ step controller_unlock_2_2: SELECT pg_advisory_unlock(2, 2);
 pg_advisory_unlock
 
 t              
-s2: NOTICE:  blurt_and_lock_4() called for k1
+s2: NOTICE:  blurt_and_lock_4() called for k1 in session 2
 s2: NOTICE:  acquiring advisory lock on 4
 step controller_show: SELECT * FROM upserttest;
 key            data           
@@ -351,11 +352,11 @@ step controller_unlock_1_2: SELECT pg_advisory_unlock(1, 2);
 pg_advisory_unlock
 
 t              
-s1: NOTICE:  blurt_and_lock_4() called for k1
+s1: NOTICE:  blurt_and_lock_4() called for k1 in session 1
 s1: NOTICE:  acquiring advisory lock on 4
-s1: NOTICE:  blurt_and_lock_123() called for k1
+s1: NOTICE:  blurt_and_lock_123() called for k1 in session 1
 s1: NOTICE:  acquiring advisory lock on 2
-s1: NOTICE:  blurt_and_lock_123() called for k1
+s1: NOTICE:  blurt_and_lock_123() called for k1 in session 1
 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        
@@ -366,10 +367,13 @@ step controller_unlock_2_4: SELECT pg_advisory_unlock(2, 4);
 pg_advisory_unlock
 
 t              
-s1: NOTICE:  blurt_and_lock_123() called for k1
+s1: NOTICE:  blurt_and_lock_123() called for k1 in session 1
+s1: NOTICE:  acquiring advisory lock on 2
+step s2_upsert: <... completed>
+step s2_commit: COMMIT;
+s1: NOTICE:  blurt_and_lock_123() called for k1 in session 1
 s1: NOTICE:  acquiring advisory lock on 2
 step s1_upsert: <... completed>
-step s2_upsert: <... completed>
 step controller_show: SELECT * FROM upserttest;
 key            data           
 
diff --git i/src/test/isolation/specs/insert-conflict-specconflict.spec w/src/test/isolation/specs/insert-conflict-specconflict.spec
index 2696c332a89..c029b94a9c2 100644
--- i/src/test/isolation/specs/insert-conflict-specconflict.spec
+++ w/src/test/isolation/specs/insert-conflict-specconflict.spec
@@ -10,7 +10,7 @@ setup
 {
      CREATE OR REPLACE FUNCTION blurt_and_lock_123(text) RETURNS text IMMUTABLE LANGUAGE plpgsql AS $$
      BEGIN
-        RAISE NOTICE 'blurt_and_lock_123() called for %', $1;
+        RAISE NOTICE 'blurt_and_lock_123() called for % in session %', $1, current_setting('spec.session')::int;
 
 	-- depending on lock state, wait for lock 2 or 3
         IF pg_try_advisory_xact_lock(current_setting('spec.session')::int, 1) THEN
@@ -25,8 +25,8 @@ setup
 
     CREATE OR REPLACE FUNCTION blurt_and_lock_4(text) RETURNS text IMMUTABLE LANGUAGE plpgsql AS $$
     BEGIN
-        RAISE NOTICE 'blurt_and_lock_4() called for %', $1;
-		RAISE NOTICE 'acquiring advisory lock on 4';
+        RAISE NOTICE 'blurt_and_lock_4() called for % in session %', $1, current_setting('spec.session')::int;
+        RAISE NOTICE 'acquiring advisory lock on 4';
         PERFORM pg_advisory_xact_lock(current_setting('spec.session')::int, 4);
     RETURN $1;
     END;$$;
@@ -210,6 +210,7 @@ permutation
    "s1_confirm_index_order"
    "controller_locks"
    "controller_show"
+   "s2_begin"
    # Both sessions wait on advisory locks
    "s1_upsert" "s2_upsert"
    "controller_show"
@@ -235,5 +236,6 @@ permutation
    # 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"
+   "s2_commit"
    # This should now show a successful UPSERT
    "controller_show"