Re: Newbie questions relating to transactions - Mailing list pgsql-general

On Sunday, March 08, 2009, Alvaro Herrera wrote:
> Carl Sopchak wrote:
> > Well, the upgrade to 8.3 seemed to rid me of the command limit, but now
> > I'm running out of memory.  I have 2Gb physical and 8Gb swap (after
> > adding 4Gb).
>
> Do you have AFTER triggers on the involved tables?  They are recorded on
> memory and we have no mechanism to spill to disk, so it's frequent that
> those cause out-of-memory.  If that's the explanation, your workaround
> would be to get rid of them.
No triggers have been defined on any tables in the database...

>
> > Is there a way for me to run this outside of one huge transaction?  This
> > really shouldn't be using more than a few hundred megs of RAM (assuming
> > cursor records are all stored in memory)...
>
> Hmm, maybe you're holding too many cursors open and not closing them
> timely?  Did you post your function for review?
I am only using one cursor, which is opened and closed repeatedly.  It pulls
the base data from the database for the calculations.

I have not posted the function for review yet because its function is
proprietary.  However, I have stripped out the proprietary stuff, and include
the code below.  I marked everything stripped out by placing a brief
description enclosed within {{ and }}.  I left all of the places that the
database is accessed in the code.  I changed some of the line wrapping to fit
a reasonable width (which I mention in case you see syntax type errors).

There is a few lines that save the calculation details based on a flag in the
trial_header table.  This flag is set to N for the run that I am having
issues with, so these records are not being created.  I left that code in
below for completeness...

I realize this isn't probably the cleanest code out there (I'm sure using
prepared statements would help speed), but it was really meant to be a "quick
and dirty" way to calculate the data I need.  Any comments or suggestions on
improving the code is welcome.

create or replace function Run_Trial (tid integer) returns void as $proc$

declare
  {{ declarations }}
begin
   -- Set start time...
   program_version := '1.16';
   update trial_header set start_timestamp = clock_timestamp(),
      run_version = program_version, end_timestamp = null
      where trial_id = tid;

   -- get rid of prior run, if any:
   delete from trial_results where trial_id = tid;
   delete from trial_calc_detail where trial_id = tid;

   -- Get the trial parameters:
   select * into trial_hdr from trial_header where trial_id = tid;

   {{ Do some calculations  }}

   -- Create temp table of data.  This simplifies the coding below A LOT.
   {{ conditional calc }}

   -- (I can't figure out how to do this with a dynamic select and
   -- "insert into trial_data select" and I get an error
   -- if I make the dynamic SQL a "select into temp table trial_data"...)
   -- Do it by brute force, I guess...
   drop table if exists trial_data;
   create temp table trial_data(
      {{ fields }}
   );
   for row in execute
      'select {{ select statement }}' loop
         execute 'insert into trial_data values(' || {{ fields }} || ')';
   end loop;

   create index trial_data_idx on trial_data (data_yyyymm, data_date);
   create index trial_data_idx2 on trial_data (data_date);

   -- Get date range for the data set we're using
   for row in execute 'select min(data_date) as min_date,
              max(data_date) as max_date from trial_data' loop
      low_data_date := row.min_date;
      high_data_date := row.max_date;
   end loop;

   -- Calculate maximum number of years that data covers
   max_years = floor((high_data_date - low_data_date) / 365.25);

   --  Loop through all possible "x year" periods
   for cur_years in 1 .. max_years loop

      -- start from the first period on file:
      next_iteration_start := low_data_date;

      num_periods := trial_hdr.periods_per_year * cur_years
                                 + trial_hdr.{{ field }};

      for row in execute 'select count(*) as cnt from (
               select data_date from trial_data where data_date >= ' ||
                         quote_literal(next_iteration_start) ||
               ' Limit ' || to_char(num_periods, '9999999999') || ') a' loop
         data_periods := row.cnt;
      end loop;

      -- Do each "x year" period in data
      while data_periods = num_periods loop

         -- Initialize calculation

         -- used to set sucessive values for next_iteration_start:
         iteration_counter := 0;

         {{ some calculations }}

         for row in execute 'select max(data_date) as max_date' ||
                  ' from ( select data_date from trial_data' ||
                  ' where data_date >= ' ||
                   quote_literal(next_iteration_start) ||
                  ' order by data_date' ||
                  ' Limit ' || to_char(num_periods, '999999999') || ') a' loop
            per_end_date := row.max_date;
         end loop;

         -- Get data for calculation
         open data_cursor for execute 'select * from trial_data' ||
            ' where data_date >= ' || quote_literal(next_iteration_start) ||
            ' order by data_date ' ||
            ' Limit ' || to_char(num_periods, '999999999');

         loop  -- through periods for calculation
            fetch data_cursor into data;
            if not found then
               exit;
            end if;

            -- determine next iteration start date:
            iteration_counter := iteration_counter + 1;
            if iteration_counter = 1 then
               {{ calculations }}
            end if;
            if iteration_counter = 2 then
               next_iteration_start := data.data_date;
            end if;

            {{ calculations based on row data }}

            -- save details if requested:
            if upper(trial_hdr.save_calc_details) = 'Y' then
               insert into trial_calc_detail values( {{ fields }} );
            end if;

            {{ Calculation }}
         end loop;  -- through periods for calculation

         -- Final calculations:
         {{ calculations }}

         -- save results:
         insert into trial_results values( {{ fields }} );

         close data_cursor;

         for row in execute 'select count(*) as cnt from (
                  select data_date from trial_data where data_date >= ' ||
                  quote_literal(next_iteration_start) ||
                  ' Limit ' || to_char(num_periods, '9999999999') || ') a'
           loop
            data_periods := row.cnt;
         end loop;

      end loop;  -- Do each "x year" period in data

   end loop;  -- loop through all possible "x year" periods

   -- mark finish time on trial:
   update trial_header set end_timestamp = clock_timestamp()
             where trial_id = tid;

   -- clean up:
   drop table trial_data;
end;

$proc$ language plpgsql;