Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
setup
{
CREATE OR REPLACE FUNCTION blurt_and_lock(text) RETURNS text IMMUTABLE LANGUAGE plpgsql AS $$
BEGIN
RAISE NOTICE 'called for %', $1;
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)));
}
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_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'; }
session "s2"
setup { SET spec.session = 2; }
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'; }
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.