Postgresql 7.3.3 crashing on query

HI,

Executing a crashes postgresql.

If I execute this query several times (I receive the correct result set),
and then do an explain of the same query, the backend terminates with
sig 11 !!!

Sometimes it crashes by simply executing the query (without explain).

I looks a lot like if the crash has something to do with planing/optimizing.
In case the planer survives the executor can deliver the result set.

I was also able to reproduce this crash on 7.3.2. 7.2.1 does not crash on
this query.

select doc.objid, doc.id, bpa.shortname, dot.shortname, doc.documentdate,
doc.documentid, loc_from.shortname, loc_to.shortname, doc.edittimeutc,
doc.tzo_objid, edi.lastname, doc.cal_id, tzo.tzid
from timezones tzo, documenttypes dot, bpartners bpa, documentheaders as doc
left outer join locations loc_from on loc_from.objid = doc.from_objid
left outer join locations loc_to on loc_to.objid = doc.to_objid
left outer join bppersons edi on edi.objid = doc.edi_objid
where tzo.objid = doc.tzo_objid
and dot.objid = doc.dot_objid
and bpa.objid = doc.bpa_objid
and bpa.objid in (187756)
and doc.documentdate >= '2003-07-25 00:00:00.000000000+00'
and doc.documentdate <= '2003-07-25 23:59:59.009000000+00'
order by 9 DESC

Jul 28 08:34:39 localhost postgres[20301]: [19-1] LOG: query: explain select
doc.objid, doc.id, bpa.shortname, dot.shortname, doc.documentdate,
Jul 28 08:34:39 localhost postgres[20301]: [19-2] doc.documentid,
loc_from.shortname, loc_to.shortname, doc.edittimeutc,
Jul 28 08:34:39 localhost postgres[20301]: [19-3] doc.tzo_objid,
edi.lastname, do
c.cal_id, tzo.tzid
Jul 28 08:34:39 localhost postgres[20301]: [19-4] from timezones tzo,
documenttypes dot, bpartners bpa, documentheaders as doc
Jul 28 08:34:39 localhost postgres[20301]: [19-5] left outer join
locations loc_from on loc_from.objid = doc.from_objid
Jul 28 08:34:39 localhost postgres[20301]: [19-6] left outer join
locations loc_to on loc_to.objid = doc.to_objid
Jul 28 08:34:39 localhost postgres[20301]: [19-7] left outer join
bppersons edi on edi.objid = doc.edi_objid
Jul 28 08:34:39 localhost postgres[20301]: [19-8] where tzo.objid =
doc.tzo_objid
Jul 28 08:34:39 localhost postgres[20301]: [19-9] and dot.objid =
doc.dot_objid
Jul 28 08:34:39 localhost postgres[20301]: [19-10] and bpa.objid =
doc.bpa_objid
Jul 28 08:34:39 localhost postgres[20301]: [19-11] and bpa.objid in
(187756)
Jul 28 08:34:39 localhost postgres[20301]: [19-12] and doc.documentdate
>= '2003-07-25 00:00:00.000000000+00'
Jul 28 08:34:39 localhost postgres[20301]: [19-13] and doc.documentdate
<= '2003-07-25 23:59:59.009000000+00'
Jul 28 08:34:39 localhost postgres[20301]: [19-14] order by 9 DESC;
Jul 28 08:34:39 localhost postgres[11886]: [7] LOG: server process (pid
20301) was terminated by signal 11
Jul 28 08:34:39 localhost postgres[11886]: [8] LOG: terminating any other
active server processes
Jul 28 08:34:39 localhost postgres[20302]: [9] LOG: connection received:
host=[local]
Jul 28 08:34:39 localhost postgres[20302]: [10] FATAL: The database system is
in recovery mode
Jul 28 08:34:39 localhost postgres[19663]: [13-1] WARNING: Message from
PostgreSQL backend:
Jul 28 08:34:39 localhost postgres[19663]: [13-2] ^IThe Postmaster has
informed me that some other backend
Jul 28 08:34:39 localhost postgres[19663]: [13-3] ^Idied abnormally and
possibly corrupted shared memory.
Jul 28 08:34:39 localhost postgres[19663]: [13-4] ^II have rolled back the
current transaction and am
Jul 28 08:34:39 localhost postgres[19663]: [13-5] ^Igoing to terminate your
database system connection and exit.
Jul 28 08:34:39 localhost postgres[19663]: [13-6] ^IPlease reconnect to the
database system and repeat your query.
Jul 28 08:34:39 localhost postgres[19665]: [13-1] WARNING: Message from
PostgreSQL backend:
Jul 28 08:34:39 localhost postgres[19665]: [13-2] ^IThe Postmaster has
informed me that some other backend
Jul 28 08:34:39 localhost postgres[19665]: [13-3] ^Idied abnormally and
possibly corrupted shared memory.
Jul 28 08:34:39 localhost postgres[19665]: [13-4] ^II have rolled back the
current transaction and am
Jul 28 08:34:39 localhost postgres[19665]: [13-5] ^Igoing to terminate your
database system connection and exit.
Jul 28 08:34:39 localhost postgres[19665]: [13-6] ^IPlease reconnect to the
database system and repeat your query.
Jul 28 08:34:39 localhost postgres[19667]: [13-1] WARNING: Message from
PostgreSQL backend:
Jul 28 08:34:39 localhost postgres[19667]: [13-2] ^IThe Postmaster has
informed me that some other backend
Jul 28 08:34:39 localhost postgres[19667]: [13-3] ^Idied abnormally and
possibly corrupted shared memory.
Jul 28 08:34:39 localhost postgres[19667]: [13-4] ^II have rolled back the
current transaction and am
Jul 28 08:34:39 localhost postgres[19667]: [13-5] ^Igoing to terminate your
database system connection and exit.
Jul 28 08:34:39 localhost postgres[19667]: [13-6] ^IPlease reconnect to the
database system and repeat your query.
Jul 28 08:34:39 localhost postgres[19664]: [13-1] WARNING: Message from
PostgreSQL backend:
Jul 28 08:34:39 localhost postgres[19664]: [13-2] ^IThe Postmaster has
informed me that some other backend
Jul 28 08:34:39 localhost postgres[19664]: [13-3] ^Idied abnormally and
possibly corrupted shared memory.
Jul 28 08:34:39 localhost postgres[19664]: [13-4] ^II have rolled back the
current transaction and am
Jul 28 08:34:39 localhost postgres[19664]: [13-5] ^Igoing to terminate your
database system connection and exit.
Jul 28 08:34:39 localhost postgres[19664]: [13-6] ^IPlease reconnect to the
database system and repeat your query.
Jul 28 08:34:39 localhost postgres[11886]: [9] LOG: all server processes
terminated; reinitializing shared memory and semaphores
Jul 28 08:34:39 localhost postgres[20303]: [10] LOG: database system was
interrupted at 2003-07-28 05:06:45 UTC
Jul 28 08:34:39 localhost postgres[20303]: [11] LOG: checkpoint record is at
0/FBA26428
Jul 28 08:34:39 localhost postgres[20303]: [12] LOG: redo record is at
0/FBA26428; undo record is at 0/0; shutdown FALSE
Jul 28 08:34:39 localhost postgres[20303]: [13] LOG: next transaction id:
55952; next oid: 6520640
Jul 28 08:34:39 localhost postgres[20303]: [14] LOG: database system was not
properly shut down; automatic recovery in progress
Jul 28 08:34:39 localhost postgres[20303]: [15] LOG: ReadRecord: record with
zero length at 0/FBA26468
Jul 28 08:34:39 localhost postgres[20303]: [16] LOG: redo is not required
Jul 28 08:34:41 localhost postgres[20303]: [17] LOG: database system is ready

-------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=272.62..272.62 rows=1 width=156)
Sort Key: doc.edittimeutc
-> Hash Join (cost=271.39..272.61 rows=1 width=156)
Hash Cond: ("outer".bpa_objid = "inner".objid)
-> Nested Loop (cost=266.52..267.73 rows=1 width=143)
Join Filter: ("inner".objid = "outer".dot_objid)
-> Merge Join (cost=266.52..266.57 rows=1 width=128)
Merge Cond: ("outer".objid = "inner".tzo_objid)
-> Index Scan using timezones_pkey on timezones tzo
(cost=0.00..9.72 rows=327 width=21)
-> Sort (cost=266.52..266.53 rows=1 width=107)
Sort Key: doc.tzo_objid
-> Nested Loop (cost=0.00..266.51 rows=1
width=107)
-> Nested Loop (cost=0.00..260.51 rows=1
width=90)
-> Nested Loop (cost=0.00..254.71
rows=1 width=76)
-> Seq Scan on documentheaders
doc (cost=0.00..248.91 rows=1 width=62)
Filter: ((documentdate >=
'2003-07-25'::date) AND (documentdate <= '2003-07-25'::date))
-> Index Scan using
locations_pkey on locations loc_from (cost=0.00..5.78 rows=1 width=14)
Index Cond: (loc_from.objid
= "outer".from_objid)
-> Index Scan using locations_pkey on
locations loc_to (cost=0.00..5.78 rows=1 width=14)
Index Cond: (loc_to.objid =
"outer".to_objid)
-> Index Scan using bppersons_pkey on
bppersons edi (cost=0.00..5.99 rows=1 width=17)
Index Cond: (edi.objid =
"outer".edi_objid)
-> Seq Scan on documenttypes dot (cost=0.00..1.07 rows=7
width=15)
-> Hash (cost=4.87..4.87 rows=1 width=13)
-> Index Scan using bpartners_pkey on bpartners bpa
(cost=0.00..4.87 rows=1 width=13)
Index Cond: (objid = 187756)
(26 rows)

-- Name: timezones; Type: TABLE; Schema: public; Owner: sd
--

CREATE TABLE timezones (
objid integer NOT NULL,
id integer NOT NULL,
shortname character varying(12) NOT NULL,
name character varying(100) NOT NULL,
tzid character varying(50) NOT NULL,
utcrawoffset integer NOT NULL
);

-- Name: documenttypes; Type: TABLE; Schema: public; Owner: sd
--

CREATE TABLE documenttypes (
objid integer NOT NULL,
id integer NOT NULL,
shortname character varying(12) NOT NULL,
name character varying(50),
description character varying(4000) NOT NULL,
hasfromlocation character varying(1) NOT NULL,
hastolocation character varying(1) NOT NULL,
hasquantity character varying(1) NOT NULL,
hasreservedquantity character varying(1) NOT NULL,
cancreate character varying(1) NOT NULL,
mustcalcpurchaseprice character varying(1) NOT NULL,
allownegativequantity character varying(1) NOT NULL
);

-- Name: bpartners_tmp; Type: TABLE; Schema: public; Owner: sd
--

CREATE TABLE bpartners_tmp (
objid integer,
id integer,
shortname character varying(12),
name character varying(50),
street character varying(50),
city character varying(50),
zip character varying(50),
tel character varying(50),
fax character varying(50),
email character varying(50),
"location" character varying(50),
memo character varying(2000),
sdcallflag character varying(1),
sdcallpingflag character varying(1),
sdcallmobileflag character varying(1),
sdinventoryflag character varying(1),
sdreportflag character varying(1),
sdbenchmarkflag character varying(1),
cou_objid integer,
tzo_objid integer,
logofilename character varying(50),
additionalmenulinks character varying(2000),
showonlyallowedmenuentries character varying(1),
duns character varying(50),
accountcode character varying(50),
bpa_objid integer
);

-- Name: documentheaders_tmp; Type: TABLE; Schema: public; Owner: sd
--

CREATE TABLE documentheaders_tmp (
objid integer,
id integer,
dot_objid integer,
documentdate date,
edittime timestamp with time zone,
edittimeutc timestamp with time zone,
tzo_objid integer,
edi_objid integer,
documentid character varying(50),
cal_id integer,
from_objid integer,
to_objid integer
);

-- Name: locations; Type: TABLE; Schema: public; Owner: sd
--

CREATE TABLE locations (
objid integer NOT NULL,
id integer NOT NULL,
shortname character varying(12) NOT NULL,
name character varying(50),
zip character varying(50),
city character varying(50),
street character varying(50),
cou_objid integer,
tel character varying(50),
fax character varying(50),
modemtelnr character varying(50),
description character varying(2000),
sco_objid integer,
contactfirstname character varying(50),
contactlastname character varying(50),
contactsalutation character varying(50),
contacttitle character varying(50),
moopenfromam time without time zone,
moopentoam time without time zone,
moopenfrompm time without time zone,
moopentopm time without time zone,
tuopenfromam time without time zone,
tuopentoam time without time zone,
tuopenfrompm time without time zone,
tuopentopm time without time zone,
weopenfromam time without time zone,
weopentoam time without time zone,
weopenfrompm time without time zone,
weopentopm time without time zone,
thopenfromam time without time zone,
thopentoam time without time zone,
thopenfrompm time without time zone,
thopentopm time without time zone,
fropenfromam time without time zone,
fropentoam time without time zone,
fropenfrompm time without time zone,
fropentopm time without time zone,
saopenfromam time without time zone,
saopentoam time without time zone,
saopenfrompm time without time zone,
saopentopm time without time zone,
suopenfromam time without time zone,
suopentoam time without time zone,
suopenfrompm time without time zone,
suopentopm time without time zone,
edittime timestamp with time zone NOT NULL,
edittimeutc timestamp with time zone NOT NULL,
tzo_objid integer NOT NULL,
edi_objid integer NOT NULL,
isactiv character varying(1) NOT NULL,
locationtype character varying(50)
);

-- Name: bppersons_tmp; Type: TABLE; Schema: public; Owner: sd
--

CREATE TABLE bppersons_tmp (
objid integer,
id integer,
shortname character varying(12),
firstname character varying(50),
lastname character varying(50),
title character varying(20),
salutation character varying(50),
street character varying(50),
city character varying(50),
zip character varying(50),
cou_objid integer,
tel character varying(50),
tel2 character varying(50),
mobiletel character varying(50),
fax character varying(50),
"password" character varying(50),
email character varying(50),
pgr_objid integer,
isactiv character varying(1),
"location" character varying(50),
loc_objid integer,
tzo_objid integer,
pin character varying(50),
description character varying(4000),
department character varying(50),
sign character varying(50)
);

-Philipp
--
: Dipl-Ing Philipp Reisner Tel +43-1-8178292-50 :
: LINBIT Information Technologies GmbH Fax +43-1-8178292-82 :
: Schönbrunnerstr 244, 1120 Vienna, Austria http://www.linbit.com :