Re: BUG #6698: sub-query with join producing out of memory in where clause

On 19.06.2012 04:01, armando.miraglia@stud-inf.unibz.it wrote:
> The following bug has been logged on the website:
>
> Bug reference:      6698
> Logged by:          Armando Miraglia
> Email address:      armando.miraglia@stud-inf.unibz.it
> PostgreSQL version: 9.1.2
> Operating system:   Arch Linux/Ubuntu
> Description:
>
> Hi everybody!
>
> Fact: while I was trying to produce a c-function I got an OOM which
> RhodiumToad helped me to debug. The OOM is reproducible with also standard
> query.
>
> Environment: I tested the POC using 9.1.2 but also 9.2devel compiled
> "by-hand"
>
> Reproducibility:
> - limit the memory usage
> ulimit -S -v 500000
> - start postgresql
> postgres -D ../data.ascii/
>
> - run the following query from psql
> SELECT *
>
>    FROM generate_series(1,1000000) i
>
>   WHERE 100<= (SELECT COUNT(*)
>
>                   FROM unnest(array(select j from
> generate_series(i-100,i+100) j)) u1
>                        JOIN
>
>                        unnest(array(select j from
> generate_series(i-100,i+100) j)) u2
>                        ON (u1.u1=u2.u2));
>
> Error:
> - psql side:
> ERROR:  out of memory
> DETAIL:  Failed on request of size 828.
> - server side:
> ...
>    PortalMemory: 8192 total in 1 blocks; 7888 free (0 chunks); 304 used
>      PortalHeapMemory: 1024 total in 1 blocks; 824 free (0 chunks); 200 used
>        ExecutorState: 458358928 total in 67 blocks; 794136 free (15965
> chunks); 457564792 used
>          accumArrayResult: 8192 total in 1 blocks; 5744 free (4 chunks); 2448
> used
>          HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
>            HashBatchContext: 32768 total in 2 blocks; 8416 free (1 chunks);
> 24352 used
> ...

This test case can be further reduced into:

explain analyze
SELECT * FROM generate_series(1,100000) i
WHERE (SELECT array(select repeat('a', 10000) || i) u1) is not null;

We're leaking the array constructed on each row, in ExecSetParamPlan().
At line 1000 in nodeSubplan.c, we create a new array and store it as the
value of the PARAM_EXEC parameter. But it's never free'd. The old value
of the parameter is simply overwritten.

I'm not sure what the correct fix is. I suppose we could pfree() the old
value before overwriting it, but I'm not sure if that's safe, or if
there might still be references to the old value somewhere in the executor.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com