aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/utils/adt/ruleutils.c40
-rw-r--r--src/test/regress/expected/with.out103
-rw-r--r--src/test/regress/sql/with.sql30
3 files changed, 170 insertions, 3 deletions
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index b932a83827f..b15bd81b9ca 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -375,6 +375,8 @@ static void identify_join_columns(JoinExpr *j, RangeTblEntry *jrte,
deparse_columns *colinfo);
static char *get_rtable_name(int rtindex, deparse_context *context);
static void set_deparse_plan(deparse_namespace *dpns, Plan *plan);
+static Plan *find_recursive_union(deparse_namespace *dpns,
+ WorkTableScan *wtscan);
static void push_child_plan(deparse_namespace *dpns, Plan *plan,
deparse_namespace *save_dpns);
static void pop_child_plan(deparse_namespace *dpns,
@@ -4866,6 +4868,9 @@ set_deparse_plan(deparse_namespace *dpns, Plan *plan)
* For a SubqueryScan, pretend the subplan is INNER referent. (We don't
* use OUTER because that could someday conflict with the normal meaning.)
* Likewise, for a CteScan, pretend the subquery's plan is INNER referent.
+ * For a WorkTableScan, locate the parent RecursiveUnion plan node and use
+ * that as INNER referent.
+ *
* For ON CONFLICT .. UPDATE we just need the inner tlist to point to the
* excluded expression's tlist. (Similar to the SubqueryScan we don't want
* 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)
else if (IsA(plan, CteScan))
dpns->inner_plan = list_nth(dpns->subplans,
((CteScan *) plan)->ctePlanId - 1);
+ else if (IsA(plan, WorkTableScan))
+ dpns->inner_plan = find_recursive_union(dpns,
+ (WorkTableScan *) plan);
else if (IsA(plan, ModifyTable))
dpns->inner_plan = plan;
else
@@ -4900,6 +4908,29 @@ set_deparse_plan(deparse_namespace *dpns, Plan *plan)
}
/*
+ * Locate the ancestor plan node that is the RecursiveUnion generating
+ * the WorkTableScan's work table. We can match on wtParam, since that
+ * should be unique within the plan tree.
+ */
+static Plan *
+find_recursive_union(deparse_namespace *dpns, WorkTableScan *wtscan)
+{
+ ListCell *lc;
+
+ foreach(lc, dpns->ancestors)
+ {
+ Plan *ancestor = (Plan *) lfirst(lc);
+
+ if (IsA(ancestor, RecursiveUnion) &&
+ ((RecursiveUnion *) ancestor)->wtParam == wtscan->wtParam)
+ return ancestor;
+ }
+ elog(ERROR, "could not find RecursiveUnion for WorkTableScan with wtParam %d",
+ wtscan->wtParam);
+ return NULL;
+}
+
+/*
* push_child_plan: temporarily transfer deparsing attention to a child plan
*
* When expanding an OUTER_VAR or INNER_VAR reference, we must adjust the
@@ -7646,9 +7677,12 @@ get_name_for_var_field(Var *var, int fieldno,
{
/*
* We're deparsing a Plan tree so we don't have a CTE
- * list. But the only place we'd see a Var directly
- * referencing a CTE RTE is in a CteScan plan node, and we
- * can look into the subplan's tlist instead.
+ * list. But the only places we'd see a Var directly
+ * referencing a CTE RTE are in CteScan or WorkTableScan
+ * plan nodes. For those cases, set_deparse_plan arranged
+ * for dpns->inner_plan to be the plan node that emits the
+ * CTE or RecursiveUnion result, and we can look at its
+ * tlist instead.
*/
TargetEntry *tle;
deparse_namespace save_dpns;
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index 622d5da7d27..a3a2e383e3c 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -644,6 +644,41 @@ insert into graph0 values
(2, 3, 'arc 2 -> 3'),
(1, 4, 'arc 1 -> 4'),
(4, 5, 'arc 4 -> 5');
+explain (verbose, costs off)
+with recursive search_graph(f, t, label) as (
+ select * from graph0 g
+ union all
+ select g.*
+ from graph0 g, search_graph sg
+ where g.f = sg.t
+) search depth first by f, t set seq
+select * from search_graph order by seq;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
+ Sort
+ Output: search_graph.f, search_graph.t, search_graph.label, search_graph.seq
+ Sort Key: search_graph.seq
+ CTE search_graph
+ -> Recursive Union
+ -> Seq Scan on pg_temp.graph0 g
+ Output: g.f, g.t, g.label, ARRAY[ROW(g.f, g.t)]
+ -> Merge Join
+ Output: g_1.f, g_1.t, g_1.label, array_cat(sg.seq, ARRAY[ROW(g_1.f, g_1.t)])
+ Merge Cond: (g_1.f = sg.t)
+ -> Sort
+ Output: g_1.f, g_1.t, g_1.label
+ Sort Key: g_1.f
+ -> Seq Scan on pg_temp.graph0 g_1
+ Output: g_1.f, g_1.t, g_1.label
+ -> Sort
+ Output: sg.seq, sg.t
+ Sort Key: sg.t
+ -> WorkTable Scan on search_graph sg
+ Output: sg.seq, sg.t
+ -> CTE Scan on search_graph
+ Output: search_graph.f, search_graph.t, search_graph.label, search_graph.seq
+(22 rows)
+
with recursive search_graph(f, t, label) as (
select * from graph0 g
union all
@@ -682,6 +717,41 @@ select * from search_graph order by seq;
4 | 5 | arc 4 -> 5 | {"(4,5)"}
(7 rows)
+explain (verbose, costs off)
+with recursive search_graph(f, t, label) as (
+ select * from graph0 g
+ union all
+ select g.*
+ from graph0 g, search_graph sg
+ where g.f = sg.t
+) search breadth first by f, t set seq
+select * from search_graph order by seq;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
+ Sort
+ Output: search_graph.f, search_graph.t, search_graph.label, search_graph.seq
+ Sort Key: search_graph.seq
+ CTE search_graph
+ -> Recursive Union
+ -> Seq Scan on pg_temp.graph0 g
+ Output: g.f, g.t, g.label, ROW('0'::bigint, g.f, g.t)
+ -> Merge Join
+ Output: g_1.f, g_1.t, g_1.label, ROW(int8inc((sg.seq)."*DEPTH*"), g_1.f, g_1.t)
+ Merge Cond: (g_1.f = sg.t)
+ -> Sort
+ Output: g_1.f, g_1.t, g_1.label
+ Sort Key: g_1.f
+ -> Seq Scan on pg_temp.graph0 g_1
+ Output: g_1.f, g_1.t, g_1.label
+ -> Sort
+ Output: sg.seq, sg.t
+ Sort Key: sg.t
+ -> WorkTable Scan on search_graph sg
+ Output: sg.seq, sg.t
+ -> CTE Scan on search_graph
+ Output: search_graph.f, search_graph.t, search_graph.label, search_graph.seq
+(22 rows)
+
with recursive search_graph(f, t, label) as (
select * from graph0 g
union all
@@ -945,6 +1015,39 @@ select * from search_graph order by path;
(25 rows)
-- CYCLE clause
+explain (verbose, costs off)
+with recursive search_graph(f, t, label) as (
+ select * from graph g
+ union all
+ select g.*
+ from graph g, search_graph sg
+ where g.f = sg.t
+) cycle f, t set is_cycle using path
+select * from search_graph;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CTE Scan on search_graph
+ Output: search_graph.f, search_graph.t, search_graph.label, search_graph.is_cycle, search_graph.path
+ CTE search_graph
+ -> Recursive Union
+ -> Seq Scan on pg_temp.graph g
+ Output: g.f, g.t, g.label, false, ARRAY[ROW(g.f, g.t)]
+ -> Merge Join
+ 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)])
+ Merge Cond: (g_1.f = sg.t)
+ -> Sort
+ Output: g_1.f, g_1.t, g_1.label
+ Sort Key: g_1.f
+ -> Seq Scan on pg_temp.graph g_1
+ Output: g_1.f, g_1.t, g_1.label
+ -> Sort
+ Output: sg.path, sg.t
+ Sort Key: sg.t
+ -> WorkTable Scan on search_graph sg
+ Output: sg.path, sg.t
+ Filter: (NOT sg.is_cycle)
+(20 rows)
+
with recursive search_graph(f, t, label) as (
select * from graph g
union all
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index 0476c55fee1..46668a903ea 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -358,6 +358,16 @@ insert into graph0 values
(1, 4, 'arc 1 -> 4'),
(4, 5, 'arc 4 -> 5');
+explain (verbose, costs off)
+with recursive search_graph(f, t, label) as (
+ select * from graph0 g
+ union all
+ select g.*
+ from graph0 g, search_graph sg
+ where g.f = sg.t
+) search depth first by f, t set seq
+select * from search_graph order by seq;
+
with recursive search_graph(f, t, label) as (
select * from graph0 g
union all
@@ -376,6 +386,16 @@ with recursive search_graph(f, t, label) as (
) search depth first by f, t set seq
select * from search_graph order by seq;
+explain (verbose, costs off)
+with recursive search_graph(f, t, label) as (
+ select * from graph0 g
+ union all
+ select g.*
+ from graph0 g, search_graph sg
+ where g.f = sg.t
+) search breadth first by f, t set seq
+select * from search_graph order by seq;
+
with recursive search_graph(f, t, label) as (
select * from graph0 g
union all
@@ -503,6 +523,16 @@ select * from search_graph order by path;
-- CYCLE clause
+explain (verbose, costs off)
+with recursive search_graph(f, t, label) as (
+ select * from graph g
+ union all
+ select g.*
+ from graph g, search_graph sg
+ where g.f = sg.t
+) cycle f, t set is_cycle using path
+select * from search_graph;
+
with recursive search_graph(f, t, label) as (
select * from graph g
union all
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-30 10:23:17 +0000