On Thu, Feb 24, 2011 at 3:52 AM, Gnanakumar <gnanam@xxxxxxxxxx> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
