Skip to content

Commit 2b5154b

Browse files
tglsfdctglsfdc
committed
Extend ALTER OPERATOR to allow setting more optimization attributes.
Allow the COMMUTATOR, NEGATOR, MERGES, and HASHES attributes to be set by ALTER OPERATOR. However, we don't allow COMMUTATOR/NEGATOR to be changed once set, nor allow the MERGES/HASHES flags to be unset once set. Changes like that might invalidate plans already made, and dealing with the consequences seems like more trouble than it's worth. The main use-case we foresee for this is to allow addition of missed properties in extension update scripts, such as extending an existing operator to support hashing. So only transitions from not-set to set states seem very useful. This patch also causes us to reject some incorrect cases that formerly resulted in inconsistent catalog state, such as trying to set the commutator of an operator to be some other operator that already has a (different) commutator. While at it, move the InvokeObjectPostCreateHook call for CREATE OPERATOR to not occur until after we've fixed up commutator or negator links as needed. The previous ordering could only be justified by thinking of the OperatorUpd call as a kind of ALTER OPERATOR step; but we don't call InvokeObjectPostAlterHook therein. It seems better to let the hook see the final state of the operator object. In the documentation, move the discussion of how to establish commutator pairs from xoper.sgml to the CREATE OPERATOR ref page. Tommy Pavlicek, reviewed and editorialized a bit by me Discussion: https://postgr.es/m/CAEhP-W-vGVzf4udhR5M8Bdv88UYnPrhoSkj3ieR3QNrsGQoqdg@mail.gmail.com
1 parent dcd4454 commit 2b5154b

File tree

13 files changed

+869
-205
lines changed

13 files changed

+869
-205
lines changed

‎doc/src/sgml/ref/alter_operator.sgml

Lines changed: 81 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -30,7 +30,11 @@ ALTER OPERATOR <replaceable>name</replaceable> ( { <replaceable>left_type</repla
3030
ALTER OPERATOR <replaceable>name</replaceable> ( { <replaceable>left_type</replaceable> | NONE } , <replaceable>right_type</replaceable> )
3131
SET ( { RESTRICT = { <replaceable class="parameter">res_proc</replaceable> | NONE }
3232
| JOIN = { <replaceable class="parameter">join_proc</replaceable> | NONE }
33-
} [, ... ] )
33+
| COMMUTATOR = <replaceable class="parameter">com_op</replaceable>
34+
| NEGATOR = <replaceable class="parameter">neg_op</replaceable>
35+
| HASHES
36+
| MERGES
37+
} [, ... ] )
3438
</synopsis>
3539
</refsynopsisdiv>
3640

@@ -121,23 +125,95 @@ ALTER OPERATOR <replaceable>name</replaceable> ( { <replaceable>left_type</repla
121125
</listitem>
122126
</varlistentry>
123127

128+
<varlistentry>
129+
<term><replaceable class="parameter">com_op</replaceable></term>
130+
<listitem>
131+
<para>
132+
The commutator of this operator. Can only be changed if the operator
133+
does not have an existing commutator.
134+
</para>
135+
</listitem>
136+
</varlistentry>
137+
138+
<varlistentry>
139+
<term><replaceable class="parameter">neg_op</replaceable></term>
140+
<listitem>
141+
<para>
142+
The negator of this operator. Can only be changed if the operator does
143+
not have an existing negator.
144+
</para>
145+
</listitem>
146+
</varlistentry>
147+
148+
<varlistentry>
149+
<term><literal>HASHES</literal></term>
150+
<listitem>
151+
<para>
152+
Indicates this operator can support a hash join. Can only be enabled and
153+
not disabled.
154+
</para>
155+
</listitem>
156+
</varlistentry>
157+
158+
<varlistentry>
159+
<term><literal>MERGES</literal></term>
160+
<listitem>
161+
<para>
162+
Indicates this operator can support a merge join. Can only be enabled
163+
and not disabled.
164+
</para>
165+
</listitem>
166+
</varlistentry>
167+
124168
</variablelist>
125169
</refsect1>
126170

171+
<refsect1>
172+
<title>Notes</title>
173+
174+
<para>
175+
Refer to <xref linkend="xoper"/> and
176+
<xref linkend="xoper-optimization"/> for further information.
177+
</para>
178+
179+
<para>
180+
Since commutators come in pairs that are commutators of each other,
181+
<literal>ALTER OPERATOR SET COMMUTATOR</literal> will also set the
182+
commutator of the <replaceable class="parameter">com_op</replaceable>
183+
to be the target operator. Likewise, <literal>ALTER OPERATOR SET
184+
NEGATOR</literal> will also set the negator of
185+
the <replaceable class="parameter">neg_op</replaceable> to be the
186+
target operator. Therefore, you must own the commutator or negator
187+
operator as well as the target operator.
188+
</para>
189+
</refsect1>
190+
127191
<refsect1>
128192
<title>Examples</title>
129193

130194
<para>
131195
Change the owner of a custom operator <literal>a @@ b</literal> for type <type>text</type>:
132196
<programlisting>
133197
ALTER OPERATOR @@ (text, text) OWNER TO joe;
134-
</programlisting></para>
198+
</programlisting>
199+
</para>
135200

136201
<para>
137-
Change the restriction and join selectivity estimator functions of a custom operator <literal>a &amp;&amp; b</literal> for type <type>int[]</type>:
202+
Change the restriction and join selectivity estimator functions of a
203+
custom operator <literal>a &amp;&amp; b</literal> for
204+
type <type>int[]</type>:
138205
<programlisting>
139-
ALTER OPERATOR &amp;&amp; (_int4, _int4) SET (RESTRICT = _int_contsel, JOIN = _int_contjoinsel);
140-
</programlisting></para>
206+
ALTER OPERATOR &amp;&amp; (int[], int[]) SET (RESTRICT = _int_contsel, JOIN = _int_contjoinsel);
207+
</programlisting>
208+
</para>
209+
210+
<para>
211+
Mark the <literal>&amp;&amp;</literal> operator as being its own
212+
commutator:
213+
<programlisting>
214+
ALTER OPERATOR &amp;&amp; (int[], int[]) SET (COMMUTATOR = &amp;&amp;);
215+
</programlisting>
216+
</para>
141217

142218
</refsect1>
143219

‎doc/src/sgml/ref/create_operator.sgml

Lines changed: 63 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -104,15 +104,15 @@ CREATE OPERATOR <replaceable>name</replaceable> (
104104
</para>
105105

106106
<para>
107-
The other clauses specify optional operator optimization clauses.
107+
The other clauses specify optional operator optimization attributes.
108108
Their meaning is detailed in <xref linkend="xoper-optimization"/>.
109109
</para>
110110

111111
<para>
112112
To be able to create an operator, you must have <literal>USAGE</literal>
113113
privilege on the argument types and the return type, as well
114114
as <literal>EXECUTE</literal> privilege on the underlying function. If a
115-
commutator or negator operator is specified, you must own these operators.
115+
commutator or negator operator is specified, you must own those operators.
116116
</para>
117117
</refsect1>
118118

@@ -231,7 +231,67 @@ COMMUTATOR = OPERATOR(myschema.===) ,
231231
<title>Notes</title>
232232

233233
<para>
234-
Refer to <xref linkend="xoper"/> for further information.
234+
Refer to <xref linkend="xoper"/> and <xref linkend="xoper-optimization"/>
235+
for further information.
236+
</para>
237+
238+
<para>
239+
When you are defining a self-commutative operator, you just do it.
240+
When you are defining a pair of commutative operators, things are
241+
a little trickier: how can the first one to be defined refer to the
242+
other one, which you haven't defined yet? There are three solutions
243+
to this problem:
244+
245+
<itemizedlist>
246+
<listitem>
247+
<para>
248+
One way is to omit the <literal>COMMUTATOR</literal> clause in the
249+
first operator that you define, and then provide one in the second
250+
operator's definition. Since <productname>PostgreSQL</productname>
251+
knows that commutative operators come in pairs, when it sees the
252+
second definition it will automatically go back and fill in the
253+
missing <literal>COMMUTATOR</literal> clause in the first
254+
definition.
255+
</para>
256+
</listitem>
257+
258+
<listitem>
259+
<para>
260+
Another, more straightforward way is just to
261+
include <literal>COMMUTATOR</literal> clauses in both definitions.
262+
When <productname>PostgreSQL</productname> processes the first
263+
definition and realizes that <literal>COMMUTATOR</literal> refers to
264+
a nonexistent operator, the system will make a dummy entry for that
265+
operator in the system catalog. This dummy entry will have valid
266+
data only for the operator name, left and right operand types, and
267+
owner, since that's all that <productname>PostgreSQL</productname>
268+
can deduce at this point. The first operator's catalog entry will
269+
link to this dummy entry. Later, when you define the second
270+
operator, the system updates the dummy entry with the additional
271+
information from the second definition. If you try to use the dummy
272+
operator before it's been filled in, you'll just get an error
273+
message.
274+
</para>
275+
</listitem>
276+
277+
<listitem>
278+
<para>
279+
Alternatively, both operators can be defined
280+
without <literal>COMMUTATOR</literal> clauses
281+
and then <command>ALTER OPERATOR</command> can be used to set their
282+
commutator links. It's sufficient to <command>ALTER</command>
283+
either one of the pair.
284+
</para>
285+
</listitem>
286+
</itemizedlist>
287+
288+
In all three cases, you must own both operators in order to mark
289+
them as commutators.
290+
</para>
291+
292+
<para>
293+
Pairs of negator operators can be defined using the same methods
294+
as for commutator pairs.
235295
</para>
236296

237297
<para>

‎doc/src/sgml/xoper.sgml

Lines changed: 0 additions & 44 deletions
Original file line numberDiff line numberDiff line change
@@ -146,44 +146,6 @@ SELECT (a + b) AS c FROM test_complex;
146146
<literal>=</literal> operator must specify that it is valid, by marking the
147147
operator with commutator information.
148148
</para>
149-
150-
<para>
151-
When you are defining a self-commutative operator, you just do it.
152-
When you are defining a pair of commutative operators, things are
153-
a little trickier: how can the first one to be defined refer to the
154-
other one, which you haven't defined yet? There are two solutions
155-
to this problem:
156-
157-
<itemizedlist>
158-
<listitem>
159-
<para>
160-
One way is to omit the <literal>COMMUTATOR</literal> clause in the first operator that
161-
you define, and then provide one in the second operator's definition.
162-
Since <productname>PostgreSQL</productname> knows that commutative
163-
operators come in pairs, when it sees the second definition it will
164-
automatically go back and fill in the missing <literal>COMMUTATOR</literal> clause in
165-
the first definition.
166-
</para>
167-
</listitem>
168-
169-
<listitem>
170-
<para>
171-
The other, more straightforward way is just to include <literal>COMMUTATOR</literal> clauses
172-
in both definitions. When <productname>PostgreSQL</productname> processes
173-
the first definition and realizes that <literal>COMMUTATOR</literal> refers to a nonexistent
174-
operator, the system will make a dummy entry for that operator in the
175-
system catalog. This dummy entry will have valid data only
176-
for the operator name, left and right operand types, and result type,
177-
since that's all that <productname>PostgreSQL</productname> can deduce
178-
at this point. The first operator's catalog entry will link to this
179-
dummy entry. Later, when you define the second operator, the system
180-
updates the dummy entry with the additional information from the second
181-
definition. If you try to use the dummy operator before it's been filled
182-
in, you'll just get an error message.
183-
</para>
184-
</listitem>
185-
</itemizedlist>
186-
</para>
187149
</sect2>
188150

189151
<sect2 id="xoper-negator">
@@ -217,12 +179,6 @@ SELECT (a + b) AS c FROM test_complex;
217179
<literal>x &lt;&gt; y</literal>. This comes up more often than you might think, because
218180
<literal>NOT</literal> operations can be inserted as a consequence of other rearrangements.
219181
</para>
220-
221-
<para>
222-
Pairs of negator operators can be defined using the same methods
223-
explained above for commutator pairs.
224-
</para>
225-
226182
</sect2>
227183

228184
<sect2 id="xoper-restrict">

0 commit comments

Comments
 (0)