Re: Fwd: Problem with a "complex" upsert - Mailing list pgsql-bugs

I wrote:
> Andres Freund <andres@anarazel.de> writes:
>> I think we definitely should try to get this in.

> Well, if you're excited about it, help Dean review it.

So, in the spirit of "put your money where your mouth is", I've been
working off-list with Dean today to review this patch.  We found a couple
additional minor issues:

* We noticed that the rewriter was expanding the EXCLUDED
pseudo-relation's RTE into an RTE_SUBQUERY RTE, rather than leaving it
in the intended form as an RTE_RELATION RTE with a nonstandard rtekind.
(This happens basically always with a view target rel in the existing
code, but only in corner cases after Dean's patch.)  While this doesn't
seem to have obvious bad effects, it's both unintended and a waste of
cycles.  Also, the fact that this area seems rather undertested leaves
me not wanting to have weird data structure differences that happen
only in corner cases.  So we added a check to fireRIRrules to prevent
that from happening.

* We happened across some unexpected permissions failures while checking
the patch in cases where the calling user is not the view owner.  This
turned out to be because of a pre-existing oversight: the replacement
EXCLUDED RTE is initially manufactured with requiredPerms = ACL_SELECT,
and nothing was getting done to change that, leading to failure if the
calling user doesn't have SELECT on the underlying table.  (The desired
behavior is that the view owner needs permissions on the underlying table,
but the calling user only needs permissions on the view.)  So that's
easily fixed by zeroing out requiredPerms in the EXCLUDED RTE; nothing is
lost because other code was already adding the required permission check
flags to the query's actual target relation.  But out of paranoia we added
a bunch of permissions-checking test cases to updatable_views.sql.

Attached is our finished patch against HEAD.  This is pretty much all
Dean's work, but I'm posting it on his behalf because it's late in the UK
and he's gone offline for the day.  In the interests of getting a
full set of buildfarm testing on the patch before Monday's wrap deadline,
I'm going to finish up back-porting the patch and push it tonight.

            regards, tom lane

diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 05f5759..c601b6d 100644
*** a/src/backend/parser/analyze.c
--- b/src/backend/parser/analyze.c
*************** transformOnConflictClause(ParseState *ps
*** 1022,1030 ****
      if (onConflictClause->action == ONCONFLICT_UPDATE)
      {
          Relation    targetrel = pstate->p_target_relation;
-         Var           *var;
-         TargetEntry *te;
-         int            attno;

          /*
           * All INSERT expressions have been parsed, get ready for potentially
--- 1022,1027 ----
*************** transformOnConflictClause(ParseState *ps
*** 1033,1107 ****
          pstate->p_is_insert = false;

          /*
!          * Add range table entry for the EXCLUDED pseudo relation; relkind is
           * set to composite to signal that we're not dealing with an actual
!          * relation.
           */
          exclRte = addRangeTableEntryForRelation(pstate,
                                                  targetrel,
                                                  makeAlias("excluded", NIL),
                                                  false, false);
          exclRte->relkind = RELKIND_COMPOSITE_TYPE;
!         exclRelIndex = list_length(pstate->p_rtable);
!
!         /*
!          * Build a targetlist representing the columns of the EXCLUDED pseudo
!          * relation.  Have to be careful to use resnos that correspond to
!          * attnos of the underlying relation.
!          */
!         for (attno = 0; attno < RelationGetNumberOfAttributes(targetrel); attno++)
!         {
!             Form_pg_attribute attr = TupleDescAttr(targetrel->rd_att, attno);
!             char       *name;
!
!             if (attr->attisdropped)
!             {
!                 /*
!                  * can't use atttypid here, but it doesn't really matter what
!                  * type the Const claims to be.
!                  */
!                 var = (Var *) makeNullConst(INT4OID, -1, InvalidOid);
!                 name = "";
!             }
!             else
!             {
!                 var = makeVar(exclRelIndex, attno + 1,
!                               attr->atttypid, attr->atttypmod,
!                               attr->attcollation,
!                               0);
!                 name = pstrdup(NameStr(attr->attname));
!             }
!
!             te = makeTargetEntry((Expr *) var,
!                                  attno + 1,
!                                  name,
!                                  false);

!             /* don't require select access yet */
!             exclRelTlist = lappend(exclRelTlist, te);
!         }

!         /*
!          * Add a whole-row-Var entry to support references to "EXCLUDED.*".
!          * Like the other entries in exclRelTlist, its resno must match the
!          * Var's varattno, else the wrong things happen while resolving
!          * references in setrefs.c.  This is against normal conventions for
!          * targetlists, but it's okay since we don't use this as a real tlist.
!          */
!         var = makeVar(exclRelIndex, InvalidAttrNumber,
!                       targetrel->rd_rel->reltype,
!                       -1, InvalidOid, 0);
!         te = makeTargetEntry((Expr *) var, InvalidAttrNumber, NULL, true);
!         exclRelTlist = lappend(exclRelTlist, te);

          /*
           * Add EXCLUDED and the target RTE to the namespace, so that they can
!          * be used in the UPDATE statement.
           */
          addRTEtoQuery(pstate, exclRte, false, true, true);
          addRTEtoQuery(pstate, pstate->p_target_rangetblentry,
                        false, true, true);

          onConflictSet =
              transformUpdateTargetList(pstate, onConflictClause->targetList);

--- 1030,1065 ----
          pstate->p_is_insert = false;

          /*
!          * Add range table entry for the EXCLUDED pseudo relation.  relkind is
           * set to composite to signal that we're not dealing with an actual
!          * relation, and no permission checks are required on it.  (We'll
!          * check the actual target relation, instead.)
           */
          exclRte = addRangeTableEntryForRelation(pstate,
                                                  targetrel,
                                                  makeAlias("excluded", NIL),
                                                  false, false);
          exclRte->relkind = RELKIND_COMPOSITE_TYPE;
!         exclRte->requiredPerms = 0;
!         /* other permissions fields in exclRte are already empty */

!         exclRelIndex = list_length(pstate->p_rtable);

!         /* Create EXCLUDED rel's targetlist for use by EXPLAIN */
!         exclRelTlist = BuildOnConflictExcludedTargetlist(targetrel,
!                                                          exclRelIndex);

          /*
           * Add EXCLUDED and the target RTE to the namespace, so that they can
!          * be used in the UPDATE subexpressions.
           */
          addRTEtoQuery(pstate, exclRte, false, true, true);
          addRTEtoQuery(pstate, pstate->p_target_rangetblentry,
                        false, true, true);

+         /*
+          * Now transform the UPDATE subexpressions.
+          */
          onConflictSet =
              transformUpdateTargetList(pstate, onConflictClause->targetList);

*************** transformOnConflictClause(ParseState *ps
*** 1127,1132 ****
--- 1085,1158 ----


  /*
+  * BuildOnConflictExcludedTargetlist
+  *        Create target list for the EXCLUDED pseudo-relation of ON CONFLICT,
+  *        representing the columns of targetrel with varno exclRelIndex.
+  *
+  * Note: Exported for use in the rewriter.
+  */
+ List *
+ BuildOnConflictExcludedTargetlist(Relation targetrel,
+                                   Index exclRelIndex)
+ {
+     List       *result = NIL;
+     int            attno;
+     Var           *var;
+     TargetEntry *te;
+
+     /*
+      * Note that resnos of the tlist must correspond to attnos of the
+      * underlying relation, hence we need entries for dropped columns too.
+      */
+     for (attno = 0; attno < RelationGetNumberOfAttributes(targetrel); attno++)
+     {
+         Form_pg_attribute attr = TupleDescAttr(targetrel->rd_att, attno);
+         char       *name;
+
+         if (attr->attisdropped)
+         {
+             /*
+              * can't use atttypid here, but it doesn't really matter what type
+              * the Const claims to be.
+              */
+             var = (Var *) makeNullConst(INT4OID, -1, InvalidOid);
+             name = "";
+         }
+         else
+         {
+             var = makeVar(exclRelIndex, attno + 1,
+                           attr->atttypid, attr->atttypmod,
+                           attr->attcollation,
+                           0);
+             name = pstrdup(NameStr(attr->attname));
+         }
+
+         te = makeTargetEntry((Expr *) var,
+                              attno + 1,
+                              name,
+                              false);
+
+         result = lappend(result, te);
+     }
+
+     /*
+      * Add a whole-row-Var entry to support references to "EXCLUDED.*".  Like
+      * the other entries in the EXCLUDED tlist, its resno must match the Var's
+      * varattno, else the wrong things happen while resolving references in
+      * setrefs.c.  This is against normal conventions for targetlists, but
+      * it's okay since we don't use this as a real tlist.
+      */
+     var = makeVar(exclRelIndex, InvalidAttrNumber,
+                   targetrel->rd_rel->reltype,
+                   -1, InvalidOid, 0);
+     te = makeTargetEntry((Expr *) var, InvalidAttrNumber, NULL, true);
+     result = lappend(result, te);
+
+     return result;
+ }
+
+
+ /*
   * count_rowexpr_columns -
   *      get number of columns contained in a ROW() expression;
   *      return -1 if expression isn't a RowExpr or a Var referencing one.
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 5b87c55..3123ee2 100644
*** a/src/backend/rewrite/rewriteHandler.c
--- b/src/backend/rewrite/rewriteHandler.c
***************
*** 29,34 ****
--- 29,35 ----
  #include "nodes/nodeFuncs.h"
  #include "parser/analyze.h"
  #include "parser/parse_coerce.h"
+ #include "parser/parse_relation.h"
  #include "parser/parsetree.h"
  #include "rewrite/rewriteDefine.h"
  #include "rewrite/rewriteHandler.h"
*************** fireRIRrules(Query *parsetree, List *act
*** 1771,1776 ****
--- 1772,1788 ----
              continue;

          /*
+          * In INSERT ... ON CONFLICT, ignore the EXCLUDED pseudo-relation;
+          * even if it points to a view, we needn't expand it, and should not
+          * because we want the RTE to remain of RTE_RELATION type.  Otherwise,
+          * it would get changed to RTE_SUBQUERY type, which is an
+          * untested/unsupported situation.
+          */
+         if (parsetree->onConflict &&
+             rt_index == parsetree->onConflict->exclRelIndex)
+             continue;
+
+         /*
           * If the table is not referenced in the query, then we ignore it.
           * This prevents infinite expansion loop due to new rtable entries
           * inserted by expansion of a rule. A table is referenced if it is
*************** rewriteTargetView(Query *parsetree, Rela
*** 2875,2882 ****
       */
      base_rte->relkind = base_rel->rd_rel->relkind;

-     heap_close(base_rel, NoLock);
-
      /*
       * If the view query contains any sublink subqueries then we need to also
       * acquire locks on any relations they refer to.  We know that there won't
--- 2887,2892 ----
*************** rewriteTargetView(Query *parsetree, Rela
*** 3035,3040 ****
--- 3045,3137 ----
      }

      /*
+      * For INSERT .. ON CONFLICT .. DO UPDATE, we must also update assorted
+      * stuff in the onConflict data structure.
+      */
+     if (parsetree->onConflict &&
+         parsetree->onConflict->action == ONCONFLICT_UPDATE)
+     {
+         Index        old_exclRelIndex,
+                     new_exclRelIndex;
+         RangeTblEntry *new_exclRte;
+         List       *tmp_tlist;
+
+         /*
+          * Like the INSERT/UPDATE code above, update the resnos in the
+          * auxiliary UPDATE targetlist to refer to columns of the base
+          * relation.
+          */
+         foreach(lc, parsetree->onConflict->onConflictSet)
+         {
+             TargetEntry *tle = (TargetEntry *) lfirst(lc);
+             TargetEntry *view_tle;
+
+             if (tle->resjunk)
+                 continue;
+
+             view_tle = get_tle_by_resno(view_targetlist, tle->resno);
+             if (view_tle != NULL && !view_tle->resjunk && IsA(view_tle->expr, Var))
+                 tle->resno = ((Var *) view_tle->expr)->varattno;
+             else
+                 elog(ERROR, "attribute number %d not found in view targetlist",
+                      tle->resno);
+         }
+
+         /*
+          * Also, create a new RTE for the EXCLUDED pseudo-relation, using the
+          * query's new base rel (which may well have a different column list
+          * from the view, hence we need a new column alias list).  This should
+          * match transformOnConflictClause.  In particular, note that the
+          * relkind is set to composite to signal that we're not dealing with
+          * an actual relation, and no permissions checks are wanted.
+          */
+         old_exclRelIndex = parsetree->onConflict->exclRelIndex;
+
+         new_exclRte = addRangeTableEntryForRelation(make_parsestate(NULL),
+                                                     base_rel,
+                                                     makeAlias("excluded",
+                                                               NIL),
+                                                     false, false);
+         new_exclRte->relkind = RELKIND_COMPOSITE_TYPE;
+         new_exclRte->requiredPerms = 0;
+         /* other permissions fields in new_exclRte are already empty */
+
+         parsetree->rtable = lappend(parsetree->rtable, new_exclRte);
+         new_exclRelIndex = parsetree->onConflict->exclRelIndex =
+             list_length(parsetree->rtable);
+
+         /*
+          * Replace the targetlist for the EXCLUDED pseudo-relation with a new
+          * one, representing the columns from the new base relation.
+          */
+         parsetree->onConflict->exclRelTlist =
+             BuildOnConflictExcludedTargetlist(base_rel, new_exclRelIndex);
+
+         /*
+          * Update all Vars in the ON CONFLICT clause that refer to the old
+          * EXCLUDED pseudo-relation.  We want to use the column mappings
+          * defined in the view targetlist, but we need the outputs to refer to
+          * the new EXCLUDED pseudo-relation rather than the new target RTE.
+          * Also notice that "EXCLUDED.*" will be expanded using the view's
+          * rowtype, which seems correct.
+          */
+         tmp_tlist = copyObject(view_targetlist);
+
+         ChangeVarNodes((Node *) tmp_tlist, new_rt_index,
+                        new_exclRelIndex, 0);
+
+         parsetree->onConflict = (OnConflictExpr *)
+             ReplaceVarsFromTargetList((Node *) parsetree->onConflict,
+                                       old_exclRelIndex,
+                                       0,
+                                       view_rte,
+                                       tmp_tlist,
+                                       REPLACEVARS_REPORT_ERROR,
+                                       0,
+                                       &parsetree->hasSubLinks);
+     }
+
+     /*
       * For UPDATE/DELETE, pull up any WHERE quals from the view.  We know that
       * any Vars in the quals must reference the one base relation, so we need
       * only adjust their varnos to reference the new target (just the same as
*************** rewriteTargetView(Query *parsetree, Rela
*** 3161,3166 ****
--- 3258,3265 ----
          }
      }

+     heap_close(base_rel, NoLock);
+
      return parsetree;
  }

diff --git a/src/include/parser/analyze.h b/src/include/parser/analyze.h
index 687ae1b..7b5b90c 100644
*** a/src/include/parser/analyze.h
--- b/src/include/parser/analyze.h
*************** extern void applyLockingClause(Query *qr
*** 43,46 ****
--- 43,49 ----
                     LockClauseStrength strength,
                     LockWaitPolicy waitPolicy, bool pushedDown);

+ extern List *BuildOnConflictExcludedTargetlist(Relation targetrel,
+                                   Index exclRelIndex);
+
  #endif                            /* ANALYZE_H */
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index b34bab4..e64d693 100644
*** a/src/test/regress/expected/updatable_views.out
--- b/src/test/regress/expected/updatable_views.out
*************** ERROR:  new row violates check option fo
*** 2578,2580 ****
--- 2578,2774 ----
  DETAIL:  Failing row contains (2, no such row in sometable).
  drop view wcowrtest_v, wcowrtest_v2;
  drop table wcowrtest, sometable;
+ -- Check INSERT .. ON CONFLICT DO UPDATE works correctly when the view's
+ -- columns are named and ordered differently than the underlying table's.
+ create table uv_iocu_tab (a text unique, b float);
+ insert into uv_iocu_tab values ('xyxyxy', 0);
+ create view uv_iocu_view as
+    select b, b+1 as c, a, '2.0'::text as two from uv_iocu_tab;
+ insert into uv_iocu_view (a, b) values ('xyxyxy', 1)
+    on conflict (a) do update set b = uv_iocu_view.b;
+ select * from uv_iocu_tab;
+    a    | b
+ --------+---
+  xyxyxy | 0
+ (1 row)
+
+ insert into uv_iocu_view (a, b) values ('xyxyxy', 1)
+    on conflict (a) do update set b = excluded.b;
+ select * from uv_iocu_tab;
+    a    | b
+ --------+---
+  xyxyxy | 1
+ (1 row)
+
+ -- OK to access view columns that are not present in underlying base
+ -- relation in the ON CONFLICT portion of the query
+ insert into uv_iocu_view (a, b) values ('xyxyxy', 3)
+    on conflict (a) do update set b = cast(excluded.two as float);
+ select * from uv_iocu_tab;
+    a    | b
+ --------+---
+  xyxyxy | 2
+ (1 row)
+
+ explain (costs off)
+ insert into uv_iocu_view (a, b) values ('xyxyxy', 3)
+    on conflict (a) do update set b = excluded.b where excluded.c > 0;
+                                     QUERY PLAN
+ -----------------------------------------------------------------------------------
+  Insert on uv_iocu_tab
+    Conflict Resolution: UPDATE
+    Conflict Arbiter Indexes: uv_iocu_tab_a_key
+    Conflict Filter: ((excluded.b + '1'::double precision) > '0'::double precision)
+    ->  Result
+ (5 rows)
+
+ insert into uv_iocu_view (a, b) values ('xyxyxy', 3)
+    on conflict (a) do update set b = excluded.b where excluded.c > 0;
+ select * from uv_iocu_tab;
+    a    | b
+ --------+---
+  xyxyxy | 3
+ (1 row)
+
+ drop view uv_iocu_view;
+ drop table uv_iocu_tab;
+ -- Test whole-row references to the view
+ create table uv_iocu_tab (a int unique, b text);
+ create view uv_iocu_view as
+     select b as bb, a as aa, uv_iocu_tab::text as cc from uv_iocu_tab;
+ insert into uv_iocu_view (aa,bb) values (1,'x');
+ explain (costs off)
+ insert into uv_iocu_view (aa,bb) values (1,'y')
+    on conflict (aa) do update set bb = 'Rejected: '||excluded.*
+    where excluded.aa > 0
+    and excluded.bb != ''
+    and excluded.cc is not null;
+                                                QUERY PLAN
+ ---------------------------------------------------------------------------------------------------------
+  Insert on uv_iocu_tab
+    Conflict Resolution: UPDATE
+    Conflict Arbiter Indexes: uv_iocu_tab_a_key
+    Conflict Filter: ((excluded.a > 0) AND (excluded.b <> ''::text) AND ((excluded.*)::text IS NOT NULL))
+    ->  Result
+ (5 rows)
+
+ insert into uv_iocu_view (aa,bb) values (1,'y')
+    on conflict (aa) do update set bb = 'Rejected: '||excluded.*
+    where excluded.aa > 0
+    and excluded.bb != ''
+    and excluded.cc is not null;
+ select * from uv_iocu_view;
+            bb            | aa |               cc
+ -------------------------+----+---------------------------------
+  Rejected: (y,1,"(1,y)") |  1 | (1,"Rejected: (y,1,""(1,y)"")")
+ (1 row)
+
+ -- Test omitting a column of the base relation
+ delete from uv_iocu_view;
+ insert into uv_iocu_view (aa,bb) values (1,'x');
+ insert into uv_iocu_view (aa) values (1)
+    on conflict (aa) do update set bb = 'Rejected: '||excluded.*;
+ select * from uv_iocu_view;
+           bb           | aa |              cc
+ -----------------------+----+-------------------------------
+  Rejected: (,1,"(1,)") |  1 | (1,"Rejected: (,1,""(1,)"")")
+ (1 row)
+
+ alter table uv_iocu_tab alter column b set default 'table default';
+ insert into uv_iocu_view (aa) values (1)
+    on conflict (aa) do update set bb = 'Rejected: '||excluded.*;
+ select * from uv_iocu_view;
+                           bb                           | aa |                                 cc
            
+
-------------------------------------------------------+----+---------------------------------------------------------------------
+  Rejected: ("table default",1,"(1,""table default"")") |  1 | (1,"Rejected: (""table default"",1,""(1,""""table
default"""")"")")
+ (1 row)
+
+ alter view uv_iocu_view alter column bb set default 'view default';
+ insert into uv_iocu_view (aa) values (1)
+    on conflict (aa) do update set bb = 'Rejected: '||excluded.*;
+ select * from uv_iocu_view;
+                          bb                          | aa |                                cc
        
+
-----------------------------------------------------+----+-------------------------------------------------------------------
+  Rejected: ("view default",1,"(1,""view default"")") |  1 | (1,"Rejected: (""view default"",1,""(1,""""view
default"""")"")")
+ (1 row)
+
+ -- Should fail to update non-updatable columns
+ insert into uv_iocu_view (aa) values (1)
+    on conflict (aa) do update set cc = 'XXX';
+ ERROR:  cannot insert into column "cc" of view "uv_iocu_view"
+ DETAIL:  View columns that are not columns of their base relation are not updatable.
+ drop view uv_iocu_view;
+ drop table uv_iocu_tab;
+ -- ON CONFLICT DO UPDATE permissions checks
+ create user regress_view_user1;
+ create user regress_view_user2;
+ set session authorization regress_view_user1;
+ create table base_tbl(a int unique, b text, c float);
+ insert into base_tbl values (1,'xxx',1.0);
+ create view rw_view1 as select b as bb, c as cc, a as aa from base_tbl;
+ grant select (aa,bb) on rw_view1 to regress_view_user2;
+ grant insert on rw_view1 to regress_view_user2;
+ grant update (bb) on rw_view1 to regress_view_user2;
+ set session authorization regress_view_user2;
+ insert into rw_view1 values ('yyy',2.0,1)
+   on conflict (aa) do update set bb = excluded.cc; -- Not allowed
+ ERROR:  permission denied for view rw_view1
+ insert into rw_view1 values ('yyy',2.0,1)
+   on conflict (aa) do update set bb = rw_view1.cc; -- Not allowed
+ ERROR:  permission denied for view rw_view1
+ insert into rw_view1 values ('yyy',2.0,1)
+   on conflict (aa) do update set bb = excluded.bb; -- OK
+ insert into rw_view1 values ('zzz',2.0,1)
+   on conflict (aa) do update set bb = rw_view1.bb||'xxx'; -- OK
+ insert into rw_view1 values ('zzz',2.0,1)
+   on conflict (aa) do update set cc = 3.0; -- Not allowed
+ ERROR:  permission denied for view rw_view1
+ reset session authorization;
+ select * from base_tbl;
+  a |   b    | c
+ ---+--------+---
+  1 | yyyxxx | 1
+ (1 row)
+
+ set session authorization regress_view_user1;
+ grant select (a,b) on base_tbl to regress_view_user2;
+ grant insert (a,b) on base_tbl to regress_view_user2;
+ grant update (a,b) on base_tbl to regress_view_user2;
+ set session authorization regress_view_user2;
+ create view rw_view2 as select b as bb, c as cc, a as aa from base_tbl;
+ insert into rw_view2 (aa,bb) values (1,'xxx')
+   on conflict (aa) do update set bb = excluded.bb; -- Not allowed
+ ERROR:  permission denied for table base_tbl
+ create view rw_view3 as select b as bb, a as aa from base_tbl;
+ insert into rw_view3 (aa,bb) values (1,'xxx')
+   on conflict (aa) do update set bb = excluded.bb; -- OK
+ reset session authorization;
+ select * from base_tbl;
+  a |  b  | c
+ ---+-----+---
+  1 | xxx | 1
+ (1 row)
+
+ set session authorization regress_view_user2;
+ create view rw_view4 as select aa, bb, cc FROM rw_view1;
+ insert into rw_view4 (aa,bb) values (1,'yyy')
+   on conflict (aa) do update set bb = excluded.bb; -- Not allowed
+ ERROR:  permission denied for view rw_view1
+ create view rw_view5 as select aa, bb FROM rw_view1;
+ insert into rw_view5 (aa,bb) values (1,'yyy')
+   on conflict (aa) do update set bb = excluded.bb; -- OK
+ reset session authorization;
+ select * from base_tbl;
+  a |  b  | c
+ ---+-----+---
+  1 | yyy | 1
+ (1 row)
+
+ drop view rw_view5;
+ drop view rw_view4;
+ drop view rw_view3;
+ drop view rw_view2;
+ drop view rw_view1;
+ drop table base_tbl;
+ drop user regress_view_user1;
+ drop user regress_view_user2;
diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql
index a7786b2..dc6d5cb 100644
*** a/src/test/regress/sql/updatable_views.sql
--- b/src/test/regress/sql/updatable_views.sql
*************** insert into wcowrtest_v2 values (2, 'no
*** 1244,1246 ****
--- 1244,1381 ----

  drop view wcowrtest_v, wcowrtest_v2;
  drop table wcowrtest, sometable;
+
+ -- Check INSERT .. ON CONFLICT DO UPDATE works correctly when the view's
+ -- columns are named and ordered differently than the underlying table's.
+ create table uv_iocu_tab (a text unique, b float);
+ insert into uv_iocu_tab values ('xyxyxy', 0);
+ create view uv_iocu_view as
+    select b, b+1 as c, a, '2.0'::text as two from uv_iocu_tab;
+
+ insert into uv_iocu_view (a, b) values ('xyxyxy', 1)
+    on conflict (a) do update set b = uv_iocu_view.b;
+ select * from uv_iocu_tab;
+ insert into uv_iocu_view (a, b) values ('xyxyxy', 1)
+    on conflict (a) do update set b = excluded.b;
+ select * from uv_iocu_tab;
+
+ -- OK to access view columns that are not present in underlying base
+ -- relation in the ON CONFLICT portion of the query
+ insert into uv_iocu_view (a, b) values ('xyxyxy', 3)
+    on conflict (a) do update set b = cast(excluded.two as float);
+ select * from uv_iocu_tab;
+
+ explain (costs off)
+ insert into uv_iocu_view (a, b) values ('xyxyxy', 3)
+    on conflict (a) do update set b = excluded.b where excluded.c > 0;
+
+ insert into uv_iocu_view (a, b) values ('xyxyxy', 3)
+    on conflict (a) do update set b = excluded.b where excluded.c > 0;
+ select * from uv_iocu_tab;
+
+ drop view uv_iocu_view;
+ drop table uv_iocu_tab;
+
+ -- Test whole-row references to the view
+ create table uv_iocu_tab (a int unique, b text);
+ create view uv_iocu_view as
+     select b as bb, a as aa, uv_iocu_tab::text as cc from uv_iocu_tab;
+
+ insert into uv_iocu_view (aa,bb) values (1,'x');
+ explain (costs off)
+ insert into uv_iocu_view (aa,bb) values (1,'y')
+    on conflict (aa) do update set bb = 'Rejected: '||excluded.*
+    where excluded.aa > 0
+    and excluded.bb != ''
+    and excluded.cc is not null;
+ insert into uv_iocu_view (aa,bb) values (1,'y')
+    on conflict (aa) do update set bb = 'Rejected: '||excluded.*
+    where excluded.aa > 0
+    and excluded.bb != ''
+    and excluded.cc is not null;
+ select * from uv_iocu_view;
+
+ -- Test omitting a column of the base relation
+ delete from uv_iocu_view;
+ insert into uv_iocu_view (aa,bb) values (1,'x');
+ insert into uv_iocu_view (aa) values (1)
+    on conflict (aa) do update set bb = 'Rejected: '||excluded.*;
+ select * from uv_iocu_view;
+
+ alter table uv_iocu_tab alter column b set default 'table default';
+ insert into uv_iocu_view (aa) values (1)
+    on conflict (aa) do update set bb = 'Rejected: '||excluded.*;
+ select * from uv_iocu_view;
+
+ alter view uv_iocu_view alter column bb set default 'view default';
+ insert into uv_iocu_view (aa) values (1)
+    on conflict (aa) do update set bb = 'Rejected: '||excluded.*;
+ select * from uv_iocu_view;
+
+ -- Should fail to update non-updatable columns
+ insert into uv_iocu_view (aa) values (1)
+    on conflict (aa) do update set cc = 'XXX';
+
+ drop view uv_iocu_view;
+ drop table uv_iocu_tab;
+
+ -- ON CONFLICT DO UPDATE permissions checks
+ create user regress_view_user1;
+ create user regress_view_user2;
+
+ set session authorization regress_view_user1;
+ create table base_tbl(a int unique, b text, c float);
+ insert into base_tbl values (1,'xxx',1.0);
+ create view rw_view1 as select b as bb, c as cc, a as aa from base_tbl;
+
+ grant select (aa,bb) on rw_view1 to regress_view_user2;
+ grant insert on rw_view1 to regress_view_user2;
+ grant update (bb) on rw_view1 to regress_view_user2;
+
+ set session authorization regress_view_user2;
+ insert into rw_view1 values ('yyy',2.0,1)
+   on conflict (aa) do update set bb = excluded.cc; -- Not allowed
+ insert into rw_view1 values ('yyy',2.0,1)
+   on conflict (aa) do update set bb = rw_view1.cc; -- Not allowed
+ insert into rw_view1 values ('yyy',2.0,1)
+   on conflict (aa) do update set bb = excluded.bb; -- OK
+ insert into rw_view1 values ('zzz',2.0,1)
+   on conflict (aa) do update set bb = rw_view1.bb||'xxx'; -- OK
+ insert into rw_view1 values ('zzz',2.0,1)
+   on conflict (aa) do update set cc = 3.0; -- Not allowed
+ reset session authorization;
+ select * from base_tbl;
+
+ set session authorization regress_view_user1;
+ grant select (a,b) on base_tbl to regress_view_user2;
+ grant insert (a,b) on base_tbl to regress_view_user2;
+ grant update (a,b) on base_tbl to regress_view_user2;
+
+ set session authorization regress_view_user2;
+ create view rw_view2 as select b as bb, c as cc, a as aa from base_tbl;
+ insert into rw_view2 (aa,bb) values (1,'xxx')
+   on conflict (aa) do update set bb = excluded.bb; -- Not allowed
+ create view rw_view3 as select b as bb, a as aa from base_tbl;
+ insert into rw_view3 (aa,bb) values (1,'xxx')
+   on conflict (aa) do update set bb = excluded.bb; -- OK
+ reset session authorization;
+ select * from base_tbl;
+
+ set session authorization regress_view_user2;
+ create view rw_view4 as select aa, bb, cc FROM rw_view1;
+ insert into rw_view4 (aa,bb) values (1,'yyy')
+   on conflict (aa) do update set bb = excluded.bb; -- Not allowed
+ create view rw_view5 as select aa, bb FROM rw_view1;
+ insert into rw_view5 (aa,bb) values (1,'yyy')
+   on conflict (aa) do update set bb = excluded.bb; -- OK
+ reset session authorization;
+ select * from base_tbl;
+
+ drop view rw_view5;
+ drop view rw_view4;
+ drop view rw_view3;
+ drop view rw_view2;
+ drop view rw_view1;
+ drop table base_tbl;
+ drop user regress_view_user1;
+ drop user regress_view_user2;