intagg memory leak - Mailing list pgsql-general

Hi,

I've been using the intagg code to perform aggregations under the
assumption that it's going to be more efficient than the array_accum
documented elsewhere[1].

I'm seeing big memory leaks when doing a query like:

  SELECT d.source_loc_id, d.movement_date - '2006-1-1',
    array_to_string(int_array_aggregate(l.source_ls_id),' ') AS livestockids
  FROM movedates d, livestock_locations l
  WHERE l.source_loc_id = d.source_loc_id
    AND d.movement_date BETWEEN l.start_date AND COALESCE(l.end_date,'2500-1-1')
  GROUP BY d.source_loc_id, d.movement_date
  ORDER BY d.movement_date, d.source_loc_id;

Explain gives the following reasonable plan:

 Sort  (cost=340841771.28..340843520.38 rows=699639 width=12)
   Sort Key: d.movement_date, d.source_loc_id
   ->  HashAggregate  (cost=340761605.76..340773849.45 rows=699639 width=12)
         ->  Merge Join  (cost=19364430.15..327907117.88 rows=1713931718 width=12)
               Merge Cond: (d.source_loc_id = l.source_loc_id)
               Join Filter: ((d.movement_date >= l.start_date) AND (d.movement_date <= COALESCE(l.end_date,
'2500-01-01'::date)))
               ->  Sort  (cost=899684.97..917175.93 rows=6996384 width=8)
                     Sort Key: d.source_loc_id
                     ->  Seq Scan on movedates d  (cost=0.00..104259.84 rows=6996384 width=8)
               ->  Sort  (cost=18464745.18..18733010.76 rows=107306232 width=16)
                     Sort Key: l.source_loc_id
                     ->  Seq Scan on livestock_locations l  (cost=0.00..2134386.32 rows=107306232 width=16)
(12 rows)

But I'm getting an out of memory (i.e. RAM, not disk space) error after
a while.  I've broken the query down into chunks to solve my immediate
problem, but if anyone has any better solutions that would be great.

I'm assuming the arrays that int_array_aggregate() returns aren't ever
getting released.  Memory usage goes to a few gigs (it's a 32bit build)
before bombing out.


Thanks,
  Sam

 [1] http://www.postgresql.org/docs/current/static/xaggr.html