BUG #2225: Backend crash -- BIG table

The following bug has been logged online:

Bug reference: 2225
Logged by: Patrick Rotsaert
Email address: patrick(dot)rotsaert(at)arrowup(dot)be
PostgreSQL version: 8.1.2
Operating system: Linux
Description: Backend crash -- BIG table
Details:

Situation:
---------
Database with 1 table:
CREATE TABLE pptran
(
cchk int4,
trid char(14),
csnr char(13),
amount int4,
date date,
"time" time,
lane int4,
"type" int4,
data char(24),
stat int4,
skip int4,
retry int4,
points_bc int4,
points_chip int4,
trid_tid int2,
trid_seq int2
);

This table contains approx. 36 million records!

CREATE INDEX pptran_trid
ON pptran
USING btree (trid);

Problem:
-------
Executing a select query causes the backend to crash. This is the output
from the psql frontend:

pointspp=# select trid, count(*) from pptran group by trid having count(*)
> 1;
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.
!>

This error appears after several minutes. During execution, 'top' shows
about 4% CPU usage and 98% memory usage of the postmaster process.

At the time of the crash, the server logs:
LOG: server process (PID 21815) was terminated by signal 9
LOG: terminating any other active server processes
FATAL: the database system is in recovery mode
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted at 2006-01-30 15:04:31 CET
LOG: checkpoint record is at 3/275944AC
LOG: redo record is at 3/275944AC; undo record is at 0/0; shutdown TRUE
LOG: next transaction ID: 5415; next OID: 16444
LOG: next MultiXactId: 1; next MultiXactOffset: 0
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: record with zero length at 3/275944F0
LOG: redo is not required
LOG: database system is ready
LOG: transaction ID wrap limit is 2147484146, limited by database
"postgres"

Platform info:
-------------
- PostgreSQL version 8.1.2
- Linux Slackware 9.1.0, Kernel 2.4.22
- /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Pentium(R) 4 CPU 2.20GHz
stepping : 4
cpu MHz : 2192.973
cache size : 512 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca
cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm
bogomips : 4377.80
- /proc/meminfo
total: used: free: shared: buffers: cached:
Mem: 926220288 33148928 893071360 0 1163264 17268736
Swap: 2048376832 18296832 2030080000
MemTotal: 904512 kB
MemFree: 872140 kB
MemShared: 0 kB
Buffers: 1136 kB
Cached: 15288 kB
SwapCached: 1576 kB
Active: 5824 kB
Inactive: 15820 kB
HighTotal: 0 kB
HighFree: 0 kB
LowTotal: 904512 kB
LowFree: 872140 kB
SwapTotal: 2000368 kB
SwapFree: 1982500 kB