> > I think I have found part of the cause. We have duplicate type
> > conversion functions, and the parser is choosing the one that is not
> > in the access method tables.
>
> I don't think so for this case; I got stuck on this for awhile too. It
> seems that "oidint4" is an actual data type, so has an "oidint4eq"
> comparison function. The parser/planner/optimizer is finding the correct
> functions, which are "oideqint4" and "int4eqoid".
>
> Don't know what "oidint4" actually does or how it's used. Confusing...
OK, new information. The following query shows the pg_proc names for
oid.*int4:
select pg_operator.oid as pg_operator_oid, pg_proc.proname from pg_proc,
pg_operator where proname ~ 'oid.*int4' and pg_proc.oid =
RegprocToOid(pg_operator.oprcode)
pg_operator_oid|proname
---------------+---------
1137|oideqint4
930|oidint4lt
931|oidint4le
932|oidint4eq
933|oidint4ge
934|oidint4gt
935|oidint4ne
(7 rows)
The pg_operator_oid is the value stored in pg_amop.amopopr field. The
optimizer is finding a function that works for the query, in our case
oideqint4, with a pg_operator oid of 1137.
The problem is that there is no pg_am.amopopr for 1137. What is defined
for oid is this:
/*
* nbtree oid_ops
*/
DATA(insert OID = 0 ( 403 427 609 1 btreesel btreenpage ));
DATA(insert OID = 0 ( 403 427 611 2 btreesel btreenpage ));
DATA(insert OID = 0 ( 403 427 607 3 btreesel btreenpage ));
DATA(insert OID = 0 ( 403 427 612 4 btreesel btreenpage ));
DATA(insert OID = 0 ( 403 427 610 5 btreesel btreenpage ));
For example, the third number in the third line is 607, which is the
amopopr, and is oideq. We know we can use this for the query because
int4 and oid are identical, but the parser has already chosen the more
appopriate 1137/oideqint4. If we could add an extra line to this file,
perhaps:
DATA(insert OID = 0 ( 403 427 607 3 btreesel btreenpage ));
However, I don't think the access methods allow more than one line in
this way.
The other problem is that there is not a <, >, etc for int4/oid, so a
separate op type can not be created. In fact, I am not sure it would
work anyway. I think the operator oid chosen by the backend must
EXACTLY match the oid stored in pg_amop.amopopr for the index to be
used.
That is what match_clause_to_indexkey is doing by calling op_class().
[Update you source trees. The op_class parameter names were wrong,
called opid instead of opno, so it was very confusing. Fixed now.]
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
