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

Commit362e2dc

Browse files
committed
Fix rewriter to set hasModifyingCTE correctly on rewritten queries.
If we copy data-modifying CTEs from the original query to a replacementquery (from a DO INSTEAD rule), we must set hasModifyingCTE properlyin the replacement query. Failure to do this can cause variousunpleasantness, such as unsafe usage of parallel plans. The code alsoneglected to propagate hasRecursive, though that's only cosmetic atthe moment.A difficulty arises if the rule action is an INSERT...SELECT. Weattach the original query's RTEs and CTEs to the sub-SELECT Query, butdata-modifying CTEs are only allowed to appear in the topmost Query.For the moment, throw an error in such cases. It would probably bepossible to avoid this error by attaching the CTEs to the top INSERTQuery instead; but that would require a bunch of new code to adjustctelevelsup references. Given the narrowness of the use-case, andthe need to back-patch this fix, it does not seem worth the troublefor now. We can revisit this if we get field complaints.Per report from Greg Nancarrow. Back-patch to all supported branches.(The test case added here does not fail before v10, but there areplenty of places checking top-level hasModifyingCTE in 9.6, so I haveno doubt that this code change is necessary there too.)Greg Nancarrow and Tom LaneDiscussion:https://postgr.es/m/CAJcOf-f68DT=26YAMz_i0+Au3TcLO5oiHY5=fL6Sfuits6r+_w@mail.gmail.comDiscussion:https://postgr.es/m/CAJcOf-fAdj=nDKMsRhQzndm-O13NY4dL6xGcEvdX5Xvbbi0V7g@mail.gmail.com
1 parentf7c53bb commit362e2dc

File tree

3 files changed

+85
-2
lines changed

3 files changed

+85
-2
lines changed

‎src/backend/rewrite/rewriteHandler.c

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -535,6 +535,9 @@ rewriteRuleAction(Query *parsetree,
535535
*
536536
* This could possibly be fixed by using some sort of internally
537537
* generated ID, instead of names, to link CTE RTEs to their CTEs.
538+
* However, decompiling the results would be quite confusing; note the
539+
* merge of hasRecursive flags below, which could change the apparent
540+
* semantics of such redundantly-named CTEs.
538541
*/
539542
foreach(lc,parsetree->cteList)
540543
{
@@ -556,6 +559,26 @@ rewriteRuleAction(Query *parsetree,
556559
/* OK, it's safe to combine the CTE lists */
557560
sub_action->cteList=list_concat(sub_action->cteList,
558561
copyObject(parsetree->cteList));
562+
/* ... and don't forget about the associated flags */
563+
sub_action->hasRecursive |=parsetree->hasRecursive;
564+
sub_action->hasModifyingCTE |=parsetree->hasModifyingCTE;
565+
566+
/*
567+
* If rule_action is different from sub_action (i.e., the rule action
568+
* is an INSERT...SELECT), then we might have just added some
569+
* data-modifying CTEs that are not at the top query level. This is
570+
* disallowed by the parser and we mustn't generate such trees here
571+
* either, so throw an error.
572+
*
573+
* Conceivably such cases could be supported by attaching the original
574+
* query's CTEs to rule_action not sub_action. But to do that, we'd
575+
* have to increment ctelevelsup in RTEs and SubLinks copied from the
576+
* original query. For now, it doesn't seem worth the trouble.
577+
*/
578+
if (sub_action->hasModifyingCTE&&rule_action!=sub_action)
579+
ereport(ERROR,
580+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
581+
errmsg("INSERT...SELECT rule actions are not supported for queries having data-modifying statements in WITH")));
559582
}
560583

561584
/*

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

Lines changed: 36 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2381,7 +2381,7 @@ SELECT * FROM bug6051;
23812381
CREATE TEMP TABLE bug6051_2 (i int);
23822382
CREATE RULE bug6051_ins AS ON INSERT TO bug6051 DO INSTEAD
23832383
INSERT INTO bug6051_2
2384-
SELECTNEW.i;
2384+
VALUES(NEW.i);
23852385
WITH t1 AS ( DELETE FROM bug6051 RETURNING * )
23862386
INSERT INTO bug6051 SELECT * FROM t1;
23872387
SELECT * FROM bug6051;
@@ -2397,6 +2397,41 @@ SELECT * FROM bug6051_2;
23972397
3
23982398
(3 rows)
23992399

2400+
-- check INSERT...SELECT rule actions are disallowed on commands
2401+
-- that have modifyingCTEs
2402+
CREATE OR REPLACE RULE bug6051_ins AS ON INSERT TO bug6051 DO INSTEAD
2403+
INSERT INTO bug6051_2
2404+
SELECT NEW.i;
2405+
WITH t1 AS ( DELETE FROM bug6051 RETURNING * )
2406+
INSERT INTO bug6051 SELECT * FROM t1;
2407+
ERROR: INSERT...SELECT rule actions are not supported for queries having data-modifying statements in WITH
2408+
-- silly example to verify that hasModifyingCTE flag is propagated
2409+
CREATE TEMP TABLE bug6051_3 AS
2410+
SELECT a FROM generate_series(11,13) AS a;
2411+
CREATE RULE bug6051_3_ins AS ON INSERT TO bug6051_3 DO INSTEAD
2412+
SELECT i FROM bug6051_2;
2413+
BEGIN; SET LOCAL force_parallel_mode = on;
2414+
WITH t1 AS ( DELETE FROM bug6051_3 RETURNING * )
2415+
INSERT INTO bug6051_3 SELECT * FROM t1;
2416+
i
2417+
---
2418+
1
2419+
2
2420+
3
2421+
1
2422+
2
2423+
3
2424+
1
2425+
2
2426+
3
2427+
(9 rows)
2428+
2429+
COMMIT;
2430+
SELECT * FROM bug6051_3;
2431+
a
2432+
---
2433+
(0 rows)
2434+
24002435
-- a truly recursive CTE in the same list
24012436
WITH RECURSIVE t(a) AS (
24022437
SELECT 0

‎src/test/regress/sql/with.sql

Lines changed: 26 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1118,14 +1118,39 @@ CREATE TEMP TABLE bug6051_2 (i int);
11181118

11191119
CREATERULEbug6051_insASON INSERT TO bug6051 DO INSTEAD
11201120
INSERT INTO bug6051_2
1121-
SELECTNEW.i;
1121+
VALUES(NEW.i);
11221122

11231123
WITH t1AS (DELETEFROM bug6051 RETURNING* )
11241124
INSERT INTO bug6051SELECT*FROM t1;
11251125

11261126
SELECT*FROM bug6051;
11271127
SELECT*FROM bug6051_2;
11281128

1129+
-- check INSERT...SELECT rule actions are disallowed on commands
1130+
-- that have modifyingCTEs
1131+
CREATE OR REPLACERULEbug6051_insASON INSERT TO bug6051 DO INSTEAD
1132+
INSERT INTO bug6051_2
1133+
SELECTNEW.i;
1134+
1135+
WITH t1AS (DELETEFROM bug6051 RETURNING* )
1136+
INSERT INTO bug6051SELECT*FROM t1;
1137+
1138+
-- silly example to verify that hasModifyingCTE flag is propagated
1139+
CREATE TEMP TABLE bug6051_3AS
1140+
SELECT aFROM generate_series(11,13)AS a;
1141+
1142+
CREATERULEbug6051_3_insASON INSERT TO bug6051_3 DO INSTEAD
1143+
SELECT iFROM bug6051_2;
1144+
1145+
BEGIN;SET LOCAL force_parallel_mode=on;
1146+
1147+
WITH t1AS (DELETEFROM bug6051_3 RETURNING* )
1148+
INSERT INTO bug6051_3SELECT*FROM t1;
1149+
1150+
COMMIT;
1151+
1152+
SELECT*FROM bug6051_3;
1153+
11291154
-- a truly recursive CTE in the same list
11301155
WITH RECURSIVE t(a)AS (
11311156
SELECT0

0 commit comments

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