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

Commit08e2edc

Browse files
committed
Add isolation test for INSERT ON CONFLICT speculative insertion failure.
This path previously was not reliably covered. There was someheuristic coverage via insert-conflict-toast.spec, but that test isnot deterministic, and only tested for a somewhat specific bug.Backpatch, as this is a complicated and otherwise untested codepath. Unfortunately 9.5 cannot handle two waiting sessions, and thuscannot execute this test.Triggered by a conversion with Melanie Plageman.Author: Andres FreundDiscussion:https://postgr.es/m/CAAKRu_a7hbyrk=wveHYhr4LbcRnRCG=yPUVoQYB9YO1CdUBE9Q@mail.gmail.comBackpatch: 9.5-
1 parent6d2fba3 commit08e2edc

File tree

3 files changed

+329
-0
lines changed

3 files changed

+329
-0
lines changed
Lines changed: 179 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,179 @@
1+
Parsed test spec with 3 sessions
2+
3+
starting permutation: controller_locks controller_show s1_upsert s2_upsert controller_show controller_unlock_1_1 controller_unlock_2_1 controller_unlock_1_3 controller_unlock_2_3 controller_show controller_unlock_2_2 controller_show controller_unlock_1_2 controller_show
4+
step controller_locks: SELECT pg_advisory_lock(sess, lock), sess, lock FROM generate_series(1, 2) a(sess), generate_series(1,3) b(lock);
5+
pg_advisory_locksess lock
6+
7+
1 1
8+
1 2
9+
1 3
10+
2 1
11+
2 2
12+
2 3
13+
step controller_show: SELECT * FROM upserttest;
14+
key data
15+
16+
step s1_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s1') ON CONFLICT (blurt_and_lock(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; <waiting ...>
17+
step s2_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s2') ON CONFLICT (blurt_and_lock(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s2'; <waiting ...>
18+
step controller_show: SELECT * FROM upserttest;
19+
key data
20+
21+
step controller_unlock_1_1: SELECT pg_advisory_unlock(1, 1);
22+
pg_advisory_unlock
23+
24+
t
25+
step controller_unlock_2_1: SELECT pg_advisory_unlock(2, 1);
26+
pg_advisory_unlock
27+
28+
t
29+
step controller_unlock_1_3: SELECT pg_advisory_unlock(1, 3);
30+
pg_advisory_unlock
31+
32+
t
33+
step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3);
34+
pg_advisory_unlock
35+
36+
t
37+
step controller_show: SELECT * FROM upserttest;
38+
key data
39+
40+
step controller_unlock_2_2: SELECT pg_advisory_unlock(2, 2);
41+
pg_advisory_unlock
42+
43+
t
44+
step s2_upsert: <... completed>
45+
step controller_show: SELECT * FROM upserttest;
46+
key data
47+
48+
k1 inserted s2
49+
step controller_unlock_1_2: SELECT pg_advisory_unlock(1, 2);
50+
pg_advisory_unlock
51+
52+
t
53+
step s1_upsert: <... completed>
54+
step controller_show: SELECT * FROM upserttest;
55+
key data
56+
57+
k1 inserted s2 with conflict update s1
58+
59+
starting permutation: controller_locks controller_show s1_upsert s2_upsert controller_show controller_unlock_1_1 controller_unlock_2_1 controller_unlock_1_3 controller_unlock_2_3 controller_show controller_unlock_1_2 controller_show controller_unlock_2_2 controller_show
60+
step controller_locks: SELECT pg_advisory_lock(sess, lock), sess, lock FROM generate_series(1, 2) a(sess), generate_series(1,3) b(lock);
61+
pg_advisory_locksess lock
62+
63+
1 1
64+
1 2
65+
1 3
66+
2 1
67+
2 2
68+
2 3
69+
step controller_show: SELECT * FROM upserttest;
70+
key data
71+
72+
step s1_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s1') ON CONFLICT (blurt_and_lock(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; <waiting ...>
73+
step s2_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s2') ON CONFLICT (blurt_and_lock(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s2'; <waiting ...>
74+
step controller_show: SELECT * FROM upserttest;
75+
key data
76+
77+
step controller_unlock_1_1: SELECT pg_advisory_unlock(1, 1);
78+
pg_advisory_unlock
79+
80+
t
81+
step controller_unlock_2_1: SELECT pg_advisory_unlock(2, 1);
82+
pg_advisory_unlock
83+
84+
t
85+
step controller_unlock_1_3: SELECT pg_advisory_unlock(1, 3);
86+
pg_advisory_unlock
87+
88+
t
89+
step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3);
90+
pg_advisory_unlock
91+
92+
t
93+
step controller_show: SELECT * FROM upserttest;
94+
key data
95+
96+
step controller_unlock_1_2: SELECT pg_advisory_unlock(1, 2);
97+
pg_advisory_unlock
98+
99+
t
100+
step s1_upsert: <... completed>
101+
step controller_show: SELECT * FROM upserttest;
102+
key data
103+
104+
k1 inserted s1
105+
step controller_unlock_2_2: SELECT pg_advisory_unlock(2, 2);
106+
pg_advisory_unlock
107+
108+
t
109+
step s2_upsert: <... completed>
110+
step controller_show: SELECT * FROM upserttest;
111+
key data
112+
113+
k1 inserted s1 with conflict update s2
114+
115+
starting permutation: controller_locks controller_show s1_begin s2_begin s1_upsert s2_upsert controller_show controller_unlock_1_1 controller_unlock_2_1 controller_unlock_1_3 controller_unlock_2_3 controller_show controller_unlock_1_2 controller_show controller_unlock_2_2 controller_show s1_commit controller_show s2_commit controller_show
116+
step controller_locks: SELECT pg_advisory_lock(sess, lock), sess, lock FROM generate_series(1, 2) a(sess), generate_series(1,3) b(lock);
117+
pg_advisory_locksess lock
118+
119+
1 1
120+
1 2
121+
1 3
122+
2 1
123+
2 2
124+
2 3
125+
step controller_show: SELECT * FROM upserttest;
126+
key data
127+
128+
step s1_begin: BEGIN;
129+
step s2_begin: BEGIN;
130+
step s1_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s1') ON CONFLICT (blurt_and_lock(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; <waiting ...>
131+
step s2_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s2') ON CONFLICT (blurt_and_lock(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s2'; <waiting ...>
132+
step controller_show: SELECT * FROM upserttest;
133+
key data
134+
135+
step controller_unlock_1_1: SELECT pg_advisory_unlock(1, 1);
136+
pg_advisory_unlock
137+
138+
t
139+
step controller_unlock_2_1: SELECT pg_advisory_unlock(2, 1);
140+
pg_advisory_unlock
141+
142+
t
143+
step controller_unlock_1_3: SELECT pg_advisory_unlock(1, 3);
144+
pg_advisory_unlock
145+
146+
t
147+
step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3);
148+
pg_advisory_unlock
149+
150+
t
151+
step controller_show: SELECT * FROM upserttest;
152+
key data
153+
154+
step controller_unlock_1_2: SELECT pg_advisory_unlock(1, 2);
155+
pg_advisory_unlock
156+
157+
t
158+
step s1_upsert: <... completed>
159+
step controller_show: SELECT * FROM upserttest;
160+
key data
161+
162+
step controller_unlock_2_2: SELECT pg_advisory_unlock(2, 2);
163+
pg_advisory_unlock
164+
165+
t
166+
step controller_show: SELECT * FROM upserttest;
167+
key data
168+
169+
step s1_commit: COMMIT;
170+
step s2_upsert: <... completed>
171+
step controller_show: SELECT * FROM upserttest;
172+
key data
173+
174+
k1 inserted s1
175+
step s2_commit: COMMIT;
176+
step controller_show: SELECT * FROM upserttest;
177+
key data
178+
179+
k1 inserted s1 with conflict update s2

‎src/test/isolation/isolation_schedule

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,7 @@ test: insert-conflict-do-update
3636
test: insert-conflict-do-update-2
3737
test: insert-conflict-do-update-3
3838
test: insert-conflict-toast
39+
test: insert-conflict-specconflict
3940
test: delete-abort-savept
4041
test: delete-abort-savept-2
4142
test: aborted-keyrevoke
Lines changed: 149 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,149 @@
1+
# INSERT ... ON CONFLICT test verifying that speculative insertion
2+
# failures are handled
3+
#
4+
# Does this by using advisory locks controlling progress of
5+
# insertions. By waiting when building the index keys, it's possible
6+
# to schedule concurrent INSERT ON CONFLICTs so that there will always
7+
# be a speculative conflict.
8+
9+
setup
10+
{
11+
CREATEORREPLACEFUNCTIONblurt_and_lock(text)RETURNStextIMMUTABLELANGUAGEplpgsqlAS $$
12+
BEGIN
13+
RAISENOTICE'called for %', $1;
14+
15+
--dependingonlockstate,waitforlock2or3
16+
IFpg_try_advisory_xact_lock(current_setting('spec.session')::int,1)THEN
17+
RAISENOTICE'blocking 2';
18+
PERFORMpg_advisory_xact_lock(current_setting('spec.session')::int,2);
19+
ELSE
20+
RAISENOTICE'blocking 3';
21+
PERFORMpg_advisory_xact_lock(current_setting('spec.session')::int,3);
22+
ENDIF;
23+
RETURN $1;
24+
END;$$;
25+
26+
CREATETABLEupserttest(keytext,datatext);
27+
28+
CREATEUNIQUEINDEXONupserttest((blurt_and_lock(key)));
29+
}
30+
31+
teardown
32+
{
33+
DROPTABLEupserttest;
34+
}
35+
36+
session"controller"
37+
setup
38+
{
39+
SETdefault_transaction_isolation='read committed';
40+
}
41+
step"controller_locks"{SELECTpg_advisory_lock(sess,lock),sess,lockFROMgenerate_series(1,2)a(sess),generate_series(1,3)b(lock);}
42+
step"controller_unlock_1_1"{SELECTpg_advisory_unlock(1,1);}
43+
step"controller_unlock_2_1"{SELECTpg_advisory_unlock(2,1);}
44+
step"controller_unlock_1_2"{SELECTpg_advisory_unlock(1,2);}
45+
step"controller_unlock_2_2"{SELECTpg_advisory_unlock(2,2);}
46+
step"controller_unlock_1_3"{SELECTpg_advisory_unlock(1,3);}
47+
step"controller_unlock_2_3"{SELECTpg_advisory_unlock(2,3);}
48+
step"controller_show"{SELECT *FROMupserttest;}
49+
50+
session"s1"
51+
setup
52+
{
53+
SETdefault_transaction_isolation='read committed';
54+
SETspec.session=1;
55+
}
56+
step"s1_begin"{BEGIN;}
57+
step"s1_upsert"{INSERTINTOupserttest(key,data)VALUES('k1','inserted s1')ONCONFLICT(blurt_and_lock(key))DOUPDATESETdata=upserttest.data ||' with conflict update s1';}
58+
step"s1_commit"{COMMIT;}
59+
60+
session"s2"
61+
setup
62+
{
63+
SETdefault_transaction_isolation='read committed';
64+
SETspec.session=2;
65+
}
66+
step"s2_begin"{BEGIN;}
67+
step"s2_upsert"{INSERTINTOupserttest(key,data)VALUES('k1','inserted s2')ONCONFLICT(blurt_and_lock(key))DOUPDATESETdata=upserttest.data ||' with conflict update s2';}
68+
step"s2_commit"{COMMIT;}
69+
70+
# Test that speculative locks are correctly acquired and released, s2
71+
# inserts, s1 updates.
72+
permutation
73+
# acquire a number of locks, to control execution flow - the
74+
# blurt_and_lock function acquires advisory locks that allow us to
75+
# continue after a) the optimistic conflict probe b) after the
76+
# insertion of the speculative tuple.
77+
"controller_locks"
78+
"controller_show"
79+
"s1_upsert""s2_upsert"
80+
"controller_show"
81+
# Switch both sessions to wait on the other lock next time (the speculative insertion)
82+
"controller_unlock_1_1""controller_unlock_2_1"
83+
# Allow both sessions to continue
84+
"controller_unlock_1_3""controller_unlock_2_3"
85+
"controller_show"
86+
# Allow the second session to finish insertion
87+
"controller_unlock_2_2"
88+
# This should now show a successful insertion
89+
"controller_show"
90+
# Allow the first session to finish insertion
91+
"controller_unlock_1_2"
92+
# This should now show a successful UPSERT
93+
"controller_show"
94+
95+
# Test that speculative locks are correctly acquired and released, s2
96+
# inserts, s1 updates.
97+
permutation
98+
# acquire a number of locks, to control execution flow - the
99+
# blurt_and_lock function acquires advisory locks that allow us to
100+
# continue after a) the optimistic conflict probe b) after the
101+
# insertion of the speculative tuple.
102+
"controller_locks"
103+
"controller_show"
104+
"s1_upsert""s2_upsert"
105+
"controller_show"
106+
# Switch both sessions to wait on the other lock next time (the speculative insertion)
107+
"controller_unlock_1_1""controller_unlock_2_1"
108+
# Allow both sessions to continue
109+
"controller_unlock_1_3""controller_unlock_2_3"
110+
"controller_show"
111+
# Allow the first session to finish insertion
112+
"controller_unlock_1_2"
113+
# This should now show a successful insertion
114+
"controller_show"
115+
# Allow the second session to finish insertion
116+
"controller_unlock_2_2"
117+
# This should now show a successful UPSERT
118+
"controller_show"
119+
120+
# Test that speculative locks are correctly acquired and released, s2
121+
# inserts, s1 updates. With the added complication that transactions
122+
# don't immediately commit.
123+
permutation
124+
# acquire a number of locks, to control execution flow - the
125+
# blurt_and_lock function acquires advisory locks that allow us to
126+
# continue after a) the optimistic conflict probe b) after the
127+
# insertion of the speculative tuple.
128+
"controller_locks"
129+
"controller_show"
130+
"s1_begin""s2_begin"
131+
"s1_upsert""s2_upsert"
132+
"controller_show"
133+
# Switch both sessions to wait on the other lock next time (the speculative insertion)
134+
"controller_unlock_1_1""controller_unlock_2_1"
135+
# Allow both sessions to continue
136+
"controller_unlock_1_3""controller_unlock_2_3"
137+
"controller_show"
138+
# Allow the first session to finish insertion
139+
"controller_unlock_1_2"
140+
# But the change isn't visible yet, nor should the second session continue
141+
"controller_show"
142+
# Allow the second session to finish insertion, but it's blocked
143+
"controller_unlock_2_2"
144+
"controller_show"
145+
# But committing should unblock
146+
"s1_commit"
147+
"controller_show"
148+
"s2_commit"
149+
"controller_show"

0 commit comments

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