Postgres Pro
Downloads
Home   >   mailing lists

Thread: Is the COMMUTATOR clause required for self commutative operators?

Is the COMMUTATOR clause required for self commutative operators?

From
Date:
In the command

CREATE OPERATOR name (
    PROCEDURE = func_name
    [, LEFTARG = lefttype ] [, RIGHTARG = righttype ]
    [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]
    [, RESTRICT = res_proc ] [, JOIN = join_proc ]
    [, HASHES ] [, MERGES ]
    [, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ]
    [, LTCMP = less_than_op ] [, GTCMP = greater_than_op ]
)


Is omitting the COMMUTATOR option a problem at all in case of
self-commutative operators? That is, if I define

CREATE OPERATOR public.+(
  PROCEDURE = numeric_add_null,
  LEFTARG = numeric,
  RIGHTARG = numeric,
  COMMUTATOR = +)


(where numeric_add_null is a function that handles in NULLs in a
non-standard, but more useful way), since "+" is self commutative, is it
necessary to include it here? Any advantage or disadvantage to ommitting
or including it?

~Berend Tober

Re: Is the COMMUTATOR clause required for self commutative operators?

From
Peter Eisentraut
Date:
btober@seaworthysys.com wrote:
> In the command
>
> CREATE OPERATOR name (
>     PROCEDURE = func_name
>     [, LEFTARG = lefttype ] [, RIGHTARG = righttype ]
>     [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]
>     [, RESTRICT = res_proc ] [, JOIN = join_proc ]
>     [, HASHES ] [, MERGES ]
>     [, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ]
>     [, LTCMP = less_than_op ] [, GTCMP = greater_than_op ]
> )
>
>
> Is omitting the COMMUTATOR option a problem at all in case of
> self-commutative operators?

It's an optimization clause, so if you omit it then you prevent certain
optimizations.  See
http://www.postgresql.org/docs/current/static/xoper-optimization.html#AEN29826
for details.

Re: Is the COMMUTATOR clause required for self commutative operators?

From
Tom Lane
Date:
<btober@seaworthysys.com> writes:
> Is omitting the COMMUTATOR option a problem at all in case of
> self-commutative operators?

At the moment it only really matters for operators that are used in
top-level WHERE clauses --- which lets out anything that doesn't return
boolean.  But I'd say that as a matter of style, if the operator is
self-commutative you should mark it so.  Some day the system might
pay attention.

            regards, tom lane