Re: EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery

On Tue, Sep 5, 2023 at 3:23 PM Lepikhov Andrei <lepikhov(at)fastmail(dot)com>
wrote:

> Hi,
>
> While designing a CustomScan node, I got stuck into two errors:
> 1. "failed to find plan for CTE."
> 2. "failed to find plan for subquery."
> After a short research, I found commit 3f50b82, which shows the problem's
> origins - setrefs don't change the varno of custom_scan_tlist and can
> directly reference CTE or Subquery entry. In the "EXPLAIN VERBOSE" case,
> the deparsing routine can't find dpns->inner_plan for such an entry.

I was able to reproduce both errors with the help of the query in [1]
and the extension provided in [2]. It seems that the assumption in the
case of RTE_SUBQUERY and RTE_CTE in get_name_for_var_field() does not
always hold:

* the only place we'd see a Var directly referencing a
* SUBQUERY RTE is in a SubqueryScan plan node

* the only places we'd see a Var directly
* referencing a CTE RTE are in CteScan or WorkTableScan
* plan nodes.

But this issue shows that in a CustomScan node we can also see a Var
directly referencing a SUBQUERY RTE or CTE RTE. (I suspect that it also
happens with ForeignScan node.)

So it seems that we need to assign a proper INNER referent for
CustomScan node in set_deparse_plan(). I tried 'trick.diff' in [1]
which uses linitial(dpns->subplans), it fixes the query there but would
crash the query below.

explain (verbose, costs off)
select (rr).column2 from
(select r from (values(1,2),(3,4)) r) s join
(select rr from (values(1,7),(3,8)) rr limit 2) ss
on (r).column1 = (rr).column1;
server closed the connection unexpectedly

Maybe we can use the first plan in CustomScan->custom_plans as the INNER
referent? I'm not sure.

--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -5004,6 +5004,13 @@ set_deparse_plan(deparse_namespace *dpns, Plan *plan)
else if (IsA(plan, WorkTableScan))
dpns->inner_plan = find_recursive_union(dpns,
(WorkTableScan *) plan);
+ else if (IsA(plan, CustomScan))
+ {
+ CustomScan *cplan = (CustomScan *) plan;
+
+ if (cplan->custom_plans)
+ dpns->inner_plan = linitial(cplan->custom_plans);
+ }

Hi Tom, have you got a chance to look into this issue?

[1]
https://www.postgresql.org/message-id/3f7bcdb7-c263-4c06-a138-140f5c3898ed%40app.fastmail.com
[2]
https://www.postgresql.org/message-id/3933834e-b657-4ad1-bf4e-5f3fbba7ba14%40app.fastmail.com

Thanks
Richard