plpgsql memory leak in 7.3.2? (repost) - Mailing list pgsql-general

Not sure this made it to the list the first time around, apologies to
anyone who received it twice.


Mark.


-----Original Message-----
From: Mark Cave-Ayland [mailto:m.cave-ayland@webbased.co.uk]
Sent: 01 March 2003 21:10
To: 'pgsql-general@postgresql.org'
Subject: plpgsql memory leak in 7.3.2?


Hi everyone,

Whilst continuing work on some of our large tables again, we've
encountered what we think may be a memory leak in plpgsql in 7.3.2. The
process to recreate the problem we are experiencing is given below:


1. First create a table with a million or so rows and create an index on
it

create table restable (
    resid int8
    );

create or replace function t_pop() returns int as '
declare
    i int8;
    sql varchar;
begin
    i := 0;

    while i < 1000000 loop
        sql := ''insert into restable values ('' || i || '')'';
        execute sql;
        i:=i+1;
    end loop;

    return 1;
end;
' language 'plpgsql';

select t_pop();

create index restable_index on restable(resid);


2. Next create a simplified version of our stored procedure

create or replace function t_leak() returns int as '
declare
    count int8;
    row record;
    sql varchar;
begin
    count := 0;

    while count < 1000000 loop

        sql := ''select * from restable where resid='' || count
|| ''::bigint'';
        for row in execute sql loop
            -- Do something interesting here....
            -- Yes, the line below can still be commented
and the problem
            -- still occurs!
            -- execute sql;
        end loop;
        count := count + 1;
    end loop;

    return 1;
end;
' language 'plpgsql';


3. Connect to another session the machine running the db and execute
'top -d1' to view
   memory usage.

4. Start the stored procedure t_leak() and watch the memory usage


What we are finding happens here is that the postmaster process
continues to grow and grab megs of memory (approx 600Mb in this case)
even though it should only be retrieving one record at a time based on
an index. The problem seems to be caused by the 'for row in execute sql
loop' section, since when this is removed, the problem goes away.
However it is necessary to restart the database and then reinsert the
function before the memory usage goes back to normal.

We would be really grateful if someone could verify if this is a bug or
a problem with our build environment and suggest a suitable fix. What we
have done at the moment is to downgrade back to 7.3.1 which doesn't
appear to exhibit the same problem (memory usage remains minimal) so we
can run these stored procedures. On our production system, the memory
grabbed by the postmaster process is about 1.4G which, as you can
imagine, causes everything to run very slowly.....


Many thanks,


Mark.


---

Mark Cave-Ayland
Webbased Ltd.
Tamar Science Park
Derriford
Plymouth
PL6 8BX
England

Tel: +44 (0)1752 764445
Fax: +44 (0)1752 764446


This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender. You
should not copy it or use it for any purpose nor disclose or distribute
its contents to any other person.