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

Commit43e0841

Browse files
committed
Test additional speculative conflict scenarios.
Previously, the speculative insert tests did not cover the case when atuple t is inserted into a table with a unique index on a column butbefore it can insert into the index, a concurrent transaction hasinserted a conflicting value into the index and the insertion of tuple tmust be aborted.The basic permutation is one session successfully inserts into the tableand an associated unique index while a concurrent session successfullyinserts into the table but discovers a conflict before inserting intothe index and must abort the insertion.Several variants on this include:- swap which session is successful- first session insert transaction does not commit, so second sessionmust wait on a transaction lock- first session insert does not "complete", so second session must waiton a speculative insertion lockAlso, refactor the existing TOAST table upsert test to be in the samespec and reuse the steps.Author: Melanie Plageman, Ashwin Agrawal, Andres FreundReviewed-by: Andres Freund, Taylor VeselyDiscussion:https://postgr.es/m/CAAKRu_ZRmxy_OEryfY3G8Zp01ouhgw59_-_Cm8n7LzRH5BAvng@mail.gmail.com
1 parentbe6221e commit43e0841

File tree

5 files changed

+356
-128
lines changed

5 files changed

+356
-128
lines changed

‎src/test/isolation/expected/insert-conflict-specconflict.out

Lines changed: 233 additions & 44 deletions
Original file line numberDiff line numberDiff line change
@@ -13,12 +13,12 @@ pg_advisory_locksess lock
1313
step controller_show: SELECT * FROM upserttest;
1414
key data
1515

16-
s1: NOTICE: called for k1
17-
s1: NOTICE:blocking 3
18-
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 ...>
19-
s2: NOTICE: called for k1
20-
s2: NOTICE:blocking 3
21-
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 ...>
16+
s1: NOTICE:blurt_and_lock_123()called for k1 in session 1
17+
s1: NOTICE:acquiring advisory lock on 3
18+
step s1_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s1') ON CONFLICT (blurt_and_lock_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; <waiting ...>
19+
s2: NOTICE:blurt_and_lock_123()called for k1 in session 2
20+
s2: NOTICE:acquiring advisory lock on 3
21+
step s2_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s2') ON CONFLICT (blurt_and_lock_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s2'; <waiting ...>
2222
step controller_show: SELECT * FROM upserttest;
2323
key data
2424

@@ -34,14 +34,14 @@ step controller_unlock_1_3: SELECT pg_advisory_unlock(1, 3);
3434
pg_advisory_unlock
3535

3636
t
37-
s1: NOTICE: called for k1
38-
s1: NOTICE:blocking 2
37+
s1: NOTICE:blurt_and_lock_123()called for k1 in session 1
38+
s1: NOTICE:acquiring advisory lock on 2
3939
step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3);
4040
pg_advisory_unlock
4141

4242
t
43-
s2: NOTICE: called for k1
44-
s2: NOTICE:blocking 2
43+
s2: NOTICE:blurt_and_lock_123()called for k1 in session 2
44+
s2: NOTICE:acquiring advisory lock on 2
4545
step controller_show: SELECT * FROM upserttest;
4646
key data
4747

@@ -58,10 +58,10 @@ step controller_unlock_1_2: SELECT pg_advisory_unlock(1, 2);
5858
pg_advisory_unlock
5959

6060
t
61-
s1: NOTICE: called for k1
62-
s1: NOTICE:blocking 2
63-
s1: NOTICE: called for k1
64-
s1: NOTICE:blocking 2
61+
s1: NOTICE:blurt_and_lock_123()called for k1 in session 1
62+
s1: NOTICE:acquiring advisory lock on 2
63+
s1: NOTICE:blurt_and_lock_123()called for k1 in session 1
64+
s1: NOTICE:acquiring advisory lock on 2
6565
step s1_upsert: <... completed>
6666
step controller_show: SELECT * FROM upserttest;
6767
key data
@@ -81,12 +81,12 @@ pg_advisory_locksess lock
8181
step controller_show: SELECT * FROM upserttest;
8282
key data
8383

84-
s1: NOTICE: called for k1
85-
s1: NOTICE:blocking 3
86-
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 ...>
87-
s2: NOTICE: called for k1
88-
s2: NOTICE:blocking 3
89-
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 ...>
84+
s1: NOTICE:blurt_and_lock_123()called for k1 in session 1
85+
s1: NOTICE:acquiring advisory lock on 3
86+
step s1_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s1') ON CONFLICT (blurt_and_lock_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; <waiting ...>
87+
s2: NOTICE:blurt_and_lock_123()called for k1 in session 2
88+
s2: NOTICE:acquiring advisory lock on 3
89+
step s2_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s2') ON CONFLICT (blurt_and_lock_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s2'; <waiting ...>
9090
step controller_show: SELECT * FROM upserttest;
9191
key data
9292

@@ -102,14 +102,14 @@ step controller_unlock_1_3: SELECT pg_advisory_unlock(1, 3);
102102
pg_advisory_unlock
103103

104104
t
105-
s1: NOTICE: called for k1
106-
s1: NOTICE:blocking 2
105+
s1: NOTICE:blurt_and_lock_123()called for k1 in session 1
106+
s1: NOTICE:acquiring advisory lock on 2
107107
step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3);
108108
pg_advisory_unlock
109109

110110
t
111-
s2: NOTICE: called for k1
112-
s2: NOTICE:blocking 2
111+
s2: NOTICE:blurt_and_lock_123()called for k1 in session 2
112+
s2: NOTICE:acquiring advisory lock on 2
113113
step controller_show: SELECT * FROM upserttest;
114114
key data
115115

@@ -126,16 +126,84 @@ step controller_unlock_2_2: SELECT pg_advisory_unlock(2, 2);
126126
pg_advisory_unlock
127127

128128
t
129-
s2: NOTICE: called for k1
130-
s2: NOTICE:blocking 2
131-
s2: NOTICE: called for k1
132-
s2: NOTICE:blocking 2
129+
s2: NOTICE:blurt_and_lock_123()called for k1 in session 2
130+
s2: NOTICE:acquiring advisory lock on 2
131+
s2: NOTICE:blurt_and_lock_123()called for k1 in session 2
132+
s2: NOTICE:acquiring advisory lock on 2
133133
step s2_upsert: <... completed>
134134
step controller_show: SELECT * FROM upserttest;
135135
key data
136136

137137
k1 inserted s1 with conflict update s2
138138

139+
starting permutation: controller_locks controller_show s1_insert_toast s2_insert_toast 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_count controller_unlock_2_2 controller_show_count
140+
step controller_locks: SELECT pg_advisory_lock(sess, lock), sess, lock FROM generate_series(1, 2) a(sess), generate_series(1,3) b(lock);
141+
pg_advisory_locksess lock
142+
143+
1 1
144+
1 2
145+
1 3
146+
2 1
147+
2 2
148+
2 3
149+
step controller_show: SELECT * FROM upserttest;
150+
key data
151+
152+
s1: NOTICE: blurt_and_lock_123() called for k2 in session 1
153+
s1: NOTICE: acquiring advisory lock on 3
154+
step s1_insert_toast: INSERT INTO upserttest VALUES('k2', ctoast_large_val()) ON CONFLICT DO NOTHING; <waiting ...>
155+
s2: NOTICE: blurt_and_lock_123() called for k2 in session 2
156+
s2: NOTICE: acquiring advisory lock on 3
157+
step s2_insert_toast: INSERT INTO upserttest VALUES('k2', ctoast_large_val()) ON CONFLICT DO NOTHING; <waiting ...>
158+
step controller_show: SELECT * FROM upserttest;
159+
key data
160+
161+
step controller_unlock_1_1: SELECT pg_advisory_unlock(1, 1);
162+
pg_advisory_unlock
163+
164+
t
165+
step controller_unlock_2_1: SELECT pg_advisory_unlock(2, 1);
166+
pg_advisory_unlock
167+
168+
t
169+
step controller_unlock_1_3: SELECT pg_advisory_unlock(1, 3);
170+
pg_advisory_unlock
171+
172+
t
173+
s1: NOTICE: blurt_and_lock_123() called for k2 in session 1
174+
s1: NOTICE: acquiring advisory lock on 2
175+
step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3);
176+
pg_advisory_unlock
177+
178+
t
179+
s2: NOTICE: blurt_and_lock_123() called for k2 in session 2
180+
s2: NOTICE: acquiring advisory lock on 2
181+
step controller_show: SELECT * FROM upserttest;
182+
key data
183+
184+
step controller_unlock_1_2: SELECT pg_advisory_unlock(1, 2);
185+
pg_advisory_unlock
186+
187+
t
188+
step s1_insert_toast: <... completed>
189+
step controller_show_count: SELECT COUNT(*) FROM upserttest;
190+
count
191+
192+
1
193+
step controller_unlock_2_2: SELECT pg_advisory_unlock(2, 2);
194+
pg_advisory_unlock
195+
196+
t
197+
s2: NOTICE: blurt_and_lock_123() called for k2 in session 2
198+
s2: NOTICE: acquiring advisory lock on 2
199+
s2: NOTICE: blurt_and_lock_123() called for k2 in session 2
200+
s2: NOTICE: acquiring advisory lock on 2
201+
step s2_insert_toast: <... completed>
202+
step controller_show_count: SELECT COUNT(*) FROM upserttest;
203+
count
204+
205+
1
206+
139207
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
140208
step controller_locks: SELECT pg_advisory_lock(sess, lock), sess, lock FROM generate_series(1, 2) a(sess), generate_series(1,3) b(lock);
141209
pg_advisory_locksess lock
@@ -151,12 +219,12 @@ key data
151219

152220
step s1_begin: BEGIN;
153221
step s2_begin: BEGIN;
154-
s1: NOTICE: called for k1
155-
s1: NOTICE:blocking 3
156-
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 ...>
157-
s2: NOTICE: called for k1
158-
s2: NOTICE:blocking 3
159-
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 ...>
222+
s1: NOTICE:blurt_and_lock_123()called for k1 in session 1
223+
s1: NOTICE:acquiring advisory lock on 3
224+
step s1_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s1') ON CONFLICT (blurt_and_lock_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; <waiting ...>
225+
s2: NOTICE:blurt_and_lock_123()called for k1 in session 2
226+
s2: NOTICE:acquiring advisory lock on 3
227+
step s2_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s2') ON CONFLICT (blurt_and_lock_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s2'; <waiting ...>
160228
step controller_show: SELECT * FROM upserttest;
161229
key data
162230

@@ -172,14 +240,14 @@ step controller_unlock_1_3: SELECT pg_advisory_unlock(1, 3);
172240
pg_advisory_unlock
173241

174242
t
175-
s1: NOTICE: called for k1
176-
s1: NOTICE:blocking 2
243+
s1: NOTICE:blurt_and_lock_123()called for k1 in session 1
244+
s1: NOTICE:acquiring advisory lock on 2
177245
step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3);
178246
pg_advisory_unlock
179247

180248
t
181-
s2: NOTICE: called for k1
182-
s2: NOTICE:blocking 2
249+
s2: NOTICE:blurt_and_lock_123()called for k1 in session 2
250+
s2: NOTICE:acquiring advisory lock on 2
183251
step controller_show: SELECT * FROM upserttest;
184252
key data
185253

@@ -195,16 +263,16 @@ step controller_unlock_2_2: SELECT pg_advisory_unlock(2, 2);
195263
pg_advisory_unlock
196264

197265
t
198-
s2: NOTICE: called for k1
199-
s2: NOTICE:blocking 2
200-
s2: NOTICE: called for k1
201-
s2: NOTICE:blocking 2
266+
s2: NOTICE:blurt_and_lock_123()called for k1 in session 2
267+
s2: NOTICE:acquiring advisory lock on 2
268+
s2: NOTICE:blurt_and_lock_123()called for k1 in session 2
269+
s2: NOTICE:acquiring advisory lock on 2
202270
step controller_show: SELECT * FROM upserttest;
203271
key data
204272

205273
step s1_commit: COMMIT;
206-
s2: NOTICE: called for k1
207-
s2: NOTICE:blocking 2
274+
s2: NOTICE:blurt_and_lock_123()called for k1 in session 2
275+
s2: NOTICE:acquiring advisory lock on 2
208276
step s2_upsert: <... completed>
209277
step controller_show: SELECT * FROM upserttest;
210278
key data
@@ -215,3 +283,124 @@ step controller_show: SELECT * FROM upserttest;
215283
key data
216284

217285
k1 inserted s1 with conflict update s2
286+
287+
starting permutation: s1_create_non_unique_index s1_confirm_index_order controller_locks controller_show 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_lock_2_4 controller_unlock_2_2 controller_show controller_unlock_1_2 controller_print_speculative_locks controller_unlock_2_4 controller_print_speculative_locks s2_commit controller_show controller_print_speculative_locks
288+
step s1_create_non_unique_index: CREATE INDEX upserttest_key_idx ON upserttest((blurt_and_lock_4(key)));
289+
step s1_confirm_index_order: SELECT 'upserttest_key_uniq_idx'::regclass::int8 < 'upserttest_key_idx'::regclass::int8;
290+
?column?
291+
292+
t
293+
step controller_locks: SELECT pg_advisory_lock(sess, lock), sess, lock FROM generate_series(1, 2) a(sess), generate_series(1,3) b(lock);
294+
pg_advisory_locksess lock
295+
296+
1 1
297+
1 2
298+
1 3
299+
2 1
300+
2 2
301+
2 3
302+
step controller_show: SELECT * FROM upserttest;
303+
key data
304+
305+
step s2_begin: BEGIN;
306+
s1: NOTICE: blurt_and_lock_123() called for k1 in session 1
307+
s1: NOTICE: acquiring advisory lock on 3
308+
step s1_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s1') ON CONFLICT (blurt_and_lock_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s1'; <waiting ...>
309+
s2: NOTICE: blurt_and_lock_123() called for k1 in session 2
310+
s2: NOTICE: acquiring advisory lock on 3
311+
step s2_upsert: INSERT INTO upserttest(key, data) VALUES('k1', 'inserted s2') ON CONFLICT (blurt_and_lock_123(key)) DO UPDATE SET data = upserttest.data || ' with conflict update s2'; <waiting ...>
312+
step controller_show: SELECT * FROM upserttest;
313+
key data
314+
315+
step controller_unlock_1_1: SELECT pg_advisory_unlock(1, 1);
316+
pg_advisory_unlock
317+
318+
t
319+
step controller_unlock_2_1: SELECT pg_advisory_unlock(2, 1);
320+
pg_advisory_unlock
321+
322+
t
323+
step controller_unlock_1_3: SELECT pg_advisory_unlock(1, 3);
324+
pg_advisory_unlock
325+
326+
t
327+
s1: NOTICE: blurt_and_lock_123() called for k1 in session 1
328+
s1: NOTICE: acquiring advisory lock on 2
329+
step controller_unlock_2_3: SELECT pg_advisory_unlock(2, 3);
330+
pg_advisory_unlock
331+
332+
t
333+
s2: NOTICE: blurt_and_lock_123() called for k1 in session 2
334+
s2: NOTICE: acquiring advisory lock on 2
335+
step controller_show: SELECT * FROM upserttest;
336+
key data
337+
338+
step controller_lock_2_4: SELECT pg_advisory_lock(2, 4);
339+
pg_advisory_lock
340+
341+
342+
step controller_unlock_2_2: SELECT pg_advisory_unlock(2, 2);
343+
pg_advisory_unlock
344+
345+
t
346+
s2: NOTICE: blurt_and_lock_4() called for k1 in session 2
347+
s2: NOTICE: acquiring advisory lock on 4
348+
step controller_show: SELECT * FROM upserttest;
349+
key data
350+
351+
step controller_unlock_1_2: SELECT pg_advisory_unlock(1, 2);
352+
pg_advisory_unlock
353+
354+
t
355+
s1: NOTICE: blurt_and_lock_4() called for k1 in session 1
356+
s1: NOTICE: acquiring advisory lock on 4
357+
s1: NOTICE: blurt_and_lock_123() called for k1 in session 1
358+
s1: NOTICE: acquiring advisory lock on 2
359+
s1: NOTICE: blurt_and_lock_123() called for k1 in session 1
360+
s1: NOTICE: acquiring advisory lock on 2
361+
step controller_print_speculative_locks:
362+
SELECT pa.application_name, locktype, mode, granted
363+
FROM pg_locks pl JOIN pg_stat_activity pa USING (pid)
364+
WHERE locktype IN ('speculative token', 'transactionid') AND pa.datname = current_database()
365+
ORDER BY 1, 2, 3, 4;
366+
367+
application_namelocktype mode granted
368+
369+
isolation/insert-conflict-specconflict-s1speculative tokenShareLock f
370+
isolation/insert-conflict-specconflict-s1transactionid ExclusiveLock t
371+
isolation/insert-conflict-specconflict-s2speculative tokenExclusiveLock t
372+
isolation/insert-conflict-specconflict-s2transactionid ExclusiveLock t
373+
step controller_unlock_2_4: SELECT pg_advisory_unlock(2, 4);
374+
pg_advisory_unlock
375+
376+
t
377+
s1: NOTICE: blurt_and_lock_123() called for k1 in session 1
378+
s1: NOTICE: acquiring advisory lock on 2
379+
step s2_upsert: <... completed>
380+
step controller_print_speculative_locks:
381+
SELECT pa.application_name, locktype, mode, granted
382+
FROM pg_locks pl JOIN pg_stat_activity pa USING (pid)
383+
WHERE locktype IN ('speculative token', 'transactionid') AND pa.datname = current_database()
384+
ORDER BY 1, 2, 3, 4;
385+
386+
application_namelocktype mode granted
387+
388+
isolation/insert-conflict-specconflict-s1transactionid ExclusiveLock t
389+
isolation/insert-conflict-specconflict-s1transactionid ShareLock f
390+
isolation/insert-conflict-specconflict-s2transactionid ExclusiveLock t
391+
step s2_commit: COMMIT;
392+
s1: NOTICE: blurt_and_lock_123() called for k1 in session 1
393+
s1: NOTICE: acquiring advisory lock on 2
394+
step s1_upsert: <... completed>
395+
step controller_show: SELECT * FROM upserttest;
396+
key data
397+
398+
k1 inserted s2 with conflict update s1
399+
step controller_print_speculative_locks:
400+
SELECT pa.application_name, locktype, mode, granted
401+
FROM pg_locks pl JOIN pg_stat_activity pa USING (pid)
402+
WHERE locktype IN ('speculative token', 'transactionid') AND pa.datname = current_database()
403+
ORDER BY 1, 2, 3, 4;
404+
405+
application_namelocktype mode granted
406+

‎src/test/isolation/expected/insert-conflict-toast.out

Lines changed: 0 additions & 15 deletions
This file was deleted.

‎src/test/isolation/isolation_schedule

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -38,7 +38,6 @@ test: insert-conflict-do-nothing-2
3838
test: insert-conflict-do-update
3939
test: insert-conflict-do-update-2
4040
test: insert-conflict-do-update-3
41-
test: insert-conflict-toast
4241
test: insert-conflict-specconflict
4342
test: delete-abort-savept
4443
test: delete-abort-savept-2

0 commit comments

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