Обсуждение: memory leak while using cursors

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
hasballooned 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/2100: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

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/2100: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

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/2100: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