Thread: Proposed new create command, CREATE OPERATOR CLASS

I'd like to propose a new command, CREATE OPERATOR CLASS. Its purpose is
to create a named operator class, so that you can create new types of
index ops. Also, its inclusion would remove the section of the
documentation where we tell people how to manually manipulate the system
tables.

Since schema support is going to change some of the details of the system
tables in important ways, I think it's better to move away from manual
updates.

The command is basically an instrumentation of the documentation on how to
add new operator classes.

Here's the syntax I'd like to propose:

CREATE OPERATOR CLASS <name> [DEFAULT] FOR TYPE <typename> USING <access
method> WITH <list of operators> AND <list of support functions>

New keywords are "CLASS" (SQL99 reserved word) and "REPEATABLE" (SQL99
non-reserved word, see below for usage).

<name> is the class's name, and <typename> is the type to be indexed.
<access method> is the assosciated access method from pg_am (btree, rtree,
hash, gist).

The presence of [DEFAULT] indicates that this operator class shold be made
the default operator class for the type.

<list of operators> is a comma-delimited list of operator specs. An
operator spec is either an operator or an operator followed by the keyword
"REPEATABLE". The presence of "REPEATABLE" indicates that amopreqcheck
should be set to true for this operator. Each item in this list will
generate an entry in pg_amop.

<list of support functions> is a comma-seperated list of functions used to
assist the index method. Each item in this list will generate an item in
pg_amproc.

I agree that I think it is rare that anything will set "REPEATABLE", but
the point of this effort is to keep folks from mucking around with the
system tables manually, so we should support making any reasonable entry
in pg_amop.

Here's an example based on the programmer's guide. We've created the type
"complex", and have comparison functions complex_abs_lt, complex_abs_le,
complex_abs_eq, complex_abs_gt, complex_abs_ge. Then let us have created
operators "||<", "||<=", "||=", "||>", "||>=" based on them. We also have
the complex_abs_cmp helper function. To create the operator class, the
command would be:

CREATE OPERATOR CLASS complex_abs_ops DEFAULT FOR TYPE complex USING
btree with ||<, ||<=, ||=, ||>=, ||> and complex_abs_cmp;

Among other things, complex_abs_ops would be the default operator class
for the complex type after this command.


An example using REPEATABLE would be:

CREATE OPERATOR CLASS complex_abs_ops DEFAULT FOR TYPE complex USING btree
with ||< REPEATABLE, ||<=, ||=, ||>=, ||> REPEATABLE and complex_abs_cmp;

Note: I don't think the above command will create a correct operator
class, it just shows how to add REPEATABLE.

The alternative to "REPEATABLE" would be something like
"hit_needs_recheck" after the operator. Suggestions?

Thoughts?

Take care,

Bill

On Tue, 23 Oct 2001, Bill Studenmund wrote:

> Here's the syntax I'd like to propose:
>
> CREATE OPERATOR CLASS <name> [DEFAULT] FOR TYPE <typename> USING <access
> method> WITH <list of operators> AND <list of support functions>

Hmmm.. Teach me to read the docs. :-) There's no way to set opckeytype. So
hwo about:

CREATE OPERATOR CLASS <name> [DEFAULT] FOR TYPE <typename> [AS <stored
type>] USING <access method> WITH <list of operators> AND <list of support
functions>

With AS <stored type> present, the opckeytype column gets set to that type
name's oid.

> New keywords are "CLASS" (SQL99 reserved word) and "REPEATABLE" (SQL99
> non-reserved word, see below for usage).
>
> <name> is the class's name, and <typename> is the type to be indexed.
> <access method> is the assosciated access method from pg_am (btree, rtree,
> hash, gist).
>
> The presence of [DEFAULT] indicates that this operator class shold be made
> the default operator class for the type.
>
> <list of operators> is a comma-delimited list of operator specs. An
> operator spec is either an operator or an operator followed by the keyword
> "REPEATABLE". The presence of "REPEATABLE" indicates that amopreqcheck
> should be set to true for this operator. Each item in this list will
> generate an entry in pg_amop.

I decided to change that to an operator followed by "needs_recheck" to
indicate a recheck is needed. "needs_recheck" is not handled as a keyword,
but as an IDENT which is examined at parse time.

> <list of support functions> is a comma-seperated list of functions used to
> assist the index method. Each item in this list will generate an item in
> pg_amproc.
>
> I agree that I think it is rare that anything will set "REPEATABLE", but
> the point of this effort is to keep folks from mucking around with the
> system tables manually, so we should support making any reasonable entry
> in pg_amop.

Take care,

Bill

Bill Studenmund <wrstuden@netbsd.org> writes:
> Do any of the access methods really support using non-binary operators?

Whether they do today is not the question.  The issue is whether they
could --- and they certainly could.

> Oh gross. I just looked at contrib/intarray, and it defines two entries in
> pg_amop for amopstrategy number 20. They do happen to be commutators of
> each other. Look for the @@ and ~~ operators.

> Wait a second, how can you do that? Doesn't that violate
> pg_amop_opc_strategy_index ?

It sure does, but running the script shows that the second insert
doesn't try to insert any rows.  There's no entry in the temp table
for ~~ because its left and right operands are not the types the
SELECT/INTO is looking for.

This is evidently a bug in the script.  Oleg?
        regards, tom lane

On Wed, 24 Oct 2001, Tom Lane wrote:

> Bill Studenmund <wrstuden@netbsd.org> writes:
> > I'd like to propose a new command, CREATE OPERATOR CLASS.
>
> Seems like a good idea.
>
> > operator spec is either an operator or an operator followed by the keyword
> > "REPEATABLE". The presence of "REPEATABLE" indicates that amopreqcheck
> > should be set to true for this operator.
>
> This is bogus, since REPEATABLE is a very poor description of the
> meaning of amopreqcheck; to the extent that it matches the meaning
> at all, it's backwards.  Don't pick a keyword for this solely on the
> basis of what you can find that's already reserved by SQL99.
>
> Given the restricted syntax, the keyword could be a TokenId anyway,
> so it's not really reserved; accordingly there's no need to limit
> ourselves to what SQL99 says we can reserve.
>
> Perhaps use "RECHECK"?  That would fit the field more closely...

I was writing a note saying that as this one came in. Yes, it's now a
TokenId, and I look for the text "needs_recheck".

> > I agree that I think it is rare that anything will set "REPEATABLE", but
> > the point of this effort is to keep folks from mucking around with the
> > system tables manually, so we should support making any reasonable entry
> > in pg_amop.
>
> Then you'd better add support for specifying an opckeytype, too.  BTW
> these things are not all that rare; there are examples right now in
> contrib.

Yep, I noticed that.

> > CREATE OPERATOR CLASS complex_abs_ops DEFAULT FOR TYPE complex USING
> > btree with ||<, ||<=, ||=, ||>=, ||> and complex_abs_cmp;
>
> This syntax is obviously insufficient to identify the procedures, since
> it doesn't show argument lists (and we do allow overloading).  Less

So then funcname(type list) [, funcname(type list)]  would be the way to
go?

> obviously, it's not sufficient to identify the operators either.  I
> think you're implicitly assuming that only binary operators on the
> specified type will ever be members of index opclasses.  That does not
> seem like a good assumption to wire into the syntax.  Perhaps borrow

Well, the requirement of binarity is something which is explicit in our
example documentation, and so that's why I used it.

> the syntax used for DROP OPERATOR, which is ugly but not ambiguous:
>
>     operator (type, type)
>     operator (type, NONE)
>     operator (NONE, type)
>
> We could allow an operator without any parenthesized args to imply a
> binary op on the specified type, which would certainly be the most
> common case.

Do any of the access methods really support using non-binary operators?

> BTW, is there any need to support filling nonconsecutive amopstrategy or
> amprocnum slots?  This syntax can't do that.  GiST seems to have a
> pretty loose idea of what set of strategy numbers you can have, so
> there might possibly be a future need for that.

I can add support for skipping operators, if needed. A comma followed by a
comma would indicate a null name.

Oh gross. I just looked at contrib/intarray, and it defines two entries in
pg_amop for amopstrategy number 20. They do happen to be commutators of
each other. Look for the @@ and ~~ operators.

Wait a second, how can you do that? Doesn't that violate
pg_amop_opc_strategy_index ? It's supposed to make pairs of amopclaid and
amopstrategy be unique.

Confused....

> Also, it might be better to use a syntax in the style of CREATE
> OPERATOR, with a list of param = value notations, because that's
> more easily extensible if we change the opclass stuff again.
>
>     CREATE OPERATOR CLASS classname (
>         basetype = complex,
>         default,
>         operator1 = ||< ,
>         ...
>         proc1 = complex_abs_cmp );
>
> However, specifying the proc arglists in this style would be awfully
> tedious :-(.  I can't think of anything better than
>
>         proc1arg1 = complex,
>         proc1arg2 = complex,
>         ...
>
> which is mighty ugly.

Which is why I didn't use it. :-)

If we can't make the other syntax work, then we can go with a DefineStmt
type syntax.

Take care,

Bill

Bill Studenmund <wrstuden@netbsd.org> writes:
> I'd like to propose a new command, CREATE OPERATOR CLASS.

Seems like a good idea.

> operator spec is either an operator or an operator followed by the keyword
> "REPEATABLE". The presence of "REPEATABLE" indicates that amopreqcheck
> should be set to true for this operator.

This is bogus, since REPEATABLE is a very poor description of the
meaning of amopreqcheck; to the extent that it matches the meaning
at all, it's backwards.  Don't pick a keyword for this solely on the
basis of what you can find that's already reserved by SQL99.

Given the restricted syntax, the keyword could be a TokenId anyway,
so it's not really reserved; accordingly there's no need to limit
ourselves to what SQL99 says we can reserve.

Perhaps use "RECHECK"?  That would fit the field more closely...

> I agree that I think it is rare that anything will set "REPEATABLE", but
> the point of this effort is to keep folks from mucking around with the
> system tables manually, so we should support making any reasonable entry
> in pg_amop.

Then you'd better add support for specifying an opckeytype, too.  BTW
these things are not all that rare; there are examples right now in
contrib.

> CREATE OPERATOR CLASS complex_abs_ops DEFAULT FOR TYPE complex USING
> btree with ||<, ||<=, ||=, ||>=, ||> and complex_abs_cmp;

This syntax is obviously insufficient to identify the procedures, since
it doesn't show argument lists (and we do allow overloading).  Less
obviously, it's not sufficient to identify the operators either.  I
think you're implicitly assuming that only binary operators on the
specified type will ever be members of index opclasses.  That does not
seem like a good assumption to wire into the syntax.  Perhaps borrow
the syntax used for DROP OPERATOR, which is ugly but not ambiguous:
operator (type, type)operator (type, NONE)operator (NONE, type)

We could allow an operator without any parenthesized args to imply a
binary op on the specified type, which would certainly be the most
common case.

BTW, is there any need to support filling nonconsecutive amopstrategy or
amprocnum slots?  This syntax can't do that.  GiST seems to have a
pretty loose idea of what set of strategy numbers you can have, so
there might possibly be a future need for that.

Also, it might be better to use a syntax in the style of CREATE
OPERATOR, with a list of param = value notations, because that's
more easily extensible if we change the opclass stuff again.
CREATE OPERATOR CLASS classname (    basetype = complex,    default,    operator1 = ||< ,    ...    proc1 =
complex_abs_cmp);
 

However, specifying the proc arglists in this style would be awfully
tedious :-(.  I can't think of anything better than
    proc1arg1 = complex,    proc1arg2 = complex,    ...

which is mighty ugly.
        regards, tom lane

Bill Studenmund <wrstuden@netbsd.org> writes:
> [ revised proposal for CREATE OPERATOR CLASS syntax ]

I don't like the idea of writing a bunch of consecutive commas (and
having to count them correctly) for cases where we're inserting
noncontigous amopstrategy or amprocnum numbers.  Perhaps the syntax
for the elements of the lists could be
[ integer ]  operator  [ ( argtype, argtype ) ] [ RECHECK ]
[ integer ]  funcname ( argtypes )

where if the integer is given, it is the strategy/procnum for this
entry, and if it's not given then it defaults to 1 for the first
item and previous-entry's-number-plus-one for later items.

Or just require the integer all the time.  That seems a lot less
mistake-prone, really.  Concision is not a virtue in the case of
a command as specialized as this.  Is there really anything wrong with

CREATE OPERATOR CLASS complex_abs_opsDEFAULT FOR TYPE complex USING btreeWITH    1  ||<,    2  ||<=,    3  ||=,    4
||>=,   5  ||>AND    1  complex_abs_cmp(complex, complex);
 

(One could imagine adding system catalogs that give symbolic names
to the strategy/procnum numbers for each access method, and then
allowing names instead of integers in this command.  I'm not sure
whether GiST has sufficiently well-defined strategy numbers to make that
work, but even if not, I like this better than a positional approach to
figuring out which operator is which.)


> I decided to change that to an operator followed by "needs_recheck" to
> indicate a recheck is needed. "needs_recheck" is not handled as a keyword,
> but as an IDENT which is examined at parse time.

Ugh.  Make it a keyword.  As long as it can be a TokenId there is no
downside to doing so, and doing it that way eliminates interesting
issues about case folding etc.  (Did you know that case folding rules
are slightly different for keywords and identifiers?)

I still like RECHECK better than NEEDS_RECHECK, but that's a minor
quibble.
        regards, tom lane

>>Wait a second, how can you do that? Doesn't that violate
>>pg_amop_opc_strategy_index ?
>>
> 
> It sure does, but running the script shows that the second insert
> doesn't try to insert any rows.  There's no entry in the temp table
> for ~~ because its left and right operands are not the types the
> SELECT/INTO is looking for.
> 
> This is evidently a bug in the script.  Oleg?
> 


Make me right if I mistake.

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 ~~.



-- 
Teodor Sigaev
teodor@stack.net

On Thu, 25 Oct 2001, Teodor Sigaev wrote:

> Make me right if I mistake.
>
> 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 ~~.

So only one of the two needs to go into pg_amop, correct? Then everything
else is fine.

Take care,

Bill

On Wed, 24 Oct 2001, Tom Lane wrote:

> Bill Studenmund <wrstuden@netbsd.org> writes:
> > [ revised proposal for CREATE OPERATOR CLASS syntax ]
>
> I don't like the idea of writing a bunch of consecutive commas (and
> having to count them correctly) for cases where we're inserting
> noncontigous amopstrategy or amprocnum numbers.  Perhaps the syntax
> for the elements of the lists could be
>
>     [ integer ]  operator  [ ( argtype, argtype ) ] [ RECHECK ]
>
>     [ integer ]  funcname ( argtypes )
>
> where if the integer is given, it is the strategy/procnum for this
> entry, and if it's not given then it defaults to 1 for the first
> item and previous-entry's-number-plus-one for later items.

That would work.

> Or just require the integer all the time.  That seems a lot less
> mistake-prone, really.  Concision is not a virtue in the case of
> a command as specialized as this.  Is there really anything wrong with
>
> CREATE OPERATOR CLASS complex_abs_ops
>     DEFAULT FOR TYPE complex USING btree
>     WITH
>         1  ||<,
>         2  ||<=,
>         3  ||=,
>         4  ||>=,
>         5  ||>
>     AND
>         1  complex_abs_cmp(complex, complex);

Not really. Especially when there are ones which are 3, 6, 7, 8, 20
floating around. :-)

> (One could imagine adding system catalogs that give symbolic names
> to the strategy/procnum numbers for each access method, and then
> allowing names instead of integers in this command.  I'm not sure
> whether GiST has sufficiently well-defined strategy numbers to make that
> work, but even if not, I like this better than a positional approach to
> figuring out which operator is which.)

Something like that (having a catalog of what the different operators are
supposed to be) would be nice. Especially for the support procs, so that
CREATE OPERATOR CLASS could make sure you gave the right ones for each
number.

> > I decided to change that to an operator followed by "needs_recheck" to
> > indicate a recheck is needed. "needs_recheck" is not handled as a keyword,
> > but as an IDENT which is examined at parse time.
>
> Ugh.  Make it a keyword.  As long as it can be a TokenId there is no
> downside to doing so, and doing it that way eliminates interesting
> issues about case folding etc.  (Did you know that case folding rules
> are slightly different for keywords and identifiers?)

Ok. Will do. Yes, I know the case folding is different, though I'm not
100% sure how so. I assume it's something like for identifiers, acents &
such get folded to unaccented characters?

> I still like RECHECK better than NEEDS_RECHECK, but that's a minor
> quibble.

RECHECK is one word. I'll go with it.

Take care,

Bill

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 ?
> >>
> >
> > It sure does, but running the script shows that the second insert
> > doesn't try to insert any rows.  There's no entry in the temp table
> > for ~~ because its left and right operands are not the types the
> > SELECT/INTO is looking for.
> >
> > This is evidently a bug in the script.  Oleg?
> >
>
>
> Make me right if I mistake.
>
> 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 ~~.

Tom,

this is interesting question - do we really need commutator to get
postgres to use index. This is the only reason we created  ~~ operator.
Regards,    Oleg
>
>
>
>
Regards,    Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Oleg Bartunov <oleg@sai.msu.su> writes:
> this is interesting question - do we really need commutator to get
> postgres to use index. This is the only reason we created  ~~ operator.

AFAIR there is not a requirement to have a commutator link.  However
the indexable operation has to be framed as "indexedvar OP constant".
If the natural way to write it is as "constant OP indexedvar" then
you won't get an indexscan unless it can be commuted to the other way.
The same issue arises if you think that the operator might be useful
in joins.
        regards, tom lane

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

Bill, do you have a newer version of this patch for application to 7.3?


---------------------------------------------------------------------------

Bill Studenmund wrote:
> I'd like to propose a new command, CREATE OPERATOR CLASS. Its purpose is
> to create a named operator class, so that you can create new types of
> index ops. Also, its inclusion would remove the section of the
> documentation where we tell people how to manually manipulate the system
> tables.
> 
> Since schema support is going to change some of the details of the system
> tables in important ways, I think it's better to move away from manual
> updates.
> 
> The command is basically an instrumentation of the documentation on how to
> add new operator classes.
> 
> Here's the syntax I'd like to propose:
> 
> CREATE OPERATOR CLASS <name> [DEFAULT] FOR TYPE <typename> USING <access
> method> WITH <list of operators> AND <list of support functions>
> 
> New keywords are "CLASS" (SQL99 reserved word) and "REPEATABLE" (SQL99
> non-reserved word, see below for usage).
> 
> <name> is the class's name, and <typename> is the type to be indexed.
> <access method> is the assosciated access method from pg_am (btree, rtree,
> hash, gist).
> 
> The presence of [DEFAULT] indicates that this operator class shold be made
> the default operator class for the type.
> 
> <list of operators> is a comma-delimited list of operator specs. An
> operator spec is either an operator or an operator followed by the keyword
> "REPEATABLE". The presence of "REPEATABLE" indicates that amopreqcheck
> should be set to true for this operator. Each item in this list will
> generate an entry in pg_amop.
> 
> <list of support functions> is a comma-seperated list of functions used to
> assist the index method. Each item in this list will generate an item in
> pg_amproc.
> 
> I agree that I think it is rare that anything will set "REPEATABLE", but
> the point of this effort is to keep folks from mucking around with the
> system tables manually, so we should support making any reasonable entry
> in pg_amop.
> 
> Here's an example based on the programmer's guide. We've created the type
> "complex", and have comparison functions complex_abs_lt, complex_abs_le,
> complex_abs_eq, complex_abs_gt, complex_abs_ge. Then let us have created
> operators "||<", "||<=", "||=", "||>", "||>=" based on them. We also have
> the complex_abs_cmp helper function. To create the operator class, the
> command would be:
> 
> CREATE OPERATOR CLASS complex_abs_ops DEFAULT FOR TYPE complex USING
> btree with ||<, ||<=, ||=, ||>=, ||> and complex_abs_cmp;
> 
> Among other things, complex_abs_ops would be the default operator class
> for the complex type after this command.
> 
> 
> An example using REPEATABLE would be:
> 
> CREATE OPERATOR CLASS complex_abs_ops DEFAULT FOR TYPE complex USING btree
> with ||< REPEATABLE, ||<=, ||=, ||>=, ||> REPEATABLE and complex_abs_cmp;
> 
> Note: I don't think the above command will create a correct operator
> class, it just shows how to add REPEATABLE.
> 
> The alternative to "REPEATABLE" would be something like
> "hit_needs_recheck" after the operator. Suggestions?
> 
> Thoughts?
> 
> Take care,
> 
> Bill
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Bill, is there a patch that is ready for application?

---------------------------------------------------------------------------

Bill Studenmund wrote:
> 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
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026