Re: [WIP] Support for "ANY/ALL(array) op scalar" (Was: Re: Boolean operators without commutators vs. ALL/ANY)

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> On ons, 2011-06-15 at 22:19 -0400, Tom Lane wrote:
>> (FWIW, I've come around to liking the idea of using =~ and the obvious
>> variants of that for regex operators, mainly because of the Perl
>> precedent.)

> Maybe I'm not completely up to date on this, but I observe that Perl
> itself doesn't appear to have a commutator for =~ .

Nope, it doesn't. But that doesn't mean we don't need one. Aside from
the ANY/ALL business, the index infrastructure is asymmetrical: it will
only deal with indexable WHERE clauses that have the index column on the
left. So those are two very good reasons to make sure that
operators returning boolean all have commutators. (I will refrain for
the moment from speculating whether we'll ever have an index type that
supports regexp match directly as an indexable operator...)

At the moment, this query:

select oid::regoperator, oprcode from pg_operator
where oprkind = 'b' and oprresult = 'bool'::regtype and oprcom = 0;

says we have 83 such operators without commutators. Of these, if I'm
counting correctly, 26 are the LIKE and regex operators in question.
It looks like at least another twenty do in fact constitute commutator
pairs, they're just not documented as such via oprcom. The remaining
forty or less are a morass of functions for obsolete types, duplicate
names for the same function, etc. I don't think it's unreasonable at
all to establish an expectation that all non-legacy binary boolean
operators should come in commutator pairs.

regards, tom lane