diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c new file mode 100644 index 05f5759..87e084b --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -1022,9 +1022,6 @@ transformOnConflictClause(ParseState *ps 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 @@ -1043,56 +1040,8 @@ transformOnConflictClause(ParseState *ps 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); + exclRelTlist = BuildOnConflictExcludedTargetlist(targetrel, + exclRelIndex); /* * Add EXCLUDED and the target RTE to the namespace, so that they can @@ -1124,6 +1073,75 @@ transformOnConflictClause(ParseState *ps return result; } + + +/* + * BuildOnConflictExcludedTargetlist + * Create the target list of EXCLUDED pseudo-relation of ON CONFLICT + * + * Note: Exported for use in the rewriter. + */ +List * +BuildOnConflictExcludedTargetlist(Relation targetrel, + Index exclRelIndex) +{ + List *result = NIL; + int attno; + Var *var; + TargetEntry *te; + + /* + * 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 */ + result = lappend(result, 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); + result = lappend(result, te); + + return result; +} /* diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c new file mode 100644 index 5b87c55..345d69b --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -29,6 +29,7 @@ #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" @@ -2875,8 +2876,6 @@ rewriteTargetView(Query *parsetree, Rela */ 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 @@ -3035,6 +3034,81 @@ rewriteTargetView(Query *parsetree, Rela } /* + * Similarly for INSERT .. ON CONFLICT .. DO UPDATE, update the resnos in + * the auxiliary UPDATE's targetlist to refer to columns of the base + * relation. + */ + if (parsetree->onConflict && + parsetree->onConflict->action == ONCONFLICT_UPDATE) + { + Index old_exclRelIndex, + new_exclRelIndex; + RangeTblEntry *new_exclRte; + List *tmp_tlist; + + 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); + } + + /* + * Create a new RTE for the EXCLUDED pseudo-relation using base_rel. + * This closely mimics the code in transformOnConflictClause. + */ + old_exclRelIndex = parsetree->onConflict->exclRelIndex; + + new_exclRte = addRangeTableEntryForRelation(make_parsestate(NULL), + base_rel, + makeAlias("excluded", + NIL), + false, false); + new_exclRte->relkind = RELKIND_COMPOSITE_TYPE; + parsetree->rtable = lappend(parsetree->rtable, new_exclRte); + new_exclRelIndex = parsetree->onConflict->exclRelIndex = + list_length(parsetree->rtable); + + /* + * Replace the targetlist for the old EXCLUDED pseudo-relation with a + * new one using the columns from the 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 using the column mappings defined in the + * view targetlist. We do this using a modified copy of the view + * targetlist, that refers to the new EXCLUDED pseudo-relation rather + * than the new target RTE. + */ + 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 @@ -3161,6 +3235,8 @@ rewriteTargetView(Query *parsetree, Rela } } + heap_close(base_rel, NoLock); + return parsetree; } diff --git a/src/include/parser/analyze.h b/src/include/parser/analyze.h new file mode 100644 index 687ae1b..7b5b90c --- a/src/include/parser/analyze.h +++ b/src/include/parser/analyze.h @@ -43,4 +43,7 @@ extern void applyLockingClause(Query *qr 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 new file mode 100644 index b34bab4..4726c67 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -2578,3 +2578,84 @@ ERROR: new row violates check option fo 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', 1); +create view uv_iocu_view as select b, b+1 as c, a from uv_iocu_tab; +insert into uv_iocu_view (a, b) values ('xyxyxy', 2) + on conflict (a) do update set b = uv_iocu_view.b; +-- OK to access view columns that are not present in underlying base +-- relation in the ON CONFLICT portion of the query +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; +-- should display 'xyxyxy, 3' +select * from uv_iocu_tab; + a | b +--------+--- + xyxyxy | 3 +(1 row) + +drop view uv_iocu_view; +drop table uv_iocu_tab; +-- Example with 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 omiting 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) + +-- 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; diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql new file mode 100644 index a7786b2..6e92f33 --- a/src/test/regress/sql/updatable_views.sql +++ b/src/test/regress/sql/updatable_views.sql @@ -1244,3 +1244,57 @@ insert into wcowrtest_v2 values (2, 'no 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', 1); +create view uv_iocu_view as select b, b+1 as c, a from uv_iocu_tab; +insert into uv_iocu_view (a, b) values ('xyxyxy', 2) + on conflict (a) do update set b = uv_iocu_view.b; + +-- OK to access view columns that are not present in underlying base +-- relation in the ON CONFLICT portion of the query +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; +-- should display 'xyxyxy, 3' +select * from uv_iocu_tab; +drop view uv_iocu_view; +drop table uv_iocu_tab; + +-- Example with 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 omiting 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; + +-- 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;