I have been able to crash the database consistently on a Fedora Core 5
machine running postgresql 8.2.0.
The attached files are an example database (crash.shema) and the query
that is used (crash.sql) as well as the log output from turning on all the
debugging (crash.log).
I have a couple of other queries that do this as well, but this is the
first one that I noticed. This database schema and query works fine
version 8.1.4.
--
//\\
|| D. Hageman ||
\\//LOG: 0: database system was shut down at 2007-01-02 12:16:33 CST
LOCATION: StartupXLOG, xlog.c:4672
LOG: 0: checkpoint record is at 0/130E42C
LOCATION: StartupXLOG, xlog.c:4762
LOG: 0: redo record is at 0/130E42C; undo record is at 0/0; shutdown TRUE
LOCATION: StartupXLOG, xlog.c:4789
LOG: 0: next transaction ID: 0/59568; next OID: 32768
LOCATION: StartupXLOG, xlog.c:4793
LOG: 0: next MultiXactId: 1; next MultiXactOffset: 0
LOCATION: StartupXLOG, xlog.c:4796
LOG: 0: database system is ready
LOCATION: StartupXLOG, xlog.c:5188
DEBUG: 0: transaction ID wrap limit is 2147484171, limited by database
"postgres"
LOCATION: SetTransactionIdLimit, varsup.c:278
DEBUG: 0: proc_exit(0)
LOCATION: proc_exit, ipc.c:94
DEBUG: 0: shmem_exit(0)
LOCATION: shmem_exit, ipc.c:125
DEBUG: 0: exit(0)
LOCATION: proc_exit, ipc.c:112
DEBUG: 0: reaping dead processes
LOCATION: reaper, postmaster.c:2010
DEBUG: 0: forked new backend, pid=32688 socket=7
LOCATION: BackendStartup, postmaster.c:2573
DEBUG: 0: Ident protocol identifies remote user as "postgres"
LOCATION: authident, hba.c:1615
DEBUG: 0: postmaster child[32688]: starting with (
LOCATION: BackendRun, postmaster.c:2917
DEBUG: 0: postgres
LOCATION: BackendRun, postmaster.c:2920
DEBUG: 0: -v196608
LOCATION: BackendRun, postmaster.c:2920
DEBUG: 0: -y
LOCATION: BackendRun, postmaster.c:2920
DEBUG: 0: test
LOCATION: BackendRun, postmaster.c:2920
DEBUG: 0: )
LOCATION: BackendRun, postmaster.c:2922
DEBUG: 0: InitPostgres
LOCATION: PostgresMain, postgres.c:3137
DEBUG: 0: StartTransaction
LOCATION: ShowTransactionState, xact.c:3985
DEBUG: 0: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 59568/1/0, nestlvl: 1, children: <>
LOCATION: ShowTransactionStateRec, xact.c:4010
DEBUG: 0: CommitTransaction
LOCATION: ShowTransactionState, xact.c:3985
DEBUG: 0: name: unnamed; blockState: STARTED; state: INPROGR,
xid/subid/cid: 59568/1/0, nestlvl: 1, children: <>
LOCATION: ShowTransactionStateRec, xact.c:4010
DEBUG: 0: StartTransactionCommand
LOCATION: start_xact_command, postgres.c:2200
STATEMENT: SELECT f.id, f.category_id, f.status, f.moderated, f.topics,
f.posts, f.forum, f.last_post, f.system_name, f.description, p.last_post_date,
p.last_post_time, p.topic FROM "crash"."forum" f LEFT JOIN "crash"."forum_post"
p ON ( f.last_post = p.id ) WHERE ( f.status = 1 ) ORDER BY f.forum, f.id LIMIT
ALL OFFSET 0;
DEBUG: 0: StartTransaction
LOCATION: ShowTransactionState, xact.c:3985
STATEMENT: SELECT f.id, f.category_id, f.status, f.moderated, f.topics,
f.posts, f.forum, f.last_post, f.system_name, f.description, p.last_post_date,
p.last_post_time, p.topic FROM "crash"."forum" f LEFT JOIN "crash"."forum_post"
p ON ( f.last_post = p.id ) WHERE ( f.status = 1 ) ORDER BY f.forum, f.id LIMIT
ALL OFFSET 0;
DEBUG: 0: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 59569/1/0, nestlvl: 1, children: <>
LOCATION: ShowTransactionStateRec, xact.c:4010
STATEMENT: SELECT f.id, f.category_id, f.status, f.moderated, f.topics,
f.posts, f.forum, f.last_post, f.system_name, f.description, p.last_post_date,
p.last_post_time, p.topic FROM "crash"."forum" f LEFT JOIN "crash"."forum_post"
p ON ( f.last_post = p.id ) WHERE ( f.status = 1 ) ORDER BY f.forum, f.id LIMIT
ALL OFFSET 0;
LOG: 0: statement: SELECT f.id, f.category_id, f.status, f.moderated,
f.topics, f.posts, f.forum, f.last_post, f.system_name, f.description,
p.last_post_date, p.last_post_time, p.topic FROM "crash"."forum" f LEFT JOIN
"crash"."forum_post" p ON ( f.last_post = p.id ) WHERE ( f.status = 1 ) ORDER
BY f.forum, f.id LIMIT ALL OFFSET 0;
LOCATION: exec_simple_query, postgres.c:811
STATEMENT: SELECT f.id, f.category_id, f.status, f.moderated, f.topics,
f.posts, f.forum, f.last_post, f.system_name, f.description, p.last_post_date,
p.last_post_time, p.topic FROM "crash"."forum" f LEFT JOIN "crash"."forum_post"
p ON ( f.last_post = p.id ) WHERE ( f.status = 1 ) ORDER BY f.forum, f.id LIMIT
ALL OFFSET 0;
DEBUG: 0: reaping dead processes
LOCATION: reaper, postmaster.c:2010
DEBUG: 0: server process (PID 32688) was terminated by signal 11
LOCATION: LogChildExit, postmaster.c:2425
LOG: 0: server process (PID
