On Sat, Nov 14, 2009 at 2:27 PM, Jeff Davis <pg...@j-davis.com> wrote:
> New patches attached.
Forgive me if this is discussed before, but why does this store the
strategy numbers of the relevant operators instead of the operators
themselves? It seems like this could lead to surprising behavior if
the user modifies the definition of the operator class.
I'm wondering if we can't use the existing
BuildIndexValueDescription() rather than the new function
tuple_as_string(). I realize there are two tuples, but maybe it makes
sense to just call it twice?
I'm attaching a revised doc patch for your consideration.
...Robert
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 082dfe4..a73c015 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -51,10 +51,13 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PAR
PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> |
CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) |
FOREIGN KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> [, ... ] ) ]
- [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }
+ [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] |
+ EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ]
+ ( { <replaceable class="parameter">column</replaceable> | <replaceable class="parameter">column</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] WITH <replaceable class="parameter">operator</replaceable> [, ... ] )
+ <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
-<phrase><replaceable class="PARAMETER">index_parameters</replaceable> in <literal>UNIQUE</literal> and <literal>PRIMARY KEY</literal> constraints are:</phrase>
+<phrase><replaceable class="PARAMETER">index_parameters</replaceable> in <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and <literal>EXCLUDE</literal> constraints are:</phrase>
[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) ]
[ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ]
@@ -547,6 +550,43 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PAR
</varlistentry>
<varlistentry>
+ <term><literal>EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ]
+ ( {<replaceable class="parameter">column</replaceable> | <replaceable class="parameter">column</replaceable> | (<replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] WITH <replaceable class="parameter">operator</replaceable> [, ... ] )
+ <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] }</literal></term>
+ <listitem>
+ <para>
+ The <literal>EXCLUDE</> clause specifies an operator exclusion
+ constraint. An operator exclusion constraint guarantees that if any two
+ tuples are compared on the specified columns or expressions using the
+ specified operators, at least one such comparison will return false.
+ If all of the specified operators test for equality, it is equivalent
+ to a UNIQUE constraint, although an ordinary unique constraint will
+ normally be faster. However, operator exclusion constraints can use
+ index methods other than btree, and can specify more general constraints.
+ For instance, you can specify the constraint that no two tuples in the
+ table contain overlapping circles
+ (see <xref linkend="datatype-geometric">) by using the
+ <literal>&&</literal> operator.
+ </para>
+
+ <para>
+ Operator exclusion constraints are implemented internally using
+ an index, so the specified operators must be associated with an
+ appropriate operator class for the given access method, and the
+ access method must support amgettuple (see <xref linkend="indexam">
+ for details). The operators are also required to be their own
+ commutators (see <xref linkend="sql-createoperator">).
+ </para>
+
+ <para>
+ The <replaceable class="parameter">predicate</replaceable>
+ allows you to specify a constraint on a subset of the table,
+ internally using a partial index.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>DEFERRABLE</literal></term>
<term><literal>NOT DEFERRABLE</literal></term>
<listitem>
@@ -1111,6 +1151,18 @@ CREATE TABLE cinemas (
</programlisting>
</para>
+ <para>
+ Create table <structname>circles</> with an operator exclusion
+ constraint that prevents overlapping circles within it:
+
+<programlisting>
+CREATE TABLE circles (
+ c circle,
+ EXCLUDE USING gist (c WITH &&)
+);
+</programlisting>
+ </para>
+
</refsect1>
<refsect1 id="SQL-CREATETABLE-compatibility">
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
