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

Commit0a08446

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 parentab1d9f0 commit0a08446

File tree

3 files changed

+107
-44
lines changed

3 files changed

+107
-44
lines changed

‎src/backend/rewrite/rewriteHandler.c

Lines changed: 60 additions & 40 deletions
Original file line numberDiff line numberDiff line change
@@ -65,14 +65,13 @@ static List *rewriteTargetListIU(List *targetList,
6565
CmdTypecommandType,
6666
OverridingKindoverride,
6767
Relationtarget_relation,
68-
intresult_rti,
69-
List**attrno_list);
68+
intresult_rti);
7069
staticTargetEntry*process_matched_tle(TargetEntry*src_tle,
7170
TargetEntry*prior_tle,
7271
constchar*attrName);
7372
staticNode*get_assignment_input(Node*node);
74-
staticboolrewriteValuesRTE(Query*parsetree,RangeTblEntry*rte,
75-
Relationtarget_relation,List*attrnos,boolforce_nulls);
73+
staticboolrewriteValuesRTE(Query*parsetree,RangeTblEntry*rte,intrti,
74+
Relationtarget_relation,boolforce_nulls);
7675
staticvoidmarkQueryForLocking(Query*qry,Node*jtnode,
7776
LockClauseStrengthstrength,LockWaitPolicywaitPolicy,
7877
boolpushedDown);
@@ -702,25 +701,19 @@ adjustJoinTreeList(Query *parsetree, bool removert, int rt_index)
702701
* is not needed for rewriting, but will be needed by the planner, and we
703702
* can do it essentially for free while handling the other items.
704703
*
705-
* If attrno_list isn't NULL, we return an additional output besides the
706-
* rewritten targetlist: an integer list of the assigned-to attnums, in
707-
* order of the original tlist's non-junk entries. This is needed for
708-
* processing VALUES RTEs.
709-
*
710704
* Note that for an inheritable UPDATE, this processing is only done once,
711705
* using the parent relation as reference. It must not do anything that
712706
* will not be correct when transposed to the child relation(s). (Step 4
713707
* is incorrect by this light, since child relations might have different
714-
*colun ordering, but the planner will fix things by re-sorting the tlist
708+
*column ordering, but the planner will fix things by re-sorting the tlist
715709
* for each child.)
716710
*/
717711
staticList*
718712
rewriteTargetListIU(List*targetList,
719713
CmdTypecommandType,
720714
OverridingKindoverride,
721715
Relationtarget_relation,
722-
intresult_rti,
723-
List**attrno_list)
716+
intresult_rti)
724717
{
725718
TargetEntry**new_tles;
726719
List*new_tlist=NIL;
@@ -731,9 +724,6 @@ rewriteTargetListIU(List *targetList,
731724
numattrs;
732725
ListCell*temp;
733726

734-
if (attrno_list)/* initialize optional result list */
735-
*attrno_list=NIL;
736-
737727
/*
738728
* We process the normal (non-junk) attributes by scanning the input tlist
739729
* once and transferring TLEs into an array, then scanning the array to
@@ -759,10 +749,6 @@ rewriteTargetListIU(List *targetList,
759749
elog(ERROR,"bogus resno %d in targetlist",attrno);
760750
att_tup=target_relation->rd_att->attrs[attrno-1];
761751

762-
/* put attrno into attrno_list even if it's dropped */
763-
if (attrno_list)
764-
*attrno_list=lappend_int(*attrno_list,attrno);
765-
766752
/* We can (and must) ignore deleted attributes */
767753
if (att_tup->attisdropped)
768754
continue;
@@ -1234,22 +1220,26 @@ searchForDefault(RangeTblEntry *rte)
12341220
* an insert into an auto-updatable view, and the product queries are inserts
12351221
* into a rule-updatable view.
12361222
*
1237-
* Note that we currently can't support subscripted or field assignment
1238-
* in the multi-VALUES case. The targetlist will contain simple Vars
1239-
* referencing the VALUES RTE, and therefore process_matched_tle() will
1240-
* reject any such attempt with "multiple assignments to same column".
1223+
* Note that we may have subscripted or field assignment targetlist entries,
1224+
* as well as more complex expressions from already-replaced DEFAULT items if
1225+
* we have recursed to here for an auto-updatable view. However, it ought to
1226+
* be impossible for such entries to have DEFAULTs assigned to them --- we
1227+
* should only have to replace DEFAULT items for targetlist entries that
1228+
* contain simple Vars referencing the VALUES RTE.
12411229
*
12421230
* Returns true if all DEFAULT items were replaced, and false if some were
12431231
* left untouched.
12441232
*/
12451233
staticbool
1246-
rewriteValuesRTE(Query*parsetree,RangeTblEntry*rte,
1247-
Relationtarget_relation,List*attrnos,boolforce_nulls)
1234+
rewriteValuesRTE(Query*parsetree,RangeTblEntry*rte,intrti,
1235+
Relationtarget_relation,boolforce_nulls)
12481236
{
12491237
List*newValues;
12501238
ListCell*lc;
12511239
boolisAutoUpdatableView;
12521240
boolallReplaced;
1241+
intnumattrs;
1242+
int*attrnos;
12531243

12541244
/*
12551245
* Rebuilding all the lists is a pretty expensive proposition in a big
@@ -1262,8 +1252,33 @@ rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte,
12621252
if (!force_nulls&& !searchForDefault(rte))
12631253
return true;/* nothing to do */
12641254

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

12681283
/*
12691284
* Check if the target relation is an auto-updatable view, in which case
@@ -1314,18 +1329,23 @@ rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte,
13141329
List*sublist= (List*)lfirst(lc);
13151330
List*newList=NIL;
13161331
ListCell*lc2;
1317-
ListCell*lc3;
1332+
inti;
1333+
1334+
Assert(list_length(sublist)==numattrs);
13181335

1319-
forboth(lc2,sublist,lc3,attrnos)
1336+
i=0;
1337+
foreach(lc2,sublist)
13201338
{
13211339
Node*col= (Node*)lfirst(lc2);
1322-
intattrno=lfirst_int(lc3);
1340+
intattrno=attrnos[i++];
13231341

13241342
if (IsA(col,SetToDefault))
13251343
{
13261344
Form_pg_attributeatt_tup;
13271345
Node*new_expr;
13281346

1347+
if (attrno==0)
1348+
elog(ERROR,"cannot set value in column %d to DEFAULT",i);
13291349
att_tup=target_relation->rd_att->attrs[attrno-1];
13301350

13311351
if (!force_nulls&& !att_tup->attisdropped)
@@ -1373,6 +1393,8 @@ rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte,
13731393
}
13741394
rte->values_lists=newValues;
13751395

1396+
pfree(attrnos);
1397+
13761398
returnallReplaced;
13771399
}
13781400

@@ -3446,7 +3468,6 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
34463468
List*locks;
34473469
List*product_queries;
34483470
boolhasUpdate= false;
3449-
List*attrnos=NIL;
34503471
intvalues_rte_index=0;
34513472
booldefaults_remaining= false;
34523473

@@ -3496,11 +3517,10 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
34963517
parsetree->commandType,
34973518
parsetree->override,
34983519
rt_entry_relation,
3499-
parsetree->resultRelation,
3500-
&attrnos);
3520+
parsetree->resultRelation);
35013521
/* ... and the VALUES expression lists */
3502-
if (!rewriteValuesRTE(parsetree,values_rte,
3503-
rt_entry_relation,attrnos,false))
3522+
if (!rewriteValuesRTE(parsetree,values_rte,values_rte_index,
3523+
rt_entry_relation, false))
35043524
defaults_remaining= true;
35053525
}
35063526
else
@@ -3511,7 +3531,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
35113531
parsetree->commandType,
35123532
parsetree->override,
35133533
rt_entry_relation,
3514-
parsetree->resultRelation,NULL);
3534+
parsetree->resultRelation);
35153535
}
35163536

35173537
if (parsetree->onConflict&&
@@ -3522,8 +3542,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
35223542
CMD_UPDATE,
35233543
parsetree->override,
35243544
rt_entry_relation,
3525-
parsetree->resultRelation,
3526-
NULL);
3545+
parsetree->resultRelation);
35273546
}
35283547
}
35293548
elseif (event==CMD_UPDATE)
@@ -3533,7 +3552,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
35333552
parsetree->commandType,
35343553
parsetree->override,
35353554
rt_entry_relation,
3536-
parsetree->resultRelation,NULL);
3555+
parsetree->resultRelation);
35373556
}
35383557
elseif (event==CMD_DELETE)
35393558
{
@@ -3578,7 +3597,8 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
35783597
RangeTblEntry*values_rte=rt_fetch(values_rte_index,
35793598
pt->rtable);
35803599

3581-
rewriteValuesRTE(pt,values_rte,rt_entry_relation,attrnos,
3600+
rewriteValuesRTE(pt,values_rte,values_rte_index,
3601+
rt_entry_relation,
35823602
true);/* Force remaining defaults to NULL */
35833603
}
35843604
}

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

Lines changed: 33 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -2665,6 +2665,7 @@ insert into base_tab_def_view values (12), (13);
26652665
insert into base_tab_def_view values (14, default, default, default, default);
26662666
insert into base_tab_def_view values (15, default, default, default, default),
26672667
(16, default, default, default, default);
2668+
insert into base_tab_def_view values (17), (default);
26682669
select * from base_tab_def order by a;
26692670
a | b | c | d | e
26702671
----+---------------+---------------+--------------+---
@@ -2680,7 +2681,9 @@ select * from base_tab_def order by a;
26802681
14 | View default | Table default | View default |
26812682
15 | View default | Table default | View default |
26822683
16 | View default | Table default | View default |
2683-
(12 rows)
2684+
17 | View default | Table default | View default |
2685+
| View default | Table default | View default |
2686+
(14 rows)
26842687

26852688
-- Adding an INSTEAD OF trigger should cause NULLs to be inserted instead of
26862689
-- table defaults, where there are no view defaults.
@@ -2706,6 +2709,7 @@ insert into base_tab_def_view values (12), (13);
27062709
insert into base_tab_def_view values (14, default, default, default, default);
27072710
insert into base_tab_def_view values (15, default, default, default, default),
27082711
(16, default, default, default, default);
2712+
insert into base_tab_def_view values (17), (default);
27092713
select * from base_tab_def order by a;
27102714
a | b | c | d | e
27112715
----+---------------+---------------+--------------+---
@@ -2721,7 +2725,9 @@ select * from base_tab_def order by a;
27212725
14 | View default | | View default |
27222726
15 | View default | | View default |
27232727
16 | View default | | View default |
2724-
(12 rows)
2728+
17 | View default | | View default |
2729+
| View default | | View default |
2730+
(14 rows)
27252731

27262732
-- Using an unconditional DO INSTEAD rule should also cause NULLs to be
27272733
-- inserted where there are no view defaults.
@@ -2740,6 +2746,7 @@ insert into base_tab_def_view values (12), (13);
27402746
insert into base_tab_def_view values (14, default, default, default, default);
27412747
insert into base_tab_def_view values (15, default, default, default, default),
27422748
(16, default, default, default, default);
2749+
insert into base_tab_def_view values (17), (default);
27432750
select * from base_tab_def order by a;
27442751
a | b | c | d | e
27452752
----+---------------+---------------+--------------+---
@@ -2755,7 +2762,9 @@ select * from base_tab_def order by a;
27552762
14 | View default | | View default |
27562763
15 | View default | | View default |
27572764
16 | View default | | View default |
2758-
(12 rows)
2765+
17 | View default | | View default |
2766+
| View default | | View default |
2767+
(14 rows)
27592768

27602769
-- A DO ALSO rule should cause each row to be inserted twice. The first
27612770
-- insert should behave the same as an auto-updatable view (using table
@@ -2776,6 +2785,7 @@ insert into base_tab_def_view values (12), (13);
27762785
insert into base_tab_def_view values (14, default, default, default, default);
27772786
insert into base_tab_def_view values (15, default, default, default, default),
27782787
(16, default, default, default, default);
2788+
insert into base_tab_def_view values (17), (default);
27792789
select * from base_tab_def order by a, c NULLS LAST;
27802790
a | b | c | d | e
27812791
----+---------------+---------------+--------------+---
@@ -2797,7 +2807,26 @@ select * from base_tab_def order by a, c NULLS LAST;
27972807
15 | View default | | View default |
27982808
16 | View default | Table default | View default |
27992809
16 | View default | | View default |
2800-
(18 rows)
2810+
17 | View default | Table default | View default |
2811+
17 | View default | | View default |
2812+
| View default | Table default | View default |
2813+
| View default | | View default |
2814+
(22 rows)
28012815

28022816
drop view base_tab_def_view;
28032817
drop table base_tab_def;
2818+
-- Test defaults with array assignments
2819+
create table base_tab (a serial, b int[], c text, d text default 'Table default');
2820+
create view base_tab_view as select c, a, b from base_tab;
2821+
alter view base_tab_view alter column c set default 'View default';
2822+
insert into base_tab_view (b[1], b[2], c, b[5], b[4], a, b[3])
2823+
values (1, 2, default, 5, 4, default, 3), (10, 11, 'C value', 14, 13, 100, 12);
2824+
select * from base_tab order by a;
2825+
a | b | c | d
2826+
-----+------------------+--------------+---------------
2827+
1 | {1,2,3,4,5} | View default | Table default
2828+
100 | {10,11,12,13,14} | C value | Table default
2829+
(2 rows)
2830+
2831+
drop view base_tab_view;
2832+
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
@@ -1321,6 +1321,7 @@ insert into base_tab_def_view values (12), (13);
13211321
insert into base_tab_def_viewvalues (14, default, default, default, default);
13221322
insert into base_tab_def_viewvalues (15, default, default, default, default),
13231323
(16, default, default, default, default);
1324+
insert into base_tab_def_viewvalues (17), (default);
13241325
select*from base_tab_deforder by a;
13251326

13261327
-- Adding an INSTEAD OF trigger should cause NULLs to be inserted instead of
@@ -1347,6 +1348,7 @@ insert into base_tab_def_view values (12), (13);
13471348
insert into base_tab_def_viewvalues (14, default, default, default, default);
13481349
insert into base_tab_def_viewvalues (15, default, default, default, default),
13491350
(16, default, default, default, default);
1351+
insert into base_tab_def_viewvalues (17), (default);
13501352
select*from base_tab_deforder by a;
13511353

13521354
-- Using an unconditional DO INSTEAD rule should also cause NULLs to be
@@ -1366,6 +1368,7 @@ insert into base_tab_def_view values (12), (13);
13661368
insert into base_tab_def_viewvalues (14, default, default, default, default);
13671369
insert into base_tab_def_viewvalues (15, default, default, default, default),
13681370
(16, default, default, default, default);
1371+
insert into base_tab_def_viewvalues (17), (default);
13691372
select*from base_tab_deforder by a;
13701373

13711374
-- A DO ALSO rule should cause each row to be inserted twice. The first
@@ -1387,7 +1390,18 @@ insert into base_tab_def_view values (12), (13);
13871390
insert into base_tab_def_viewvalues (14, default, default, default, default);
13881391
insert into base_tab_def_viewvalues (15, default, default, default, default),
13891392
(16, default, default, default, default);
1393+
insert into base_tab_def_viewvalues (17), (default);
13901394
select*from base_tab_deforder by a, c NULLS LAST;
13911395

13921396
dropview base_tab_def_view;
13931397
droptable base_tab_def;
1398+
1399+
-- Test defaults with array assignments
1400+
createtablebase_tab (aserial, bint[], ctext, dtext default'Table default');
1401+
createviewbase_tab_viewasselect c, a, bfrom base_tab;
1402+
alterview base_tab_view alter column cset default'View default';
1403+
insert into base_tab_view (b[1], b[2], c, b[5], b[4], a, b[3])
1404+
values (1,2, default,5,4, default,3), (10,11,'C value',14,13,100,12);
1405+
select*from base_taborder by a;
1406+
dropview base_tab_view;
1407+
droptable base_tab;

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp