Tom Lane wrote:
Klint Gore <kgore4@xxxxxxxxxx> writes:
> Faulting application postgres.exe, version 8.3.3.8160, faulting module
> msvcr80.dll, version 8.0.50727.1433, fault address 0x0001e44a.
> I have a function that's doing a summary report counting data. It makes
> a couple of small temp tables then makes a big temp table. In the query
> that makes the big temp table, there's an immutable function that gets
> called thousands of times in the execution. In the immutable function,
> there was a raise notice for debugging . Without the raise, the query
> finishes in about 40sec. With it, the postgres.exe grinds down to about
> 2% cpu usage and eventually throws the message above. Then I reboot the
> machine - nothing responds to control.
Please try to narrow it down a little. It seems like this could be
caused by sending the messages to the postmaster log, or by sending
them to the client, or by the client not processing them nicely.
(You didn't say what client program you're using.) I'd suggest
adjusting client_min_messages and log_min_messages so that the notice
message goes to only one of the two places, and and then seeing what
happens.
Client is pgAdmin from the 8.3.3 installer.
client=warning, log=warning completes
client=warning, log=notice fails
client=notice, log=warning completes
client=notice, log=notice fails
It fails a heck of a lot quicker with client=warning, log=notice.
This is the raise that causes it
CREATE OR REPLACE FUNCTION stagecode(date, date)
RETURNS text AS
$BODY$
declare
TimeSpan integer = $2 - $1;
begin
raise notice '%',TimeSpan;
return
case when $1 is null or $2 is null then 'X'
when TimeSpan < 10 then 'B'
when TimeSpan < 70 then 'L'
when TimeSpan < 120 then 'W'
when TimeSpan < 330 then 'P'
when Timespan < 450 then 'Y'
when Timespan < 700 then 'H'
else 'A'
end;
end;$BODY$
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@xxxxxxxxxx
