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

On Wed, 2023-07-19 at 08:49 +0000, PG Bug reporting form wrote:
> PostgreSQL version: 16beta2
>
> My fuzzer finds a potential bug in Postgres, which consumes lots of memory
> and finally kills the Postgres server on my machine.
>
> --- Test case ---
> create table t0 (vkey int4, c0 int4, c5 text);
> create table t4 (c28 text);
>
> insert into t0 values (16, -32, 'hCV');
> insert into t0 values (18, 59, '#;y=+');
> insert into t0 values (33, 16, 'xyOG;');
> insert into t0 values (40, -38, 'I|f');
> insert into t0 values (44, -58, 'F');
> insert into t0 values (47, 88, 'I');
> insert into t0 values (52, -0, 'BGrK');
> insert into t0 values (53, -7, ' T*k');
>
> insert into t4 values ('E');
> insert into t4 values ('l&^');
> insert into t4 values ('');
> insert into t4 values ('Z(at)~');
> insert into t4 values (null::text);
> insert into t4 values ('ngL@');
> insert into t4 values (null::text);
> insert into t4 values ('M+');
>
> 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)
>
> --- Actual behavior ---
> Postgres server consumes lots of memories and finally gets killed
>
> --- Postgres version ---
> Github commit: 8fab4b34801331f1c59352cb0a248be436b60aef
> Version: PostgreSQL 17devel on x86_64-pc-linux-gnu, compiled by clang
> version 10.0.0-4ubuntu1 , 64-bit
>
> --- Note ---
> After I replace "where t0.vkey >= 0" with "where true" (which does not
> change semantics because t0.vkey is always larger than 0), the SELECT query
> is much faster and consumes much less memories.

Strange. When I run your test case on v16beta2 or Git HEAD, I get

ERROR: requested length too large

Commit 6217053f4e added some extra checks in that case, but you should
have that commit. The symptoms you describe would match an attempt to
allocate a lot of memory, with the OOM killer killing PostgreSQL.

Yours,
Laurenz Albe