From 881564456e3c0b868324f61568f835f330cb2ad3 Mon Sep 17 00:00:00 2001 From: Peter Geoghegan Date: Wed, 27 Aug 2014 15:11:15 -0700 Subject: [PATCH 3/4] Tests for INSERT ... ON CONFLICT {UPDATE | IGNORE} Add dedicated isolation tests for both UPDATE and IGNORE variants, illustrating the "MVCC violation" that allows a READ COMMITTED transaction's UPDATE to succeed in updating a tuple with no version visible to its command's MVCC snapshot. Regression tests are for the most part intended to exercise interactions with inter-related features. Add a few general purpose smoke tests too. --- .../isolation/expected/insert-conflict-ignore.out | 23 ++++++ .../isolation/expected/insert-conflict-update.out | 23 ++++++ src/test/isolation/isolation_schedule | 2 + .../isolation/specs/insert-conflict-ignore.spec | 41 ++++++++++ .../isolation/specs/insert-conflict-update.spec | 40 ++++++++++ src/test/regress/expected/inherit.out | 3 + src/test/regress/expected/privileges.out | 5 ++ src/test/regress/expected/rules.out | 22 +++++- src/test/regress/expected/subselect.out | 18 +++++ src/test/regress/expected/triggers.out | 90 ++++++++++++++++++++++ src/test/regress/expected/updatable_views.out | 2 + src/test/regress/expected/update.out | 5 ++ src/test/regress/expected/with.out | 64 +++++++++++++++ src/test/regress/input/constraints.source | 5 ++ src/test/regress/output/constraints.source | 15 +++- src/test/regress/sql/inherit.sql | 3 + src/test/regress/sql/privileges.sql | 3 + src/test/regress/sql/rules.sql | 9 +++ src/test/regress/sql/subselect.sql | 14 ++++ src/test/regress/sql/triggers.sql | 58 ++++++++++++++ src/test/regress/sql/updatable_views.sql | 1 + src/test/regress/sql/update.sql | 8 ++ src/test/regress/sql/with.sql | 37 +++++++++ 23 files changed, 485 insertions(+), 6 deletions(-) create mode 100644 src/test/isolation/expected/insert-conflict-ignore.out create mode 100644 src/test/isolation/expected/insert-conflict-update.out create mode 100644 src/test/isolation/specs/insert-conflict-ignore.spec create mode 100644 src/test/isolation/specs/insert-conflict-update.spec diff --git a/src/test/isolation/expected/insert-conflict-ignore.out b/src/test/isolation/expected/insert-conflict-ignore.out new file mode 100644 index 0000000..e6cc2a1 --- /dev/null +++ b/src/test/isolation/expected/insert-conflict-ignore.out @@ -0,0 +1,23 @@ +Parsed test spec with 2 sessions + +starting permutation: ignore1 ignore2 c1 select2 c2 +step ignore1: INSERT INTO ints(key, val) VALUES(1, 'ignore1') ON CONFLICT IGNORE; +step ignore2: INSERT INTO ints(key, val) VALUES(1, 'ignore2') ON CONFLICT IGNORE; +step c1: COMMIT; +step ignore2: <... completed> +step select2: SELECT * FROM ints; +key val + +1 ignore1 +step c2: COMMIT; + +starting permutation: ignore1 ignore2 a1 select2 c2 +step ignore1: INSERT INTO ints(key, val) VALUES(1, 'ignore1') ON CONFLICT IGNORE; +step ignore2: INSERT INTO ints(key, val) VALUES(1, 'ignore2') ON CONFLICT IGNORE; +step a1: ABORT; +step ignore2: <... completed> +step select2: SELECT * FROM ints; +key val + +1 ignore2 +step c2: COMMIT; diff --git a/src/test/isolation/expected/insert-conflict-update.out b/src/test/isolation/expected/insert-conflict-update.out new file mode 100644 index 0000000..8b2ad84 --- /dev/null +++ b/src/test/isolation/expected/insert-conflict-update.out @@ -0,0 +1,23 @@ +Parsed test spec with 2 sessions + +starting permutation: insert1 insert2 c1 select2 c2 +step insert1: INSERT INTO upsert(key, val) VALUES(1, 'insert1') ON CONFLICT UPDATE set val = val || ' updated by insert1'; +step insert2: INSERT INTO upsert(key, val) VALUES(1, 'insert2') ON CONFLICT UPDATE set val = val || ' updated by insert2'; +step c1: COMMIT; +step insert2: <... completed> +step select2: SELECT * FROM upsert; +key val + +1 insert1 updated by insert2 +step c2: COMMIT; + +starting permutation: insert1 insert2 a1 select2 c2 +step insert1: INSERT INTO upsert(key, val) VALUES(1, 'insert1') ON CONFLICT UPDATE set val = val || ' updated by insert1'; +step insert2: INSERT INTO upsert(key, val) VALUES(1, 'insert2') ON CONFLICT UPDATE set val = val || ' updated by insert2'; +step a1: ABORT; +step insert2: <... completed> +step select2: SELECT * FROM upsert; +key val + +1 insert2 +step c2: COMMIT; diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule index 10c89ff..fa92329 100644 --- a/src/test/isolation/isolation_schedule +++ b/src/test/isolation/isolation_schedule @@ -16,6 +16,8 @@ test: fk-deadlock2 test: eval-plan-qual test: lock-update-delete test: lock-update-traversal +test: insert-conflict-ignore +test: insert-conflict-update test: delete-abort-savept test: delete-abort-savept-2 test: aborted-keyrevoke diff --git a/src/test/isolation/specs/insert-conflict-ignore.spec b/src/test/isolation/specs/insert-conflict-ignore.spec new file mode 100644 index 0000000..fde43b3 --- /dev/null +++ b/src/test/isolation/specs/insert-conflict-ignore.spec @@ -0,0 +1,41 @@ +# INSERT...ON CONFLICT IGNORE test +# +# This test tries to expose problems with the interaction between concurrent +# sessions during INSERT...ON CONFLICT IGNORE. +# +# The convention here is that session 1 always ends up inserting, and session 2 +# always ends up ignoring. + +setup +{ + CREATE TABLE ints (key int primary key, val text); +} + +teardown +{ + DROP TABLE ints; +} + +session "s1" +setup +{ + BEGIN ISOLATION LEVEL READ COMMITTED; +} +step "ignore1" { INSERT INTO ints(key, val) VALUES(1, 'ignore1') ON CONFLICT IGNORE; } +step "c1" { COMMIT; } +step "a1" { ABORT; } + +session "s2" +setup +{ + BEGIN ISOLATION LEVEL READ COMMITTED; +} +step "ignore2" { INSERT INTO ints(key, val) VALUES(1, 'ignore2') ON CONFLICT IGNORE; } +step "select2" { SELECT * FROM ints; } +step "c2" { COMMIT; } +step "a2" { ABORT; } + +# Regular case where one session block-waits on another to determine if it +# should proceed with an insert or ignore. +permutation "ignore1" "ignore2" "c1" "select2" "c2" +permutation "ignore1" "ignore2" "a1" "select2" "c2" diff --git a/src/test/isolation/specs/insert-conflict-update.spec b/src/test/isolation/specs/insert-conflict-update.spec new file mode 100644 index 0000000..e5f62b2 --- /dev/null +++ b/src/test/isolation/specs/insert-conflict-update.spec @@ -0,0 +1,40 @@ +# INSERT...ON CONFLICT UPDATE test +# +# This test tries to expose problems with the interaction between concurrent +# sessions. + +setup +{ + CREATE TABLE upsert (key int primary key, val text); +} + +teardown +{ + DROP TABLE upsert; +} + +session "s1" +setup +{ + BEGIN ISOLATION LEVEL READ COMMITTED; +} +step "insert1" { INSERT INTO upsert(key, val) VALUES(1, 'insert1') ON CONFLICT UPDATE set val = 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 UPDATE set val = 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/inherit.out b/src/test/regress/expected/inherit.out index 56e2c99..27aae0e 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -13,6 +13,9 @@ INSERT INTO a(aa) VALUES('aaaaa'); INSERT INTO a(aa) VALUES('aaaaaa'); INSERT INTO a(aa) VALUES('aaaaaaa'); INSERT INTO a(aa) VALUES('aaaaaaaa'); +-- INSERT ON CONFLICT UPDATE does not support inheritance +INSERT INTO a(aa) VALUES('aaaaaaaa') ON CONFLICT UPDATE set aa = 'bbbbbbbb'; +ERROR: INSERT...ON CONFLICT does not support table inheritance INSERT INTO b(aa) VALUES('bbb'); INSERT INTO b(aa) VALUES('bbbb'); INSERT INTO b(aa) VALUES('bbbbb'); diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 1675075..243eb6c 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -367,6 +367,11 @@ UPDATE atest5 SET one = 8; -- fail ERROR: permission denied for relation atest5 UPDATE atest5 SET three = 5, one = 2; -- fail ERROR: permission denied for relation atest5 +INSERT INTO atest5(two) VALUES (6) ON CONFLICT UPDATE set three = 10; -- ok +INSERT INTO atest5(two) VALUES (6) ON CONFLICT UPDATE set one = 8; -- fails (due to UPDATE) +ERROR: permission denied for relation atest5 +INSERT INTO atest5(three) VALUES (4) ON CONFLICT UPDATE set three = 10; -- fail (due to INSERT) +ERROR: permission denied for relation atest5 SET SESSION AUTHORIZATION regressuser1; REVOKE ALL (one) ON atest5 FROM regressuser4; GRANT SELECT (one,two,blue) ON atest6 TO regressuser4; diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index ca56b47..4490dab 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1123,12 +1123,19 @@ 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); +-- insert rules still apply - ON CONFLICT UPDATE is irrelevant +insert into shoelace values ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0) + on conflict ignore; +insert into shoelace values ('sl10', 1000, 'magenta', 70.0, 'inch', 0.0) + on conflict update set sl_color = 'orange'; SELECT * FROM shoelace_obsolete ORDER BY sl_len_cm; sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm ------------+----------+------------+--------+----------+----------- sl9 | 0 | pink | 35 | inch | 88.9 sl10 | 1000 | magenta | 40 | inch | 101.6 -(2 rows) + sl10 | 1000 | magenta | 40 | inch | 101.6 + sl10 | 1000 | magenta | 70 | inch | 177.8 +(4 rows) SELECT * FROM shoelace_candelete; sl_name | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm @@ -1144,6 +1151,8 @@ SELECT * FROM shoelace ORDER BY sl_name; ------------+----------+------------+--------+----------+----------- sl1 | 5 | black | 80 | cm | 80 sl10 | 1000 | magenta | 40 | inch | 101.6 + sl10 | 1000 | magenta | 40 | inch | 101.6 + sl10 | 1000 | magenta | 70 | inch | 177.8 sl2 | 6 | black | 100 | cm | 100 sl3 | 10 | black | 35 | inch | 88.9 sl4 | 8 | black | 40 | inch | 101.6 @@ -1151,7 +1160,7 @@ SELECT * FROM shoelace ORDER BY sl_name; sl6 | 20 | brown | 0.9 | m | 90 sl7 | 6 | brown | 60 | cm | 60 sl8 | 21 | brown | 40 | inch | 101.6 -(9 rows) +(11 rows) SELECT * FROM shoe ORDER BY shoename; shoename | sh_avail | slcolor | slminlen | slminlen_cm | slmaxlen | slmaxlen_cm | slunit @@ -2324,6 +2333,15 @@ 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". +insert into rule_and_refint_t3 values (1, 13, 11, 'row6') + on conflict ignore; +ERROR: insert or update on table "rule_and_refint_t3" violates foreign key constraint "rule_and_refint_t3_id3a_fkey" +DETAIL: Key (id3a, id3b)=(1, 13) is not present in table "rule_and_refint_t1". +insert into rule_and_refint_t3 values (1, 13, 11, 'row6') + on conflict update set id3a = 1, id2b = 11, id3c = 11; +ERROR: column "id2b" of relation "rule_and_refint_t3" does not exist +LINE 2: on conflict update set id3a = 1, id2b = 11, id3c = 11; + ^ create rule rule_and_refint_t3_ins as on insert to rule_and_refint_t3 where (exists (select 1 from rule_and_refint_t3 where (((rule_and_refint_t3.id3a = new.id3a) diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index 01c9130..9769599 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -599,6 +599,24 @@ from (0 rows) -- +-- Test case for subselect within UPDATE of INSERT...ON CONFLICT UPDATE +-- +create temp table upsert(key int4 primary key, val text); +insert into upsert values(1, 'val') on conflict update set val = 'not seen'; +insert into upsert values(1, 'val') on conflict update set val = 'should see ' || (select f1 from int4_tbl where f1 != 0 limit 1)::text; +ERROR: paramaterized auxiliary UPDATE queries are unsupported +select * from upsert; + key | val +-----+----- + 1 | val +(1 row) + +with aa as (select 'int4_tbl' u from int4_tbl limit 1) +insert into upsert values (1, 'x'), (999, 'y') +on conflict update set val = (select u from aa) +returning *; +ERROR: paramaterized auxiliary UPDATE queries are unsupported +-- -- Test case for cross-type partial matching in hashed subplan (bug #7597) -- create temp table outer_7597 (f1 int4, f2 int4); diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index f1a5fde..803532c 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -1731,3 +1731,93 @@ select * from self_ref_trigger; drop table self_ref_trigger; drop function self_ref_trigger_ins_func(); drop function self_ref_trigger_del_func(); +-- +-- Verify behavior of before and after triggers with INSERT...ON CONFLICT +-- UPDATE +-- +create table upsert (key int4 primary key, color text); +create function upsert_before_func() + returns trigger language plpgsql as +$$ +begin + if (TG_OP = 'UPDATE') then + raise warning 'before update (old): %', old.*::text; + raise warning 'before update (new): %', new.*::text; + elsif (TG_OP = 'INSERT') then + raise warning 'before insert (new): %', new.*::text; + if new.key % 2 = 0 then + new.key := new.key + 1; + new.color := new.color || ' trig modified'; + raise warning 'before insert (new, modified): %', new.*::text; + end if; + end if; + return new; +end; +$$; +create trigger upsert_before_trig before insert or update on upsert + for each row execute procedure upsert_before_func(); +create function upsert_after_func() + returns trigger language plpgsql as +$$ +begin + if (TG_OP = 'UPDATE') then + raise warning 'after update (old): %', new.*::text; + raise warning 'after update (new): %', new.*::text; + elsif (TG_OP = 'INSERT') then + raise warning 'after insert (new): %', new.*::text; + end if; + return null; +end; +$$; +create trigger upsert_after_trig after insert or update on upsert + for each row execute procedure upsert_after_func(); +insert into upsert values(1, 'black') on conflict update set color = 'updated ' || color; +WARNING: before insert (new): (1,black) +WARNING: after insert (new): (1,black) +insert into upsert values(2, 'red') on conflict update set color = 'updated ' || 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 update set color = 'updated ' || 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 update set color = 'updated ' || 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 update set color = 'updated ' || 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 update set color = 'updated ' || 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 update set color = 'updated ' || 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 update set color = 'updated ' || 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 ea9197a..3403879 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -215,6 +215,8 @@ 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 UPDATE SET upper = upper; -- fails, unsupported +ERROR: INSERT ON CONFLICT is not supported on updatable views ALTER VIEW rw_view15 ALTER COLUMN upper SET DEFAULT 'NOT SET'; INSERT INTO rw_view15 (a) VALUES (4); -- should fail ERROR: cannot insert into column "upper" of view "rw_view15" diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out index 1de2a86..41da5e9 100644 --- a/src/test/regress/expected/update.out +++ b/src/test/regress/expected/update.out @@ -147,4 +147,9 @@ SELECT a, b, char_length(c) FROM update_test; 42 | 12 | 10000 (4 rows) +ALTER TABLE update_test ADD constraint uuu UNIQUE(a); +INSERT INTO update_test +VALUES (21, 1, 'b'), (41, 1, 'b'), (42, 1, 'b') +ON CONFLICT UPDATE SET (b, c) = (7, 'f'); +-- SELECT a, b FROM update_test; DROP TABLE update_test; diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out index 06b372b..44a2f48 100644 --- a/src/test/regress/expected/with.out +++ b/src/test/regress/expected/with.out @@ -1806,6 +1806,70 @@ SELECT * FROM y; -400 (22 rows) +-- data-modifying WITH containing INSERT...ON CONFLICT UPDATE +CREATE TABLE z AS SELECT i AS k, (i || ' v')::text v FROM generate_series(1, 16, 3) i; +ALTER TABLE z ADD UNIQUE (k); +WITH t AS ( + INSERT INTO z SELECT i, 'insert' + FROM generate_series(0, 16) i + ON CONFLICT UPDATE SET v = v || ', now update' + RETURNING * +) +SELECT * FROM t JOIN y ON t.k = y.a ORDER BY a, k; + k | v | a +---+--------+--- + 0 | insert | 0 + 0 | insert | 0 +(2 rows) + +-- New query/snapshot demonstrates side-effects of previous query. +SELECT * FROM z ORDER BY k; + k | v +----+------------------ + 0 | insert + 1 | 1 v, now update + 2 | insert + 3 | insert + 4 | 4 v, now update + 5 | insert + 6 | insert + 7 | 7 v, now update + 8 | insert + 9 | insert + 10 | 10 v, now update + 11 | insert + 12 | insert + 13 | 13 v, now update + 14 | insert + 15 | insert + 16 | 16 v, now update +(17 rows) + +-- +-- All these cases should fail, due to restrictions imposed upon the UPDATE +-- portion of the query. +-- +WITH aa AS (SELECT 1 a, 2 b) +INSERT INTO z VALUES(1, 'insert') +ON CONFLICT UPDATE SET V = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1); +ERROR: paramaterized auxiliary UPDATE queries are unsupported +WITH aa AS (SELECT 1 a, 2 b) +INSERT INTO z VALUES(1, 'insert') +ON CONFLICT UPDATE SET v = ' update' WHERE k = (SELECT a FROM aa); +ERROR: paramaterized auxiliary UPDATE queries are unsupported +WITH aa AS (SELECT 1 a, 2 b) +INSERT INTO z VALUES(1, 'insert') +ON CONFLICT UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1); +ERROR: paramaterized auxiliary UPDATE queries are unsupported +WITH aa AS (SELECT 'a' a, 'b' b UNION ALL SELECT 'a' a, 'b' b) +INSERT INTO z VALUES(1, 'insert') +ON CONFLICT UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 'a' LIMIT 1); +ERROR: paramaterized auxiliary UPDATE queries are unsupported +WITH aa AS (SELECT 1 a, 2 b) +INSERT INTO z VALUES(1, (SELECT b || ' insert' FROM aa WHERE a = 1 )) +ON CONFLICT UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1); +ERROR: paramaterized auxiliary UPDATE queries are unsupported +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 16d38f6..a004a7e 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 UPDATE SET t = 'five-upsert-update'; +INSERT INTO UNIQUE_TBL VALUES (6, 'six-upsert-insert') ON CONFLICT UPDATE SET t = 'six-upsert-update'; +-- should fail +INSERT INTO UNIQUE_TBL VALUES (1, 'a'), (2, 'b'), (2, 'b') ON CONFLICT UPDATE SET t = 'fails'; + SELECT '' AS five, * FROM UNIQUE_TBL; DROP TABLE UNIQUE_TBL; diff --git a/src/test/regress/output/constraints.source b/src/test/regress/output/constraints.source index 2ffd263..1f6870b 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 UPDATE SET t = 'five-upsert-update'; +INSERT INTO UNIQUE_TBL VALUES (6, 'six-upsert-insert') ON CONFLICT UPDATE SET t = 'six-upsert-update'; +-- should fail +INSERT INTO UNIQUE_TBL VALUES (1, 'a'), (2, 'b'), (2, 'b') ON CONFLICT UPDATE SET t = 'fails'; +ERROR: could not lock instantaneously invisible tuple inserted in same transaction +HINT: Ensure that no rows proposed for insertion in the same command have constrained values that duplicate each other. SELECT '' AS five, * FROM UNIQUE_TBL; - five | i | t -------+---+------- + five | i | t +------+---+-------------------- | 1 | one | 2 | two | 4 | four - | 5 | one | | six | | seven -(6 rows) + | 5 | five-upsert-update + | 6 | six-upsert-insert +(7 rows) DROP TABLE UNIQUE_TBL; CREATE TABLE UNIQUE_TBL (i int, t text, diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index 09bb750..5101858 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -13,6 +13,9 @@ INSERT INTO a(aa) VALUES('aaaaaa'); INSERT INTO a(aa) VALUES('aaaaaaa'); INSERT INTO a(aa) VALUES('aaaaaaaa'); +-- INSERT ON CONFLICT UPDATE does not support inheritance +INSERT INTO a(aa) VALUES('aaaaaaaa') ON CONFLICT UPDATE set aa = 'bbbbbbbb'; + INSERT INTO b(aa) VALUES('bbb'); INSERT INTO b(aa) VALUES('bbbb'); INSERT INTO b(aa) VALUES('bbbbb'); diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql index a0ff953..230b6d6 100644 --- a/src/test/regress/sql/privileges.sql +++ b/src/test/regress/sql/privileges.sql @@ -245,6 +245,9 @@ INSERT INTO atest5 VALUES (5,5,5); -- fail UPDATE atest5 SET three = 10; -- ok UPDATE atest5 SET one = 8; -- fail UPDATE atest5 SET three = 5, one = 2; -- fail +INSERT INTO atest5(two) VALUES (6) ON CONFLICT UPDATE set three = 10; -- ok +INSERT INTO atest5(two) VALUES (6) ON CONFLICT UPDATE set one = 8; -- fails (due to UPDATE) +INSERT INTO atest5(three) VALUES (4) ON CONFLICT UPDATE set three = 10; -- fail (due to INSERT) SET SESSION AUTHORIZATION regressuser1; REVOKE ALL (one) ON atest5 FROM regressuser4; diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql index 1e15f84..91b5f2d 100644 --- a/src/test/regress/sql/rules.sql +++ b/src/test/regress/sql/rules.sql @@ -680,6 +680,11 @@ 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); +-- insert rules still apply - ON CONFLICT UPDATE is irrelevant +insert into shoelace values ('sl10', 1000, 'magenta', 40.0, 'inch', 0.0) + on conflict ignore; +insert into shoelace values ('sl10', 1000, 'magenta', 70.0, 'inch', 0.0) + on conflict update set sl_color = 'orange'; SELECT * FROM shoelace_obsolete ORDER BY sl_len_cm; SELECT * FROM shoelace_candelete; @@ -844,6 +849,10 @@ 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'); +insert into rule_and_refint_t3 values (1, 13, 11, 'row6') + on conflict ignore; +insert into rule_and_refint_t3 values (1, 13, 11, 'row6') + on conflict update set id3a = 1, id2b = 11, id3c = 11; create rule rule_and_refint_t3_ins as on insert to rule_and_refint_t3 where (exists (select 1 from rule_and_refint_t3 diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql index 56707e2..7d6e35b 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -361,6 +361,20 @@ from int4_tbl i4 on dummy = i4.f1; -- +-- Test case for subselect within UPDATE of INSERT...ON CONFLICT UPDATE +-- +create temp table upsert(key int4 primary key, val text); +insert into upsert values(1, 'val') on conflict update set val = 'not seen'; +insert into upsert values(1, 'val') on conflict update set val = 'should see ' || (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 update set val = (select u from aa) +returning *; + +-- -- Test case for cross-type partial matching in hashed subplan (bug #7597) -- diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index 0ea2c31..d3b6aa3 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -1173,3 +1173,61 @@ select * from self_ref_trigger; drop table self_ref_trigger; drop function self_ref_trigger_ins_func(); drop function self_ref_trigger_del_func(); + +-- +-- Verify behavior of before and after triggers with INSERT...ON CONFLICT +-- UPDATE +-- +create table upsert (key int4 primary key, color text); + +create function upsert_before_func() + returns trigger language plpgsql as +$$ +begin + if (TG_OP = 'UPDATE') then + raise warning 'before update (old): %', old.*::text; + raise warning 'before update (new): %', new.*::text; + elsif (TG_OP = 'INSERT') then + raise warning 'before insert (new): %', new.*::text; + if new.key % 2 = 0 then + new.key := new.key + 1; + new.color := new.color || ' trig modified'; + raise warning 'before insert (new, modified): %', new.*::text; + end if; + end if; + return new; +end; +$$; +create trigger upsert_before_trig before insert or update on upsert + for each row execute procedure upsert_before_func(); + +create function upsert_after_func() + returns trigger language plpgsql as +$$ +begin + if (TG_OP = 'UPDATE') then + raise warning 'after update (old): %', new.*::text; + raise warning 'after update (new): %', new.*::text; + elsif (TG_OP = 'INSERT') then + raise warning 'after insert (new): %', new.*::text; + end if; + return null; +end; +$$; +create trigger upsert_after_trig after insert or update on upsert + for each row execute procedure upsert_after_func(); + +insert into upsert values(1, 'black') on conflict update set color = 'updated ' || color; +insert into upsert values(2, 'red') on conflict update set color = 'updated ' || color; +insert into upsert values(3, 'orange') on conflict update set color = 'updated ' || color; +insert into upsert values(4, 'green') on conflict update set color = 'updated ' || color; +insert into upsert values(5, 'purple') on conflict update set color = 'updated ' || color; +insert into upsert values(6, 'white') on conflict update set color = 'updated ' || color; +insert into upsert values(7, 'pink') on conflict update set color = 'updated ' || color; +insert into upsert values(8, 'yellow') on conflict update set color = 'updated ' || 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 c072fca..0cc2f8c 100644 --- a/src/test/regress/sql/updatable_views.sql +++ b/src/test/regress/sql/updatable_views.sql @@ -69,6 +69,7 @@ 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 UPDATE SET upper = upper; -- fails, unsupported ALTER VIEW rw_view15 ALTER COLUMN upper SET DEFAULT 'NOT SET'; INSERT INTO rw_view15 (a) VALUES (4); -- should fail UPDATE rw_view15 SET upper='ROW 3' WHERE a=3; -- should fail diff --git a/src/test/regress/sql/update.sql b/src/test/regress/sql/update.sql index e71128c..2692eef 100644 --- a/src/test/regress/sql/update.sql +++ b/src/test/regress/sql/update.sql @@ -74,4 +74,12 @@ UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a = 10; UPDATE update_test SET c = repeat('x', 10000) WHERE c = 'car'; SELECT a, b, char_length(c) FROM update_test; +ALTER TABLE update_test ADD constraint uuu UNIQUE(a); + +INSERT INTO update_test +VALUES (21, 1, 'b'), (41, 1, 'b'), (42, 1, 'b') +ON CONFLICT UPDATE SET (b, c) = (7, 'f'); + +-- SELECT a, b FROM update_test; + DROP TABLE update_test; diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql index c716369..200c200 100644 --- a/src/test/regress/sql/with.sql +++ b/src/test/regress/sql/with.sql @@ -795,6 +795,43 @@ SELECT * FROM t LIMIT 10; SELECT * FROM y; +-- data-modifying WITH containing INSERT...ON CONFLICT UPDATE +CREATE TABLE z AS SELECT i AS k, (i || ' v')::text v FROM generate_series(1, 16, 3) i; +ALTER TABLE z ADD UNIQUE (k); + +WITH t AS ( + INSERT INTO z SELECT i, 'insert' + FROM generate_series(0, 16) i + ON CONFLICT UPDATE SET v = v || ', now update' + RETURNING * +) +SELECT * FROM t JOIN y ON t.k = y.a ORDER BY a, k; + +-- New query/snapshot demonstrates side-effects of previous query. +SELECT * FROM z ORDER BY k; + +-- +-- All these cases should fail, due to restrictions imposed upon the UPDATE +-- portion of the query. +-- +WITH aa AS (SELECT 1 a, 2 b) +INSERT INTO z VALUES(1, 'insert') +ON CONFLICT 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 UPDATE SET v = ' update' WHERE k = (SELECT a FROM aa); +WITH aa AS (SELECT 1 a, 2 b) +INSERT INTO z VALUES(1, 'insert') +ON CONFLICT 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 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 UPDATE SET v = (SELECT b || ' update' FROM aa WHERE a = 1 LIMIT 1); + +DROP TABLE Z; + -- check that run to completion happens in proper ordering TRUNCATE TABLE y; -- 1.9.1