blob: 9fad57e7689ad7289e66cc1a38c4ba3407970e12 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
|
setup
{
CREATE TABLE pk_noparted (
a int PRIMARY KEY
);
CREATE TABLE fk_parted_pk (
a int PRIMARY KEY REFERENCES pk_noparted ON DELETE CASCADE
) PARTITION BY LIST (a);
CREATE TABLE fk_parted_pk_1 PARTITION OF fk_parted_pk FOR VALUES IN (1);
CREATE TABLE fk_parted_pk_2 PARTITION OF fk_parted_pk FOR VALUES IN (2);
CREATE TABLE fk_noparted (
a int REFERENCES fk_parted_pk ON DELETE NO ACTION INITIALLY DEFERRED
);
CREATE TABLE fk_noparted_sn (
a int REFERENCES pk_noparted ON DELETE SET NULL
);
INSERT INTO pk_noparted VALUES (1);
INSERT INTO fk_parted_pk VALUES (1);
INSERT INTO fk_noparted VALUES (1);
}
teardown
{
DROP TABLE pk_noparted, fk_parted_pk, fk_noparted, fk_noparted_sn;
}
session s1
step s1brr { BEGIN ISOLATION LEVEL REPEATABLE READ; }
step s1brc { BEGIN ISOLATION LEVEL READ COMMITTED; }
step s1ifp2 { INSERT INTO fk_parted_pk VALUES (2); }
step s1ifp1 { INSERT INTO fk_parted_pk VALUES (1); }
step s1ifn2 { INSERT INTO fk_noparted_sn VALUES (2); }
step s1dfp { DELETE FROM fk_parted_pk WHERE a = 1; }
step s1c { COMMIT; }
step s1sfp { SELECT * FROM fk_parted_pk; }
step s1sp { SELECT * FROM pk_noparted; }
step s1sfn { SELECT * FROM fk_noparted; }
session s2
step s2brr { BEGIN ISOLATION LEVEL REPEATABLE READ; }
step s2brc { BEGIN ISOLATION LEVEL READ COMMITTED; }
step s2ip2 { INSERT INTO pk_noparted VALUES (2); }
step s2dp2 { DELETE FROM pk_noparted WHERE a = 2; }
step s2ifn2 { INSERT INTO fk_noparted VALUES (2); }
step s2c { COMMIT; }
step s2sfp { SELECT * FROM fk_parted_pk; }
step s2sfn { SELECT * FROM fk_noparted; }
# inserting into referencing tables in transaction-snapshot mode
# PK table is non-partitioned
permutation s1brr s2brc s2ip2 s1sp s2c s1sp s1ifp2 s1c s1sfp
# PK table is partitioned: buggy, because s2's serialization transaction can
# see the uncommitted row thanks to the latest snapshot taken for
# partition lookup to work correctly also ends up getting used by the PK index
# scan
permutation s2ip2 s2brr s1brc s1ifp2 s2sfp s1c s2sfp s2ifn2 s2c s2sfn
# inserting into referencing tables in up-to-date snapshot mode
permutation s1brc s2brc s2ip2 s1sp s2c s1sp s1ifp2 s2brc s2sfp s1c s1sfp s2ifn2 s2c s2sfn
# deleting a referenced row and then inserting again in the same transaction; works
# the same no matter the snapshot mode
permutation s1brr s1dfp s1ifp1 s1c s1sfn
permutation s1brc s1dfp s1ifp1 s1c s1sfn
# trying to delete a row through DELETE CASCADE, whilst that row is deleted
# in a concurrent transaction
permutation s2ip2 s1brr s1ifp2 s2brr s2dp2 s1c s2c
# trying to update a row through DELETE SET NULL, whilst that row is deleted
# in a concurrent transaction
permutation s2ip2 s1brr s1ifn2 s2brr s2dp2 s1c s2c
|
