Bug #883: explain analyze causes postgres to die

Christopher Hodson (nerf@distributed.net) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
explain analyze causes postgres to die

Long Description
running explain analyze on the code below causes postgres to crash.  See below for error messages.

                                version
-----------------------------------------------------------------------
 PostgreSQL 7.3.1 on i386-portbld-freebsd4.6.1, compiled by GCC 2.95.3

from psql (almost immediately):
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

the log has this to say:
2003-01-23 20:57:54 [29981]  LOG:  server process (pid 30037) was terminated by signal 11
2003-01-23 20:57:54 [29981]  LOG:  terminating any other active server processes
The connection to the server was lost. Attempting reset: 2003-01-23 20:57:54 [29981]  LOG:  all server processes
terminated;reinitializing shared memory and semaphores 
2003-01-23 20:57:54 [30051]  LOG:  connection received: host=[local]
2003-01-23 20:57:54 [30051]  FATAL:  The database system is starting up
Failed.
!# 2003-01-23 20:57:54 [30050]  LOG:  database system was interrupted at 2003-01-23 20:40:44 GMT
2003-01-23 20:57:54 [30050]  LOG:  checkpoint record is at 26/201BAC04
2003-01-23 20:57:54 [30050]  LOG:  redo record is at 26/201BAC04; undo record is at 0/0; shutdown TRUE
2003-01-23 20:57:54 [30050]  LOG:  next transaction id: 5645; next oid: 78509014
2003-01-23 20:57:54 [30050]  LOG:  database system was not properly shut down; automatic recovery in progress
2003-01-23 20:57:54 [30050]  LOG:  ReadRecord: record with zero length at 26/201BAC44
2003-01-23 20:57:54 [30050]  LOG:  redo is not required
2003-01-23 20:57:57 [30050]  LOG:  database system is ready

Sample Code
explain analyze INSERT INTO stubs(id, stub_id, nodecount, platform_id, return_count)
SELECT I.id, A.stub_id, L.nodecount, P.platform_id, count(*)
        FROM logdata L, id_lookup I, all_stubs A, platform P
        WHERE lower(L.email) = lower(I.email)
        AND L.stub_marks = A.stub_marks
        AND L.os_type = P.os_type
        AND L.cpu_type = P.cpu_type
        AND L.version = P.version
        GROUP BY I.id, A.stub_id, L.nodecount, P.platform_id

here are the results of 'explain' on the same query

Subquery Scan "*SELECT*"  (cost=9281796.01..9313773.17 rows=213181 width=155)
   ->  Aggregate  (cost=9281796.01..9313773.17 rows=213181 width=155)
         ->  Group  (cost=9281796.01..9308443.64 rows=2131811 width=155)
               ->  Sort  (cost=9281796.01..9287125.53 rows=2131811 width=155)
                     Sort Key: i.id, a.stub_id, l.nodecount, p.platform_id
                     ->  Nested Loop  (cost=2.75..8361073.78 rows=2131811 width=155)
                           ->  Nested Loop  (cost=2.75..1632538.08 rows=1083 width=101)
                                 ->  Hash Join  (cost=2.75..1626418.99 rows=1083 width=78)
                                       Hash Cond: ("outer".os_type = "inner".os_type)
                                       Join Filter: (("outer".cpu_type = "inner".cpu_type) AND ("outer"."version" =
"inner"."version"))
                                       ->  Seq Scan on logdata l  (cost=0.00..458727.96 rows=21230696 width=62)
                                       ->  Hash  (cost=2.40..2.40 rows=140 width=16)
                                             ->  Seq Scan on platform p  (cost=0.00..2.40 rows=140 width=16)
                                 ->  Index Scan using all_marks on all_stubs a  (cost=0.00..5.64 rows=1 width=23)
                                       Index Cond: ("outer".stub_marks = a.stub_marks)
                           ->  Index Scan using idlookup_email_lower on id_lookup i  (cost=0.00..6177.29 rows=1968
width=54)
                                 Index Cond: (lower(("outer".email)::text) = lower((i.email)::text))
(17 rows)


No file was uploaded with this report