|
I had one simple query that kept crashing the connection. It
crashes after several minutes. Tried restarting, it still error’d at the same place. Tried recreating the table it was selecting from, it still
error’d at the same place. I rewrote the query with an ARRAY subselect and it finished
flawlessly in a few seconds. There is about 4 million records in the table its selecting
from. No array ends up with more than 4 elements. For some reason, the log indicates this is causing an issue
with autovacuum…it says it was -9’d, but it wasn’t by or any
other physical person. Here is the problem query… create table public.temptrips as select trip_id,array_agg(customer_upload_id) from trip_ids_to_customer_upload_ids group by trip_id; 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:
Failed. !> LOG: 00000: autovacuum launcher process (PID 10264)
was terminated by signal 9: Killed 2009-07-21 08:44:26 EDT - LOCATION:
LogChildExit, postmaster.c:2673 2009-07-21 08:44:26 EDT - LOG:
00000: terminating any other active server processes 2009-07-21 08:44:26 EDT - LOCATION:
HandleChildCrash, postmaster.c:2500 2009-07-21 08:44:26 EDT - WARNING:
57P02: terminating connection because of crash of another server process 2009-07-21 08:44:26 EDT - 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. 2009-07-21 08:44:26 EDT - HINT: In a
moment you should be able to reconnect to the database and repeat your command. 2009-07-21 08:44:26 EDT - LOCATION:
quickdie, postgres.c:2495 2009-07-21 08:44:27 EDT - postgres postgres
/usr/local/pgsql/bin/postmaster FATAL: 57P03: the database system is in
recovery mode 2009-07-21 08:44:27 EDT - postgres postgres
/usr/local/pgsql/bin/postmaster LOCATION: ProcessStartupPacket,
postmaster.c:1721 2009-07-21 08:44:26 EDT - postgres postgres startup
WARNING: 57P02: terminating connection because of crash of another server
process 2009-07-21 08:44:27 EDT - postgres postgres startup
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. 2009-07-21 08:44:27 EDT - postgres postgres startup
HINT: In a moment you should be able to reconnect to the database and
repeat your command. 2009-07-21 08:44:27 EDT - postgres postgres startup
LOCATION: quickdie, postgres.c:2495 2009-07-21 08:44:28 EDT - LOG:
00000: all server processes terminated; reinitializing 2009-07-21 08:44:28 EDT - LOCATION:
PostmasterStateMachine, postmaster.c:2858 2009-07-21 08:44:28 EDT - LOG:
00000: database system was interrupted; last known up at 2009-07-21 08:41:32
EDT 2009-07-21 08:44:28 EDT - LOCATION:
StartupXLOG, xlog.c:5236 2009-07-21 08:44:28 EDT - LOG:
00000: database system was not properly shut down; automatic recovery in
progress 2009-07-21 08:44:28 EDT - LOCATION:
StartupXLOG, xlog.c:5410 2009-07-21 08:44:28 EDT - LOG:
00000: redo starts at 76/4380AC70 2009-07-21 08:44:28 EDT - LOCATION:
StartupXLOG, xlog.c:5493 2009-07-21 08:44:29 EDT - LOG:
00000: record with zero length at 76/438869D0 2009-07-21 08:44:29 EDT - LOCATION:
ReadRecord, xlog.c:3532 2009-07-21 08:44:29 EDT - LOG:
00000: redo done at 76/438869A0 2009-07-21 08:44:29 EDT - LOCATION:
StartupXLOG, xlog.c:5625 2009-07-21 08:44:29 EDT - LOG: 00000:
last completed transaction was at log time 2009-07-21 08:41:49.707423-04 2009-07-21 08:44:29 EDT - LOCATION:
StartupXLOG, xlog.c:5629 2009-07-21 08:44:30 EDT - LOG:
00000: autovacuum launcher started 2009-07-21 08:44:30 EDT - LOCATION:
AutoVacLauncherMain, autovacuum.c:529 2009-07-21 08:44:30 EDT - LOG:
00000: database system is ready to accept connections 2009-07-21 08:44:30 EDT - LOCATION:
reaper, postmaster.c:2272 It looks like this is causing the autovacuum to crash, what
could cause this? postgres=# select version();
version ---------------------------------------------------------------------------------------------------------------------- PostgreSQL 8.4.0 on x86_64-unknown-linux-gnu, compiled
by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-8.0.1), 64-bit (1 row) Chris |
