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

Tom Lane wrote:

> Ed Loehr <ELOEHR@austin.rr.com> writes:
> >    Anyone know what this error is or how to prevent it?  Seems to
> >    usually show up on large queries...
> >        "ExecInitIndexScan: both left and right op's are rel-vars"
>
> Sounds like you've found a bug.  How about a specific example of
> a query that causes this?

Unfortunately, this is the simplest example I have to offer.  The
following query succeeds numerous times before going into a continuous
failure mode due to the error above.  Vacuuming the DB fixes the
problem temporarily "for a while".

SELECT sum( cet.default_budget_per_unit * cahrn.hr_count )
FROM contract_activity_hr_need cahrn, contract_expense_type cet,    contract_activity_type_expense_type catet,
contract_activity_typecat, activity pa
 
WHERE -- lame attempt at making this easy on the eye...                 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= 6 AND            pa.activity_state_id <> 5 AND            pa.activity_state_id <> 4 AND
       (pa.billable = 0 OR cahrn.billable = 0) 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 AND
pa.activity_type_id= cat.activity_type_id;
 

Without including the rather lengthy schema definition for the 5
tables involved, let me clarify the data types of the example by
saying that every single column in the query above is of type INTEGER
except for cet.default_budget_per_unit in the SELECT clause, which is
of type FLOAT8.   Note that all columns above ending in 'XXX_id' are
foreign keys referencing the 'id' column of the 'XXX' table, which is
declared as type SERIAL.  Note also that every table has a couple of
book-keeping columns ('creation_time' and 'record_status').  For
example, cet.contract_id is an INTEGER value acting as a foreign key
to the 'contract' table:

CREATE TABLE contract (  id            SERIAL, -- pkey, ref'd as fkey 'contract_id'  ...  creation_time DATETIME NOT
NULLDEFAULT CURRENT_TIMESTAMP,  record_status INTEGER NOT NULL DEFAULT 1
 
);

CREATE TABLE contract_expense_type (  id           SERIAL,  contract_id  INTEGER NOT NULL, -- fkey to contract table
... creation_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,  record_status INTEGER NOT NULL DEFAULT 1
 
);

One might suspect the size of my tuples might be a factor.  I believe
my **largest** rowsize in any table is 152 bytes, though I'm not sure
how VARCHARs are sized (all my varchar values are considerably less
than 256 bytes, and rarely are there more than 2 of these in a table).

I think the error comes from line 862 of
.../src/backend/executor/nodeIndexscan.c, though it's possible it may
have come at times from line 925 of the same file (a similar error msg
differing only by an apostrophe).

Other current configuration details:
   Pgsql configured with:  ./configure --prefix=/usr/local/pgsql
-with-odbc
   PG:  PostgreSQL 6.5.2 on i686-pc-linux-gnu, compiled by gcc
egcs-2.91.66   OS:  RH6.1 Linux XXX 2.2.12-20smp #1 SMP Mon Sep 27 10:34:45 EDT
1999 i686 unknown,   HW:  dual P3 600Mhz w/1Gb RAM and 3 UW 9Gb SCSI drives in software
RAID.   SW:  Apache 1.3.9 with mod_ssl 2.4.9, mod_perl 1.21, DBI 1.13,
DBD/Pg 0.92

I've also seen this problem on RH6.0, Pg6.5.2, Linux2.2.12-15,
512MbRAM, dual450MhzP3, NoRAID, mod_ssl 2.4.5...

Any help would be greatly appreciated.  I can code around this, of
course, but it'd be nice...

Cheers,
Ed Loehr