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 10:46 AM, Benedikt Grundmann
<bgrundmann(at)janestreet(dot)com> wrote:
>
> DECLARE sqmlcursor51587 CURSOR FOR select
> entry_time,source,bad_fields,isin,sedol,cusip,bloomberg,reuters,exchange_code,currency,description,bbg_instrument_type,instrument_type,specifics,definer,primary_exchange,is_primary_security,is_primary_listing,tags,bloomberg_id,status
> from vw_instruments_v7 where jane_symbol = E'FOO BAR' and true and
> effective_until = (select max(effective_until) from
> vw_instruments_v7)"
>
> Sorry I imagine that the fact that this generates a cursor every time
> is important
> but it had honestly escaped my attention, because the library we use to query
> the database uses CURSORs basically for every select, so that it can process
> the data in batches (in this particular case that is conceptually unnecessary as
> the query will only return one row, but the library does not know that).
>
Actually I believe this must be it. I just went back and checked the library
and it does not CLOSE the cursors. This is normally not a problem as most
transactions we have run one or two queries only... I'll patch the library
to CLOSE the cursor when all the data has been delivered and test if the
error does not happen then.

I also noticed just know that all TopMemoryContext's after the first one
look significantly different. They contain large PortalMemory sections.
Are those related to cursors?

TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0
chunks); 32 used
Portal hash: 8380416 total in 10 blocks; 3345088 free (34 chunks);
5035328 used
PortalMemory: 16769024 total in 11 blocks; 2737280 free (15 chunks);
14031744 used
PortalHeapMemory: 56320 total in 9 blocks; 4320 free (0 chunks); 52000 used
ExecutorState: 57344 total in 3 blocks; 15248 free (3 chunks); 42096 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
PortalHeapMemory: 56320 total in 9 blocks; 4320 free (0 chunks); 52000 used
ExecutorState: 57344 total in 3 blocks; 15248 free (3 chunks); 42096 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
...

Thanks everyone,

Bene