diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c new file mode 100644 index 0338e4e..f61fd25 --- 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); static void markQueryForLocking(Query *qry, Node *jtnode, LockClauseStrength strength, LockWaitPolicy waitPolicy, bool pushedDown); @@ -1223,12 +1223,19 @@ searchForDefault(RangeTblEntry *rte) * 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 (can happen for an auto-updatable view, allowing the + * defaults of the underlying base relation to be applied). */ -static void -rewriteValuesRTE(RangeTblEntry *rte, Relation target_relation, List *attrnos) +static bool +rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte, + Relation target_relation, List *attrnos) { List *newValues; ListCell *lc; + bool isAutoUpdatableView; + bool allReplaced; /* * Rebuilding all the lists is a pretty expensive proposition in a big @@ -1236,12 +1243,60 @@ rewriteValuesRTE(RangeTblEntry *rte, Rel * placeholders. So first scan to see if there are any. */ if (!searchForDefault(rte)) - return; /* nothing to do */ + 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))); + /* + * If we see a DEFAULT placeholder in the VALUES list for a column that + * has no default, we explicitly set the column to NULL except if the + * target relation is an auto-updatable view, in which case we leave the + * value untouched so that any default on the underlying base relation + * will be applied instead. + * + * This exception only applies to auto-updatable views --- for rule- and + * trigger-updatable views, it is the rule/trigger's responsibility to + * work out whether to insert default values into the base relation, and + * we must explicitly set the column to NULL, as for tables. + */ + isAutoUpdatableView = false; + if (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); @@ -1268,10 +1323,19 @@ rewriteValuesRTE(RangeTblEntry *rte, Rel /* * 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 +1360,76 @@ rewriteValuesRTE(RangeTblEntry *rte, Rel newValues = lappend(newValues, newList); } rte->values_lists = newValues; + + return allReplaced; +} + + +/* + * For an INSERT ... VALUES with a VALUES RTE (ie, multiple VALUES lists), + * replace any remaining DEFAULT items in the VALUES lists with NULLs. + * + * This is used after rewriteValuesRTE(), if it left any DEFAULT items + * untouched (because the target relation was an auto-updatable view) and the + * target relation is no longer an auto-updatable view. This applies to the + * product queries when an automatically updatable view has DO ALSO rules + * attached -- the original query is turned into an insert on the base + * relation, using that relation to replace any remaining defaults, but any + * product queries refer to the rule action, so any remaining defaults there + * must be set to NULL. + */ +static void +rewriteValuesRTEFinal(RangeTblEntry *rte, Relation target_relation, + List *attrnos) +{ + List *newValues; + ListCell *lc; + + /* Check list lengths (we can assume all the VALUES sublists are alike) */ + Assert(list_length(attrnos) == list_length(linitial(rte->values_lists))); + + newValues = NIL; + foreach(lc, rte->values_lists) + { + List *sublist = (List *) lfirst(lc); + List *newList = NIL; + ListCell *lc2; + ListCell *lc3; + + forboth(lc2, sublist, lc3, attrnos) + { + Node *col = (Node *) lfirst(lc2); + int attrno = lfirst_int(lc3); + + if (IsA(col, SetToDefault)) + { + Form_pg_attribute att_tup; + Node *new_expr; + + att_tup = TupleDescAttr(target_relation->rd_att, attrno - 1); + new_expr = (Node *) makeConst(att_tup->atttypid, + -1, + att_tup->attcollation, + att_tup->attlen, + (Datum) 0, + true, /* isnull */ + att_tup->attbyval); + /* this is to catch a NOT NULL domain constraint */ + new_expr = coerce_to_domain(new_expr, + InvalidOid, -1, + att_tup->atttypid, + COERCION_IMPLICIT, + COERCE_IMPLICIT_CAST, + -1, + false); + newList = lappend(newList, new_expr); + } + else + newList = lappend(newList, col); + } + newValues = lappend(newValues, newList); + } + rte->values_lists = newValues; } @@ -3383,6 +3517,9 @@ RewriteQuery(Query *parsetree, List *rew List *locks; List *product_queries; bool hasUpdate = false; + List *attrnos = NIL; + int values_rte_index = 0; + bool finalize_values_rte = false; result_relation = parsetree->resultRelation; Assert(result_relation != 0); @@ -3416,14 +3553,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 +3570,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)) + finalize_values_rte = true; } else { @@ -3488,6 +3628,32 @@ 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 (finalize_values_rte && 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); + + rewriteValuesRTEFinal(values_rte, rt_entry_relation, attrnos); + } + } + + /* * 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;