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 <dhageman@dracken.com> ||
\\========================================================//
LOG: 00000: database system was shut down at 2007-01-02 12:16:33 CST
LOCATION: StartupXLOG, xlog.c:4672
LOG: 00000: checkpoint record is at 0/130E42C
LOCATION: StartupXLOG, xlog.c:4762
LOG: 00000: redo record is at 0/130E42C; undo record is at 0/0; shutdown TRUE
LOCATION: StartupXLOG, xlog.c:4789
LOG: 00000: next transaction ID: 0/59568; next OID: 32768
LOCATION: StartupXLOG, xlog.c:4793
LOG: 00000: next MultiXactId: 1; next MultiXactOffset: 0
LOCATION: StartupXLOG, xlog.c:4796
LOG: 00000: database system is ready
LOCATION: StartupXLOG, xlog.c:5188
DEBUG: 00000: transaction ID wrap limit is 2147484171, limited by database
"postgres"
LOCATION: SetTransactionIdLimit, varsup.c:278
DEBUG: 00000: proc_exit(0)
LOCATION: proc_exit, ipc.c:94
DEBUG: 00000: shmem_exit(0)
LOCATION: shmem_exit, ipc.c:125
DEBUG: 00000: exit(0)
LOCATION: proc_exit, ipc.c:112
DEBUG: 00000: reaping dead processes
LOCATION: reaper, postmaster.c:2010
DEBUG: 00000: forked new backend, pid=32688 socket=7
LOCATION: BackendStartup, postmaster.c:2573
DEBUG: 00000: Ident protocol identifies remote user as "postgres"
LOCATION: authident, hba.c:1615
DEBUG: 00000: postmaster child[32688]: starting with (
LOCATION: BackendRun, postmaster.c:2917
DEBUG: 00000: postgres
LOCATION: BackendRun, postmaster.c:2920
DEBUG: 00000: -v196608
LOCATION: BackendRun, postmaster.c:2920
DEBUG: 00000: -y
LOCATION: BackendRun, postmaster.c:2920
DEBUG: 00000: test
LOCATION: BackendRun, postmaster.c:2920
DEBUG: 00000: )
LOCATION: BackendRun, postmaster.c:2922
DEBUG: 00000: InitPostgres
LOCATION: PostgresMain, postgres.c:3137
DEBUG: 00000: StartTransaction
LOCATION: ShowTransactionState, xact.c:3985
DEBUG: 00000: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 59568/1/0, nestlvl: 1, children: <>
LOCATION: ShowTransactionStateRec, xact.c:4010
DEBUG: 00000: CommitTransaction
LOCATION: ShowTransactionState, xact.c:3985
DEBUG: 00000: name: unnamed; blockState: STARTED; state: INPROGR,
xid/subid/cid: 59568/1/0, nestlvl: 1, children: <>
LOCATION: ShowTransactionStateRec, xact.c:4010
DEBUG: 00000: 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: 00000: 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: 00000: 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: 00000: 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: 00000: reaping dead processes
LOCATION: reaper, postmaster.c:2010
DEBUG: 00000: server process (PID 32688) was terminated by signal 11
LOCATION: LogChildExit, postmaster.c:2425
LOG: 00000: server process (PID 32688) was terminated by signal 11
LOCATION: LogChildExit, postmaster.c:2425
LOG: 00000: terminating any other active server processes
LOCATION: HandleChildCrash, postmaster.c:2310
DEBUG: 00000: sending SIGQUIT to process 32680
LOCATION: HandleChildCrash, postmaster.c:2361
DEBUG: 00000: sending SIGQUIT to process 32681
LOCATION: HandleChildCrash, postmaster.c:2395
LOG: 00000: all server processes terminated; reinitializing
LOCATION: reaper, postmaster.c:2212
DEBUG: 00000: shmem_exit(0)
LOCATION: shmem_exit, ipc.c:125
DEBUG: 00000: invoking IpcMemoryCreate(size=29237248)
LOCATION: CreateSharedMemoryAndSemaphores, ipci.c:124
DEBUG: 00000: reaping dead processes
LOCATION: reaper, postmaster.c:2010
LOG: 00000: database system was interrupted at 2007-01-02 12:16:41 CST
LOCATION: StartupXLOG, xlog.c:4692
FATAL: 57P03: the database system is starting up
LOCATION: ProcessStartupPacket, postmaster.c:1573
LOG: 00000: checkpoint record is at 0/130E42C
LOCATION: StartupXLOG, xlog.c:4762
LOG: 00000: redo record is at 0/130E42C; undo record is at 0/0; shutdown TRUE
LOCATION: StartupXLOG, xlog.c:4789
LOG: 00000: next transaction ID: 0/59568; next OID: 32768
LOCATION: StartupXLOG, xlog.c:4793
LOG: 00000: next MultiXactId: 1; next MultiXactOffset: 0
LOCATION: StartupXLOG, xlog.c:4796
LOG: 00000: database system was not properly shut down; automatic recovery in
progress
LOCATION: StartupXLOG, xlog.c:4863
DEBUG: 00000: proc_exit(1)
LOCATION: proc_exit, ipc.c:94
DEBUG: 00000: shmem_exit(1)
LOCATION: shmem_exit, ipc.c:125
DEBUG: 00000: exit(1)
LOCATION: proc_exit, ipc.c:112
DEBUG: 00000: forked new backend, pid=32691 socket=7
LOCATION: BackendStartup, postmaster.c:2573
DEBUG: 00000: reaping dead processes
LOCATION: reaper, postmaster.c:2010
DEBUG: 00000: server process (PID 32691) exited with exit code 1
LOCATION: LogChildExit, postmaster.c:2417
LOG: 00000: record with zero length at 0/130E474
LOCATION: ReadRecord, xlog.c:3031
LOG: 00000: redo is not required
LOCATION: StartupXLOG, xlog.c:5002
LOG: 00000: database system is ready
LOCATION: StartupXLOG, xlog.c:5188
DEBUG: 00000: transaction ID wrap limit is 2147484171, limited by database
"postgres"
LOCATION: SetTransactionIdLimit, varsup.c:278
DEBUG: 00000: proc_exit(0)
LOCATION: proc_exit, ipc.c:94
DEBUG: 00000: shmem_exit(0)
LOCATION: shmem_exit, ipc.c:125
DEBUG: 00000: exit(0)
LOCATION: proc_exit, ipc.c:112
DEBUG: 00000: reaping dead processes
LOCATION: reaper, postmaster.c:2010
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;
--
-- PostgreSQL database dump
--
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
--
-- Name: crash; Type: SCHEMA; Schema: -; Owner: postgres
--
CREATE SCHEMA crash;
ALTER SCHEMA crash OWNER TO postgres;
--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--
COMMENT ON SCHEMA public IS 'Standard public schema';
SET search_path = crash, pg_catalog;
--
-- Name: forum_seq; Type: SEQUENCE; Schema: crash; Owner: postgres
--
CREATE SEQUENCE forum_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE crash.forum_seq OWNER TO postgres;
--
-- Name: forum_seq; Type: SEQUENCE SET; Schema: crash; Owner: postgres
--
SELECT pg_catalog.setval('forum_seq', 1, true);
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: forum; Type: TABLE; Schema: crash; Owner: postgres; Tablespace:
--
CREATE TABLE forum (
id integer DEFAULT nextval('forum_seq'::regclass) NOT NULL,
category_id integer NOT NULL,
status integer DEFAULT 1,
moderated integer DEFAULT 0,
topics integer DEFAULT 0,
posts integer DEFAULT 0,
last_post integer DEFAULT 0,
forum character varying,
system_name character varying,
description text
);
ALTER TABLE crash.forum OWNER TO postgres;
--
-- Name: forum_post_seq; Type: SEQUENCE; Schema: crash; Owner: postgres
--
CREATE SEQUENCE forum_post_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE crash.forum_post_seq OWNER TO postgres;
--
-- Name: forum_post_seq; Type: SEQUENCE SET; Schema: crash; Owner: postgres
--
SELECT pg_catalog.setval('forum_post_seq', 1, true);
--
-- Name: forum_post; Type: TABLE; Schema: crash; Owner: postgres; Tablespace:
--
CREATE TABLE forum_post (
id bigint DEFAULT nextval('forum_post_seq'::regclass) NOT NULL,
status integer DEFAULT 1,
forum_id integer NOT NULL,
thread_id integer DEFAULT 0,
sticky integer DEFAULT 0,
user_id integer NOT NULL,
create_date date DEFAULT now(),
create_time time without time zone DEFAULT now(),
last_poster_id integer DEFAULT 0,
last_post_date date DEFAULT now(),
last_post_time time without time zone DEFAULT now(),
posts integer DEFAULT 0,
topic character varying,
post text
);
ALTER TABLE crash.forum_post OWNER TO postgres;
--
-- Data for Name: forum; Type: TABLE DATA; Schema: crash; Owner: postgres
--
--
-- Data for Name: forum_post; Type: TABLE DATA; Schema: crash; Owner: postgres
--
--
-- Name: forum_pkey; Type: CONSTRAINT; Schema: crash; Owner: postgres;
Tablespace:
--
ALTER TABLE ONLY forum
ADD CONSTRAINT forum_pkey PRIMARY KEY (id);
--
-- Name: forum_post_pkey; Type: CONSTRAINT; Schema: crash; Owner: postgres;
Tablespace:
--
ALTER TABLE ONLY forum_post
ADD CONSTRAINT forum_post_pkey PRIMARY KEY (id);
--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;
--
-- Name: forum_seq; Type: ACL; Schema: crash; Owner: postgres
--
REVOKE ALL ON SEQUENCE forum_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE forum_seq FROM postgres;
GRANT ALL ON SEQUENCE forum_seq TO postgres;
GRANT ALL ON SEQUENCE forum_seq TO apache;
--
-- Name: forum; Type: ACL; Schema: crash; Owner: postgres
--
REVOKE ALL ON TABLE forum FROM PUBLIC;
REVOKE ALL ON TABLE forum FROM postgres;
GRANT ALL ON TABLE forum TO postgres;
GRANT ALL ON TABLE forum TO apache;
--
-- Name: forum_post_seq; Type: ACL; Schema: crash; Owner: postgres
--
REVOKE ALL ON SEQUENCE forum_post_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE forum_post_seq FROM postgres;
GRANT ALL ON SEQUENCE forum_post_seq TO postgres;
GRANT ALL ON SEQUENCE forum_post_seq TO apache;
--
-- Name: forum_post; Type: ACL; Schema: crash; Owner: postgres
--
REVOKE ALL ON TABLE forum_post FROM PUBLIC;
REVOKE ALL ON TABLE forum_post FROM postgres;
GRANT ALL ON TABLE forum_post TO postgres;
GRANT ALL ON TABLE forum_post TO apache;
--
-- PostgreSQL database dump complete
--
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
