Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit41531e4

Browse files
committed
Fix DEFAULT-handling in multi-row VALUES lists for updatable views.
INSERT ... VALUES for a single VALUES row is implemented differentlyfrom a multi-row VALUES list, which causes inconsistent behaviour inthe way that DEFAULT items are handled. In particular, when insertinginto an auto-updatable view on top of a table with a column default, aDEFAULT item in a single VALUES row gets correctly replaced with thetable column's default, but for a multi-row VALUES list it is replacedwith NULL.Fix this by allowing rewriteValuesRTE() to leave DEFAULT items in theVALUES list untouched if the target relation is an auto-updatable viewand has no column default, deferring DEFAULT-expansion until the queryagainst the base relation is rewritten. For all other types of targetrelation, including tables and trigger- and rule-updatable views, wemust continue to replace DEFAULT items with NULL in the absence of acolumn default.This is somewhat complicated by the fact that if an auto-updatableview has DO ALSO rules attached, the VALUES lists for the productqueries need to be handled differently from the original query, sincethe product queries need to act like rule-updatable views whereas theoriginal 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 parent56fadbe commit41531e4

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
@@ -73,8 +73,8 @@ static TargetEntry *process_matched_tle(TargetEntry *src_tle,
7373
TargetEntry*prior_tle,
7474
constchar*attrName);
7575
staticNode*get_assignment_input(Node*node);
76-
staticvoidrewriteValuesRTE(RangeTblEntry*rte,Relationtarget_relation,
77-
List*attrnos);
76+
staticboolrewriteValuesRTE(Query*parsetree,RangeTblEntry*rte,
77+
Relationtarget_relation,List*attrnos,boolforce_nulls);
7878
staticvoidmarkQueryForLocking(Query*qry,Node*jtnode,
7979
LockClauseStrengthstrength,LockWaitPolicywaitPolicy,
8080
boolpushedDown);
@@ -1219,29 +1219,102 @@ searchForDefault(RangeTblEntry *rte)
12191219
* the appropriate default expressions. The other aspects of targetlist
12201220
* rewriting need be applied only to the query's targetlist proper.
12211221
*
1222+
* For an auto-updatable view, each DEFAULT item in the VALUES list is
1223+
* replaced with the default from the view, if it has one. Otherwise it is
1224+
* left untouched so that the underlying base relation's default can be
1225+
* applied instead (when we later recurse to here after rewriting the query
1226+
* to refer to the base relation instead of the view).
1227+
*
1228+
* For other types of relation, including rule- and trigger-updatable views,
1229+
* all DEFAULT items are replaced, and if the target relation doesn't have a
1230+
* default, the value is explicitly set to NULL.
1231+
*
1232+
* Additionally, if force_nulls is true, the target relation's defaults are
1233+
* ignored and all DEFAULT items in the VALUES list are explicitly set to
1234+
* NULL, regardless of the target relation's type. This is used for the
1235+
* product queries generated by DO ALSO rules attached to an auto-updatable
1236+
* view, for which we will have already called this function with force_nulls
1237+
* false. For these product queries, we must then force any remaining DEFAULT
1238+
* items to NULL to provide concrete values for the rule actions.
1239+
* Essentially, this is a mix of the 2 cases above --- the original query is
1240+
* an insert into an auto-updatable view, and the product queries are inserts
1241+
* into a rule-updatable view.
1242+
*
12221243
* Note that we currently can't support subscripted or field assignment
12231244
* in the multi-VALUES case. The targetlist will contain simple Vars
12241245
* referencing the VALUES RTE, and therefore process_matched_tle() will
12251246
* reject any such attempt with "multiple assignments to same column".
1247+
*
1248+
* Returns true if all DEFAULT items were replaced, and false if some were
1249+
* left untouched.
12261250
*/
1227-
staticvoid
1228-
rewriteValuesRTE(RangeTblEntry*rte,Relationtarget_relation,List*attrnos)
1251+
staticbool
1252+
rewriteValuesRTE(Query*parsetree,RangeTblEntry*rte,
1253+
Relationtarget_relation,List*attrnos,boolforce_nulls)
12291254
{
12301255
List*newValues;
12311256
ListCell*lc;
1257+
boolisAutoUpdatableView;
1258+
boolallReplaced;
12321259

12331260
/*
12341261
* Rebuilding all the lists is a pretty expensive proposition in a big
12351262
* VALUES list, and it's a waste of time if there aren't any DEFAULT
12361263
* placeholders. So first scan to see if there are any.
1264+
*
1265+
* We skip this check if force_nulls is true, because we know that there
1266+
* are DEFAULT items present in that case.
12371267
*/
1238-
if (!searchForDefault(rte))
1239-
return;/* nothing to do */
1268+
if (!force_nulls&& !searchForDefault(rte))
1269+
return true;/* nothing to do */
12401270

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

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

12621335
att_tup=TupleDescAttr(target_relation->rd_att,attrno-1);
12631336

1264-
if (!att_tup->attisdropped)
1337+
if (!force_nulls&& !att_tup->attisdropped)
12651338
new_expr=build_column_default(target_relation,attrno);
12661339
else
12671340
new_expr=NULL;/* force a NULL if dropped */
12681341

12691342
/*
12701343
* If there is no default (ie, default is effectively NULL),
1271-
* we've got to explicitly set the column to NULL.
1344+
* we've got to explicitly set the column to NULL, unless the
1345+
* target relation is an auto-updatable view.
12721346
*/
12731347
if (!new_expr)
12741348
{
1349+
if (isAutoUpdatableView)
1350+
{
1351+
/* Leave the value untouched */
1352+
newList=lappend(newList,col);
1353+
allReplaced= false;
1354+
continue;
1355+
}
1356+
12751357
new_expr= (Node*)makeConst(att_tup->atttypid,
12761358
-1,
12771359
att_tup->attcollation,
@@ -1296,6 +1378,8 @@ rewriteValuesRTE(RangeTblEntry *rte, Relation target_relation, List *attrnos)
12961378
newValues=lappend(newValues,newList);
12971379
}
12981380
rte->values_lists=newValues;
1381+
1382+
returnallReplaced;
12991383
}
13001384

13011385

@@ -3383,6 +3467,9 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
33833467
List*locks;
33843468
List*product_queries;
33853469
boolhasUpdate= false;
3470+
List*attrnos=NIL;
3471+
intvalues_rte_index=0;
3472+
booldefaults_remaining= false;
33863473

33873474
result_relation=parsetree->resultRelation;
33883475
Assert(result_relation!=0);
@@ -3416,14 +3503,15 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
34163503
parsetree->rtable);
34173504

34183505
if (rte->rtekind==RTE_VALUES)
3506+
{
34193507
values_rte=rte;
3508+
values_rte_index=rtr->rtindex;
3509+
}
34203510
}
34213511
}
34223512

34233513
if (values_rte)
34243514
{
3425-
List*attrnos;
3426-
34273515
/* Process the main targetlist ... */
34283516
parsetree->targetList=rewriteTargetListIU(parsetree->targetList,
34293517
parsetree->commandType,
@@ -3432,7 +3520,9 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
34323520
parsetree->resultRelation,
34333521
&attrnos);
34343522
/* ... and the VALUES expression lists */
3435-
rewriteValuesRTE(values_rte,rt_entry_relation,attrnos);
3523+
if (!rewriteValuesRTE(parsetree,values_rte,
3524+
rt_entry_relation,attrnos, false))
3525+
defaults_remaining= true;
34363526
}
34373527
else
34383528
{
@@ -3487,6 +3577,33 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
34873577
&returning,
34883578
&qual_product);
34893579

3580+
/*
3581+
* If we have a VALUES RTE with any remaining untouched DEFAULT items,
3582+
* and we got any product queries, finalize the VALUES RTE for each
3583+
* product query (replacing the remaining DEFAULT items with NULLs).
3584+
* We don't do this for the original query, because we know that it
3585+
* must be an auto-insert on a view, and so should use the base
3586+
* relation's defaults for any remaining DEFAULT items.
3587+
*/
3588+
if (defaults_remaining&&product_queries!=NIL)
3589+
{
3590+
ListCell*n;
3591+
3592+
/*
3593+
* Each product query has its own copy of the VALUES RTE at the
3594+
* same index in the rangetable, so we must finalize each one.
3595+
*/
3596+
foreach(n,product_queries)
3597+
{
3598+
Query*pt= (Query*)lfirst(n);
3599+
RangeTblEntry*values_rte=rt_fetch(values_rte_index,
3600+
pt->rtable);
3601+
3602+
rewriteValuesRTE(pt,values_rte,rt_entry_relation,attrnos,
3603+
true);/* Force remaining defaults to NULL */
3604+
}
3605+
}
3606+
34903607
/*
34913608
* If there were no INSTEAD rules, and the target relation is a view
34923609
* 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
@@ -2774,3 +2774,156 @@ drop view rw_view1;
27742774
drop table base_tbl;
27752775
drop user regress_view_user1;
27762776
drop user regress_view_user2;
2777+
-- Test single- and multi-row inserts with table and view defaults.
2778+
-- Table defaults should be used, unless overridden by view defaults.
2779+
create table base_tab_def (a int, b text default 'Table default',
2780+
c text default 'Table default', d text, e text);
2781+
create view base_tab_def_view as select * from base_tab_def;
2782+
alter view base_tab_def_view alter b set default 'View default';
2783+
alter view base_tab_def_view alter d set default 'View default';
2784+
insert into base_tab_def values (1);
2785+
insert into base_tab_def values (2), (3);
2786+
insert into base_tab_def values (4, default, default, default, default);
2787+
insert into base_tab_def values (5, default, default, default, default),
2788+
(6, default, default, default, default);
2789+
insert into base_tab_def_view values (11);
2790+
insert into base_tab_def_view values (12), (13);
2791+
insert into base_tab_def_view values (14, default, default, default, default);
2792+
insert into base_tab_def_view values (15, default, default, default, default),
2793+
(16, default, default, default, default);
2794+
select * from base_tab_def order by a;
2795+
a | b | c | d | e
2796+
----+---------------+---------------+--------------+---
2797+
1 | Table default | Table default | |
2798+
2 | Table default | Table default | |
2799+
3 | Table default | Table default | |
2800+
4 | Table default | Table default | |
2801+
5 | Table default | Table default | |
2802+
6 | Table default | Table default | |
2803+
11 | View default | Table default | View default |
2804+
12 | View default | Table default | View default |
2805+
13 | View default | Table default | View default |
2806+
14 | View default | Table default | View default |
2807+
15 | View default | Table default | View default |
2808+
16 | View default | Table default | View default |
2809+
(12 rows)
2810+
2811+
-- Adding an INSTEAD OF trigger should cause NULLs to be inserted instead of
2812+
-- table defaults, where there are no view defaults.
2813+
create function base_tab_def_view_instrig_func() returns trigger
2814+
as
2815+
$$
2816+
begin
2817+
insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e);
2818+
return new;
2819+
end;
2820+
$$
2821+
language plpgsql;
2822+
create trigger base_tab_def_view_instrig instead of insert on base_tab_def_view
2823+
for each row execute function base_tab_def_view_instrig_func();
2824+
truncate base_tab_def;
2825+
insert into base_tab_def values (1);
2826+
insert into base_tab_def values (2), (3);
2827+
insert into base_tab_def values (4, default, default, default, default);
2828+
insert into base_tab_def values (5, default, default, default, default),
2829+
(6, default, default, default, default);
2830+
insert into base_tab_def_view values (11);
2831+
insert into base_tab_def_view values (12), (13);
2832+
insert into base_tab_def_view values (14, default, default, default, default);
2833+
insert into base_tab_def_view values (15, default, default, default, default),
2834+
(16, default, default, default, default);
2835+
select * from base_tab_def order by a;
2836+
a | b | c | d | e
2837+
----+---------------+---------------+--------------+---
2838+
1 | Table default | Table default | |
2839+
2 | Table default | Table default | |
2840+
3 | Table default | Table default | |
2841+
4 | Table default | Table default | |
2842+
5 | Table default | Table default | |
2843+
6 | Table default | Table default | |
2844+
11 | View default | | View default |
2845+
12 | View default | | View default |
2846+
13 | View default | | View default |
2847+
14 | View default | | View default |
2848+
15 | View default | | View default |
2849+
16 | View default | | View default |
2850+
(12 rows)
2851+
2852+
-- Using an unconditional DO INSTEAD rule should also cause NULLs to be
2853+
-- inserted where there are no view defaults.
2854+
drop trigger base_tab_def_view_instrig on base_tab_def_view;
2855+
drop function base_tab_def_view_instrig_func;
2856+
create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view
2857+
do instead insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e);
2858+
truncate base_tab_def;
2859+
insert into base_tab_def values (1);
2860+
insert into base_tab_def values (2), (3);
2861+
insert into base_tab_def values (4, default, default, default, default);
2862+
insert into base_tab_def values (5, default, default, default, default),
2863+
(6, default, default, default, default);
2864+
insert into base_tab_def_view values (11);
2865+
insert into base_tab_def_view values (12), (13);
2866+
insert into base_tab_def_view values (14, default, default, default, default);
2867+
insert into base_tab_def_view values (15, default, default, default, default),
2868+
(16, default, default, default, default);
2869+
select * from base_tab_def order by a;
2870+
a | b | c | d | e
2871+
----+---------------+---------------+--------------+---
2872+
1 | Table default | Table default | |
2873+
2 | Table default | Table default | |
2874+
3 | Table default | Table default | |
2875+
4 | Table default | Table default | |
2876+
5 | Table default | Table default | |
2877+
6 | Table default | Table default | |
2878+
11 | View default | | View default |
2879+
12 | View default | | View default |
2880+
13 | View default | | View default |
2881+
14 | View default | | View default |
2882+
15 | View default | | View default |
2883+
16 | View default | | View default |
2884+
(12 rows)
2885+
2886+
-- A DO ALSO rule should cause each row to be inserted twice. The first
2887+
-- insert should behave the same as an auto-updatable view (using table
2888+
-- defaults, unless overridden by view defaults). The second insert should
2889+
-- behave the same as a rule-updatable view (inserting NULLs where there are
2890+
-- no view defaults).
2891+
drop rule base_tab_def_view_ins_rule on base_tab_def_view;
2892+
create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view
2893+
do also insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e);
2894+
truncate base_tab_def;
2895+
insert into base_tab_def values (1);
2896+
insert into base_tab_def values (2), (3);
2897+
insert into base_tab_def values (4, default, default, default, default);
2898+
insert into base_tab_def values (5, default, default, default, default),
2899+
(6, default, default, default, default);
2900+
insert into base_tab_def_view values (11);
2901+
insert into base_tab_def_view values (12), (13);
2902+
insert into base_tab_def_view values (14, default, default, default, default);
2903+
insert into base_tab_def_view values (15, default, default, default, default),
2904+
(16, default, default, default, default);
2905+
select * from base_tab_def order by a, c NULLS LAST;
2906+
a | b | c | d | e
2907+
----+---------------+---------------+--------------+---
2908+
1 | Table default | Table default | |
2909+
2 | Table default | Table default | |
2910+
3 | Table default | Table default | |
2911+
4 | Table default | Table default | |
2912+
5 | Table default | Table default | |
2913+
6 | Table default | Table default | |
2914+
11 | View default | Table default | View default |
2915+
11 | View default | | View default |
2916+
12 | View default | Table default | View default |
2917+
12 | View default | | View default |
2918+
13 | View default | Table default | View default |
2919+
13 | View default | | View default |
2920+
14 | View default | Table default | View default |
2921+
14 | View default | | View default |
2922+
15 | View default | Table default | View default |
2923+
15 | View default | | View default |
2924+
16 | View default | Table default | View default |
2925+
16 | View default | | View default |
2926+
(18 rows)
2927+
2928+
drop view base_tab_def_view;
2929+
drop table base_tab_def;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp