pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedEXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery
7+ messages / 5 participants
[nested] [flat]
* EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery
@ 2023-09-05 05:31 Lepikhov Andrei <lepikhov@fastmail.com>
2023-09-11 06:28 ` Re: EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Lepikhov Andrei @ 2023-09-05 05:31 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; tgl@sss.pgh.pa.us; +Cc: Алена Рыбакина <a.rybakina@postgrespro.ru>; Дамир Белялов <d.belyalov@postgrespro.ru>
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.
Regards,
Andrei Lepikhov
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery
2023-09-05 05:31 EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery Lepikhov Andrei <lepikhov@fastmail.com>
@ 2023-09-11 06:28 ` Richard Guo <guofenglinux@gmail.com>
2023-09-11 09:03 ` Re: EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery Lepikhov Andrei <lepikhov@fastmail.com>
2023-09-14 04:58 ` Re: EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery Lepikhov Andrei <lepikhov@fastmail.com>
2023-09-28 06:32 ` Re: EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery Andrey Lepikhov <a.lepikhov@postgrespro.ru>
0 siblings, 3 replies; 7+ messages in thread
From: Richard Guo @ 2023-09-11 06:28 UTC (permalink / raw)
To: Lepikhov Andrei <lepikhov@fastmail.com>; +Cc: pgsql-bugs@lists.postgresql.org, tgl@sss.pgh.pa.us, Алена Рыбакина <a.rybakina@postgrespro.ru>; Дамир Белялов <d.belyalov@postgrespro.ru>
On Tue, Sep 5, 2023 at 3:23 PM Lepikhov Andrei <lepikhov@fastmail.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.
inline patch 1: src/backend/utils/adt/ruleutils.c (+7/-0 lines) download inline-1.patch
--- 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);
+ }
* Re: EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery
2023-09-05 05:31 EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery Lepikhov Andrei <lepikhov@fastmail.com>
2023-09-11 06:28 ` Re: EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery Richard Guo <guofenglinux@gmail.com>
@ 2023-09-11 09:03 ` Lepikhov Andrei <lepikhov@fastmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Lepikhov Andrei @ 2023-09-11 09:03 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org, tgl@sss.pgh.pa.us, Алена Рыбакина <a.rybakina@postgrespro.ru>; Дамир Белялов <d.belyalov@postgrespro.ru>
On Mon, Sep 11, 2023, at 1:28 PM, Richard Guo wrote:
> On Tue, Sep 5, 2023 at 3:23 PM Lepikhov Andrei <lepikhov@fastmail.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.
I have written the letter with second thoughts, because the logic of building CustomScan target list isn't clear for me. Maybe I just have made a mistake in my code?
>
> 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.)
Maybe, but I couldn't imagine such a situation.
>
> 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.
I see here two different cases. Direct link to a Subquery need rte->subquery, which was nullified due to optimization. The case with reference to CTE is more complex. which subplan of the statement subplans should we refer here? But it is my first glance into this code, maybe someone understand it better.
--
Regards,
Andrei Lepikhov
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery
2023-09-05 05:31 EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery Lepikhov Andrei <lepikhov@fastmail.com>
2023-09-11 06:28 ` Re: EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery Richard Guo <guofenglinux@gmail.com>
@ 2023-09-14 04:58 ` Lepikhov Andrei <lepikhov@fastmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Lepikhov Andrei @ 2023-09-14 04:58 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org, "Tom Lane" <tgl@sss.pgh.pa.us>; Алена Рыбакина <a.rybakina@postgrespro.ru>; Дамир Белялов <d.belyalov@postgrespro.ru>
On Mon, Sep 11, 2023, at 1:28 PM, Richard Guo wrote:
> Maybe we can use the first plan in CustomScan->custom_plans as the INNER
> referent? I'm not sure.
After some exploration of ruleutils.c I grasped more on this issue.
If we have come to the CTE or SubPlan entry by the INDEX_VAR link from custom_scan_tlist, we don't have any information on which subplan to go further (entry info already removed because of optimization).
To find a specific subplan, we must pass through the custom node subtree and find CteScan or SubqueryScan, whose target list refers to this entry. After that, we can go into the subplan to correctly explain the var.
It looks like a lot of work. One vague idea is to change set_customscan_references and wrap up CustomScan with useful data.
Or, maybe it is better not to try to go under the custom_scan_tlist and explain CTE and Subquery right on the level of the CustomScan?
--
Regards,
Andrei Lepikhov
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery
2023-09-05 05:31 EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery Lepikhov Andrei <lepikhov@fastmail.com>
2023-09-11 06:28 ` Re: EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery Richard Guo <guofenglinux@gmail.com>
@ 2023-09-28 06:32 ` Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2023-10-09 08:41 ` Re: EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery Andrei Lepikhov <a.lepikhov@postgrespro.ru>
2 siblings, 1 reply; 7+ messages in thread
From: Andrey Lepikhov @ 2023-09-28 06:32 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; Lepikhov Andrei <lepikhov@fastmail.com>; +Cc: pgsql-bugs@lists.postgresql.org, tgl@sss.pgh.pa.us, Алена Рыбакина <a.rybakina@postgrespro.ru>; Дамир Белялов <d.belyalov@postgrespro.ru>
On 11/9/2023 13:28, Richard Guo wrote:
> 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.
Looking into this issue more, I figured out that we can't be sure from
which side the current varno of custom_scan_tlist was produced: from
inner, outer or one of the custom subplans. In one of my previous R&D
projects, I remember that the CustomScan node received tuples from the
network, gathering them from other pg-instances.
So, as we already have an optional ExplainCustomScan routine, maybe add
one more for the show_plan_tlist routine? It can print a target list or
ignore it as the Append node does.
--
regards,
Andrey Lepikhov
Postgres Professional
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery
2023-09-05 05:31 EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery Lepikhov Andrei <lepikhov@fastmail.com>
2023-09-11 06:28 ` Re: EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery Richard Guo <guofenglinux@gmail.com>
2023-09-28 06:32 ` Re: EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery Andrey Lepikhov <a.lepikhov@postgrespro.ru>
@ 2023-10-09 08:41 ` Andrei Lepikhov <a.lepikhov@postgrespro.ru>
2023-10-10 14:01 ` Re: EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery a.rybakina <a.rybakina@postgrespro.ru>
0 siblings, 1 reply; 7+ messages in thread
From: Andrei Lepikhov @ 2023-10-09 08:41 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; Lepikhov Andrei <lepikhov@fastmail.com>; +Cc: pgsql-bugs@lists.postgresql.org, Алена Рыбакина <a.rybakina@postgrespro.ru>; Дамир Белялов <d.belyalov@postgrespro.ru>
After some attempts to resolve this issue, I must admit that we should
add a non-trivial method in the CustomScan node interface, which would
be called in the ruleutils code.
I guess it is too much for such a rare situation. So maybe just add a
documentation entry on this thing and set a couple of assertions on
early detection of problems?.
In the attachment - is an offer on how to change the code.
--
regards,
Andrey Lepikhov
Postgres Professional
Attachments:
0001-Be-careful-allowing-custom-nodes-to-use-CTE-and-Subp.patch (text/plain, 3.0K) download
From d3b4761a53d8817922b0a6a7aae0fc19c70f28df Mon Sep 17 00:00:00 2001
From: "Andrey V. Lepikhov" <a.lepikhov@postgrespro.ru>
Date: Mon, 9 Oct 2023 15:31:04 +0700
Subject: [PATCH] Be careful allowing custom nodes to use CTE and Subplan links
in the targetlist. Add a paragraph in documentation, comment in the place of
the error triggers and assert to quickly detect problem at the developing
stage.
---
doc/src/sgml/custom-scan.sgml | 4 ++++
src/backend/utils/adt/ruleutils.c | 20 ++++++++++++++++++++
2 files changed, 24 insertions(+)
diff --git a/doc/src/sgml/custom-scan.sgml b/doc/src/sgml/custom-scan.sgml
index a200d502cd..0bc65cbd06 100644
--- a/doc/src/sgml/custom-scan.sgml
+++ b/doc/src/sgml/custom-scan.sgml
@@ -206,6 +206,10 @@ typedef struct CustomScan
the actual scan tuples. <structfield>custom_scan_tlist</structfield> must be
provided for joins, and could be provided for scans if the custom scan
provider can compute some non-Var expressions.
+ Don't include into <structfield>custom_scan_tlist</structfield> references to CTE
+ or SubPlan entries because it directly points to the list of RangeTableEntries.
+ In most cases it doesn't cause any issues but during the EXPLAIN ANALYZE it can fail
+ to find proper subplan during preparing textual representation of targetlist.
<structfield>custom_relids</structfield> is set by the core code to the set of
relations (range table indexes) that this scan node handles; except when
this scan is replacing a join, it will have only one member.
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 442205382e..a60268dcac 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -7860,6 +7860,16 @@ get_name_for_var_field(Var *var, int fieldno,
deparse_namespace save_dpns;
const char *result;
+ /*
+ * Limitation, related to CustomScan nodes.
+ * custom_scan_tlist points to the range table entries
+ * directly. In the case of CTEs and subqueries we have some
+ * sort of uncertainity here: only the developer of the
+ * custom node knows from which subtree they get the subplan
+ * link.
+ */
+ Assert(!IsA(dpns->plan, CustomScan));
+
if (!dpns->inner_plan)
elog(ERROR, "failed to find plan for subquery %s",
rte->eref->aliasname);
@@ -7982,6 +7992,16 @@ get_name_for_var_field(Var *var, int fieldno,
deparse_namespace save_dpns;
const char *result;
+ /*
+ * Limitation, related to CustomScan nodes.
+ * custom_scan_tlist points to the range table entries
+ * directly. In the case of CTEs and subqueries we have some
+ * sort of uncertainity here: only the developer of the
+ * custom node knows from which subtree they get the subplan
+ * link.
+ */
+ Assert(!IsA(dpns->plan, CustomScan));
+
if (!dpns->inner_plan)
elog(ERROR, "failed to find plan for CTE %s",
rte->eref->aliasname);
--
2.42.0
* Re: EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery
2023-09-05 05:31 EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery Lepikhov Andrei <lepikhov@fastmail.com>
2023-09-11 06:28 ` Re: EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery Richard Guo <guofenglinux@gmail.com>
2023-09-28 06:32 ` Re: EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2023-10-09 08:41 ` Re: EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery Andrei Lepikhov <a.lepikhov@postgrespro.ru>
@ 2023-10-10 14:01 ` a.rybakina <a.rybakina@postgrespro.ru>
0 siblings, 0 replies; 7+ messages in thread
From: a.rybakina @ 2023-10-10 14:01 UTC (permalink / raw)
To: Lepikhov Andrei <lepikhov@fastmail.com>; +Cc: pgsql-bugs@lists.postgresql.org, Дамир Белялов <d.belyalov@postgrespro.ru>; Richard Guo <guofenglinux@gmail.com>; Andrei Lepikhov <a.lepikhov@postgrespro.ru>
On 09.10.2023 11:41, Andrei Lepikhov wrote:
> After some attempts to resolve this issue, I must admit that we should
> add a non-trivial method in the CustomScan node interface, which would
> be called in the ruleutils code.
> I guess it is too much for such a rare situation. So maybe just add a
> documentation entry on this thing and set a couple of assertions on
> early detection of problems?.
> In the attachment - is an offer on how to change the code.
>
Hi!
I have studied the problem and I agree with your suggestion to disable
the possibility of explaining subqueries in CustomNode for now.
So far I can only find information about HashJoin and ValueScan, but I
expected that this is not what is needed.
(gdb) p *((Plan *)(cscan->custom_plans->elements[0].ptr_value))->lefttree
$29 = {type = T_ValuesScan, startup_cost = 0, total_cost =
0.025000000000000001, plan_rows = 2, plan_width = 4, parallel_aware =
false, parallel_safe = true, async_capable = false, plan_node_id = 0,
targetlist = 0x560fc9979398, qual = 0x0, lefttree = 0x0, righttree =
0x0, initPlan = 0x0, extParam = 0x0, allParam = 0x0}
(gdb) p *((Plan *)(cscan->custom_plans->elements[0].ptr_value))
$30 = {type = T_HashJoin, startup_cost = 0.050000000000000003,
total_cost = 0.10250000000000001, plan_rows = 2, plan_width = 32,
parallel_aware = false, parallel_safe = false, async_capable = false,
plan_node_id = 0, targetlist = 0x560fc9979208, qual = 0x0, lefttree =
0x560fc9979438, righttree = 0x560fc9979958, initPlan = 0x0, extParam =
0x0, allParam = 0x0}
(gdb) p *((Plan *)(cscan->custom_plans->elements[0].ptr_value))->righttree
$31 = {type = T_Hash, startup_cost = 0.025000000000000001, total_cost =
0.025000000000000001, plan_rows = 2, plan_width = 32, parallel_aware =
false, parallel_safe = false, async_capable = false,
plan_node_id = 0, targetlist = 0x560fc9979518, qual = 0x0, lefttree =
0x560fc9979698, righttree = 0x0, initPlan = 0x0, extParam = 0x0,
allParam = 0x0}
(gdb) p *((Plan *)(cscan->custom_plans->elements[0].ptr_value))->lefttree
$32 = {type = T_ValuesScan, startup_cost = 0, total_cost =
0.025000000000000001, plan_rows = 2, plan_width = 4, parallel_aware =
false, parallel_safe = true, async_capable = false, plan_node_id = 0,
targetlist = 0x560fc9979398, qual = 0x0, lefttree = 0x0, righttree =
0x0, initPlan = 0x0, extParam = 0x0, allParam = 0x0}
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-10-10 14:01 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-05 05:31 EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE and Subquery Lepikhov Andrei <lepikhov@fastmail.com>
2023-09-11 06:28 ` Richard Guo <guofenglinux@gmail.com>
2023-09-11 09:03 ` Lepikhov Andrei <lepikhov@fastmail.com>
2023-09-14 04:58 ` Lepikhov Andrei <lepikhov@fastmail.com>
2023-09-28 06:32 ` Andrey Lepikhov <a.lepikhov@postgrespro.ru>
2023-10-09 08:41 ` Andrei Lepikhov <a.lepikhov@postgrespro.ru>
2023-10-10 14:01 ` a.rybakina <a.rybakina@postgrespro.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox