Adding upstream version 14.5.upstream/14.5upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 259 insertions, 0 deletions

diff --git a/src/test/isolation/specs/insert-conflict-specconflict.spec b/src/test/isolation/specs/insert-conflict-specconflict.spec
new file mode 100644
index 0000000..0d55a01
--- /dev/null
+++ b/src/test/isolation/specs/insert-conflict-specconflict.spec
@@ -0,0 +1,259 @@
+# INSERT ... ON CONFLICT test verifying that speculative insertion
+# failures are handled
+#
+# Does this by using advisory locks controlling progress of
+# insertions. By waiting when building the index keys, it's possible
+# to schedule concurrent INSERT ON CONFLICTs so that there will always
+# be a speculative conflict.
+
+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 % 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
+            RAISE NOTICE 'acquiring advisory lock on 2';
+            PERFORM pg_advisory_xact_lock(current_setting('spec.session')::int, 2);
+        ELSE
+            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 % 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;$$;
+
+    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_123(key)));
+}
+
+teardown
+{
+    DROP TABLE upserttest;
+}
+
+session controller
+setup
+{
+    SET default_transaction_isolation = 'read committed';
+}
+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_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 pa.application_name, locktype, mode, granted
+    FROM pg_locks pl JOIN pg_stat_activity pa USING (pid)
+    WHERE
+        locktype IN ('spectoken', 'transactionid')
+        AND pa.datname = current_database()
+        AND pa.application_name LIKE 'isolation/insert-conflict-specconflict/s%'
+    ORDER BY 1, 2, 3, 4;
+}
+
+session s1
+setup
+{
+    SET default_transaction_isolation = 'read committed';
+    SET spec.session = 1;
+}
+step s1_begin  { BEGIN; }
+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; }
+step s1_noop    { }
+
+session s2
+setup
+{
+    SET default_transaction_isolation = 'read committed';
+    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_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; }
+step s2_noop    { }
+
+# 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_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_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, s1
+# inserts, s2 updates.
+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_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 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_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_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
+
+# 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
+   s2_begin
+   # Both sessions wait on advisory locks
+   # (but don't show s2_upsert as complete till we've seen all of s1's notices)
+   s1_upsert s2_upsert (s1_upsert notices 10)
+   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 on speculative lock, but proceed to wait on the
+   # transaction to finish.  The no-op step is needed to ensure that
+   # we don't advance to the reporting step until s2_upsert has completed.
+   controller_unlock_2_4 s2_noop
+   # Should report that s1 is now waiting for s2 to commit
+   controller_print_speculative_locks
+   # Once s2 commits, s1 is finally free to continue to update
+   s2_commit s1_noop
+   # This should now show a successful UPSERT
+   controller_show
+   # Ensure no unexpected locks survive
+   controller_print_speculative_locks