Re: [HACKERS] "ExecInitIndexScan: both left and right..." meaning?

I'm seeing an old showstopper bug in a new form in 6.5.2:

ExecInitIndexScan: both left and right op's are rel-vars

[I also sorely wish the error message identified the offending part
of the WHERE clause.]

I'm running with PGOPTIONS="-fn" (the previously assumed
brute force prevention), and have partially verified there are no
nested loops occurring (output & details below). Vacuum analyze
no longer helps as it once did.

This was presumed fixed in the coming 7.0, but the latest
manifestation suggests the problem may not be fully understood.
More below...

For the sordid history, see

http://www.deja.com/qs.xp?QRY=ExecInitIndexScan&OP=dnquery.xp&showsort=date

or search Udm for ExecIndexInitScan.

Here's context on where we last left it...

Tom Lane wrote:

> [...long explanation of bug and solution deleted...]
> I have fixed this in current sources by removing the field in question
> from RestrictInfo nodes and storing the information in separate lists.
> But it's a pretty major change and I don't want to try to back-patch it.
>
> I would suggest, instead, that you work around the problem until 7.0
> comes out. I think you could do this by removing your two-column
> indexes in favor of single-column indexes, or even just switching the
> order of the indexes... However switching the order would be
> a bit fragile since it'd depend on which fields you compare to constants
> and which ones you use as join keys in your queries. If that doesn't
> work, a brute-force solution is to run your application with environment
> variable PGOPTIONS="-fn" (forbid nestloop joins), which discourages the
> planner from considering nestloop joins at all. The bug will not arise
> if a merge or hash join plan is used.

It appears the -fn flag is not preventing the bug.

One detail seems odd (and different from the nested-loop manifestation):
when the offending "rather large" SELECT query is run via Apache/
mod_perl/DBI/DBD::Pg, the error occurs, but when I cut and paste the
query from the logs into psql, it does not trigger the error (it also does not
yield any results).

I'm including the offending query and explain output below...

Cheers,
Ed Loehr

SELECT sum( cet.default_budget_per_unit *
cahrn.hr_count *
cahrn.duration ) AS "amount"
FROM contract_activity_hr_need cahrn,
contract_expense_type cet,
contract_activity_type_expense_type catet,
contract_activity_type cat, activity pa
WHERE cet.contract_id = 1
AND catet.contract_id = 1
AND cahrn.contract_id = 1
AND pa.contract_id = 1
AND cat.contract_id = 1
AND cet.expense_unit_id = 3
AND pa.activity_state_id <> 5
AND pa.activity_state_id <> 4
AND (pa.billable = 0 OR cahrn.billable = 0)
AND pa.activity_type_id = cat.activity_type_id
AND catet.expense_type_id = cet.expense_type_id
AND catet.activity_type_id = cat.activity_type_id
AND cahrn.contract_activity_type_id = cat.id;

20000109.02:13:48.783 [13865] NOTICE: QUERY PLAN:

Aggregate (cost=28.61 rows=6608 width=52)
-> Hash Join (cost=28.61 rows=6608 width=52)
-> Hash Join (cost=14.74 rows=1 width=44)
-> Seq Scan on contract_activity_hr_need cahrn (cost=2.02 rows=3
width=16)
-> Hash (cost=11.58 rows=1 width=28)
-> Merge Join (cost=11.58 rows=1 width=28)
-> Seq Scan (cost=9.34 rows=1 width=20)
-> Sort (cost=9.34 rows=1 width=20)
-> Hash Join (cost=8.34 rows=1 width=20)
-> Index Scan using contract_activi
ty_type_exp_pkey on contract_activity_type_expense_ catet (cost=3.87 rows=38 wi
dth=8)
-> Hash (cost=2.18 rows=1 width=12
)
-> Index Scan using contract_
expense_type_pkey on contract_expense_type cet (cost=2.18 rows=1 width=12)
-> Index Scan using contract_activity_type_pkey on co
ntract_activity_type cat (cost=2.12 rows=3 width=8)
-> Hash (cost=13.84 rows=0 width=8)
-> Index Scan using activity_cid on activity pa (cost=13.84 rows
=0 width=8)