Feedback-ID: i80a949e3:Fastmail
User-Agent: Cyrus-JMAP/3.9.0-alpha0-711-g440737448e-fm-20230828.001-g44073744
Mime-Version: 1.0
Message-Id: <df23647f-d90a-4eb6-9a82-7a4f901cb4aa@app.fastmail.com>
In-Reply-To: 
 <CAMbWs49A1VegrUX08on3WdH7b7t7FUE15JT4qk_vsQ_qfHMGhQ@mail.gmail.com>
References: <dfe47e78-f9c3-4f59-a345-591f7fd3ceec@app.fastmail.com>
 <CAMbWs49A1VegrUX08on3WdH7b7t7FUE15JT4qk_vsQ_qfHMGhQ@mail.gmail.com>
Date: Mon, 11 Sep 2023 16:03:58 +0700
From: "Lepikhov Andrei" <lepikhov@fastmail.com>
To: "Richard Guo" <guofenglinux@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org, tgl@sss.pgh.pa.us,
 =?UTF-8?Q?=D0=90=D0=BB=D0=B5=D0=BD=D0=B0_=D0=A0=D1=8B=D0=B1=D0=B0=D0=BA?=
 =?UTF-8?Q?=D0=B8=D0=BD=D0=B0?= <a.rybakina@postgrespro.ru>,
 =?UTF-8?Q?=D0=94=D0=B0=D0=BC=D0=B8=D1=80_=D0=91=D0=B5=D0=BB=D1=8F=D0=BB?=
 =?UTF-8?Q?=D0=BE=D0=B2?= <d.belyalov@postgrespro.ru>
Subject: Re: EXPLAIN Verbose issue - custom_scan_tlist can directly refer CTE
 and
 Subquery
Content-Type: text/plain;charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/df23647f-d90a-4eb6-9a82-7a4f901cb4aa%40app.fastmail.com>
Precedence: bulk


On Mon, Sep 11, 2023, at 1:28 PM, Richard Guo wrote:
> On Tue, Sep 5, 2023 at 3:23=E2=80=AFPM Lepikhov Andrei <lepikhov@fastm=
ail.com> wrote:
>> Hi,
>>=20
>> 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 probl=
em's origins - setrefs don't change the varno of custom_scan_tlist and c=
an directly reference CTE or Subquery entry. In the "EXPLAIN VERBOSE" ca=
se, 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 bui=
lding CustomScan target list isn't clear for me. Maybe I just have made =
a mistake in my code?
=20
>
> 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 al=
so
> 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->subq=
uery, which was nullified due to optimization. The case with reference t=
o 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 und=
erstand it better.

--=20
Regards,
Andrei Lepikhov