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

Commit98b83b7

Browse files
committed
Fix multi-row DEFAULT handling for INSERT ... SELECT rules.
Given an updatable view with a DO ALSO INSERT ... SELECT rule, amulti-row INSERT ... VALUES query on the view fails if the VALUES listcontains any DEFAULTs that are not replaced by view defaults. Thismanifests as an "unrecognized node type" error, or an Assert failure,in an assert-enabled build.The reason is that when RewriteQuery() attempts to replace theremaining DEFAULT items with NULLs in any product queries, usingrewriteValuesRTEToNulls(), it assumes that the VALUES RTE is locatedat the same rangetable index in each product query. However, if theproduct query is an INSERT ... SELECT, then the VALUES RTE is actuallyin the SELECT part of that query (at the same index), rather than thetop-level product query itself.Fix, by descending to the SELECT in such cases. Note that we can'tsimply use getInsertSelectQuery() for this, since that expects to begiven a raw rule action with OLD and NEW placeholder entries, so weduplicate its logic instead.While at it, beef up the checks in getInsertSelectQuery() by checkingthat the jointree->fromlist node is indeed a RangeTblRef, and that theRTE it points to has rtekind == RTE_SUBQUERY.Per bug #17803, from Alexander Lakhin. Back-patch to all supportedbranches.Dean Rasheed, reviewed by Tom Lane.Discussion:https://postgr.es/m/17803-53c63ed4ecb4eac6%40postgresql.org
1 parent497f863 commit98b83b7

File tree

4 files changed

+65
-7
lines changed

4 files changed

+65
-7
lines changed

‎src/backend/rewrite/rewriteHandler.c

Lines changed: 29 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1459,7 +1459,6 @@ rewriteValuesRTEToNulls(Query *parsetree, RangeTblEntry *rte)
14591459
List*newValues;
14601460
ListCell*lc;
14611461

1462-
Assert(rte->rtekind==RTE_VALUES);
14631462
newValues=NIL;
14641463
foreach(lc,rte->values_lists)
14651464
{
@@ -3783,12 +3782,39 @@ RewriteQuery(Query *parsetree, List *rewrite_events, int orig_rt_length)
37833782
/*
37843783
* Each product query has its own copy of the VALUES RTE at the
37853784
* same index in the rangetable, so we must finalize each one.
3785+
*
3786+
* Note that if the product query is an INSERT ... SELECT, then
3787+
* the VALUES RTE will be at the same index in the SELECT part of
3788+
* the product query rather than the top-level product query
3789+
* itself.
37863790
*/
37873791
foreach(n,product_queries)
37883792
{
37893793
Query*pt= (Query*)lfirst(n);
3790-
RangeTblEntry*values_rte=rt_fetch(values_rte_index,
3791-
pt->rtable);
3794+
RangeTblEntry*values_rte;
3795+
3796+
if (pt->commandType==CMD_INSERT&&
3797+
pt->jointree&&IsA(pt->jointree,FromExpr)&&
3798+
list_length(pt->jointree->fromlist)==1)
3799+
{
3800+
Node*jtnode= (Node*)linitial(pt->jointree->fromlist);
3801+
3802+
if (IsA(jtnode,RangeTblRef))
3803+
{
3804+
intrtindex= ((RangeTblRef*)jtnode)->rtindex;
3805+
RangeTblEntry*src_rte=rt_fetch(rtindex,pt->rtable);
3806+
3807+
if (src_rte->rtekind==RTE_SUBQUERY&&
3808+
src_rte->subquery&&
3809+
IsA(src_rte->subquery,Query)&&
3810+
src_rte->subquery->commandType==CMD_SELECT)
3811+
pt=src_rte->subquery;
3812+
}
3813+
}
3814+
3815+
values_rte=rt_fetch(values_rte_index,pt->rtable);
3816+
if (values_rte->rtekind!=RTE_VALUES)
3817+
elog(ERROR,"failed to find VALUES RTE in product query");
37923818

37933819
rewriteValuesRTEToNulls(pt,values_rte);
37943820
}

‎src/backend/rewrite/rewriteManip.c

Lines changed: 7 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -948,12 +948,15 @@ getInsertSelectQuery(Query *parsetree, Query ***subquery_ptr)
948948
if (list_length(parsetree->jointree->fromlist)!=1)
949949
elog(ERROR,"expected to find SELECT subquery");
950950
rtr= (RangeTblRef*)linitial(parsetree->jointree->fromlist);
951-
Assert(IsA(rtr,RangeTblRef));
951+
if (!IsA(rtr,RangeTblRef))
952+
elog(ERROR,"expected to find SELECT subquery");
952953
selectrte=rt_fetch(rtr->rtindex,parsetree->rtable);
953-
selectquery=selectrte->subquery;
954-
if (!(selectquery&&IsA(selectquery,Query)&&
955-
selectquery->commandType==CMD_SELECT))
954+
if (!(selectrte->rtekind==RTE_SUBQUERY&&
955+
selectrte->subquery&&
956+
IsA(selectrte->subquery,Query)&&
957+
selectrte->subquery->commandType==CMD_SELECT))
956958
elog(ERROR,"expected to find SELECT subquery");
959+
selectquery=selectrte->subquery;
957960
if (list_length(selectquery->rtable) >=2&&
958961
strcmp(rt_fetch(PRS2_OLD_VARNO,selectquery->rtable)->eref->aliasname,
959962
"old")==0&&

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

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3074,6 +3074,25 @@ select * from base_tab_def order by a, c NULLS LAST;
30743074
| View default | | View default |
30753075
(22 rows)
30763076

3077+
-- Test a DO ALSO INSERT ... SELECT rule
3078+
drop rule base_tab_def_view_ins_rule on base_tab_def_view;
3079+
create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view
3080+
do also insert into base_tab_def (a, b, e) select new.a, new.b, 'xxx';
3081+
truncate base_tab_def;
3082+
insert into base_tab_def_view values (1, default, default, default, default);
3083+
insert into base_tab_def_view values (2, default, default, default, default),
3084+
(3, default, default, default, default);
3085+
select * from base_tab_def order by a, e nulls first;
3086+
a | b | c | d | e
3087+
---+--------------+---------------+--------------+-----
3088+
1 | View default | Table default | View default |
3089+
1 | View default | Table default | | xxx
3090+
2 | View default | Table default | View default |
3091+
2 | View default | Table default | | xxx
3092+
3 | View default | Table default | View default |
3093+
3 | View default | Table default | | xxx
3094+
(6 rows)
3095+
30773096
drop view base_tab_def_view;
30783097
drop table base_tab_def;
30793098
-- Test defaults with array assignments

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

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1537,6 +1537,16 @@ insert into base_tab_def_view values (15, default, default, default, default),
15371537
insert into base_tab_def_viewvalues (17), (default);
15381538
select*from base_tab_deforder by a, c NULLS LAST;
15391539

1540+
-- Test a DO ALSO INSERT ... SELECT rule
1541+
droprule base_tab_def_view_ins_ruleon base_tab_def_view;
1542+
createrulebase_tab_def_view_ins_ruleason insert to base_tab_def_view
1543+
do alsoinsert into base_tab_def (a, b, e)selectnew.a,new.b,'xxx';
1544+
truncate base_tab_def;
1545+
insert into base_tab_def_viewvalues (1, default, default, default, default);
1546+
insert into base_tab_def_viewvalues (2, default, default, default, default),
1547+
(3, default, default, default, default);
1548+
select*from base_tab_deforder by a, e nulls first;
1549+
15401550
dropview base_tab_def_view;
15411551
droptable base_tab_def;
15421552

0 commit comments

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