Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit00cb86e

Browse files
committed
Add isolation tests for snapshot behavior in ri_triggers.c
They are to check the behavior of RI_FKey_check() andri_Check_Pk_Match(). A test case whereby RI_FKey_check() queries apartitioned PK table under REPEATABLE READ isolation produces wrongoutput due to a bug of the partition-descriptor logic and that is notedas such in the comment in the test. A subsequent commit will fix thebug and replace the buggy output by the correct one.Author: Amit Langote <amitlangote09@gmail.com>Discussion:https://postgr.es/m/1627848.1636676261@sss.pgh.pa.us
1 parent2c7ea57 commit00cb86e

File tree

3 files changed

+186
-0
lines changed

3 files changed

+186
-0
lines changed
Lines changed: 124 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,124 @@
1+
Parsed test spec with 2 sessions
2+
3+
starting permutation: s1brr s2brc s2ip2 s1sp s2c s1sp s1ifp2 s1c s1sfp
4+
step s1brr: BEGIN ISOLATION LEVEL REPEATABLE READ;
5+
step s2brc: BEGIN ISOLATION LEVEL READ COMMITTED;
6+
step s2ip2: INSERT INTO pk_noparted VALUES (2);
7+
step s1sp: SELECT * FROM pk_noparted;
8+
a
9+
-
10+
1
11+
(1 row)
12+
13+
step s2c: COMMIT;
14+
step s1sp: SELECT * FROM pk_noparted;
15+
a
16+
-
17+
1
18+
(1 row)
19+
20+
step s1ifp2: INSERT INTO fk_parted_pk VALUES (2);
21+
ERROR: insert or update on table "fk_parted_pk_2" violates foreign key constraint "fk_parted_pk_a_fkey"
22+
step s1c: COMMIT;
23+
step s1sfp: SELECT * FROM fk_parted_pk;
24+
a
25+
-
26+
1
27+
(1 row)
28+
29+
30+
starting permutation: s2ip2 s2brr s1brc s1ifp2 s2sfp s1c s2sfp s2ifn2 s2c s2sfn
31+
step s2ip2: INSERT INTO pk_noparted VALUES (2);
32+
step s2brr: BEGIN ISOLATION LEVEL REPEATABLE READ;
33+
step s1brc: BEGIN ISOLATION LEVEL READ COMMITTED;
34+
step s1ifp2: INSERT INTO fk_parted_pk VALUES (2);
35+
step s2sfp: SELECT * FROM fk_parted_pk;
36+
a
37+
-
38+
1
39+
(1 row)
40+
41+
step s1c: COMMIT;
42+
step s2sfp: SELECT * FROM fk_parted_pk;
43+
a
44+
-
45+
1
46+
(1 row)
47+
48+
step s2ifn2: INSERT INTO fk_noparted VALUES (2);
49+
step s2c: COMMIT;
50+
step s2sfn: SELECT * FROM fk_noparted;
51+
a
52+
-
53+
1
54+
2
55+
(2 rows)
56+
57+
58+
starting permutation: s1brc s2brc s2ip2 s1sp s2c s1sp s1ifp2 s2brc s2sfp s1c s1sfp s2ifn2 s2c s2sfn
59+
step s1brc: BEGIN ISOLATION LEVEL READ COMMITTED;
60+
step s2brc: BEGIN ISOLATION LEVEL READ COMMITTED;
61+
step s2ip2: INSERT INTO pk_noparted VALUES (2);
62+
step s1sp: SELECT * FROM pk_noparted;
63+
a
64+
-
65+
1
66+
(1 row)
67+
68+
step s2c: COMMIT;
69+
step s1sp: SELECT * FROM pk_noparted;
70+
a
71+
-
72+
1
73+
2
74+
(2 rows)
75+
76+
step s1ifp2: INSERT INTO fk_parted_pk VALUES (2);
77+
step s2brc: BEGIN ISOLATION LEVEL READ COMMITTED;
78+
step s2sfp: SELECT * FROM fk_parted_pk;
79+
a
80+
-
81+
1
82+
(1 row)
83+
84+
step s1c: COMMIT;
85+
step s1sfp: SELECT * FROM fk_parted_pk;
86+
a
87+
-
88+
1
89+
2
90+
(2 rows)
91+
92+
step s2ifn2: INSERT INTO fk_noparted VALUES (2);
93+
step s2c: COMMIT;
94+
step s2sfn: SELECT * FROM fk_noparted;
95+
a
96+
-
97+
1
98+
2
99+
(2 rows)
100+
101+
102+
starting permutation: s1brr s1dfp s1ifp1 s1c s1sfn
103+
step s1brr: BEGIN ISOLATION LEVEL REPEATABLE READ;
104+
step s1dfp: DELETE FROM fk_parted_pk WHERE a = 1;
105+
step s1ifp1: INSERT INTO fk_parted_pk VALUES (1);
106+
step s1c: COMMIT;
107+
step s1sfn: SELECT * FROM fk_noparted;
108+
a
109+
-
110+
1
111+
(1 row)
112+
113+
114+
starting permutation: s1brc s1dfp s1ifp1 s1c s1sfn
115+
step s1brc: BEGIN ISOLATION LEVEL READ COMMITTED;
116+
step s1dfp: DELETE FROM fk_parted_pk WHERE a = 1;
117+
step s1ifp1: INSERT INTO fk_parted_pk VALUES (1);
118+
step s1c: COMMIT;
119+
step s1sfn: SELECT * FROM fk_noparted;
120+
a
121+
-
122+
1
123+
(1 row)
124+

‎src/test/isolation/isolation_schedule

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -33,6 +33,7 @@ test: fk-deadlock
3333
test: fk-deadlock2
3434
test: fk-partitioned-1
3535
test: fk-partitioned-2
36+
test: fk-snapshot
3637
test: eval-plan-qual
3738
test: eval-plan-qual-trigger
3839
test: lock-update-delete
Lines changed: 61 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,61 @@
1+
setup
2+
{
3+
CREATETABLEpk_noparted (
4+
aintPRIMARYKEY
5+
);
6+
7+
CREATETABLEfk_parted_pk (
8+
aintPRIMARYKEYREFERENCESpk_nopartedONDELETECASCADE
9+
)PARTITIONBYLIST (a);
10+
CREATETABLEfk_parted_pk_1PARTITIONOFfk_parted_pkFORVALUESIN (1);
11+
CREATETABLEfk_parted_pk_2PARTITIONOFfk_parted_pkFORVALUESIN (2);
12+
13+
CREATETABLEfk_noparted (
14+
aintREFERENCESfk_parted_pkONDELETENOACTIONINITIALLYDEFERRED
15+
);
16+
INSERTINTOpk_nopartedVALUES (1);
17+
INSERTINTOfk_parted_pkVALUES (1);
18+
INSERTINTOfk_nopartedVALUES (1);
19+
}
20+
21+
teardown
22+
{
23+
DROPTABLEpk_noparted,fk_parted_pk,fk_noparted;
24+
}
25+
26+
sessions1
27+
steps1brr{BEGINISOLATIONLEVELREPEATABLEREAD; }
28+
steps1brc{BEGINISOLATIONLEVELREADCOMMITTED; }
29+
steps1ifp2{INSERTINTOfk_parted_pkVALUES (2); }
30+
steps1ifp1{INSERTINTOfk_parted_pkVALUES (1); }
31+
steps1dfp{DELETEFROMfk_parted_pkWHEREa=1; }
32+
steps1c{COMMIT; }
33+
steps1sfp{SELECT*FROMfk_parted_pk; }
34+
steps1sp{SELECT*FROMpk_noparted; }
35+
steps1sfn{SELECT*FROMfk_noparted; }
36+
37+
sessions2
38+
steps2brr{BEGINISOLATIONLEVELREPEATABLEREAD; }
39+
steps2brc{BEGINISOLATIONLEVELREADCOMMITTED; }
40+
steps2ip2{INSERTINTOpk_nopartedVALUES (2); }
41+
steps2ifn2{INSERTINTOfk_nopartedVALUES (2); }
42+
steps2c{COMMIT; }
43+
steps2sfp{SELECT*FROMfk_parted_pk; }
44+
steps2sfn{SELECT*FROMfk_noparted; }
45+
46+
# inserting into referencing tables in transaction-snapshot mode
47+
# PK table is non-partitioned
48+
permutations1brrs2brcs2ip2s1sps2cs1sps1ifp2s1cs1sfp
49+
# PK table is partitioned: buggy, because s2's serialization transaction can
50+
# see the uncommitted row thanks to the latest snapshot taken for
51+
# partition lookup to work correctly also ends up getting used by the PK index
52+
# scan
53+
permutations2ip2s2brrs1brcs1ifp2s2sfps1cs2sfps2ifn2s2cs2sfn
54+
55+
# inserting into referencing tables in up-to-date snapshot mode
56+
permutations1brcs2brcs2ip2s1sps2cs1sps1ifp2s2brcs2sfps1cs1sfps2ifn2s2cs2sfn
57+
58+
# deleting a referenced row and then inserting again in the same transaction; works
59+
# the same no matter the snapshot mode
60+
permutations1brrs1dfps1ifp1s1cs1sfn
61+
permutations1brcs1dfps1ifp1s1cs1sfn

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp