8000 Tests for INSERT ... ON CONFLICT {UPDATE | IGNORE} · petergeoghegan/postgres@b7c5025 · GitHub
[go: up one dir, main page]
Skip to content

Commit b7c5025

Browse files
author
Peter Geoghegan
committed
Tests for INSERT ... ON CONFLICT {UPDATE | IGNORE}
Add dedicated isolation tests for both UPDATE and IGNORE variants, illustrating the "MVCC violation" that allows a READ COMMITTED transaction's UPDATE to succeed in updating a tuple with no version visible to its command's MVCC snapshot. Add regression tests, which for the most part are intended to exercise interactions with other features (e.g. updatable views, inheritance, triggers). Add a few general purpose smoke tests too, testing everything from EXPLAIN output to unique index inference.
1 parent b303364 commit b7c5025

31 files changed

+1127
-8
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2226,6 +2226,13 @@ INSERT INTO ft1(c1, c2) VALUES(11, 12); -- duplicate key
22262226
ERROR: duplicate key value violates unique constraint "t1_pkey"
22272227
DETAIL: Key ("C 1")=(11) already exists.
22282228
CONTEXT: Remote SQL command: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
2229+
INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT IGNORE; -- works
2230+
INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) IGNORE; -- unsupported
2231+
ERROR: relation "ft1" is not an ordinary table
2232+
HINT: Only ordinary tables are accepted as targets when a unique index is inferred for ON CONFLICT.
2233+
INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) UPDATE SET c3 = 'ffg'; -- unsupported
2234+
ERROR: relation "ft1" is not an ordinary table
2235+
HINT: Only ordinary tables are accepted as targets when a unique index is inferred for ON CONFLICT.
22292236
INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive
22302237
ERROR: new row for relation "T 1" violates check constraint "c2positive"
22312238
DETAIL: Failing row contains (1111, -2, null, null, null, null, ft1 , null).

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -353,6 +353,9 @@ UPDATE ft2 SET c2 = c2 + 600 WHERE c1 % 10 = 8 AND c1 < 1200 RETURNING *;
353353
ALTER TABLE "S 1"."T 1" ADD CONSTRAINT c2positive CHECK (c2 >= 0);
354354

355355
INSERT INTO ft1(c1, c2) VALUES(11, 12); -- duplicate key
356+
INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT IGNORE; -- works
357+
INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) IGNORE; -- unsupported
358+
INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) UPDATE SET c3 = 'ffg'; -- unsupported
356359
INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive
357360
UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive
358361

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

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,23 @@
1+
Parsed test spec with 2 sessions
2+
3+
starting permutation: ignore1 ignore2 c1 select2 c2
4+
step ignore1: INSERT INTO ints(key, val) VALUES(1, 'ignore1') ON CONFLICT IGNORE;
5+
step ignore2: INSERT INTO ints(key, val) VALUES(1, 'ignore2') ON CONFLICT IGNORE; <waiting ...>
6+
step c1: COMMIT;
7+
step ignore2: <... completed>
8+
step select2: SELECT * FROM ints;
9+
key val
10+
11+
1 ignore1
12+
step c2: COMMIT;
13+
14+
starting permutation: ignore1 ignore2 a1 select2 c2
15+
step ignore1: INSERT INTO ints(key, val) VALUES(1, 'ignore1') ON CONFLICT IGNORE;
16+
step ignore2: INSERT INTO ints(key, val) VALUES(1, 'ignore2') ON CONFLICT IGNORE; <waiting ...>
17+
step a1: ABORT;
18+
step ignore2: <... completed>
19+
step select2: SELECT * FROM ints;
20+
key val
21+
22+
1 ignore2
23+
step c2: COMMIT;

‎src/test/isolation/expected/insert-conflict-update-2.out

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,23 @@
1+
Parsed test spec with 2 sessions
2+
3+
starting permutation: insert1 insert2 c1 select2 c2
4+
step insert1: INSERT INTO upsert(key, payload) VALUES('FooFoo', 'insert1') ON CONFLICT (lower(key)) UPDATE set key = EXCLUDED.key, payload = TARGET.payload || ' updated by insert1';
5+
step insert2: INSERT INTO upsert(key, payload) VALUES('FOOFOO', 'insert2') ON CONFLICT (lower(key)) UPDATE set key = EXCLUDED.key, payload = TARGET.payload || ' updated by insert2'; <waiting ...>
6+
step c1: COMMIT;
7+
step insert2: <... completed>
8+
step select2: SELECT * FROM upsert;
9+
key payload
10+
11+
FOOFOO insert1 updated by insert2
12+
step c2: COMMIT;
13+
14+
starting permutation: insert1 insert2 a1 select2 c2
15+
step insert1: INSERT INTO upsert(key, payload) VALUES('FooFoo', 'insert1') ON CONFLICT (lower(key)) UPDATE set key = EXCLUDED.key, payload = TARGET.payload || ' updated by insert1';
16+
step insert2: INSERT INTO upsert(key, payload) VALUES('FOOFOO', 'insert2') ON CONFLICT (lower(key)) UPDATE set key = EXCLUDED.key, payload = TARGET.payload || ' updated by insert2'; <waiting ...>
17+
step a1: ABORT;
18+
step insert2: <... completed>
19+
step select2: SELECT * FROM upsert;
20+
key payload
21+
22+
FOOFOO insert2
23+
step c2: COMMIT;

‎src/test/isolation/expected/insert-conflict-update-3.out

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,26 @@
1+
Parsed test spec with 2 sessions
2+
3+
starting permutation: update2 insert1 c2 select1surprise c1
4+
step update2: UPDATE colors SET is_active = true WHERE key = 1;
5+
step insert1:
6+
WITH t AS (
7+
INSERT INTO colors(key, color, is_active)
8+
VALUES(1, 'Brown', true), (2, 'Gray', true)
9+
ON CONFLICT (key) UPDATE
10+
SET color = EXCLUDED.color
11+
WHERE TARGET.is_active)
12+
SELECT * FROM colors ORDER BY key; <waiting ...>
13+
step c2: COMMIT;
14+
step insert1: <... completed>
15+
key color is_active
16+
17+
1 Red f
18+
2 Green f
19+
3 Blue f
20+
step select1surprise: SELECT * FROM colors ORDER BY key;
21+
key color is_active
22+
23+
1 Brown t
24+
2 Green f
25+
3 Blue f
26+
step c1: COMMIT;

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

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,23 @@
1+
Parsed test spec with 2 sessions
2+
3+
starting permutation: insert1 insert2 c1 select2 c2
4+
step insert1: INSERT INTO upsert(key, val) VALUES(1, 'insert1') ON CONFLICT (key) UPDATE set val = TARGET.val || ' updated by insert1';
5+
step insert2: INSERT INTO upsert(key, val) VALUES(1, 'insert2') ON CONFLICT (key) UPDATE set val = TARGET.val || ' updated by insert2'; <waiting ...>
6+
step c1: COMMIT;
7+
step insert2: <... completed>
8+
step select2: SELECT * FROM upsert;
9+
key val
10+
11+
1 insert1 updated by insert2
12+
step c2: COMMIT;
13+
14+
starting permutation: insert1 insert2 a1 select2 c2
15+
step insert1: INSERT INTO upsert(key, val) VALUES(1, 'insert1') ON CONFLICT (key) UPDATE set val = TARGET.val || ' updated by insert1';
16+
step insert2: INSERT INTO upsert(key, val) VALUES(1, 'insert2') ON CONFLICT (key) UPDATE set val = TARGET.val || ' updated by insert2'; <waiting ...>
17+
step a1: ABORT;
18+
step insert2: <... completed>
19+
step select2: SELECT * FROM upsert;
20+
key val
21+
22+
1 insert2
23+
step c2: COMMIT;

‎src/test/isolation/isolation_schedule

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,10 @@ test: fk-deadlock2
1616
test: eval-plan-qual
1717
test: lock-update-delete
1818
test: lock-update-traversal
19+
test: insert-conflict-ignore
20+
test: insert-conflict-update
21+
test: insert-conflict-update-2
22+
test: insert-conflict-update-3
1923
test: delete-abort-savept
2024
test: delete-abort-savept-2
2125
test: aborted-keyrevoke

‎src/test/isolation/specs/insert-conflict-ignore.spec

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,41 @@
1+
# INSERT...ON CONFLICT IGNORE test
2+
#
3+
# This test tries to expose problems with the interaction between concurrent
4+
# sessions during INSERT...ON CONFLICT IGNORE.
5+
#
6+
# The convention here is that session 1 always ends up inserting, and session 2
7+
# always ends up ignoring.
8+
9+
setup
10+
{
11+
CREATE TABLE ints (key int primary key, val text);
12+
}
13+
14+
teardown
15+
{
16+
DROP TABLE ints;
17+
}
18+
19+
session "s1"
20+
setup
21+
{
22+
BEGIN ISOLATION LEVEL READ COMMITTED;
23+
}
24+
step "ignore1" { INSERT INTO ints(key, val) VALUES(1, 'ignore1') ON CONFLICT IGNORE; }
25+
step "c1" { COMMIT; }
26+
step "a1" { ABORT; }
27+
28+
session "s2"
29+
setup
30+
{
31+
BEGIN ISOLATION LEVEL READ COMMITTED;
32+
}
33+
step "ignore2" { INSERT INTO ints(key, val) VALUES(1, 'ignore2') ON CONFLICT IGNORE; }
34+
step "select2" { SELECT * FROM ints; }
35+
step "c2" { COMMIT; }
36+
step "a2" { ABORT; }
37+
38+
# Regular case where one session block-waits on another to determine if it
39+
# should proceed with an insert or ignore.
40+
permutation "ignore1" "ignore2" "c1" "select2" "c2"
41+
permutation "ignore1" "ignore2" "a1" "select2" "c2"

‎src/test/isolation/specs/insert-conflict-update-2.spec

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,41 @@
1+
# INSERT...ON CONFLICT UPDATE test
2+
#
3+
# This test shows a plausible scenario in which the user might wish to UPDATE a
4+
# value that is also constrained by the unique index that is the arbiter of
5+
# whether the alternative path should be taken.
6+
7+
setup
8+
{
9+
CREATE TABLE upsert (key text not null, payload text);
10+
CREATE UNIQUE INDEX ON upsert(lower(key));
11+
}
12+
13+
teardown
14+
{
15+
DROP TABLE upsert;
16+
}
17+
18+
session "s1"
19+
setup
20+
{
21+
BEGIN ISOLATION LEVEL READ COMMITTED;
22+
}
23+
step "insert1" { INSERT INTO upsert(key, payload) VALUES('FooFoo', 'insert1') ON CONFLICT (lower(key)) UPDATE set key = EXCLUDED.key, payload = TARGET.payload || ' updated by insert1'; }
24+
step "c1" { COMMIT; }
25+
step "a1" { ABORT; }
26+
27+
session "s2"
28+
setup
29+
{
30+
BEGIN ISOLATION LEVEL READ COMMITTED;
31+
}
32+
step "insert2" { INSERT INTO upsert(key, payload) VALUES('FOOFOO', 'insert2') ON CONFLICT (lower(key)) UPDATE set key = EXCLUDED.key, payload = TARGET.payload || ' updated by insert2'; }
33+
step "select2" { SELECT * FROM upsert; }
34+
step "c2" { COMMIT; }
35+
step "a2" { ABORT; }
36+
37+
# One session (session 2) block-waits on another (session 1) to determine if it
38+
# should proceed with an insert or update. The user can still usefully UPDATE
39+
# a column constrained by a unique index, as the example illustrates.
40+
permutation "insert1" "insert2" "c1" "select2" "c2"
41+
permutation "insert1" "insert2" "a1" "select2" "c2"

‎src/test/isolation/specs/insert-conflict-update-3.spec

Lines changed: 69 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,69 @@
1+
# INSERT...ON CONFLICT UPDATE test
2+
#
3+
# Other INSERT...ON CONFLICT UPDATE isolation tests illustrate the "MVCC
4+
# violation" added to facilitate the feature, whereby a
5+
# not-visible-to-our-snapshot tuple can be updated by our command all the same.
6+
# This is generally needed to provide a guarantee of a successful INSERT or
7+
# UPDATE in READ COMMITTED mode. This MVCC violation is quite distinct from
8+
# the putative "MVCC violation" that has existed in PostgreSQL for many years,
9+
# the EvalPlanQual() mechanism, because that mechanism always starts from a
10+
# tuple that is visible to the command's MVCC snapshot. This test illustrates
11+
# a slightly distinct user-visible consequence of the same MVCC violation
12+
# generally associated with INSERT...ON CONFLICT UPDATE. The impact of the
13+
# MVCC violation goes a little beyond updating MVCC-invisible tuples.
14+
#
15+
# With INSERT...ON CONFLICT UPDATE, the UPDATE predicate is only evaluated
16+
# once, on this conclusively-locked tuple, and not any other version of the
17+
# same tuple. It is therefore possible (in READ COMMITTED mode) that the
18+
# predicate "fail to be satisfied" according to the command's MVCC snapshot.
19+
# It might simply be that there is n 10000 o row version visible, but it's also
20+
# possible that there is some row version visible, but only as a version that
21+
# doesn't satisfy the predicate. If, however, the conclusively-locked version
22+
# satisfies the predicate, that's good enough, and the tuple is updated. The
23+
# MVCC-snapshot-visible row version is denied the opportunity to prevent the
24+
# UPDATE from taking place, because we don't walk the UPDATE chain in the usual
25+
# way.
26+
27+
setup
28+
{
29+
CREATE TABLE colors (key int4 PRIMARY KEY, color text, is_active boolean);
30+
INSERT INTO colors (key, color, is_active) VALUES(1, 'Red', false);
31+
INSERT INTO colors (key, color, is_active) VALUES(2, 'Green', false);
32+
INSERT INTO colors (key, color, is_active) VALUES(3, 'Blue', false);
33+
}
34+
35+
teardown
36+
{
37+
DROP TABLE colors;
38+
}
39+
40+
session "s1"
41+
setup
42+
{
43+
BEGIN ISOLATION LEVEL READ COMMITTED;
44+
}
45+
step "insert1" {
46+
WITH t AS (
47+
INSERT INTO colors(key, color, is_active)
48+
VALUES(1, 'Brown', true), (2, 'Gray', true)
49+
ON CONFLICT (key) UPDATE
50+
SET color = EXCLUDED.color
51+
WHERE TARGET.is_active)
52+
SELECT * FROM colors ORDER BY key;}
53+
step "select1surprise" { SELECT * FROM colors ORDER BY key; }
54+
step "c1" { COMMIT; }
55+
56+
session "s2"
57+
setup
58+
{
59+
BEGIN ISOLATION LEVEL READ COMMITTED;
60+
}
61+
step "update2" { UPDATE colors SET is_active = true WHERE key = 1; }
62+
step "c2" { COMMIT; }
63+
64+
# Perhaps surprisingly, the session 1 MVCC-snapshot-visible tuple (the tuple
65+
# with the pre-populated color 'Red') is denied the opportunity to prevent the
66+
# UPDATE from taking place -- only the conclusively-locked tuple version
67+
# matters, and so the tuple with key value 1 was updated to 'Brown' (but not
68+
# tuple with key value 2, since nothing changed there):
69+
permutation "update2" "insert1" "c2" "select1surprise" "c1"

‎src/test/isolation/specs/insert-conflict-update.spec

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,40 @@
1+
# INSERT...ON CONFLICT UPDATE test
2+
#
3+
# This test tries to expose problems with the interaction between concurrent
4+
# sessions.
5+
6+
setup
7+
{
8+
CREATE TABLE upsert (key int primary key, val text);
9+
}
10+
11+
teardown
12+
{
13+
DROP TABLE upsert;
14+
}
15+
16+
session "s1"
17+
setup
18+
{
19+
BEGIN ISOLATION LEVEL READ COMMITTED;
20+
}
21+
step "insert1" { INSERT INTO upsert(key, val) VALUES(1, 'insert1') ON CONFLICT (key) UPDATE set val = TARGET.val || ' updated by insert1'; }
22+
step "c1" { COMMIT; }
23+
step "a1" { ABORT; }
24+
25+
session "s2"
26+
setup
27+
{
28+
BEGIN ISOLATION LEVEL READ COMMITTED;
29+
}
30+
step "insert2" { INSERT INTO upsert(key, val) VALUES(1, 'insert2') ON CONFLICT (key) UPDATE set val = TARGET.val || ' updated by insert2'; }
31+
step "select2" { SELECT * FROM upsert; }
32+
step "c2" { COMMIT; }
33+
step "a2" { ABORT; }
34+
35+
# One session (session 2) block-waits on another (session 1) to determine if it
36+
# should proceed with an insert or update. Notably, this entails updating a
37+
# tuple while there is no version of that tuple visible to the updating
38+
# session's snapshot. This is permitted only in READ COMMITTED mode.
39+
permutation "insert1" "insert2" "c1" "select2" "c2"
40+
permutation "insert1" "insert2" "a1" "select2" "c2"

0 commit comments

Comments
 (0)
0