Обсуждение: Memory grows without bounds in aggregates!

Hello,

I have downloaded the latest snapshot-version from 27th, compiled and
installed it onto a Linux 2.1.131, libc6  I have the following table:

CREATE TABLE "west0" (       "lfnr" int8,       "kdnr" int8,       "artnr" int8,       "eknumsatz" float8,
"ekumsatz"float8,       "vkumsatz" float8,       "lvkumsatz" float8,       "menge" float8,       "anz" int2,
"datum"date);
 


Doing the following is quite fast and memory usage of the postmaster is
ok (abt 3MB).

stamm=> select count(*) from west0;  count
--------
12290703
(1 row)


But doing the following aggregate on the same table will crash the
backend:

stamm=> select sum(ekumsatz), sum(vkumsatz),sum(lvkumsatz),count(*) from
west0;
pqReadData() -- backend closed the channel unexpectedly.       This probably means the backend terminated abnormally
  before or while processing the request.
 
We have lost the connection to the backend, so further processing is
impossible.  Terminating.

Take a look at the output of top after about 2 minutes:
 6:43pm  up 25 days, 10:19,  1 user,  load average: 1.97, 0.71, 0.42
70 processes: 68 sleeping, 2 running, 0 zombie, 0 stopped
CPU states: 25.6% user, 11.1% system,  1.5% nice, 63.4% idle
Mem:  257244K av, 254048K used,   3196K free,   6116K shrd,  13100K buff

Swap: 130748K av, 122264K used,   8484K free                 18812K
cached
 PID USER     PRI  NI  SIZE  RSS SHARE STAT  LIB %CPU %MEM   TIME
COMMAND
12253 postgres  16   0  304M 205M  1148 R       0 33.8 81.7   2:00
postmaster                                      ^^^^^^^^^^


Any idea?

Kind regards

Michael Contzen

Dohle Handelsgruppe Systemberatung GmbH, Germany
E-Mail mcontzen@dohle.com

Michael Contzen <mcontzen@dohle.com> writes:
> [ out of memory for ]
> stamm=> select sum(ekumsatz), sum(vkumsatz),sum(lvkumsatz),count(*) from
> west0;

Right, this is an instance of a known problem (palloc'd temporaries for
aggregate functions aren't freed until end of statement).  I think
someone was looking into a quick-hack patch for aggregates, but there
are comparable problems in evaluation of WHERE expressions, COPY, etc.
We really need a general-purpose solution, and that probably won't
happen till 6.6.

In the meantime, I expect that doing only one float8 sum() per select
would take a third as much memory --- you might find that that's an
adequate workaround for the short run.
        regards, tom lane

> Michael Contzen <mcontzen@dohle.com> writes:
> > [ out of memory for ]
> > stamm=> select sum(ekumsatz), sum(vkumsatz),sum(lvkumsatz),count(*) from
> > west0;
> 
> Right, this is an instance of a known problem (palloc'd temporaries for
> aggregate functions aren't freed until end of statement).  I think
> someone was looking into a quick-hack patch for aggregates, but there
> are comparable problems in evaluation of WHERE expressions, COPY, etc.
> We really need a general-purpose solution, and that probably won't
> happen till 6.6.
> 
> In the meantime, I expect that doing only one float8 sum() per select
> would take a third as much memory --- you might find that that's an
> adequate workaround for the short run.
> 

I thought we fixed this recently with that aggregate patch? 

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Bruce Momjian <maillist@candle.pha.pa.us> writes:
>> Right, this is an instance of a known problem (palloc'd temporaries for
>> aggregate functions aren't freed until end of statement).

> I thought we fixed this recently with that aggregate patch? 

No, we backed out said patch because it was busted (tried to free temp
even for pass-by-value types :-().  Anyone want to try again?
        regards, tom lane