The following bug has been logged online:
Bug reference: 3418
Logged by: Viatcheslav
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.2.4
Operating system: RedHat Linux kernel 2.6.9-34.EL
Description: Memory leak with sql EXCEPTION?
Details:
On our system we came across the following issue:
/---------------------------------------------------/
create table dummy(
id integer primary key,
value varchar(10)
);
CREATE OR REPLACE FUNCTION "public"."test" (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;
select test(10000000);
/---------------------------------------------------/
We've been watching CPU and memory usage during the execution of the
'select' with and without exception trap of 'insert'. While in the second
case memory usage kept being relatively low and stayed constant at the level
of approx 2.9Mb across the execution time, in the first case it gradually
raised with step of approx 6Mb up to somewhat 140Mb in the end.
With more complicated PL/PgSQL code (with the same structure: begin -
exception - end block in the main function, some complex function (without
exceptions inside them) doing actual job were called from there) we've run
out of virtual memory (> 1Gb) already with 30000 cycles loop and the process
got killed by operating system. Again, everything worked just fine with
negligible memory consumption without exceptions.
We are aware of additional costs of exceptions yet this one appears to be a
memory leak.
My apologies for poor-styled report in advance for this is my first one.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
