0003-Tests-for-INSERT-.-ON-CONFLICT-UPDATE-IGNORE.patch

From 881564456e3c0b868324f61568f835f330cb2ad3 Mon Sep 17 00:00:00 2001
From: Peter Geoghegan <pg@heroku.com>
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; <waiting ...>
+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; <waiting ...>
+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'; <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 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'; <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 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