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