Hello
It hard to say where can be a problem
I see a some risks
a) v8.2.3 isn't last version of 8.2 line
b) why you use a varchar data type for v_loopingdate,
v_cur_start_date, v_cur_end_date - it's bad idea? - you have to do
cast between date and varchar - all operation are slower and needs
more memory - it should be timestamp
Regards
Pavel Stehule
2011/2/24 Gnanakumar <gnanam@xxxxxxxxxx>:
> 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';
>
> Regards,
> Gnanam
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
