From 0cb65564e5f855b1e9aa145fd645352130f74646 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 7 Dec 2009 05:22:23 +0000 Subject: Add exclusion constraints, which generalize the concept of uniqueness to support any indexable commutative operator, not just equality. Two rows violate the exclusion constraint if "row1.col OP row2.col" is TRUE for each of the columns in the constraint. Jeff Davis, reviewed by Robert Haas --- doc/src/sgml/catalogs.sgml | 71 +++++++++++++++----- doc/src/sgml/errcodes.sgml | 8 ++- doc/src/sgml/ref/create_table.sgml | 132 +++++++++++++++++++++++++++++-------- 3 files changed, 168 insertions(+), 43 deletions(-) (limited to 'doc/src') 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 @@ - + @@ -1536,11 +1536,7 @@ bool - True if this is a table and it has (or recently had) any - indexes. This is set by CREATE INDEX, but - not cleared immediately by DROP INDEX. - VACUUM clears relhasindex if it finds the - table has no indexes + True if this is a table and it has (or recently had) any indexes @@ -1617,6 +1613,17 @@ + + relhasexclusion + bool + + + 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 + + + relhasrules bool @@ -1680,6 +1687,17 @@ + + + Several of the boolean flags in 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, relhasindex is set by + CREATE INDEX, but it is never cleared by + DROP INDEX. Instead, VACUUM clears + relhasindex if it finds the table has no indexes. This + arrangement avoids race conditions and improves concurrency. + @@ -1690,11 +1708,12 @@ - The catalog pg_constraint 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 - pg_attribute catalog. + The catalog pg_constraint 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 pg_attribute + catalog, not here. @@ -1739,7 +1758,8 @@ c = check constraint, f = foreign key constraint, p = primary key constraint, - u = unique constraint + u = unique constraint, + x = exclusion constraint @@ -1776,7 +1796,7 @@ oid pg_class.oid The index supporting this constraint, if it's a unique, primary - key, or foreign key constraint; else 0 + key, foreign key, or exclusion constraint; else 0 @@ -1828,7 +1848,7 @@ bool - 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 @@ -1838,7 +1858,8 @@ int4 - 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 @@ -1878,6 +1899,13 @@ If a foreign key, list of the equality operators for FK = FK comparisons + + conexclop + oid[] + pg_operator.oid + If an exclusion constraint, list of the per-column exclusion operators + + conbin text @@ -1895,6 +1923,16 @@ + + In the case of an exclusion constraint, conkey + is only useful for constraint elements that are simple column references. + For other cases, a zero appears in conkey + and the associated index must be consulted to discover the expression + that is constrained. (conkey thus has the + same contents as pg_index.indkey for the + index.) + + consrc is not updated when referenced objects @@ -1908,7 +1946,8 @@ pg_class.relchecks needs to agree with the number of check-constraint entries found in this table for each - relation. + relation. Also, pg_class.relhasexclusion must + be true if there are any exclusion-constraint entries for the relation. 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 @@ - + <productname>PostgreSQL</productname> Error Codes @@ -640,6 +640,12 @@ check_violation + +23P01 +EXCLUSION VIOLATION +exclusion_violation + + Class 24 — Invalid Cursor State 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 @@ @@ -24,7 +24,7 @@ PostgreSQL documentation CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( [ { column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ] | table_constraint - | LIKE parent_table [ { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL } ] ... } + | LIKE parent_table [ like_option ... ] } [, ... ] ] ) [ INHERITS ( parent_table [, ... ] ) ] @@ -37,9 +37,9 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE constraint_name ] { NOT NULL | NULL | + CHECK ( expression ) | UNIQUE index_parameters | PRIMARY KEY index_parameters | - CHECK ( expression ) | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] @@ -47,17 +47,26 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_constraint is: [ CONSTRAINT constraint_name ] -{ UNIQUE ( column_name [, ... ] ) index_parameters | +{ CHECK ( expression ) | + UNIQUE ( column_name [, ... ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] ) index_parameters | - CHECK ( expression ) | + EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] -index_parameters in UNIQUE and PRIMARY KEY constraints are: +and like_option is: + +{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL } + +index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are: [ WITH ( storage_parameter [= value] [, ... ] ) ] [ USING INDEX TABLESPACE tablespace ] + +exclude_element in an EXCLUDE constraint is: + +{ column | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] @@ -251,7 +260,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE + + CHECK ( expression ) + + + The 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. + + + + Currently, CHECK expressions cannot contain + subqueries nor refer to variables other than columns of the + current row. + + + + UNIQUE (column constraint) UNIQUE ( column_name [, ... ] ) (table constraint) @@ -406,29 +438,54 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE expression ) + EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] - The 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 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 TRUE. If all of the + specified operators test for equality, this is equivalent to a + 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 ) by using the + && operator. - Currently, CHECK 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 ) for the index access + method index_method. + The operators are required to be commutative. + Each exclude_element + can optionally specify an operator class and/or ordering options; + these are described fully under + . + + + + The access method must support amgettuple (see ); at present this means 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 GiST. + + + + The 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. - REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (column constraint) @@ -557,7 +614,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE command). NOT DEFERRABLE is the default. - Currently, only UNIQUE, PRIMARY KEY, and + Currently, only UNIQUE, PRIMARY KEY, + EXCLUDE, and REFERENCES (foreign key) constraints accept this clause. NOT NULL and CHECK constraints are not deferrable. @@ -695,8 +753,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE is consulted, or if the table is temporary. @@ -715,8 +773,9 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE . The storage parameters currently available for tables are listed below. For each parameter, unless noted, @@ -1099,6 +1158,18 @@ WITH (fillfactor=70); + + Create table circles with an exclusion + constraint that prevents any two circles from overlapping: + + +CREATE TABLE circles ( + c circle, + EXCLUDE USING gist (c WITH &&) +); + + + Create table cinemas in tablespace diskvol1: @@ -1194,6 +1265,15 @@ CREATE TABLE cinemas ( + + <literal>EXCLUDE</literal> Constraint + + + The EXCLUDE constraint type is a + PostgreSQL extension. + + + <literal>NULL</literal> <quote>Constraint</quote> -- cgit v1.2.3