diff options
| author | Andres Freund | 2015-05-08 03:31:36 +0000 |
|---|---|---|
| committer | Andres Freund | 2015-05-08 03:43:10 +0000 |
| commit | 168d5805e4c08bed7b95d351bf097cff7c07dd65 (patch) | |
| tree | cd55bff71bf05324f388d3404c1b3697f3a96e7e /src/test | |
| parent | 2c8f4836db058d0715bc30a30655d646287ba509 (diff) | |
Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE.
The newly added ON CONFLICT clause allows to specify an alternative to
raising a unique or exclusion constraint violation error when inserting.
ON CONFLICT refers to constraints that can either be specified using a
inference clause (by specifying the columns of a unique constraint) or
by naming a unique or exclusion constraint. DO NOTHING avoids the
constraint violation, without touching the pre-existing row. DO UPDATE
SET ... [WHERE ...] updates the pre-existing tuple, and has access to
both the tuple proposed for insertion and the existing tuple; the
optional WHERE clause can be used to prevent an update from being
executed. The UPDATE SET and WHERE clauses have access to the tuple
proposed for insertion using the "magic" EXCLUDED alias, and to the
pre-existing tuple using the table name or its alias.
This feature is often referred to as upsert.
This is implemented using a new infrastructure called "speculative
insertion". It is an optimistic variant of regular insertion that first
does a pre-check for existing tuples and then attempts an insert. If a
violating tuple was inserted concurrently, the speculatively inserted
tuple is deleted and a new attempt is made. If the pre-check finds a
matching tuple the alternative DO NOTHING or DO UPDATE action is taken.
If the insertion succeeds without detecting a conflict, the tuple is
deemed inserted.
To handle the possible ambiguity between the excluded alias and a table
named excluded, and for convenience with long relation names, INSERT
INTO now can alias its target table.
Bumps catversion as stored rules change.
Author: Peter Geoghegan, with significant contributions from Heikki
Linnakangas and Andres Freund. Testing infrastructure by Jeff Janes.
Reviewed-By: Heikki Linnakangas, Andres Freund, Robert Haas, Simon Riggs,
Dean Rasheed, Stephen Frost and many others.
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; |