BUG #6698: sub-query with join producing out of memory in where clause - Mailing list pgsql-bugs

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:=20=20=20=20=20=20=20=20

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 *=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
=20=20=20=20=20=20=20
  FROM generate_series(1,1000000) i=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20
=20=20=20=20=20=20=20
 WHERE 100 <=3D (SELECT COUNT(*)=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20
=20=20=20=20=20=20=20
                 FROM unnest(array(select j from
generate_series(i-100,i+100) j)) u1
                      JOIN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20
=20=20=20=20=20=20=20
                      unnest(array(select j from
generate_series(i-100,i+100) j)) u2
                      ON (u1.u1=3Du2.u2));=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
=20=20=20=20=20=20=20
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
...

As you can see I am using enconding sqlascii.

Thanks a lot!
Armando Miraglia