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

Re: BUG #3418: Memory leak with sql EXCEPTION?

From: Viatcheslav Kalinin <vka(at)ipcb(dot)net>
To: tgl(at)sss(dot)pgh(dot)pa(dot)us
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #3418: Memory leak with sql EXCEPTION?
Date: 2007-07-01 16:05:45
Message-ID: 4687D0D9.2070408@ipcb.net (view raw or flat)
Thread:
Lists: 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


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2007-07-01 16:30:32
Subject: Re: BUG #3422: Segmentation violation in PL/pgSQL
Previous:From: Jaime CasanovaDate: 2007-07-01 13:34:22
Subject: Fwd: BUG #3421: Failed to create temporary batch file

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