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

Commited4653d

Browse files
committed
Further fixing for multi-row VALUES lists for updatable views.
Previously, rewriteTargetListIU() generated a list of attributenumbers from the targetlist, which were passed to rewriteValuesRTE(),which expected them to contain the same number of entries as there arecolumns in the VALUES RTE, and to be in the same order. That was finewhen the target relation was a table, but for an updatable view itcould be broken in at least three different ways ---rewriteTargetListIU() could insert additional targetlist entries forview columns with defaults, the view columns could be in a differentorder from the columns of the underlying base relation, and targetlistentries could be merged together when assigning to elements of anarray or composite type. As a result, when recursing to the baserelation, the list of attribute numbers generated from the rewrittentargetlist could no longer be relied upon to match the columns of theVALUES RTE. We got away with that prior to41531e4 because it usedto always be the case that rewriteValuesRTE() did nothing for theunderlying base relation, since all DEFAULTS had already been replacedwhen it was initially invoked for the view, but that was incorrectbecause it failed to apply defaults from the base relation.Fix this by examining the targetlist entries more carefully andpicking out just those that are simple Vars referencing the VALUESRTE. That's sufficient for the purposes of rewriteValuesRTE(), whichis only responsible for dealing with DEFAULT items in the VALUESRTE. Any DEFAULT item in the VALUES RTE that doesn't have a matchingsimple-Var-assignment in the targetlist is an error which we complainabout, but in theory that ought to be impossible.Additionally, move this code into rewriteValuesRTE() to give a clearerseparation of concerns between the 2 functions. There is no need forrewriteTargetListIU() to know about the details of the VALUES RTE.While at it, fix the comment for rewriteValuesRTE() which claimed thatit doesn't support array element and field assignments --- that hasn'tbeen true sincea3c7a99 (9.6 and later).Back-patch to all supported versions, with minor differences for thepre-9.6 branches, which don't support array element and fieldassignments to the same column in multi-row VALUES lists.Reviewed by Amit Langote.Discussion:https://postgr.es/m/15623-5d67a46788ec8b7f@postgresql.org
1 parent3422955 commited4653d

File tree

3 files changed

+106
-43
lines changed

3 files changed

+106
-43
lines changed

‎src/backend/rewrite/rewriteHandler.c

Lines changed: 59 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -67,14 +67,13 @@ static List *rewriteTargetListIU(List *targetList,
6767
CmdTypecommandType,
6868
OverridingKindoverride,
6969
Relationtarget_relation,
70-
intresult_rti,
71-
List**attrno_list);
70+
intresult_rti);
7271
staticTargetEntry*process_matched_tle(TargetEntry*src_tle,
7372
TargetEntry*prior_tle,
7473
constchar*attrName);
7574
staticNode*get_assignment_input(Node*node);
76-
staticboolrewriteValuesRTE(Query*parsetree,RangeTblEntry*rte,
77-
Relationtarget_relation,List*attrnos,boolforce_nulls);
75+
staticboolrewriteValuesRTE(Query*parsetree,RangeTblEntry*rte,intrti,
76+
Relationtarget_relation,boolforce_nulls);
7877
staticvoidmarkQueryForLocking(Query*qry,Node*jtnode,
7978
LockClauseStrengthstrength,LockWaitPolicywaitPolicy,
8079
boolpushedDown);
@@ -705,11 +704,6 @@ adjustJoinTreeList(Query *parsetree, bool removert, int rt_index)
705704
* is not needed for rewriting, but will be needed by the planner, and we
706705
* can do it essentially for free while handling the other items.
707706
*
708-
* If attrno_list isn't NULL, we return an additional output besides the
709-
* rewritten targetlist: an integer list of the assigned-to attnums, in
710-
* order of the original tlist's non-junk entries. This is needed for
711-
* processing VALUES RTEs.
712-
*
713707
* Note that for an inheritable UPDATE, this processing is only done once,
714708
* using the parent relation as reference. It must not do anything that
715709
* will not be correct when transposed to the child relation(s). (Step 4
@@ -722,8 +716,7 @@ rewriteTargetListIU(List *targetList,
722716
CmdTypecommandType,
723717
OverridingKindoverride,
724718
Relationtarget_relation,
725-
intresult_rti,
726-
List**attrno_list)
719+
intresult_rti)
727720
{
728721
TargetEntry**new_tles;
729722
List*new_tlist=NIL;
@@ -734,9 +727,6 @@ rewriteTargetListIU(List *targetList,
734727
numattrs;
735728
ListCell*temp;
736729

737-
if (attrno_list)/* initialize optional result list */
738-
*attrno_list=NIL;
739-
740730
/*
741731
* We process the normal (non-junk) attributes by scanning the input tlist
742732
* once and transferring TLEs into an array, then scanning the array to
@@ -762,10 +752,6 @@ rewriteTargetListIU(List *targetList,
762752
elog(ERROR,"bogus resno %d in targetlist",attrno);
763753
att_tup=TupleDescAttr(target_relation->rd_att,attrno-1);
764754

765-
/* put attrno into attrno_list even if it's dropped */
766-
if (attrno_list)
767-
*attrno_list=lappend_int(*attrno_list,attrno);
768-
769755
/* We can (and must) ignore deleted attributes */
770756
if (att_tup->attisdropped)
771757
continue;
@@ -1240,22 +1226,26 @@ searchForDefault(RangeTblEntry *rte)
12401226
* an insert into an auto-updatable view, and the product queries are inserts
12411227
* into a rule-updatable view.
12421228
*
1243-
* Note that we currently can't support subscripted or field assignment
1244-
* in the multi-VALUES case. The targetlist will contain simple Vars
1245-
* referencing the VALUES RTE, and therefore process_matched_tle() will
1246-
* reject any such attempt with "multiple assignments to same column".
1229+
* Note that we may have subscripted or field assignment targetlist entries,
1230+
* as well as more complex expressions from already-replaced DEFAULT items if
1231+
* we have recursed to here for an auto-updatable view. However, it ought to
1232+
* be impossible for such entries to have DEFAULTs assigned to them --- we
1233+
* should only have to replace DEFAULT items for targetlist entries that
1234+
* contain simple Vars referencing the VALUES RTE.
12471235
*
12481236
* Returns true if all DEFAULT items were replaced, and false if some were
12491237
* left untouched.
12501238
*/
12511239
staticbool
1252-
rewriteValuesRTE(Query*parsetree,RangeTblEntry*rte,
1253-
Relationtarget_relation,List*attrnos,boolforce_nulls)
1240+
rewriteValuesRTE(Query*parsetree,RangeTblEntry*rte,intrti,
1241+
Relationtarget_relation,boolforce_nulls)
12541242
{
12551243
List*newValues;
12561244
ListCell*lc;
12571245
boolisAutoUpdatableView;
12581246
boolallReplaced;
1247+
intnumattrs;
1248+
int*attrnos;
12591249

12601250
/*
12611251
* Rebuilding all the lists is a pretty expensive proposition in a big
@@ -1268,8 +1258,33 @@ rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte,
12681258
if (!force_nulls&& !searchForDefault(rte))
12691259
return true;/* nothing to do */
12701260

1271-
/* Check list lengths (we can assume all the VALUES sublists are alike) */
1272-
Assert(list_length(attrnos)==list_length(linitial(rte->values_lists)));
1261+
/*
1262+
* Scan the targetlist for entries referring to the VALUES RTE, and note
1263+
* the target attributes. As noted above, we should only need to do this
1264+
* for targetlist entries containing simple Vars --- nothing else in the
1265+
* VALUES RTE should contain DEFAULT items, and we complain if such a
1266+
* thing does occur.
1267+
*/
1268+
numattrs=list_length(linitial(rte->values_lists));
1269+
attrnos= (int*)palloc0(numattrs*sizeof(int));
1270+
1271+
foreach(lc,parsetree->targetList)
1272+
{
1273+
TargetEntry*tle= (TargetEntry*)lfirst(lc);
1274+
1275+
if (IsA(tle->expr,Var))
1276+
{
1277+
Var*var= (Var*)tle->expr;
1278+
1279+
if (var->varno==rti)
1280+
{
1281+
intattrno=var->varattno;
1282+
1283+
Assert(attrno >=1&&attrno <=numattrs);
1284+
attrnos[attrno-1]=tle->resno;
1285+
}
1286+
}
1287+
}
12731288

12741289
/*
12751290
* Check if the target relation is an auto-updatable view, in which case
@@ -1320,18 +1335,23 @@ rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte,
13201335
List*sublist= (List*)lfirst(lc);
13211336
List*newList=NIL;
13221337
ListCell*lc2;
1323-
ListCell*lc3;
1338+
inti;
1339+
1340+
Assert(list_length(sublist)==numattrs);
13241341

1325-
forboth(lc2,sublist,lc3,attrnos)
1342+
i=0;
1343+
foreach(lc2,sublist)
13261344
{
13271345
Node*col= (Node*)lfirst(lc2);
1328-
intattrno=lfirst_int(lc3);
1346+
intattrno=attrnos[i++];
13291347

13301348
if (IsA(col,SetToDefault))
13311349
{
13321350
Form_pg_attributeatt_tup;
13331351
Node*new_expr;
13341352

1353+
if (attrno==0)
1354+
elog(ERROR,"cannot set value in column %d to DEFAULT",i);
13351355
att_tup=TupleDescAttr(target_relation->rd_att,attrno-1);
13361356

13371357
if (!force_nulls&& !att_tup->attisdropped)
@@ -1379,6 +1399,8 @@ rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte,
13791399
}
13801400
rte->values_lists=newValues;
13811401

1402+
pfree(attrnos);
1403+
13821404
returnallReplaced;
13831405
}
13841406

@@ -3467,7 +3489,6 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
34673489
List*locks;
34683490
List*product_queries;
34693491
boolhasUpdate= false;
3470-
List*attrnos=NIL;
34713492
intvalues_rte_index=0;
34723493
booldefaults_remaining= false;
34733494

@@ -3517,11 +3538,10 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
35173538
parsetree->commandType,
35183539
parsetree->override,
35193540
rt_entry_relation,
3520-
parsetree->resultRelation,
3521-
&attrnos);
3541+
parsetree->resultRelation);
35223542
/* ... and the VALUES expression lists */
3523-
if (!rewriteValuesRTE(parsetree,values_rte,
3524-
rt_entry_relation,attrnos,false))
3543+
if (!rewriteValuesRTE(parsetree,values_rte,values_rte_index,
3544+
rt_entry_relation, false))
35253545
defaults_remaining= true;
35263546
}
35273547
else
@@ -3532,7 +3552,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
35323552
parsetree->commandType,
35333553
parsetree->override,
35343554
rt_entry_relation,
3535-
parsetree->resultRelation,NULL);
3555+
parsetree->resultRelation);
35363556
}
35373557

35383558
if (parsetree->onConflict&&
@@ -3543,8 +3563,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
35433563
CMD_UPDATE,
35443564
parsetree->override,
35453565
rt_entry_relation,
3546-
parsetree->resultRelation,
3547-
NULL);
3566+
parsetree->resultRelation);
35483567
}
35493568
}
35503569
elseif (event==CMD_UPDATE)
@@ -3554,7 +3573,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
35543573
parsetree->commandType,
35553574
parsetree->override,
35563575
rt_entry_relation,
3557-
parsetree->resultRelation,NULL);
3576+
parsetree->resultRelation);
35583577
}
35593578
elseif (event==CMD_DELETE)
35603579
{
@@ -3599,7 +3618,8 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
35993618
RangeTblEntry*values_rte=rt_fetch(values_rte_index,
36003619
pt->rtable);
36013620

3602-
rewriteValuesRTE(pt,values_rte,rt_entry_relation,attrnos,
3621+
rewriteValuesRTE(pt,values_rte,values_rte_index,
3622+
rt_entry_relation,
36033623
true);/* Force remaining defaults to NULL */
36043624
}
36053625
}

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

Lines changed: 33 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -2791,6 +2791,7 @@ insert into base_tab_def_view values (12), (13);
27912791
insert into base_tab_def_view values (14, default, default, default, default);
27922792
insert into base_tab_def_view values (15, default, default, default, default),
27932793
(16, default, default, default, default);
2794+
insert into base_tab_def_view values (17), (default);
27942795
select * from base_tab_def order by a;
27952796
a | b | c | d | e
27962797
----+---------------+---------------+--------------+---
@@ -2806,7 +2807,9 @@ select * from base_tab_def order by a;
28062807
14 | View default | Table default | View default |
28072808
15 | View default | Table default | View default |
28082809
16 | View default | Table default | View default |
2809-
(12 rows)
2810+
17 | View default | Table default | View default |
2811+
| View default | Table default | View default |
2812+
(14 rows)
28102813

28112814
-- Adding an INSTEAD OF trigger should cause NULLs to be inserted instead of
28122815
-- table defaults, where there are no view defaults.
@@ -2832,6 +2835,7 @@ insert into base_tab_def_view values (12), (13);
28322835
insert into base_tab_def_view values (14, default, default, default, default);
28332836
insert into base_tab_def_view values (15, default, default, default, default),
28342837
(16, default, default, default, default);
2838+
insert into base_tab_def_view values (17), (default);
28352839
select * from base_tab_def order by a;
28362840
a | b | c | d | e
28372841
----+---------------+---------------+--------------+---
@@ -2847,7 +2851,9 @@ select * from base_tab_def order by a;
28472851
14 | View default | | View default |
28482852
15 | View default | | View default |
28492853
16 | View default | | View default |
2850-
(12 rows)
2854+
17 | View default | | View default |
2855+
| View default | | View default |
2856+
(14 rows)
28512857

28522858
-- Using an unconditional DO INSTEAD rule should also cause NULLs to be
28532859
-- inserted where there are no view defaults.
@@ -2866,6 +2872,7 @@ insert into base_tab_def_view values (12), (13);
28662872
insert into base_tab_def_view values (14, default, default, default, default);
28672873
insert into base_tab_def_view values (15, default, default, default, default),
28682874
(16, default, default, default, default);
2875+
insert into base_tab_def_view values (17), (default);
28692876
select * from base_tab_def order by a;
28702877
a | b | c | d | e
28712878
----+---------------+---------------+--------------+---
@@ -2881,7 +2888,9 @@ select * from base_tab_def order by a;
28812888
14 | View default | | View default |
28822889
15 | View default | | View default |
28832890
16 | View default | | View default |
2884-
(12 rows)
2891+
17 | View default | | View default |
2892+
| View default | | View default |
2893+
(14 rows)
28852894

28862895
-- A DO ALSO rule should cause each row to be inserted twice. The first
28872896
-- insert should behave the same as an auto-updatable view (using table
@@ -2902,6 +2911,7 @@ insert into base_tab_def_view values (12), (13);
29022911
insert into base_tab_def_view values (14, default, default, default, default);
29032912
insert into base_tab_def_view values (15, default, default, default, default),
29042913
(16, default, default, default, default);
2914+
insert into base_tab_def_view values (17), (default);
29052915
select * from base_tab_def order by a, c NULLS LAST;
29062916
a | b | c | d | e
29072917
----+---------------+---------------+--------------+---
@@ -2923,7 +2933,26 @@ select * from base_tab_def order by a, c NULLS LAST;
29232933
15 | View default | | View default |
29242934
16 | View default | Table default | View default |
29252935
16 | View default | | View default |
2926-
(18 rows)
2936+
17 | View default | Table default | View default |
2937+
17 | View default | | View default |
2938+
| View default | Table default | View default |
2939+
| View default | | View default |
2940+
(22 rows)
29272941

29282942
drop view base_tab_def_view;
29292943
drop table base_tab_def;
2944+
-- Test defaults with array assignments
2945+
create table base_tab (a serial, b int[], c text, d text default 'Table default');
2946+
create view base_tab_view as select c, a, b from base_tab;
2947+
alter view base_tab_view alter column c set default 'View default';
2948+
insert into base_tab_view (b[1], b[2], c, b[5], b[4], a, b[3])
2949+
values (1, 2, default, 5, 4, default, 3), (10, 11, 'C value', 14, 13, 100, 12);
2950+
select * from base_tab order by a;
2951+
a | b | c | d
2952+
-----+------------------+--------------+---------------
2953+
1 | {1,2,3,4,5} | View default | Table default
2954+
100 | {10,11,12,13,14} | C value | Table default
2955+
(2 rows)
2956+
2957+
drop view base_tab_view;
2958+
drop table base_tab;

‎src/test/regress/sql/updatable_views.sql

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1400,6 +1400,7 @@ insert into base_tab_def_view values (12), (13);
14001400
insert into base_tab_def_viewvalues (14, default, default, default, default);
14011401
insert into base_tab_def_viewvalues (15, default, default, default, default),
14021402
(16, default, default, default, default);
1403+
insert into base_tab_def_viewvalues (17), (default);
14031404
select*from base_tab_deforder by a;
14041405

14051406
-- Adding an INSTEAD OF trigger should cause NULLs to be inserted instead of
@@ -1426,6 +1427,7 @@ insert into base_tab_def_view values (12), (13);
14261427
insert into base_tab_def_viewvalues (14, default, default, default, default);
14271428
insert into base_tab_def_viewvalues (15, default, default, default, default),
14281429
(16, default, default, default, default);
1430+
insert into base_tab_def_viewvalues (17), (default);
14291431
select*from base_tab_deforder by a;
14301432

14311433
-- Using an unconditional DO INSTEAD rule should also cause NULLs to be
@@ -1445,6 +1447,7 @@ insert into base_tab_def_view values (12), (13);
14451447
insert into base_tab_def_viewvalues (14, default, default, default, default);
14461448
insert into base_tab_def_viewvalues (15, default, default, default, default),
14471449
(16, default, default, default, default);
1450+
insert into base_tab_def_viewvalues (17), (default);
14481451
select*from base_tab_deforder by a;
14491452

14501453
-- A DO ALSO rule should cause each row to be inserted twice. The first
@@ -1466,7 +1469,18 @@ insert into base_tab_def_view values (12), (13);
14661469
insert into base_tab_def_viewvalues (14, default, default, default, default);
14671470
insert into base_tab_def_viewvalues (15, default, default, default, default),
14681471
(16, default, default, default, default);
1472+
insert into base_tab_def_viewvalues (17), (default);
14691473
select*from base_tab_deforder by a, c NULLS LAST;
14701474

14711475
dropview base_tab_def_view;
14721476
droptable base_tab_def;
1477+
1478+
-- Test defaults with array assignments
1479+
createtablebase_tab (aserial, bint[], ctext, dtext default'Table default');
1480+
createviewbase_tab_viewasselect c, a, bfrom base_tab;
1481+
alterview base_tab_view alter column cset default'View default';
1482+
insert into base_tab_view (b[1], b[2], c, b[5], b[4], a, b[3])
1483+
values (1,2, default,5,4, default,3), (10,11,'C value',14,13,100,12);
1484+
select*from base_taborder by a;
1485+
dropview base_tab_view;
1486+
droptable base_tab;

0 commit comments

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