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

Commit75c7376

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 parent337903a commit75c7376

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
@@ -1604,7 +1604,6 @@ rewriteValuesRTEToNulls(Query *parsetree, RangeTblEntry *rte)
16041604
List*newValues;
16051605
ListCell*lc;
16061606

1607-
Assert(rte->rtekind==RTE_VALUES);
16081607
newValues=NIL;
16091608
foreach(lc,rte->values_lists)
16101609
{
@@ -3861,12 +3860,39 @@ RewriteQuery(Query *parsetree, List *rewrite_events, int orig_rt_length)
38613860
/*
38623861
* Each product query has its own copy of the VALUES RTE at the
38633862
* same index in the rangetable, so we must finalize each one.
3863+
*
3864+
* Note that if the product query is an INSERT ... SELECT, then
3865+
* the VALUES RTE will be at the same index in the SELECT part of
3866+
* the product query rather than the top-level product query
3867+
* itself.
38643868
*/
38653869
foreach(n,product_queries)
38663870
{
38673871
Query*pt= (Query*)lfirst(n);
3868-
RangeTblEntry*values_rte=rt_fetch(values_rte_index,
3869-
pt->rtable);
3872+
RangeTblEntry*values_rte;
3873+
3874+
if (pt->commandType==CMD_INSERT&&
3875+
pt->jointree&&IsA(pt->jointree,FromExpr)&&
3876+
list_length(pt->jointree->fromlist)==1)
3877+
{
3878+
Node*jtnode= (Node*)linitial(pt->jointree->fromlist);
3879+
3880+
if (IsA(jtnode,RangeTblRef))
3881+
{
3882+
intrtindex= ((RangeTblRef*)jtnode)->rtindex;
3883+
RangeTblEntry*src_rte=rt_fetch(rtindex,pt->rtable);
3884+
3885+
if (src_rte->rtekind==RTE_SUBQUERY&&
3886+
src_rte->subquery&&
3887+
IsA(src_rte->subquery,Query)&&
3888+
src_rte->subquery->commandType==CMD_SELECT)
3889+
pt=src_rte->subquery;
3890+
}
3891+
}
3892+
3893+
values_rte=rt_fetch(values_rte_index,pt->rtable);
3894+
if (values_rte->rtekind!=RTE_VALUES)
3895+
elog(ERROR,"failed to find VALUES RTE in product query");
38703896

38713897
rewriteValuesRTEToNulls(pt,values_rte);
38723898
}

‎src/backend/rewrite/rewriteManip.c

Lines changed: 7 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1012,12 +1012,15 @@ getInsertSelectQuery(Query *parsetree, Query ***subquery_ptr)
10121012
if (list_length(parsetree->jointree->fromlist)!=1)
10131013
elog(ERROR,"expected to find SELECT subquery");
10141014
rtr= (RangeTblRef*)linitial(parsetree->jointree->fromlist);
1015-
Assert(IsA(rtr,RangeTblRef));
1015+
if (!IsA(rtr,RangeTblRef))
1016+
elog(ERROR,"expected to find SELECT subquery");
10161017
selectrte=rt_fetch(rtr->rtindex,parsetree->rtable);
1017-
selectquery=selectrte->subquery;
1018-
if (!(selectquery&&IsA(selectquery,Query)&&
1019-
selectquery->commandType==CMD_SELECT))
1018+
if (!(selectrte->rtekind==RTE_SUBQUERY&&
1019+
selectrte->subquery&&
1020+
IsA(selectrte->subquery,Query)&&
1021+
selectrte->subquery->commandType==CMD_SELECT))
10201022
elog(ERROR,"expected to find SELECT subquery");
1023+
selectquery=selectrte->subquery;
10211024
if (list_length(selectquery->rtable) >=2&&
10221025
strcmp(rt_fetch(PRS2_OLD_VARNO,selectquery->rtable)->eref->aliasname,
10231026
"old")==0&&

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

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

3299+
-- Test a DO ALSO INSERT ... SELECT rule
3300+
drop rule base_tab_def_view_ins_rule on base_tab_def_view;
3301+
create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view
3302+
do also insert into base_tab_def (a, b, e) select new.a, new.b, 'xxx';
3303+
truncate base_tab_def;
3304+
insert into base_tab_def_view values (1, default, default, default, default);
3305+
insert into base_tab_def_view values (2, default, default, default, default),
3306+
(3, default, default, default, default);
3307+
select * from base_tab_def order by a, e nulls first;
3308+
a | b | c | d | e
3309+
---+--------------+---------------+--------------+-----
3310+
1 | View default | Table default | View default |
3311+
1 | View default | Table default | | xxx
3312+
2 | View default | Table default | View default |
3313+
2 | View default | Table default | | xxx
3314+
3 | View default | Table default | View default |
3315+
3 | View default | Table default | | xxx
3316+
(6 rows)
3317+
32993318
drop view base_tab_def_view;
33003319
drop table base_tab_def;
33013320
-- 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
@@ -1717,6 +1717,16 @@ insert into base_tab_def_view values (15, default, default, default, default),
17171717
insert into base_tab_def_viewvalues (17), (default);
17181718
select*from base_tab_deforder by a, c NULLS LAST;
17191719

1720+
-- Test a DO ALSO INSERT ... SELECT rule
1721+
droprule base_tab_def_view_ins_ruleon base_tab_def_view;
1722+
createrulebase_tab_def_view_ins_ruleason insert to base_tab_def_view
1723+
do alsoinsert into base_tab_def (a, b, e)selectnew.a,new.b,'xxx';
1724+
truncate base_tab_def;
1725+
insert into base_tab_def_viewvalues (1, default, default, default, default);
1726+
insert into base_tab_def_viewvalues (2, default, default, default, default),
1727+
(3, default, default, default, default);
1728+
select*from base_tab_deforder by a, e nulls first;
1729+
17201730
dropview base_tab_def_view;
17211731
droptable base_tab_def;
17221732

0 commit comments

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