diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c new file mode 100644 index 0338e4e..65dd317 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -73,8 +73,8 @@ static TargetEntry *process_matched_tle( TargetEntry *prior_tle, const char *attrName); static Node *get_assignment_input(Node *node); -static void rewriteValuesRTE(RangeTblEntry *rte, Relation target_relation, - List *attrnos); +static bool rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte, + Relation target_relation, List *attrnos, bool force_nulls); static void markQueryForLocking(Query *qry, Node *jtnode, LockClauseStrength strength, LockWaitPolicy waitPolicy, bool pushedDown); @@ -1219,29 +1219,102 @@ searchForDefault(RangeTblEntry *rte) * the appropriate default expressions. The other aspects of targetlist * rewriting need be applied only to the query's targetlist proper. * + * For an auto-updatable view, each DEFAULT item in the VALUES list is + * replaced with the default from the view, if it has one. Otherwise it is + * left untouched so that the underlying base relation's default can be + * applied instead (when we later recurse to here after rewriting the query + * to refer to the base relation instead of the view). + * + * For other types of relation, including rule- and trigger-updatable views, + * all DEFAULT items are replaced, and if the target relation doesn't have a + * default, the value is explicitly set to NULL. + * + * Additionally, if force_nulls is true, the target relation's defaults are + * ignored and all DEFAULT items in the VALUES list are explicitly set to + * NULL, regardless of the target relation's type. This is used for the + * product queries generated by DO ALSO rules attached to an auto-updatable + * view, for which we will have already called this function with force_nulls + * false. For these product queries, we must then force any remaining DEFAULT + * items to NULL to provide concrete values for the rule actions. + * Essentially, this is a mix of the 2 cases above --- the original query is + * an insert into an auto-updatable view, and the product queries are inserts + * into a rule-updatable view. + * * Note that we currently can't support subscripted or field assignment * in the multi-VALUES case. The targetlist will contain simple Vars * referencing the VALUES RTE, and therefore process_matched_tle() will * reject any such attempt with "multiple assignments to same column". + * + * Returns true if all DEFAULT items were replaced, and false if some were + * left untouched. */ -static void -rewriteValuesRTE(RangeTblEntry *rte, Relation target_relation, List *attrnos) +static bool +rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte, + Relation target_relation, List *attrnos, bool force_nulls) { List *newValues; ListCell *lc; + bool isAutoUpdatableView; + bool allReplaced; /* * Rebuilding all the lists is a pretty expensive proposition in a big * VALUES list, and it's a waste of time if there aren't any DEFAULT * placeholders. So first scan to see if there are any. + * + * We skip this check if force_nulls is true, because we know that there + * are DEFAULT items present in that case. */ - if (!searchForDefault(rte)) - return; /* nothing to do */ + if (!force_nulls && !searchForDefault(rte)) + return true; /* nothing to do */ /* Check list lengths (we can assume all the VALUES sublists are alike) */ Assert(list_length(attrnos) == list_length(linitial(rte->values_lists))); + /* + * Check if the target relation is an auto-updatable view, in which case + * unresolved defaults will be left untouched rather than being set to + * NULL. If force_nulls is true, we always set DEFAULT items to NULL, so + * skip this check in that case --- it isn't an auto-updatable view. + */ + isAutoUpdatableView = false; + if (!force_nulls && + target_relation->rd_rel->relkind == RELKIND_VIEW && + !view_has_instead_trigger(target_relation, CMD_INSERT)) + { + List *locks; + bool hasUpdate; + bool found; + ListCell *l; + + /* Look for an unconditional DO INSTEAD rule */ + locks = matchLocks(CMD_INSERT, target_relation->rd_rules, + parsetree->resultRelation, parsetree, &hasUpdate); + + found = false; + foreach(l, locks) + { + RewriteRule *rule_lock = (RewriteRule *) lfirst(l); + + if (rule_lock->isInstead && + rule_lock->qual == NULL) + { + found = true; + break; + } + } + + /* + * If we didn't find an unconditional DO INSTEAD rule, assume that the + * view is auto-updatable. If it isn't, rewriteTargetView() will + * throw an error. + */ + if (!found) + isAutoUpdatableView = true; + } + newValues = NIL; + allReplaced = true; foreach(lc, rte->values_lists) { List *sublist = (List *) lfirst(lc); @@ -1261,17 +1334,26 @@ rewriteValuesRTE(RangeTblEntry *rte, Rel att_tup = TupleDescAttr(target_relation->rd_att, attrno - 1); - if (!att_tup->attisdropped) + if (!force_nulls && !att_tup->attisdropped) new_expr = build_column_default(target_relation, attrno); else new_expr = NULL; /* force a NULL if dropped */ /* * If there is no default (ie, default is effectively NULL), - * we've got to explicitly set the column to NULL. + * we've got to explicitly set the column to NULL, unless the + * target relation is an auto-updatable view. */ if (!new_expr) { + if (isAutoUpdatableView) + { + /* Leave the value untouched */ + newList = lappend(newList, col); + allReplaced = false; + continue; + } + new_expr = (Node *) makeConst(att_tup->atttypid, -1, att_tup->attcollation, @@ -1296,6 +1378,8 @@ rewriteValuesRTE(RangeTblEntry *rte, Rel newValues = lappend(newValues, newList); } rte->values_lists = newValues; + + return allReplaced; } @@ -3383,6 +3467,9 @@ RewriteQuery(Query *parsetree, List *rew List *locks; List *product_queries; bool hasUpdate = false; + List *attrnos = NIL; + int values_rte_index = 0; + bool defaults_remaining = false; result_relation = parsetree->resultRelation; Assert(result_relation != 0); @@ -3416,14 +3503,15 @@ RewriteQuery(Query *parsetree, List *rew parsetree->rtable); if (rte->rtekind == RTE_VALUES) + { values_rte = rte; + values_rte_index = rtr->rtindex; + } } } if (values_rte) { - List *attrnos; - /* Process the main targetlist ... */ parsetree->targetList = rewriteTargetListIU(parsetree->targetList, parsetree->commandType, @@ -3432,7 +3520,9 @@ RewriteQuery(Query *parsetree, List *rew parsetree->resultRelation, &attrnos); /* ... and the VALUES expression lists */ - rewriteValuesRTE(values_rte, rt_entry_relation, attrnos); + if (!rewriteValuesRTE(parsetree, values_rte, + rt_entry_relation, attrnos, false)) + defaults_remaining = true; } else { @@ -3488,6 +3578,33 @@ RewriteQuery(Query *parsetree, List *rew &qual_product); /* + * If we have a VALUES RTE with any remaining untouched DEFAULT items, + * and we got any product queries, finalize the VALUES RTE for each + * product query (replacing the remaining DEFAULT items with NULLs). + * We don't do this for the original query, because we know that it + * must be an auto-insert on a view, and so should use the base + * relation's defaults for any remaining DEFAULT items. + */ + if (defaults_remaining && product_queries != NIL) + { + ListCell *n; + + /* + * Each product query has its own copy of the VALUES RTE at the + * same index in the rangetable, so we must finalize each one. + */ + foreach(n, product_queries) + { + Query *pt = (Query *) lfirst(n); + RangeTblEntry *values_rte = rt_fetch(values_rte_index, + pt->rtable); + + rewriteValuesRTE(pt, values_rte, rt_entry_relation, attrnos, + true); /* Force defaults to NULL */ + } + } + + /* * If there were no INSTEAD rules, and the target relation is a view * without any INSTEAD OF triggers, see if the view can be * automatically updated. If so, we perform the necessary query diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out new file mode 100644 index 420c5a5..ed07c1a --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -2772,3 +2772,156 @@ drop view rw_view1; drop table base_tbl; drop user regress_view_user1; drop user regress_view_user2; +-- Test single- and multi-row inserts with table and view defaults. +-- Table defaults should be used, unless overridden by view defaults. +create table base_tab_def (a int, b text default 'Table default', + c text default 'Table default', d text, e text); +create view base_tab_def_view as select * from base_tab_def; +alter view base_tab_def_view alter b set default 'View default'; +alter view base_tab_def_view alter d set default 'View default'; +insert into base_tab_def values (1); +insert into base_tab_def values (2), (3); +insert into base_tab_def values (4, default, default, default, default); +insert into base_tab_def values (5, default, default, default, default), + (6, default, default, default, default); +insert into base_tab_def_view values (11); +insert into base_tab_def_view values (12), (13); +insert into base_tab_def_view values (14, default, default, default, default); +insert into base_tab_def_view values (15, default, default, default, default), + (16, default, default, default, default); +select * from base_tab_def order by a; + a | b | c | d | e +----+---------------+---------------+--------------+--- + 1 | Table default | Table default | | + 2 | Table default | Table default | | + 3 | Table default | Table default | | + 4 | Table default | Table default | | + 5 | Table default | Table default | | + 6 | Table default | Table default | | + 11 | View default | Table default | View default | + 12 | View default | Table default | View default | + 13 | View default | Table default | View default | + 14 | View default | Table default | View default | + 15 | View default | Table default | View default | + 16 | View default | Table default | View default | +(12 rows) + +-- Adding an INSTEAD OF trigger should cause NULLs to be inserted instead of +-- table defaults, where there are no view defaults. +create function base_tab_def_view_instrig_func() returns trigger +as +$$ +begin + insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e); + return new; +end; +$$ +language plpgsql; +create trigger base_tab_def_view_instrig instead of insert on base_tab_def_view + for each row execute function base_tab_def_view_instrig_func(); +truncate base_tab_def; +insert into base_tab_def values (1); +insert into base_tab_def values (2), (3); +insert into base_tab_def values (4, default, default, default, default); +insert into base_tab_def values (5, default, default, default, default), + (6, default, default, default, default); +insert into base_tab_def_view values (11); +insert into base_tab_def_view values (12), (13); +insert into base_tab_def_view values (14, default, default, default, default); +insert into base_tab_def_view values (15, default, default, default, default), + (16, default, default, default, default); +select * from base_tab_def order by a; + a | b | c | d | e +----+---------------+---------------+--------------+--- + 1 | Table default | Table default | | + 2 | Table default | Table default | | + 3 | Table default | Table default | | + 4 | Table default | Table default | | + 5 | Table default | Table default | | + 6 | Table default | Table default | | + 11 | View default | | View default | + 12 | View default | | View default | + 13 | View default | | View default | + 14 | View default | | View default | + 15 | View default | | View default | + 16 | View default | | View default | +(12 rows) + +-- Using an unconditional DO INSTEAD rule should also cause NULLs to be +-- inserted where there are no view defaults. +drop trigger base_tab_def_view_instrig on base_tab_def_view; +drop function base_tab_def_view_instrig_func; +create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view + do instead insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e); +truncate base_tab_def; +insert into base_tab_def values (1); +insert into base_tab_def values (2), (3); +insert into base_tab_def values (4, default, default, default, default); +insert into base_tab_def values (5, default, default, default, default), + (6, default, default, default, default); +insert into base_tab_def_view values (11); +insert into base_tab_def_view values (12), (13); +insert into base_tab_def_view values (14, default, default, default, default); +insert into base_tab_def_view values (15, default, default, default, default), + (16, default, default, default, default); +select * from base_tab_def order by a; + a | b | c | d | e +----+---------------+---------------+--------------+--- + 1 | Table default | Table default | | + 2 | Table default | Table default | | + 3 | Table default | Table default | | + 4 | Table default | Table default | | + 5 | Table default | Table default | | + 6 | Table default | Table default | | + 11 | View default | | View default | + 12 | View default | | View default | + 13 | View default | | View default | + 14 | View default | | View default | + 15 | View default | | View default | + 16 | View default | | View default | +(12 rows) + +-- A DO ALSO rule should cause each row to be inserted twice. The first +-- insert should behave the same as an auto-updatable view (using table +-- defaults, unless overridden by view defaults). The second insert should +-- behave the same as a rule-updatable view (inserting NULLs where there are +-- no view defaults). +drop rule base_tab_def_view_ins_rule on base_tab_def_view; +create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view + do also insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e); +truncate base_tab_def; +insert into base_tab_def values (1); +insert into base_tab_def values (2), (3); +insert into base_tab_def values (4, default, default, default, default); +insert into base_tab_def values (5, default, default, default, default), + (6, default, default, default, default); +insert into base_tab_def_view values (11); +insert into base_tab_def_view values (12), (13); +insert into base_tab_def_view values (14, default, default, default, default); +insert into base_tab_def_view values (15, default, default, default, default), + (16, default, default, default, default); +select * from base_tab_def order by a, c NULLS LAST; + a | b | c | d | e +----+---------------+---------------+--------------+--- + 1 | Table default | Table default | | + 2 | Table default | Table default | | + 3 | Table default | Table default | | + 4 | Table default | Table default | | + 5 | Table default | Table default | | + 6 | Table default | Table default | | + 11 | View default | Table default | View default | + 11 | View default | | View default | + 12 | View default | Table default | View default | + 12 | View default | | View default | + 13 | View default | Table default | View default | + 13 | View default | | View default | + 14 | View default | Table default | View default | + 14 | View default | | View default | + 15 | View default | Table default | View default | + 15 | View default | | View default | + 16 | View default | Table default | View default | + 16 | View default | | View default | +(18 rows) + +drop view base_tab_def_view; +drop table base_tab_def; diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql new file mode 100644 index dc6d5cb..56d4c19 --- a/src/test/regress/sql/updatable_views.sql +++ b/src/test/regress/sql/updatable_views.sql @@ -1379,3 +1379,91 @@ drop view rw_view1; drop table base_tbl; drop user regress_view_user1; drop user regress_view_user2; + +-- Test single- and multi-row inserts with table and view defaults. +-- Table defaults should be used, unless overridden by view defaults. +create table base_tab_def (a int, b text default 'Table default', + c text default 'Table default', d text, e text); +create view base_tab_def_view as select * from base_tab_def; +alter view base_tab_def_view alter b set default 'View default'; +alter view base_tab_def_view alter d set default 'View default'; +insert into base_tab_def values (1); +insert into base_tab_def values (2), (3); +insert into base_tab_def values (4, default, default, default, default); +insert into base_tab_def values (5, default, default, default, default), + (6, default, default, default, default); +insert into base_tab_def_view values (11); +insert into base_tab_def_view values (12), (13); +insert into base_tab_def_view values (14, default, default, default, default); +insert into base_tab_def_view values (15, default, default, default, default), + (16, default, default, default, default); +select * from base_tab_def order by a; + +-- Adding an INSTEAD OF trigger should cause NULLs to be inserted instead of +-- table defaults, where there are no view defaults. +create function base_tab_def_view_instrig_func() returns trigger +as +$$ +begin + insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e); + return new; +end; +$$ +language plpgsql; +create trigger base_tab_def_view_instrig instead of insert on base_tab_def_view + for each row execute function base_tab_def_view_instrig_func(); +truncate base_tab_def; +insert into base_tab_def values (1); +insert into base_tab_def values (2), (3); +insert into base_tab_def values (4, default, default, default, default); +insert into base_tab_def values (5, default, default, default, default), + (6, default, default, default, default); +insert into base_tab_def_view values (11); +insert into base_tab_def_view values (12), (13); +insert into base_tab_def_view values (14, default, default, default, default); +insert into base_tab_def_view values (15, default, default, default, default), + (16, default, default, default, default); +select * from base_tab_def order by a; + +-- Using an unconditional DO INSTEAD rule should also cause NULLs to be +-- inserted where there are no view defaults. +drop trigger base_tab_def_view_instrig on base_tab_def_view; +drop function base_tab_def_view_instrig_func; +create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view + do instead insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e); +truncate base_tab_def; +insert into base_tab_def values (1); +insert into base_tab_def values (2), (3); +insert into base_tab_def values (4, default, default, default, default); +insert into base_tab_def values (5, default, default, default, default), + (6, default, default, default, default); +insert into base_tab_def_view values (11); +insert into base_tab_def_view values (12), (13); +insert into base_tab_def_view values (14, default, default, default, default); +insert into base_tab_def_view values (15, default, default, default, default), + (16, default, default, default, default); +select * from base_tab_def order by a; + +-- A DO ALSO rule should cause each row to be inserted twice. The first +-- insert should behave the same as an auto-updatable view (using table +-- defaults, unless overridden by view defaults). The second insert should +-- behave the same as a rule-updatable view (inserting NULLs where there are +-- no view defaults). +drop rule base_tab_def_view_ins_rule on base_tab_def_view; +create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view + do also insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e); +truncate base_tab_def; +insert into base_tab_def values (1); +insert into base_tab_def values (2), (3); +insert into base_tab_def values (4, default, default, default, default); +insert into base_tab_def values (5, default, default, default, default), + (6, default, default, default, default); +insert into base_tab_def_view values (11); +insert into base_tab_def_view values (12), (13); +insert into base_tab_def_view values (14, default, default, default, default); +insert into base_tab_def_view values (15, default, default, default, default), + (16, default, default, default, default); +select * from base_tab_def order by a, c NULLS LAST; + +drop view base_tab_def_view; +drop table base_tab_def;