|The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 478 pages
RRP £14.95 ($19.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
COMMUTATOR clause, if provided, names an operator that is the
commutator of the operator being defined. We say that operator A is the
commutator of operator B if (x A y) equals (y B x) for all possible input
values x, y. Notice that B is also the commutator of A. For example,
> for a particular data type are usually each others'
commutators, and operator
+ is usually commutative with itself.
- is usually not commutative with anything.
The left operand type of a commutable operator is the same as the
right operand type of its commutator, and vice versa. So the name of
the commutator operator is all that PostgreSQL
needs to be given to look up the commutator, and that's all that needs to
be provided in the
It's critical to provide commutator information for operators that
will be used in indexes and join clauses, because this allows the
query optimizer to “flip around” such a clause to the forms
needed for different plan types. For example, consider a query with
a WHERE clause like
tab1.x = tab2.y, where
tab2.y are of a user-defined type, and suppose that
tab2.y is indexed. The optimizer cannot generate an
index scan unless it can determine how to flip the clause around to
tab2.y = tab1.x, because the index-scan machinery expects
to see the indexed column on the left of the operator it is given.
PostgreSQL will not simply
assume that this is a valid transformation--the creator of the
= operator must specify that it is valid, by marking the
operator with commutator information.
When you are defining a self-commutative operator, you just do it. When you are defining a pair of commutative operators, things are a little trickier: how can the first one to be defined refer to the other one, which you haven't defined yet? There are two solutions to this problem:
One way is to omit the
COMMUTATORclause in the first operator that you define, and then provide one in the second operator's definition. Since PostgreSQL knows that commutative operators come in pairs, when it sees the second definition it will automatically go back and fill in the missing
COMMUTATORclause in the first definition.
The other, more straightforward way is just to include
COMMUTATORclauses in both definitions. When PostgreSQL processes the first definition and realizes that
COMMUTATORrefers to a nonexistent operator, the system will make a dummy entry for that operator in the system catalog. This dummy entry will have valid data only for the operator name, left and right operand types, and result type, since that's all that PostgreSQL can deduce at this point. The first operator's catalog entry will link to this dummy entry. Later, when you define the second operator, the system updates the dummy entry with the additional information from the second definition. If you try to use the dummy operator before it's been filled in, you'll just get an error message.
|ISBN 9781906966065||The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide||See the print edition|