diff options
Diffstat (limited to 'src/test')
34 files changed, 2025 insertions, 9 deletions
diff --git a/src/test/isolation/expected/insert-conflict-do-nothing.out b/src/test/isolation/expected/insert-conflict-do-nothing.out new file mode 100644 index 00000000000..0a0958f0341 --- /dev/null +++ b/src/test/isolation/expected/insert-conflict-do-nothing.out @@ -0,0 +1,23 @@ +Parsed test spec with 2 sessions + +starting permutation: donothing1 donothing2 c1 select2 c2 +step donothing1: INSERT INTO ints(key, val) VALUES(1, 'donothing1') ON CONFLICT DO NOTHING; +step donothing2: INSERT INTO ints(key, val) VALUES(1, 'donothing2') ON CONFLICT DO NOTHING; <waiting ...> +step c1: COMMIT; +step donothing2: <... completed> +step select2: SELECT * FROM ints; +key val + +1 donothing1 +step c2: COMMIT; + +starting permutation: donothing1 donothing2 a1 select2 c2 +step donothing1: INSERT INTO ints(key, val) VALUES(1, 'donothing1') ON CONFLICT DO NOTHING; +step donothing2: INSERT INTO ints(key, val) VALUES(1, 'donothing2') ON CONFLICT DO NOTHING; <waiting ...> +step a1: ABORT; +step donothing2: <... completed> +step select2: SELECT * FROM ints; +key val + +1 donothing2 +step c2: COMMIT; diff --git a/src/test/isolation/expected/insert-conflict-do-update-2.out b/src/test/isolation/expected/insert-conflict-do-update-2.out new file mode 100644 index 00000000000..05fb06f8d8d --- /dev/null +++ b/src/test/isolation/expected/insert-conflict-do-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)) DO UPDATE set key = EXCLUDED.key, payload = upsert.payload || ' updated by insert1'; +step insert2: INSERT INTO upsert(key, payload) VALUES('FOOFOO', 'insert2') ON CONFLICT (lower(key)) DO UPDATE set key = EXCLUDED.key, payload = upsert.payload || ' updated by insert2'; <waiting ...> +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)) DO UPDATE set key = EXCLUDED.key, payload = upsert.payload || ' updated by insert1'; +step insert2: INSERT INTO upsert(key, payload) VALUES('FOOFOO', 'insert2') ON CONFLICT (lower(key)) DO UPDATE set key = EXCLUDED.key, payload = upsert.payload || ' updated by insert2'; <waiting ...> +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-do-update-3.out b/src/test/isolation/expected/insert-conflict-do-update-3.out new file mode 100644 index 00000000000..66004106183 --- /dev/null +++ b/src/test/isolation/expected/insert-conflict-do-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) DO UPDATE + SET color = EXCLUDED.color + WHERE colors.is_active) + SELECT * FROM colors ORDER BY key; <waiting ...> +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-do-update.out b/src/test/isolation/expected/insert-conflict-do-update.out new file mode 100644 index 00000000000..a6349187842 --- /dev/null +++ b/src/test/isolation/expected/insert-conflict-do-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) DO UPDATE set val = upsert.val || ' updated by insert1'; +step insert2: INSERT INTO upsert(key, val) VALUES(1, 'insert2') ON CONFLICT (key) DO UPDATE set val = upsert.val || ' updated by insert2'; <waiting ...> +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) DO UPDATE set val = upsert.val || ' updated by insert1'; +step insert2: INSERT INTO upsert(key, val) VALUES(1, 'insert2') ON CONFLICT (key) DO UPDATE set val = upsert.val || ' updated by insert2'; <waiting ...> +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 3e2614ecacd..c0ed637cd24 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-do-nothing +test: insert-conflict-do-update +test: insert-conflict-do-update-2 +test: insert-conflict-do-update-3 test: delete-abort-savept test: delete-abort-savept-2 test: aborted-keyrevoke diff --git a/src/test/isolation/specs/insert-conflict-do-nothing.spec b/src/test/isolation/specs/insert-conflict-do-nothing.spec new file mode 100644 index 00000000000..9b92c35cec6 --- /dev/null +++ b/src/test/isolation/specs/insert-conflict-do-nothing.spec @@ -0,0 +1,41 @@ +# INSERT...ON CONFLICT DO NOTHING test +# +# This test tries to expose problems with the interaction between concurrent +# sessions during INSERT...ON CONFLICT DO NOTHING. +# +# The convention here is that session 1 always ends up inserting, and session 2 +# always ends up doing nothing. + +setup +{ + CREATE TABLE ints (key int primary key, val text); +} + +teardown +{ + DROP TABLE ints; +} + +session "s1" +setup +{ + BEGIN ISOLATION LEVEL READ COMMITTED; +} +step "donothing1" { INSERT INTO ints(key, val) VALUES(1, 'donothing1') ON CONFLICT DO NOTHING; } +step "c1" { COMMIT; } +step "a1" { ABORT; } + +session "s2" +setup +{ + BEGIN ISOLATION LEVEL READ COMMITTED; +} +step "donothing2" { INSERT INTO ints(key, val) VALUES(1, 'donothing2') ON CONFLICT DO NOTHING; } +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 do nothing. +permutation "donothing1" "donothing2" "c1" "select2" "c2" +permutation "donothing1" "donothing2" "a1" "select2" "c2" diff --git a/src/test/isolation/specs/insert-conflict-do-update-2.spec b/src/test/isolation/specs/insert-conflict-do-update-2.spec new file mode 100644 index 00000000000..cd7e3f42feb --- /dev/null +++ b/src/test/isolation/specs/insert-conflict-do-update-2.spec @@ -0,0 +1,41 @@ +# INSERT...ON CONFLICT DO 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)) DO UPDATE set key = EXCLUDED.key, payload = upsert.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)) DO UPDATE set key = EXCLUDED.key, payload = upsert.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-do-update-3.spec b/src/test/isolation/specs/insert-conflict-do-update-3.spec new file mode 100644 index 00000000000..e282c3beca5 --- /dev/null +++ b/src/test/isolation/specs/insert-conflict-do-update-3.spec @@ -0,0 +1,69 @@ +# INSERT...ON CONFLICT DO UPDATE test +# +# Other INSERT...ON CONFLICT DO 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 DO UPDATE. The impact of the +# MVCC violation goes a little beyond updating MVCC-invisible tuples. +# +# With INSERT...ON CONFLICT DO 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) DO UPDATE + SET color = EXCLUDED.color + WHERE colors.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-do-update.spec b/src/test/isolation/specs/insert-conflict-do-update.spec new file mode 100644 index 00000000000..5d335a34449 --- /dev/null +++ b/src/test/isolation/specs/insert-conflict-do-update.spec @@ -0,0 +1,40 @@ +# INSERT...ON CONFLICT DO 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) DO UPDATE set val = upsert.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) DO UPDATE set val = upsert.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/errors.out b/src/test/regress/expected/errors.out index 5f8868da26e..210e5ff39cb 100644 --- a/src/test/regress/expected/errors.out +++ b/src/test/regress/expected/errors.out @@ -32,7 +32,9 @@ LINE 1: select nonesuch from pg_database; ^ -- empty distinct list isn't OK select distinct from pg_database; -ERROR: SELECT DISTINCT must have at least one column +ERROR: syntax error at or near "from" +LINE 1: select distinct from pg_database; + ^ -- bad attribute name on lhs of operator select * from pg_database where nonesuch = pg_database.datname; ERROR: column "nonesuch" does not exist diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out new file mode 100644 index 00000000000..3273d987932 --- /dev/null +++ b/src/test/regress/expected/insert_conflict.out @@ -0,0 +1,476 @@ +-- +-- insert...on conflict do unique index inference +-- +create table insertconflicttest(key int4, fruit text); +-- +-- Test unique index inference with operator class specifications and +-- named collations +-- +create unique index op_index_key on insertconflicttest(key, fruit text_pattern_ops); +create unique index collation_index_key on insertconflicttest(key, fruit collate "C"); +create unique index both_index_key on insertconflicttest(key, fruit collate "C" text_pattern_ops); +create unique index both_index_expr_key on insertconflicttest(key, lower(fruit) collate "C" text_pattern_ops); +-- fails +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key) do nothing; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (fruit) do nothing; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +-- succeeds +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit) do nothing; + QUERY PLAN +------------------------------------------------------------------------------- + Insert on insertconflicttest + Conflict Resolution: NOTHING + Conflict Arbiter Indexes: op_index_key, collation_index_key, both_index_key + -> Result +(4 rows) + +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (fruit, key, fruit, key) do nothing; + QUERY PLAN +------------------------------------------------------------------------------- + Insert on insertconflicttest + Conflict Resolution: NOTHING + Conflict Arbiter Indexes: op_index_key, collation_index_key, both_index_key + -> Result +(4 rows) + +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit), key, lower(fruit), key) do nothing; + QUERY PLAN +------------------------------------------------- + Insert on insertconflicttest + Conflict Resolution: NOTHING + Conflict Arbiter Indexes: both_index_expr_key + -> Result +(4 rows) + +-- Neither collation nor operator class specifications are required -- +-- supplying them merely *limits* matches to indexes with matching opclasses +-- used for relevant indexes +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit text_pattern_ops) do nothing; + QUERY PLAN +---------------------------------------------------------- + Insert on insertconflicttest + Conflict Resolution: NOTHING + Conflict Arbiter Indexes: op_index_key, both_index_key + -> Result +(4 rows) + +-- Okay, arbitrates using both index where text_pattern_ops opclass does and +-- does not appear. +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit collate "C") do nothing; + QUERY PLAN +----------------------------------------------------------------- + Insert on insertconflicttest + Conflict Resolution: NOTHING + Conflict Arbiter Indexes: collation_index_key, both_index_key + -> Result +(4 rows) + +-- Okay, but only accepts the single index where both opclass and collation are +-- specified +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (fruit collate "C" text_pattern_ops, key) do nothing; + QUERY PLAN +-------------------------------------------- + Insert on insertconflicttest + Conflict Resolution: NOTHING + Conflict Arbiter Indexes: both_index_key + -> Result +(4 rows) + +-- Okay, but only accepts the single index where both opclass and collation are +-- specified (plus expression variant) +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit) collate "C", key, key) do nothing; + QUERY PLAN +------------------------------------------------- + Insert on insertconflicttest + Conflict Resolution: NOTHING + Conflict Arbiter Indexes: both_index_expr_key + -> Result +(4 rows) + +-- Attribute appears twice, while not all attributes/expressions on attributes +-- appearing within index definition match in terms of both opclass and +-- collation. +-- +-- Works because every attribute in inference specification needs to be +-- satisfied once or more by cataloged index attribute, and as always when an +-- attribute in the cataloged definition has a non-default opclass/collation, +-- it still satisfied some inference attribute lacking any particular +-- opclass/collation specification. +-- +-- The implementation is liberal in accepting inference specifications on the +-- assumption that multiple inferred unique indexes will prevent problematic +-- cases. It rolls with unique indexes where attributes redundantly appear +-- multiple times, too (which is not tested here). +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (fruit, key, fruit text_pattern_ops, key) do nothing; + QUERY PLAN +---------------------------------------------------------- + Insert on insertconflicttest + Conflict Resolution: NOTHING + Conflict Arbiter Indexes: op_index_key, both_index_key + -> Result +(4 rows) + +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit) collate "C" text_pattern_ops, key, key) do nothing; + QUERY PLAN +------------------------------------------------- + Insert on insertconflicttest + Conflict Resolution: NOTHING + Conflict Arbiter Indexes: both_index_expr_key + -> Result +(4 rows) + +drop index op_index_key; +drop index collation_index_key; +drop index both_index_key; +drop index both_index_expr_key; +-- +-- 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) do update set fruit = excluded.fruit; + QUERY PLAN +--------------------------------------- + Insert on insertconflicttest + Conflict Resolution: UPDATE + Conflict Arbiter Indexes: key_index + -> Result +(4 rows) + +-- Should display qual actually attributable to internal sequential scan: +explain (costs off) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) do update set fruit = excluded.fruit where insertconflicttest.fruit != 'Cawesh'; + QUERY PLAN +----------------------------------------------------------------- + Insert on insertconflicttest + Conflict Resolution: UPDATE + Conflict Arbiter Indexes: key_index + Conflict Filter: (insertconflicttest.fruit <> 'Cawesh'::text) + -> Result +(5 rows) + +-- With EXCLUDED.* expression in scan node: +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key) do update set fruit = excluded.fruit where excluded.fruit != 'Elderberry'; + QUERY PLAN +----------------------------------------------------------- + Insert on insertconflicttest + Conflict Resolution: UPDATE + Conflict Arbiter Indexes: key_index + Conflict Filter: (excluded.fruit <> 'Elderberry'::text) + -> Result +(5 rows) + +-- Does the same, but JSON format shows "Conflict Arbiter Index" as JSON array: +explain (costs off, format json) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) do update set fruit = excluded.fruit where insertconflicttest.fruit != 'Lime' returning *; + QUERY PLAN +------------------------------------------------------------------------ + [ + + { + + "Plan": { + + "Node Type": "ModifyTable", + + "Operation": "Insert", + + "Relation Name": "insertconflicttest", + + "Alias": "insertconflicttest", + + "Conflict Resolution": "UPDATE", + + "Conflict Arbiter Indexes": ["key_index"], + + "Conflict Filter": "(insertconflicttest.fruit <> 'Lime'::text)",+ + "Plans": [ + + { + + "Node Type": "Result", + + "Parent Relationship": "Member" + + } + + ] + + } + + } + + ] +(1 row) + +-- Fails (no unique index inference specification, required for do update variant): +insert into insertconflicttest values (1, 'Apple') on conflict do update set fruit = excluded.fruit; +ERROR: ON CONFLICT DO UPDATE requires inference specification or constraint name +LINE 1: ...nsert into insertconflicttest values (1, 'Apple') on conflic... + ^ +HINT: For example, ON CONFLICT ON CONFLICT (<column>). +-- inference succeeds: +insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit; +insert into insertconflicttest values (2, 'Orange') on conflict (key, key, key) do update set fruit = excluded.fruit; +-- Succeed, since multi-assignment does not involve subquery: +insert into insertconflicttest +values (1, 'Apple'), (2, 'Orange') +on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key); +-- Give good diagnostic message when EXCLUDED.* spuriously referenced from +-- RETURNING: +insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit; +ERROR: invalid reference to FROM-clause entry for table "excluded" +LINE 1: ...y) do update set fruit = excluded.fruit RETURNING excluded.f... + ^ +HINT: There is an entry for table "excluded", but it cannot be referenced from this part of the query. +-- Only suggest <table>.* column when inference element misspelled: +insert into insertconflicttest values (1, 'Apple') on conflict (keyy) do update set fruit = excluded.fruit; +ERROR: column "keyy" does not exist +LINE 1: ...nsertconflicttest values (1, 'Apple') on conflict (keyy) do ... + ^ +HINT: Perhaps you meant to reference the column "insertconflicttest"."key". +-- Have useful HINT for EXCLUDED.* RTE within UPDATE: +insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruitt; +ERROR: column excluded.fruitt does not exist +LINE 1: ... 'Apple') on conflict (key) do update set fruit = excluded.f... + ^ +HINT: Perhaps you meant to reference the column "excluded"."fruit". +-- inference fails: +insert into insertconflicttest values (3, 'Kiwi') on conflict (key, fruit) do update set fruit = excluded.fruit; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +insert into insertconflicttest values (4, 'Mango') on conflict (fruit, key) do update set fruit = excluded.fruit; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +insert into insertconflicttest values (5, 'Lemon') on conflict (fruit) do update set fruit = excluded.fruit; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +insert into insertconflicttest values (6, 'Passionfruit') on conflict (lower(fruit)) do update set fruit = excluded.fruit; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +-- Check the target relation can be aliased +insert into insertconflicttest values (6, 'Passionfruits') on conflict (key) do update set fruit = excluded.fruit; +insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (key) do update set fruit = excluded.fruit; -- ok, no reference to target table +insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (key) do update set fruit = ict.fruit; -- ok, alias +insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (key) do update set fruit = insertconflicttest.fruit; -- error, references aliased away name +ERROR: invalid reference to FROM-clause entry for table "insertconflicttest" +LINE 1: ...onfruit') on conflict (key) do update set fruit = insertconf... + ^ +HINT: Perhaps you meant to reference the table alias "ict". +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) do update set fruit = excluded.fruit; +insert into insertconflicttest values (8, 'Lime') on conflict (fruit, key) do update set fruit = excluded.fruit; +-- inference fails: +insert into insertconflicttest values (9, 'Banana') on conflict (key) do update set fruit = excluded.fruit; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +insert into insertconflicttest values (10, 'Blueberry') on conflict (key, key, key) do update set fruit = excluded.fruit; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +insert into insertconflicttest values (11, 'Cherry') on conflict (key, lower(fruit)) do update set fruit = excluded.fruit; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +insert into insertconflicttest values (12, 'Date') on conflict (lower(fruit), key) do update set fruit = excluded.fruit; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +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) do update set fruit = excluded.fruit; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +insert into insertconflicttest values (14, 'Raisin') on conflict (fruit, key) do update set fruit = excluded.fruit; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +insert into insertconflicttest values (15, 'Cranberry') on conflict (key) do update set fruit = excluded.fruit; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +insert into insertconflicttest values (16, 'Melon') on conflict (key, key, key) do update set fruit = excluded.fruit; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +insert into insertconflicttest values (17, 'Mulberry') on conflict (key, lower(fruit)) do update set fruit = excluded.fruit; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +insert into insertconflicttest values (18, 'Pineapple') on conflict (lower(fruit), key) do update set fruit = excluded.fruit; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +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)) do update set fruit = excluded.fruit; +insert into insertconflicttest values (21, 'Pomegranate') on conflict (lower(fruit), lower(fruit)) do update set fruit = excluded.fruit; +-- inference fails: +insert into insertconflicttest values (22, 'Apricot') on conflict (upper(fruit)) do update set fruit = excluded.fruit; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +insert into insertconflicttest values (23, 'Blackberry') on conflict (fruit) do update set fruit = excluded.fruit; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +drop index expr_key_index; +-- +-- Expression index tests (with regular column) +-- +create unique index expr_comp_key_index on insertconflicttest(key, lower(fruit)); +create unique index tricky_expr_comp_key_index on insertconflicttest(key, lower(fruit), upper(fruit)); +-- inference succeeds: +insert into insertconflicttest values (24, 'Plum') on conflict (key, lower(fruit)) do update set fruit = excluded.fruit; +insert into insertconflicttest values (25, 'Peach') on conflict (lower(fruit), key) do update set fruit = excluded.fruit; +-- Should not infer "tricky_expr_comp_key_index" index: +explain (costs off) insert into insertconflicttest values (26, 'Fig') on conflict (lower(fruit), key, lower(fruit), key) do update set fruit = excluded.fruit; + QUERY PLAN +------------------------------------------------- + Insert on insertconflicttest + Conflict Resolution: UPDATE + Conflict Arbiter Indexes: expr_comp_key_index + -> Result +(4 rows) + +-- inference fails: +insert into insertconflicttest values (27, 'Prune') on conflict (key, upper(fruit)) do update set fruit = excluded.fruit; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +insert into insertconflicttest values (28, 'Redcurrant') on conflict (fruit, key) do update set fruit = excluded.fruit; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +insert into insertconflicttest values (29, 'Nectarine') on conflict (key) do update set fruit = excluded.fruit; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +drop index expr_comp_key_index; +drop index tricky_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) do 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) do 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) do 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' do update set fruit = excluded.fruit; +insert into insertconflicttest values (23, 'Blackberry') on conflict (key) where fruit like '%berry' and fruit = 'inconsequential' do nothing; +-- fails +insert into insertconflicttest values (23, 'Blackberry') on conflict (key) do update set fruit = excluded.fruit; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +insert into insertconflicttest values (23, 'Blackberry') on conflict (key) where fruit like '%berry' or fruit = 'consequential' do nothing; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +insert into insertconflicttest values (23, 'Blackberry') on conflict (fruit) where fruit like '%berry' do update set fruit = excluded.fruit; +ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification +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. Unique index inference +-- specification will do the right thing, though. +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: +select * from capitals; + name | population | altitude | state +------------+------------+----------+------- + Sacramento | 369400 | 30 | CA + Madison | 191300 | 845 | WI +(2 rows) + +-- Succeeds: +insert into cities values ('Las Vegas', 2.583E+5, 2174) on conflict do nothing; +insert into capitals values ('Sacramento', 4664.E+5, 30, 'CA') on conflict (name) do update set population = excluded.population; +-- Wrong "Sacramento", so do nothing: +insert into capitals values ('Sacramento', 50, 2267, 'NE') on conflict (name) do nothing; +select * from capitals; + name | population | altitude | state +------------+------------+----------+------- + Madison | 191300 | 845 | WI + Sacramento | 466400000 | 30 | CA +(2 rows) + +insert into cities values ('Las Vegas', 5.83E+5, 2001) on conflict (name) do update set population = excluded.population, altitude = excluded.altitude; +select tableoid::regclass, * from cities; + tableoid | name | population | altitude +----------+---------------+------------+---------- + cities | San Francisco | 724000 | 63 + cities | Mariposa | 1200 | 1953 + cities | Las Vegas | 583000 | 2001 + capitals | Madison | 191300 | 845 + capitals | Sacramento | 466400000 | 30 +(5 rows) + +insert into capitals values ('Las Vegas', 5.83E+5, 2222, 'NV') on conflict (name) do update set population = excluded.population; +-- Capitals will contain new capital, Las Vegas: +select * from capitals; + name | population | altitude | state +------------+------------+----------+------- + Madison | 191300 | 845 | WI + Sacramento | 466400000 | 30 | CA + Las Vegas | 583000 | 2222 | NV +(3 rows) + +-- Cities contains two instances of "Las Vegas", since unique constraints don't +-- work across inheritance: +select tableoid::regclass, * from cities; + tableoid | name | population | altitude +----------+---------------+------------+---------- + cities | San Francisco | 724000 | 63 + cities | Mariposa | 1200 | 1953 + cities | Las Vegas | 583000 | 2001 + capitals | Madison | 191300 | 845 + capitals | Sacramento | 466400000 | 30 + capitals | Las Vegas | 583000 | 2222 +(6 rows) + +-- This only affects "cities" version of "Las Vegas": +insert into cities values ('Las Vegas', 5.86E+5, 2223) on conflict (name) do update set population = excluded.population, altitude = excluded.altitude; +select tableoid::regclass, * from cities; + tableoid | name | population | altitude +----------+---------------+------------+---------- + cities | San Francisco | 724000 | 63 + cities | Mariposa | 1200 | 1953 + cities | Las Vegas | 586000 | 2223 + capitals | Madison | 191300 | 845 + capitals | Sacramento | 466400000 | 30 + capitals | Las Vegas | 583000 | 2222 +(6 rows) + +-- clean up +drop table capitals; +drop table cities; +-- Make sure a table named excluded is handled properly +create table excluded(key int primary key, data text); +insert into excluded values(1, '1'); +-- error, ambiguous +insert into excluded values(1, '2') on conflict (key) do update set data = excluded.data RETURNING *; +ERROR: table reference "excluded" is ambiguous +LINE 1: ...es(1, '2') on conflict (key) do update set data = excluded.d... + ^ +-- ok, aliased +insert into excluded AS target values(1, '2') on conflict (key) do update set data = excluded.data RETURNING *; + key | data +-----+------ + 1 | 2 +(1 row) + +-- ok, aliased +insert into excluded AS target values(1, '2') on conflict (key) do update set data = target.data RETURNING *; + key | data +-----+------ + 1 | 2 +(1 row) + +-- make sure excluded isn't a problem in returning clause +insert into excluded values(1, '2') on conflict (key) do update set data = 3 RETURNING excluded.*; + key | data +-----+------ + 1 | 3 +(1 row) + +-- clean up +drop table excluded; diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 0db1df30402..64a93309ebc 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, four int unique); 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,33 @@ 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 +-- Check that column level privs are enforced in RETURNING +-- Ok. +INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10; +-- Error. No SELECT on column three. +INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10 RETURNING atest5.three; +ERROR: permission denied for relation atest5 +-- Ok. May SELECT on column "one": +INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10 RETURNING atest5.one; + one +----- + +(1 row) + +-- Check that column level privileges are enforced for EXCLUDED +-- Ok. we may select one +INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLUDED.one; +-- Error. No select rights on three +INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLUDED.three; +ERROR: permission denied for relation atest5 +INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set one = 8; -- fails (due to UPDATE) +ERROR: permission denied for relation atest5 +INSERT INTO atest5(three) VALUES (4) ON CONFLICT (two) DO UPDATE set three = 10; -- fails (due to INSERT) +ERROR: permission denied for relation atest5 +-- Check that the the columns in the inference require select privileges +-- Error. No privs on four +INSERT INTO atest5(three) VALUES (4) ON CONFLICT (four) DO UPDATE set three = 10; +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/returning.out b/src/test/regress/expected/returning.out index 69bdacc103c..cb51bb86876 100644 --- a/src/test/regress/expected/returning.out +++ b/src/test/regress/expected/returning.out @@ -331,3 +331,27 @@ SELECT * FROM voo; 17 | zoo2 (2 rows) +-- Check aliased target relation +INSERT INTO foo AS bar DEFAULT VALUES RETURNING *; -- ok + f1 | f2 | f3 | f4 +----+----+----+---- + 4 | | 42 | 99 +(1 row) + +INSERT INTO foo AS bar DEFAULT VALUES RETURNING foo.*; -- fails, wrong name +ERROR: invalid reference to FROM-clause entry for table "foo" +LINE 1: INSERT INTO foo AS bar DEFAULT VALUES RETURNING foo.*; + ^ +HINT: Perhaps you meant to reference the table alias "bar". +INSERT INTO foo AS bar DEFAULT VALUES RETURNING bar.*; -- ok + f1 | f2 | f3 | f4 +----+----+----+---- + 5 | | 42 | 99 +(1 row) + +INSERT INTO foo AS bar DEFAULT VALUES RETURNING bar.f3; -- ok + f3 +---- + 42 +(1 row) + diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index ad936321749..82bc47895a8 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -1491,6 +1491,138 @@ SELECT * FROM b1; (21 rows) -- +-- INSERT ... ON CONFLICT DO UPDATE and Row-level security +-- +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; +-- Exists... +SELECT * FROM document WHERE did = 2; + did | cid | dlevel | dauthor | dtitle +-----+-----+--------+-------------------+----------------- + 2 | 11 | 2 | rls_regress_user1 | my second novel +(1 row) + +-- ...so violates actual WITH CHECK OPTION within UPDATE (not INSERT, since +-- alternative UPDATE path happens to be taken): +INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user2', 'my first novel') + ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, dauthor = EXCLUDED.dauthor; +ERROR: new row violates row level security policy for "document" +-- Violates USING qual for UPDATE policy p3. +-- +-- 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, 22, 1, 'rls_regress_user1', 'okay science fiction'); -- preparation for next statement +INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'Some novel, replaces sci-fi') -- takes UPDATE path + ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle; +ERROR: new row violates row level security policy (USING expression) for "document" +-- Fine (we UPDATE, since INSERT WCOs and UPDATE security barrier quals + WCOs +-- not violated): +INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel') + ON CONFLICT (did) DO 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" ("technology") isn't evaluated): +INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel') + ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *; + did | cid | dlevel | dauthor | dtitle +-----+-----+--------+-------------------+----------------------- + 78 | 11 | 1 | rls_regress_user1 | some technology novel +(1 row) + +-- Fine (same query, but we UPDATE, so "cid = 33", ("technology") is not the +-- case in respect of *existing* tuple): +INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel') + ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *; + did | cid | dlevel | dauthor | dtitle +-----+-----+--------+-------------------+----------------------- + 78 | 33 | 1 | rls_regress_user1 | some technology novel +(1 row) + +-- Same query a third time, but now fails due to existing tuple finally not +-- passing quals: +INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel') + ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *; +ERROR: new row violates row level security policy (USING expression) for "document" +-- Don't fail just 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 (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert') + ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; + did | cid | dlevel | dauthor | dtitle +-----+-----+--------+-------------------+---------------------------------- + 79 | 33 | 1 | rls_regress_user1 | technology book, can only insert +(1 row) + +-- But this time, the same statement fails, because the UPDATE path is taken, +-- and updating the row just inserted falls afoul of security barrier qual +-- (enforced as WCO) -- what we might have updated target tuple to is +-- irrelevant, in fact. +INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert') + ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; +ERROR: new row violates row level security policy (USING expression) for "document" +-- Test default USING qual enforced as WCO +SET SESSION AUTHORIZATION rls_regress_user0; +DROP POLICY p1 ON document; +DROP POLICY p2 ON document; +DROP POLICY p3 ON document; +CREATE POLICY p3_with_default ON document FOR UPDATE + USING (cid = (SELECT cid from category WHERE cname = 'novel')); +SET SESSION AUTHORIZATION rls_regress_user1; +-- Just because WCO-style enforcement of USING quals occurs with +-- existing/target tuple does not mean that the implementation can be allowed +-- to fail to also enforce this qual against the final tuple appended to +-- relation (since in the absence of an explicit WCO, this is also interpreted +-- as an UPDATE/ALL WCO in general). +-- +-- UPDATE path is taken here (fails due to existing tuple). Note that this is +-- not reported as a "USING expression", because it's an RLS UPDATE check that originated as +-- a USING qual for the purposes of RLS in general, as opposed to an explicit +-- USING qual that is ordinarily a security barrier. We leave it up to the +-- UPDATE to make this fail: +INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert') + ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; +ERROR: new row violates row level security policy for "document" +-- UPDATE path is taken here. Existing tuple passes, since it's cid +-- corresponds to "novel", but default USING qual is enforced against +-- post-UPDATE tuple too (as always when updating with a policy that lacks an +-- explicit WCO), and so this fails: +INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'my first novel') + ON CONFLICT (did) DO UPDATE SET cid = EXCLUDED.cid, dtitle = EXCLUDED.dtitle RETURNING *; +ERROR: new row violates row level security policy for "document" +SET SESSION AUTHORIZATION rls_regress_user0; +DROP POLICY p3_with_default ON document; +-- +-- Test ALL policies with ON CONFLICT DO UPDATE (much the same as existing UPDATE +-- tests) +-- +CREATE POLICY p3_with_all ON document FOR ALL + USING (cid = (SELECT cid from category WHERE cname = 'novel')) + WITH CHECK (dauthor = current_user); +SET SESSION AUTHORIZATION rls_regress_user1; +-- Fails, since ALL WCO is enforced in insert path: +INSERT INTO document VALUES (80, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user2', 'my first novel') + ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33; +ERROR: new row violates row level security policy for "document" +-- Fails, since ALL policy USING qual is enforced (existing, target tuple is in +-- violation, since it has the "manga" cid): +INSERT INTO document VALUES (4, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel') + ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle; +ERROR: new row violates row level security policy (USING expression) for "document" +-- Fails, since ALL WCO are enforced: +INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel') + ON CONFLICT (did) DO UPDATE SET dauthor = 'rls_regress_user2'; +ERROR: new row violates row level security policy for "document" +-- -- 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 f7f016be219..2df24c0de16 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 do nothing; +ERROR: INSERT with ON CONFLICT clause cannot be used with table that has 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 ------------+----------+------------+--------+----------+----------- @@ -2362,6 +2366,22 @@ 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 do nothing; +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) do 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 +insert into shoelace values ('sl9', 0, 'pink', 35.0, 'inch', 0.0) + on conflict (sl_name) do update + set sl_avail = excluded.sl_avail; +ERROR: INSERT with ON CONFLICT clause cannot be used with table that has INSERT or UPDATE rules 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) @@ -2743,3 +2763,73 @@ View definition: FROM ( VALUES (1,2)) v(q, w); drop view rule_v1; +-- +-- Check DO INSTEAD rules with ON CONFLICT +-- +CREATE TABLE hats ( + hat_name char(10) primary key, + hat_color char(10) -- hat color +); +CREATE TABLE hat_data ( + hat_name char(10) primary key, + hat_color char(10) -- hat color +); +-- okay +CREATE RULE hat_nosert AS ON INSERT TO hats + DO INSTEAD + INSERT INTO hat_data VALUES ( + NEW.hat_name, + NEW.hat_color) + ON CONFLICT (hat_name) DO NOTHING RETURNING *; +-- Works (projects row) +INSERT INTO hats VALUES ('h7', 'black') RETURNING *; + hat_name | hat_color +------------+------------ + h7 | black +(1 row) + +-- Works (does nothing) +INSERT INTO hats VALUES ('h7', 'black') RETURNING *; + hat_name | hat_color +----------+----------- +(0 rows) + +SELECT tablename, rulename, definition FROM pg_rules + WHERE tablename = 'hats'; + tablename | rulename | definition +-----------+------------+------------------------------------------------------------------------------ + hats | hat_nosert | CREATE RULE hat_nosert AS + + | | ON INSERT TO hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color)+ + | | VALUES (new.hat_name, new.hat_color) ON CONFLICT DO NOTHING + + | | RETURNING hat_data.hat_name, + + | | hat_data.hat_color; +(1 row) + +DROP RULE hat_nosert ON hats; +CREATE RULE hat_upsert AS ON INSERT TO hats + DO INSTEAD + INSERT INTO hat_data VALUES ( + NEW.hat_name, + NEW.hat_color) + ON CONFLICT (hat_name) DO UPDATE SET hat_color = 'Orange' RETURNING *; +-- Works (does upsert) +INSERT INTO hats VALUES ('h7', 'black') RETURNING *; + hat_name | hat_color +------------+------------ + h7 | Orange +(1 row) + +SELECT tablename, rulename, definition FROM pg_rules + WHERE tablename = 'hats'; + tablename | rulename | definition +-----------+------------+----------------------------------------------------------------------------------------------- + hats | hat_upsert | CREATE RULE hat_upsert AS + + | | ON INSERT TO hats DO INSTEAD INSERT INTO hat_data (hat_name, hat_color) + + | | VALUES (new.hat_name, new.hat_color) ON CONFLICT DO UPDATE SET hat_color = 'Orange'::bpchar+ + | | RETURNING hat_data.hat_name, + + | | hat_data.hat_color; +(1 row) + +DROP RULE hat_upsert ON hats; +drop table hats; +drop table hat_data; diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index b14410fd222..de64ca7ec72 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 DO UPDATE +-- +create temp table upsert(key int4 primary key, val text); +insert into upsert values(1, 'val') on conflict (key) do update set val = 'not seen'; +insert into upsert values(1, 'val') on conflict (key) do update set val = 'seen with subselect ' || (select f1 from int4_tbl where f1 != 0 limit 1)::text; +select * from upsert; + key | val +-----+---------------------------- + 1 | seen with subselect 123456 +(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) do update set val = (select u from aa) +returning *; + key | val +-----+---------- + 1 | int4_tbl + 999 | y +(2 rows) + +-- -- 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 f1a5fde107d..3b32e8fdfe3 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) + DO 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 +-- DO 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) do update set color = 'updated ' || upsert.color; +WARNING: before insert (new): (1,black) +WARNING: after insert (new): (1,black) +insert into upsert values(2, 'red') on conflict (key) do update set color = 'updated ' || upsert.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) do update set color = 'updated ' || upsert.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) do update set color = 'updated ' || upsert.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) do update set color = 'updated ' || upsert.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) do update set color = 'updated ' || upsert.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) do update set color = 'updated ' || upsert.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) do update set color = 'updated ' || upsert.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 ccabe9e3dcb..7eb92612d73 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -215,6 +215,67 @@ 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 DO NOTHING; -- succeeds +SELECT * FROM rw_view15; + a | upper +----+------------- + -2 | ROW -2 + -1 | ROW -1 + 0 | ROW 0 + 1 | ROW 1 + 2 | ROW 2 + 3 | UNSPECIFIED +(6 rows) + +INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO NOTHING; -- succeeds +SELECT * FROM rw_view15; + a | upper +----+------------- + -2 | ROW -2 + -1 | ROW -1 + 0 | ROW 0 + 1 | ROW 1 + 2 | ROW 2 + 3 | UNSPECIFIED +(6 rows) + +INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO UPDATE set a = excluded.a; -- succeeds +SELECT * FROM rw_view15; + a | upper +----+------------- + -2 | ROW -2 + -1 | ROW -1 + 0 | ROW 0 + 1 | ROW 1 + 2 | ROW 2 + 3 | UNSPECIFIED +(6 rows) + +INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO UPDATE set upper = 'blarg'; -- fails +ERROR: cannot insert into column "upper" of view "rw_view15" +DETAIL: View columns that are not columns of their base relation are not updatable. +SELECT * FROM rw_view15; + a | upper +----+------------- + -2 | ROW -2 + -1 | ROW -1 + 0 | ROW 0 + 1 | ROW 1 + 2 | ROW 2 + 3 | UNSPECIFIED +(6 rows) + +SELECT * FROM rw_view15; + a | upper +----+------------- + -2 | ROW -2 + -1 | ROW -1 + 0 | ROW 0 + 1 | ROW 1 + 2 | ROW 2 + 3 | UNSPECIFIED +(6 rows) + 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 1de2a867a85..adc1fd7c394 100644 --- a/src/test/regress/expected/update.out +++ b/src/test/regress/expected/update.out @@ -6,6 +6,10 @@ CREATE TABLE update_test ( b INT, c TEXT ); +CREATE TABLE upsert_test ( + a INT PRIMARY KEY, + b TEXT +); INSERT INTO update_test VALUES (5, 10, 'foo'); INSERT INTO update_test(b, a) VALUES (15, 10); SELECT * FROM update_test; @@ -147,4 +151,34 @@ SELECT a, b, char_length(c) FROM update_test; 42 | 12 | 10000 (4 rows) +-- Test ON CONFLICT DO UPDATE +INSERT INTO upsert_test VALUES(1, 'Boo'); +-- uncorrelated sub-select: +WITH aaa AS (SELECT 1 AS a, 'Foo' AS b) INSERT INTO upsert_test + VALUES (1, 'Bar') ON CONFLICT(a) + DO UPDATE SET (b, a) = (SELECT b, a FROM aaa) RETURNING *; + a | b +---+----- + 1 | Foo +(1 row) + +-- correlated sub-select: +INSERT INTO upsert_test VALUES (1, 'Baz') ON CONFLICT(a) + DO UPDATE SET (b, a) = (SELECT b || ', Correlated', a from upsert_test i WHERE i.a = upsert_test.a) + RETURNING *; + a | b +---+----------------- + 1 | Foo, Correlated +(1 row) + +-- correlated sub-select (EXCLUDED.* alias): +INSERT INTO upsert_test VALUES (1, 'Bat') ON CONFLICT(a) + DO UPDATE SET (b, a) = (SELECT b || ', Excluded', a from upsert_test i WHERE i.a = excluded.a) + RETURNING *; + a | b +---+--------------------------- + 1 | Foo, Correlated, Excluded +(1 row) + DROP TABLE update_test; +DROP TABLE upsert_test; diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out index a31ec341e69..2c9226c3db5 100644 --- a/src/test/regress/expected/with.out +++ b/src/test/regress/expected/with.out @@ -1806,6 +1806,88 @@ SELECT * FROM y; -400 (22 rows) +-- data-modifying WITH containing INSERT...ON CONFLICT DO 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) DO UPDATE SET v = z.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) + +-- Test EXCLUDED.* reference within CTE +WITH aa AS ( + INSERT INTO z VALUES(1, 5) ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v + WHERE z.k != EXCLUDED.k + RETURNING * +) +SELECT * FROM aa; + k | v +---+--- +(0 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) + +-- +-- Ensure subqueries within the update clause work, even if they +-- reference outside values +-- +WITH aa AS (SELECT 1 a, 2 b) +INSERT INTO z VALUES(1, 'insert') +ON CONFLICT (k) DO 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) DO UPDATE SET v = ' update' WHERE z.k = (SELECT a FROM aa); +WITH aa AS (SELECT 1 a, 2 b) +INSERT INTO z VALUES(1, 'insert') +ON CONFLICT (k) DO 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) DO 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) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1); +-- This shows an attempt to update an invisible row, which should really be +-- reported as a cardinality violation, but it doesn't seem worth fixing: +WITH simpletup AS ( + SELECT 2 k, 'Green' v), +upsert_cte AS ( + INSERT INTO z VALUES(2, 'Blue') ON CONFLICT (k) DO + UPDATE SET (k, v) = (SELECT k, v FROM simpletup WHERE simpletup.k = z.k) + RETURNING k, v) +INSERT INTO z VALUES(2, 'Red') ON CONFLICT (k) DO +UPDATE SET (k, v) = (SELECT k, v FROM upsert_cte WHERE upsert_cte.k = z.k) +RETURNING k, v; +ERROR: attempted to update invisible tuple +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 c16f65088a9..7647544f9b9 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) DO UPDATE SET t = 'five-upsert-update'; +INSERT INTO UNIQUE_TBL VALUES (6, 'six-upsert-insert') ON CONFLICT (i) DO UPDATE SET t = 'six-upsert-update'; +-- should fail +INSERT INTO UNIQUE_TBL VALUES (1, 'a'), (2, 'b'), (2, 'b') ON CONFLICT (i) DO UPDATE SET t = 'fails'; + SELECT '' AS five, * FROM UNIQUE_TBL; DROP TABLE UNIQUE_TBL; @@ -438,6 +443,12 @@ INSERT INTO circles VALUES('<(0,0), 5>', '<(0,0), 4>'); INSERT INTO circles VALUES('<(10,10), 10>', '<(0,0), 5>'); -- fail, overlaps INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>'); +-- succeed, because violation is ignored +INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>') + ON CONFLICT ON CONSTRAINT circles_c1_c2_excl DO NOTHING; +-- fail, because DO UPDATE variant requires unique index +INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>') + ON CONFLICT ON CONSTRAINT circles_c1_c2_excl DO UPDATE SET c2 = EXCLUDED.c2; -- succeed because c1 doesn't overlap INSERT INTO circles VALUES('<(20,20), 1>', '<(0,0), 5>'); -- succeed because c2 doesn't overlap @@ -462,6 +473,7 @@ CREATE TABLE deferred_excl ( INSERT INTO deferred_excl VALUES(1); INSERT INTO deferred_excl VALUES(2); INSERT INTO deferred_excl VALUES(1); -- fail +INSERT INTO deferred_excl VALUES(1) ON CONFLICT ON CONSTRAINT deferred_excl_con DO NOTHING; -- fail BEGIN; INSERT INTO deferred_excl VALUES(2); -- no fail here COMMIT; -- should fail here diff --git a/src/test/regress/output/constraints.source b/src/test/regress/output/constraints.source index d3ec2333139..bbe4ed19760 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) DO UPDATE SET t = 'five-upsert-update'; +INSERT INTO UNIQUE_TBL VALUES (6, 'six-upsert-insert') ON CONFLICT (i) DO UPDATE SET t = 'six-upsert-update'; +-- should fail +INSERT INTO UNIQUE_TBL VALUES (1, 'a'), (2, 'b'), (2, 'b') ON CONFLICT (i) DO UPDATE SET t = 'fails'; +ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time +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, @@ -605,6 +612,13 @@ INSERT INTO circles VALUES('<(10,10), 10>', '<(0,0), 5>'); INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>'); ERROR: conflicting key value violates exclusion constraint "circles_c1_c2_excl" DETAIL: Key (c1, (c2::circle))=(<(20,20),10>, <(0,0),4>) conflicts with existing key (c1, (c2::circle))=(<(10,10),10>, <(0,0),5>). +-- succeed, because violation is ignored +INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>') + ON CONFLICT ON CONSTRAINT circles_c1_c2_excl DO NOTHING; +-- fail, because DO UPDATE variant requires unique index +INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 4>') + ON CONFLICT ON CONSTRAINT circles_c1_c2_excl DO UPDATE SET c2 = EXCLUDED.c2; +ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints -- succeed because c1 doesn't overlap INSERT INTO circles VALUES('<(20,20), 1>', '<(0,0), 5>'); -- succeed because c2 doesn't overlap @@ -627,6 +641,8 @@ INSERT INTO deferred_excl VALUES(2); INSERT INTO deferred_excl VALUES(1); -- fail ERROR: conflicting key value violates exclusion constraint "deferred_excl_con" DETAIL: Key (f1)=(1) conflicts with existing key (f1)=(1). +INSERT INTO deferred_excl VALUES(1) ON CONFLICT ON CONSTRAINT deferred_excl_con DO NOTHING; -- fail +ERROR: ON CONFLICT does not support deferred unique constraints/exclusion constraints as arbiters BEGIN; INSERT INTO deferred_excl VALUES(2); -- no fail here COMMIT; -- should fail here diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 6d3b865351d..b0ebb6b3f4c 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 8326894ed9d..8409c0f3ef2 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 00000000000..ba2b66bdb67 --- /dev/null +++ b/src/test/regress/sql/insert_conflict.sql @@ -0,0 +1,284 @@ +-- +-- insert...on conflict do unique index inference +-- +create table insertconflicttest(key int4, fruit text); + +-- +-- Test unique index inference with operator class specifications and +-- named collations +-- +create unique index op_index_key on insertconflicttest(key, fruit text_pattern_ops); +create unique index collation_index_key on insertconflicttest(key, fruit collate "C"); +create unique index both_index_key on insertconflicttest(key, fruit collate "C" text_pattern_ops); +create unique index both_index_expr_key on insertconflicttest(key, lower(fruit) collate "C" text_pattern_ops); + +-- fails +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key) do nothing; +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (fruit) do nothing; + +-- succeeds +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit) do nothing; +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (fruit, key, fruit, key) do nothing; +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit), key, lower(fruit), key) do nothing; +-- Neither collation nor operator class specifications are required -- +-- supplying them merely *limits* matches to indexes with matching opclasses +-- used for relevant indexes +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit text_pattern_ops) do nothing; +-- Okay, arbitrates using both index where text_pattern_ops opclass does and +-- does not appear. +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit collate "C") do nothing; +-- Okay, but only accepts the single index where both opclass and collation are +-- specified +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (fruit collate "C" text_pattern_ops, key) do nothing; +-- Okay, but only accepts the single index where both opclass and collation are +-- specified (plus expression variant) +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit) collate "C", key, key) do nothing; +-- Attribute appears twice, while not all attributes/expressions on attributes +-- appearing within index definition match in terms of both opclass and +-- collation. +-- +-- Works because every attribute in inference specification needs to be +-- satisfied once or more by cataloged index attribute, and as always when an +-- attribute in the cataloged definition has a non-default opclass/collation, +-- it still satisfied some inference attribute lacking any particular +-- opclass/collation specification. +-- +-- The implementation is liberal in accepting inference specifications on the +-- assumption that multiple inferred unique indexes will prevent problematic +-- cases. It rolls with unique indexes where attributes redundantly appear +-- multiple times, too (which is not tested here). +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (fruit, key, fruit text_pattern_ops, key) do nothing; +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (lower(fruit) collate "C" text_pattern_ops, key, key) do nothing; + +drop index op_index_key; +drop index collation_index_key; +drop index both_index_key; +drop index both_index_expr_key; + +-- +-- 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) do 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) do update set fruit = excluded.fruit where insertconflicttest.fruit != 'Cawesh'; +-- With EXCLUDED.* expression in scan node: +explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key) do update set fruit = excluded.fruit where excluded.fruit != 'Elderberry'; +-- Does the same, but JSON format shows "Conflict Arbiter Index" as JSON array: +explain (costs off, format json) insert into insertconflicttest values (0, 'Bilberry') on conflict (key) do update set fruit = excluded.fruit where insertconflicttest.fruit != 'Lime' returning *; + +-- Fails (no unique index inference specification, required for do update variant): +insert into insertconflicttest values (1, 'Apple') on conflict do update set fruit = excluded.fruit; + +-- inference succeeds: +insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit; +insert into insertconflicttest values (2, 'Orange') on conflict (key, key, key) do update set fruit = excluded.fruit; + +-- Succeed, since multi-assignment does not involve subquery: +insert into insertconflicttest +values (1, 'Apple'), (2, 'Orange') +on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key); + +-- Give good diagnostic message when EXCLUDED.* spuriously referenced from +-- RETURNING: +insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit; + +-- Only suggest <table>.* column when inference element misspelled: +insert into insertconflicttest values (1, 'Apple') on conflict (keyy) do update set fruit = excluded.fruit; + +-- Have useful HINT for EXCLUDED.* RTE within UPDATE: +insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruitt; + +-- inference fails: +insert into insertconflicttest values (3, 'Kiwi') on conflict (key, fruit) do update set fruit = excluded.fruit; +insert into insertconflicttest values (4, 'Mango') on conflict (fruit, key) do update set fruit = excluded.fruit; +insert into insertconflicttest values (5, 'Lemon') on conflict (fruit) do update set fruit = excluded.fruit; +insert into insertconflicttest values (6, 'Passionfruit') on conflict (lower(fruit)) do update set fruit = excluded.fruit; + +-- Check the target relation can be aliased +insert into insertconflicttest values (6, 'Passionfruits') on conflict (key) do update set fruit = excluded.fruit; +insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (key) do update set fruit = excluded.fruit; -- ok, no reference to target table +insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (key) do update set fruit = ict.fruit; -- ok, alias +insert into insertconflicttest AS ict values (6, 'Passionfruit') on conflict (key) do update set fruit = insertconflicttest.fruit; -- error, references aliased away name + +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) do update set fruit = excluded.fruit; +insert into insertconflicttest values (8, 'Lime') on conflict (fruit, key) do update set fruit = excluded.fruit; + +-- inference fails: +insert into insertconflicttest values (9, 'Banana') on conflict (key) do update set fruit = excluded.fruit; +insert into insertconflicttest values (10, 'Blueberry') on conflict (key, key, key) do update set fruit = excluded.fruit; +insert into insertconflicttest values (11, 'Cherry') on conflict (key, lower(fruit)) do update set fruit = excluded.fruit; +insert into insertconflicttest values (12, 'Date') on conflict (lower(fruit), key) do 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) do update set fruit = excluded.fruit; +insert into insertconflicttest values (14, 'Raisin') on conflict (fruit, key) do update set fruit = excluded.fruit; +insert into insertconflicttest values (15, 'Cranberry') on conflict (key) do update set fruit = excluded.fruit; +insert into insertconflicttest values (16, 'Melon') on conflict (key, key, key) do update set fruit = excluded.fruit; +insert into insertconflicttest values (17, 'Mulberry') on conflict (key, lower(fruit)) do update set fruit = excluded.fruit; +insert into insertconflicttest values (18, 'Pineapple') on conflict (lower(fruit), key) do 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)) do update set fruit = excluded.fruit; +insert into insertconflicttest values (21, 'Pomegranate') on conflict (lower(fruit), lower(fruit)) do update set fruit = excluded.fruit; + +-- inference fails: +insert into insertconflicttest values (22, 'Apricot') on conflict (upper(fruit)) do update set fruit = excluded.fruit; +insert into insertconflicttest values (23, 'Blackberry') on conflict (fruit) do 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)); +create unique index tricky_expr_comp_key_index on insertconflicttest(key, lower(fruit), upper(fruit)); + +-- inference succeeds: +insert into insertconflicttest values (24, 'Plum') on conflict (key, lower(fruit)) do update set fruit = excluded.fruit; +insert into insertconflicttest values (25, 'Peach') on conflict (lower(fruit), key) do update set fruit = excluded.fruit; +-- Should not infer "tricky_expr_comp_key_index" index: +explain (costs off) insert into insertconflicttest values (26, 'Fig') on conflict (lower(fruit), key, lower(fruit), key) do update set fruit = excluded.fruit; + +-- inference fails: +insert into insertconflicttest values (27, 'Prune') on conflict (key, upper(fruit)) do update set fruit = excluded.fruit; +insert into insertconflicttest values (28, 'Redcurrant') on conflict (fruit, key) do update set fruit = excluded.fruit; +insert into insertconflicttest values (29, 'Nectarine') on conflict (key) do update set fruit = excluded.fruit; + +drop index expr_comp_key_index; +drop index tricky_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) do 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) do 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) do 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' do update set fruit = excluded.fruit; +insert into insertconflicttest values (23, 'Blackberry') on conflict (key) where fruit like '%berry' and fruit = 'inconsequential' do nothing; + +-- fails +insert into insertconflicttest values (23, 'Blackberry') on conflict (key) do update set fruit = excluded.fruit; +insert into insertconflicttest values (23, 'Blackberry') on conflict (key) where fruit like '%berry' or fruit = 'consequential' do nothing; +insert into insertconflicttest values (23, 'Blackberry') on conflict (fruit) where fruit like '%berry' do update set fruit = excluded.fruit; + +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. Unique index inference +-- specification will do the right thing, though. +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: +select * from capitals; + +-- Succeeds: +insert into cities values ('Las Vegas', 2.583E+5, 2174) on conflict do nothing; +insert into capitals values ('Sacramento', 4664.E+5, 30, 'CA') on conflict (name) do update set population = excluded.population; +-- Wrong "Sacramento", so do nothing: +insert into capitals values ('Sacramento', 50, 2267, 'NE') on conflict (name) do nothing; +select * from capitals; +insert into cities values ('Las Vegas', 5.83E+5, 2001) on conflict (name) do update set population = excluded.population, altitude = excluded.altitude; +select tableoid::regclass, * from cities; +insert into capitals values ('Las Vegas', 5.83E+5, 2222, 'NV') on conflict (name) do update set population = excluded.population; +-- Capitals will contain new capital, Las Vegas: +select * from capitals; +-- Cities contains two instances of "Las Vegas", since unique constraints don't +-- work across inheritance: +select tableoid::regclass, * from cities; +-- This only affects "cities" version of "Las Vegas": +insert into cities values ('Las Vegas', 5.86E+5, 2223) on conflict (name) do update set population = excluded.population, altitude = excluded.altitude; +select tableoid::regclass, * from cities; + +-- clean up +drop table capitals; +drop table cities; + + +-- Make sure a table named excluded is handled properly +create table excluded(key int primary key, data text); +insert into excluded values(1, '1'); +-- error, ambiguous +insert into excluded values(1, '2') on conflict (key) do update set data = excluded.data RETURNING *; +-- ok, aliased +insert into excluded AS target values(1, '2') on conflict (key) do update set data = excluded.data RETURNING *; +-- ok, aliased +insert into excluded AS target values(1, '2') on conflict (key) do update set data = target.data RETURNING *; +-- make sure excluded isn't a problem in returning clause +insert into excluded values(1, '2') on conflict (key) do update set data = 3 RETURNING excluded.*; + +-- clean up +drop table excluded; diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql index f97a75a5fdc..22b54a28c46 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, four int unique); 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,23 @@ 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 +-- Check that column level privs are enforced in RETURNING +-- Ok. +INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10; +-- Error. No SELECT on column three. +INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10 RETURNING atest5.three; +-- Ok. May SELECT on column "one": +INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10 RETURNING atest5.one; +-- Check that column level privileges are enforced for EXCLUDED +-- Ok. we may select one +INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLUDED.one; +-- Error. No select rights on three +INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLUDED.three; +INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set one = 8; -- fails (due to UPDATE) +INSERT INTO atest5(three) VALUES (4) ON CONFLICT (two) DO UPDATE set three = 10; -- fails (due to INSERT) +-- Check that the the columns in the inference require select privileges +-- Error. No privs on four +INSERT INTO atest5(three) VALUES (4) ON CONFLICT (four) DO UPDATE set three = 10; SET SESSION AUTHORIZATION regressuser1; REVOKE ALL (one) ON atest5 FROM regressuser4; diff --git a/src/test/regress/sql/returning.sql b/src/test/regress/sql/returning.sql index 0ed9a489510..a460f82fb7c 100644 --- a/src/test/regress/sql/returning.sql +++ b/src/test/regress/sql/returning.sql @@ -154,3 +154,9 @@ UPDATE joinview SET f1 = f1 + 1 WHERE f3 = 57 RETURNING *, other + 1; SELECT * FROM joinview; SELECT * FROM foo; SELECT * FROM voo; + +-- Check aliased target relation +INSERT INTO foo AS bar DEFAULT VALUES RETURNING *; -- ok +INSERT INTO foo AS bar DEFAULT VALUES RETURNING foo.*; -- fails, wrong name +INSERT INTO foo AS bar DEFAULT VALUES RETURNING bar.*; -- ok +INSERT INTO foo AS bar DEFAULT VALUES RETURNING bar.f3; -- ok diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql index 7d12dd00a2f..e8c5932b201 100644 --- a/src/test/regress/sql/rowsecurity.sql +++ b/src/test/regress/sql/rowsecurity.sql @@ -511,6 +511,118 @@ DELETE FROM bv1 WHERE a = 6 AND f_leak(b); SET SESSION AUTHORIZATION rls_regress_user0; SELECT * FROM b1; +-- +-- INSERT ... ON CONFLICT DO UPDATE and Row-level security +-- + +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; + +-- Exists... +SELECT * FROM document WHERE did = 2; + +-- ...so violates actual WITH CHECK OPTION within UPDATE (not INSERT, since +-- alternative UPDATE path happens to be taken): +INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user2', 'my first novel') + ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, dauthor = EXCLUDED.dauthor; + +-- Violates USING qual for UPDATE policy p3. +-- +-- 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, 22, 1, 'rls_regress_user1', 'okay science fiction'); -- preparation for next statement +INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'Some novel, replaces sci-fi') -- takes UPDATE path + ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle; +-- Fine (we UPDATE, since INSERT WCOs and UPDATE security barrier quals + WCOs +-- not violated): +INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel') + ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; +-- Fine (we INSERT, so "cid = 33" ("technology") isn't evaluated): +INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel') + ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *; +-- Fine (same query, but we UPDATE, so "cid = 33", ("technology") is not the +-- case in respect of *existing* tuple): +INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel') + ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *; +-- Same query a third time, but now fails due to existing tuple finally not +-- passing quals: +INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel') + ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *; +-- Don't fail just 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 (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert') + ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; +-- But this time, the same statement fails, because the UPDATE path is taken, +-- and updating the row just inserted falls afoul of security barrier qual +-- (enforced as WCO) -- what we might have updated target tuple to is +-- irrelevant, in fact. +INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert') + ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; + +-- Test default USING qual enforced as WCO +SET SESSION AUTHORIZATION rls_regress_user0; +DROP POLICY p1 ON document; +DROP POLICY p2 ON document; +DROP POLICY p3 ON document; + +CREATE POLICY p3_with_default ON document FOR UPDATE + USING (cid = (SELECT cid from category WHERE cname = 'novel')); + +SET SESSION AUTHORIZATION rls_regress_user1; +-- Just because WCO-style enforcement of USING quals occurs with +-- existing/target tuple does not mean that the implementation can be allowed +-- to fail to also enforce this qual against the final tuple appended to +-- relation (since in the absence of an explicit WCO, this is also interpreted +-- as an UPDATE/ALL WCO in general). +-- +-- UPDATE path is taken here (fails due to existing tuple). Note that this is +-- not reported as a "USING expression", because it's an RLS UPDATE check that originated as +-- a USING qual for the purposes of RLS in general, as opposed to an explicit +-- USING qual that is ordinarily a security barrier. We leave it up to the +-- UPDATE to make this fail: +INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert') + ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; + +-- UPDATE path is taken here. Existing tuple passes, since it's cid +-- corresponds to "novel", but default USING qual is enforced against +-- post-UPDATE tuple too (as always when updating with a policy that lacks an +-- explicit WCO), and so this fails: +INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'my first novel') + ON CONFLICT (did) DO UPDATE SET cid = EXCLUDED.cid, dtitle = EXCLUDED.dtitle RETURNING *; + +SET SESSION AUTHORIZATION rls_regress_user0; +DROP POLICY p3_with_default ON document; + +-- +-- Test ALL policies with ON CONFLICT DO UPDATE (much the same as existing UPDATE +-- tests) +-- +CREATE POLICY p3_with_all ON document FOR ALL + USING (cid = (SELECT cid from category WHERE cname = 'novel')) + WITH CHECK (dauthor = current_user); + +SET SESSION AUTHORIZATION rls_regress_user1; + +-- Fails, since ALL WCO is enforced in insert path: +INSERT INTO document VALUES (80, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user2', 'my first novel') + ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33; +-- Fails, since ALL policy USING qual is enforced (existing, target tuple is in +-- violation, since it has the "manga" cid): +INSERT INTO document VALUES (4, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel') + ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle; +-- Fails, since ALL WCO are enforced: +INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel') + ON CONFLICT (did) DO UPDATE SET dauthor = 'rls_regress_user2'; -- -- ROLE/GROUP diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql index c385e414578..6f1a1b84e79 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 do nothing; 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 do nothing; +-- rule not fired, so fk violation +insert into rule_and_refint_t3 values (1, 13, 11, 'row6') + on conflict (id3a, id3b, id3c) do update + set id3b = excluded.id3b; +-- rule fired, so unsupported +insert into shoelace values ('sl9', 0, 'pink', 35.0, 'inch', 0.0) + on conflict (sl_name) do 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 @@ -1025,3 +1039,48 @@ drop view rule_v1; create view rule_v1(x) as select * from (values(1,2)) v(q,w); \d+ rule_v1 drop view rule_v1; + +-- +-- Check DO INSTEAD rules with ON CONFLICT +-- +CREATE TABLE hats ( + hat_name char(10) primary key, + hat_color char(10) -- hat color +); + +CREATE TABLE hat_data ( + hat_name char(10) primary key, + hat_color char(10) -- hat color +); + +-- okay +CREATE RULE hat_nosert AS ON INSERT TO hats + DO INSTEAD + INSERT INTO hat_data VALUES ( + NEW.hat_name, + NEW.hat_color) + ON CONFLICT (hat_name) DO NOTHING RETURNING *; + +-- Works (projects row) +INSERT INTO hats VALUES ('h7', 'black') RETURNING *; +-- Works (does nothing) +INSERT INTO hats VALUES ('h7', 'black') RETURNING *; +SELECT tablename, rulename, definition FROM pg_rules + WHERE tablename = 'hats'; +DROP RULE hat_nosert ON hats; + +CREATE RULE hat_upsert AS ON INSERT TO hats + DO INSTEAD + INSERT INTO hat_data VALUES ( + NEW.hat_name, + NEW.hat_color) + ON CONFLICT (hat_name) DO UPDATE SET hat_color = 'Orange' RETURNING *; + +-- Works (does upsert) +INSERT INTO hats VALUES ('h7', 'black') RETURNING *; +SELECT tablename, rulename, definition FROM pg_rules + WHERE tablename = 'hats'; +DROP RULE hat_upsert ON hats; + +drop table hats; +drop table hat_data; diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql index 4be2e40a000..29912230891 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 DO UPDATE +-- +create temp table upsert(key int4 primary key, val text); +insert into upsert values(1, 'val') on conflict (key) do update set val = 'not seen'; +insert into upsert values(1, 'val') on conflict (key) do update set val = 'seen with subselect ' || (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) do 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 0ea2c314dee..9f66702ceef 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) + DO 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 +-- DO 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) do update set color = 'updated ' || upsert.color; +insert into upsert values(2, 'red') on conflict (key) do update set color = 'updated ' || upsert.color; +insert into upsert values(3, 'orange') on conflict (key) do update set color = 'updated ' || upsert.color; +insert into upsert values(4, 'green') on conflict (key) do update set color = 'updated ' || upsert.color; +insert into upsert values(5, 'purple') on conflict (key) do update set color = 'updated ' || upsert.color; +insert into upsert values(6, 'white') on conflict (key) do update set color = 'updated ' || upsert.color; +insert into upsert values(7, 'pink') on conflict (key) do update set color = 'updated ' || upsert.color; +insert into upsert values(8, 'yellow') on conflict (key) do update set color = 'updated ' || upsert.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 697363665c1..8fe96f5c51c 100644 --- a/src/test/regress/sql/updatable_views.sql +++ b/src/test/regress/sql/updatable_views.sql @@ -69,6 +69,15 @@ 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 DO NOTHING; -- succeeds +SELECT * FROM rw_view15; +INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO NOTHING; -- succeeds +SELECT * FROM rw_view15; +INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO UPDATE set a = excluded.a; -- succeeds +SELECT * FROM rw_view15; +INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO UPDATE set upper = 'blarg'; -- fails +SELECT * FROM rw_view15; +SELECT * FROM rw_view15; 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 e71128c04dd..5637c68acf7 100644 --- a/src/test/regress/sql/update.sql +++ b/src/test/regress/sql/update.sql @@ -8,6 +8,11 @@ CREATE TABLE update_test ( c TEXT ); +CREATE TABLE upsert_test ( + a INT PRIMARY KEY, + b TEXT +); + INSERT INTO update_test VALUES (5, 10, 'foo'); INSERT INTO update_test(b, a) VALUES (15, 10); @@ -74,4 +79,20 @@ 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; +-- Test ON CONFLICT DO UPDATE +INSERT INTO upsert_test VALUES(1, 'Boo'); +-- uncorrelated sub-select: +WITH aaa AS (SELECT 1 AS a, 'Foo' AS b) INSERT INTO upsert_test + VALUES (1, 'Bar') ON CONFLICT(a) + DO UPDATE SET (b, a) = (SELECT b, a FROM aaa) RETURNING *; +-- correlated sub-select: +INSERT INTO upsert_test VALUES (1, 'Baz') ON CONFLICT(a) + DO UPDATE SET (b, a) = (SELECT b || ', Correlated', a from upsert_test i WHERE i.a = upsert_test.a) + RETURNING *; +-- correlated sub-select (EXCLUDED.* alias): +INSERT INTO upsert_test VALUES (1, 'Bat') ON CONFLICT(a) + DO UPDATE SET (b, a) = (SELECT b || ', Excluded', a from upsert_test i WHERE i.a = excluded.a) + RETURNING *; + DROP TABLE update_test; +DROP TABLE upsert_test; diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql index 1687c119830..3fd55f96b3f 100644 --- a/src/test/regress/sql/with.sql +++ b/src/test/regress/sql/with.sql @@ -795,6 +795,63 @@ SELECT * FROM t LIMIT 10; SELECT * FROM y; +-- data-modifying WITH containing INSERT...ON CONFLICT DO 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) DO UPDATE SET v = z.v || ', now update' + RETURNING * +) +SELECT * FROM t JOIN y ON t.k = y.a ORDER BY a, k; + +-- Test EXCLUDED.* reference within CTE +WITH aa AS ( + INSERT INTO z VALUES(1, 5) ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v + WHERE z.k != EXCLUDED.k + RETURNING * +) +SELECT * FROM aa; + +-- New query/snapshot demonstrates side-effects of previous query. +SELECT * FROM z ORDER BY k; + +-- +-- Ensure subqueries within the update clause work, even if they +-- reference outside values +-- +WITH aa AS (SELECT 1 a, 2 b) +INSERT INTO z VALUES(1, 'insert') +ON CONFLICT (k) DO 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) DO UPDATE SET v = ' update' WHERE z.k = (SELECT a FROM aa); +WITH aa AS (SELECT 1 a, 2 b) +INSERT INTO z VALUES(1, 'insert') +ON CONFLICT (k) DO 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) DO 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) DO UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1); + +-- This shows an attempt to update an invisible row, which should really be +-- reported as a cardinality violation, but it doesn't seem worth fixing: +WITH simpletup AS ( + SELECT 2 k, 'Green' v), +upsert_cte AS ( + INSERT INTO z VALUES(2, 'Blue') ON CONFLICT (k) DO + UPDATE SET (k, v) = (SELECT k, v FROM simpletup WHERE simpletup.k = z.k) + RETURNING k, v) +INSERT INTO z VALUES(2, 'Red') ON CONFLICT (k) DO +UPDATE SET (k, v) = (SELECT k, v FROM upsert_cte WHERE upsert_cte.k = z.k) +RETURNING k, v; + +DROP TABLE z; + -- check that run to completion happens in proper ordering TRUNCATE TABLE y; |