Skip site navigation (1) Skip section navigation (2)

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

From: Benedikt Grundmann <bgrundmann(at)janestreet(dot)com>
To: Andres Freund <andres(at)2ndquadrant(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: postgres 8.4.9: running out of memory (malloc fails) when running a transaction that runs a LOT of selects
Date: 2012-07-20 09:56:09
Message-ID: CADbMkNO6i9aJQp+1rp0JczwRhuB3QFAYRVz_4_Kdzz83vr4epQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Benedikt GrundmannDate: 2012-07-20 10:34:34
Subject: Re: postgres 8.4.9: running out of memory (malloc fails) when running a transaction that runs a LOT of selects
Previous:From: Benedikt GrundmannDate: 2012-07-20 09:49:12
Subject: Re: postgres 8.4.9: running out of memory (malloc fails) when running a transaction that runs a LOT of selects

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group