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

Commit2c7ed9f

Browse files
committed
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 parent6344bc0 commit2c7ed9f

File tree

3 files changed

+99
-54
lines changed

3 files changed

+99
-54
lines changed

‎src/backend/rewrite/rewriteHandler.c

Lines changed: 38 additions & 9 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);
@@ -3612,9 +3616,13 @@ rewriteTargetView(Query *parsetree, Relation view)
36123616
*
36133617
* rewrite_events is a list of open query-rewrite actions, so we can detect
36143618
* infinite recursion.
3619+
*
3620+
* orig_rt_length is the length of the originating query's rtable, for product
3621+
* queries created by fireRules(), and 0 otherwise. This is used to skip any
3622+
* already-processed VALUES RTEs from the original query.
36153623
*/
36163624
staticList*
3617-
RewriteQuery(Query*parsetree,List*rewrite_events)
3625+
RewriteQuery(Query*parsetree,List*rewrite_events,intorig_rt_length)
36183626
{
36193627
CmdTypeevent=parsetree->commandType;
36203628
boolinstead= false;
@@ -3638,7 +3646,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
36383646
if (ctequery->commandType==CMD_SELECT)
36393647
continue;
36403648

3641-
newstuff=RewriteQuery(ctequery,rewrite_events);
3649+
newstuff=RewriteQuery(ctequery,rewrite_events,0);
36423650

36433651
/*
36443652
* Currently we can only handle unconditional, single-statement DO
@@ -3712,6 +3720,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
37123720
RangeTblEntry*rt_entry;
37133721
Relationrt_entry_relation;
37143722
List*locks;
3723+
intproduct_orig_rt_length;
37153724
List*product_queries;
37163725
boolhasUpdate= false;
37173726
intvalues_rte_index=0;
@@ -3733,23 +3742,30 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
37333742
*/
37343743
if (event==CMD_INSERT)
37353744
{
3745+
ListCell*lc2;
37363746
RangeTblEntry*values_rte=NULL;
37373747

37383748
/*
3739-
* If it's an INSERT ... VALUES (...), (...), ... there will be a
3740-
* single RTE for the VALUES targetlists.
3749+
* Test if it's a multi-row INSERT ... VALUES (...), (...), ... by
3750+
* looking for a VALUES RTE in the fromlist. For product queries,
3751+
* we must ignore any already-processed VALUES RTEs from the
3752+
* original query. These appear at the start of the rangetable.
37413753
*/
3742-
if (list_length(parsetree->jointree->fromlist)==1)
3754+
foreach(lc2,parsetree->jointree->fromlist)
37433755
{
3744-
RangeTblRef*rtr= (RangeTblRef*)linitial(parsetree->jointree->fromlist);
3756+
RangeTblRef*rtr= (RangeTblRef*)lfirst(lc2);
37453757

3746-
if (IsA(rtr,RangeTblRef))
3758+
if (IsA(rtr,RangeTblRef)&&rtr->rtindex>orig_rt_length)
37473759
{
37483760
RangeTblEntry*rte=rt_fetch(rtr->rtindex,
37493761
parsetree->rtable);
37503762

37513763
if (rte->rtekind==RTE_VALUES)
37523764
{
3765+
/* should not find more than one VALUES RTE */
3766+
if (values_rte!=NULL)
3767+
elog(ERROR,"more than one VALUES RTE found");
3768+
37533769
values_rte=rte;
37543770
values_rte_index=rtr->rtindex;
37553771
}
@@ -3821,6 +3837,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
38213837
locks=matchLocks(event,rt_entry_relation->rd_rules,
38223838
result_relation,parsetree,&hasUpdate);
38233839

3840+
product_orig_rt_length=list_length(parsetree->rtable);
38243841
product_queries=fireRules(parsetree,
38253842
result_relation,
38263843
event,
@@ -3977,7 +3994,19 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
39773994
Query*pt= (Query*)lfirst(n);
39783995
List*newstuff;
39793996

3980-
newstuff=RewriteQuery(pt,rewrite_events);
3997+
/*
3998+
* For an updatable view, pt might be the rewritten version of
3999+
* the original query, in which case we pass on orig_rt_length
4000+
* to finish processing any VALUES RTE it contained.
4001+
*
4002+
* Otherwise, we have a product query created by fireRules().
4003+
* Any VALUES RTEs from the original query have been fully
4004+
* processed, and must be skipped when we recurse.
4005+
*/
4006+
newstuff=RewriteQuery(pt,rewrite_events,
4007+
pt==parsetree ?
4008+
orig_rt_length :
4009+
product_orig_rt_length);
39814010
rewritten=list_concat(rewritten,newstuff);
39824011
}
39834012

@@ -4129,7 +4158,7 @@ QueryRewrite(Query *parsetree)
41294158
*
41304159
* Apply all non-SELECT rules possibly getting 0 or many queries
41314160
*/
4132-
querylist=RewriteQuery(parsetree,NIL);
4161+
querylist=RewriteQuery(parsetree,NIL,0);
41334162

41344163
/*
41354164
* Step 2

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

Lines changed: 52 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -3103,11 +3103,11 @@ select pg_get_viewdef('shoe'::regclass,0) as prettier;
31033103
--
31043104
-- check multi-row VALUES in rules
31053105
--
3106-
create table rules_src(f1 int, f2 int);
3107-
create table rules_log(f1 int, f2 int, tag text);
3106+
create table rules_src(f1 int, f2 int default 0);
3107+
create table rules_log(f1 int, f2 int, tag text, id serial);
31083108
insert into rules_src values(1,2), (11,12);
31093109
create rule r1 as on update to rules_src do also
3110-
insert into rules_log values(old.*, 'old'), (new.*, 'new');
3110+
insert into rules_log values(old.*, 'old', default), (new.*, 'new', default);
31113111
update rules_src set f2 = f2 + 1;
31123112
update rules_src set f2 = f2 * 10;
31133113
select * from rules_src;
@@ -3118,16 +3118,16 @@ select * from rules_src;
31183118
(2 rows)
31193119

31203120
select * from rules_log;
3121-
f1 | f2 | tag
3122-
----+-----+-----
3123-
1 | 2 | old
3124-
1 | 3 | new
3125-
11 | 12 | old
3126-
11 | 13 | new
3127-
1 | 3 | old
3128-
1 | 30 | new
3129-
11 | 13 | old
3130-
11 | 130 | new
3121+
f1 | f2 | tag| id
3122+
----+-----+-----+----
3123+
1 | 2 | old | 1
3124+
1 | 3 | new | 2
3125+
11 | 12 | old | 3
3126+
11 | 13 | new | 4
3127+
1 | 3 | old | 5
3128+
1 | 30 | new | 6
3129+
11 | 13 | old | 7
3130+
11 | 130 | new | 8
31313131
(8 rows)
31323132

31333133
create rule r2 as on update to rules_src do also
@@ -3141,71 +3141,84 @@ update rules_src set f2 = f2 / 10;
31413141
11 | 13 | new
31423142
(4 rows)
31433143

3144+
create rule r3 as on insert to rules_src do also
3145+
insert into rules_log values(null, null, '-', default), (new.*, 'new', default);
3146+
insert into rules_src values(22,23), (33,default);
31443147
select * from rules_src;
31453148
f1 | f2
31463149
----+----
31473150
1 | 3
31483151
11 | 13
3149-
(2 rows)
3152+
22 | 23
3153+
33 | 0
3154+
(4 rows)
31503155

31513156
select * from rules_log;
3152-
f1 | f2 | tag
3153-
----+-----+-----
3154-
1 | 2 | old
3155-
1 | 3 | new
3156-
11 | 12 | old
3157-
11 | 13 | new
3158-
1 | 3 | old
3159-
1 | 30 | new
3160-
11 | 13 | old
3161-
11 | 130 | new
3162-
1 | 30 | old
3163-
1 | 3 | new
3164-
11 | 130 | old
3165-
11 | 13 | new
3166-
(12 rows)
3167-
3168-
create rule r3 as on delete to rules_src do notify rules_src_deletion;
3157+
f1 | f2 | tag | id
3158+
----+-----+-----+----
3159+
1 | 2 | old | 1
3160+
1 | 3 | new | 2
3161+
11 | 12 | old | 3
3162+
11 | 13 | new | 4
3163+
1 | 3 | old | 5
3164+
1 | 30 | new | 6
3165+
11 | 13 | old | 7
3166+
11 | 130 | new | 8
3167+
1 | 30 | old | 9
3168+
1 | 3 | new | 10
3169+
11 | 130 | old | 11
3170+
11 | 13 | new | 12
3171+
| | - | 13
3172+
22 | 23 | new | 14
3173+
| | - | 15
3174+
33 | 0 | new | 16
3175+
(16 rows)
3176+
3177+
create rule r4 as on delete to rules_src do notify rules_src_deletion;
31693178
\d+ rules_src
31703179
Table "public.rules_src"
31713180
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
31723181
--------+---------+-----------+----------+---------+---------+--------------+-------------
31733182
f1 | integer | | | | plain | |
3174-
f2 | integer | | | | plain | |
3183+
f2 | integer | | |0 | plain | |
31753184
Rules:
31763185
r1 AS
3177-
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)
3186+
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)
31783187
r2 AS
31793188
ON UPDATE TO rules_src DO VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text)
31803189
r3 AS
3190+
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)
3191+
r4 AS
31813192
ON DELETE TO rules_src DO
31823193
NOTIFY rules_src_deletion
31833194

31843195
--
31853196
-- Ensure an aliased target relation for insert is correctly deparsed.
31863197
--
3187-
create ruler4 as on insert to rules_src do instead insert into rules_log AS trgt SELECT NEW.* RETURNING trgt.f1, trgt.f2;
3188-
create ruler5 as on update to rules_src do instead UPDATE rules_log AS trgt SET tag = 'updated' WHERE trgt.f1 = new.f1;
3198+
create ruler5 as on insert to rules_src do instead insert into rules_log AS trgt SELECT NEW.* RETURNING trgt.f1, trgt.f2;
3199+
create ruler6 as on update to rules_src do instead UPDATE rules_log AS trgt SET tag = 'updated' WHERE trgt.f1 = new.f1;
31893200
\d+ rules_src
31903201
Table "public.rules_src"
31913202
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
31923203
--------+---------+-----------+----------+---------+---------+--------------+-------------
31933204
f1 | integer | | | | plain | |
3194-
f2 | integer | | | | plain | |
3205+
f2 | integer | | |0 | plain | |
31953206
Rules:
31963207
r1 AS
3197-
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)
3208+
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)
31983209
r2 AS
31993210
ON UPDATE TO rules_src DO VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text)
32003211
r3 AS
3212+
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)
3213+
r4 AS
32013214
ON DELETE TO rules_src DO
32023215
NOTIFY rules_src_deletion
3203-
r4 AS
3216+
r5 AS
32043217
ON INSERT TO rules_src DO INSTEAD INSERT INTO rules_log AS trgt (f1, f2) SELECT new.f1,
32053218
new.f2
32063219
RETURNING trgt.f1,
32073220
trgt.f2
3208-
r5 AS
3221+
r6 AS
32093222
ON UPDATE TO rules_src DO INSTEAD UPDATE rules_log trgt SET tag = 'updated'::text
32103223
WHERE trgt.f1 = new.f1
32113224

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

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

1019-
createtablerules_src(f1int, f2int);
1020-
createtablerules_log(f1int, f2int, tagtext);
1019+
createtablerules_src(f1int, f2int default0);
1020+
createtablerules_log(f1int, f2int, tagtext, idserial);
10211021
insert into rules_srcvalues(1,2), (11,12);
10221022
createruler1asonupdate to rules_src do also
1023-
insert into rules_logvalues(old.*,'old'), (new.*,'new');
1023+
insert into rules_logvalues(old.*,'old', default), (new.*,'new', default);
10241024
update rules_srcset f2= f2+1;
10251025
update rules_srcset f2= f2*10;
10261026
select*from rules_src;
10271027
select*from rules_log;
10281028
createruler2asonupdate to rules_src do also
10291029
values(old.*,'old'), (new.*,'new');
10301030
update rules_srcset f2= f2/10;
1031+
createruler3ason insert to rules_src do also
1032+
insert into rules_logvalues(null,null,'-', default), (new.*,'new', default);
1033+
insert into rules_srcvalues(22,23), (33,default);
10311034
select*from rules_src;
10321035
select*from rules_log;
1033-
createruler3ason delete to rules_src do notify rules_src_deletion;
1036+
createruler4ason delete to rules_src do notify rules_src_deletion;
10341037
\d+ rules_src
10351038

10361039
--
10371040
-- Ensure an aliased target relation for insert is correctly deparsed.
10381041
--
1039-
createruler4ason insert to rules_src do insteadinsert into rules_logAS trgtSELECT NEW.* RETURNINGtrgt.f1,trgt.f2;
1040-
createruler5asonupdate to rules_src do insteadUPDATE rules_logAS trgtSET tag='updated'WHEREtrgt.f1=new.f1;
1042+
createruler5ason insert to rules_src do insteadinsert into rules_logAS trgtSELECT NEW.* RETURNINGtrgt.f1,trgt.f2;
1043+
createruler6asonupdate to rules_src do insteadUPDATE rules_logAS trgtSET tag='updated'WHEREtrgt.f1=new.f1;
10411044
\d+ rules_src
10421045

10431046
--

0 commit comments

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