From: Tom Lane <tgl@sss.pgh.pa.us>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: gnanam@zoniac.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Function execution consuming lot of memory and eventually making server unresponsive
Date: Thu, 24 Feb 2011 10:58:53 -0500
Message-ID: <11544.1298563133@sss.pgh.pa.us> (raw)
In-Reply-To: <AANLkTim+BmS2mwmFaJn7S1D7Yt8cNDuJNxQnMamCLGnV@mail.gmail.com>
References: <00af01cbd408$93b63590$bb22a0b0$@com>
<AANLkTim+BmS2mwmFaJn7S1D7Yt8cNDuJNxQnMamCLGnV@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, mmoncure@gmail.com, gnanam@zoniac.com
Subject: Re: Function execution consuming lot of memory and eventually making server unresponsive
In-Reply-To: <11544.1298563133@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox