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