Postgresql 7.3.3 crashing on query
От | Philipp Reisner |
---|---|
Тема | Postgresql 7.3.3 crashing on query |
Дата | |
Msg-id | 200307281105.13456.philipp.reisner@linbit.com обсуждение исходный текст |
Ответы |
Re: Postgresql 7.3.3 crashing on query
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-bugs |
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 :
В списке pgsql-bugs по дате отправления: