Skip site navigation (1) Skip section navigation (2)

BUG #2225: Backend crash -- BIG table

From: "Patrick Rotsaert" <patrick(dot)rotsaert(at)arrowup(dot)be>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2225: Backend crash -- BIG table
Date: 2006-01-30 14:50:06
Message-ID: 20060130145006.01D95F0B00@svr2.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2006-01-30 15:19:48
Subject: Re: BUG #2224: unlogical syntax error
Previous:From: Andrew DunstanDate: 2006-01-30 13:21:34
Subject: Re: [PATCHES] BUG #2221: Bad delimiters allowed in COPY ...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group