Re: BUG #3418: Memory leak with sql EXCEPTION? - Mailing list pgsql-bugs

Hello, Tom

As of you last mail:

 > Well, if you think there's some other memory leak then you need to
 > submit a test case that demonstrates it.

Not to be going to make any assumptions on what might be malfunctioning
internally, I populated a test case that at least on our system
demonstrates behavior that imo couldn't take place if per-iteration
overhead was only a record in XIDs list.
I've made two cases actually, simple one that I already mentioned about
in my previous mails and more complex one that I tried to make somewhat
close to our work case where I first encountered the problem in question.



Simple:

/---------------------------------------------------/
create table dummy(
    id integer primary key,
    value varchar(10)
);

CREATE OR REPLACE FUNCTION "public"."test_smpl" (fi integer) RETURNS
"pg_catalog"."void" AS
$body$
declare
 vi integer;
 idx integer := 0;
begin

while idx < fi loop
    idx := idx + 1;
    begin
        insert into dummy values (idx, idx::varchar);
    exception
    when others then
       raise exception '% %', idx, 'stop';
    end;
end loop;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;

/---------------------------------------------------/




Complex:

/---------------------------------------------------/

create table ref1 (
    id       integer primary key,
    value    varchar(10)
);

insert into ref1 values (1, 'ref#1');

create table cplx1 (
    id       integer primary key,
    fid      integer references ref1 (id),
    value1   smallint not null,
    value2   varchar(100) unique,
    value3   varchar(100)
);

create table cplx2 (
    fid      integer references cplx1 (id),
    value1   varchar(50),
    value2   varchar(50),
    value3   varchar(50),
    value4   smallint,
    value5   real
);



CREATE OR REPLACE FUNCTION "public"."test_cplx" (fi integer) RETURNS
"pg_catalog"."void" AS
$body$
declare
    idx         integer := 0;
    viid        integer;

    vivalue1    smallint;
    vsvalue2    varchar;
    vsvalue3    varchar;

    vsvalue2_1  varchar;
    vsvalue2_2  varchar;
    vsvalue2_3  varchar;
    vivalue2_4  smallint;
    vxvalue2_5  real;
begin

while idx < fi loop
    idx := idx + 1;

    viid := nextval('autoinc');

    vivalue1 := idx % 32000;
    vsvalue2 := 'val' || trunc(random() * 10000000);
    vsvalue3 := 'aaa' || idx;

    vsvalue2_1  := 'bbb' || idx;
    vsvalue2_2  := 'ccc' || idx;
    vsvalue2_3  := 'ddd' || idx;
    vivalue2_4  := trunc(random() * 2);
    vxvalue2_5  := random();


    begin

    perform internal_insert(viid, vivalue1, vsvalue2, vsvalue3,
                      vsvalue2_1, vsvalue2_2, vsvalue2_3,
                      vivalue2_4, vxvalue2_5);
    exception
    when others then
       raise notice '% %', idx, SQLERRM;
    end;
end loop;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;



CREATE OR REPLACE FUNCTION "public"."internal_insert" (piid integer,
pivalue1 smallint, psvalue2 varchar, psvalue3 varchar, psvalue2_1
varchar, psvalue2_2 varchar, psvalue2_3 varchar, pivalue2_4 smallint,
pxvalue2_5 real) RETURNS "pg_catalog"."void" AS
$body$
begin
    perform 1
       from cplx1
      where value2 = psvalue2;


    if found then
        raise exception 'not unique';
    end if;


    insert into cplx1
    values (piid, 1, pivalue1, psvalue2, psvalue3);

    if psvalue2_1 is not null or
       psvalue2_2 is not null or
       psvalue2_3 is not null or
       pivalue2_4 is not null or
       pxvalue2_5 is not null
    then
        insert into cplx2
        values (piid, psvalue2_1, psvalue2_2, psvalue2_3, pivalue2_4,
pxvalue2_5);
    end if;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;


/---------------------------------------------------/

Couple of notes on the second one: main function fills in 2 linked
indexed tables with certain data of various types, work prototype of the
function "test_cplx" is used as an interface to some applications (such
as CGI scripts) thus does some internal parameters check demonstrated
here as 'perform' block.


Our box is Linux 2.6.9-34 running on P4 1.8GHz.

Starting from new connection:

# ps -eo %cpu,cputime,vsize,size,rss,pid,cmd | grep postgres

 0.0 00:00:00 141524 2620 4912 21861 postgres: postgres <...> idle
------------------------------------------------------------------


I do 'select test_smpl(100000);' and near the end of the transaction get
the following report:

 11.9 00:00:05 143988 4968 13712 21861 postgres: postgres <...> SELECT
----------------------------------------------------------------------


When I do 'select test_cplx(100000)' I get:

 99.6 00:01:01 971552 832508 858012 21812 postgres: postgres <...> SELECT
-------------------------------------------------------------------------


I should also note here that it seems that the allocated memory is not
freed after th transaction ends, so if I start next select memory
consumption continues to grow.


Having commented out 'begin' and 'exception ... when ... end' statements
in test_cplx (and returning immediately from internal_insert if value2
is not unique) I get:

 39.6 00:00:39 142420 3400 27824 21877 postgres: postgres <...> SELECT
----------------------------------------------------------------------


This behavior seems odd to me.


Sincerely, Viatcheslav