Ok, I have a fairly nasty situation. I am having a production server that is crashing upon execution of a pl/pgsql function...on code that has been working flawlessly for weeks. My production server is running 8.0 on win32 and I was able to 'sort-of' reproduce the behavior on my development machine here at the office.
What happens: On the production machine, upon execution of the function (with a very specific parameter value, all others work ok), all server backends freeze and completely stop working. Any attempt to connect to the server hangs psql in limbo. In addition, the service fails to shut down, and the only way to get working again is to kill postmaster.exe and all instances of postgres.exe. However after that everything runs o.k. until I try to run the function again. There is nothing useful in the log. Following this, I did a dump of the production database and loaded it into my office machine. Here, I try and execute the function and I get: esp=# select generate_oe_bom(18208); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. And server seems to recover from this. Looking at the event log, I see: NOTICE: hello LOG: server process (PID 5720) exited with unexpected status 128 LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. LOG: all server processes terminated; reinitializing LOG: database system was interrupted at 2005-03-03 14:16:15 Eastern Standard Time LOG: checkpoint record is at 6/D7546E28 LOG: redo record is at 6/D7546E28; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 11208897; next OID: 62532404 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 6/D7546E68 LOG: unexpected pageaddr 6/CF5BA000 in log file 6, segment 215, offset 6004736 LOG: redo done at 6/D75B7F90 LOG: database system is ready This will repeat if the function is run again. Only the exact parameter (order# 18208) will cause the crash. Another order, 18150, runs through ok. I would expect data corrumption to be the cause of the problem except I was able to reproduce the problem on a different server following a dump/restore. Unfortunately, this is sensitive data. Attached is the pl/pgsql code. There is a raise notice 'hello'. This gets raised exactly once before the crash. Merlin
oebom.sql
Description: oebom.sql
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org