Skip to content

Commit 4215947

Browse files
tglsfdctglsfdc
committed
Update documentation to reflect the fact that ORDER BY, GROUP BY, etc
are now driven by the default btree opclass, rather than assuming that particular operator names have the needed semantics.
1 parent 1b68704 commit 4215947

File tree

4 files changed

+82
-32
lines changed

4 files changed

+82
-32
lines changed

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

Lines changed: 3 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_operator.sgml,v 1.35 2003/04/22 10:08:08 petere Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_operator.sgml,v 1.36 2003/08/17 22:09:00 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -43,17 +43,11 @@ CREATE OPERATOR <replaceable>name</replaceable> (
4343
The operator name is a sequence of up to <symbol>NAMEDATALEN</>-1
4444
(63 by default) characters from the following list:
4545
<literallayout>
46-
+ - * / &lt; &gt; = ~ ! @ # % ^ &amp; | ` ? $
46+
+ - * / &lt; &gt; = ~ ! @ # % ^ &amp; | ` ?
4747
</literallayout>
4848

4949
There are a few restrictions on your choice of name:
5050
<itemizedlist>
51-
<listitem>
52-
<para>
53-
<literal>$</literal> cannot be defined as a single-character operator,
54-
although it can be part of a multicharacter operator name.
55-
</para>
56-
</listitem>
5751
<listitem>
5852
<para>
5953
<literal>--</literal> and <literal>/*</literal> cannot appear anywhere in an operator name,
@@ -66,7 +60,7 @@ CREATE OPERATOR <replaceable>name</replaceable> (
6660
<literal>-</literal>,
6761
unless the name also contains at least one of these characters:
6862
<literallayout>
69-
~ ! @ # % ^ &amp; | ` ? $
63+
~ ! @ # % ^ &amp; | ` ?
7064
</literallayout>
7165
For example, <literal>@-</literal> is an allowed operator name,
7266
but <literal>*-</literal> is not.

‎doc/src/sgml/ref/select.sgml

Lines changed: 19 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/select.sgml,v 1.67 2003/05/04 02:23:16 petere Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/select.sgml,v 1.68 2003/08/17 22:09:00 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -660,8 +660,11 @@ SELECT name FROM distributors ORDER BY code;
660660
<literal>ORDER BY</> clause. If not specified, <literal>ASC</> is
661661
assumed by default. Alternatively, a specific ordering operator
662662
name may be specified in the <literal>USING</> clause.
663-
<literal>ASC</> is equivalent to <literal>USING &lt;</> and
664-
<literal>DESC</> is equivalent to <literal>USING &gt;</>.
663+
<literal>ASC</> is usually equivalent to <literal>USING &lt;</> and
664+
<literal>DESC</> is usually equivalent to <literal>USING &gt;</>.
665+
(But the creator of a user-defined datatype can define exactly what the
666+
default sort ordering is, and it might correspond to operators with other
667+
names.)
665668
</para>
666669

667670
<para>
@@ -671,7 +674,7 @@ SELECT name FROM distributors ORDER BY code;
671674
</para>
672675

673676
<para>
674-
Data of character types is sorted according to the locale-specific
677+
Character-string data is sorted according to the locale-specific
675678
collation order that was established when the database cluster
676679
was initialized.
677680
</para>
@@ -1003,7 +1006,9 @@ SELECT distributors.* FROM distributors d, distributors distributors;
10031006
that he will actually get. To help detect this sort of mistake,
10041007
PostgreSQL will warn if the implicit-<literal>FROM</literal>
10051008
feature is used in a <command>SELECT</command> statement that also
1006-
contains an explicit <literal>FROM</literal> clause.
1009+
contains an explicit <literal>FROM</literal> clause. Also, it is
1010+
possible to disable the implicit-<literal>FROM</literal> feature
1011+
by setting the <varname>ADD_MISSING_FROM</> parameter to false.
10071012
</para>
10081013
</refsect2>
10091014

@@ -1015,7 +1020,7 @@ SELECT distributors.* FROM distributors d, distributors distributors;
10151020
noise and can be omitted without affecting the meaning. The
10161021
<productname>PostgreSQL</productname> parser requires this key
10171022
word when renaming output columns because the type extensibility
1018-
features lead to parsing ambiguities in this context.
1023+
features lead to parsing ambiguities without it.
10191024
<literal>AS</literal> is optional in <literal>FROM</literal>
10201025
items, however.
10211026
</para>
@@ -1025,7 +1030,7 @@ SELECT distributors.* FROM distributors d, distributors distributors;
10251030
<title>Namespace Available to <literal>GROUP BY</literal> and <literal>ORDER BY</literal></title>
10261031

10271032
<para>
1028-
In the SQL standard, an <literal>ORDER BY</literal> clause may
1033+
In the SQL92 standard, an <literal>ORDER BY</literal> clause may
10291034
only use result column names or numbers, while a <literal>GROUP
10301035
BY</literal> clause may only use expressions based on input column
10311036
names. <productname>PostgreSQL</productname> extends each of
@@ -1036,6 +1041,13 @@ SELECT distributors.* FROM distributors d, distributors distributors;
10361041
expression will always be taken as input-column names, not as
10371042
result-column names.
10381043
</para>
1044+
1045+
<para>
1046+
SQL99 uses a slightly different definition which is not upward compatible
1047+
with SQL92. In most cases, however, <productname>PostgreSQL</productname>
1048+
will interpret an <literal>ORDER BY</literal> or <literal>GROUP
1049+
BY</literal> expression the same way SQL99 does.
1050+
</para>
10391051
</refsect2>
10401052

10411053
<refsect2>

‎doc/src/sgml/xindex.sgml

Lines changed: 59 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/xindex.sgml,v 1.30 2003/04/13 09:57:35 petere Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/xindex.sgml,v 1.31 2003/08/17 22:09:00 tgl Exp $
33
-->
44

55
<sect1 id="xindex">
@@ -452,7 +452,7 @@ CREATE OPERATOR = (
452452
</programlisting>
453453
It is important to specify the restriction and join selectivity
454454
functions, otherwise the optimizer will be unable to make effective
455-
use of the index. Note that there less-than, equal, and
455+
use of the index. Note that the less-than, equal, and
456456
greater-than cases should use different selectivity functions.
457457
</para>
458458

@@ -551,13 +551,68 @@ CREATE OPERATOR CLASS complex_abs_ops
551551
</para>
552552
</sect2>
553553

554+
<sect2 id="xindex-opclass-dependencies">
555+
<title>System Dependencies on Operator Classes</title>
556+
557+
<indexterm>
558+
<primary>ordering operator</primary>
559+
</indexterm>
560+
561+
<para>
562+
<productname>PostgreSQL</productname> uses operator classes to infer the
563+
properties of operators in more ways than just whether they can be used
564+
with indexes. Therefore, you might want to create operator classes
565+
even if you have no intention of indexing any columns of your datatype.
566+
</para>
567+
568+
<para>
569+
In particular, there are SQL features such as <literal>ORDER BY</> and
570+
<literal>DISTINCT</> that require comparison and sorting of values.
571+
To implement these features on a user-defined datatype,
572+
<productname>PostgreSQL</productname> looks for the default B-tree operator
573+
class for the datatype. The <quote>equals</> member of this operator
574+
class defines the system's notion of equality of values for
575+
<literal>GROUP BY</> and <literal>DISTINCT</>, and the sort ordering
576+
imposed by the operator class defines the default <literal>ORDER BY</>
577+
ordering.
578+
</para>
579+
580+
<para>
581+
Comparison of arrays of user-defined types also relies on the semantics
582+
defined by the default B-tree operator class.
583+
</para>
584+
585+
<para>
586+
If there is no default B-tree operator class for a datatype, the system
587+
will look for a default hash operator class. But since that kind of
588+
operator class only provides equality, in practice it is only enough
589+
to support array equality.
590+
</para>
591+
592+
<para>
593+
When there is no default operator class for a datatype, you will get
594+
errors like <quote>could not identify an ordering operator</> if you
595+
try to use these SQL features with the datatype.
596+
</para>
597+
598+
<note>
599+
<para>
600+
In <ProductName>PostgreSQL</ProductName> versions before 7.4,
601+
sorting and grouping operations would implicitly use operators named
602+
<literal>=</>, <literal>&lt;</>, and <literal>&gt;</>. The new
603+
behavior of relying on default operator classes avoids having to make
604+
any assumption about the behavior of operators with particular names.
605+
</para>
606+
</note>
607+
</sect2>
608+
554609
<sect2 id="xindex-opclass-features">
555610
<title>Special Features of Operator Classes</title>
556611

557612
<para>
558613
There are two special features of operator classes that we have
559-
not discussed yet, mainly because they are not very useful
560-
with the default B-tree index method.
614+
not discussed yet, mainly because they are not useful
615+
with the most commonly used index methods.
561616
</para>
562617

563618
<para>

‎doc/src/sgml/xoper.sgml

Lines changed: 1 addition & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/xoper.sgml,v 1.24 2003/06/22 22:04:54 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/xoper.sgml,v 1.25 2003/08/17 22:09:00 tgl Exp $
33
-->
44

55
<sect1 id="xoper">
@@ -471,17 +471,6 @@ table1.column1 OP table2.column2
471471
</para>
472472
</note>
473473

474-
<note>
475-
<para>
476-
<literal>GROUP BY</> and <literal>DISTINCT</> operations require each
477-
datatype being grouped or compared to have a mergejoinable
478-
equality operator named <literal>=</>. The equality operator and its
479-
associated <literal>SORT1</> operator are used to implement these
480-
operations. Also, the associated <literal>SORT1</> operator is the
481-
default ordering operator for <literal>ORDER BY</>.
482-
</para>
483-
</note>
484-
485474
<note>
486475
<para>
487476
In <ProductName>PostgreSQL</ProductName> versions before 7.3,

0 commit comments

Comments
 (0)