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

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