Thread: 8.3.3 win32 crashing
Does this mean anything to anyone? 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. After reading of recent win32 network buffer problems, I'm wondering if this is another one. The context statement is nearly 8k long. The server is w2k3 on a 3.8ghz P4 with 3g memory on a 10mbit network (don't ask about the network - it just is and there's nothing I can do about it). The client is xp pentium M notebook, 2ghz, 1g memory. 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@une.edu.au
Klint Gore <kgore4@une.edu.au> 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.
regards, tom lane
Tom Lane wrote:
> Klint Gore <kgore4@une.edu.au> 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@une.edu.au
Klint Gore <kgore4@une.edu.au> writes:
>>> [ heavy RAISE NOTICE traffic crashes the server ]
> Tom Lane wrote:
>> Please try to narrow it down a little.
> 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.
Okay, so the problem is definitely on the postmaster-log side.
Please show us all of your logging-related configuration settings.
ISTR that Magnus was poking at some problem in the WIN32 log collector
code, but whether this is related is not yet clear ...
regards, tom lane
Tom Lane wrote: > Klint Gore <kgore4@une.edu.au> writes: > >>> [ heavy RAISE NOTICE traffic crashes the server ] > > > > 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. > > Okay, so the problem is definitely on the postmaster-log side. > Please show us all of your logging-related configuration settings. > I can duplicate it on my notebook with a slightly smaller set of data (pentium M, 2ghz, 1g mem, 5400rpm pata drive 23gig free). Both server and notebook were installed off the same installer. All settings not commented. The log_min_messages is only there from the test runs. It's normally the default. port = 5432 # (change requires restart) max_connections = 100 # (change requires restart) shared_buffers = 32MB # min 128kB or max_connections*16kB max_fsm_pages = 204800 # min max_fsm_relations*16, 6 bytes each log_destination = 'stderr' # Valid values are combinations of logging_collector = on # Enable capturing of stderr and csvlog log_min_messages = warning log_line_prefix = '%t ' # special values: datestyle = 'iso, mdy' lc_messages = 'C' # locale for system error message lc_monetary = 'C' # locale for monetary formatting lc_numeric = 'C' # locale for number formatting lc_time = 'C' # locale for time formatting default_text_search_config = 'pg_catalog.english' The notebook as visual studio 2005 professional if that helps but it's not setup to build postgres. 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@une.edu.au
Tom Lane wrote: > Klint Gore <kgore4@une.edu.au> writes: > >>> [ heavy RAISE NOTICE traffic crashes the server ] > > > Tom Lane wrote: > >> Please try to narrow it down a little. > > > 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. > > Okay, so the problem is definitely on the postmaster-log side. > Please show us all of your logging-related configuration settings. > > ISTR that Magnus was poking at some problem in the WIN32 log collector > code, but whether this is related is not yet clear ... Maybe it's related to another bug reported earlier. See here for the complete references: http://archives.postgresql.org/message-id/20080625133912.GB17573%40alvh.no-ip.org -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.