From 840ab27666378d7405b782628be863615e66aafb Mon Sep 17 00:00:00 2001 From: Peter Geoghegan Date: Wed, 27 Aug 2014 15:11:15 -0700 Subject: [PATCH 6/8] 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, RLS). Add a few general purpose smoke tests too, testing everything from EXPLAIN output to unique index inference (expression indexes, partial indexes, etc). --- contrib/postgres_fdw/expected/postgres_fdw.out | 7 + contrib/postgres_fdw/sql/postgres_fdw.sql | 3 + .../isolation/expected/insert-conflict-ignore.out | 23 ++ .../expected/insert-conflict-update-2.out | 23 ++ .../expected/insert-conflict-update-3.out | 26 +++ .../isolation/expected/insert-conflict-update.out | 23 ++ src/test/isolation/isolation_schedule | 4 + .../isolation/specs/insert-conflict-ignore.spec | 41 ++++ .../isolation/specs/insert-conflict-update-2.spec | 41 ++++ .../isolation/specs/insert-conflict-update-3.spec | 69 ++++++ .../isolation/specs/insert-conflict-update.spec | 40 ++++ src/test/regress/expected/insert_conflict.out | 242 +++++++++++++++++++++ src/test/regress/expected/privileges.out | 7 +- src/test/regress/expected/rowsecurity.out | 90 ++++++++ src/test/regress/expected/rules.out | 21 ++ src/test/regress/expected/subselect.out | 22 ++ src/test/regress/expected/triggers.out | 102 ++++++++- src/test/regress/expected/updatable_views.out | 4 + src/test/regress/expected/update.out | 27 +++ src/test/regress/expected/with.out | 74 +++++++ src/test/regress/input/constraints.source | 5 + src/test/regress/output/constraints.source | 15 +- src/test/regress/parallel_schedule | 1 + src/test/regress/serial_schedule | 1 + src/test/regress/sql/insert_conflict.sql | 192 ++++++++++++++++ src/test/regress/sql/privileges.sql | 5 +- src/test/regress/sql/rowsecurity.sql | 73 +++++++ src/test/regress/sql/rules.sql | 14 ++ src/test/regress/sql/subselect.sql | 14 ++ src/test/regress/sql/triggers.sql | 69 +++++- src/test/regress/sql/updatable_views.sql | 2 + src/test/regress/sql/update.sql | 14 ++ src/test/regress/sql/with.sql | 37 ++++ 33 files changed, 1323 insertions(+), 8 deletions(-) create mode 100644 src/test/isolation/expected/insert-conflict-ignore.out create mode 100644 src/test/isolation/expected/insert-conflict-update-2.out create mode 100644 src/test/isolation/expected/insert-conflict-update-3.out create mode 100644 src/test/isolation/expected/insert-conflict-update.out create mode 100644 src/test/isolation/specs/insert-conflict-ignore.spec create mode 100644 src/test/isolation/specs/insert-conflict-update-2.spec create mode 100644 src/test/isolation/specs/insert-conflict-update-3.spec create mode 100644 src/test/isolation/specs/insert-conflict-update.spec create mode 100644 src/test/regress/expected/insert_conflict.out create mode 100644 src/test/regress/sql/insert_conflict.sql diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 583cce7..5133386 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -2327,6 +2327,13 @@ INSERT INTO ft1(c1, c2) VALUES(11, 12); -- duplicate key ERROR: duplicate key value violates unique constraint "t1_pkey" DETAIL: Key ("C 1")=(11) already exists. 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) +INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT IGNORE; -- works +INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) IGNORE; -- unsupported +ERROR: relation "ft1" is not an ordinary table +HINT: Only ordinary tables are accepted as targets when a unique index is inferred for ON CONFLICT. +INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) UPDATE SET c3 = 'ffg'; -- unsupported +ERROR: relation "ft1" is not an ordinary table +HINT: Only ordinary tables are accepted as targets when a unique index is inferred for ON CONFLICT. INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive ERROR: new row for relation "T 1" violates check constraint "c2positive" DETAIL: Failing row contains (1111, -2, null, null, null, null, ft1 , null). diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 83e8fa7..e01d34e 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -372,6 +372,9 @@ UPDATE ft2 SET c2 = c2 + 600 WHERE c1 % 10 = 8 AND c1 < 1200 RETURNING *; ALTER TABLE "S 1"."T 1" ADD CONSTRAINT c2positive CHECK (c2 >= 0); INSERT INTO ft1(c1, c2) VALUES(11, 12); -- duplicate key +INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT IGNORE; -- works +INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) IGNORE; -- unsupported +INSERT INTO ft1(c1, c2) VALUES(11, 12) ON CONFLICT (c1, c2) UPDATE SET c3 = 'ffg'; -- unsupported INSERT INTO ft1(c1, c2) VALUES(1111, -2); -- c2positive UPDATE ft1 SET c2 = -c2 WHERE c1 = 1; -- c2positive diff --git a/src/test/isolation/expected/insert-conflict-ignore.out b/src/test/isolation/expected/insert-conflict-ignore.out new file mode 100644 index 0000000..e6cc2a1 --- /dev/null +++ b/src/test/isolation/expected/insert-conflict-ignore.out @@ -0,0 +1,23 @@ +Parsed test spec with 2 sessions + +starting permutation: ignore1 ignore2 c1 select2 c2 +step ignore1: INSERT INTO ints(key, val) VALUES(1, 'ignore1') ON CONFLICT IGNORE; +step ignore2: INSERT INTO ints(key, val) VALUES(1, 'ignore2') ON CONFLICT IGNORE; +step c1: COMMIT; +step ignore2: <... completed> +step select2: SELECT * FROM ints; +key val + +1 ignore1 +step c2: COMMIT; + +starting permutation: ignore1 ignore2 a1 select2 c2 +step ignore1: INSERT INTO ints(key, val) VALUES(1, 'ignore1') ON CONFLICT IGNORE; +step ignore2: INSERT INTO ints(key, val) VALUES(1, 'ignore2') ON CONFLICT IGNORE; +step a1: ABORT; +step ignore2: <... completed> +step select2: SELECT * FROM ints; +key val + +1 ignore2 +step c2: COMMIT; diff --git a/src/test/isolation/expected/insert-conflict-update-2.out b/src/test/isolation/expected/insert-conflict-update-2.out new file mode 100644 index 0000000..6a5ddfe --- /dev/null +++ b/src/test/isolation/expected/insert-conflict-update-2.out @@ -0,0 +1,23 @@ +Parsed test spec with 2 sessions + +starting permutation: insert1 insert2 c1 select2 c2 +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'; +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'; +step c1: COMMIT; +step insert2: <... completed> +step select2: SELECT * FROM upsert; +key payload + +FOOFOO insert1 updated by insert2 +step c2: COMMIT; + +starting permutation: insert1 insert2 a1 select2 c2 +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'; +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'; +step a1: ABORT; +step insert2: <... completed> +step select2: SELECT * FROM upsert; +key payload + +FOOFOO insert2 +step c2: COMMIT; diff --git a/src/test/isolation/expected/insert-conflict-update-3.out b/src/test/isolation/expected/insert-conflict-update-3.out new file mode 100644 index 0000000..29dd8b0 --- /dev/null +++ b/src/test/isolation/expected/insert-conflict-update-3.out @@ -0,0 +1,26 @@ +Parsed test spec with 2 sessions + +starting permutation: update2 insert1 c2 select1surprise c1 +step update2: UPDATE colors SET is_active = true WHERE key = 1; +step insert1: + WITH t AS ( + INSERT INTO colors(key, color, is_active) + VALUES(1, 'Brown', true), (2, 'Gray', true) + ON CONFLICT (key) UPDATE + SET color = EXCLUDED.color + WHERE TARGET.is_active) + SELECT * FROM colors ORDER BY key; +step c2: COMMIT; +step insert1: <... completed> +key color is_active + +1 Red f +2 Green f +3 Blue f +step select1surprise: SELECT * FROM colors ORDER BY key; +key color is_active + +1 Brown t +2 Green f +3 Blue f +step c1: COMMIT; diff --git a/src/test/isolation/expected/insert-conflict-update.out b/src/test/isolation/expected/insert-conflict-update.out new file mode 100644 index 0000000..6976124 --- /dev/null +++ b/src/test/isolation/expected/insert-conflict-update.out @@ -0,0 +1,23 @@ +Parsed test spec with 2 sessions + +starting permutation: insert1 insert2 c1 select2 c2 +step insert1: INSERT INTO upsert(key, val) VALUES(1, 'insert1') ON CONFLICT (key) UPDATE set val = TARGET.val || ' updated by insert1'; +step insert2: INSERT INTO upsert(key, val) VALUES(1, 'insert2') ON CONFLICT (key) UPDATE set val = TARGET.val || ' updated by insert2'; +step c1: COMMIT; +step insert2: <... completed> +step select2: SELECT * FROM upsert; +key val + +1 insert1 updated by insert2 +step c2: COMMIT; + +starting permutation: insert1 insert2 a1 select2 c2 +step insert1: INSERT INTO upsert(key, val) VALUES(1, 'insert1') ON CONFLICT (key) UPDATE set val = TARGET.val || ' updated by insert1'; +step insert2: INSERT INTO upsert(key, val) VALUES(1, 'insert2') ON CONFLICT (key) UPDATE set val = TARGET.val || ' updated by insert2'; +step a1: ABORT; +step insert2: <... completed> +step select2: SELECT * FROM upsert; +key val + +1 insert2 +step c2: COMMIT; diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule index c055a53..50948a2 100644 --- a/src/test/isolation/isolation_schedule +++ b/src/test/isolation/isolation_schedule @@ -16,6 +16,10 @@ test: fk-deadlock2 test: eval-plan-qual test: lock-update-delete test: lock-update-traversal +test: insert-conflict-ignore +test: insert-conflict-update +test: insert-conflict-update-2 +test: insert-conflict-update-3 test: delete-abort-savept test: delete-abort-savept-2 test: aborted-keyrevoke diff --git a/src/test/isolation/specs/insert-conflict-ignore.spec b/src/test/isolation/specs/insert-conflict-ignore.spec new file mode 100644 index 0000000..fde43b3 --- /dev/null +++ b/src/test/isolation/specs/insert-conflict-ignore.spec @@ -0,0 +1,41 @@ +# INSERT...ON CONFLICT IGNORE test +# +# This test tries to expose problems with the interaction between concurrent +# sessions during INSERT...ON CONFLICT IGNORE. +# +# The convention here is that session 1 always ends up inserting, and session 2 +# always ends up ignoring. + +setup +{ + CREATE TABLE ints (key int primary key, val text); +} + +teardown +{ + DROP TABLE ints; +} + +session "s1" +setup +{ + BEGIN ISOLATION LEVEL READ COMMITTED; +} +step "ignore1" { INSERT INTO ints(key, val) VALUES(1, 'ignore1') ON CONFLICT IGNORE; } +step "c1" { COMMIT; } +step "a1" { ABORT; } + +session "s2" +setup +{ + BEGIN ISOLATION LEVEL READ COMMITTED; +} +step "ignore2" { INSERT INTO ints(key, val) VALUES(1, 'ignore2') ON CONFLICT IGNORE; } +step "select2" { SELECT * FROM ints; } +step "c2" { COMMIT; } +step "a2" { ABORT; } + +# Regular case where one session block-waits on another to determine if it +# should proceed with an insert or ignore. +permutation "ignore1" "ignore2" "c1" "select2" "c2" +permutation "ignore1" "ignore2" "a1" "select2" "c2" diff --git a/src/test/isolation/specs/insert-conflict-update-2.spec b/src/test/isolation/specs/insert-conflict-update-2.spec new file mode 100644 index 0000000..3e6e944 --- /dev/null +++ b/src/test/isolation/specs/insert-conflict-update-2.spec @@ -0,0 +1,41 @@ +# INSERT...ON CONFLICT UPDATE test +# +# This test shows a plausible scenario in which the user might wish to UPDATE a +# value that is also constrained by the unique index that is the arbiter of +# whether the alternative path should be taken. + +setup +{ + CREATE TABLE upsert (key text not null, payload text); + CREATE UNIQUE INDEX ON upsert(lower(key)); +} + +teardown +{ + DROP TABLE upsert; +} + +session "s1" +setup +{ + BEGIN ISOLATION LEVEL READ COMMITTED; +} +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'; } +step "c1" { COMMIT; } +step "a1" { ABORT; } + +session "s2" +setup +{ + BEGIN ISOLATION LEVEL READ COMMITTED; +} +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'; } +step "select2" { SELECT * FROM upsert; } +step "c2" { COMMIT; } +step "a2" { ABORT; } + +# One session (session 2) block-waits on another (session 1) to determine if it +# should proceed with an insert or update. The user can still usefully UPDATE +# a column constrained by a unique index, as the example illustrates. +permutation "insert1" "insert2" "c1" "select2" "c2" +permutation "insert1" "insert2" "a1" "select2" "c2" diff --git a/src/test/isolation/specs/insert-conflict-update-3.spec b/src/test/isolation/specs/insert-conflict-update-3.spec new file mode 100644 index 0000000..94ae3df --- /dev/null +++ b/src/test/isolation/specs/insert-conflict-update-3.spec @@ -0,0 +1,69 @@ +# INSERT...ON CONFLICT UPDATE test +# +# Other INSERT...ON CONFLICT UPDATE isolation tests illustrate the "MVCC +# violation" added to facilitate the feature, whereby a +# not-visible-to-our-snapshot tuple can be updated by our command all the same. +# This is generally needed to provide a guarantee of a successful INSERT or +# UPDATE in READ COMMITTED mode. This MVCC violation is quite distinct from +# the putative "MVCC violation" that has existed in PostgreSQL for many years, +# the EvalPlanQual() mechanism, because that mechanism always starts from a +# tuple that is visible to the command's MVCC snapshot. This test illustrates +# a slightly distinct user-visible consequence of the same MVCC violation +# generally associated with INSERT...ON CONFLICT UPDATE. The impact of the +# MVCC violation goes a little beyond updating MVCC-invisible tuples. +# +# With INSERT...ON CONFLICT UPDATE, the UPDATE predicate is only evaluated +# once, on this conclusively-locked tuple, and not any other version of the +# same tuple. It is therefore possible (in READ COMMITTED mode) that the +# predicate "fail to be satisfied" according to the command's MVCC snapshot. +# It might simply be that there is no row version visible, but it's also +# possible that there is some row version visible, but only as a version that +# doesn't satisfy the predicate. If, however, the conclusively-locked version +# satisfies the predicate, that's good enough, and the tuple is updated. The +# MVCC-snapshot-visible row version is denied the opportunity to prevent the +# UPDATE from taking place, because we don't walk the UPDATE chain in the usual +# way. + +setup +{ + CREATE TABLE colors (key int4 PRIMARY KEY, color text, is_active boolean); + INSERT INTO colors (key, color, is_active) VALUES(1, 'Red', false); + INSERT INTO colors (key, color, is_active) VALUES(2, 'Green', false); + INSERT INTO colors (key, color, is_active) VALUES(3, 'Blue', false); +} + +teardown +{ + DROP TABLE colors; +} + +session "s1" +setup +{ + BEGIN ISOLATION LEVEL READ COMMITTED; +} +step "insert1" { + WITH t AS ( + INSERT INTO colors(key, color, is_active) + VALUES(1, 'Brown', true), (2, 'Gray', true) + ON CONFLICT (key) UPDATE + SET color = EXCLUDED.color + WHERE TARGET.is_active) + SELECT * FROM colors ORDER BY key;} +step "select1surprise" { SELECT * FROM colors ORDER BY key; } +step "c1" { COMMIT; } + +session "s2" +setup +{ + BEGIN ISOLATION LEVEL READ COMMITTED; +} +step "update2" { UPDATE colors SET is_active = true WHERE key = 1; } +step "c2" { COMMIT; } + +# Perhaps surprisingly, the session 1 MVCC-snapshot-visible tuple (the tuple +# with the pre-populated color 'Red') is denied the opportunity to prevent the +# UPDATE from taking place -- only the conclusively-locked tuple version +# matters, and so the tuple with key value 1 was updated to 'Brown' (but not +# tuple with key value 2, since nothing changed there): +permutation "update2" "insert1" "c2" "select1surprise" "c1" diff --git a/src/test/isolation/specs/insert-conflict-update.spec b/src/test/isolation/specs/insert-conflict-update.spec new file mode 100644 index 0000000..6529a0c --- /dev/null +++ b/src/test/isolation/specs/insert-conflict-update.spec @@ -0,0 +1,40 @@ +# INSERT...ON CONFLICT UPDATE test +# +# This test tries to expose problems with the interaction between concurrent +# sessions. + +setup +{ + CREATE TABLE upsert (key int primary key, val text); +} + +teardown +{ + DROP TABLE upsert; +} + +session "s1" +setup +{ + BEGIN ISOLATION LEVEL READ COMMITTED; +} +step "insert1" { INSERT INTO upsert(key, val) VALUES(1, 'insert1') ON CONFLICT (key) UPDATE set val = TARGET.val || ' updated by insert1'; } +step "c1" { COMMIT; } +step "a1" { ABORT; } + +session "s2" +setup +{ + BEGIN ISOLATION LEVEL READ COMMITTED; +} +step "insert2" { INSERT INTO upsert(key, val) VALUES(1, 'insert2') ON CONFLICT (key) UPDATE set val = TARGET.val || ' updated by insert2'; } +step "select2" { SELECT * FROM upsert; } +step "c2" { COMMIT; } +step "a2" { ABORT; } + +# One session (session 2) block-waits on another (session 1) to determine if it +# should proceed with an insert or update. Notably, this entails updating a +# tuple while there is no version of that tuple visible to the updating +# session's snapshot. This is permitted only in READ COMMITTED mode. +permutation "insert1" "insert2" "c1" "select2" "c2" +permutation "insert1" "insert2" "a1" "select2" "c2" diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out new file mode 100644 index 0000000..bd35585 --- /dev/null +++ b/src/test/regress/expected/insert_conflict.out @@ -0,0 +1,242 @@ +-- +-- insert...on conflict update unique index inference +-- +create table insertconflicttest(key int4, fruit text); +-- +-- Single key tests +-- +create unique index key_index on insertconflicttest(key); +-- +-- Explain tests +-- +explain (costs off) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) update set fruit = excluded.fruit; + QUERY PLAN +--------------------------------------------- + Insert on insertconflicttest + -> Result + -> Conflict Update on insertconflicttest +(3 rows) + +-- Should display qual actually attributable to internal sequential scan: +explain (costs off) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) update set fruit = excluded.fruit where target.fruit != 'Cawesh'; + QUERY PLAN +--------------------------------------------- + Insert on insertconflicttest + -> Result + -> Conflict Update on insertconflicttest + Filter: (fruit <> 'Cawesh'::text) +(4 rows) + +-- With EXCLUDED.* expression in scan node: +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key) update set fruit = excluded.fruit where excluded.fruit != 'Elderberry'; + QUERY PLAN +---------------------------------------------------------- + Insert on insertconflicttest + -> Result + -> Conflict Update on insertconflicttest + Filter: ((excluded.fruit) <> 'Elderberry'::text) +(4 rows) + +-- Does the same, but JSON format shows "Arbiter Index": +explain (costs off, format json) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) update set fruit = excluded.fruit where target.fruit != 'Lime' returning *; + QUERY PLAN +-------------------------------------------------- + [ + + { + + "Plan": { + + "Node Type": "ModifyTable", + + "Operation": "Insert", + + "Relation Name": "insertconflicttest", + + "Alias": "insertconflicttest", + + "Arbiter Index": "key_index", + + "Plans": [ + + { + + "Node Type": "Result", + + "Parent Relationship": "Member" + + }, + + { + + "Node Type": "ModifyTable", + + "Operation": "Conflict Update", + + "Parent Relationship": "Member", + + "Relation Name": "insertconflicttest",+ + "Alias": "insertconflicttest", + + "Filter": "(fruit <> 'Lime'::text)" + + } + + ] + + } + + } + + ] +(1 row) + +-- Fails (no unique index inference specification, required for update variant): +insert into insertconflicttest values (1, 'Apple') on conflict update set fruit = excluded.fruit; +ERROR: ON CONFLICT with UPDATE must contain columns or expressions to infer a unique index from +LINE 1: ...nsert into insertconflicttest values (1, 'Apple') on conflic... + ^ +-- inference succeeds: +insert into insertconflicttest values (1, 'Apple') on conflict (key) update set fruit = excluded.fruit; +insert into insertconflicttest values (2, 'Orange') on conflict (key, key, key) update set fruit = excluded.fruit; +-- Succeed, since multi-assignment does not involve subquery: +INSERT INTO insertconflicttest +VALUES (1, 'Apple'), (2, 'Orange') +ON CONFLICT (key) UPDATE SET (fruit, key) = (EXCLUDED.fruit, EXCLUDED.key); +-- Don't accept original table name -- only TARGET.* alias: +insert into insertconflicttest values (1, 'Apple') on conflict (key) update set fruit = insertconflicttest.fruit; +ERROR: invalid reference to FROM-clause entry for table "insertconflicttest" +LINE 1: ...(1, 'Apple') on conflict (key) update set fruit = insertconf... + ^ +HINT: Perhaps you meant to reference the table alias "excluded". +-- inference fails: +insert into insertconflicttest values (3, 'Kiwi') on conflict (key, fruit) update set fruit = excluded.fruit; +ERROR: could not infer which unique index to use from expressions/columns and predicate provided for ON CONFLICT +insert into insertconflicttest values (4, 'Mango') on conflict (fruit, key) update set fruit = excluded.fruit; +ERROR: could not infer which unique index to use from expressions/columns and predicate provided for ON CONFLICT +insert into insertconflicttest values (5, 'Lemon') on conflict (fruit) update set fruit = excluded.fruit; +ERROR: could not infer which unique index to use from expressions/columns and predicate provided for ON CONFLICT +insert into insertconflicttest values (6, 'Passionfruit') on conflict (lower(fruit)) update set fruit = excluded.fruit; +ERROR: could not infer which unique index to use from expressions/columns and predicate provided for ON CONFLICT +drop index key_index; +-- +-- Composite key tests +-- +create unique index comp_key_index on insertconflicttest(key, fruit); +-- inference succeeds: +insert into insertconflicttest values (7, 'Raspberry') on conflict (key, fruit) update set fruit = excluded.fruit; +insert into insertconflicttest values (8, 'Lime') on conflict (fruit, key) update set fruit = excluded.fruit; +-- inference fails: +insert into insertconflicttest values (9, 'Banana') on conflict (key) update set fruit = excluded.fruit; +ERROR: could not infer which unique index to use from expressions/columns and predicate provided for ON CONFLICT +insert into insertconflicttest values (10, 'Blueberry') on conflict (key, key, key) update set fruit = excluded.fruit; +ERROR: could not infer which unique index to use from expressions/columns and predicate provided for ON CONFLICT +insert into insertconflicttest values (11, 'Cherry') on conflict (key, lower(fruit)) update set fruit = excluded.fruit; +ERROR: could not infer which unique index to use from expressions/columns and predicate provided for ON CONFLICT +insert into insertconflicttest values (12, 'Date') on conflict (lower(fruit), key) update set fruit = excluded.fruit; +ERROR: could not infer which unique index to use from expressions/columns and predicate provided for ON CONFLICT +drop index comp_key_index; +-- +-- Partial index tests, no inference predicate specificied +-- +create unique index part_comp_key_index on insertconflicttest(key, fruit) where key < 5; +create unique index expr_part_comp_key_index on insertconflicttest(key, lower(fruit)) where key < 5; +-- inference fails: +insert into insertconflicttest values (13, 'Grape') on conflict (key, fruit) update set fruit = excluded.fruit; +ERROR: could not infer which unique index to use from expressions/columns and predicate provided for ON CONFLICT +insert into insertconflicttest values (14, 'Raisin') on conflict (fruit, key) update set fruit = excluded.fruit; +ERROR: could not infer which unique index to use from expressions/columns and predicate provided for ON CONFLICT +insert into insertconflicttest values (15, 'Cranberry') on conflict (key) update set fruit = excluded.fruit; +ERROR: could not infer which unique index to use from expressions/columns and predicate provided for ON CONFLICT +insert into insertconflicttest values (16, 'Melon') on conflict (key, key, key) update set fruit = excluded.fruit; +ERROR: could not infer which unique index to use from expressions/columns and predicate provided for ON CONFLICT +insert into insertconflicttest values (17, 'Mulberry') on conflict (key, lower(fruit)) update set fruit = excluded.fruit; +ERROR: could not infer which unique index to use from expressions/columns and predicate provided for ON CONFLICT +insert into insertconflicttest values (18, 'Pineapple') on conflict (lower(fruit), key) update set fruit = excluded.fruit; +ERROR: could not infer which unique index to use from expressions/columns and predicate provided for ON CONFLICT +drop index part_comp_key_index; +drop index expr_part_comp_key_index; +-- +-- Expression index tests +-- +create unique index expr_key_index on insertconflicttest(lower(fruit)); +-- inference succeeds: +insert into insertconflicttest values (20, 'Quince') on conflict (lower(fruit)) update set fruit = excluded.fruit; +insert into insertconflicttest values (21, 'Pomegranate') on conflict (lower(fruit), lower(fruit)) update set fruit = excluded.fruit; +-- inference fails: +insert into insertconflicttest values (22, 'Apricot') on conflict (upper(fruit)) update set fruit = excluded.fruit; +ERROR: could not infer which unique index to use from expressions/columns and predicate provided for ON CONFLICT +insert into insertconflicttest values (23, 'Blackberry') on conflict (fruit) update set fruit = excluded.fruit; +ERROR: could not infer which unique index to use from expressions/columns and predicate provided for ON CONFLICT +drop index expr_key_index; +-- +-- Expression index tests (with regular column) +-- +create unique index expr_comp_key_index on insertconflicttest(key, lower(fruit)); +-- inference succeeds: +insert into insertconflicttest values (24, 'Plum') on conflict (key, lower(fruit)) update set fruit = excluded.fruit; +insert into insertconflicttest values (25, 'Peach') on conflict (lower(fruit), key) update set fruit = excluded.fruit; +insert into insertconflicttest values (26, 'Fig') on conflict (lower(fruit), key, lower(fruit), key) update set fruit = excluded.fruit; +-- inference fails: +insert into insertconflicttest values (27, 'Prune') on conflict (key, upper(fruit)) update set fruit = excluded.fruit; +ERROR: could not infer which unique index to use from expressions/columns and predicate provided for ON CONFLICT +insert into insertconflicttest values (28, 'Redcurrant') on conflict (fruit, key) update set fruit = excluded.fruit; +ERROR: could not infer which unique index to use from expressions/columns and predicate provided for ON CONFLICT +insert into insertconflicttest values (29, 'Nectarine') on conflict (key) update set fruit = excluded.fruit; +ERROR: could not infer which unique index to use from expressions/columns and predicate provided for ON CONFLICT +drop index expr_comp_key_index; +-- +-- Non-spurious duplicate violation tests +-- +create unique index key_index on insertconflicttest(key); +create unique index fruit_index on insertconflicttest(fruit); +-- succeeds, since UPDATE happens to update "fruit" to existing value: +insert into insertconflicttest values (26, 'Fig') on conflict (key) update set fruit = excluded.fruit; +-- fails, since UPDATE is to row with key value 26, and we're updating "fruit" +-- to a value that happens to exist in another row ('peach'): +insert into insertconflicttest values (26, 'Peach') on conflict (key) update set fruit = excluded.fruit; +ERROR: duplicate key value violates unique constraint "fruit_index" +DETAIL: Key (fruit)=(Peach) already exists. +-- succeeds, since "key" isn't repeated/referenced in UPDATE, and "fruit" +-- arbitrates that statement updates existing "Fig" row: +insert into insertconflicttest values (25, 'Fig') on conflict (fruit) update set fruit = excluded.fruit; +drop index key_index; +drop index fruit_index; +-- +-- Test partial unique index inference +-- +create unique index partial_key_index on insertconflicttest(key) where fruit like '%berry'; +-- Succeeds +insert into insertconflicttest values (23, 'Blackberry') on conflict (key where fruit like '%berry') update set fruit = excluded.fruit; +insert into insertconflicttest values (23, 'Blackberry') on conflict (key where fruit like '%berry' and fruit = 'inconsequential') ignore; +-- fails +insert into insertconflicttest values (23, 'Blackberry') on conflict (key) update set fruit = excluded.fruit; +ERROR: could not infer which unique index to use from expressions/columns and predicate provided for ON CONFLICT +insert into insertconflicttest values (23, 'Blackberry') on conflict (key where fruit like '%berry' or fruit = 'consequential') ignore; +ERROR: could not infer which unique index to use from expressions/columns and predicate provided for ON CONFLICT +insert into insertconflicttest values (23, 'Blackberry') on conflict (fruit where fruit like '%berry') update set fruit = excluded.fruit; +ERROR: could not infer which unique index to use from expressions/columns and predicate provided for ON CONFLICT +insert into insertconflicttest values (23, 'Uncovered by Index') on conflict (key where fruit like '%berry') ignore; +ERROR: partial arbiter unique index has predicate that does not cover tuple proposed for insertion +DETAIL: ON CONFLICT inference clause implies that the tuple proposed for insertion actually be covered by partial predicate for index "partial_key_index". +HINT: ON CONFLICT inference clause must infer a unique index that covers the final tuple, after BEFORE ROW INSERT triggers fire. +drop index partial_key_index; +-- Cleanup +drop table insertconflicttest; +-- ****************************************************************** +-- * * +-- * Test inheritance (example taken from tutorial) * +-- * * +-- ****************************************************************** +create table cities ( + name text, + population float8, + altitude int -- (in ft) +); +create table capitals ( + state char(2) +) inherits (cities); +-- Create unique indexes. Due to a general limitation of inheritance, +-- uniqueness is only enforced per-relation +create unique index cities_names_unique on cities (name); +create unique index capitals_names_unique on capitals (name); +-- prepopulate the tables. +insert into cities values ('San Francisco', 7.24E+5, 63); +insert into cities values ('Las Vegas', 2.583E+5, 2174); +insert into cities values ('Mariposa', 1200, 1953); +insert into capitals values ('Sacramento', 3.694E+5, 30, 'CA'); +insert into capitals values ('Madison', 1.913E+5, 845, 'WI'); +-- Tests proper for inheritance: +-- fails: +insert into cities values ('Las Vegas', 2.583E+5, 2174) on conflict (name) update set altitude = excluded.altitude; +ERROR: relation "cities" has inheritance children +HINT: Only heap relations without inheritance children are accepted as targets when a unique index is inferred for ON CONFLICT. +insert into cities values ('Las Vegas', 2.583E+5, 2174) on conflict (name) ignore; +ERROR: relation "cities" has inheritance children +HINT: Only heap relations without inheritance children are accepted as targets when a unique index is inferred for ON CONFLICT. +-- Succeeds: +-- There is at least limited support for relations with children: +insert into cities values ('Las Vegas', 2.583E+5, 2174) on conflict ignore; +-- No children, and so no restrictions: +insert into capitals values ('Sacramento', 3.694E+5, 30, 'CA') on conflict (name) update set altitude = excluded.altitude; +insert into capitals values ('Sacramento', 3.694E+5, 30, 'CA') on conflict (name) ignore; +-- clean up +drop table capitals; +drop table cities; diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 74b0450..bc44c45 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -269,7 +269,7 @@ SELECT * FROM atestv2; -- fail (even though regressuser2 can access underlying a ERROR: permission denied for relation atest2 -- Test column level permissions SET SESSION AUTHORIZATION regressuser1; -CREATE TABLE atest5 (one int, two int, three int); +CREATE TABLE atest5 (one int, two int unique, three int); CREATE TABLE atest6 (one int, two int, blue int); GRANT SELECT (one), INSERT (two), UPDATE (three) ON atest5 TO regressuser4; GRANT ALL (one) ON atest5 TO regressuser3; @@ -367,6 +367,11 @@ UPDATE atest5 SET one = 8; -- fail ERROR: permission denied for relation atest5 UPDATE atest5 SET three = 5, one = 2; -- fail ERROR: permission denied for relation atest5 +INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) UPDATE set three = 10; -- ok +INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) UPDATE set one = 8; -- fails (due to UPDATE) +ERROR: permission denied for relation atest5 +INSERT INTO atest5(three) VALUES (4) ON CONFLICT (two) UPDATE set three = 10; -- fails (due to INSERT) +ERROR: permission denied for relation atest5 SET SESSION AUTHORIZATION regressuser1; REVOKE ALL (one) ON atest5 FROM regressuser4; GRANT SELECT (one,two,blue) ON atest6 TO regressuser4; diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 21817d8..07cb54f 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -1179,6 +1179,96 @@ NOTICE: f_leak => yyyyyy (3 rows) -- +-- INSERT ... ON CONFLICT UPDATE and Row-level security +-- +-- Would fail with unique violation, but with ON CONFLICT fails as row is +-- locked for update (notably, existing/target row is not leaked): +INSERT INTO document VALUES (8, 44, 1, 'rls_regress_user1', 'my fourth manga') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle; +ERROR: new row violates WITH CHECK OPTION for "document" +-- Can't insert new violating tuple, either: +INSERT INTO document VALUES (22, 11, 2, 'rls_regress_user2', 'mediocre novel') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle; +ERROR: new row violates WITH CHECK OPTION for "document" +-- INSERT path is taken here, so UPDATE targelist doesn't matter: +INSERT INTO document VALUES (33, 22, 1, 'rls_regress_user1', 'okay science fiction') + ON CONFLICT (did) UPDATE SET dauthor = 'rls_regress_user3' RETURNING *; + did | cid | dlevel | dauthor | dtitle +-----+-----+--------+-------------------+---------------------- + 33 | 22 | 1 | rls_regress_user1 | okay science fiction +(1 row) + +-- Update path will now taken for same query, so UPDATE targelist now matters +-- (this is the same query as the last, but now fails): +INSERT INTO document VALUES (33, 22, 1, 'rls_regress_user1', 'okay science fiction') + ON CONFLICT (did) UPDATE SET dauthor = 'rls_regress_user3' RETURNING *; +ERROR: new row violates WITH CHECK OPTION for "document" +SET SESSION AUTHORIZATION rls_regress_user0; +DROP POLICY p1 ON document; +CREATE POLICY p1 ON document FOR SELECT USING (true); +CREATE POLICY p2 ON document FOR INSERT WITH CHECK (dauthor = current_user); +CREATE POLICY p3 ON document FOR UPDATE + USING (cid = (SELECT cid from category WHERE cname = 'novel')) + WITH CHECK (dauthor = current_user); +SET SESSION AUTHORIZATION rls_regress_user1; +-- Again, would fail with unique violation, but with ON CONFLICT fails as row is +-- locked for update (notably, existing/target row is not leaked, which is what +-- failed to satisfy WITH CHECK options - not row proposed for insertion by +-- user): +INSERT INTO document VALUES (8, 44, 1, 'rls_regress_user1', 'my fourth manga') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle; +ERROR: new row violates WITH CHECK OPTION for "document" +-- Again, can't insert new violating tuple, either (unsuccessfully inserted tuple +-- values are reported here, though) +-- +-- Violates actual CHECK OPTION within UPDATE: +INSERT INTO document VALUES (2, 11, 1, 'rls_regress_user2', 'my first novel') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle, dauthor = EXCLUDED.dauthor; +ERROR: new row violates WITH CHECK OPTION for "document" +-- Violates USING qual for UPDATE policy p3, interpreted here as CHECK OPTION. +-- +-- UPDATE path is taken, but UPDATE fails purely because *existing* row to be +-- updated is not a "novel"/cid 11 (row is not leaked, even though we have +-- SELECT privileges sufficient to see the row in this instance): +INSERT INTO document VALUES (33, 11, 1, 'rls_regress_user1', 'Some novel, replaces sci-fi') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle; +ERROR: new row violates WITH CHECK OPTION for "document" +-- Fine (we UPDATE): +INSERT INTO document VALUES (2, 11, 1, 'rls_regress_user1', 'my first novel') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; + did | cid | dlevel | dauthor | dtitle +-----+-----+--------+-------------------+---------------- + 2 | 11 | 2 | rls_regress_user1 | my first novel +(1 row) + +-- Fine (we INSERT, so "cid = 33" isn't evaluated): +INSERT INTO document VALUES (78, 11, 1, 'rls_regress_user1', 'some other novel') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *; + did | cid | dlevel | dauthor | dtitle +-----+-----+--------+-------------------+------------------ + 78 | 11 | 1 | rls_regress_user1 | some other novel +(1 row) + +-- Fail (same query, but we UPDATE, so "cid = 33" is evaluated at end of +-- UPDATE): +INSERT INTO document VALUES (78, 11, 1, 'rls_regress_user1', 'some other novel') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *; +ERROR: new row violates WITH CHECK OPTION for "document" +-- Fail (we UPDATE, so dauthor assignment is evaluated at end of UPDATE): +INSERT INTO document VALUES (78, 11, 1, 'rls_regress_user1', 'some other novel') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle, dauthor = 'rls_regress_user2'; +ERROR: new row violates WITH CHECK OPTION for "document" +-- Don't fail because INSERT doesn't satisfy WITH CHECK option that originated +-- as a barrier/USING() qual from the UPDATE. Note that the UPDATE path +-- *isn't* taken, and so UPDATE-related policy does not apply: +INSERT INTO document VALUES (88, 33, 1, 'rls_regress_user1', 'technology book, can only insert') + ON CONFLICT (did) UPDATE SET dtitle = upper(EXCLUDED.dtitle) RETURNING *; + did | cid | dlevel | dauthor | dtitle +-----+-----+--------+-------------------+---------------------------------- + 88 | 33 | 1 | rls_regress_user1 | technology book, can only insert +(1 row) + +-- -- ROLE/GROUP -- SET SESSION AUTHORIZATION rls_regress_user0; diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index d50b103..c634579 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1123,6 +1123,10 @@ SELECT * FROM shoelace_log ORDER BY sl_name; SELECT * FROM shoelace_obsolete WHERE sl_avail = 0; insert into shoelace values ('sl9', 0, 'pink', 35.0, 'inch', 0.0); insert into shoelace values ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0); +-- Unsupported (even though a similar updatable view construct is) +insert into shoelace values ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0) + on conflict ignore; +ERROR: INSERT with ON CONFLICT clause may not target relation with INSERT or UPDATE rules SELECT * FROM shoelace_obsolete ORDER BY sl_len_cm; sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm ------------+----------+------------+--------+----------+----------- @@ -2351,6 +2355,23 @@ DETAIL: Key (id3a, id3c)=(1, 13) is not present in table "rule_and_refint_t2". insert into rule_and_refint_t3 values (1, 13, 11, 'row6'); ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey" DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1". +-- Ordinary table +insert into rule_and_refint_t3 values (1, 13, 11, 'row6') + on conflict ignore; +ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey" +DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1". +-- rule not fired, so fk violation +insert into rule_and_refint_t3 values (1, 13, 11, 'row6') + on conflict (id3a, id3b, id3c) update + set id3b = excluded.id3b; +ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey" +DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1". +-- rule fired, so unsupported (only updatable views have limited support) +insert into shoelace values ('sl9', 0, 'pink', 35.0, 'inch', 0.0) + on conflict (id1a, id1b) update + set sl_avail = excluded.sl_avail; +ERROR: relation "shoelace" is not an ordinary table +HINT: Only ordinary tables are accepted as targets when a unique index is inferred for ON CONFLICT. create rule rule_and_refint_t3_ins as on insert to rule_and_refint_t3 where (exists (select 1 from rule_and_refint_t3 where (((rule_and_refint_t3.id3a = new.id3a) diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index b14410f..9ba3a44 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -639,6 +639,28 @@ from (0 rows) -- +-- Test case for subselect within UPDATE of INSERT...ON CONFLICT UPDATE +-- +create temp table upsert(key int4 primary key, val text); +insert into upsert values(1, 'val') on conflict (key) update set val = 'not seen'; +insert into upsert values(1, 'val') on conflict (key) update set val = 'unsupported ' || (select f1 from int4_tbl where f1 != 0 limit 1)::text; +ERROR: cannot use subquery in ON CONFLICT UPDATE +LINE 1: ...conflict (key) update set val = 'unsupported ' || (select f1... + ^ +select * from upsert; + key | val +-----+----- + 1 | val +(1 row) + +with aa as (select 'int4_tbl' u from int4_tbl limit 1) +insert into upsert values (1, 'x'), (999, 'y') +on conflict (key) update set val = (select u from aa) +returning *; +ERROR: cannot use subquery in ON CONFLICT UPDATE +LINE 3: on conflict (key) update set val = (select u from aa) + ^ +-- -- Test case for cross-type partial matching in hashed subplan (bug #7597) -- create temp table outer_7597 (f1 int4, f2 int4); diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index f1a5fde..77dfa06 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -274,7 +274,7 @@ drop sequence ttdummy_seq; -- tests for per-statement triggers -- CREATE TABLE log_table (tstamp timestamp default timeofday()::timestamp); -CREATE TABLE main_table (a int, b int); +CREATE TABLE main_table (a int unique, b int); COPY main_table (a,b) FROM stdin; CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS ' BEGIN @@ -291,6 +291,14 @@ FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_ins_stmt'); -- CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON main_table EXECUTE PROCEDURE trigger_func('after_upd_stmt'); +-- Both insert and update statement level triggers (before and after) should +-- fire. Doesn't fire UPDATE before trigger, but only because one isn't +-- defined. +INSERT INTO main_table (a, b) VALUES (5, 10) ON CONFLICT (a) + UPDATE SET b = EXCLUDED.b; +NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT +NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +NOTICE: trigger_func(after_ins_stmt) called: action = INSERT, when = AFTER, level = STATEMENT CREATE TRIGGER after_upd_row_trig AFTER UPDATE ON main_table FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_row'); INSERT INTO main_table DEFAULT VALUES; @@ -305,6 +313,8 @@ NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, lev -- UPDATE that effects zero rows should still call per-statement trigger UPDATE main_table SET a = a + 2 WHERE b > 100; NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT +-- constraint now unneeded +ALTER TABLE main_table DROP CONSTRAINT main_table_a_key; -- COPY should fire per-row and per-statement INSERT triggers COPY main_table (a, b) FROM stdin; NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT @@ -1731,3 +1741,93 @@ select * from self_ref_trigger; drop table self_ref_trigger; drop function self_ref_trigger_ins_func(); drop function self_ref_trigger_del_func(); +-- +-- Verify behavior of before and after triggers with INSERT...ON CONFLICT +-- UPDATE +-- +create table upsert (key int4 primary key, color text); +create function upsert_before_func() + returns trigger language plpgsql as +$$ +begin + if (TG_OP = 'UPDATE') then + raise warning 'before update (old): %', old.*::text; + raise warning 'before update (new): %', new.*::text; + elsif (TG_OP = 'INSERT') then + raise warning 'before insert (new): %', new.*::text; + if new.key % 2 = 0 then + new.key := new.key + 1; + new.color := new.color || ' trig modified'; + raise warning 'before insert (new, modified): %', new.*::text; + end if; + end if; + return new; +end; +$$; +create trigger upsert_before_trig before insert or update on upsert + for each row execute procedure upsert_before_func(); +create function upsert_after_func() + returns trigger language plpgsql as +$$ +begin + if (TG_OP = 'UPDATE') then + raise warning 'after update (old): %', new.*::text; + raise warning 'after update (new): %', new.*::text; + elsif (TG_OP = 'INSERT') then + raise warning 'after insert (new): %', new.*::text; + end if; + return null; +end; +$$; +create trigger upsert_after_trig after insert or update on upsert + for each row execute procedure upsert_after_func(); +insert into upsert values(1, 'black') on conflict (key) update set color = 'updated ' || target.color; +WARNING: before insert (new): (1,black) +WARNING: after insert (new): (1,black) +insert into upsert values(2, 'red') on conflict (key) update set color = 'updated ' || target.color; +WARNING: before insert (new): (2,red) +WARNING: before insert (new, modified): (3,"red trig modified") +WARNING: after insert (new): (3,"red trig modified") +insert into upsert values(3, 'orange') on conflict (key) update set color = 'updated ' || target.color; +WARNING: before insert (new): (3,orange) +WARNING: before update (old): (3,"red trig modified") +WARNING: before update (new): (3,"updated red trig modified") +WARNING: after update (old): (3,"updated red trig modified") +WARNING: after update (new): (3,"updated red trig modified") +insert into upsert values(4, 'green') on conflict (key) update set color = 'updated ' || target.color; +WARNING: before insert (new): (4,green) +WARNING: before insert (new, modified): (5,"green trig modified") +WARNING: after insert (new): (5,"green trig modified") +insert into upsert values(5, 'purple') on conflict (key) update set color = 'updated ' || target.color; +WARNING: before insert (new): (5,purple) +WARNING: before update (old): (5,"green trig modified") +WARNING: before update (new): (5,"updated green trig modified") +WARNING: after update (old): (5,"updated green trig modified") +WARNING: after update (new): (5,"updated green trig modified") +insert into upsert values(6, 'white') on conflict (key) update set color = 'updated ' || target.color; +WARNING: before insert (new): (6,white) +WARNING: before insert (new, modified): (7,"white trig modified") +WARNING: after insert (new): (7,"white trig modified") +insert into upsert values(7, 'pink') on conflict (key) update set color = 'updated ' || target.color; +WARNING: before insert (new): (7,pink) +WARNING: before update (old): (7,"white trig modified") +WARNING: before update (new): (7,"updated white trig modified") +WARNING: after update (old): (7,"updated white trig modified") +WARNING: after update (new): (7,"updated white trig modified") +insert into upsert values(8, 'yellow') on conflict (key) update set color = 'updated ' || target.color; +WARNING: before insert (new): (8,yellow) +WARNING: before insert (new, modified): (9,"yellow trig modified") +WARNING: after insert (new): (9,"yellow trig modified") +select * from upsert; + key | color +-----+----------------------------- + 1 | black + 3 | updated red trig modified + 5 | updated green trig modified + 7 | updated white trig modified + 9 | yellow trig modified +(5 rows) + +drop table upsert; +drop function upsert_before_func(); +drop function upsert_after_func(); diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out index 80c5706..22b5bc1 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -215,6 +215,10 @@ INSERT INTO rw_view15 VALUES (3, 'ROW 3'); -- should fail ERROR: cannot insert into column "upper" of view "rw_view15" DETAIL: View columns that are not columns of their base relation are not updatable. INSERT INTO rw_view15 (a) VALUES (3); -- should be OK +INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT IGNORE; -- succeeds +INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) IGNORE; -- fails, unsupported +ERROR: relation "rw_view15" is not an ordinary table +HINT: Only ordinary tables are accepted as targets when a unique index is inferred for ON CONFLICT. ALTER VIEW rw_view15 ALTER COLUMN upper SET DEFAULT 'NOT SET'; INSERT INTO rw_view15 (a) VALUES (4); -- should fail ERROR: cannot insert into column "upper" of view "rw_view15" diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out index 1de2a86..58714ac 100644 --- a/src/test/regress/expected/update.out +++ b/src/test/regress/expected/update.out @@ -147,4 +147,31 @@ SELECT a, b, char_length(c) FROM update_test; 42 | 12 | 10000 (4 rows) +ALTER TABLE update_test ADD constraint uuu UNIQUE(a); +-- fail, update predicates are disallowed: +INSERT INTO update_test VALUES(31, 77) ON CONFLICT (a) UPDATE SET b = 16 +WHERE a NOT IN (SELECT a FROM update_test); +ERROR: cannot use subquery in ON CONFLICT UPDATE +LINE 2: WHERE a NOT IN (SELECT a FROM update_test); + ^ +INSERT INTO update_test VALUES(31, 77) ON CONFLICT (a) UPDATE SET b = 16 +WHERE EXISTS(SELECT b FROM update_test); +ERROR: cannot use subquery in ON CONFLICT UPDATE +LINE 2: WHERE EXISTS(SELECT b FROM update_test); + ^ +INSERT INTO update_test VALUES(31, 77) ON CONFLICT (a) UPDATE SET b = 16 +WHERE a IN (SELECT a FROM update_test); +ERROR: cannot use subquery in ON CONFLICT UPDATE +LINE 2: WHERE a IN (SELECT a FROM update_test); + ^ +INSERT INTO update_test VALUES(31, 77) ON CONFLICT (a) UPDATE SET b = 16 +WHERE a = ALL(SELECT a FROM update_test); +ERROR: cannot use subquery in ON CONFLICT UPDATE +LINE 2: WHERE a = ALL(SELECT a FROM update_test); + ^ +INSERT INTO update_test VALUES(31, 77) ON CONFLICT (a) UPDATE SET b = 16 +WHERE a = ANY(SELECT a FROM update_test); +ERROR: cannot use subquery in ON CONFLICT UPDATE +LINE 2: WHERE a = ANY(SELECT a FROM update_test); + ^ DROP TABLE update_test; diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out index 06b372b..81d664e 100644 --- a/src/test/regress/expected/with.out +++ b/src/test/regress/expected/with.out @@ -1806,6 +1806,80 @@ SELECT * FROM y; -400 (22 rows) +-- data-modifying WITH containing INSERT...ON CONFLICT UPDATE +CREATE TABLE z AS SELECT i AS k, (i || ' v')::text v FROM generate_series(1, 16, 3) i; +ALTER TABLE z ADD UNIQUE (k); +WITH t AS ( + INSERT INTO z SELECT i, 'insert' + FROM generate_series(0, 16) i + ON CONFLICT (k) UPDATE SET v = TARGET.v || ', now update' + RETURNING * +) +SELECT * FROM t JOIN y ON t.k = y.a ORDER BY a, k; + k | v | a +---+--------+--- + 0 | insert | 0 + 0 | insert | 0 +(2 rows) + +-- New query/snapshot demonstrates side-effects of previous query. +SELECT * FROM z ORDER BY k; + k | v +----+------------------ + 0 | insert + 1 | 1 v, now update + 2 | insert + 3 | insert + 4 | 4 v, now update + 5 | insert + 6 | insert + 7 | 7 v, now update + 8 | insert + 9 | insert + 10 | 10 v, now update + 11 | insert + 12 | insert + 13 | 13 v, now update + 14 | insert + 15 | insert + 16 | 16 v, now update +(17 rows) + +-- +-- All these cases should fail, due to restrictions imposed upon the UPDATE +-- portion of the query. +-- +WITH aa AS (SELECT 1 a, 2 b) +INSERT INTO z VALUES(1, 'insert') +ON CONFLICT (k) UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1); +ERROR: cannot use subquery in ON CONFLICT UPDATE +LINE 3: ON CONFLICT (k) UPDATE SET v = (SELECT b || ' update' FROM a... + ^ +WITH aa AS (SELECT 1 a, 2 b) +INSERT INTO z VALUES(1, 'insert') +ON CONFLICT (k) UPDATE SET v = ' update' WHERE target.k = (SELECT a FROM aa); +ERROR: cannot use subquery in ON CONFLICT UPDATE +LINE 3: ...ICT (k) UPDATE SET v = ' update' WHERE target.k = (SELECT a ... + ^ +WITH aa AS (SELECT 1 a, 2 b) +INSERT INTO z VALUES(1, 'insert') +ON CONFLICT (k) UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1); +ERROR: cannot use subquery in ON CONFLICT UPDATE +LINE 3: ON CONFLICT (k) UPDATE SET v = (SELECT b || ' update' FROM a... + ^ +WITH aa AS (SELECT 'a' a, 'b' b UNION ALL SELECT 'a' a, 'b' b) +INSERT INTO z VALUES(1, 'insert') +ON CONFLICT (k) UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 'a' LIMIT 1); +ERROR: cannot use subquery in ON CONFLICT UPDATE +LINE 3: ON CONFLICT (k) UPDATE SET v = (SELECT b || ' update' FROM a... + ^ +WITH aa AS (SELECT 1 a, 2 b) +INSERT INTO z VALUES(1, (SELECT b || ' insert' FROM aa WHERE a = 1 )) +ON CONFLICT (k) UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1); +ERROR: cannot use subquery in ON CONFLICT UPDATE +LINE 3: ON CONFLICT (k) UPDATE SET v = (SELECT b || ' update' FROM a... + ^ +DROP TABLE z; -- check that run to completion happens in proper ordering TRUNCATE TABLE y; INSERT INTO y SELECT generate_series(1, 3); diff --git a/src/test/regress/input/constraints.source b/src/test/regress/input/constraints.source index 8ec0054..46bce36 100644 --- a/src/test/regress/input/constraints.source +++ b/src/test/regress/input/constraints.source @@ -292,6 +292,11 @@ INSERT INTO UNIQUE_TBL VALUES (5, 'one'); INSERT INTO UNIQUE_TBL (t) VALUES ('six'); INSERT INTO UNIQUE_TBL (t) VALUES ('seven'); +INSERT INTO UNIQUE_TBL VALUES (5, 'five-upsert-insert') ON CONFLICT (i) UPDATE SET t = 'five-upsert-update'; +INSERT INTO UNIQUE_TBL VALUES (6, 'six-upsert-insert') ON CONFLICT (i) UPDATE SET t = 'six-upsert-update'; +-- should fail +INSERT INTO UNIQUE_TBL VALUES (1, 'a'), (2, 'b'), (2, 'b') ON CONFLICT (i) UPDATE SET t = 'fails'; + SELECT '' AS five, * FROM UNIQUE_TBL; DROP TABLE UNIQUE_TBL; diff --git a/src/test/regress/output/constraints.source b/src/test/regress/output/constraints.source index 0d32a9eab..add3f0c 100644 --- a/src/test/regress/output/constraints.source +++ b/src/test/regress/output/constraints.source @@ -421,16 +421,23 @@ INSERT INTO UNIQUE_TBL VALUES (4, 'four'); INSERT INTO UNIQUE_TBL VALUES (5, 'one'); INSERT INTO UNIQUE_TBL (t) VALUES ('six'); INSERT INTO UNIQUE_TBL (t) VALUES ('seven'); +INSERT INTO UNIQUE_TBL VALUES (5, 'five-upsert-insert') ON CONFLICT (i) UPDATE SET t = 'five-upsert-update'; +INSERT INTO UNIQUE_TBL VALUES (6, 'six-upsert-insert') ON CONFLICT (i) UPDATE SET t = 'six-upsert-update'; +-- should fail +INSERT INTO UNIQUE_TBL VALUES (1, 'a'), (2, 'b'), (2, 'b') ON CONFLICT (i) UPDATE SET t = 'fails'; +ERROR: ON CONFLICT UPDATE command could not lock/update self-inserted tuple +HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values. SELECT '' AS five, * FROM UNIQUE_TBL; - five | i | t -------+---+------- + five | i | t +------+---+-------------------- | 1 | one | 2 | two | 4 | four - | 5 | one | | six | | seven -(6 rows) + | 5 | five-upsert-update + | 6 | six-upsert-insert +(7 rows) DROP TABLE UNIQUE_TBL; CREATE TABLE UNIQUE_TBL (i int, t text, diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index e0ae2f2..528d3b7 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -36,6 +36,7 @@ test: geometry horology regex oidjoins type_sanity opr_sanity # These four each depend on the previous one # ---------- test: insert +test: insert_conflict test: create_function_1 test: create_type test: create_table diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 7f762bd..b7c8f53 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -50,6 +50,7 @@ test: oidjoins test: type_sanity test: opr_sanity test: insert +test: insert_conflict test: create_function_1 test: create_type test: create_table diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql new file mode 100644 index 0000000..472d4ab --- /dev/null +++ b/src/test/regress/sql/insert_conflict.sql @@ -0,0 +1,192 @@ +-- +-- insert...on conflict update unique index inference +-- +create table insertconflicttest(key int4, fruit text); + +-- +-- Single key tests +-- +create unique index key_index on insertconflicttest(key); + +-- +-- Explain tests +-- +explain (costs off) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) update set fruit = excluded.fruit; +-- Should display qual actually attributable to internal sequential scan: +explain (costs off) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) update set fruit = excluded.fruit where target.fruit != 'Cawesh'; +-- With EXCLUDED.* expression in scan node: +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key) update set fruit = excluded.fruit where excluded.fruit != 'Elderberry'; +-- Does the same, but JSON format shows "Arbiter Index": +explain (costs off, format json) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) update set fruit = excluded.fruit where target.fruit != 'Lime' returning *; + +-- Fails (no unique index inference specification, required for update variant): +insert into insertconflicttest values (1, 'Apple') on conflict update set fruit = excluded.fruit; + +-- inference succeeds: +insert into insertconflicttest values (1, 'Apple') on conflict (key) update set fruit = excluded.fruit; +insert into insertconflicttest values (2, 'Orange') on conflict (key, key, key) update set fruit = excluded.fruit; + +-- Succeed, since multi-assignment does not involve subquery: +INSERT INTO insertconflicttest +VALUES (1, 'Apple'), (2, 'Orange') +ON CONFLICT (key) UPDATE SET (fruit, key) = (EXCLUDED.fruit, EXCLUDED.key); +-- Don't accept original table name -- only TARGET.* alias: +insert into insertconflicttest values (1, 'Apple') on conflict (key) update set fruit = insertconflicttest.fruit; + +-- inference fails: +insert into insertconflicttest values (3, 'Kiwi') on conflict (key, fruit) update set fruit = excluded.fruit; +insert into insertconflicttest values (4, 'Mango') on conflict (fruit, key) update set fruit = excluded.fruit; +insert into insertconflicttest values (5, 'Lemon') on conflict (fruit) update set fruit = excluded.fruit; +insert into insertconflicttest values (6, 'Passionfruit') on conflict (lower(fruit)) update set fruit = excluded.fruit; + +drop index key_index; + +-- +-- Composite key tests +-- +create unique index comp_key_index on insertconflicttest(key, fruit); + +-- inference succeeds: +insert into insertconflicttest values (7, 'Raspberry') on conflict (key, fruit) update set fruit = excluded.fruit; +insert into insertconflicttest values (8, 'Lime') on conflict (fruit, key) update set fruit = excluded.fruit; + +-- inference fails: +insert into insertconflicttest values (9, 'Banana') on conflict (key) update set fruit = excluded.fruit; +insert into insertconflicttest values (10, 'Blueberry') on conflict (key, key, key) update set fruit = excluded.fruit; +insert into insertconflicttest values (11, 'Cherry') on conflict (key, lower(fruit)) update set fruit = excluded.fruit; +insert into insertconflicttest values (12, 'Date') on conflict (lower(fruit), key) update set fruit = excluded.fruit; + +drop index comp_key_index; + +-- +-- Partial index tests, no inference predicate specificied +-- +create unique index part_comp_key_index on insertconflicttest(key, fruit) where key < 5; +create unique index expr_part_comp_key_index on insertconflicttest(key, lower(fruit)) where key < 5; + +-- inference fails: +insert into insertconflicttest values (13, 'Grape') on conflict (key, fruit) update set fruit = excluded.fruit; +insert into insertconflicttest values (14, 'Raisin') on conflict (fruit, key) update set fruit = excluded.fruit; +insert into insertconflicttest values (15, 'Cranberry') on conflict (key) update set fruit = excluded.fruit; +insert into insertconflicttest values (16, 'Melon') on conflict (key, key, key) update set fruit = excluded.fruit; +insert into insertconflicttest values (17, 'Mulberry') on conflict (key, lower(fruit)) update set fruit = excluded.fruit; +insert into insertconflicttest values (18, 'Pineapple') on conflict (lower(fruit), key) update set fruit = excluded.fruit; + +drop index part_comp_key_index; +drop index expr_part_comp_key_index; + +-- +-- Expression index tests +-- +create unique index expr_key_index on insertconflicttest(lower(fruit)); + +-- inference succeeds: +insert into insertconflicttest values (20, 'Quince') on conflict (lower(fruit)) update set fruit = excluded.fruit; +insert into insertconflicttest values (21, 'Pomegranate') on conflict (lower(fruit), lower(fruit)) update set fruit = excluded.fruit; + +-- inference fails: +insert into insertconflicttest values (22, 'Apricot') on conflict (upper(fruit)) update set fruit = excluded.fruit; +insert into insertconflicttest values (23, 'Blackberry') on conflict (fruit) update set fruit = excluded.fruit; + +drop index expr_key_index; + +-- +-- Expression index tests (with regular column) +-- +create unique index expr_comp_key_index on insertconflicttest(key, lower(fruit)); + +-- inference succeeds: +insert into insertconflicttest values (24, 'Plum') on conflict (key, lower(fruit)) update set fruit = excluded.fruit; +insert into insertconflicttest values (25, 'Peach') on conflict (lower(fruit), key) update set fruit = excluded.fruit; +insert into insertconflicttest values (26, 'Fig') on conflict (lower(fruit), key, lower(fruit), key) update set fruit = excluded.fruit; + +-- inference fails: +insert into insertconflicttest values (27, 'Prune') on conflict (key, upper(fruit)) update set fruit = excluded.fruit; +insert into insertconflicttest values (28, 'Redcurrant') on conflict (fruit, key) update set fruit = excluded.fruit; +insert into insertconflicttest values (29, 'Nectarine') on conflict (key) update set fruit = excluded.fruit; + +drop index expr_comp_key_index; + +-- +-- Non-spurious duplicate violation tests +-- +create unique index key_index on insertconflicttest(key); +create unique index fruit_index on insertconflicttest(fruit); + +-- succeeds, since UPDATE happens to update "fruit" to existing value: +insert into insertconflicttest values (26, 'Fig') on conflict (key) update set fruit = excluded.fruit; +-- fails, since UPDATE is to row with key value 26, and we're updating "fruit" +-- to a value that happens to exist in another row ('peach'): +insert into insertconflicttest values (26, 'Peach') on conflict (key) update set fruit = excluded.fruit; +-- succeeds, since "key" isn't repeated/referenced in UPDATE, and "fruit" +-- arbitrates that statement updates existing "Fig" row: +insert into insertconflicttest values (25, 'Fig') on conflict (fruit) update set fruit = excluded.fruit; + +drop index key_index; +drop index fruit_index; + +-- +-- Test partial unique index inference +-- +create unique index partial_key_index on insertconflicttest(key) where fruit like '%berry'; + +-- Succeeds +insert into insertconflicttest values (23, 'Blackberry') on conflict (key where fruit like '%berry') update set fruit = excluded.fruit; +insert into insertconflicttest values (23, 'Blackberry') on conflict (key where fruit like '%berry' and fruit = 'inconsequential') ignore; + +-- fails +insert into insertconflicttest values (23, 'Blackberry') on conflict (key) update set fruit = excluded.fruit; +insert into insertconflicttest values (23, 'Blackberry') on conflict (key where fruit like '%berry' or fruit = 'consequential') ignore; +insert into insertconflicttest values (23, 'Blackberry') on conflict (fruit where fruit like '%berry') update set fruit = excluded.fruit; +insert into insertconflicttest values (23, 'Uncovered by Index') on conflict (key where fruit like '%berry') ignore; + +drop index partial_key_index; + +-- Cleanup +drop table insertconflicttest; + +-- ****************************************************************** +-- * * +-- * Test inheritance (example taken from tutorial) * +-- * * +-- ****************************************************************** +create table cities ( + name text, + population float8, + altitude int -- (in ft) +); + +create table capitals ( + state char(2) +) inherits (cities); + +-- Create unique indexes. Due to a general limitation of inheritance, +-- uniqueness is only enforced per-relation +create unique index cities_names_unique on cities (name); +create unique index capitals_names_unique on capitals (name); + +-- prepopulate the tables. +insert into cities values ('San Francisco', 7.24E+5, 63); +insert into cities values ('Las Vegas', 2.583E+5, 2174); +insert into cities values ('Mariposa', 1200, 1953); + +insert into capitals values ('Sacramento', 3.694E+5, 30, 'CA'); +insert into capitals values ('Madison', 1.913E+5, 845, 'WI'); + +-- Tests proper for inheritance: + +-- fails: +insert into cities values ('Las Vegas', 2.583E+5, 2174) on conflict (name) update set altitude = excluded.altitude; +insert into cities values ('Las Vegas', 2.583E+5, 2174) on conflict (name) ignore; + +-- Succeeds: + +-- There is at least limited support for relations with children: +insert into cities values ('Las Vegas', 2.583E+5, 2174) on conflict ignore; +-- No children, and so no restrictions: +insert into capitals values ('Sacramento', 3.694E+5, 30, 'CA') on conflict (name) update set altitude = excluded.altitude; +insert into capitals values ('Sacramento', 3.694E+5, 30, 'CA') on conflict (name) ignore; + +-- clean up +drop table capitals; +drop table cities; diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql index f97a75a..861eac6 100644 --- a/src/test/regress/sql/privileges.sql +++ b/src/test/regress/sql/privileges.sql @@ -194,7 +194,7 @@ SELECT * FROM atestv2; -- fail (even though regressuser2 can access underlying a -- Test column level permissions SET SESSION AUTHORIZATION regressuser1; -CREATE TABLE atest5 (one int, two int, three int); +CREATE TABLE atest5 (one int, two int unique, three int); CREATE TABLE atest6 (one int, two int, blue int); GRANT SELECT (one), INSERT (two), UPDATE (three) ON atest5 TO regressuser4; GRANT ALL (one) ON atest5 TO regressuser3; @@ -245,6 +245,9 @@ INSERT INTO atest5 VALUES (5,5,5); -- fail UPDATE atest5 SET three = 10; -- ok UPDATE atest5 SET one = 8; -- fail UPDATE atest5 SET three = 5, one = 2; -- fail +INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) UPDATE set three = 10; -- ok +INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) UPDATE set one = 8; -- fails (due to UPDATE) +INSERT INTO atest5(three) VALUES (4) ON CONFLICT (two) UPDATE set three = 10; -- fails (due to INSERT) SET SESSION AUTHORIZATION regressuser1; REVOKE ALL (one) ON atest5 FROM regressuser4; diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql index ed7adbf..5c660d5 100644 --- a/src/test/regress/sql/rowsecurity.sql +++ b/src/test/regress/sql/rowsecurity.sql @@ -436,6 +436,79 @@ DELETE FROM only t1 WHERE f_leak(b) RETURNING oid, *, t1; DELETE FROM t1 WHERE f_leak(b) RETURNING oid, *, t1; -- +-- INSERT ... ON CONFLICT UPDATE and Row-level security +-- + +-- Would fail with unique violation, but with ON CONFLICT fails as row is +-- locked for update (notably, existing/target row is not leaked): +INSERT INTO document VALUES (8, 44, 1, 'rls_regress_user1', 'my fourth manga') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle; + +-- Can't insert new violating tuple, either: +INSERT INTO document VALUES (22, 11, 2, 'rls_regress_user2', 'mediocre novel') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle; + +-- INSERT path is taken here, so UPDATE targelist doesn't matter: +INSERT INTO document VALUES (33, 22, 1, 'rls_regress_user1', 'okay science fiction') + ON CONFLICT (did) UPDATE SET dauthor = 'rls_regress_user3' RETURNING *; + +-- Update path will now taken for same query, so UPDATE targelist now matters +-- (this is the same query as the last, but now fails): +INSERT INTO document VALUES (33, 22, 1, 'rls_regress_user1', 'okay science fiction') + ON CONFLICT (did) UPDATE SET dauthor = 'rls_regress_user3' RETURNING *; + +SET SESSION AUTHORIZATION rls_regress_user0; +DROP POLICY p1 ON document; + +CREATE POLICY p1 ON document FOR SELECT USING (true); +CREATE POLICY p2 ON document FOR INSERT WITH CHECK (dauthor = current_user); +CREATE POLICY p3 ON document FOR UPDATE + USING (cid = (SELECT cid from category WHERE cname = 'novel')) + WITH CHECK (dauthor = current_user); + +SET SESSION AUTHORIZATION rls_regress_user1; + +-- Again, would fail with unique violation, but with ON CONFLICT fails as row is +-- locked for update (notably, existing/target row is not leaked, which is what +-- failed to satisfy WITH CHECK options - not row proposed for insertion by +-- user): +INSERT INTO document VALUES (8, 44, 1, 'rls_regress_user1', 'my fourth manga') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle; + +-- Again, can't insert new violating tuple, either (unsuccessfully inserted tuple +-- values are reported here, though) +-- +-- Violates actual CHECK OPTION within UPDATE: +INSERT INTO document VALUES (2, 11, 1, 'rls_regress_user2', 'my first novel') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle, dauthor = EXCLUDED.dauthor; + +-- Violates USING qual for UPDATE policy p3, interpreted here as CHECK OPTION. +-- +-- UPDATE path is taken, but UPDATE fails purely because *existing* row to be +-- updated is not a "novel"/cid 11 (row is not leaked, even though we have +-- SELECT privileges sufficient to see the row in this instance): +INSERT INTO document VALUES (33, 11, 1, 'rls_regress_user1', 'Some novel, replaces sci-fi') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle; +-- Fine (we UPDATE): +INSERT INTO document VALUES (2, 11, 1, 'rls_regress_user1', 'my first novel') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; +-- Fine (we INSERT, so "cid = 33" isn't evaluated): +INSERT INTO document VALUES (78, 11, 1, 'rls_regress_user1', 'some other novel') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *; +-- Fail (same query, but we UPDATE, so "cid = 33" is evaluated at end of +-- UPDATE): +INSERT INTO document VALUES (78, 11, 1, 'rls_regress_user1', 'some other novel') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *; +-- Fail (we UPDATE, so dauthor assignment is evaluated at end of UPDATE): +INSERT INTO document VALUES (78, 11, 1, 'rls_regress_user1', 'some other novel') + ON CONFLICT (did) UPDATE SET dtitle = EXCLUDED.dtitle, dauthor = 'rls_regress_user2'; +-- Don't fail because INSERT doesn't satisfy WITH CHECK option that originated +-- as a barrier/USING() qual from the UPDATE. Note that the UPDATE path +-- *isn't* taken, and so UPDATE-related policy does not apply: +INSERT INTO document VALUES (88, 33, 1, 'rls_regress_user1', 'technology book, can only insert') + ON CONFLICT (did) UPDATE SET dtitle = upper(EXCLUDED.dtitle) RETURNING *; + +-- -- ROLE/GROUP -- SET SESSION AUTHORIZATION rls_regress_user0; diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql index 1e15f84..7cb5f39 100644 --- a/src/test/regress/sql/rules.sql +++ b/src/test/regress/sql/rules.sql @@ -680,6 +680,9 @@ SELECT * FROM shoelace_log ORDER BY sl_name; insert into shoelace values ('sl9', 0, 'pink', 35.0, 'inch', 0.0); insert into shoelace values ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0); +-- Unsupported (even though a similar updatable view construct is) +insert into shoelace values ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0) + on conflict ignore; SELECT * FROM shoelace_obsolete ORDER BY sl_len_cm; SELECT * FROM shoelace_candelete; @@ -844,6 +847,17 @@ insert into rule_and_refint_t3 values (1, 12, 11, 'row3'); insert into rule_and_refint_t3 values (1, 12, 12, 'row4'); insert into rule_and_refint_t3 values (1, 11, 13, 'row5'); insert into rule_and_refint_t3 values (1, 13, 11, 'row6'); +-- Ordinary table +insert into rule_and_refint_t3 values (1, 13, 11, 'row6') + on conflict ignore; +-- rule not fired, so fk violation +insert into rule_and_refint_t3 values (1, 13, 11, 'row6') + on conflict (id3a, id3b, id3c) update + set id3b = excluded.id3b; +-- rule fired, so unsupported (only updatable views have limited support) +insert into shoelace values ('sl9', 0, 'pink', 35.0, 'inch', 0.0) + on conflict (id1a, id1b) update + set sl_avail = excluded.sl_avail; create rule rule_and_refint_t3_ins as on insert to rule_and_refint_t3 where (exists (select 1 from rule_and_refint_t3 diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql index 4be2e40..2be9cb7 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -374,6 +374,20 @@ from int4_tbl i4 on dummy = i4.f1; -- +-- Test case for subselect within UPDATE of INSERT...ON CONFLICT UPDATE +-- +create temp table upsert(key int4 primary key, val text); +insert into upsert values(1, 'val') on conflict (key) update set val = 'not seen'; +insert into upsert values(1, 'val') on conflict (key) update set val = 'unsupported ' || (select f1 from int4_tbl where f1 != 0 limit 1)::text; + +select * from upsert; + +with aa as (select 'int4_tbl' u from int4_tbl limit 1) +insert into upsert values (1, 'x'), (999, 'y') +on conflict (key) update set val = (select u from aa) +returning *; + +-- -- Test case for cross-type partial matching in hashed subplan (bug #7597) -- diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index 0ea2c31..323ca1a 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -208,7 +208,7 @@ drop sequence ttdummy_seq; CREATE TABLE log_table (tstamp timestamp default timeofday()::timestamp); -CREATE TABLE main_table (a int, b int); +CREATE TABLE main_table (a int unique, b int); COPY main_table (a,b) FROM stdin; 5 10 @@ -237,6 +237,12 @@ FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_ins_stmt'); CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON main_table EXECUTE PROCEDURE trigger_func('after_upd_stmt'); +-- Both insert and update statement level triggers (before and after) should +-- fire. Doesn't fire UPDATE before trigger, but only because one isn't +-- defined. +INSERT INTO main_table (a, b) VALUES (5, 10) ON CONFLICT (a) + UPDATE SET b = EXCLUDED.b; + CREATE TRIGGER after_upd_row_trig AFTER UPDATE ON main_table FOR EACH ROW EXECUTE PROCEDURE trigger_func('after_upd_row'); @@ -246,6 +252,9 @@ UPDATE main_table SET a = a + 1 WHERE b < 30; -- UPDATE that effects zero rows should still call per-statement trigger UPDATE main_table SET a = a + 2 WHERE b > 100; +-- constraint now unneeded +ALTER TABLE main_table DROP CONSTRAINT main_table_a_key; + -- COPY should fire per-row and per-statement INSERT triggers COPY main_table (a, b) FROM stdin; 30 40 @@ -1173,3 +1182,61 @@ select * from self_ref_trigger; drop table self_ref_trigger; drop function self_ref_trigger_ins_func(); drop function self_ref_trigger_del_func(); + +-- +-- Verify behavior of before and after triggers with INSERT...ON CONFLICT +-- UPDATE +-- +create table upsert (key int4 primary key, color text); + +create function upsert_before_func() + returns trigger language plpgsql as +$$ +begin + if (TG_OP = 'UPDATE') then + raise warning 'before update (old): %', old.*::text; + raise warning 'before update (new): %', new.*::text; + elsif (TG_OP = 'INSERT') then + raise warning 'before insert (new): %', new.*::text; + if new.key % 2 = 0 then + new.key := new.key + 1; + new.color := new.color || ' trig modified'; + raise warning 'before insert (new, modified): %', new.*::text; + end if; + end if; + return new; +end; +$$; +create trigger upsert_before_trig before insert or update on upsert + for each row execute procedure upsert_before_func(); + +create function upsert_after_func() + returns trigger language plpgsql as +$$ +begin + if (TG_OP = 'UPDATE') then + raise warning 'after update (old): %', new.*::text; + raise warning 'after update (new): %', new.*::text; + elsif (TG_OP = 'INSERT') then + raise warning 'after insert (new): %', new.*::text; + end if; + return null; +end; +$$; +create trigger upsert_after_trig after insert or update on upsert + for each row execute procedure upsert_after_func(); + +insert into upsert values(1, 'black') on conflict (key) update set color = 'updated ' || target.color; +insert into upsert values(2, 'red') on conflict (key) update set color = 'updated ' || target.color; +insert into upsert values(3, 'orange') on conflict (key) update set color = 'updated ' || target.color; +insert into upsert values(4, 'green') on conflict (key) update set color = 'updated ' || target.color; +insert into upsert values(5, 'purple') on conflict (key) update set color = 'updated ' || target.color; +insert into upsert values(6, 'white') on conflict (key) update set color = 'updated ' || target.color; +insert into upsert values(7, 'pink') on conflict (key) update set color = 'updated ' || target.color; +insert into upsert values(8, 'yellow') on conflict (key) update set color = 'updated ' || target.color; + +select * from upsert; + +drop table upsert; +drop function upsert_before_func(); +drop function upsert_after_func(); diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql index 60c7e29..48dd9a9 100644 --- a/src/test/regress/sql/updatable_views.sql +++ b/src/test/regress/sql/updatable_views.sql @@ -69,6 +69,8 @@ DELETE FROM rw_view14 WHERE a=3; -- should be OK -- Partially updatable view INSERT INTO rw_view15 VALUES (3, 'ROW 3'); -- should fail INSERT INTO rw_view15 (a) VALUES (3); -- should be OK +INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT IGNORE; -- succeeds +INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) IGNORE; -- fails, unsupported ALTER VIEW rw_view15 ALTER COLUMN upper SET DEFAULT 'NOT SET'; INSERT INTO rw_view15 (a) VALUES (4); -- should fail UPDATE rw_view15 SET upper='ROW 3' WHERE a=3; -- should fail diff --git a/src/test/regress/sql/update.sql b/src/test/regress/sql/update.sql index e71128c..903f3fb 100644 --- a/src/test/regress/sql/update.sql +++ b/src/test/regress/sql/update.sql @@ -74,4 +74,18 @@ UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a = 10; UPDATE update_test SET c = repeat('x', 10000) WHERE c = 'car'; SELECT a, b, char_length(c) FROM update_test; +ALTER TABLE update_test ADD constraint uuu UNIQUE(a); + +-- fail, update predicates are disallowed: +INSERT INTO update_test VALUES(31, 77) ON CONFLICT (a) UPDATE SET b = 16 +WHERE a NOT IN (SELECT a FROM update_test); +INSERT INTO update_test VALUES(31, 77) ON CONFLICT (a) UPDATE SET b = 16 +WHERE EXISTS(SELECT b FROM update_test); +INSERT INTO update_test VALUES(31, 77) ON CONFLICT (a) UPDATE SET b = 16 +WHERE a IN (SELECT a FROM update_test); +INSERT INTO update_test VALUES(31, 77) ON CONFLICT (a) UPDATE SET b = 16 +WHERE a = ALL(SELECT a FROM update_test); +INSERT INTO update_test VALUES(31, 77) ON CONFLICT (a) UPDATE SET b = 16 +WHERE a = ANY(SELECT a FROM update_test); + DROP TABLE update_test; diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql index c716369..8d49384 100644 --- a/src/test/regress/sql/with.sql +++ b/src/test/regress/sql/with.sql @@ -795,6 +795,43 @@ SELECT * FROM t LIMIT 10; SELECT * FROM y; +-- data-modifying WITH containing INSERT...ON CONFLICT UPDATE +CREATE TABLE z AS SELECT i AS k, (i || ' v')::text v FROM generate_series(1, 16, 3) i; +ALTER TABLE z ADD UNIQUE (k); + +WITH t AS ( + INSERT INTO z SELECT i, 'insert' + FROM generate_series(0, 16) i + ON CONFLICT (k) UPDATE SET v = TARGET.v || ', now update' + RETURNING * +) +SELECT * FROM t JOIN y ON t.k = y.a ORDER BY a, k; + +-- New query/snapshot demonstrates side-effects of previous query. +SELECT * FROM z ORDER BY k; + +-- +-- All these cases should fail, due to restrictions imposed upon the UPDATE +-- portion of the query. +-- +WITH aa AS (SELECT 1 a, 2 b) +INSERT INTO z VALUES(1, 'insert') +ON CONFLICT (k) UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1); +WITH aa AS (SELECT 1 a, 2 b) +INSERT INTO z VALUES(1, 'insert') +ON CONFLICT (k) UPDATE SET v = ' update' WHERE target.k = (SELECT a FROM aa); +WITH aa AS (SELECT 1 a, 2 b) +INSERT INTO z VALUES(1, 'insert') +ON CONFLICT (k) UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1); +WITH aa AS (SELECT 'a' a, 'b' b UNION ALL SELECT 'a' a, 'b' b) +INSERT INTO z VALUES(1, 'insert') +ON CONFLICT (k) UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 'a' LIMIT 1); +WITH aa AS (SELECT 1 a, 2 b) +INSERT INTO z VALUES(1, (SELECT b || ' insert' FROM aa WHERE a = 1 )) +ON CONFLICT (k) UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1); + +DROP TABLE z; + -- check that run to completion happens in proper ordering TRUNCATE TABLE y; -- 1.9.1