1# This test verifies behavior when traversing an update chain during 2# locking an old version of the tuple. There are three tests here: 3# 1. update the tuple, then delete it; a second transaction locks the 4# first version. This should raise an error if the DELETE succeeds, 5# but be allowed to continue if it aborts. 6# 2. Same as (1), except that instead of deleting the tuple, we merely 7# update its key. The behavior should be the same as for (1). 8# 3. Same as (2), except that we update the tuple without modifying its 9# key. In this case, no error should be raised. 10# When run in REPEATABLE READ or SERIALIZABLE transaction isolation levels, all 11# permutations that commit s2 cause a serializability error; all permutations 12# that rollback s2 can get through. 13# 14# We use an advisory lock (which is locked during s1's setup) to let s2 obtain 15# its snapshot early and only allow it to actually traverse the update chain 16# when s1 is done creating it. 17 18setup 19{ 20 DROP TABLE IF EXISTS foo; 21 CREATE TABLE foo ( 22 key int PRIMARY KEY, 23 value int 24 ); 25 26 INSERT INTO foo VALUES (1, 1); 27} 28 29teardown 30{ 31 DROP TABLE foo; 32} 33 34session s1 35# obtain lock on the tuple, traversing its update chain 36step s1l { SELECT * FROM foo WHERE pg_advisory_xact_lock(0) IS NOT NULL AND key = 1 FOR KEY SHARE; } 37 38session s2 39setup { SELECT pg_advisory_lock(0); } 40step s2b { BEGIN; } 41step s2u { UPDATE foo SET value = 2 WHERE key = 1; } 42step s2_blocker1 { DELETE FROM foo; } 43step s2_blocker2 { UPDATE foo SET key = 2 WHERE key = 1; } 44step s2_blocker3 { UPDATE foo SET value = 2 WHERE key = 1; } 45step s2_unlock { SELECT pg_advisory_unlock(0); } 46step s2c { COMMIT; } 47step s2r { ROLLBACK; } 48 49permutation s2b s1l s2u s2_blocker1 s2_unlock s2c 50permutation s2b s1l s2u s2_blocker2 s2_unlock s2c 51permutation s2b s1l s2u s2_blocker3 s2_unlock s2c 52permutation s2b s1l s2u s2_blocker1 s2_unlock s2r 53permutation s2b s1l s2u s2_blocker2 s2_unlock s2r 54permutation s2b s1l s2u s2_blocker3 s2_unlock s2r 55 56permutation s2b s1l s2u s2_blocker1 s2c s2_unlock 57permutation s2b s1l s2u s2_blocker2 s2c s2_unlock 58permutation s2b s1l s2u s2_blocker3 s2c s2_unlock 59permutation s2b s1l s2u s2_blocker1 s2r s2_unlock 60permutation s2b s1l s2u s2_blocker2 s2r s2_unlock 61permutation s2b s1l s2u s2_blocker3 s2r s2_unlock 62