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

Commit3bed881

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 parent7944d2d commit3bed881

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
@@ -413,6 +413,10 @@ rewriteRuleAction(Query *parsetree,
413413
* NOTE: because planner will destructively alter rtable, we must ensure
414414
* that rule action's rtable is separate and shares no substructure with
415415
* the main rtable. Hence do a deep copy here.
416+
*
417+
* Note also that RewriteQuery() relies on the fact that RT entries from
418+
* the original query appear at the start of the expanded rtable, so
419+
* beware of changing this.
416420
*/
417421
sub_action->rtable=list_concat(copyObject(parsetree->rtable),
418422
sub_action->rtable);
@@ -3543,9 +3547,13 @@ rewriteTargetView(Query *parsetree, Relation view)
35433547
*
35443548
* rewrite_events is a list of open query-rewrite actions, so we can detect
35453549
* infinite recursion.
3550+
*
3551+
* orig_rt_length is the length of the originating query's rtable, for product
3552+
* queries created by fireRules(), and 0 otherwise. This is used to skip any
3553+
* already-processed VALUES RTEs from the original query.
35463554
*/
35473555
staticList*
3548-
RewriteQuery(Query*parsetree,List*rewrite_events)
3556+
RewriteQuery(Query*parsetree,List*rewrite_events,intorig_rt_length)
35493557
{
35503558
CmdTypeevent=parsetree->commandType;
35513559
boolinstead= false;
@@ -3569,7 +3577,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
35693577
if (ctequery->commandType==CMD_SELECT)
35703578
continue;
35713579

3572-
newstuff=RewriteQuery(ctequery,rewrite_events);
3580+
newstuff=RewriteQuery(ctequery,rewrite_events,0);
35733581

35743582
/*
35753583
* Currently we can only handle unconditional, single-statement DO
@@ -3643,6 +3651,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
36433651
RangeTblEntry*rt_entry;
36443652
Relationrt_entry_relation;
36453653
List*locks;
3654+
intproduct_orig_rt_length;
36463655
List*product_queries;
36473656
boolhasUpdate= false;
36483657
intvalues_rte_index=0;
@@ -3664,23 +3673,30 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
36643673
*/
36653674
if (event==CMD_INSERT)
36663675
{
3676+
ListCell*lc2;
36673677
RangeTblEntry*values_rte=NULL;
36683678

36693679
/*
3670-
* If it's an INSERT ... VALUES (...), (...), ... there will be a
3671-
* single RTE for the VALUES targetlists.
3680+
* Test if it's a multi-row INSERT ... VALUES (...), (...), ... by
3681+
* looking for a VALUES RTE in the fromlist. For product queries,
3682+
* we must ignore any already-processed VALUES RTEs from the
3683+
* original query. These appear at the start of the rangetable.
36723684
*/
3673-
if (list_length(parsetree->jointree->fromlist)==1)
3685+
foreach(lc2,parsetree->jointree->fromlist)
36743686
{
3675-
RangeTblRef*rtr= (RangeTblRef*)linitial(parsetree->jointree->fromlist);
3687+
RangeTblRef*rtr= (RangeTblRef*)lfirst(lc2);
36763688

3677-
if (IsA(rtr,RangeTblRef))
3689+
if (IsA(rtr,RangeTblRef)&&rtr->rtindex>orig_rt_length)
36783690
{
36793691
RangeTblEntry*rte=rt_fetch(rtr->rtindex,
36803692
parsetree->rtable);
36813693

36823694
if (rte->rtekind==RTE_VALUES)
36833695
{
3696+
/* should not find more than one VALUES RTE */
3697+
if (values_rte!=NULL)
3698+
elog(ERROR,"more than one VALUES RTE found");
3699+
36843700
values_rte=rte;
36853701
values_rte_index=rtr->rtindex;
36863702
}
@@ -3747,6 +3763,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
37473763
locks=matchLocks(event,rt_entry_relation->rd_rules,
37483764
result_relation,parsetree,&hasUpdate);
37493765

3766+
product_orig_rt_length=list_length(parsetree->rtable);
37503767
product_queries=fireRules(parsetree,
37513768
result_relation,
37523769
event,
@@ -3903,7 +3920,19 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
39033920
Query*pt= (Query*)lfirst(n);
39043921
List*newstuff;
39053922

3906-
newstuff=RewriteQuery(pt,rewrite_events);
3923+
/*
3924+
* For an updatable view, pt might be the rewritten version of
3925+
* the original query, in which case we pass on orig_rt_length
3926+
* to finish processing any VALUES RTE it contained.
3927+
*
3928+
* Otherwise, we have a product query created by fireRules().
3929+
* Any VALUES RTEs from the original query have been fully
3930+
* processed, and must be skipped when we recurse.
3931+
*/
3932+
newstuff=RewriteQuery(pt,rewrite_events,
3933+
pt==parsetree ?
3934+
orig_rt_length :
3935+
product_orig_rt_length);
39073936
rewritten=list_concat(rewritten,newstuff);
39083937
}
39093938

@@ -4055,7 +4084,7 @@ QueryRewrite(Query *parsetree)
40554084
*
40564085
* Apply all non-SELECT rules possibly getting 0 or many queries
40574086
*/
4058-
querylist=RewriteQuery(parsetree,NIL);
4087+
querylist=RewriteQuery(parsetree,NIL,0);
40594088

40604089
/*
40614090
* Step 2

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

Lines changed: 52 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -2964,11 +2964,11 @@ select pg_get_viewdef('shoe'::regclass,0) as prettier;
29642964
--
29652965
-- check multi-row VALUES in rules
29662966
--
2967-
create table rules_src(f1 int, f2 int);
2968-
create table rules_log(f1 int, f2 int, tag text);
2967+
create table rules_src(f1 int, f2 int default 0);
2968+
create table rules_log(f1 int, f2 int, tag text, id serial);
29692969
insert into rules_src values(1,2), (11,12);
29702970
create rule r1 as on update to rules_src do also
2971-
insert into rules_log values(old.*, 'old'), (new.*, 'new');
2971+
insert into rules_log values(old.*, 'old', default), (new.*, 'new', default);
29722972
update rules_src set f2 = f2 + 1;
29732973
update rules_src set f2 = f2 * 10;
29742974
select * from rules_src;
@@ -2979,16 +2979,16 @@ select * from rules_src;
29792979
(2 rows)
29802980

29812981
select * from rules_log;
2982-
f1 | f2 | tag
2983-
----+-----+-----
2984-
1 | 2 | old
2985-
1 | 3 | new
2986-
11 | 12 | old
2987-
11 | 13 | new
2988-
1 | 3 | old
2989-
1 | 30 | new
2990-
11 | 13 | old
2991-
11 | 130 | new
2982+
f1 | f2 | tag| id
2983+
----+-----+-----+----
2984+
1 | 2 | old | 1
2985+
1 | 3 | new | 2
2986+
11 | 12 | old | 3
2987+
11 | 13 | new | 4
2988+
1 | 3 | old | 5
2989+
1 | 30 | new | 6
2990+
11 | 13 | old | 7
2991+
11 | 130 | new | 8
29922992
(8 rows)
29932993

29942994
create rule r2 as on update to rules_src do also
@@ -3002,71 +3002,84 @@ update rules_src set f2 = f2 / 10;
30023002
11 | 13 | new
30033003
(4 rows)
30043004

3005+
create rule r3 as on insert to rules_src do also
3006+
insert into rules_log values(null, null, '-', default), (new.*, 'new', default);
3007+
insert into rules_src values(22,23), (33,default);
30053008
select * from rules_src;
30063009
f1 | f2
30073010
----+----
30083011
1 | 3
30093012
11 | 13
3010-
(2 rows)
3013+
22 | 23
3014+
33 | 0
3015+
(4 rows)
30113016

30123017
select * from rules_log;
3013-
f1 | f2 | tag
3014-
----+-----+-----
3015-
1 | 2 | old
3016-
1 | 3 | new
3017-
11 | 12 | old
3018-
11 | 13 | new
3019-
1 | 3 | old
3020-
1 | 30 | new
3021-
11 | 13 | old
3022-
11 | 130 | new
3023-
1 | 30 | old
3024-
1 | 3 | new
3025-
11 | 130 | old
3026-
11 | 13 | new
3027-
(12 rows)
3028-
3029-
create rule r3 as on delete to rules_src do notify rules_src_deletion;
3018+
f1 | f2 | tag | id
3019+
----+-----+-----+----
3020+
1 | 2 | old | 1
3021+
1 | 3 | new | 2
3022+
11 | 12 | old | 3
3023+
11 | 13 | new | 4
3024+
1 | 3 | old | 5
3025+
1 | 30 | new | 6
3026+
11 | 13 | old | 7
3027+
11 | 130 | new | 8
3028+
1 | 30 | old | 9
3029+
1 | 3 | new | 10
3030+
11 | 130 | old | 11
3031+
11 | 13 | new | 12
3032+
| | - | 13
3033+
22 | 23 | new | 14
3034+
| | - | 15
3035+
33 | 0 | new | 16
3036+
(16 rows)
3037+
3038+
create rule r4 as on delete to rules_src do notify rules_src_deletion;
30303039
\d+ rules_src
30313040
Table "public.rules_src"
30323041
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
30333042
--------+---------+-----------+----------+---------+---------+--------------+-------------
30343043
f1 | integer | | | | plain | |
3035-
f2 | integer | | | | plain | |
3044+
f2 | integer | | |0 | plain | |
30363045
Rules:
30373046
r1 AS
3038-
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)
3047+
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)
30393048
r2 AS
30403049
ON UPDATE TO rules_src DO VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text)
30413050
r3 AS
3051+
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)
3052+
r4 AS
30423053
ON DELETE TO rules_src DO
30433054
NOTIFY rules_src_deletion
30443055

30453056
--
30463057
-- Ensure an aliased target relation for insert is correctly deparsed.
30473058
--
3048-
create ruler4 as on insert to rules_src do instead insert into rules_log AS trgt SELECT NEW.* RETURNING trgt.f1, trgt.f2;
3049-
create ruler5 as on update to rules_src do instead UPDATE rules_log AS trgt SET tag = 'updated' WHERE trgt.f1 = new.f1;
3059+
create ruler5 as on insert to rules_src do instead insert into rules_log AS trgt SELECT NEW.* RETURNING trgt.f1, trgt.f2;
3060+
create ruler6 as on update to rules_src do instead UPDATE rules_log AS trgt SET tag = 'updated' WHERE trgt.f1 = new.f1;
30503061
\d+ rules_src
30513062
Table "public.rules_src"
30523063
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
30533064
--------+---------+-----------+----------+---------+---------+--------------+-------------
30543065
f1 | integer | | | | plain | |
3055-
f2 | integer | | | | plain | |
3066+
f2 | integer | | |0 | plain | |
30563067
Rules:
30573068
r1 AS
3058-
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)
3069+
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)
30593070
r2 AS
30603071
ON UPDATE TO rules_src DO VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text)
30613072
r3 AS
3073+
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)
3074+
r4 AS
30623075
ON DELETE TO rules_src DO
30633076
NOTIFY rules_src_deletion
3064-
r4 AS
3077+
r5 AS
30653078
ON INSERT TO rules_src DO INSTEAD INSERT INTO rules_log AS trgt (f1, f2) SELECT new.f1,
30663079
new.f2
30673080
RETURNING trgt.f1,
30683081
trgt.f2
3069-
r5 AS
3082+
r6 AS
30703083
ON UPDATE TO rules_src DO INSTEAD UPDATE rules_log trgt SET tag = 'updated'::text
30713084
WHERE trgt.f1 = new.f1
30723085

‎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