Skip to content

Commit 2b1971c

Browse files
deanrasheeddeanrasheed
committed
Fix DEFAULT-handling in multi-row VALUES lists for updatable views.
INSERT ... VALUES for a single VALUES row is implemented differently from a multi-row VALUES list, which causes inconsistent behaviour in the way that DEFAULT items are handled. In particular, when inserting into an auto-updatable view on top of a table with a column default, a DEFAULT item in a single VALUES row gets correctly replaced with the table column's default, but for a multi-row VALUES list it is replaced with NULL. Fix this by allowing rewriteValuesRTE() to leave DEFAULT items in the VALUES list untouched if the target relation is an auto-updatable view and has no column default, deferring DEFAULT-expansion until the query against the base relation is rewritten. For all other types of target relation, including tables and trigger- and rule-updatable views, we must continue to replace DEFAULT items with NULL in the absence of a column default. This is somewhat complicated by the fact that if an auto-updatable view has DO ALSO rules attached, the VALUES lists for the product queries need to be handled differently from the original query, since the product queries need to act like rule-updatable views whereas the original query has auto-updatable view semantics. Back-patch to all supported versions. Reported by Roger Curley (bug #15623). Patch by Amit Langote and me. Discussion: https://postgr.es/m/15623-5d67a46788ec8b7f@postgresql.org
1 parent a80f5c2 commit 2b1971c

File tree

3 files changed

+369
-11
lines changed

3 files changed

+369
-11
lines changed

‎src/backend/rewrite/rewriteHandler.c

Lines changed: 128 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -71,8 +71,8 @@ static TargetEntry *process_matched_tle(TargetEntry *src_tle,
7171
TargetEntry *prior_tle,
7272
const char *attrName);
7373
static Node *get_assignment_input(Node *node);
74-
static void rewriteValuesRTE(RangeTblEntry *rte, Relation target_relation,
75-
List *attrnos);
74+
static bool rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte,
75+
Relation target_relation, List *attrnos, bool force_nulls);
7676
static void markQueryForLocking(Query *qry, Node *jtnode,
7777
LockClauseStrength strength, LockWaitPolicy waitPolicy,
7878
bool pushedDown);
@@ -1213,29 +1213,102 @@ searchForDefault(RangeTblEntry *rte)
12131213
* the appropriate default expressions. The other aspects of targetlist
12141214
* rewriting need be applied only to the query's targetlist proper.
12151215
*
1216+
* For an auto-updatable view, each DEFAULT item in the VALUES list is
1217+
* replaced with the default from the view, if it has one. Otherwise it is
1218+
* left untouched so that the underlying base relation's default can be
1219+
* applied instead (when we later recurse to here after rewriting the query
1220+
* to refer to the base relation instead of the view).
1221+
*
1222+
* For other types of relation, including rule- and trigger-updatable views,
1223+
* all DEFAULT items are replaced, and if the target relation doesn't have a
1224+
* default, the value is explicitly set to NULL.
1225+
*
1226+
* Additionally, if force_nulls is true, the target relation's defaults are
1227+
* ignored and all DEFAULT items in the VALUES list are explicitly set to
1228+
* NULL, regardless of the target relation's type. This is used for the
1229+
* product queries generated by DO ALSO rules attached to an auto-updatable
1230+
* view, for which we will have already called this function with force_nulls
1231+
* false. For these product queries, we must then force any remaining DEFAULT
1232+
* items to NULL to provide concrete values for the rule actions.
1233+
* Essentially, this is a mix of the 2 cases above --- the original query is
1234+
* an insert into an auto-updatable view, and the product queries are inserts
1235+
* into a rule-updatable view.
1236+
*
12161237
* Note that we currently can't support subscripted or field assignment
12171238
* in the multi-VALUES case. The targetlist will contain simple Vars
12181239
* referencing the VALUES RTE, and therefore process_matched_tle() will
12191240
* reject any such attempt with "multiple assignments to same column".
1241+
*
1242+
* Returns true if all DEFAULT items were replaced, and false if some were
1243+
* left untouched.
12201244
*/
1221-
static void
1222-
rewriteValuesRTE(RangeTblEntry *rte, Relation target_relation, List *attrnos)
1245+
static bool
1246+
rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte,
1247+
Relation target_relation, List *attrnos, bool force_nulls)
12231248
{
12241249
List *newValues;
12251250
ListCell *lc;
1251+
bool isAutoUpdatableView;
1252+
bool allReplaced;
12261253

12271254
/*
12281255
* Rebuilding all the lists is a pretty expensive proposition in a big
12291256
* VALUES list, and it's a waste of time if there aren't any DEFAULT
12301257
* placeholders. So first scan to see if there are any.
1258+
*
1259+
* We skip this check if force_nulls is true, because we know that there
1260+
* are DEFAULT items present in that case.
12311261
*/
1232-
if (!searchForDefault(rte))
1233-
return; /* nothing to do */
1262+
if (!force_nulls && !searchForDefault(rte))
1263+
return true; /* nothing to do */
12341264

12351265
/* Check list lengths (we can assume all the VALUES sublists are alike) */
12361266
Assert(list_length(attrnos) == list_length(linitial(rte->values_lists)));
12371267

1268+
/*
1269+
* Check if the target relation is an auto-updatable view, in which case
1270+
* unresolved defaults will be left untouched rather than being set to
1271+
* NULL. If force_nulls is true, we always set DEFAULT items to NULL, so
1272+
* skip this check in that case --- it isn't an auto-updatable view.
1273+
*/
1274+
isAutoUpdatableView = false;
1275+
if (!force_nulls &&
1276+
target_relation->rd_rel->relkind == RELKIND_VIEW &&
1277+
!view_has_instead_trigger(target_relation, CMD_INSERT))
1278+
{
1279+
List *locks;
1280+
bool hasUpdate;
1281+
bool found;
1282+
ListCell *l;
1283+
1284+
/* Look for an unconditional DO INSTEAD rule */
1285+
locks = matchLocks(CMD_INSERT, target_relation->rd_rules,
1286+
parsetree->resultRelation, parsetree, &hasUpdate);
1287+
1288+
found = false;
1289+
foreach(l, locks)
1290+
{
1291+
RewriteRule *rule_lock = (RewriteRule *) lfirst(l);
1292+
1293+
if (rule_lock->isInstead &&
1294+
rule_lock->qual == NULL)
1295+
{
1296+
found = true;
1297+
break;
1298+
}
1299+
}
1300+
1301+
/*
1302+
* If we didn't find an unconditional DO INSTEAD rule, assume that the
1303+
* view is auto-updatable. If it isn't, rewriteTargetView() will
1304+
* throw an error.
1305+
*/
1306+
if (!found)
1307+
isAutoUpdatableView = true;
1308+
}
1309+
12381310
newValues = NIL;
1311+
allReplaced = true;
12391312
foreach(lc, rte->values_lists)
12401313
{
12411314
List *sublist = (List *) lfirst(lc);
@@ -1255,17 +1328,26 @@ rewriteValuesRTE(RangeTblEntry *rte, Relation target_relation, List *attrnos)
12551328

12561329
att_tup = target_relation->rd_att->attrs[attrno - 1];
12571330

1258-
if (!att_tup->attisdropped)
1331+
if (!force_nulls && !att_tup->attisdropped)
12591332
new_expr = build_column_default(target_relation, attrno);
12601333
else
12611334
new_expr = NULL; /* force a NULL if dropped */
12621335

12631336
/*
12641337
* If there is no default (ie, default is effectively NULL),
1265-
* we've got to explicitly set the column to NULL.
1338+
* we've got to explicitly set the column to NULL, unless the
1339+
* target relation is an auto-updatable view.
12661340
*/
12671341
if (!new_expr)
12681342
{
1343+
if (isAutoUpdatableView)
1344+
{
1345+
/* Leave the value untouched */
1346+
newList = lappend(newList, col);
1347+
allReplaced = false;
1348+
continue;
1349+
}
1350+
12691351
new_expr = (Node *) makeConst(att_tup->atttypid,
12701352
-1,
12711353
att_tup->attcollation,
@@ -1290,6 +1372,8 @@ rewriteValuesRTE(RangeTblEntry *rte, Relation target_relation, List *attrnos)
12901372
newValues = lappend(newValues, newList);
12911373
}
12921374
rte->values_lists = newValues;
1375+
1376+
return allReplaced;
12931377
}
12941378

12951379

@@ -3362,6 +3446,9 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
33623446
List *locks;
33633447
List *product_queries;
33643448
bool hasUpdate = false;
3449+
List *attrnos = NIL;
3450+
int values_rte_index = 0;
3451+
bool defaults_remaining = false;
33653452

33663453
result_relation = parsetree->resultRelation;
33673454
Assert(result_relation != 0);
@@ -3395,14 +3482,15 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
33953482
parsetree->rtable);
33963483

33973484
if (rte->rtekind == RTE_VALUES)
3485+
{
33983486
values_rte = rte;
3487+
values_rte_index = rtr->rtindex;
3488+
}
33993489
}
34003490
}
34013491

34023492
if (values_rte)
34033493
{
3404-
List *attrnos;
3405-
34063494
/* Process the main targetlist ... */
34073495
parsetree->targetList = rewriteTargetListIU(parsetree->targetList,
34083496
parsetree->commandType,
@@ -3411,7 +3499,9 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
34113499
parsetree->resultRelation,
34123500
&attrnos);
34133501
/* ... and the VALUES expression lists */
3414-
rewriteValuesRTE(values_rte, rt_entry_relation, attrnos);
3502+
if (!rewriteValuesRTE(parsetree, values_rte,
3503+
rt_entry_relation, attrnos, false))
3504+
defaults_remaining = true;
34153505
}
34163506
else
34173507
{
@@ -3466,6 +3556,33 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
34663556
&returning,
34673557
&qual_product);
34683558

3559+
/*
3560+
* If we have a VALUES RTE with any remaining untouched DEFAULT items,
3561+
* and we got any product queries, finalize the VALUES RTE for each
3562+
* product query (replacing the remaining DEFAULT items with NULLs).
3563+
* We don't do this for the original query, because we know that it
3564+
* must be an auto-insert on a view, and so should use the base
3565+
* relation's defaults for any remaining DEFAULT items.
3566+
*/
3567+
if (defaults_remaining && product_queries != NIL)
3568+
{
3569+
ListCell *n;
3570+
3571+
/*
3572+
* Each product query has its own copy of the VALUES RTE at the
3573+
* same index in the rangetable, so we must finalize each one.
3574+
*/
3575+
foreach(n, product_queries)
3576+
{
3577+
Query *pt = (Query *) lfirst(n);
3578+
RangeTblEntry *values_rte = rt_fetch(values_rte_index,
3579+
pt->rtable);
3580+
3581+
rewriteValuesRTE(pt, values_rte, rt_entry_relation, attrnos,
3582+
true); /* Force remaining defaults to NULL */
3583+
}
3584+
}
3585+
34693586
/*
34703587
* If there were no INSTEAD rules, and the target relation is a view
34713588
* without any INSTEAD OF triggers, see if the view can be

‎src/test/regress/expected/updatable_views.out

Lines changed: 153 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2648,3 +2648,156 @@ drop view rw_view1;
26482648
drop table base_tbl;
26492649
drop user regress_view_user1;
26502650
drop user regress_view_user2;
2651+
-- Test single- and multi-row inserts with table and view defaults.
2652+
-- Table defaults should be used, unless overridden by view defaults.
2653+
create table base_tab_def (a int, b text default 'Table default',
2654+
c text default 'Table default', d text, e text);
2655+
create view base_tab_def_view as select * from base_tab_def;
2656+
alter view base_tab_def_view alter b set default 'View default';
2657+
alter view base_tab_def_view alter d set default 'View default';
2658+
insert into base_tab_def values (1);
2659+
insert into base_tab_def values (2), (3);
2660+
insert into base_tab_def values (4, default, default, default, default);
2661+
insert into base_tab_def values (5, default, default, default, default),
2662+
(6, default, default, default, default);
2663+
insert into base_tab_def_view values (11);
2664+
insert into base_tab_def_view values (12), (13);
2665+
insert into base_tab_def_view values (14, default, default, default, default);
2666+
insert into base_tab_def_view values (15, default, default, default, default),
2667+
(16, default, default, default, default);
2668+
select * from base_tab_def order by a;
2669+
a | b | c | d | e
2670+
----+---------------+---------------+--------------+---
2671+
1 | Table default | Table default | |
2672+
2 | Table default | Table default | |
2673+
3 | Table default | Table default | |
2674+
4 | Table default | Table default | |
2675+
5 | Table default | Table default | |
2676+
6 | Table default | Table default | |
2677+
11 | View default | Table default | View default |
2678+
12 | View default | Table default | View default |
2679+
13 | View default | Table default | View default |
2680+
14 | View default | Table default | View default |
2681+
15 | View default | Table default | View default |
2682+
16 | View default | Table default | View default |
2683+
(12 rows)
2684+
2685+
-- Adding an INSTEAD OF trigger should cause NULLs to be inserted instead of
2686+
-- table defaults, where there are no view defaults.
2687+
create function base_tab_def_view_instrig_func() returns trigger
2688+
as
2689+
$$
2690+
begin
2691+
insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e);
2692+
return new;
2693+
end;
2694+
$$
2695+
language plpgsql;
2696+
create trigger base_tab_def_view_instrig instead of insert on base_tab_def_view
2697+
for each row execute procedure base_tab_def_view_instrig_func();
2698+
truncate base_tab_def;
2699+
insert into base_tab_def values (1);
2700+
insert into base_tab_def values (2), (3);
2701+
insert into base_tab_def values (4, default, default, default, default);
2702+
insert into base_tab_def values (5, default, default, default, default),
2703+
(6, default, default, default, default);
2704+
insert into base_tab_def_view values (11);
2705+
insert into base_tab_def_view values (12), (13);
2706+
insert into base_tab_def_view values (14, default, default, default, default);
2707+
insert into base_tab_def_view values (15, default, default, default, default),
2708+
(16, default, default, default, default);
2709+
select * from base_tab_def order by a;
2710+
a | b | c | d | e
2711+
----+---------------+---------------+--------------+---
2712+
1 | Table default | Table default | |
2713+
2 | Table default | Table default | |
2714+
3 | Table default | Table default | |
2715+
4 | Table default | Table default | |
2716+
5 | Table default | Table default | |
2717+
6 | Table default | Table default | |
2718+
11 | View default | | View default |
2719+
12 | View default | | View default |
2720+
13 | View default | | View default |
2721+
14 | View default | | View default |
2722+
15 | View default | | View default |
2723+
16 | View default | | View default |
2724+
(12 rows)
2725+
2726+
-- Using an unconditional DO INSTEAD rule should also cause NULLs to be
2727+
-- inserted where there are no view defaults.
2728+
drop trigger base_tab_def_view_instrig on base_tab_def_view;
2729+
drop function base_tab_def_view_instrig_func;
2730+
create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view
2731+
do instead insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e);
2732+
truncate base_tab_def;
2733+
insert into base_tab_def values (1);
2734+
insert into base_tab_def values (2), (3);
2735+
insert into base_tab_def values (4, default, default, default, default);
2736+
insert into base_tab_def values (5, default, default, default, default),
2737+
(6, default, default, default, default);
2738+
insert into base_tab_def_view values (11);
2739+
insert into base_tab_def_view values (12), (13);
2740+
insert into base_tab_def_view values (14, default, default, default, default);
2741+
insert into base_tab_def_view values (15, default, default, default, default),
2742+
(16, default, default, default, default);
2743+
select * from base_tab_def order by a;
2744+
a | b | c | d | e
2745+
----+---------------+---------------+--------------+---
2746+
1 | Table default | Table default | |
2747+
2 | Table default | Table default | |
2748+
3 | Table default | Table default | |
2749+
4 | Table default | Table default | |
2750+
5 | Table default | Table default | |
2751+
6 | Table default | Table default | |
2752+
11 | View default | | View default |
2753+
12 | View default | | View default |
2754+
13 | View default | | View default |
2755+
14 | View default | | View default |
2756+
15 | View default | | View default |
2757+
16 | View default | | View default |
2758+
(12 rows)
2759+
2760+
-- A DO ALSO rule should cause each row to be inserted twice. The first
2761+
-- insert should behave the same as an auto-updatable view (using table
2762+
-- defaults, unless overridden by view defaults). The second insert should
2763+
-- behave the same as a rule-updatable view (inserting NULLs where there are
2764+
-- no view defaults).
2765+
drop rule base_tab_def_view_ins_rule on base_tab_def_view;
2766+
create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view
2767+
do also insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e);
2768+
truncate base_tab_def;
2769+
insert into base_tab_def values (1);
2770+
insert into base_tab_def values (2), (3);
2771+
insert into base_tab_def values (4, default, default, default, default);
2772+
insert into base_tab_def values (5, default, default, default, default),
2773+
(6, default, default, default, default);
2774+
insert into base_tab_def_view values (11);
2775+
insert into base_tab_def_view values (12), (13);
2776+
insert into base_tab_def_view values (14, default, default, default, default);
2777+
insert into base_tab_def_view values (15, default, default, default, default),
2778+
(16, default, default, default, default);
2779+
select * from base_tab_def order by a, c NULLS LAST;
2780+
a | b | c | d | e
2781+
----+---------------+---------------+--------------+---
2782+
1 | Table default | Table default | |
2783+
2 | Table default | Table default | |
2784+
3 | Table default | Table default | |
2785+
4 | Table default | Table default | |
2786+
5 | Table default | Table default | |
2787+
6 | Table default | Table default | |
2788+
11 | View default | Table default | View default |
2789+
11 | View default | | View default |
2790+
12 | View default | Table default | View default |
2791+
12 | View default | | View default |
2792+
13 | View default | Table default | View default |
2793+
13 | View default | | View default |
2794+
14 | View default | Table default | View default |
2795+
14 | View default | | View default |
2796+
15 | View default | Table default | View default |
2797+
15 | View default | | View default |
2798+
16 | View default | Table default | View default |
2799+
16 | View default | | View default |
2800+
(18 rows)
2801+
2802+
drop view base_tab_def_view;
2803+
drop table base_tab_def;

0 commit comments

Comments
 (0)