Re: Proposed new create command, CREATE OPERATOR CLASS

On Mon, 29 Oct 2001, Oleg Bartunov wrote:

> On Thu, 25 Oct 2001, Teodor Sigaev wrote:
>
> > >>Wait a second, how can you do that? Doesn't that violate
> > >>pg_amop_opc_strategy_index ?
> > >
> > > This is evidently a bug in the script.  Oleg?
> >
> > Make me right if I mistake.

Don't add @@ to pg_amop.

> > When we was developing operator @@, I saw that postgres don't use index in
> > select if operation has not commutator. But operator with different types in
> > argument can't be commutator with itself. So I maked operator ~~ only for
> > postgres can use index access for operator @@. There is no any difficulties to
> > adding index support for operator ~~. The same things is with contrib/tsearch
> > module.
> >
> > But I think that there is not any other necessity in presence ~~.

?? An operator with different times in the arguements most certainly can
be a commutator with itself.

Try:

select oid, oprname as "n", oprkind as "k", oprleft, oprright, oprresult,
oprcom, oprcode from pg_operator where oprleft <> oprright and oprname =
'+';

and look at the results. There are a number of pairs of same-name
commutators: 552 & 553 add int2 to int4, 688 & 692 add int4 to int8, and
so on.

Also, I was able to do this:

testing=# CREATE OPERATOR @@ (
testing(#    LEFTARG = _int4, RIGHTARG = query_int, PROCEDURE = boolop,
testing(# COMMUTATOR = '@@', RESTRICT = contsel, join = contjoinsel );
CREATE
testing=#  CREATE OPERATOR @@ (
testing(# LEFTARG = query_int, RIGHTARG = _int4, PROCEDURE = rboolop,
testing(# COMMUTATOR = '@@', RESTRICT = contsel, join = contjoinsel );
CREATE
testing=#

> Tom,
>
> this is interesting question - do we really need commutator to get
> postgres to use index. This is the only reason we created  ~~ operator.

Please note: my concern is not with the ~~ operator, it's with trying to
insert that operator into pg_amop. Well, with trying to insert both the @@
and ~~ operators in as strategy (amopstrategy) 20. amopclaid and
amopstrategy are part of a unique index for pg_amop. So you *can't* add
two operators in the same opclass as the same sequence number.

Although, given the above example, I think the ~~ operator should be
renamed the @@ operator. :-)

I think you do need to have both variants of the operator around. A
binary, type asymmetric operator without a commutator is less useful. And
makes lese sense.

Take care,

Bill