While developing a Python program I encountered a situation that makes
on of the PostgreSQL 8.3b4's processes crash badly with a segfault.
Let me explain.
After enabling "debug5" as log level I was able to locate and
reproduce the exact sequence of SQL queries made against my test
database
and crash postgres again using only "psql". See for yourself:
foodb=# \o out.txt
foodb=# SELECT * FROM foobaz.instrument WHERE code = 'TEST0118';
LOG: duration: 3.683 ms statement: SELECT * FROM foobaz.instrument
WHERE code = 'TEST0118';
foodb=# SELECT xpath('//sp:description/text()', content,
ARRAY[ARRAY['sp', 'http://www.foobaz.com/']])
foodb-# FROM foobaz.instrument WHERE code = 'TEST0018';
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.
The pgsql log file contains these error logs (the pid 753 is a
postgres child process):
"""""""""
[loluyede:foodb:2007-12-11 14:38:20.992 CET]LOG: 00000: duration:
0.090 ms statement: BEGIN; SET TRANSACTION ISOLATION LEVEL READ
COMMITTED
[loluyede:foodb:2007-12-11 14:38:20.992 CET]LOCATION:
exec_simple_query, postgres.c:1040
[::2007-12-11 14:38:20.993 CET]LOG: 00000: server process (PID 753)
was terminated by signal 11: Segmentation fault
[::2007-12-11 14:38:20.993 CET]LOCATION: LogChildExit, postmaster.c:2510
[::2007-12-11 14:38:20.993 CET]LOG: 00000: terminating any other
active server processes
[::2007-12-11 14:38:20.993 CET]LOCATION: HandleChildCrash, postmaster.c:2355
[::2007-12-11 14:38:20.997 CET]LOG: 00000: all server processes
terminated; reinitializing
[::2007-12-11 14:38:20.997 CET]LOCATION: PostmasterStateMachine,
postmaster.c:2663
[::2007-12-11 14:38:21.000 CET]LOG: 00000: database system was
interrupted; last known up at 2007-12-11 14:38:02 CET
[::2007-12-11 14:38:21.000 CET]LOCATION: StartupXLOG, xlog.c:4789
[::2007-12-11 14:38:21.000 CET]DEBUG: 00000: checkpoint record is at 0/2AB634A0
""""""""
The out.txt file contains the output of the "SELECT *" statement
code | kind | last_modified |
created | modified_by | created_by |
content
----------+------------+-------------------------------+------------------------------+-------------+------------+----------------------------------------------------------------------
TEST0118 | bazfoo | 2007-12-12 08:17:01.846919+01 | 2007-12-12
08:17:00.32337+01 | anonymous | anonymous | <xml ... >
(1 row)
The "instrument" table has the following schema:
"""
CREATE TABLE foobaz.instrument
(
-- Inherited: code character varying(32) NOT NULL,
-- Inherited: kind character varying(32) NOT NULL DEFAULT
'bazfoo'::character varying,
last_modified timestamp with time zone NOT NULL,
created timestamp with time zone NOT NULL DEFAULT
('now'::text)::timestamp(3) with time zone,
modified_by character varying(64),
created_by character varying(64) NOT NULL DEFAULT
'anonymous'::character varying,
content xml NOT NULL,
CONSTRAINT instrument_pkey PRIMARY KEY (code),
CONSTRAINT instrument_kind_check CHECK (kind::text = 'bazfoo'::text)
)
INHERITS (foobaz.instrument_base)
WITH (OIDS=FALSE);
"""
Its "parent" table is instrument_base and has the following schema:
"""
CREATE TABLE foobaz.instrument_base
(
code character varying(32) NOT NULL,
kind character varying(32) NOT NULL,
CONSTRAINT instrument_base_pkey PRIMARY KEY (code)
)
WITH (OIDS=FALSE);
"""
PostgreSQL is again 8.3beta 4 on Ubuntu Linux 7.10.
It's compiled with the following flags:
./configure --with-python --with-openssl --with-pam --with-libxml
--with-libxslt --enable-thread-safety --enable-debug
libxml is 2.6.30, libxslt is 1.1.21
I think that is all.
Is it a bug or am I doing something wrong?
--
Lawrence, oluyede.org - neropercaso.it
"It is difficult to get a man to understand
something when his salary depends on not
understanding it" - Upton Sinclair
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
