Re: BUG #18030: Large memory consumption caused by in-clause subqueries

On Wed, 19 Jul 2023 at 20:53, PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> select * from t0
> where
> lpad(t0.c5, int4mi(t0.vkey, t0.c0 << t0.c0)) in (
> select
> ref_0.c28 as c_0
> from
> t4 as ref_0
> where t0.vkey >= 0)
> ---
>
> --- Expected behavior ---
> The test case should be executed successfully.
>
> --- Actual behavior ---
> Postgres server consumes lots of memories and finally gets killed

When I looked at this at first, I just thought it was a problem
because of the huge values you're passing to lpad, but on looking
again, I don't quite see why we need to keep the result of the lpad
around for the entire execution of the subquery. I wonder if we're
missing a ResetExprContext(econtext) inside ExecScanSubPlan().

The following example is a bit easier to follow:

explain analyze select * from (values(1),(2)) v(v) where lpad(v::text,
1024*1024) in (select 'test' from generate_series(1,1000) where v.v >
0);

Currently, this will consume around 1GB of memory due to executing the
lpad once for each row of the subquery. But isn't it ok to just reset
the tuple context after fetching each row from the subquery? That
would mean we'd only allocate around 1MB at a time instead of 1GB.

David