array_agg() on a set larger than some arbitrary(?) limit causes runaway memory usage and eventually memory exhaustion

L.S.

Something seems wrong here.... when applying arrag_agg() on a large recordset,
above a certain size 'things fall over' and memory usage races off until the
system runs out of it:

# select version();
version
-----------------------------------------------------------------------------------
PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.7.1,
64-bit

# \! free -g
total used free shared buffers cached
Mem: 31 1 30 0 0 0
-/+ buffers/cache: 0 30
Swap: 31 0 31

====================
Create concatenate function and aggregate:

CREATE OR REPLACE FUNCTION comma_cat(text, text)
RETURNS text
LANGUAGE 'plpgsql'
IMMUTABLE
STRICT
SECURITY INVOKER
AS '
BEGIN
IF $1 = '''' THEN
RETURN $2;
ELSIF $2 = '''' THEN
RETURN $1;
ELSE
RETURN $1 || '', '' || $2;
END IF;
END;';
CREATE AGGREGATE comcat(text) ( SFUNC = comma_cat, STYPE = text );

Activate timing:

\timing
Timing is on.

Create demo data:

create temp table f as
select id, random() as value
from generate_series(1, 1e7::int) as f(id);

Time: 7036,917 ms

====================
Don't mind the 'usefulness' of the exact query below, I ran into this issue
when experimenting a bit using random().

On my system, using the comcat() aggregate is no problem regardless of the
size of the recordset:

with g as (select * from f limit 1e5)
select comcat(id::text), min(value)
from g
group by g.value
having count(1) > 1;
Time: 189,835 ms

with g as (select * from f limit 1e6)
select comcat(id::text), min(value)
from g
group by g.value
having count(1) > 1;
Time: 1815,756 ms

with g as (select * from f)
select comcat(id::text), min(value)
from g
group by g.value
having count(1) > 1;
Time: 18660,326 ms

====================
However, using the array_agg() this breaks (on my system ) on the largest set:

with g as (select * from f limit 1e5)
select array_agg(id), min(value)
from g
group by g.value
having count(1) > 1;
Time: 361,242 ms

with g as (select * from f limit 1e6)
select array_agg(id), min(value)
from g
group by g.value
having count(1) > 1;
Time: 3310,347 ms

with g as (select * from f)
select array_agg(id), min(value)
from g
group by g.value
having count(1) > 1;
Time: <none, fails>

=> the last query eats up all 32GB main memory in seconds, then starts on the
32GB swap (which obviously takes a bit longer to digest) until eventually the
child process gets killed by the oom-killer and postgresql restarts....

--

Best,

Frank.