BUG #2225: Backend crash -- BIG table - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      2225
Logged by:          Patrick Rotsaert
Email address:      patrick.rotsaert@arrowup.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