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

On Fri, Jul 20, 2012 at 9:10 AM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> 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.
>
Assuming that it isn't obvious now that I realized that we generate a cursor
every time, I will give it a shot otherwise.

> 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..
>
We are in fact automatically doing an upgrade in testing to 9.1 every day
at the moment. We plan to pull the trigger in production in a few weeks.

Thanks,

Bene