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

Commiteade004

Browse files
committed
Remove inadequate assertion check in CTE inlining.
inline_cte() expected to find exactly as many references to thetarget CTE as its cterefcount indicates. While that should beaccurate for the tree as emitted by the parser, there are someoptimizations that occur upstream of here that could falsify it,notably removal of unused subquery output expressions.Trying to make the accounting 100% accurate seems expensive anddoomed to future breakage. It's not really worth it, becauseall this code is protecting is downstream assumptions that everyreferenced CTE has a plan. Let's convert those assertions toregular test-and-elog just in case there's some actual problem,and then drop the failing assertion.Per report from Tomas Vondra (thanks also to Richard Guo foranalysis). Back-patch to v12 where the faulty code came in.Discussion:https://postgr.es/m/29196a1e-ed47-c7ca-9be2-b1c636816183@enterprisedb.com
1 parent17bdba3 commiteade004

File tree

6 files changed

+101
-11
lines changed

6 files changed

+101
-11
lines changed

‎src/backend/optimizer/path/allpaths.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2541,7 +2541,8 @@ set_cte_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
25412541
if (ndx >=list_length(cteroot->cte_plan_ids))
25422542
elog(ERROR,"could not find plan for CTE \"%s\"",rte->ctename);
25432543
plan_id=list_nth_int(cteroot->cte_plan_ids,ndx);
2544-
Assert(plan_id>0);
2544+
if (plan_id <=0)
2545+
elog(ERROR,"no plan was made for CTE \"%s\"",rte->ctename);
25452546
cteplan= (Plan*)list_nth(root->glob->subplans,plan_id-1);
25462547

25472548
/* Mark rel with estimated output rows, width, etc */

‎src/backend/optimizer/plan/createplan.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3636,7 +3636,8 @@ create_ctescan_plan(PlannerInfo *root, Path *best_path,
36363636
if (ndx >=list_length(cteroot->cte_plan_ids))
36373637
elog(ERROR,"could not find plan for CTE \"%s\"",rte->ctename);
36383638
plan_id=list_nth_int(cteroot->cte_plan_ids,ndx);
3639-
Assert(plan_id>0);
3639+
if (plan_id <=0)
3640+
elog(ERROR,"no plan was made for CTE \"%s\"",rte->ctename);
36403641
foreach(lc,cteroot->init_plans)
36413642
{
36423643
ctesplan= (SubPlan*)lfirst(lc);

‎src/backend/optimizer/plan/subselect.c

Lines changed: 0 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -64,7 +64,6 @@ typedef struct inline_cte_walker_context
6464
{
6565
constchar*ctename;/* name and relative level of target CTE */
6666
intlevelsup;
67-
intrefcount;/* number of remaining references */
6867
Query*ctequery;/* query to substitute */
6968
}inline_cte_walker_context;
7069

@@ -1131,13 +1130,9 @@ inline_cte(PlannerInfo *root, CommonTableExpr *cte)
11311130
context.ctename=cte->ctename;
11321131
/* Start at levelsup = -1 because we'll immediately increment it */
11331132
context.levelsup=-1;
1134-
context.refcount=cte->cterefcount;
11351133
context.ctequery=castNode(Query,cte->ctequery);
11361134

11371135
(void)inline_cte_walker((Node*)root->parse,&context);
1138-
1139-
/* Assert we replaced all references */
1140-
Assert(context.refcount==0);
11411136
}
11421137

11431138
staticbool
@@ -1200,9 +1195,6 @@ inline_cte_walker(Node *node, inline_cte_walker_context *context)
12001195
rte->coltypes=NIL;
12011196
rte->coltypmods=NIL;
12021197
rte->colcollations=NIL;
1203-
1204-
/* Count the number of replacements we've done */
1205-
context->refcount--;
12061198
}
12071199

12081200
return false;

‎src/include/nodes/pathnodes.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -258,7 +258,8 @@ struct PlannerInfo
258258

259259
List*init_plans;/* init SubPlans for query */
260260

261-
List*cte_plan_ids;/* per-CTE-item list of subplan IDs */
261+
List*cte_plan_ids;/* per-CTE-item list of subplan IDs (or -1 if
262+
* no subplan was made for that CTE) */
262263

263264
List*multiexpr_params;/* List of Lists of Params for MULTIEXPR
264265
* subquery outputs */

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

Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1707,6 +1707,70 @@ SELECT * FROM bug6051_3;
17071707
---
17081708
(0 rows)
17091709

1710+
-- check case where CTE reference is removed due to optimization
1711+
EXPLAIN (VERBOSE, COSTS OFF)
1712+
SELECT q1 FROM
1713+
(
1714+
WITH t_cte AS (SELECT * FROM int8_tbl t)
1715+
SELECT q1, (SELECT q2 FROM t_cte WHERE t_cte.q1 = i8.q1) AS t_sub
1716+
FROM int8_tbl i8
1717+
) ss;
1718+
QUERY PLAN
1719+
--------------------------------------
1720+
Subquery Scan on ss
1721+
Output: ss.q1
1722+
-> Seq Scan on public.int8_tbl i8
1723+
Output: i8.q1, NULL::bigint
1724+
(4 rows)
1725+
1726+
SELECT q1 FROM
1727+
(
1728+
WITH t_cte AS (SELECT * FROM int8_tbl t)
1729+
SELECT q1, (SELECT q2 FROM t_cte WHERE t_cte.q1 = i8.q1) AS t_sub
1730+
FROM int8_tbl i8
1731+
) ss;
1732+
q1
1733+
------------------
1734+
123
1735+
123
1736+
4567890123456789
1737+
4567890123456789
1738+
4567890123456789
1739+
(5 rows)
1740+
1741+
EXPLAIN (VERBOSE, COSTS OFF)
1742+
SELECT q1 FROM
1743+
(
1744+
WITH t_cte AS MATERIALIZED (SELECT * FROM int8_tbl t)
1745+
SELECT q1, (SELECT q2 FROM t_cte WHERE t_cte.q1 = i8.q1) AS t_sub
1746+
FROM int8_tbl i8
1747+
) ss;
1748+
QUERY PLAN
1749+
---------------------------------------------
1750+
Subquery Scan on ss
1751+
Output: ss.q1
1752+
-> Seq Scan on public.int8_tbl i8
1753+
Output: i8.q1, NULL::bigint
1754+
CTE t_cte
1755+
-> Seq Scan on public.int8_tbl t
1756+
Output: t.q1, t.q2
1757+
(7 rows)
1758+
1759+
SELECT q1 FROM
1760+
(
1761+
WITH t_cte AS MATERIALIZED (SELECT * FROM int8_tbl t)
1762+
SELECT q1, (SELECT q2 FROM t_cte WHERE t_cte.q1 = i8.q1) AS t_sub
1763+
FROM int8_tbl i8
1764+
) ss;
1765+
q1
1766+
------------------
1767+
123
1768+
123
1769+
4567890123456789
1770+
4567890123456789
1771+
4567890123456789
1772+
(5 rows)
1773+
17101774
-- a truly recursive CTE in the same list
17111775
WITH RECURSIVE t(a) AS (
17121776
SELECT 0

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

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -798,6 +798,37 @@ COMMIT;
798798

799799
SELECT*FROM bug6051_3;
800800

801+
-- check case where CTE reference is removed due to optimization
802+
EXPLAIN (VERBOSE, COSTS OFF)
803+
SELECT q1FROM
804+
(
805+
WITH t_cteAS (SELECT*FROM int8_tbl t)
806+
SELECT q1, (SELECT q2FROM t_cteWHEREt_cte.q1=i8.q1)AS t_sub
807+
FROM int8_tbl i8
808+
) ss;
809+
810+
SELECT q1FROM
811+
(
812+
WITH t_cteAS (SELECT*FROM int8_tbl t)
813+
SELECT q1, (SELECT q2FROM t_cteWHEREt_cte.q1=i8.q1)AS t_sub
814+
FROM int8_tbl i8
815+
) ss;
816+
817+
EXPLAIN (VERBOSE, COSTS OFF)
818+
SELECT q1FROM
819+
(
820+
WITH t_cteAS MATERIALIZED (SELECT*FROM int8_tbl t)
821+
SELECT q1, (SELECT q2FROM t_cteWHEREt_cte.q1=i8.q1)AS t_sub
822+
FROM int8_tbl i8
823+
) ss;
824+
825+
SELECT q1FROM
826+
(
827+
WITH t_cteAS MATERIALIZED (SELECT*FROM int8_tbl t)
828+
SELECT q1, (SELECT q2FROM t_cteWHEREt_cte.q1=i8.q1)AS t_sub
829+
FROM int8_tbl i8
830+
) ss;
831+
801832
-- a truly recursive CTE in the same list
802833
WITH RECURSIVE t(a)AS (
803834
SELECT0

0 commit comments

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