8000 Fix multi-row DEFAULT handling for INSERT ... SELECT rules. · postgres/postgres@e68b133 · GitHub
[go: up one dir, main page]
Skip to content

Commit e68b133

Browse files
deanrasheeddeanrasheed
committed
Fix multi-row DEFAULT handling for INSERT ... SELECT rules.
Given an updatable view with a DO ALSO INSERT ... SELECT rule, a multi-row INSERT ... VALUES query on the view fails if the VALUES list contains any DEFAULTs that are not replaced by view defaults. This manifests as an "unrecognized node type" error, or an Assert failure, in an assert-enabled build. The reason is that when RewriteQuery() attempts to replace the remaining DEFAULT items with NULLs in any product queries, using rewriteValuesRTEToNulls(), it assumes that the VALUES RTE is located at the same rangetable index in each product query. However, if the product query is an INSERT ... SELECT, then the VALUES RTE is actually in the SELECT part of that query (at the same index), rather than the top-level product query itself. Fix, by descending to the SELECT in such cases. Note that we can't simply use getInsertSelectQuery() for this, since that expects to be given a raw rule action with OLD and NEW placeholder entries, so we duplicate its logic instead. While at it, be 8000 ef up the checks in getInsertSelectQuery() by checking that the jointree->fromlist node is indeed a RangeTblRef, and that the RTE it points to has rtekind == RTE_SUBQUERY. Per bug #17803, from Alexander Lakhin. Back-patch to all supported branches. Dean Rasheed, reviewed by Tom Lane. Discussion: https://postgr.es/m/17803-53c63ed4ecb4eac6%40postgresql.org
1 parent 8de91eb commit e68b133

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
@@ -1432,7 +1432,6 @@ rewriteValuesRTEToNulls(Query *parsetree, RangeTblEntry *rte)
14321432
List *newValues;
14331433
ListCell *lc;
14341434

1435-
Assert(rte->rtekind == RTE_VALUES);
14361435
newValues = NIL;
14371436
foreach(lc, rte->values_lists)
14381437
{
@@ -3704,12 +3703,39 @@ RewriteQuery(Query *parsetree, List *rewrite_events, int orig_rt_length)
37043703
/*
37053704
* Each product query has its own copy of the VALUES RTE at the
37063705
* same index in the rangetable, so we must finalize each one.
3706+
*
3707+
* Note that if the product query is an INSERT ... SELECT, then
3708+
* the VALUES RTE will be at the same index in the SELECT part of
3709+
* the product query rather than the top-level product query
3710+
* itself.
37073711
*/
37083712
foreach(n, product_queries)
37093713
{
37103714
Query *pt = (Query *) lfirst(n);
3711-
RangeTblEntry *values_rte = rt_fetch(values_rte_index,
3712-
pt->rtable);
3715+
RangeTblEntry *values_rte;
3716+
3717+
if (pt->commandType == CMD_INSERT &&
3718+
pt->jointree && IsA(pt->jointree, FromExpr) &&
3719+
list_length(pt->jointree->fromlist) == 1)
3720+
{
3721+
Node *jtnode = (Node *) linitial(pt->jointree->fromlist);
3722+
3723+
if (IsA(jtnode, RangeTblRef))
3724+
{
3725+
int rtindex = ((RangeTblRef *) jtnode)->rtindex;
3726+
RangeTblEntry *src_rte = rt_fetch(rtindex, pt->rtable);
3727+
3728+
if (src_rte->rtekind == RTE_SUBQUERY &&
3729+
src_rte->subquery &&
3730+
IsA(src_rte->subquery, Query) &&
3731+
src_rte->subquery->commandType == CMD_SELECT)
3732+
pt = src_rte->subquery;
3733+
}
3734+
}
3735+
3736+
values_rte = rt_fetch(values_rte_index, pt->rtable);
3737+
if (values_rte->rtekind != RTE_VALUES)
3738+
elog(ERROR, "failed to find VALUES RTE in product query");
37133739

37143740
rewriteValuesRTEToNulls(pt, values_rte);
37153741
}

‎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
@@ -2980,6 +2980,25 @@ select * from base_tab_def order by a, c NULLS LAST;
29802980
| View default | | View default |
29812981
(22 rows)
29822982

2983+
-- Test a DO ALSO INSERT ... SELECT rule
2984+
drop rule base_tab_def_view_ins_rule on base_tab_def_view;
2985+
create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view
2986+
do also insert into base_tab_def (a, b, e) select new.a, new.b, 'xxx';
2987+
truncate base_tab_def;
2988+
insert into base_tab_def_view values (1, default, default, default, default);
2989+
insert into base_tab_def_view values (2, default, default, default, default),
2990+
(3, default, default, default, default);
2991+
select * from base_tab_def order by a, e nulls first;
2992+
a | b | c | d | e
2993+
---+--------------+---------------+--------------+-----
2994+
1 | View default | Table default | View default |
2995+
1 | View default | Table default | | xxx
2996+
2 | View default | Table default | View default |
2997+
2 | View default | Table default | | xxx
2998+
3 | View default | Table default | View default |
2999+
3 | View default | Table default | | xxx
3000+
(6 rows)
3001+
29833002
drop view base_tab_def_view;
29843003
drop table base_tab_def;
29853004
-- 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
@@ -1500,6 +1500,16 @@ insert into base_tab_def_view values (15, default, default, default, default),
15001500
insert into base_tab_def_view values (17), (default);
15011501
select * from base_tab_def order by a, c NULLS LAST;
15021502

1503+
-- Test a DO ALSO INSERT ... SELECT rule
1504+
drop rule base_tab_def_view_ins_rule on base_tab_def_view;
1505+
create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view
1506+
do also insert into base_tab_def (a, b, e) select new.a, new.b, 'xxx';
1507+
truncate base_tab_def;
1508+
insert into base_tab_def_view values (1, default, default, default, default);
1509+
insert into base_tab_def_view values (2, default, default, default, defau 580E lt),
1510+
(3, default, default, default, default);
1511+
select * from base_tab_def order by a, e nulls first;
1512+
15031513
drop view base_tab_def_view;
15041514
drop table base_tab_def;
15051515

0 commit comments

Comments
 (0)
0