Re: out of memory woes

Tom,

Here is the sole plpgsql function that was called when the error
occurred. This function is intended to be called from a shell script in
order to cluster tables in parallel processes. One calls it with
from_perc and to_perc - the % of statements that are run (e.g. 0% to
14%). (This concept may seem a bit silly with only 7 statements, but
this a convention I use for other functions too, such as creating
indexes - there are many indexes.) I call this function from my shell
script such that only one cluster statement is run at a time, for each
of 7 different processes.

Interesting that the leak is actually in the raise. Could this possibly
be related to the exception handling memory leak I read about? When
searching this newsgroup I found a post of yours about this leak, but
decided it probably is not the issue - I believe I read that the memory
leak is local to a transaction.

Thanks,
Mark

create or replace function cluster_load_tables(from_perc integer,
to_perc integer) returns void as
$$
declare
cmdArr text[7];
max_val integer;
enabled boolean;
begin
raise notice 'cluster_load_tables called %', timeofday();

select cluster_load_tables into enabled from
secmaster_stage.data_load_config;

if enabled = false then
raise notice 'cluster_load_tables disabled - exiting out %',
timeofday();
return;
end if;

cmdArr[0] := 'CLUSTER sm_issue';
cmdArr[1] := 'CLUSTER sm_mbs_pool_detail';
cmdArr[2] := 'CLUSTER sm_mbs_quartile_distribution';
cmdArr[3] := 'CLUSTER sm_mbs_loan_distribution';
cmdArr[4] := 'CLUSTER sm_mbs_geo_pool_distribution';
cmdArr[5] := 'CLUSTER sm_issue_id';
cmdArr[6] := 'CLUSTER sm_pool_prefix';

max_val := 6;

for i in ceiling(from_perc*(max_val/100::numeric(20,1))) ..
floor(to_perc*(max_val/100::numeric(20,1))) loop
--for i in 0 .. 6 loop
begin
execute cmdArr[i];
exception
when others then
raise notice 'failed to execute %; error is: %', cmdArr[i],
sqlerrm;
end;
end loop;

/*
[snip - old commented-out code]
*/

raise notice 'cluster_load_tables done %', timeofday();
end;
$$
language plpgsql;