From: Lepikhov Andrei <a.lepikhov@postgrespro.ru>
To: Richard Guo <guofenglinux@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: fuboat@outlook.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18077: PostgreSQL server subprocess crashed by a SELECT statement with WITH clause
Date: Wed, 06 Sep 2023 10:39:56 +0700
Message-ID: <3f7bcdb7-c263-4c06-a138-140f5c3898ed@app.fastmail.com> (raw)
In-Reply-To: <CAMbWs4_i5+xB1WQ5SVOe8SQscaMswx8hbbgA1YKmtAD-vgcxnA@mail.gmail.com>
References: <18077-b9db97c6e0ab45d8@postgresql.org>
<CAMbWs4-4mDc9j5t+d3MbOSFSTqCDAHQKVV8zE8yiwsaXFGHL9Q@mail.gmail.com>
<CAMbWs4_zxFFSy8+6-FG2ZdJc91TQGM+yG0Yh7gydhjTQfb=9fg@mail.gmail.com>
<694541.1693600870@sss.pgh.pa.us>
<CAMbWs49PRmEWmR0BKG-r8Me0kwr1iLo96diFenhtHYGCvcGgvw@mail.gmail.com>
<1237514.1693879473@sss.pgh.pa.us>
<CAMbWs4_i5+xB1WQ5SVOe8SQscaMswx8hbbgA1YKmtAD-vgcxnA@mail.gmail.com>
Hi,
I am writing here just because you change this specific part of code.
Designing a custom node I found the problem with CTE and Subqueries. The reproduction sample looks quite similar to yours:
create view tt24v as
with cte as materialized (select r from (values(1,2),(3,4)) r)
select (r).column2 as col_a, (rr).column2 as col_b from
cte join (select rr from (values(1,7),(3,8)) rr limit 2) ss
on (r).column1 = (rr).column1;
explain (verbose, costs off) select * from tt24v;
but fails with the error "failed to find plan for CTE ..." with a custom node over a JOIN. Doing a trick like in trick.diff in attachment, I can obtain the next plan:
Result
Output: (cte.r).column2, (ss.rr).column2
CTE cte
-> Values Scan on "*VALUES*_2"
Output: ROW("*VALUES*_2".column1, "*VALUES*_2".column2)
-> Custom Scan (XXX)
Output: cte.r, ss.rr
-> Hash Join
Output: cte.r, (ROW("*VALUES*".column1, "*VALUES*".column2))
Hash Cond: ((cte.r).column1 = ((ROW("*VALUES*".column1, "*VALUES*".column2))).column1)
-> CTE Scan on cte
Output: cte.r
-> Hash
Output: (ROW("*VALUES*".column1, "*VALUES*".column2))
-> Limit
Output: (ROW("*VALUES*".column1, "*VALUES*".column2))
-> Values Scan on "*VALUES*"
Output: ROW("*VALUES*".column1, "*VALUES*".column2)
The result node in attempt to deparse it's targetlist goes into OUTER_VAR - Custom node. After that it goes through the INDEX_VAR ref to custom_scan_tlist, finds reference to the RangeTableEntry CTE, empty dpns->inner_plan and throws the error.
As you can see, the problem here is in wrong assumption: custom_scan_tlist can contain direct references to CTEs and Subqueries as well as WorkTableScan or CteScan.
Maybe to solve this problem too?
--
Regards,
Andrei Lepikhov
On Tue, Sep 5, 2023, at 9:37 AM, Richard Guo wrote:
> On Tue, Sep 5, 2023 at 10:04 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Richard Guo <guofenglinux@gmail.com> writes:
>> > BTW, do you think get_name_for_var_field() has similar problem for
>> > RTE_SUBQUERY case? The RTE_CTE code path in that function crawls up the
>> > namespace stack before recursing into the CTE while the RTE_SUBQUERY
>> > code patch does not, which looks like an oversight.
>>
>> Hmm, seems suspicious ...
>>
>> > I tried to find a
>> > test case to show it's indeed a problem but with no luck.
>>
>> Note that any test case here would be of the form "dump a view
>> or rule definition", not "EXPLAIN". What did you try?
>
> Ah, thanks. I got one of the form "dump a view" leveraging your test
> case from the v2 patch (with a minor tweak).
>
> create view composite_v as
> with cte(c) as materialized (select row(1, 2)),
> cte2(c) as (select * from cte)
> select 1 from cte2 as t
> where (select * from (select c as c1) s
> where (select (c1).f1 > 0)) is not null;
>
> select pg_get_viewdef('composite_v', true);
> ERROR: bogus varno: 1
>
> So it is indeed a problem!
>
> Here is v3 patch which is v2 + fix for this issue.
Attachments:
[application/octet-stream] trick.diff (691B, ../3f7bcdb7-c263-4c06-a138-140f5c3898ed@app.fastmail.com/2-trick.diff)
download | inline diff:diff --git a/contrib/Makefile b/contrib/Makefileindex bbf220407b..7d97b693b0 100644--- a/contrib/Makefile+++ b/contrib/Makefilediff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.cindex 97b0ef22ac..095ad3db24 100644--- a/src/backend/utils/adt/ruleutils.c+++ b/src/backend/utils/adt/ruleutils.c@@ -5004,6 +5004,8 @@ 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))+ dpns->inner_plan = (Plan *) linitial(dpns->subplans);
else if (IsA(plan, ModifyTable))
dpns->inner_plan = plan;
else
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: a.lepikhov@postgrespro.ru, guofenglinux@gmail.com, tgl@sss.pgh.pa.us, fuboat@outlook.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18077: PostgreSQL server subprocess crashed by a SELECT statement with WITH clause
In-Reply-To: <3f7bcdb7-c263-4c06-a138-140f5c3898ed@app.fastmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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