Function execution consuming lot of memory and eventually making server unresponsive

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux