Backend 8.0.0B4 crash on SELECT ... - Mailing list pgsql-hackers

I can reproduce a 8.0.0B4 backend crash on OSX 1.3.5. I can't even get 
it to analyze the query to get an idea of what the plan it is trying. 
What can I do to help diagnose what is going on?

Here's the query:

SELECT unit.id FROM unit WHERE  unit.delete = 'f' AND unit.status=2AND (            (unit.rent >= 100.0 AND unit.rent
<=600.0)            OR (unit.rent_type = 2 AND (                    (unit.slidinglow >= 100.0 AND unit.slidinglow <=
600.0)                   OR (unit.slidinglow < 100.0 AND unit.rent > 600.0)            )))AND unit.belowHUDFMR = 't'
ANDunit.features & 1::INT8 =1::INT8;
 

Basically, it is trying to pull in all available (status=2) rental 
(unit.features & 1::INT8 =1::INT8) unit listings between 100 and 600 
dollars per month (rent_type 2 is sliding scale rent, which we have to 
test for any overlap between the query values and the listing's sliding 
scale rent range). This query was produced by a bug in our middleware, 
neglecting to include city-limiting clauses (hence then being able to 
use an index, specifically "unit_rental_search". But still, pg ought 
not to belly up.

It works on 8.0.0B3, with query plan:
 Seq Scan on unit  (cost=0.00..2480.16 rows=11 width=8) (actual 
time=0.242..452.632 rows=2326 loops=1)   Filter: (("delete" = false) AND (status = 2) AND (((rent >= 
100::double precision) AND (rent <= 600::double precision)) OR 
((rent_type = 2) AND (((slidinglow >= 100::double precision) AND 
(slidinglow <= 600::double precision)) OR ((slidinglow < 100::double 
precision) AND (rent > 600::double precision))))) AND (belowhudfmr = 
true) AND ((features & 1::bigint) = 1::bigint)) Total runtime: 464.402 ms

(2326 rows returned)

Here's the backtrace ...

#0  0x900429ac in kill ()
#1  0x9009eb1c in abort ()
#2  0x002c10f4 in ExceptionalCondition (conditionName=0x31fd18 
"!(((((Node*)(restrictinfo))->type) == T_RestrictInfo))", 
errorType=0x2fca7c "FailedAssertion", fileName=0x31fe60 "indxpath.c", 
lineNumber=853) at assert.c:51
#3  0x00186948 in pred_test_restrict_list (predicate=0xc7f444, 
restrictinfo_list=0xc8176c) at indxpath.c:853
#4  0x00186890 in pred_test_recurse_pred (predicate=0xc7f444, 
restrictinfo_list=0xc8176c) at indxpath.c:834
#5  0x001866cc in pred_test (predicate_list=0xc7f484, 
restrictinfo_list=0xc8176c) at indxpath.c:788
#6  0x0018bb60 in best_or_subclause_index (root=0x201ff38, 
rel=0x20200e4, subclause=0xc7fef0, retIndexInfo=0xbfffdf8c, 
retIndexClauses=0xbfffdf90, retIndexQuals=0xbfffdf94, 
retStartupCost=0xbfffdf98, retTotalCost=0xbfffdfa0) at orindxpath.c:379
#7  0x0018b8dc in best_or_subclause_indexes (root=0x201ff38, 
rel=0x20200e4, subclauses=0xc7f9ec) at orindxpath.c:264
#8  0x0018b7d8 in create_or_index_paths (root=0x201ff38, rel=0x20200e4) 
at orindxpath.c:207
#9  0x001804c0 in set_plain_rel_pathlist (root=0x201ff38, 
rel=0x20200e4, rte=0x201ffc4) at allpaths.c:181
#10 0x00180400 in set_base_rel_pathlists (root=0x201ff38) at 
allpaths.c:135
#11 0x001801b8 in make_one_rel (root=0x201ff38) at allpaths.c:79
#12 0x00194604 in query_planner (root=0x201ff38, tlist=0xc7b2c4, 
tuple_fraction=0, cheapest_path=0xbfffe278, sorted_path=0xbfffe27c) at 
planmain.c:154
#13 0x00195b28 in grouping_planner (parse=0x201ff38, tuple_fraction=0) 
at planner.c:935
#14 0x00194d6c in subquery_planner (parse=0x201ff38, tuple_fraction=0) 
at planner.c:326
#15 0x001948b4 in planner (parse=0x201ff38, isCursor=0 '\0', 
cursorOptions=0, boundParams=0x0) at planner.c:129
#16 0x001fdbb8 in pg_plan_query (querytree=0x201ff38, boundParams=0x0) 
at postgres.c:647
#17 0x001fdd04 in pg_plan_queries (querytrees=0xc7b24c, 
boundParams=0x0, needSnapshot=0 '\0') at postgres.c:715
#18 0x001fe02c in exec_simple_query (query_string=0x201e634 "SELECT 
unit.id FROM unit WHERE  unit.delete = 'f' AND unit.status=2\nAND 
(\n(unit.rent >= 100.0 AND unit.rent <= 600.0)\nOR (unit.rent_type = 2 
AND (\n(unit.slidinglow >= 100.0 AND unit.slidinglow <= 600."...) at 
postgres.c:874
#19 0x00201a0c in PostgresMain (argc=5, argv=0x2001c74, 
username=0x2001c4c "social") at postgres.c:2961
#20 0x001b29e0 in BackendRun (port=0xd00e70) at postmaster.c:2773
#21 0x001b1dfc in BackendStartup (port=0xd00e70) at postmaster.c:2399
#22 0x001af454 in ServerLoop () at postmaster.c:1144
#23 0x001aec64 in PostmasterMain (argc=6, argv=0xd00760) at 
postmaster.c:863
#24 0x001539ec in main (argc=6, argv=0xd00760) at main.c:270

Here's the table and index definitions ...

(sorry table is *huge* -- gotta bust it up one of these days).

social=# \d unit                     Table "public.unit"        Column         |           Type           | Modifiers
-----------------------+--------------------------+----------- id                    | bigint                   | not
nullcity                  | bigint                   | zipcode               | bigint                   | building
       | bigint                   | waitinglist_id        | bigint                   | leadpaintunit         | bigint
               | status                | integer                  | not null features              | bigint
     | deletedate            | timestamp with time zone | lastupdatedate        | timestamp with time zone | delete
          | boolean                  | not null version               | integer                  | not null rent_type
         | integer                  | not null rent                  | double precision         | not null slidinglow
        | double precision         | not null median_rent_20        | double precision         | median_rent_30
|double precision         | median_rent_40        | double precision         | median_rent_50        | double precision
       | median_rent_60        | double precision         | securitydeposit       | double precision         | not null
minimumlease         | double precision         | not null income_based_ss_rent  | boolean                  |
medianrentnorealrent | boolean                  | not null negotiabledeposit     | boolean                  |
forsaleprice         | integer                  | minimum_down_payment  | integer                  |
forsale_sliding_low  | integer                  | homeowner_fee         | integer                  |
negotiabledownpayment| boolean                  | bedroom               | double precision         | not null bathroom
           | double precision         | not null yearbuilt             | integer                  | not null
maxoccupancyadult    | integer                  | not null maxoccupancychild     | integer                  | not null
squarefeet           | integer                  | not null section8              | boolean                  | not null
pets                 | boolean                  | not null smoking               | boolean                  | not null
furniture            | boolean                  | not null availabledate         | timestamp with time zone |
marketdate           | timestamp with time zone | postdate              | timestamp with time zone | fridge
  | boolean                  | not null washer                | boolean                  | not null washerhookup
 | boolean                  | not null dryer                 | boolean                  | not null gasincluded
| boolean                  | not null electricincluded      | boolean                  | not null waterincluded
|boolean                  | not null gasheat               | boolean                  | not null oilheat
|boolean                  | not null electricheat          | boolean                  | not null electricwater
|boolean                  | not null gaswater              | boolean                  | not null taxcredit
|boolean                  | not null subsidized            | boolean                  | not null senior
|boolean                  | not null seniorlicensed        | boolean                  | not null leadpaint
|boolean                  | not null air                   | boolean                  | not null dishwasher
|boolean                  | not null trashcollection       | boolean                  | not null yardmaintenance
|boolean                  | not null belowhudfmr           | boolean                  | not null deck
|boolean                  | purchasemoreparking   | boolean                  | trashpickup           | boolean
       | basementtype          | integer                  | parkingtype           | integer                  |
yardmaintenancetype  | integer                  | parkingspaces         | integer                  | parkingfee
  | double precision         | trashpickupfee        | double precision         | yardmaintenancefee    | double
precision        | pubxport_blocks       | integer                  | not null access                | boolean
       | not null represents            | integer                  | not null available_count       | integer
      | not null waiting_list          | boolean                  | not null type                  | text
     | street                | text                     | street2               | text                     |
description          | text                     | petsconditions        | text                     | stovetype
  | text                     | flooring              | text                     | promotion             | text
          | amenities             | text                     | parkingcomment        | text                     |
qualifiers           | text                     | upgrades_addons       | text                     | lastmodusername
  | text                     | sliding_qualifiers    | text                     |
 
Indexes:    "unit_pkey" PRIMARY KEY, btree (id)    "unit_building" btree (building, represents)
"unit_forsale_search"btree (city, forsaleprice) WHERE forsaleprice > 0    "unit_rental_search" btree (city, status,
belowhudfmr,"delete") 
 
WHERE statu
s = 2 AND belowhudfmr = true AND "delete" = false    "unit_sys_disabled" btree (building, status) WHERE status = 8
Foreign-key constraints:    "unit_building_fkey" FOREIGN KEY (building) REFERENCES building(id)    "unit_city_fkey"
FOREIGNKEY (city) REFERENCES housingcity(id)    "unit_leadpaintunit_fkey" FOREIGN KEY (leadpaintunit) REFERENCES 
 
leadpaintun
it(id)    "unit_waitinglist_id_fkey" FOREIGN KEY (waitinglist_id) REFERENCES 
waiting_l
ist(id)    "unit_zipcode_fkey" FOREIGN KEY (zipcode) REFERENCES zipcode(id)


----
James Robinson
Socialserve.com