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

Commit3f50b82

Browse files
committed
Fix EXPLAIN to handle SEARCH BREADTH FIRST queries.
The rewriter transformation for SEARCH BREADTH FIRST produces aFieldSelect on a Var of type RECORD, where the Var references therecursive union's worktable output. EXPLAIN VERBOSE failed to handlethis case, because it only expected such Vars to appear in CteScansnot WorkTableScans. Fix that, and add some test cases exercisingEXPLAIN on SEARCH and CYCLE queries.In principle this oversight is an old bug, but it seems that thecase is unreachable without SEARCH BREADTH FIRST, because theparser fails when attempting to create such a reference manually.So for today I'll just patch HEAD/v14. Someday we might find thatthe code portion of this patch needs to be back-patched further.Per report from Atsushi Torikoshi.Discussion:https://postgr.es/m/5bafa66ad529e11860339565c9e7c166@oss.nttdata.com
1 parent4ac0f45 commit3f50b82

File tree

3 files changed

+170
-3
lines changed

3 files changed

+170
-3
lines changed

‎src/backend/utils/adt/ruleutils.c

Lines changed: 37 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -375,6 +375,8 @@ static void identify_join_columns(JoinExpr *j, RangeTblEntry *jrte,
375375
deparse_columns*colinfo);
376376
staticchar*get_rtable_name(intrtindex,deparse_context*context);
377377
staticvoidset_deparse_plan(deparse_namespace*dpns,Plan*plan);
378+
staticPlan*find_recursive_union(deparse_namespace*dpns,
379+
WorkTableScan*wtscan);
378380
staticvoidpush_child_plan(deparse_namespace*dpns,Plan*plan,
379381
deparse_namespace*save_dpns);
380382
staticvoidpop_child_plan(deparse_namespace*dpns,
@@ -4866,6 +4868,9 @@ set_deparse_plan(deparse_namespace *dpns, Plan *plan)
48664868
* For a SubqueryScan, pretend the subplan is INNER referent. (We don't
48674869
* use OUTER because that could someday conflict with the normal meaning.)
48684870
* Likewise, for a CteScan, pretend the subquery's plan is INNER referent.
4871+
* For a WorkTableScan, locate the parent RecursiveUnion plan node and use
4872+
* that as INNER referent.
4873+
*
48694874
* For ON CONFLICT .. UPDATE we just need the inner tlist to point to the
48704875
* excluded expression's tlist. (Similar to the SubqueryScan we don't want
48714876
* to reuse OUTER, it's used for RETURNING in some modify table cases,
@@ -4876,6 +4881,9 @@ set_deparse_plan(deparse_namespace *dpns, Plan *plan)
48764881
elseif (IsA(plan,CteScan))
48774882
dpns->inner_plan=list_nth(dpns->subplans,
48784883
((CteScan*)plan)->ctePlanId-1);
4884+
elseif (IsA(plan,WorkTableScan))
4885+
dpns->inner_plan=find_recursive_union(dpns,
4886+
(WorkTableScan*)plan);
48794887
elseif (IsA(plan,ModifyTable))
48804888
dpns->inner_plan=plan;
48814889
else
@@ -4899,6 +4907,29 @@ set_deparse_plan(deparse_namespace *dpns, Plan *plan)
48994907
dpns->index_tlist=NIL;
49004908
}
49014909

4910+
/*
4911+
* Locate the ancestor plan node that is the RecursiveUnion generating
4912+
* the WorkTableScan's work table. We can match on wtParam, since that
4913+
* should be unique within the plan tree.
4914+
*/
4915+
staticPlan*
4916+
find_recursive_union(deparse_namespace*dpns,WorkTableScan*wtscan)
4917+
{
4918+
ListCell*lc;
4919+
4920+
foreach(lc,dpns->ancestors)
4921+
{
4922+
Plan*ancestor= (Plan*)lfirst(lc);
4923+
4924+
if (IsA(ancestor,RecursiveUnion)&&
4925+
((RecursiveUnion*)ancestor)->wtParam==wtscan->wtParam)
4926+
returnancestor;
4927+
}
4928+
elog(ERROR,"could not find RecursiveUnion for WorkTableScan with wtParam %d",
4929+
wtscan->wtParam);
4930+
returnNULL;
4931+
}
4932+
49024933
/*
49034934
* push_child_plan: temporarily transfer deparsing attention to a child plan
49044935
*
@@ -7646,9 +7677,12 @@ get_name_for_var_field(Var *var, int fieldno,
76467677
{
76477678
/*
76487679
* We're deparsing a Plan tree so we don't have a CTE
7649-
* list. But the only place we'd see a Var directly
7650-
* referencing a CTE RTE is in a CteScan plan node, and we
7651-
* can look into the subplan's tlist instead.
7680+
* list. But the only places we'd see a Var directly
7681+
* referencing a CTE RTE are in CteScan or WorkTableScan
7682+
* plan nodes. For those cases, set_deparse_plan arranged
7683+
* for dpns->inner_plan to be the plan node that emits the
7684+
* CTE or RecursiveUnion result, and we can look at its
7685+
* tlist instead.
76527686
*/
76537687
TargetEntry*tle;
76547688
deparse_namespacesave_dpns;

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

Lines changed: 103 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -644,6 +644,41 @@ insert into graph0 values
644644
(2, 3, 'arc 2 -> 3'),
645645
(1, 4, 'arc 1 -> 4'),
646646
(4, 5, 'arc 4 -> 5');
647+
explain (verbose, costs off)
648+
with recursive search_graph(f, t, label) as (
649+
select * from graph0 g
650+
union all
651+
select g.*
652+
from graph0 g, search_graph sg
653+
where g.f = sg.t
654+
) search depth first by f, t set seq
655+
select * from search_graph order by seq;
656+
QUERY PLAN
657+
----------------------------------------------------------------------------------------------
658+
Sort
659+
Output: search_graph.f, search_graph.t, search_graph.label, search_graph.seq
660+
Sort Key: search_graph.seq
661+
CTE search_graph
662+
-> Recursive Union
663+
-> Seq Scan on pg_temp.graph0 g
664+
Output: g.f, g.t, g.label, ARRAY[ROW(g.f, g.t)]
665+
-> Merge Join
666+
Output: g_1.f, g_1.t, g_1.label, array_cat(sg.seq, ARRAY[ROW(g_1.f, g_1.t)])
667+
Merge Cond: (g_1.f = sg.t)
668+
-> Sort
669+
Output: g_1.f, g_1.t, g_1.label
670+
Sort Key: g_1.f
671+
-> Seq Scan on pg_temp.graph0 g_1
672+
Output: g_1.f, g_1.t, g_1.label
673+
-> Sort
674+
Output: sg.seq, sg.t
675+
Sort Key: sg.t
676+
-> WorkTable Scan on search_graph sg
677+
Output: sg.seq, sg.t
678+
-> CTE Scan on search_graph
679+
Output: search_graph.f, search_graph.t, search_graph.label, search_graph.seq
680+
(22 rows)
681+
647682
with recursive search_graph(f, t, label) as (
648683
select * from graph0 g
649684
union all
@@ -682,6 +717,41 @@ select * from search_graph order by seq;
682717
4 | 5 | arc 4 -> 5 | {"(4,5)"}
683718
(7 rows)
684719

720+
explain (verbose, costs off)
721+
with recursive search_graph(f, t, label) as (
722+
select * from graph0 g
723+
union all
724+
select g.*
725+
from graph0 g, search_graph sg
726+
where g.f = sg.t
727+
) search breadth first by f, t set seq
728+
select * from search_graph order by seq;
729+
QUERY PLAN
730+
-------------------------------------------------------------------------------------------------
731+
Sort
732+
Output: search_graph.f, search_graph.t, search_graph.label, search_graph.seq
733+
Sort Key: search_graph.seq
734+
CTE search_graph
735+
-> Recursive Union
736+
-> Seq Scan on pg_temp.graph0 g
737+
Output: g.f, g.t, g.label, ROW('0'::bigint, g.f, g.t)
738+
-> Merge Join
739+
Output: g_1.f, g_1.t, g_1.label, ROW(int8inc((sg.seq)."*DEPTH*"), g_1.f, g_1.t)
740+
Merge Cond: (g_1.f = sg.t)
741+
-> Sort
742+
Output: g_1.f, g_1.t, g_1.label
743+
Sort Key: g_1.f
744+
-> Seq Scan on pg_temp.graph0 g_1
745+
Output: g_1.f, g_1.t, g_1.label
746+
-> Sort
747+
Output: sg.seq, sg.t
748+
Sort Key: sg.t
749+
-> WorkTable Scan on search_graph sg
750+
Output: sg.seq, sg.t
751+
-> CTE Scan on search_graph
752+
Output: search_graph.f, search_graph.t, search_graph.label, search_graph.seq
753+
(22 rows)
754+
685755
with recursive search_graph(f, t, label) as (
686756
select * from graph0 g
687757
union all
@@ -945,6 +1015,39 @@ select * from search_graph order by path;
9451015
(25 rows)
9461016

9471017
-- CYCLE clause
1018+
explain (verbose, costs off)
1019+
with recursive search_graph(f, t, label) as (
1020+
select * from graph g
1021+
union all
1022+
select g.*
1023+
from graph g, search_graph sg
1024+
where g.f = sg.t
1025+
) cycle f, t set is_cycle using path
1026+
select * from search_graph;
1027+
QUERY PLAN
1028+
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
1029+
CTE Scan on search_graph
1030+
Output: search_graph.f, search_graph.t, search_graph.label, search_graph.is_cycle, search_graph.path
1031+
CTE search_graph
1032+
-> Recursive Union
1033+
-> Seq Scan on pg_temp.graph g
1034+
Output: g.f, g.t, g.label, false, ARRAY[ROW(g.f, g.t)]
1035+
-> Merge Join
1036+
Output: g_1.f, g_1.t, g_1.label, CASE WHEN (ROW(g_1.f, g_1.t) = ANY (sg.path)) THEN true ELSE false END, array_cat(sg.path, ARRAY[ROW(g_1.f, g_1.t)])
1037+
Merge Cond: (g_1.f = sg.t)
1038+
-> Sort
1039+
Output: g_1.f, g_1.t, g_1.label
1040+
Sort Key: g_1.f
1041+
-> Seq Scan on pg_temp.graph g_1
1042+
Output: g_1.f, g_1.t, g_1.label
1043+
-> Sort
1044+
Output: sg.path, sg.t
1045+
Sort Key: sg.t
1046+
-> WorkTable Scan on search_graph sg
1047+
Output: sg.path, sg.t
1048+
Filter: (NOT sg.is_cycle)
1049+
(20 rows)
1050+
9481051
with recursive search_graph(f, t, label) as (
9491052
select * from graph g
9501053
union all

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

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -358,6 +358,16 @@ insert into graph0 values
358358
(1,4,'arc 1 -> 4'),
359359
(4,5,'arc 4 -> 5');
360360

361+
explain (verbose, costs off)
362+
with recursive search_graph(f, t, label)as (
363+
select*from graph0 g
364+
union all
365+
select g.*
366+
from graph0 g, search_graph sg
367+
whereg.f=sg.t
368+
) search depth first by f, tset seq
369+
select*from search_graphorder by seq;
370+
361371
with recursive search_graph(f, t, label)as (
362372
select*from graph0 g
363373
union all
@@ -376,6 +386,16 @@ with recursive search_graph(f, t, label) as (
376386
) search depth first by f, tset seq
377387
select*from search_graphorder by seq;
378388

389+
explain (verbose, costs off)
390+
with recursive search_graph(f, t, label)as (
391+
select*from graph0 g
392+
union all
393+
select g.*
394+
from graph0 g, search_graph sg
395+
whereg.f=sg.t
396+
) search breadth first by f, tset seq
397+
select*from search_graphorder by seq;
398+
379399
with recursive search_graph(f, t, label)as (
380400
select*from graph0 g
381401
union all
@@ -503,6 +523,16 @@ select * from search_graph order by path;
503523

504524
-- CYCLE clause
505525

526+
explain (verbose, costs off)
527+
with recursive search_graph(f, t, label)as (
528+
select*from graph g
529+
union all
530+
select g.*
531+
from graph g, search_graph sg
532+
whereg.f=sg.t
533+
) cycle f, tset is_cycle usingpath
534+
select*from search_graph;
535+
506536
with recursive search_graph(f, t, label)as (
507537
select*from graph g
508538
union all

0 commit comments

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