Re: Function execution consuming lot of memory and eventually making server unresponsive - Mailing list pgsql-performance

On Thu, Feb 24, 2011 at 3:52 AM, Gnanakumar <gnanam@zoniac.com> wrote:
> Hi,
>
> We're using PostgreSQL v8.2.3 on RHEL5.
>
> I'm developing a PostgreSQL plpgsql function for one of our application
> report.  When I try to run the function multiple times (even twice or
> thrice), I'm seeing considerable amount of memory being taken up by
> PostgreSQL and thereby after sometime, complete server itself comes to
> standstill and not responding at all, even am not able to login to my server
> using PuTTY client.  I then end up physically restarting the server.
>
> Pasted below the function which I'm developing.
>
> Is there something am doing differently in the function that would cause
> PostgreSQL to consume lot of memory?  In my experience, I can say, this is
> the first time I'm seeing PostgreSQL consuming/eating lot of memory and
> causing severe performance issue and eventually making server come to
> standstill.  Also, I can say that another 2 functions which I'm calling from
> within this function ("get_hours_worked" and
> "convert_hours_n_minutes_to_decimal") do not have any performance issues,
> since those 2 functions we're already using in some other reports and have
> not found any performance issues.
>
> Experts suggestions/recommendations on this are highly appreciated.
>
> For example, I would call this function like: SELECT
> hours_worked_day_wise_breakup(90204,23893,38921, '01-01-2010 00:00:00',
> '12-31-2010 23:59:59');
> Output of this function will be like this:
> 8.00-typ1,4.25-typ2,0.00-typ5,6.00-typ3,8.00-typ4
> Logic of this function: Given any 2 dates and filter inputs (input1, input2,
> input3), it would return hours worked for each day (along with a suffix -
> typ[1,2,3,4]) in comma separated form.  In above example, I'm trying to run
> this function for one year.
>
> CREATE or replace FUNCTION hours_worked_day_wise_breakup(numeric, numeric,
> numeric, varchar, varchar) RETURNS VARCHAR AS '
>
> DECLARE
>        p_input1                        ALIAS FOR $1;
>        p_input2                        ALIAS FOR $2;
>        p_input3                        ALIAS FOR $3;
>        p_startdate                     ALIAS FOR $4;
>        p_enddate                       ALIAS FOR $5;
>
>        v_loopingdate                   VARCHAR;
>        v_cur_start_date                VARCHAR;
>        v_cur_end_date                  VARCHAR;
>        v_hours_in_decimal                      NUMERIC := 0.00;
>        v_returnvalue                   VARCHAR := '''';
>
> BEGIN
>        v_loopingdate := TO_CHAR(DATE(p_startdate), ''mm-dd-yyyy'');
>
>        WHILE (DATE(v_loopingdate) <= DATE(p_enddate)) LOOP
>                v_cur_start_date := v_loopingdate || '' 00:00:00'';
>                v_cur_end_date := v_loopingdate || '' 23:59:59'';
>
>                IF (LENGTH(TRIM(v_returnvalue)) >0) THEN
>                        v_returnvalue := v_returnvalue || '','';
>                END IF;
>
>                v_hours_in_decimal :=
> convert_hours_n_minutes_to_decimal(get_hours_worked(p_input1, p_input2, 7,
> 1, -1, p_input3, v_cur_start_date, v_cur_end_date));
>                IF (v_hours_in_decimal > 0) THEN
>                        v_returnvalue := v_returnvalue || v_hours_in_decimal
> || ''-typ1'';
>                        v_loopingdate := TO_CHAR((DATE(v_loopingdate) +
> interval ''1 day''), ''mm-dd-yyyy'');
>                        CONTINUE;
>                END IF;
>
>                v_hours_in_decimal :=
> convert_hours_n_minutes_to_decimal(get_hours_worked(p_input1, p_input2, 6,
> 1, -1, p_input3, v_cur_start_date, v_cur_end_date));
>                IF (v_hours_in_decimal > 0) THEN
>                        v_returnvalue := v_returnvalue || v_hours_in_decimal
> || ''-typ2'';
>                        v_loopingdate := TO_CHAR((DATE(v_loopingdate) +
> interval ''1 day''), ''mm-dd-yyyy'');
>                        CONTINUE;
>                END IF;
>
>                v_hours_in_decimal :=
> convert_hours_n_minutes_to_decimal(get_hours_worked(p_input1, p_input2, 4,
> 1, -1, p_input3, v_cur_start_date, v_cur_end_date));
>                IF (v_hours_in_decimal > 0) THEN
>                        v_returnvalue := v_returnvalue || v_hours_in_decimal
> || ''-typ3'';
>                        v_loopingdate := TO_CHAR((DATE(v_loopingdate) +
> interval ''1 day''), ''mm-dd-yyyy'');
>                        CONTINUE;
>                END IF;
>
>                v_hours_in_decimal :=
> convert_hours_n_minutes_to_decimal(get_hours_worked(p_input1, p_input2, 3,
> 1, -1, p_input3, v_cur_start_date, v_cur_end_date));
>                IF (v_hours_in_decimal > 0) THEN
>                        v_returnvalue := v_returnvalue || v_hours_in_decimal
> || ''-typ4'';
>                        v_loopingdate := TO_CHAR((DATE(v_loopingdate) +
> interval ''1 day''), ''mm-dd-yyyy'');
>                        CONTINUE;
>                END IF;
>
>                v_hours_in_decimal := 0.00;
>                v_returnvalue := v_returnvalue || v_hours_in_decimal ||
> ''-typ5'';
>                v_loopingdate := TO_CHAR((DATE(v_loopingdate) + interval ''1
> day''), ''mm-dd-yyyy'');
>        END LOOP;
>
>        RETURN v_returnvalue;
>
> END ;'
> LANGUAGE 'plpgsql';

It's a pretty safe bet you are stuck in the loop (either infinite, or
very long) using string concatenation operator on the return code. ||
is not designed for extremely heavy use on large strings in a loop.

Your entire function could probably be reduced to one SQL expression
with some thought.

merlin