Backend crash during explain - Mailing list pgsql-hackers

Hi,

This is on Intel OSX, anon CVS download today.

Build process:-

1. make distclean
2. ./configure --enable-debug --enable-cassert --enable-integer-datetimes 
--prefix=/Users/grant/Development/bin/pgsql --enable-depend
3. make all install

The query with no EXPLAIN (ANALYSE) completes fine.

The query with EXPLAIN ANALYSE completes fine.

> foo=# explain analyse select this_.id as id6_2_, this_1_.created_at as created2_6_2_, this_1_.created_by as
created3_6_2_,this_1_.updated_at as updated4_6_2_, this_1_.updated_by as updated5_6_2_, this_1_.from_date as
from6_6_2_,this_1_.party_id as party8_6_2_, this_1_.thru_date as thru7_6_2_, this_1_.type_id as type9_6_2_, this_.tag
astag14_2_, this_.taggedBy_id as taggedBy4_14_2_, this_.taggedDate as taggedDate14_2_, partyrolet2_.id as id3_0_,
partyrolet2_.created_atas created2_3_0_, partyrolet2_.created_by as created3_3_0_, partyrolet2_.updated_at as
updated4_3_0_,partyrolet2_.updated_by as updated5_3_0_, partyrolet2_.description as descript6_3_0_, partyrolet2_.name
asname3_0_, tagimplant3_.id as id6_1_, tagimplant3_1_.created_at as created2_6_1_, tagimplant3_1_.created_by as
created3_6_1_,tagimplant3_1_.updated_at as updated4_6_1_, tagimplant3_1_.updated_by as updated5_6_1_,
tagimplant3_1_.from_dateas from6_6_1_, tagimplant3_1_.party_id as party8_6_1_, tagimplant
 
3_1_.thru_date as thru7_6_1_, tagimplant3_1_.type_id as type9_6_1_ from tagged_asset this_ inner join party_role
this_1_on this_.id=this_1_.id inner join party_role_type partyrolet2_ on this_1_.type_id=partyrolet2_.id left outer
jointag_implanter tagimplant3_ on this_.taggedBy_id=tagimplant3_.id left outer join party_role tagimplant3_1_ on
tagimplant3_.id=tagimplant3_1_.idwhere (lower(this_.tag) like '1f76%') limit 100;
 
>                                                                                           QUERY PLAN
                                                                       
 
>
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Limit  (cost=8.31..24.50 rows=1 width=3748) (actual time=23.057..209.191 rows=77 loops=1)
>    ->  Nested Loop  (cost=8.31..24.50 rows=1 width=3748) (actual time=23.055..209.142 rows=77 loops=1)
>          ->  Nested Loop Left Join  (cost=8.31..24.22 rows=1 width=2170) (actual time=23.036..208.326 rows=77
loops=1)
>                ->  Nested Loop Left Join  (cost=8.31..18.62 rows=1 width=1098) (actual time=23.033..208.204 rows=77
loops=1)
>                      ->  Merge Join  (cost=8.31..10.34 rows=1 width=1094) (actual time=23.024..208.015 rows=77
loops=1)
>                            Merge Cond: (this_1_.id = this_.id)
>                            ->  Index Scan Backward using party_role_pkey on party_role this_1_  (cost=0.00..18672.18
rows=581325width=1076) (actual time=0.102..142.963 rows=240384 loops=1)
 
>                            ->  Sort  (cost=8.31..8.32 rows=1 width=22) (actual time=0.856..0.902 rows=77 loops=1)
>                                  Sort Key: this_.id
>                                  Sort Method:  quicksort  Memory: 20kB
>                                  ->  Index Scan using tagged_asset_tag_key on tagged_asset this_  (cost=0.01..8.30
rows=1width=22) (actual time=0.109..0.739 rows=77 loops=1)
 
>                                        Index Cond: ((lower((tag)::text) >= '1f76'::text) AND (lower((tag)::text) <
'1f77'::text))
>                                        Filter: (lower((tag)::text) ~~ '1f76%'::text)
>                      ->  Index Scan using tag_implanter_pkey on tag_implanter tagimplant3_  (cost=0.00..8.27 rows=1
width=4)(actual time=0.001..0.001 rows=0 loops=77)
 
>                            Index Cond: (this_.taggedby_id = tagimplant3_.id)
>                ->  Index Scan using party_role_pkey on party_role tagimplant3_1_  (cost=0.00..5.59 rows=1 width=1076)
(actualtime=0.000..0.000 rows=0 loops=77)
 
>                      Index Cond: (tagimplant3_.id = tagimplant3_1_.id)
>          ->  Index Scan using party_role_type_pkey on party_role_type partyrolet2_  (cost=0.00..0.27 rows=1
width=1578)(actual time=0.008..0.009 rows=1 loops=77)
 
>                Index Cond: (partyrolet2_.id = this_1_.type_id)
>  Total runtime: 209.699 ms
> (20 rows)


However, with just EXPLAIN (no ANALYSE)

> foo=# explain select this_.id as id6_2_, this_1_.created_at as created2_6_2_, this_1_.created_by as created3_6_2_,
this_1_.updated_atas updated4_6_2_, this_1_.updated_by as updated5_6_2_, this_1_.from_date as from6_6_2_,
this_1_.party_idas party8_6_2_, this_1_.thru_date as thru7_6_2_, this_1_.type_id as type9_6_2_, this_.tag as tag14_2_,
this_.taggedBy_idas taggedBy4_14_2_, this_.taggedDate as taggedDate14_2_, partyrolet2_.id as id3_0_,
partyrolet2_.created_atas created2_3_0_, partyrolet2_.created_by as created3_3_0_, partyrolet2_.updated_at as
updated4_3_0_,partyrolet2_.updated_by as updated5_3_0_, partyrolet2_.description as descript6_3_0_, partyrolet2_.name
asname3_0_, tagimplant3_.id as id6_1_, tagimplant3_1_.created_at as created2_6_1_, tagimplant3_1_.created_by as
created3_6_1_,tagimplant3_1_.updated_at as updated4_6_1_, tagimplant3_1_.updated_by as updated5_6_1_,
tagimplant3_1_.from_dateas from6_6_1_, tagimplant3_1_.party_id as party8_6_1_, tagimplant3_1_.thr
 
u_date as thru7_6_1_, tagimplant3_1_.type_id as type9_6_1_ from tagged_asset this_ inner join party_role this_1_ on
this_.id=this_1_.idinner join party_role_type partyrolet2_ on this_1_.type_id=partyrolet2_.id left outer join
tag_implantertagimplant3_ on this_.taggedBy_id=tagimplant3_.id left outer join party_role tagimplant3_1_ on
tagimplant3_.id=tagimplant3_1_.idwhere (lower(this_.tag) like '1f76%') limit 100;
 
> 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.


CrashReporter trace:

Date/Time:      2007-05-31 10:21:39.285 +0200
OS Version:     10.4.9 (Build 8P2137)
Report Version: 4

Command: postmaster
Path:    ./bin/postmaster
Parent:  postmaster [23091]

Version: ??? (???)

PID:    23096
Thread: 0

Exception:  EXC_BAD_ACCESS (0x0001)
Codes:      KERN_PROTECTION_FAILURE (0x0002) at 0x00000018

Thread 0 Crashed:
0   postmaster     0x00116ec6 ExecSetSlotDescriptor + 77 (execTuples.c:344)
1   postmaster     0x001182f9 ExecAssignScanTypeFromOuterPlan + 33 (execUtils.c:771)
2   postmaster     0x001240c8 ExecInitSort + 168 (nodeSort.c:211)
3   postmaster     0x0010ee5e ExecInitNode + 678 (execProcnode.c:230)
4   postmaster     0x0012228c ExecInitMergeJoin + 252 (nodeMergejoin.c:1445)
5   postmaster     0x0010eeb8 ExecInitNode + 768 (execProcnode.c:212)
6   postmaster     0x00122b4e ExecInitNestLoop + 198 (nodeNestloop.c:322)
7   postmaster     0x0010eed6 ExecInitNode + 798 (execProcnode.c:207)
8   postmaster     0x00122b4e ExecInitNestLoop + 198 (nodeNestloop.c:322)
9   postmaster     0x0010eed6 ExecInitNode + 798 (execProcnode.c:207)
10  postmaster     0x00122b4e ExecInitNestLoop + 198 (nodeNestloop.c:322)
11  postmaster     0x0010eed6 ExecInitNode + 798 (execProcnode.c:207)
12  postmaster     0x00124d19 ExecInitLimit + 185 (nodeLimit.c:376)
13  postmaster     0x0010edaa ExecInitNode + 498 (execProcnode.c:260)
14  postmaster     0x0010df50 ExecutorStart + 1516 (execMain.c:634)
15  postmaster     0x000cf393 ExplainOnePlan + 146 (explain.c:257)
16  postmaster     0x000cf885 ExplainQuery + 267 (explain.c:170)
17  postmaster     0x001ba9fe PortalRunUtility + 233 (palloc.h:84)
18  postmaster     0x001bafef FillPortalStore + 239 (pquery.c:1050)
19  postmaster     0x001bb672 PortalRun + 994 (pquery.c:792)
20  postmaster     0x001b604d exec_simple_query + 656 (postgres.c:965)
21  postmaster     0x001b805c PostgresMain + 5628 (postgres.c:3507)
22  postmaster     0x00186dfa ServerLoop + 2828 (postmaster.c:2614)
23  postmaster     0x00187f4b PostmasterMain + 2704 (postmaster.c:972)
24  postmaster     0x00133412 main + 1236 (main.c:188)
25  postmaster     0x000024e6 _start + 216
26  postmaster     0x0000240d start + 41

Thread 0 crashed with X86 Thread State (32-bit):  eax: 0x00000000  ebx: 0x0012402e  ecx: 0x00000004  edx: 0x00000000
edi:0x00000000  esi: 0x020775b0  ebp: 0xbfffdca8  esp: 0xbfffdc90   ss: 0x0000001f  efl: 0x00010246  eip: 0x00116ec6
cs:0x00000017   ds: 0x0000001f   es: 0x0000001f   fs: 0x00000000   gs: 0x00000037
 

Binary Images Description:    0x1000 -   0x32cfff postmaster 
/Users/grant/Development/bin/pgsql/bin/postmaster
0x8fe00000 - 0x8fe4afff dyld 46.12    /usr/lib/dyld
0x90000000 - 0x90170fff libSystem.B.dylib     /usr/lib/libSystem.B.dylib
0x95a5a000 - 0x95a5cfff libmathCommon.A.dylib 
/usr/lib/system/libmathCommon.A.dylib

Regards,
Grant