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

Commita6c0a5b

Browse files
committed
Don't throw serialization errors for self-conflicts in INSERT ON CONFLICT.
A transaction that conflicts against itself, for exampleINSERT INTO t(pk) VALUES (1),(1) ON CONFLICT DO NOTHING;should behave the same regardless of isolation level. It certainlyshouldn't throw a serialization error, as retrying will not help.We got this wrong due to the ON CONFLICT logic not considering the case,as reported by Jason Dusek.Core of this patch is by Peter Geoghegan (based on an earlier patch byThomas Munro), though I didn't take his proposed code refactoring for fearthat it might have unexpected side-effects. Test cases by Thomas Munroand myself.Report: <CAO3NbwOycQjt2Oqy2VW-eLTq2M5uGMyHnGm=RNga4mjqcYD7gQ@mail.gmail.com>Related-Discussion: <57EE93C8.8080504@postgrespro.ru>
1 parent6292c23 commita6c0a5b

File tree

6 files changed

+218
-2
lines changed

6 files changed

+218
-2
lines changed

‎src/backend/executor/nodeModifyTable.c

Lines changed: 11 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -195,9 +195,18 @@ ExecCheckHeapTupleVisible(EState *estate,
195195
return;
196196

197197
if (!HeapTupleSatisfiesVisibility(tuple,estate->es_snapshot,buffer))
198-
ereport(ERROR,
199-
(errcode(ERRCODE_T_R_SERIALIZATION_FAILURE),
198+
{
199+
/*
200+
* We should not raise a serialization failure if the conflict is
201+
* against a tuple inserted by our own transaction, even if it's not
202+
* visible to our snapshot. (This would happen, for example, if
203+
* conflicting keys are proposed for insertion in a single command.)
204+
*/
205+
if (!TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetXmin(tuple->t_data)))
206+
ereport(ERROR,
207+
(errcode(ERRCODE_T_R_SERIALIZATION_FAILURE),
200208
errmsg("could not serialize access due to concurrent update")));
209+
}
201210
}
202211

203212
/*
Lines changed: 105 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,105 @@
1+
Parsed test spec with 2 sessions
2+
3+
starting permutation: beginrr1 beginrr2 donothing1 c1 donothing2 c2 show
4+
step beginrr1: BEGIN ISOLATION LEVEL REPEATABLE READ;
5+
step beginrr2: BEGIN ISOLATION LEVEL REPEATABLE READ;
6+
step donothing1: INSERT INTO ints(key, val) VALUES(1, 'donothing1') ON CONFLICT DO NOTHING;
7+
step c1: COMMIT;
8+
step donothing2: INSERT INTO ints(key, val) VALUES(1, 'donothing2'), (1, 'donothing3') ON CONFLICT DO NOTHING;
9+
step c2: COMMIT;
10+
step show: SELECT * FROM ints;
11+
key val
12+
13+
1 donothing1
14+
15+
starting permutation: beginrr1 beginrr2 donothing2 c2 donothing1 c1 show
16+
step beginrr1: BEGIN ISOLATION LEVEL REPEATABLE READ;
17+
step beginrr2: BEGIN ISOLATION LEVEL REPEATABLE READ;
18+
step donothing2: INSERT INTO ints(key, val) VALUES(1, 'donothing2'), (1, 'donothing3') ON CONFLICT DO NOTHING;
19+
step c2: COMMIT;
20+
step donothing1: INSERT INTO ints(key, val) VALUES(1, 'donothing1') ON CONFLICT DO NOTHING;
21+
step c1: COMMIT;
22+
step show: SELECT * FROM ints;
23+
key val
24+
25+
1 donothing2
26+
27+
starting permutation: beginrr1 beginrr2 donothing1 donothing2 c1 c2 show
28+
step beginrr1: BEGIN ISOLATION LEVEL REPEATABLE READ;
29+
step beginrr2: BEGIN ISOLATION LEVEL REPEATABLE READ;
30+
step donothing1: INSERT INTO ints(key, val) VALUES(1, 'donothing1') ON CONFLICT DO NOTHING;
31+
step donothing2: INSERT INTO ints(key, val) VALUES(1, 'donothing2'), (1, 'donothing3') ON CONFLICT DO NOTHING; <waiting ...>
32+
step c1: COMMIT;
33+
step donothing2: <... completed>
34+
error in steps c1 donothing2: ERROR: could not serialize access due to concurrent update
35+
step c2: COMMIT;
36+
step show: SELECT * FROM ints;
37+
key val
38+
39+
1 donothing1
40+
41+
starting permutation: beginrr1 beginrr2 donothing2 donothing1 c2 c1 show
42+
step beginrr1: BEGIN ISOLATION LEVEL REPEATABLE READ;
43+
step beginrr2: BEGIN ISOLATION LEVEL REPEATABLE READ;
44+
step donothing2: INSERT INTO ints(key, val) VALUES(1, 'donothing2'), (1, 'donothing3') ON CONFLICT DO NOTHING;
45+
step donothing1: INSERT INTO ints(key, val) VALUES(1, 'donothing1') ON CONFLICT DO NOTHING; <waiting ...>
46+
step c2: COMMIT;
47+
step donothing1: <... completed>
48+
error in steps c2 donothing1: ERROR: could not serialize access due to concurrent update
49+
step c1: COMMIT;
50+
step show: SELECT * FROM ints;
51+
key val
52+
53+
1 donothing2
54+
55+
starting permutation: begins1 begins2 donothing1 c1 donothing2 c2 show
56+
step begins1: BEGIN ISOLATION LEVEL SERIALIZABLE;
57+
step begins2: BEGIN ISOLATION LEVEL SERIALIZABLE;
58+
step donothing1: INSERT INTO ints(key, val) VALUES(1, 'donothing1') ON CONFLICT DO NOTHING;
59+
step c1: COMMIT;
60+
step donothing2: INSERT INTO ints(key, val) VALUES(1, 'donothing2'), (1, 'donothing3') ON CONFLICT DO NOTHING;
61+
step c2: COMMIT;
62+
step show: SELECT * FROM ints;
63+
key val
64+
65+
1 donothing1
66+
67+
starting permutation: begins1 begins2 donothing2 c2 donothing1 c1 show
68+
step begins1: BEGIN ISOLATION LEVEL SERIALIZABLE;
69+
step begins2: BEGIN ISOLATION LEVEL SERIALIZABLE;
70+
step donothing2: INSERT INTO ints(key, val) VALUES(1, 'donothing2'), (1, 'donothing3') ON CONFLICT DO NOTHING;
71+
step c2: COMMIT;
72+
step donothing1: INSERT INTO ints(key, val) VALUES(1, 'donothing1') ON CONFLICT DO NOTHING;
73+
step c1: COMMIT;
74+
step show: SELECT * FROM ints;
75+
key val
76+
77+
1 donothing2
78+
79+
starting permutation: begins1 begins2 donothing1 donothing2 c1 c2 show
80+
step begins1: BEGIN ISOLATION LEVEL SERIALIZABLE;
81+
step begins2: BEGIN ISOLATION LEVEL SERIALIZABLE;
82+
step donothing1: INSERT INTO ints(key, val) VALUES(1, 'donothing1') ON CONFLICT DO NOTHING;
83+
step donothing2: INSERT INTO ints(key, val) VALUES(1, 'donothing2'), (1, 'donothing3') ON CONFLICT DO NOTHING; <waiting ...>
84+
step c1: COMMIT;
85+
step donothing2: <... completed>
86+
error in steps c1 donothing2: ERROR: could not serialize access due to concurrent update
87+
step c2: COMMIT;
88+
step show: SELECT * FROM ints;
89+
key val
90+
91+
1 donothing1
92+
93+
starting permutation: begins1 begins2 donothing2 donothing1 c2 c1 show
94+
step begins1: BEGIN ISOLATION LEVEL SERIALIZABLE;
95+
step begins2: BEGIN ISOLATION LEVEL SERIALIZABLE;
96+
step donothing2: INSERT INTO ints(key, val) VALUES(1, 'donothing2'), (1, 'donothing3') ON CONFLICT DO NOTHING;
97+
step donothing1: INSERT INTO ints(key, val) VALUES(1, 'donothing1') ON CONFLICT DO NOTHING; <waiting ...>
98+
step c2: COMMIT;
99+
step donothing1: <... completed>
100+
error in steps c2 donothing1: ERROR: could not serialize access due to concurrent update
101+
step c1: COMMIT;
102+
step show: SELECT * FROM ints;
103+
key val
104+
105+
1 donothing2

‎src/test/isolation/isolation_schedule

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,7 @@ test: eval-plan-qual
2525
test: lock-update-delete
2626
test: lock-update-traversal
2727
test: insert-conflict-do-nothing
28+
test: insert-conflict-do-nothing-2
2829
test: insert-conflict-do-update
2930
test: insert-conflict-do-update-2
3031
test: insert-conflict-do-update-3
Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,34 @@
1+
# INSERT...ON CONFLICT DO NOTHING test with multiple rows
2+
# in higher isolation levels
3+
4+
setup
5+
{
6+
CREATETABLEints (keyintprimarykey,valtext);
7+
}
8+
9+
teardown
10+
{
11+
DROPTABLEints;
12+
}
13+
14+
session"s1"
15+
step"beginrr1" {BEGINISOLATIONLEVELREPEATABLEREAD; }
16+
step"begins1" {BEGINISOLATIONLEVELSERIALIZABLE; }
17+
step"donothing1" {INSERTINTOints(key,val)VALUES(1,'donothing1')ONCONFLICTDONOTHING; }
18+
step"c1" {COMMIT; }
19+
step"show" {SELECT*FROMints; }
20+
21+
session"s2"
22+
step"beginrr2" {BEGINISOLATIONLEVELREPEATABLEREAD; }
23+
step"begins2" {BEGINISOLATIONLEVELSERIALIZABLE; }
24+
step"donothing2" {INSERTINTOints(key,val)VALUES(1,'donothing2'), (1,'donothing3')ONCONFLICTDONOTHING; }
25+
step"c2" {COMMIT; }
26+
27+
permutation"beginrr1""beginrr2""donothing1""c1""donothing2""c2""show"
28+
permutation"beginrr1""beginrr2""donothing2""c2""donothing1""c1""show"
29+
permutation"beginrr1""beginrr2""donothing1""donothing2""c1""c2""show"
30+
permutation"beginrr1""beginrr2""donothing2""donothing1""c2""c1""show"
31+
permutation"begins1""begins2""donothing1""c1""donothing2""c2""show"
32+
permutation"begins1""begins2""donothing2""c2""donothing1""c1""show"
33+
permutation"begins1""begins2""donothing1""donothing2""c1""c2""show"
34+
permutation"begins1""begins2""donothing2""donothing1""c2""c1""show"

‎src/test/regress/expected/insert_conflict.out

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -727,3 +727,38 @@ select * from twoconstraints;
727727
(1 row)
728728

729729
drop table twoconstraints;
730+
-- check handling of self-conflicts at various isolation levels
731+
create table selfconflict (f1 int primary key, f2 int);
732+
begin transaction isolation level read committed;
733+
insert into selfconflict values (1,1), (1,2) on conflict do nothing;
734+
commit;
735+
begin transaction isolation level repeatable read;
736+
insert into selfconflict values (2,1), (2,2) on conflict do nothing;
737+
commit;
738+
begin transaction isolation level serializable;
739+
insert into selfconflict values (3,1), (3,2) on conflict do nothing;
740+
commit;
741+
begin transaction isolation level read committed;
742+
insert into selfconflict values (4,1), (4,2) on conflict(f1) do update set f2 = 0;
743+
ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
744+
HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.
745+
commit;
746+
begin transaction isolation level repeatable read;
747+
insert into selfconflict values (5,1), (5,2) on conflict(f1) do update set f2 = 0;
748+
ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
749+
HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.
750+
commit;
751+
begin transaction isolation level serializable;
752+
insert into selfconflict values (6,1), (6,2) on conflict(f1) do update set f2 = 0;
753+
ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
754+
HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.
755+
commit;
756+
select * from selfconflict;
757+
f1 | f2
758+
----+----
759+
1 | 1
760+
2 | 1
761+
3 | 1
762+
(3 rows)
763+
764+
drop table selfconflict;

‎src/test/regress/sql/insert_conflict.sql

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -421,3 +421,35 @@ insert into twoconstraints values(2, '((0,0),(1,2))')
421421
on conflictonconstraint twoconstraints_f2_excl do nothing;-- do nothing
422422
select*from twoconstraints;
423423
droptable twoconstraints;
424+
425+
-- check handling of self-conflicts at various isolation levels
426+
427+
createtableselfconflict (f1intprimary key, f2int);
428+
429+
begin transaction isolation level read committed;
430+
insert into selfconflictvalues (1,1), (1,2)on conflict do nothing;
431+
commit;
432+
433+
begin transaction isolation level repeatable read;
434+
insert into selfconflictvalues (2,1), (2,2)on conflict do nothing;
435+
commit;
436+
437+
begin transaction isolation level serializable;
438+
insert into selfconflictvalues (3,1), (3,2)on conflict do nothing;
439+
commit;
440+
441+
begin transaction isolation level read committed;
442+
insert into selfconflictvalues (4,1), (4,2)on conflict(f1) doupdateset f2=0;
443+
commit;
444+
445+
begin transaction isolation level repeatable read;
446+
insert into selfconflictvalues (5,1), (5,2)on conflict(f1) doupdateset f2=0;
447+
commit;
448+
449+
begin transaction isolation level serializable;
450+
insert into selfconflictvalues (6,1), (6,2)on conflict(f1) doupdateset f2=0;
451+
commit;
452+
453+
select*from selfconflict;
454+
455+
droptable selfconflict;

0 commit comments

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