array_agg crash

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 Spotts

Programmer / Analyst

Transcore

christopher(dot)spotts(at)transcore(dot)com