diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/catalogs.sgml | 71 | ||||
| -rw-r--r-- | doc/src/sgml/errcodes.sgml | 8 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_table.sgml | 132 |
3 files changed, 168 insertions, 43 deletions
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index f2959af5260..be5b037aa0b 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.211 2009/11/20 20:38:09 tgl Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.212 2009/12/07 05:22:21 tgl Exp $ --> <!-- Documentation of the system catalogs, directed toward PostgreSQL developers --> @@ -1536,11 +1536,7 @@ <entry><type>bool</type></entry> <entry></entry> <entry> - True if this is a table and it has (or recently had) any - indexes. This is set by <command>CREATE INDEX</command>, but - not cleared immediately by <command>DROP INDEX</command>. - <command>VACUUM</command> clears <structfield>relhasindex</> if it finds the - table has no indexes + True if this is a table and it has (or recently had) any indexes </entry> </row> @@ -1618,6 +1614,17 @@ </row> <row> + <entry><structfield>relhasexclusion</structfield></entry> + <entry><type>bool</type></entry> + <entry></entry> + <entry> + For a table, true if the table has (or once had) any exclusion + constraints; for an index, true if the index supports an exclusion + constraint + </entry> + </row> + + <row> <entry><structfield>relhasrules</structfield></entry> <entry><type>bool</type></entry> <entry></entry> @@ -1680,6 +1687,17 @@ </tbody> </tgroup> </table> + + <para> + Several of the boolean flags in <structname>pg_class</> are maintained + lazily: they are guaranteed to be true if that's the correct state, but + may not be reset to false immediately when the condition is no longer + true. For example, <structfield>relhasindex</> is set by + <command>CREATE INDEX</command>, but it is never cleared by + <command>DROP INDEX</command>. Instead, <command>VACUUM</command> clears + <structfield>relhasindex</> if it finds the table has no indexes. This + arrangement avoids race conditions and improves concurrency. + </para> </sect1> <sect1 id="catalog-pg-constraint"> @@ -1690,11 +1708,12 @@ </indexterm> <para> - The catalog <structname>pg_constraint</structname> stores check, primary key, unique, and foreign - key constraints on tables. (Column constraints are not treated - specially. Every column constraint is equivalent to some table - constraint.) Not-null constraints are represented in the - <structname>pg_attribute</> catalog. + The catalog <structname>pg_constraint</structname> stores check, primary + key, unique, foreign key, and exclusion constraints on tables. + (Column constraints are not treated specially. Every column constraint is + equivalent to some table constraint.) + Not-null constraints are represented in the <structname>pg_attribute</> + catalog, not here. </para> <para> @@ -1739,7 +1758,8 @@ <literal>c</> = check constraint, <literal>f</> = foreign key constraint, <literal>p</> = primary key constraint, - <literal>u</> = unique constraint + <literal>u</> = unique constraint, + <literal>x</> = exclusion constraint </entry> </row> @@ -1776,7 +1796,7 @@ <entry><type>oid</type></entry> <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry> <entry>The index supporting this constraint, if it's a unique, primary - key, or foreign key constraint; else 0</entry> + key, foreign key, or exclusion constraint; else 0</entry> </row> <row> @@ -1828,7 +1848,7 @@ <entry><type>bool</type></entry> <entry></entry> <entry> - This constraint is defined locally in the relation. Note that a + This constraint is defined locally for the relation. Note that a constraint can be locally defined and inherited simultaneously </entry> </row> @@ -1838,7 +1858,8 @@ <entry><type>int4</type></entry> <entry></entry> <entry> - The number of direct ancestors this constraint has. A constraint with + The number of direct inheritance ancestors this constraint has. + A constraint with a nonzero number of ancestors cannot be dropped nor renamed </entry> </row> @@ -1879,6 +1900,13 @@ </row> <row> + <entry><structfield>conexclop</structfield></entry> + <entry><type>oid[]</type></entry> + <entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</></entry> + <entry>If an exclusion constraint, list of the per-column exclusion operators</entry> + </row> + + <row> <entry><structfield>conbin</structfield></entry> <entry><type>text</type></entry> <entry></entry> @@ -1895,6 +1923,16 @@ </tgroup> </table> + <para> + In the case of an exclusion constraint, <structfield>conkey</structfield> + is only useful for constraint elements that are simple column references. + For other cases, a zero appears in <structfield>conkey</structfield> + and the associated index must be consulted to discover the expression + that is constrained. (<structfield>conkey</structfield> thus has the + same contents as <structname>pg_index</>.<structfield>indkey</> for the + index.) + </para> + <note> <para> <structfield>consrc</structfield> is not updated when referenced objects @@ -1908,7 +1946,8 @@ <para> <literal>pg_class.relchecks</literal> needs to agree with the number of check-constraint entries found in this table for each - relation. + relation. Also, <literal>pg_class.relhasexclusion</literal> must + be true if there are any exclusion-constraint entries for the relation. </para> </note> diff --git a/doc/src/sgml/errcodes.sgml b/doc/src/sgml/errcodes.sgml index e5597f20043..5819004f48a 100644 --- a/doc/src/sgml/errcodes.sgml +++ b/doc/src/sgml/errcodes.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/errcodes.sgml,v 1.27 2009/03/04 10:55:00 petere Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/errcodes.sgml,v 1.28 2009/12/07 05:22:21 tgl Exp $ --> <appendix id="errcodes-appendix"> <title><productname>PostgreSQL</productname> Error Codes</title> @@ -640,6 +640,12 @@ <entry>check_violation</entry> </row> +<row> +<entry><literal>23P01</literal></entry> +<entry>EXCLUSION VIOLATION</entry> +<entry>exclusion_violation</entry> +</row> + <row> <entry spanname="span13"><emphasis role="bold">Class 24 — Invalid Cursor State</></entry> diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 8e4b1bbc312..e315843187c 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.119 2009/10/27 13:58:28 alvherre Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.120 2009/12/07 05:22:21 tgl Exp $ PostgreSQL documentation --> @@ -24,7 +24,7 @@ PostgreSQL documentation CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PARAMETER">table_name</replaceable> ( [ { <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [ DEFAULT <replaceable>default_expr</replaceable> ] [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ] | <replaceable>table_constraint</replaceable> - | LIKE <replaceable>parent_table</replaceable> [ { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL } ] ... } + | LIKE <replaceable>parent_table</replaceable> [ <replaceable>like_option</replaceable> ... ] } [, ... ] ] ) [ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ] @@ -37,9 +37,9 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PAR [ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ] { NOT NULL | NULL | + CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) | UNIQUE <replaceable class="PARAMETER">index_parameters</replaceable> | PRIMARY KEY <replaceable class="PARAMETER">index_parameters</replaceable> | - CHECK ( <replaceable class="PARAMETER">expression</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> ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] @@ -47,17 +47,26 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PAR <phrase>and <replaceable class="PARAMETER">table_constraint</replaceable> is:</phrase> [ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ] -{ UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> | +{ CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) | + UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> | PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> | - CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) | + EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</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> ] } [ 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>and <replaceable class="PARAMETER">like_option</replaceable> is:</phrase> + +{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL } + +<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> ] + +<phrase><replaceable class="PARAMETER">exclude_element</replaceable> in an <literal>EXCLUDE</literal> constraint is:</phrase> + +{ <replaceable class="parameter">column</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] </synopsis> </refsynopsisdiv> @@ -251,7 +260,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PAR </varlistentry> <varlistentry> - <term><literal>LIKE <replaceable>parent_table</replaceable> [ { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL } ]</literal></term> + <term><literal>LIKE <replaceable>parent_table</replaceable> [ <replaceable>like_option</replaceable> ... ]</literal></term> <listitem> <para> The <literal>LIKE</literal> clause specifies a table from which @@ -351,6 +360,29 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PAR </varlistentry> <varlistentry> + <term><literal>CHECK ( <replaceable class="PARAMETER">expression</replaceable> )</literal></term> + <listitem> + <para> + The <literal>CHECK</> clause specifies an expression producing a + Boolean result which new or updated rows must satisfy for an + insert or update operation to succeed. Expressions evaluating + to TRUE or UNKNOWN succeed. Should any row of an insert or + update operation produce a FALSE result an error exception is + raised and the insert or update does not alter the database. A + check constraint specified as a column constraint should + reference that column's value only, while an expression + appearing in a table constraint can reference multiple columns. + </para> + + <para> + Currently, <literal>CHECK</literal> expressions cannot contain + subqueries nor refer to variables other than columns of the + current row. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><literal>UNIQUE</> (column constraint)</term> <term><literal>UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )</> (table constraint)</term> @@ -406,29 +438,54 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PAR </varlistentry> <varlistentry> - <term><literal>CHECK ( <replaceable class="PARAMETER">expression</replaceable> )</literal></term> + <term><literal>EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ]</literal></term> <listitem> <para> - The <literal>CHECK</> clause specifies an expression producing a - Boolean result which new or updated rows must satisfy for an - insert or update operation to succeed. Expressions evaluating - to TRUE or UNKNOWN succeed. Should any row of an insert or - update operation produce a FALSE result an error exception is - raised and the insert or update does not alter the database. A - check constraint specified as a column constraint should - reference that column's value only, while an expression - appearing in a table constraint can reference multiple columns. + The <literal>EXCLUDE</> clause defines an exclusion + constraint, which guarantees that if + any two rows are compared on the specified column(s) or + expression(s) using the specified operator(s), not all of these + comparisons will return <literal>TRUE</>. If all of the + specified operators test for equality, this is equivalent to a + <literal>UNIQUE</> constraint, although an ordinary unique constraint + will be faster. However, exclusion constraints can specify + constraints that are more general than simple equality. + For example, you can specify a constraint that + no two rows in the table contain overlapping circles + (see <xref linkend="datatype-geometric">) by using the + <literal>&&</> operator. </para> <para> - Currently, <literal>CHECK</literal> expressions cannot contain - subqueries nor refer to variables other than columns of the - current row. + Exclusion constraints are implemented using + an index, so each specified operator must be associated with an + appropriate operator class + (see <xref linkend="indexes-opclass">) for the index access + method <replaceable>index_method</>. + The operators are required to be commutative. + Each <replaceable class="parameter">exclude_element</replaceable> + can optionally specify an operator class and/or ordering options; + these are described fully under + <xref linkend="sql-createindex" endterm="sql-createindex-title">. + </para> + + <para> + The access method must support <literal>amgettuple</> (see <xref + linkend="indexam">); at present this means <acronym>GIN</> + cannot be used. Although it's allowed, there is little point in using + btree or hash indexes with an exclusion constraint, because this + does nothing that an ordinary unique constraint doesn't do better. + So in practice the access method will always be <acronym>GiST</>. + </para> + + <para> + The <replaceable class="parameter">predicate</> allows you to specify an + exclusion constraint on a subset of the table; internally this creates a + partial index. Note that parentheses are required around the predicate. </para> </listitem> </varlistentry> - <varlistentry> <term><literal>REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH <replaceable class="parameter">matchtype</replaceable> ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ]</literal> (column constraint)</term> @@ -557,7 +614,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PAR deferrable can be postponed until the end of the transaction (using the <xref linkend="sql-set-constraints" endterm="sql-set-constraints-title"> command). <literal>NOT DEFERRABLE</literal> is the default. - Currently, only <literal>UNIQUE</>, <literal>PRIMARY KEY</>, and + Currently, only <literal>UNIQUE</>, <literal>PRIMARY KEY</>, + <literal>EXCLUDE</>, and <literal>REFERENCES</> (foreign key) constraints accept this clause. <literal>NOT NULL</> and <literal>CHECK</> constraints are not deferrable. @@ -695,8 +753,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PAR <listitem> <para> This clause allows selection of the tablespace in which the index - associated with a <literal>UNIQUE</literal> or <literal>PRIMARY - KEY</literal> constraint will be created. + associated with a <literal>UNIQUE</literal>, <literal>PRIMARY + KEY</literal>, or <literal>EXCLUDE</> constraint will be created. If not specified, <xref linkend="guc-default-tablespace"> is consulted, or <xref linkend="guc-temp-tablespaces"> if the table is temporary. @@ -715,8 +773,9 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PAR <para> The <literal>WITH</> clause can specify <firstterm>storage parameters</> - for tables, and for indexes associated with a <literal>UNIQUE</literal> or - <literal>PRIMARY KEY</literal> constraint. Storage parameters for + for tables, and for indexes associated with a <literal>UNIQUE</literal>, + <literal>PRIMARY KEY</literal>, or <literal>EXCLUDE</> constraint. + Storage parameters for indexes are documented in <xref linkend="SQL-CREATEINDEX" endterm="sql-createindex-title">. The storage parameters currently available for tables are listed below. For each parameter, unless noted, @@ -1100,6 +1159,18 @@ WITH (fillfactor=70); </para> <para> + Create table <structname>circles</> with an exclusion + constraint that prevents any two circles from overlapping: + +<programlisting> +CREATE TABLE circles ( + c circle, + EXCLUDE USING gist (c WITH &&) +); +</programlisting> + </para> + + <para> Create table <structname>cinemas</> in tablespace <structname>diskvol1</>: <programlisting> @@ -1195,6 +1266,15 @@ CREATE TABLE cinemas ( </refsect2> <refsect2> + <title><literal>EXCLUDE</literal> Constraint</title> + + <para> + The <literal>EXCLUDE</> constraint type is a + <productname>PostgreSQL</productname> extension. + </para> + </refsect2> + + <refsect2> <title><literal>NULL</literal> <quote>Constraint</quote></title> <para> |