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
