aboutsummaryrefslogtreecommitdiff
path: root/doc
diff options
context:
space:
mode:
Diffstat (limited to 'doc')
-rw-r--r--doc/src/sgml/catalogs.sgml10
-rw-r--r--doc/src/sgml/indexam.sgml108
-rw-r--r--doc/src/sgml/ref/create_table.sgml40
-rw-r--r--doc/src/sgml/ref/set_constraints.sgml23
4 files changed, 149 insertions, 32 deletions
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index a70c11aa889..99aee810da4 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.202 2009/07/28 02:56:29 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.203 2009/07/29 20:56:17 tgl Exp $ -->
<!--
Documentation of the system catalogs, directed toward PostgreSQL developers
-->
@@ -2676,6 +2676,14 @@
</row>
<row>
+ <entry><structfield>indimmediate</structfield></entry>
+ <entry><type>bool</type></entry>
+ <entry></entry>
+ <entry>If true, the uniqueness check is enforced immediately on insertion
+ (<structfield>indisunique</> should always be true when this is true)</entry>
+ </row>
+
+ <row>
<entry><structfield>indisclustered</structfield></entry>
<entry><type>bool</type></entry>
<entry></entry>
diff --git a/doc/src/sgml/indexam.sgml b/doc/src/sgml/indexam.sgml
index 19b3c70814e..b81cd27d313 100644
--- a/doc/src/sgml/indexam.sgml
+++ b/doc/src/sgml/indexam.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/indexam.sgml,v 2.30 2009/03/24 20:17:08 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/indexam.sgml,v 2.31 2009/07/29 20:56:17 tgl Exp $ -->
<chapter id="indexam">
<title>Index Access Method Interface Definition</title>
@@ -172,20 +172,32 @@ aminsert (Relation indexRelation,
bool *isnull,
ItemPointer heap_tid,
Relation heapRelation,
- bool check_uniqueness);
+ IndexUniqueCheck checkUnique);
</programlisting>
Insert a new tuple into an existing index. The <literal>values</> and
<literal>isnull</> arrays give the key values to be indexed, and
<literal>heap_tid</> is the TID to be indexed.
If the access method supports unique indexes (its
<structname>pg_am</>.<structfield>amcanunique</> flag is true) then
- <literal>check_uniqueness</> might be true, in which case the access method
- must verify that there is no conflicting row; this is the only situation in
- which the access method normally needs the <literal>heapRelation</>
- parameter. See <xref linkend="index-unique-checks"> for details.
- The result is TRUE if an index entry was inserted, FALSE if not. (A FALSE
- result does not denote an error condition, but is used for cases such
- as an index method refusing to index a NULL.)
+ <literal>checkUnique</> indicates the type of uniqueness check to
+ perform. This varies depending on whether the unique constraint is
+ deferrable; see <xref linkend="index-unique-checks"> for details.
+ Normally the access method only needs the <literal>heapRelation</>
+ parameter when performing uniqueness checking (since then it will have to
+ look into the heap to verify tuple liveness).
+ </para>
+
+ <para>
+ The function's boolean result value is significant only when
+ <literal>checkUnique</> is <literal>UNIQUE_CHECK_PARTIAL</>.
+ In this case a TRUE result means the new entry is known unique, whereas
+ FALSE means it might be non-unique (and a deferred uniqueness check must
+ be scheduled). For other cases a constant FALSE result is recommended.
+ </para>
+
+ <para>
+ Some indexes might not index all tuples. If the tuple is not to be
+ indexed, <function>aminsert</> should just return without doing anything.
</para>
<para>
@@ -706,10 +718,10 @@ amrestrpos (IndexScanDesc scan);
</para>
<para>
- Furthermore, immediately before raising a uniqueness violation
+ Furthermore, immediately before reporting a uniqueness violation
according to the above rules, the access method must recheck the
liveness of the row being inserted. If it is committed dead then
- no error should be raised. (This case cannot occur during the
+ no violation should be reported. (This case cannot occur during the
ordinary scenario of inserting a row that's just been created by
the current transaction. It can happen during
<command>CREATE UNIQUE INDEX CONCURRENTLY</>, however.)
@@ -728,8 +740,78 @@ amrestrpos (IndexScanDesc scan);
</para>
<para>
- The main limitation of this scheme is that it has no convenient way
- to support deferred uniqueness checks.
+ If the unique constraint is deferrable, there is additional complexity:
+ we need to be able to insert an index entry for a new row, but defer any
+ uniqueness-violation error until end of statement or even later. To
+ avoid unnecessary repeat searches of the index, the index access method
+ should do a preliminary uniqueness check during the initial insertion.
+ If this shows that there is definitely no conflicting live tuple, we
+ are done. Otherwise, we schedule a recheck to occur when it is time to
+ enforce the constraint. If, at the time of the recheck, both the inserted
+ tuple and some other tuple with the same key are live, then the error
+ must be reported. (Note that for this purpose, <quote>live</> actually
+ means <quote>any tuple in the index entry's HOT chain is live</>.)
+ To implement this, the <function>aminsert</> function is passed a
+ <literal>checkUnique</> parameter having one of the following values:
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ <literal>UNIQUE_CHECK_NO</> indicates that no uniqueness checking
+ should be done (this is not a unique index).
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>UNIQUE_CHECK_YES</> indicates that this is a non-deferrable
+ unique index, and the uniqueness check must be done immediately, as
+ described above.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>UNIQUE_CHECK_PARTIAL</> indicates that the unique
+ constraint is deferrable. <productname>PostgreSQL</productname>
+ will use this mode to insert each row's index entry. The access
+ method must allow duplicate entries into the index, and report any
+ potential duplicates by returning FALSE from <function>aminsert</>.
+ For each row for which FALSE is returned, a deferred recheck will
+ be scheduled.
+ </para>
+
+ <para>
+ The access method must identify any rows which might violate the
+ unique constraint, but it is not an error for it to report false
+ positives. This allows the check to be done without waiting for other
+ transactions to finish; conflicts reported here are not treated as
+ errors and will be rechecked later, by which time they may no longer
+ be conflicts.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal>UNIQUE_CHECK_EXISTING</> indicates that this is a deferred
+ recheck of a row that was reported as a potential uniqueness violation.
+ Although this is implemented by calling <function>aminsert</>, the
+ access method must <emphasis>not</> insert a new index entry in this
+ case. The index entry is already present. Rather, the access method
+ must check to see if there is another live index entry. If so, and
+ if the target row is also still live, report error.
+ </para>
+
+ <para>
+ It is recommended that in a <literal>UNIQUE_CHECK_EXISTING</> call,
+ the access method further verify that the target row actually does
+ have an existing entry in the index, and report error if not. This
+ is a good idea because the index tuple values passed to
+ <function>aminsert</> will have been recomputed. If the index
+ definition involves functions that are not really immutable, we
+ might be checking the wrong area of the index. Checking that the
+ target row is found in the recheck verifies that we are scanning
+ for the same tuple values as were used in the original insertion.
+ </para>
+ </listitem>
+ </itemizedlist>
</para>
</sect1>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 64971752eb4..1f986bcd88c 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.114 2009/02/12 13:25:33 momjian Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.115 2009/07/29 20:56:17 tgl Exp $
PostgreSQL documentation
-->
@@ -35,8 +35,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PAR
where <replaceable class="PARAMETER">column_constraint</replaceable> is:
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
-{ NOT NULL |
- NULL |
+{ NOT NULL |
+ NULL |
UNIQUE <replaceable class="PARAMETER">index_parameters</replaceable> |
PRIMARY KEY <replaceable class="PARAMETER">index_parameters</replaceable> |
CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) |
@@ -423,11 +423,10 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
contain values that match values in the referenced
column(s) of some row of the referenced table. If <replaceable
class="parameter">refcolumn</replaceable> is omitted, the
- primary key of the <replaceable
- class="parameter">reftable</replaceable> is used. The
- referenced columns must be the columns of a unique or primary
- key constraint in the referenced table. Note that foreign key
- constraints cannot be defined between temporary tables and
+ primary key of the <replaceable class="parameter">reftable</replaceable>
+ is used. The referenced columns must be the columns of a non-deferrable
+ unique or primary key constraint in the referenced table. Note that
+ foreign key constraints cannot be defined between temporary tables and
permanent tables.
</para>
@@ -534,9 +533,11 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
after every command. Checking of constraints that are
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. Only foreign
- key constraints currently accept this clause. All other
- constraint types are not deferrable.
+ <literal>NOT DEFERRABLE</literal> is the default.
+ Currently, only <literal>UNIQUE</>, <literal>PRIMARY KEY</>, and
+ <literal>REFERENCES</> (foreign key) constraints accept this
+ clause. <literal>NOT NULL</> and <literal>CHECK</> constraints are not
+ deferrable.
</para>
</listitem>
</varlistentry>
@@ -1141,6 +1142,23 @@ CREATE TABLE cinemas (
</refsect2>
<refsect2>
+ <title>Non-deferred Uniqueness Constraints</title>
+
+ <para>
+ When a <literal>UNIQUE</> or <literal>PRIMARY KEY</> constraint is
+ not deferrable, <productname>PostgreSQL</productname> checks for
+ uniqueness immediately whenever a row is inserted or modified.
+ The SQL standard says that uniqueness should be enforced only at
+ the end of the statement; this makes a difference when, for example,
+ a single command updates multiple key values. To obtain
+ standard-compliant behavior, declare the constraint as
+ <literal>DEFERRABLE</> but not deferred (i.e., <literal>INITIALLY
+ IMMEDIATE</>). Be aware that this can be significantly slower than
+ immediate uniqueness checking.
+ </para>
+ </refsect2>
+
+ <refsect2>
<title>Column Check Constraints</title>
<para>
diff --git a/doc/src/sgml/ref/set_constraints.sgml b/doc/src/sgml/ref/set_constraints.sgml
index 58f64b2437c..e03910e2529 100644
--- a/doc/src/sgml/ref/set_constraints.sgml
+++ b/doc/src/sgml/ref/set_constraints.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/ref/set_constraints.sgml,v 1.16 2008/11/14 10:22:47 petere Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/ref/set_constraints.sgml,v 1.17 2009/07/29 20:56:17 tgl Exp $ -->
<refentry id="SQL-SET-CONSTRAINTS">
<refmeta>
<refentrytitle id="SQL-SET-CONSTRAINTS-title">SET CONSTRAINTS</refentrytitle>
@@ -48,7 +48,7 @@ SET CONSTRAINTS { ALL | <replaceable class="parameter">name</replaceable> [, ...
<command>SET CONSTRAINTS</command> with a list of constraint names changes
the mode of just those constraints (which must all be deferrable). The
current schema search path is used to find the first matching name if
- no schema name is specified. <command>SET CONSTRAINTS ALL</command>
+ no schema name is specified. <command>SET CONSTRAINTS ALL</command>
changes the mode of all deferrable constraints.
</para>
@@ -66,10 +66,19 @@ SET CONSTRAINTS { ALL | <replaceable class="parameter">name</replaceable> [, ...
</para>
<para>
- Currently, only foreign key constraints are affected by this
- setting. Check and unique constraints are always effectively
- not deferrable. Triggers that are declared as <quote>constraint
- triggers</> are also affected.
+ Currently, only <literal>UNIQUE</>, <literal>PRIMARY KEY</>, and
+ <literal>REFERENCES</> (foreign key) constraints are affected by this
+ setting. <literal>NOT NULL</> and <literal>CHECK</> constraints are
+ always checked immediately when a row is inserted or modified
+ (<emphasis>not</> at the end of the statement).
+ Uniqueness constraints that have not been declared <literal>DEFERRABLE</>
+ are also checked immediately.
+ </para>
+
+ <para>
+ The firing of triggers that are declared as <quote>constraint triggers</>
+ is also controlled by this setting &mdash; they fire at the same time
+ that the associated constraint should be checked.
</para>
</refsect1>
@@ -92,7 +101,7 @@ SET CONSTRAINTS { ALL | <replaceable class="parameter">name</replaceable> [, ...
This command complies with the behavior defined in the SQL
standard, except for the limitation that, in
<productname>PostgreSQL</productname>, it only applies to
- foreign-key constraints.
+ foreign-key and uniqueness constraints.
</para>
</refsect1>
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-31 15:03:29 +0000