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
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
