# 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"