Skip to content

Commit 5a73edf

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 2ad57e9 commit 5a73edf

File tree

3 files changed

+368
-11
lines changed

3 files changed

+368
-11
lines changed

‎src/backend/rewrite/rewriteHandler.c

Lines changed: 127 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -57,8 +57,8 @@ static TargetEntry *process_matched_tle(TargetEntry *src_tle,
5757
TargetEntry *prior_tle,
5858
const char *attrName);
5959
static Node *get_assignment_input(Node *node);
60-
static void rewriteValuesRTE(RangeTblEntry *rte, Relation target_relation,
61-
List *attrnos);
60+
static bool rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte,
61+
Relation target_relation, List *attrnos, bool force_nulls);
6262
static void rewriteTargetListUD(Query *parsetree, RangeTblEntry *target_rte,
6363
Relation target_relation);
6464
static void markQueryForLocking(Query *qry, Node *jtnode,
@@ -1133,29 +1133,101 @@ searchForDefault(RangeTblEntry *rte)
11331133
* the appropriate default expressions. The other aspects of targetlist
11341134
* rewriting need be applied only to the query's targetlist proper.
11351135
*
1136+
* For an auto-updatable view, each DEFAULT item in the VALUES list is
1137+
* replaced with the default from the view, if it has one. Otherwise it is
1138+
* left untouched so that the underlying base relation's default can be
1139+
* applied instead (when we later recurse to here after rewriting the query
1140+
* to refer to the base relation instead of the view).
1141+
*
1142+
* For other types of relation, including rule- and trigger-updatable views,
1143+
* all DEFAULT items are replaced, and if the target relation doesn't have a
1144+
* default, the value is explicitly set to NULL.
1145+
*
1146+
* Additionally, if force_nulls is true, the target relation's defaults are
1147+
* ignored and all DEFAULT items in the VALUES list are explicitly set to
1148+
* NULL, regardless of the target relation's type. This is used for the
1149+
* product queries generated by DO ALSO rules attached to an auto-updatable
1150+
* view, for which we will have already called this function with force_nulls
1151+
* false. For these product queries, we must then force any remaining DEFAULT
1152+
* items to NULL to provide concrete values for the rule actions.
1153+
* Essentially, this is a mix of the 2 cases above --- the original query is
1154+
* an insert into an auto-updatable view, and the product queries are inserts
1155+
* into a rule-updatable view.
1156+
*
11361157
* Note that we currently can't support subscripted or field assignment
11371158
* in the multi-VALUES case. The targetlist will contain simple Vars
11381159
* referencing the VALUES RTE, and therefore process_matched_tle() will
11391160
* reject any such attempt with "multiple assignments to same column".
1161+
*
1162+
* Returns true if all DEFAULT items were replaced, and false if some were
1163+
* left untouched.
11401164
*/
1141-
static void
1142-
rewriteValuesRTE(RangeTblEntry *rte, Relation target_relation, List *attrnos)
1165+
static bool
1166+
rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte,
1167+
Relation target_relation, List *attrnos, bool force_nulls)
11431168
{
11441169
List *newValues;
11451170
ListCell *lc;
1171+
bool isAutoUpdatableView;
1172+
bool allReplaced;
11461173

11471174
/*
11481175
* Rebuilding all the lists is a pretty expensive proposition in a big
11491176
* VALUES list, and it's a waste of time if there aren't any DEFAULT
11501177
* placeholders. So first scan to see if there are any.
1178+
*
1179+
* We skip this check if force_nulls is true, because we know that there
1180+
* are DEFAULT items present in that case.
11511181
*/
1152-
if (!searchForDefault(rte))
1153-
return; /* nothing to do */
1182+
if (!force_nulls && !searchForDefault(rte))
1183+
return true; /* nothing to do */
11541184

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

1188+
/*
1189+
* Check if the target relation is an auto-updatable view, in which case
1190+
* unresolved defaults will be left untouched rather than being set to
1191+
* NULL. If force_nulls is true, we always set DEFAULT items to NULL, so
1192+
* skip this check in that case --- it isn't an auto-updatable view.
1193+
*/
1194+
isAutoUpdatableView = false;
1195+
if (!force_nulls &&
1196+
target_relation->rd_rel->relkind == RELKIND_VIEW &&
1197+
!view_has_instead_trigger(target_relation, CMD_INSERT))
1198+
{
1199+
List *locks;
1200+
bool found;
1201+
ListCell *l;
1202+
1203+
/* Look for an unconditional DO INSTEAD rule */
1204+
locks = matchLocks(CMD_INSERT, target_relation->rd_rules,
1205+
parsetree->resultRelation, parsetree);
1206+
1207+
found = false;
1208+
foreach(l, locks)
1209+
{
1210+
RewriteRule *rule_lock = (RewriteRule *) lfirst(l);
1211+
1212+
if (rule_lock->isInstead &&
1213+
rule_lock->qual == NULL)
1214+
{
1215+
found = true;
1216+
break;
1217+
}
1218+
}
1219+
1220+
/*
1221+
* If we didn't find an unconditional DO INSTEAD rule, assume that the
1222+
* view is auto-updatable. If it isn't, rewriteTargetView() will
1223+
* throw an error.
1224+
*/
1225+
if (!found)
1226+
isAutoUpdatableView = true;
1227+
}
1228+
11581229
newValues = NIL;
1230+
allReplaced = true;
11591231
foreach(lc, rte->values_lists)
11601232
{
11611233
List *sublist = (List *) lfirst(lc);
@@ -1175,17 +1247,26 @@ rewriteValuesRTE(RangeTblEntry *rte, Relation target_relation, List *attrnos)
11751247

11761248
att_tup = target_relation->rd_att->attrs[attrno - 1];
11771249

1178-
if (!att_tup->attisdropped)
1250+
if (!force_nulls && !att_tup->attisdropped)
11791251
new_expr = build_column_default(target_relation, attrno);
11801252
else
11811253
new_expr = NULL; /* force a NULL if dropped */
11821254

11831255
/*
11841256
* If there is no default (ie, default is effectively NULL),
1185-
* we've got to explicitly set the column to NULL.
1257+
* we've got to explicitly set the column to NULL, unless the
1258+
* target relation is an auto-updatable view.
11861259
*/
11871260
if (!new_expr)
11881261
{
1262+
if (isAutoUpdatableView)
1263+
{
1264+
/* Leave the value untouched */
1265+
newList = lappend(newList, col);
1266+
allReplaced = false;
1267+
continue;
1268+
}
1269+
11891270
new_expr = (Node *) makeConst(att_tup->atttypid,
11901271
-1,
11911272
att_tup->attcollation,
@@ -1210,6 +1291,8 @@ rewriteValuesRTE(RangeTblEntry *rte, Relation target_relation, List *attrnos)
12101291
newValues = lappend(newValues, newList);
12111292
}
12121293
rte->values_lists = newValues;
1294+
1295+
return allReplaced;
12131296
}
12141297

12151298

@@ -3063,6 +3146,9 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
30633146
Relation rt_entry_relation;
30643147
List *locks;
30653148
List *product_queries;
3149+
List *attrnos = NIL;
3150+
int values_rte_index = 0;
3151+
bool defaults_remaining = false;
30663152

30673153
result_relation = parsetree->resultRelation;
30683154
Assert(result_relation != 0);
@@ -3096,18 +3182,21 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
30963182
parsetree->rtable);
30973183

30983184
if (rte->rtekind == RTE_VALUES)
3185+
{
30993186
values_rte = rte;
3187+
values_rte_index = rtr->rtindex;
3188+
}
31003189
}
31013190
}
31023191

31033192
if (values_rte)
31043193
{
3105-
List *attrnos;
3106-
31073194
/* Process the main targetlist ... */
31083195
rewriteTargetListIU(parsetree, rt_entry_relation, &attrnos);
31093196
/* ... and the VALUES expression lists */
3110-
rewriteValuesRTE(values_rte, rt_entry_relation, attrnos);
3197+
if (!rewriteValuesRTE(parsetree, values_rte,
3198+
rt_entry_relation, attrnos, false))
3199+
defaults_remaining = true;
31113200
}
31123201
else
31133202
{
@@ -3141,6 +3230,33 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
31413230
&returning,
31423231
&qual_product);
31433232

3233+
/*
3234+
* If we have a VALUES RTE with any remaining untouched DEFAULT items,
3235+
* and we got any product queries, finalize the VALUES RTE for each
3236+
* product query (replacing the remaining DEFAULT items with NULLs).
3237+
* We don't do this for the original query, because we know that it
3238+
* must be an auto-insert on a view, and so should use the base
3239+
* relation's defaults for any remaining DEFAULT items.
3240+
*/
3241+
if (defaults_remaining && product_queries != NIL)
3242+
{
3243+
ListCell *n;
3244+
3245+
/*
3246+
* Each product query has its own copy of the VALUES RTE at the
3247+
* same index in the rangetable, so we must finalize each one.
3248+
*/
3249+
foreach(n, product_queries)
3250+
{
3251+
Query *pt = (Query *) lfirst(n);
3252+
RangeTblEntry *values_rte = rt_fetch(values_rte_index,
3253+
pt->rtable);
3254+
3255+
rewriteValuesRTE(pt, values_rte, rt_entry_relation, attrnos,
3256+
true); /* Force remaining defaults to NULL */
3257+
}
3258+
}
3259+
31443260
/*
31453261
* If there were no INSTEAD rules, and the target relation is a view
31463262
* 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
@@ -2403,3 +2403,156 @@ ERROR: new row violates WITH CHECK OPTION for view "v1"
24032403
DETAIL: Failing row contains (-1, invalid).
24042404
DROP VIEW v1;
24052405
DROP TABLE t1;
2406+
-- Test single- and multi-row inserts with table and view defaults.
2407+
-- Table defaults should be used, unless overridden by view defaults.
2408+
create table base_tab_def (a int, b text default 'Table default',
2409+
c text default 'Table default', d text, e text);
2410+
create view base_tab_def_view as select * from base_tab_def;
2411+
alter view base_tab_def_view alter b set default 'View default';
2412+
alter view base_tab_def_view alter d set default 'View default';
2413+
insert into base_tab_def values (1);
2414+
insert into base_tab_def values (2), (3);
2415+
insert into base_tab_def values (4, default, default, default, default);
2416+
insert into base_tab_def values (5, default, default, default, default),
2417+
(6, default, default, default, default);
2418+
insert into base_tab_def_view values (11);
2419+
insert into base_tab_def_view values (12), (13);
2420+
insert into base_tab_def_view values (14, default, default, default, default);
2421+
insert into base_tab_def_view values (15, default, default, default, default),
2422+
(16, default, default, default, default);
2423+
select * from base_tab_def order by a;
2424+
a | b | c | d | e
2425+
----+---------------+---------------+--------------+---
2426+
1 | Table default | Table default | |
2427+
2 | Table default | Table default | |
2428+
3 | Table default | Table default | |
2429+
4 | Table default | Table default | |
2430+
5 | Table default | Table default | |
2431+
6 | Table default | Table default | |
2432+
11 | View default | Table default | View default |
2433+
12 | View default | Table default | View default |
2434+
13 | View default | Table default | View default |
2435+
14 | View default | Table default | View default |
2436+
15 | View default | Table default | View default |
2437+
16 | View default | Table default | View default |
2438+
(12 rows)
2439+
2440+
-- Adding an INSTEAD OF trigger should cause NULLs to be inserted instead of
2441+
-- table defaults, where there are no view defaults.
2442+
create function base_tab_def_view_instrig_func() returns trigger
2443+
as
2444+
$$
2445+
begin
2446+
insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e);
2447+
return new;
2448+
end;
2449+
$$
2450+
language plpgsql;
2451+
create trigger base_tab_def_view_instrig instead of insert on base_tab_def_view
2452+
for each row execute procedure base_tab_def_view_instrig_func();
2453+
truncate base_tab_def;
2454+
insert into base_tab_def values (1);
2455+
insert into base_tab_def values (2), (3);
2456+
insert into base_tab_def values (4, default, default, default, default);
2457+
insert into base_tab_def values (5, default, default, default, default),
2458+
(6, default, default, default, default);
2459+
insert into base_tab_def_view values (11);
2460+
insert into base_tab_def_view values (12), (13);
2461+
insert into base_tab_def_view values (14, default, default, default, default);
2462+
insert into base_tab_def_view values (15, default, default, default, default),
2463+
(16, default, default, default, default);
2464+
select * from base_tab_def order by a;
2465+
a | b | c | d | e
2466+
----+---------------+---------------+--------------+---
2467+
1 | Table default | Table default | |
2468+
2 | Table default | Table default | |
2469+
3 | Table default | Table default | |
2470+
4 | Table default | Table default | |
2471+
5 | Table default | Table default | |
2472+
6 | Table default | Table default | |
2473+
11 | View default | | View default |
2474+
12 | View default | | View default |
2475+
13 | View default | | View default |
2476+
14 | View default | | View default |
2477+
15 | View default | | View default |
2478+
16 | View default | | View default |
2479+
(12 rows)
2480+
2481+
-- Using an unconditional DO INSTEAD rule should also cause NULLs to be
2482+
-- inserted where there are no view defaults.
2483+
drop trigger base_tab_def_view_instrig on base_tab_def_view;
2484+
drop function base_tab_def_view_instrig_func();
2485+
create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view
2486+
do instead insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e);
2487+
truncate base_tab_def;
2488+
insert into base_tab_def values (1);
2489+
insert into base_tab_def values (2), (3);
2490+
insert into base_tab_def values (4, default, default, default, default);
2491+
insert into base_tab_def values (5, default, default, default, default),
2492+
(6, default, default, default, default);
2493+
insert into base_tab_def_view values (11);
2494+
insert into base_tab_def_view values (12), (13);
2495+
insert into base_tab_def_view values (14, default, default, default, default);
2496+
insert into base_tab_def_view values (15, default, default, default, default),
2497+
(16, default, default, default, default);
2498+
select * from base_tab_def order by a;
2499+
a | b | c | d | e
2500+
----+---------------+---------------+--------------+---
2501+
1 | Table default | Table default | |
2502+
2 | Table default | Table default | |
2503+
3 | Table default | Table default | |
2504+
4 | Table default | Table default | |
2505+
5 | Table default | Table default | |
2506+
6 | Table default | Table default | |
2507+
11 | View default | | View default |
2508+
12 | View default | | View default |
2509+
13 | View default | | View default |
2510+
14 | View default | | View default |
2511+
15 | View default | | View default |
2512+
16 | View default | | View default |
2513+
(12 rows)
2514+
2515+
-- A DO ALSO rule should cause each row to be inserted twice. The first
2516+
-- insert should behave the same as an auto-updatable view (using table
2517+
-- defaults, unless overridden by view defaults). The second insert should
2518+
-- behave the same as a rule-updatable view (inserting NULLs where there are
2519+
-- no view defaults).
2520+
drop rule base_tab_def_view_ins_rule on base_tab_def_view;
2521+
create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view
2522+
do also insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e);
2523+
truncate base_tab_def;
2524+
insert into base_tab_def values (1);
2525+
insert into base_tab_def values (2), (3);
2526+
insert into base_tab_def values (4, default, default, default, default);
2527+
insert into base_tab_def values (5, default, default, default, default),
2528+
(6, default, default, default, default);
2529+
insert into base_tab_def_view values (11);
2530+
insert into base_tab_def_view values (12), (13);
2531+
insert into base_tab_def_view values (14, default, default, default, default);
2532+
insert into base_tab_def_view values (15, default, default, default, default),
2533+
(16, default, default, default, default);
2534+
select * from base_tab_def order by a, c NULLS LAST;
2535+
a | b | c | d | e
2536+
----+---------------+---------------+--------------+---
2537+
1 | Table default | Table default | |
2538+
2 | Table default | Table default | |
2539+
3 | Table default | Table default | |
2540+
4 | Table default | Table default | |
2541+
5 | Table default | Table default | |
2542+
6 | Table default | Table default | |
2543+
11 | View default | Table default | View default |
2544+
11 | View default | | View default |
2545+
12 | View default | Table default | View default |
2546+
12 | View default | | View default |
2547+
13 | View default | Table default | View default |
2548+
13 | View default | | View default |
2549+
14 | View default | Table default | View default |
2550+
14 | View default | | View default |
2551+
15 | View default | Table default | View default |
2552+
15 | View default | | View default |
2553+
16 | View default | Table default | View default |
2554+
16 | View default | | View default |
2555+
(18 rows)
2556+
2557+
drop view base_tab_def_view;
2558+
drop table base_tab_def;

0 commit comments

Comments
 (0)