Fix EXPLAIN to handle SEARCH BREADTH FIRST queries.

tglsfdc · tglsfdc · commit 388726753b63 · 2021-09-16T10:45:42.000-04:00
The rewriter transformation for SEARCH BREADTH FIRST produces a FieldSelect on a Var of type RECORD, where the Var references the recursive union's worktable output. EXPLAIN VERBOSE failed to handle this case, because it only expected such Vars to appear in CteScans not WorkTableScans. Fix that, and add some test cases exercising EXPLAIN on SEARCH and CYCLE queries. In principle this oversight is an old bug, but it seems that the case is unreachable without SEARCH BREADTH FIRST, because the parser fails when attempting to create such a reference manually. So for today I'll just patch HEAD/v14. Someday we might find that the 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
diff --git 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
@@ -4899,6 +4907,29 @@ set_deparse_plan(deparse_namespace *dpns, Plan *plan)
 		dpns->index_tlist = NIL;
 }
 
+/*
+ * 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
  *
@@ -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
@@ -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
@@ -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
 --
