Re: out of memory woes - Mailing list pgsql-general

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;