BUG #7916: memory leak with array_agg - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      7916
Logged by:          Sergey Burladyan
Email address:      eshkinkot@gmail.com
PostgreSQL version: 9.2.3
Operating system:   Debian GNU/Linux 7.0 (wheezy)
Description:        =


Looks like array_agg have memory leak. In my example it eat 3 Gb RSS, but
equal user defined aggregate
created by myself use only 7Mb RSS.

PostgreSQL 9.2.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian
4.7.2-5) 4.7.2, 64-bit
set work_mem to '5MB';

Just query without aggregate:

$ ps axu | grep ^postgres | grep idle
postgres  8910  0.0  0.0 3337028 5156 ?        Ss   02:21   0:00 postgres:
seb seb [local] idle

seb=3D> explain (analyze,verbose,buffers) select n, (random() * 10)::int fr=
om
generate_series(1, 700000) n, generate_series(1, 10);
                                                                 QUERY PLAN =

                                                                =

---------------------------------------------------------------------------=
------------------------------------------------------------------
 Nested Loop  (cost=3D0.01..27510.01 rows=3D1000000 width=3D4) (actual
time=3D82.694..2827.922 rows=3D7000000 loops=3D1)
   Output: n.n, ((random() * 10::double precision))::integer
   Buffers: temp read=3D1198 written=3D1197
   ->  Function Scan on pg_catalog.generate_series n  (cost=3D0.00..10.00
rows=3D1000 width=3D4) (actual time=3D82.653..190.192 rows=3D700000 loops=
=3D1)
         Output: n.n
         Function Call: generate_series(1, 700000)
         Buffers: temp read=3D1198 written=3D1197
   ->  Function Scan on pg_catalog.generate_series  (cost=3D0.00..10.00
rows=3D1000 width=3D0) (actual time=3D0.000..0.001 rows=3D10 loops=3D700000)
         Output: generate_series.generate_series
         Function Call: generate_series(1, 10)
 Total runtime: 3318.348 ms

$ ps axu | grep ^postgres | grep idle
postgres  8910  5.0  0.0 3337176 6376 ?        Ss   02:21   0:03 postgres:
seb seb [local] idle

RSS 6376 after it

Ok, now problematic query with array_agg:

seb=3D> explain (analyze,verbose,buffers) select n, array_agg((random() *
10)::int) from generate_series(1, 700000) n, generate_series(1, 10) group by
1;
                                                                    QUERY
PLAN                                                                     =

---------------------------------------------------------------------------=
------------------------------------------------------------------------
 HashAggregate  (cost=3D32510.01..32512.51 rows=3D200 width=3D4) (actual
time=3D7276.445..7853.732 rows=3D700000 loops=3D1)
   Output: n.n, array_agg(((random() * 10::double precision))::integer)
   Buffers: temp read=3D1198 written=3D1197
   ->  Nested Loop  (cost=3D0.01..20010.01 rows=3D1000000 width=3D4) (actual
time=3D83.624..2318.832 rows=3D7000000 loops=3D1)
         Output: n.n
         Buffers: temp read=3D1198 written=3D1197
         ->  Function Scan on pg_catalog.generate_series n =

(cost=3D0.00..10.00 rows=3D1000 width=3D4) (actual time=3D83.606..217.716
rows=3D700000 loops=3D1)
               Output: n.n
               Function Call: generate_series(1, 700000)
               Buffers: temp read=3D1198 written=3D1197
         ->  Function Scan on pg_catalog.generate_series  (cost=3D0.00..10.=
00
rows=3D1000 width=3D0) (actual time=3D0.000..0.001 rows=3D10 loops=3D700000)
               Output: generate_series.generate_series
               Function Call: generate_series(1, 10)
 Total runtime: 8110.143 ms

$ ps axu | grep ^postgres | grep idle
postgres  8910  6.8 21.0 9160796 3474628 ?     Ss   02:21   0:11 postgres:
seb seb [local] idle

RSS _3474628_ =3D 3Gb allocated after it

reconnect and use user defined aggregate:

create aggregate myagg (int) ( sfunc =3D array_append, stype =3D int[]);
set work_mem to '5MB';

$ ps axu | grep ^postgres | grep idle
postgres  8962  0.0  0.0 3337036 5728 ?        Ss   02:25   0:00 postgres:
seb seb [local] idle

seb=3D> explain (analyze,verbose,buffers) select n, myagg((random() *
10)::int) from generate_series(1, 700000) n, generate_series(1, 10) group by
1;
                                                                    QUERY
PLAN                                                                     =

---------------------------------------------------------------------------=
------------------------------------------------------------------------
 HashAggregate  (cost=3D32510.01..32512.01 rows=3D200 width=3D4) (actual
time=3D8589.773..8917.671 rows=3D700000 loops=3D1)
   Output: n.n, myagg(((random() * 10::double precision))::integer)
   Buffers: temp read=3D1198 written=3D1197
   ->  Nested Loop  (cost=3D0.01..20010.01 rows=3D1000000 width=3D4) (actual
time=3D83.273..2364.212 rows=3D7000000 loops=3D1)
         Output: n.n
         Buffers: temp read=3D1198 written=3D1197
         ->  Function Scan on pg_catalog.generate_series n =

(cost=3D0.00..10.00 rows=3D1000 width=3D4) (actual time=3D83.247..196.978
rows=3D700000 loops=3D1)
               Output: n.n
               Function Call: generate_series(1, 700000)
               Buffers: temp read=3D1198 written=3D1197
         ->  Function Scan on pg_catalog.generate_series  (cost=3D0.00..10.=
00
rows=3D1000 width=3D0) (actual time=3D0.000..0.001 rows=3D10 loops=3D700000)
               Output: generate_series.generate_series
               Function Call: generate_series(1, 10)
 Total runtime: 8978.695 ms

$ ps axu | grep ^postgres | grep idle
postgres  8962 12.9  0.0 3337832 7928 ?        Ss   02:25   0:08 postgres:
seb seb [local] idle

RSS 7928 =3D 7Mb after it

Current git master (542eeba26992305d872be699158cb3ab1c2be6e6) also have this
problem.