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

Commit33f600f

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 parent35b99a1 commit33f600f

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);
@@ -3539,9 +3543,13 @@ rewriteTargetView(Query *parsetree, Relation view)
35393543
*
35403544
* rewrite_events is a list of open query-rewrite actions, so we can detect
35413545
* infinite recursion.
3546+
*
3547+
* orig_rt_length is the length of the originating query's rtable, for product
3548+
* queries created by fireRules(), and 0 otherwise. This is used to skip any
3549+
* already-processed VALUES RTEs from the original query.
35423550
*/
35433551
staticList*
3544-
RewriteQuery(Query*parsetree,List*rewrite_events)
3552+
RewriteQuery(Query*parsetree,List*rewrite_events,intorig_rt_length)
35453553
{
35463554
CmdTypeevent=parsetree->commandType;
35473555
boolinstead= false;
@@ -3565,7 +3573,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
35653573
if (ctequery->commandType==CMD_SELECT)
35663574
continue;
35673575

3568-
newstuff=RewriteQuery(ctequery,rewrite_events);
3576+
newstuff=RewriteQuery(ctequery,rewrite_events,0);
35693577

35703578
/*
35713579
* Currently we can only handle unconditional, single-statement DO
@@ -3639,6 +3647,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
36393647
RangeTblEntry*rt_entry;
36403648
Relationrt_entry_relation;
36413649
List*locks;
3650+
intproduct_orig_rt_length;
36423651
List*product_queries;
36433652
boolhasUpdate= false;
36443653
intvalues_rte_index=0;
@@ -3660,23 +3669,30 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
36603669
*/
36613670
if (event==CMD_INSERT)
36623671
{
3672+
ListCell*lc2;
36633673
RangeTblEntry*values_rte=NULL;
36643674

36653675
/*
3666-
* If it's an INSERT ... VALUES (...), (...), ... there will be a
3667-
* single RTE for the VALUES targetlists.
3676+
* Test if it's a multi-row INSERT ... VALUES (...), (...), ... by
3677+
* looking for a VALUES RTE in the fromlist. For product queries,
3678+
* we must ignore any already-processed VALUES RTEs from the
3679+
* original query. These appear at the start of the rangetable.
36683680
*/
3669-
if (list_length(parsetree->jointree->fromlist)==1)
3681+
foreach(lc2,parsetree->jointree->fromlist)
36703682
{
3671-
RangeTblRef*rtr= (RangeTblRef*)linitial(parsetree->jointree->fromlist);
3683+
RangeTblRef*rtr= (RangeTblRef*)lfirst(lc2);
36723684

3673-
if (IsA(rtr,RangeTblRef))
3685+
if (IsA(rtr,RangeTblRef)&&rtr->rtindex>orig_rt_length)
36743686
{
36753687
RangeTblEntry*rte=rt_fetch(rtr->rtindex,
36763688
parsetree->rtable);
36773689

36783690
if (rte->rtekind==RTE_VALUES)
36793691
{
3692+
/* should not find more than one VALUES RTE */
3693+
if (values_rte!=NULL)
3694+
elog(ERROR,"more than one VALUES RTE found");
3695+
36803696
values_rte=rte;
36813697
values_rte_index=rtr->rtindex;
36823698
}
@@ -3743,6 +3759,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
37433759
locks=matchLocks(event,rt_entry_relation->rd_rules,
37443760
result_relation,parsetree,&hasUpdate);
37453761

3762+
product_orig_rt_length=list_length(parsetree->rtable);
37463763
product_queries=fireRules(parsetree,
37473764
result_relation,
37483765
event,
@@ -3899,7 +3916,19 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
38993916
Query*pt= (Query*)lfirst(n);
39003917
List*newstuff;
39013918

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

@@ -4051,7 +4080,7 @@ QueryRewrite(Query *parsetree)
40514080
*
40524081
* Apply all non-SELECT rules possibly getting 0 or many queries
40534082
*/
4054-
querylist=RewriteQuery(parsetree,NIL);
4083+
querylist=RewriteQuery(parsetree,NIL,0);
40554084

40564085
/*
40574086
* Step 2

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

Lines changed: 52 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -2899,11 +2899,11 @@ select pg_get_viewdef('shoe'::regclass,0) as prettier;
28992899
--
29002900
-- check multi-row VALUES in rules
29012901
--
2902-
create table rules_src(f1 int, f2 int);
2903-
create table rules_log(f1 int, f2 int, tag text);
2902+
create table rules_src(f1 int, f2 int default 0);
2903+
create table rules_log(f1 int, f2 int, tag text, id serial);
29042904
insert into rules_src values(1,2), (11,12);
29052905
create rule r1 as on update to rules_src do also
2906-
insert into rules_log values(old.*, 'old'), (new.*, 'new');
2906+
insert into rules_log values(old.*, 'old', default), (new.*, 'new', default);
29072907
update rules_src set f2 = f2 + 1;
29082908
update rules_src set f2 = f2 * 10;
29092909
select * from rules_src;
@@ -2914,16 +2914,16 @@ select * from rules_src;
29142914
(2 rows)
29152915

29162916
select * from rules_log;
2917-
f1 | f2 | tag
2918-
----+-----+-----
2919-
1 | 2 | old
2920-
1 | 3 | new
2921-
11 | 12 | old
2922-
11 | 13 | new
2923-
1 | 3 | old
2924-
1 | 30 | new
2925-
11 | 13 | old
2926-
11 | 130 | new
2917+
f1 | f2 | tag| id
2918+
----+-----+-----+----
2919+
1 | 2 | old | 1
2920+
1 | 3 | new | 2
2921+
11 | 12 | old | 3
2922+
11 | 13 | new | 4
2923+
1 | 3 | old | 5
2924+
1 | 30 | new | 6
2925+
11 | 13 | old | 7
2926+
11 | 130 | new | 8
29272927
(8 rows)
29282928

29292929
create rule r2 as on update to rules_src do also
@@ -2937,71 +2937,84 @@ update rules_src set f2 = f2 / 10;
29372937
11 | 13 | new
29382938
(4 rows)
29392939

2940+
create rule r3 as on insert to rules_src do also
2941+
insert into rules_log values(null, null, '-', default), (new.*, 'new', default);
2942+
insert into rules_src values(22,23), (33,default);
29402943
select * from rules_src;
29412944
f1 | f2
29422945
----+----
29432946
1 | 3
29442947
11 | 13
2945-
(2 rows)
2948+
22 | 23
2949+
33 | 0
2950+
(4 rows)
29462951

29472952
select * from rules_log;
2948-
f1 | f2 | tag
2949-
----+-----+-----
2950-
1 | 2 | old
2951-
1 | 3 | new
2952-
11 | 12 | old
2953-
11 | 13 | new
2954-
1 | 3 | old
2955-
1 | 30 | new
2956-
11 | 13 | old
2957-
11 | 130 | new
2958-
1 | 30 | old
2959-
1 | 3 | new
2960-
11 | 130 | old
2961-
11 | 13 | new
2962-
(12 rows)
2963-
2964-
create rule r3 as on delete to rules_src do notify rules_src_deletion;
2953+
f1 | f2 | tag | id
2954+
----+-----+-----+----
2955+
1 | 2 | old | 1
2956+
1 | 3 | new | 2
2957+
11 | 12 | old | 3
2958+
11 | 13 | new | 4
2959+
1 | 3 | old | 5
2960+
1 | 30 | new | 6
2961+
11 | 13 | old | 7
2962+
11 | 130 | new | 8
2963+
1 | 30 | old | 9
2964+
1 | 3 | new | 10
2965+
11 | 130 | old | 11
2966+
11 | 13 | new | 12
2967+
| | - | 13
2968+
22 | 23 | new | 14
2969+
| | - | 15
2970+
33 | 0 | new | 16
2971+
(16 rows)
2972+
2973+
create rule r4 as on delete to rules_src do notify rules_src_deletion;
29652974
\d+ rules_src
29662975
Table "public.rules_src"
29672976
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
29682977
--------+---------+-----------+----------+---------+---------+--------------+-------------
29692978
f1 | integer | | | | plain | |
2970-
f2 | integer | | | | plain | |
2979+
f2 | integer | | |0 | plain | |
29712980
Rules:
29722981
r1 AS
2973-
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)
2982+
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)
29742983
r2 AS
29752984
ON UPDATE TO rules_src DO VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text)
29762985
r3 AS
2986+
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)
2987+
r4 AS
29772988
ON DELETE TO rules_src DO
29782989
NOTIFY rules_src_deletion
29792990

29802991
--
29812992
-- Ensure an aliased target relation for insert is correctly deparsed.
29822993
--
2983-
create ruler4 as on insert to rules_src do instead insert into rules_log AS trgt SELECT NEW.* RETURNING trgt.f1, trgt.f2;
2984-
create ruler5 as on update to rules_src do instead UPDATE rules_log AS trgt SET tag = 'updated' WHERE trgt.f1 = new.f1;
2994+
create ruler5 as on insert to rules_src do instead insert into rules_log AS trgt SELECT NEW.* RETURNING trgt.f1, trgt.f2;
2995+
create ruler6 as on update to rules_src do instead UPDATE rules_log AS trgt SET tag = 'updated' WHERE trgt.f1 = new.f1;
29852996
\d+ rules_src
29862997
Table "public.rules_src"
29872998
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
29882999
--------+---------+-----------+----------+---------+---------+--------------+-------------
29893000
f1 | integer | | | | plain | |
2990-
f2 | integer | | | | plain | |
3001+
f2 | integer | | |0 | plain | |
29913002
Rules:
29923003
r1 AS
2993-
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)
3004+
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)
29943005
r2 AS
29953006
ON UPDATE TO rules_src DO VALUES (old.f1,old.f2,'old'::text), (new.f1,new.f2,'new'::text)
29963007
r3 AS
3008+
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)
3009+
r4 AS
29973010
ON DELETE TO rules_src DO
29983011
NOTIFY rules_src_deletion
2999-
r4 AS
3012+
r5 AS
30003013
ON INSERT TO rules_src DO INSTEAD INSERT INTO rules_log AS trgt (f1, f2) SELECT new.f1,
30013014
new.f2
30023015
RETURNING trgt.f1,
30033016
trgt.f2
3004-
r5 AS
3017+
r6 AS
30053018
ON UPDATE TO rules_src DO INSTEAD UPDATE rules_log trgt SET tag = 'updated'::text
30063019
WHERE trgt.f1 = new.f1
30073020

‎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