Re: postgres 8.4.9: running out of memory (malloc fails) when running a transaction that runs a LOT of selects

On 20.07.2012 10:19, Benedikt Grundmann wrote:
> We yesterday encountered a program that in a degenerate case issued in
> a single transaction a huge number of selects (in a single transaction
> but each select in a separate call to PGExec) (huge = ~ 400,000).
>
> That transaction would continue to eat memory up until a point where
> calls to malloc (in aset.c) would fail and log for example:
>
> ,"out of memory","Failed on request of size 11."
>
> ...
>
> - Is that expected expected behaviour? The transaction was
> in READ_COMMITED mode, and my best guess is that this implies
> that some snapshot is taken before each subselect and all
> of them are only freed once the transaction is finished

In more complicated scenarios, with e.g subtransactions, it's normal
that there's some growth in memory usage like that. But with simple
SELECTs, I don't think there should be.

Can you write a simple self-contained test script that reproduces this?
That would make it easier to see where the memory is going.

PS, you should upgrade, the latest minor version in 8.4.x series is
8.4.12. If possible, upgrading to a more recent major version would be a
good idea too. I don't know if it will help with this problem, but it
might..

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com