To: Merlin Moncure <mmoncure@gmail.com>
cc: gnanam@zoniac.com, pgsql-performance@postgresql.org
Subject: Re: Function execution consuming lot of memory and eventually making
	server unresponsive
In-reply-to: <AANLkTim+BmS2mwmFaJn7S1D7Yt8cNDuJNxQnMamCLGnV@mail.gmail.com> 
References: <00af01cbd408$93b63590$bb22a0b0$@com>
	<AANLkTim+BmS2mwmFaJn7S1D7Yt8cNDuJNxQnMamCLGnV@mail.gmail.com>
Comments: In-reply-to Merlin Moncure <mmoncure@gmail.com>
	message dated "Thu, 24 Feb 2011 09:14:13 -0600"
Date: Thu, 24 Feb 2011 10:58:53 -0500
Message-ID: <11544.1298563133@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Merlin Moncure <mmoncure@gmail.com> writes:
> Your entire function could probably be reduced to one SQL expression
> with some thought.

Or if not that, at least try to get rid of the use of varchar.  All
those forced varchar-to-date-and-back conversions are expensive.
I'm also more than a tad worried by this:

>        v_loopingdate := TO_CHAR(DATE(p_startdate), ''mm-dd-yyyy'');
>
>        WHILE (DATE(v_loopingdate) <= DATE(p_enddate)) LOOP

There's nothing here guaranteeing that DATE() will think its input
is in mm-dd-yyyy format.  If DateStyle is set to something else,
the logic would at least be wrong, and very possibly that explains
your infinite loop.

Learn to use PG's type system instead of fighting it.  Your code
will be shorter, clearer, faster, and less error-prone.

			regards, tom lane