Thread

1. memory leak while using cursors

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2001-02-11T09:16:20Z

   Craig Schlenter (craig@webtelecoms.co.za) reports a bug with a severity of 2
   The lower the number the more severe it is.
   
   Short Description
   memory leak while using cursors
   
   Long Description
   Hi
   
   I'm trying to get a large result set back from a postgres 7.02
   database and am bumping my head into the limitations of the
   'I read the whole result set' paradigm of libpq and as a result,
   I've started using cursors. Unfortunately however, the size
   of the "postmaster" process keeps growing as I read back results
   using fetch. This is on a redhat linux 6.x machine using the
   7.02 RPMS. I'm actually doing this from perl but I see the same
   effect when trying it from psql.
   
   "postmaster" starts out being a pleasant 2616K. After declaring a cursor and fetching 20000 rows from the database, it has ballooned to
   4572K and after another fetch it's using 6444K. This process
   continues and after reading a million odd rows I need from the
   database, postmaster is a bloated to almost 100 Megs.
   
   Oh, I said "postmaster' is the process that top shows. ps shows
   it as /usr/bin/postgres localhost testuser test idle. I'll continue
   to call it "postmaster" ...
   
   The commands I'm using are:
   BEGIN WORK;
   DECLARE BOB CURSOR FOR select date_part('epoch', call_time), date_part('hour', call_time), date_part('minute', call_time), date_part('dow', call_time), * from call_jan where call_time >= '2000/12/16 00:00:00.000' and call_time < '2001/01/21 00:00:00.000'  FOR READ ONLY;
   FETCH 20000 FROM BOB;
   (and postmaster grows)
   FETCH 20000 FROM BOB;
   (and postmaster grows)
   ...
   
   psql's memory behaviour is far more reasonable. It balloons to
   a couple of megs when getting the result set back but once I've
   viewed the result set, it sinks back down to a reasonable
   memory consumption as expected.
   
   Interestingly, when I close the cursor, postmaster drops back
   down to a minimal usage again. I could understand it buffering
   the results of the fetch somehow but it continues to do this
   for each fetch it seems and the memory consumption becomes
   horrific.
   
   You should probably be able to reproduce this on any sufficiently
   large table. What bug me about this is that it looks like I'm
   not going to be able to use postgres for this application and
   will have to resort to sybase or trying out mysql to see if
   the memory behaviour is better. Argggh!!! HELP PLEASE!
   
   Thank you.
   
   --Craig
   craig@webtelecoms.co.za
   
   Sample Code
   
   
   No file was uploaded with this report
   
   
   

2. Re: memory leak while using cursors

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-11T17:59:16Z

   pgsql-bugs@postgresql.org writes:
   > memory leak while using cursors
   
   > DECLARE BOB CURSOR FOR select date_part('epoch', call_time), date_part('hour', call_time), date_part('minute', call_time), date_part('dow', call_time), * from call_jan where call_time >= '2000/12/16 00:00:00.000' and call_time < '2001/01/21 00:00:00.000'  FOR READ ONLY;
   
   The date_part function calls will each leak a few bytes of memory that
   doesn't get reclaimed till end of query (= cursor close, in this case).
   This is fixed for 7.1 but there's no fix for older versions.
   
   			regards, tom lane
   
   

3. Re: memory leak while using cursors

   Craig Schlenter <craig@webtelecoms.co.za> — 2001-02-11T18:30:04Z

   On Sun, Feb 11, 2001 at 12:59:16PM -0500, Tom Lane wrote:
   > pgsql-bugs@postgresql.org writes:
   > > memory leak while using cursors
   > 
   > > DECLARE BOB CURSOR FOR select date_part('epoch', call_time), date_part('hour', call_time), date_part('minute', call_time), date_part('dow', call_time), * from call_jan where call_time >= '2000/12/16 00:00:00.000' and call_time < '2001/01/21 00:00:00.000'  FOR READ ONLY;
   > 
   > The date_part function calls will each leak a few bytes of memory that
   > doesn't get reclaimed till end of query (= cursor close, in this case).
   > This is fixed for 7.1 but there's no fix for older versions.
   
   Aha! Thank you!!!! It didn't even occur to me that the query itself might
   have been causing the problems ...
   
   I'll probably just grab the call_time field and do the conversions
   in my perl code to get the date_part fields until 7.1 is released.
   
   Thank you again!!!!!
   
   --Craig