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

Commit11520f1

Browse files
deanrasheedpull[bot]
authored andcommitted
Fix DEFAULT handling for multi-row INSERT rules.
When updating a relation with a rule whose action performed an INSERTfrom a multi-row VALUES list, the rewriter might skip processing theVALUES list, and therefore fail to replace any DEFAULTs in it. Thiswould lead to an "unrecognized node type" error.The reason was that RewriteQuery() assumed that a query doing anINSERT from a multi-row VALUES list would necessarily only have oneitem in its fromlist, pointing to the VALUES RTE to read from. Thatassumption is correct for the original query, but not for productqueries produced for rule actions. In such cases, there may bemultiple items in the fromlist, possibly including multiple VALUESRTEs.What is required instead is for RewriteQuery() to skip any RTEs fromthe product query's originating query, which might include one or morealready-processed VALUES RTEs. What's left should then include at mostone VALUES RTE (from the rule action) to be processed.Patch by me. Thanks to Tom Lane for reviewing.Back-patch to all supported branches.Discussion:https://postgr.es/m/CAEZATCV39OOW7LAR_Xq4i%2BLc1Byux%3DeK3Q%3DHD_pF1o9LBt%3DphA%40mail.gmail.com
1 parent44ed3af commit11520f1

File tree

3 files changed

+104
-55
lines changed

3 files changed

+104
-55
lines changed

‎src/backend/rewrite/rewriteHandler.c

Lines changed: 43 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -418,6 +418,10 @@ rewriteRuleAction(Query *parsetree,
418418
* NOTE: because planner will destructively alter rtable, we must ensure
419419
* that rule action's rtable is separate and shares no substructure with
420420
* the main rtable. Hence do a deep copy here.
421+
*
422+
* Note also that RewriteQuery() relies on the fact that RT entries from
423+
* the original query appear at the start of the expanded rtable, so
424+
* beware of changing this.
421425
*/
422426
sub_action->rtable=list_concat(copyObject(parsetree->rtable),
423427
sub_action->rtable);
@@ -3622,9 +3626,13 @@ rewriteTargetView(Query *parsetree, Relation view)
36223626
*
36233627
* rewrite_events is a list of open query-rewrite actions, so we can detect
36243628
* infinite recursion.
3629+
*
3630+
* orig_rt_length is the length of the originating query's rtable, for product
3631+
* queries created by fireRules(), and 0 otherwise. This is used to skip any
3632+
* already-processed VALUES RTEs from the original query.
36253633
*/
36263634
staticList*
3627-
RewriteQuery(Query*parsetree,List*rewrite_events)
3635+
RewriteQuery(Query*parsetree,List*rewrite_events,intorig_rt_length)
36283636
{
36293637
CmdTypeevent=parsetree->commandType;
36303638
boolinstead= false;
@@ -3648,7 +3656,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
36483656
if (ctequery->commandType==CMD_SELECT)
36493657
continue;
36503658

3651-
newstuff=RewriteQuery(ctequery,rewrite_events);
3659+
newstuff=RewriteQuery(ctequery,rewrite_events,0);
36523660

36533661
/*
36543662
* Currently we can only handle unconditional, single-statement DO
@@ -3722,6 +3730,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
37223730
RangeTblEntry*rt_entry;
37233731
Relationrt_entry_relation;
37243732
List*locks;
3733+
intproduct_orig_rt_length;
37253734
List*product_queries;
37263735
boolhasUpdate= false;
37273736
intvalues_rte_index=0;
@@ -3743,23 +3752,30 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
37433752
*/
37443753
if (event==CMD_INSERT)
37453754
{
3755+
ListCell*lc2;
37463756
RangeTblEntry*values_rte=NULL;
37473757

37483758
/*
3749-
* If it's an INSERT ... VALUES (...), (...), ... there will be a
3750-
* single RTE for the VALUES targetlists.
3759+
* Test if it's a multi-row INSERT ... VALUES (...), (...), ... by
3760+
* looking for a VALUES RTE in the fromlist. For product queries,
3761+
* we must ignore any already-processed VALUES RTEs from the
3762+
* original query. These appear at the start of the rangetable.
37513763
*/
3752-
if (list_length(parsetree->jointree->fromlist)==1)
3764+
foreach(lc2,parsetree->jointree->fromlist)
37533765
{
3754-
RangeTblRef*rtr= (RangeTblRef*)linitial(parsetree->jointree->fromlist);
3766+
RangeTblRef*rtr= (RangeTblRef*)lfirst(lc2);
37553767

3756-
if (IsA(rtr,RangeTblRef))
3768+
if (IsA(rtr,RangeTblRef)&&rtr->rtindex>orig_rt_length)
37573769
{
37583770
RangeTblEntry*rte=rt_fetch(rtr->rtindex,
37593771
parsetree->rtable);
37603772

37613773
if (rte->rtekind==RTE_VALUES)
37623774
{
3775+
/* should not find more than one VALUES RTE */
3776+
if (values_rte!=NULL)
3777+
elog(ERROR,"more than one VALUES RTE found");
3778+
37633779
values_rte=rte;
37643780
values_rte_index=rtr->rtindex;
37653781
}
@@ -3837,7 +3853,11 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
38373853
break;
38383854
caseCMD_UPDATE:
38393855
caseCMD_INSERT:
3840-
/* XXX is it possible to have a VALUES clause? */
3856+
3857+
/*
3858+
* MERGE actions do not permit multi-row INSERTs, so
3859+
* there is no VALUES RTE to deal with here.
3860+
*/
38413861
action->targetList=
38423862
rewriteTargetListIU(action->targetList,
38433863
action->commandType,
@@ -3864,6 +3884,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
38643884
locks=matchLocks(event,rt_entry_relation->rd_rules,
38653885
result_relation,parsetree,&hasUpdate);
38663886

3887+
product_orig_rt_length=list_length(parsetree->rtable);
38673888
product_queries=fireRules(parsetree,
38683889
result_relation,
38693890
event,
@@ -4020,7 +4041,19 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
40204041
Query*pt= (Query*)lfirst(n);
40214042
List*newstuff;
40224043

4023-
newstuff=RewriteQuery(pt,rewrite_events);
4044+
/*
4045+
* For an updatable view, pt might be the rewritten version of
4046+
* the original query, in which case we pass on orig_rt_length
4047+
* to finish processing any VALUES RTE it contained.
4048+
*
4049+
* Otherwise, we have a product query created by fireRules().
4050+
* Any VALUES RTEs from the original query have been fully
4051+
* processed, and must be skipped when we recurse.
4052+
*/
4053+
newstuff=RewriteQuery(pt,rewrite_events,
4054+
pt==parsetree ?
4055+
orig_rt_length :
4056+
product_orig_rt_length);
40244057
rewritten=list_concat(rewritten,newstuff);
40254058
}
40264059

@@ -4172,7 +4205,7 @@ QueryRewrite(Query *parsetree)
41724205
*
41734206
* Apply all non-SELECT rules possibly getting 0 or many queries
41744207
*/
4175-
querylist=RewriteQuery(parsetree,NIL);
4208+
querylist=RewriteQuery(parsetree,NIL,0);
41764209

41774210
/*
41784211
* Step 2

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

Lines changed: 52 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -2913,11 +2913,11 @@ select pg_get_viewdef('shoe'::regclass,0) as prettier;
29132913
--
29142914
-- check multi-row VALUES in rules
29152915
--
2916-
create table rules_src(f1 int, f2 int);
2917-
create table rules_log(f1 int, f2 int, tag text);
2916+
create table rules_src(f1 int, f2 int default 0);
2917+
create table rules_log(f1 int, f2 int, tag text, id serial);
29182918
insert into rules_src values(1,2), (11,12);
29192919
create rule r1 as on update to rules_src do also
2920-
insert into rules_log values(old.*, 'old'), (new.*, 'new');
2920+
insert into rules_log values(old.*, 'old', default), (new.*, 'new', default);
29212921
update rules_src set f2 = f2 + 1;
29222922
update rules_src set f2 = f2 * 10;
29232923
select * from rules_src;
@@ -2928,16 +2928,16 @@ select * from rules_src;
29282928
(2 rows)
29292929

29302930
select * from rules_log;
2931-
f1 | f2 | tag
2932-
----+-----+-----
2933-
1 | 2 | old
2934-
1 | 3 | new
2935-
11 | 12 | old
2936-
11 | 13 | new
2937-
1 | 3 | old
2938-
1 | 30 | new
2939-
11 | 13 | old
2940-
11 | 130 | new
2931+
f1 | f2 | tag| id
2932+
----+-----+-----+----
2933+
1 | 2 | old | 1
2934+
1 | 3 | new | 2
2935+
11 | 12 | old | 3
2936+
11 | 13 | new | 4
2937+
1 | 3 | old | 5
2938+
1 | 30 | new | 6
2939+
11 | 13 | old | 7
2940+
11 | 130 | new | 8
29412941
(8 rows)
29422942

29432943
create rule r2 as on update to rules_src do also
@@ -2951,71 +2951,84 @@ update rules_src set f2 = f2 / 10;
29512951
11 | 13 | new
29522952
(4 rows)
29532953

2954+
create rule r3 as on insert to rules_src do also
2955+
insert into rules_log values(null, null, '-', default), (new.*, 'new', default);
2956+
insert into rules_src values(22,23), (33,default);
29542957
select * from rules_src;
29552958
f1 | f2
29562959
----+----
29572960
1 | 3
29582961
11 | 13
2959-
(2 rows)
2962+
22 | 23
2963+
33 | 0
2964+
(4 rows)
29602965

29612966
select * from rules_log;
2962-
f1 | f2 | tag
2963-
----+-----+-----
2964-
1 | 2 | old
2965-
1 | 3 | new
2966-
11 | 12 | old
2967-
11 | 13 | new
2968-
1 | 3 | old
2969-
1 | 30 | new
2970-
11 | 13 | old
2971-
11 | 130 | new
2972-
1 | 30 | old
2973-
1 | 3 | new
2974-
11 | 130 | old
2975-
11 | 13 | new
2976-
(12 rows)
2977-
2978-
create rule r3 as on delete to rules_src do notify rules_src_deletion;
2967+
f1 | f2 | tag | id
2968+
----+-----+-----+----
2969+
1 | 2 | old | 1
2970+
1 | 3 | new | 2
2971+
11 | 12 | old | 3
2972+
11 | 13 | new | 4
2973+
1 | 3 | old | 5
2974+
1 | 30 | new | 6
2975+
11 | 13 | old | 7
2976+
11 | 130 | new | 8
2977+
1 | 30 | old | 9
2978+
1 | 3 | new | 10
2979+
11 | 130 | old | 11
2980+
11 | 13 | new | 12
2981+
| | - | 13
2982+
22 | 23 | new | 14
2983+
| | - | 15
2984+
33 | 0 | new | 16
2985+
(16 rows)
2986+
2987+
create rule r4 as on delete to rules_src do notify rules_src_deletion;
29792988
\d+ rules_src
29802989
Table "public.rules_src"
29812990
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
29822991
--------+---------+-----------+----------+---------+---------+--------------+-------------
29832992
f1 | integer | | | | plain | |
2984-
f2 | integer | | | | plain | |
2993+
f2 | integer | | |0 | plain | |
29852994
Rules:
29862995
r1 AS
2987-
ON UPDATE TO rules_src DO INSERT INTO rules_log (f1, f2, tag) VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text)
2996+
ON UPDATE TO rules_src DO INSERT INTO rules_log (f1, f2, tag, id) VALUES (old.f1,old.f2,'old'::text,DEFAULT), (new.f1,new.f2,'new'::text,DEFAULT)
29882997
r2 AS
29892998
ON UPDATE TO rules_src DO VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text)
29902999
r3 AS
3000+
ON INSERT TO rules_src DO INSERT INTO rules_log (f1, f2, tag, id) VALUES (NULL::integer,NULL::integer,'-'::text,DEFAULT), (new.f1,new.f2,'new'::text,DEFAULT)
3001+
r4 AS
29913002
ON DELETE TO rules_src DO
29923003
NOTIFY rules_src_deletion
29933004

29943005
--
29953006
-- Ensure an aliased target relation for insert is correctly deparsed.
29963007
--
2997-
create ruler4 as on insert to rules_src do instead insert into rules_log AS trgt SELECT NEW.* RETURNING trgt.f1, trgt.f2;
2998-
create ruler5 as on update to rules_src do instead UPDATE rules_log AS trgt SET tag = 'updated' WHERE trgt.f1 = new.f1;
3008+
create ruler5 as on insert to rules_src do instead insert into rules_log AS trgt SELECT NEW.* RETURNING trgt.f1, trgt.f2;
3009+
create ruler6 as on update to rules_src do instead UPDATE rules_log AS trgt SET tag = 'updated' WHERE trgt.f1 = new.f1;
29993010
\d+ rules_src
30003011
Table "public.rules_src"
30013012
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
30023013
--------+---------+-----------+----------+---------+---------+--------------+-------------
30033014
f1 | integer | | | | plain | |
3004-
f2 | integer | | | | plain | |
3015+
f2 | integer | | |0 | plain | |
30053016
Rules:
30063017
r1 AS
3007-
ON UPDATE TO rules_src DO INSERT INTO rules_log (f1, f2, tag) VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text)
3018+
ON UPDATE TO rules_src DO INSERT INTO rules_log (f1, f2, tag, id) VALUES (old.f1,old.f2,'old'::text,DEFAULT), (new.f1,new.f2,'new'::text,DEFAULT)
30083019
r2 AS
30093020
ON UPDATE TO rules_src DO VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text)
30103021
r3 AS
3022+
ON INSERT TO rules_src DO INSERT INTO rules_log (f1, f2, tag, id) VALUES (NULL::integer,NULL::integer,'-'::text,DEFAULT), (new.f1,new.f2,'new'::text,DEFAULT)
3023+
r4 AS
30113024
ON DELETE TO rules_src DO
30123025
NOTIFY rules_src_deletion
3013-
r4 AS
3026+
r5 AS
30143027
ON INSERT TO rules_src DO INSTEAD INSERT INTO rules_log AS trgt (f1, f2) SELECT new.f1,
30153028
new.f2
30163029
RETURNING trgt.f1,
30173030
trgt.f2
3018-
r5 AS
3031+
r6 AS
30193032
ON UPDATE TO rules_src DO INSTEAD UPDATE rules_log trgt SET tag = 'updated'::text
30203033
WHERE trgt.f1 = new.f1
30213034

‎src/test/regress/sql/rules.sql

Lines changed: 9 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -997,28 +997,31 @@ select pg_get_viewdef('shoe'::regclass,0) as prettier;
997997
-- check multi-row VALUES in rules
998998
--
999999

1000-
createtablerules_src(f1int, f2int);
1001-
createtablerules_log(f1int, f2int, tagtext);
1000+
createtablerules_src(f1int, f2int default0);
1001+
createtablerules_log(f1int, f2int, tagtext, idserial);
10021002
insert into rules_srcvalues(1,2), (11,12);
10031003
createruler1asonupdate to rules_src do also
1004-
insert into rules_logvalues(old.*,'old'), (new.*,'new');
1004+
insert into rules_logvalues(old.*,'old', default), (new.*,'new', default);
10051005
update rules_srcset f2= f2+1;
10061006
update rules_srcset f2= f2*10;
10071007
select*from rules_src;
10081008
select*from rules_log;
10091009
createruler2asonupdate to rules_src do also
10101010
values(old.*,'old'), (new.*,'new');
10111011
update rules_srcset f2= f2/10;
1012+
createruler3ason insert to rules_src do also
1013+
insert into rules_logvalues(null,null,'-', default), (new.*,'new', default);
1014+
insert into rules_srcvalues(22,23), (33,default);
10121015
select*from rules_src;
10131016
select*from rules_log;
1014-
createruler3ason delete to rules_src do notify rules_src_deletion;
1017+
createruler4ason delete to rules_src do notify rules_src_deletion;
10151018
\d+ rules_src
10161019

10171020
--
10181021
-- Ensure an aliased target relation for insert is correctly deparsed.
10191022
--
1020-
createruler4ason insert to rules_src do insteadinsert into rules_logAS trgtSELECT NEW.* RETURNINGtrgt.f1,trgt.f2;
1021-
createruler5asonupdate to rules_src do insteadUPDATE rules_logAS trgtSET tag='updated'WHEREtrgt.f1=new.f1;
1023+
createruler5ason insert to rules_src do insteadinsert into rules_logAS trgtSELECT NEW.* RETURNINGtrgt.f1,trgt.f2;
1024+
createruler6asonupdate to rules_src do insteadUPDATE rules_logAS trgtSET tag='updated'WHEREtrgt.f1=new.f1;
10221025
\d+ rules_src
10231026

10241027
--

0 commit comments

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