aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/backend/utils/adt/ruleutils.c40
-rw-r--r--src/test/regress/expected/with.out74
-rw-r--r--src/test/regress/sql/with.sql24
3 files changed, 133 insertions, 5 deletions
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 00f21b10dda..871f3edc923 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..8aafacaf7c3 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -637,13 +637,48 @@ SELECT t1.id, t2.path, t2 FROM t AS t1 JOIN t AS t2 ON
(16 rows)
-- SEARCH clause
-create temp table graph0( f int, t int, label text );
+create table graph0( f int, t int, label text );
insert into graph0 values
(1, 2, 'arc 1 -> 2'),
(1, 3, 'arc 1 -> 3'),
(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 public.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 public.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 public.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 public.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
@@ -820,6 +890,8 @@ select * from v_search;
4 | 5 | arc 4 -> 5
(7 rows)
+drop table graph0 cascade;
+NOTICE: drop cascades to view v_search
--
-- test cycle detection
--
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index 0476c55fee1..2bd049a7615 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -349,7 +349,7 @@ SELECT t1.id, t2.path, t2 FROM t AS t1 JOIN t AS t2 ON
-- SEARCH clause
-create temp table graph0( f int, t int, label text );
+create table graph0( f int, t int, label text );
insert into graph0 values
(1, 2, 'arc 1 -> 2'),
@@ -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
@@ -459,6 +479,8 @@ select pg_get_viewdef('v_search');
select * from v_search;
+drop table graph0 cascade;
+
--
-- test cycle detection
--
generated by cgit v1.2.3 (git 2.25.1) at 2025-08-04 19:51:24 +0000