Обсуждение: Strange issue with CREATE OPERATOR CLASS

All,

I've been working on some scripts (for pgfoundry) which help in 
"cleaning up" databases which have TSearch and other contrib modules 
installed to schema public.  However, I ran into this odd issue:

ERROR:  btree operators must return boolean
STATEMENT:  CREATE OPERATOR CLASS contrib.tsquery_ops    DEFAULT FOR TYPE tsquery USING btree AS    OPERATOR 1
<(tsquery,tsquery),    OPERATOR 2 <=(tsquery,tsquery) ,    OPERATOR 3 =(tsquery,tsquery) ,    OPERATOR 4
>=(tsquery,tsquery),    OPERATOR 5 >(tsquery,tsquery) ,    FUNCTION 1 tsquery_cmp(tsquery,tsquery);
 

What appears to be happening there is that PG isn't finding the tsearch 
operators or is selecting the wrong operators.  Manually changing the 
search_path to "contrib, public" instead of "public, contrib" fixes the 
issue, but it seems odd that create opclass can't find operators unless 
they're in the "first" schema in the path, when other CREATE statements 
have no such difficulty.

--Josh

Josh Berkus <josh@agliodbs.com> writes:
> I've been working on some scripts (for pgfoundry) which help in 
> "cleaning up" databases which have TSearch and other contrib modules 
> installed to schema public.  However, I ran into this odd issue:

> ERROR:  btree operators must return boolean

Is that the actual error message?  The closest string I can find in 8.3
or HEAD is "index operators must return boolean".

> What appears to be happening there is that PG isn't finding the tsearch 
> operators or is selecting the wrong operators.

It seems possible that you could get an error like that as a result of
something creating a shell operator and not filling it in afterwards,
but if so I'd blame the earlier something; the worst that can be laid at
CREATE OPERATOR CLASS's door is giving a less helpful error message than
it could.  We really need to see a complete example of how to reproduce
the problem before speculating about appropriate fixes.
        regards, tom lane

Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
>> I've been working on some scripts (for pgfoundry) which help in 
>> "cleaning up" databases which have TSearch and other contrib modules 
>> installed to schema public.  However, I ran into this odd issue:
> 
>> ERROR:  btree operators must return boolean
> 
> Is that the actual error message?  The closest string I can find in 8.3
> or HEAD is "index operators must return boolean".

Oh!  Sorry, this is 8.2.12.  I can't reproduce it in 8.3 for obvious 
reasons, since there the TSearch stuff comes built-in.

> It seems possible that you could get an error like that as a result of
> something creating a shell operator and not filling it in afterwards,
> but if so I'd blame the earlier something; the worst that can be laid at
> CREATE OPERATOR CLASS's door is giving a less helpful error message than
> it could.  We really need to see a complete example of how to reproduce
> the problem before speculating about appropriate fixes.

Yeah, I'm trying to figure out the minimum test case; the one I have now 
contains proprietary client data, and *just* using tsearch2.sql doesn't 
produce the problem.

--Josh

Josh Berkus <josh@agliodbs.com> writes:
> Tom Lane wrote:
>> Is that the actual error message?  The closest string I can find in 8.3
>> or HEAD is "index operators must return boolean".

> Oh!  Sorry, this is 8.2.12.

Oh, OK.  It's the same case though.  Look for operator definitions that
specify a commutator or negator operator that never gets provided.
        regards, tom lane