Horton

Re: BUG #18077: PostgreSQL server subprocess crashed by a SELECT statement with WITH clause

Richard Guo <guofenglinux@gmail.com>

View thread View on postgr.es
From: Richard Guo <guofenglinux@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: fuboat@outlook.com, pgsql-bugs@lists.postgresql.org
Date: 2023-09-05T02:37:33Z
Lists: pgsql-bugs

Attachments

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.

Thanks
Richard

Commits

  1. Track nesting depth correctly when drilling down into RECORD Vars.

  2. Fix get_expr_result_type() to find field names for RECORD Consts.

  3. Allow extracting fields from a ROW() expression in more cases.