From 25d9bf2e3e66ee2e546c5c523d148ecab6ee1dcc Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 29 Jul 2009 20:56:21 +0000 Subject: Support deferrable uniqueness constraints. The current implementation fires an AFTER ROW trigger for each tuple that looks like it might be non-unique according to the index contents at the time of insertion. This works well as long as there aren't many conflicts, but won't scale to massive unique-key reassignments. Improving that case is a TODO item. Dean Rasheed --- doc/src/sgml/catalogs.sgml | 10 +++- doc/src/sgml/indexam.sgml | 108 ++++++++++++++++++++++++++++++---- doc/src/sgml/ref/create_table.sgml | 40 +++++++++---- doc/src/sgml/ref/set_constraints.sgml | 23 +++++--- 4 files changed, 149 insertions(+), 32 deletions(-) (limited to 'doc/src') 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 @@ - + @@ -2675,6 +2675,14 @@ (indisunique should always be true when this is true) + + indimmediate + bool + + If true, the uniqueness check is enforced immediately on insertion + (indisunique should always be true when this is true) + + indisclustered bool 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 @@ - + Index Access Method Interface Definition @@ -172,20 +172,32 @@ aminsert (Relation indexRelation, bool *isnull, ItemPointer heap_tid, Relation heapRelation, - bool check_uniqueness); + IndexUniqueCheck checkUnique); Insert a new tuple into an existing index. The values and isnull arrays give the key values to be indexed, and heap_tid is the TID to be indexed. If the access method supports unique indexes (its pg_am.amcanunique flag is true) then - 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 heapRelation - parameter. See 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.) + checkUnique indicates the type of uniqueness check to + perform. This varies depending on whether the unique constraint is + deferrable; see for details. + Normally the access method only needs the heapRelation + parameter when performing uniqueness checking (since then it will have to + look into the heap to verify tuple liveness). + + + + The function's boolean result value is significant only when + checkUnique is 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. + + + + Some indexes might not index all tuples. If the tuple is not to be + indexed, aminsert should just return without doing anything. @@ -706,10 +718,10 @@ amrestrpos (IndexScanDesc scan); - 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 CREATE UNIQUE INDEX CONCURRENTLY, however.) @@ -728,8 +740,78 @@ amrestrpos (IndexScanDesc scan); - 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, live actually + means any tuple in the index entry's HOT chain is live.) + To implement this, the aminsert function is passed a + checkUnique parameter having one of the following values: + + + + + UNIQUE_CHECK_NO indicates that no uniqueness checking + should be done (this is not a unique index). + + + + + UNIQUE_CHECK_YES indicates that this is a non-deferrable + unique index, and the uniqueness check must be done immediately, as + described above. + + + + + UNIQUE_CHECK_PARTIAL indicates that the unique + constraint is deferrable. PostgreSQL + 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 aminsert. + For each row for which FALSE is returned, a deferred recheck will + be scheduled. + + + + 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. + + + + + 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 aminsert, the + access method must 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. + + + + It is recommended that in a 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 + 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. + + + 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 @@ @@ -35,8 +35,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE column_constraint is: [ CONSTRAINT constraint_name ] -{ NOT NULL | - NULL | +{ NOT NULL | + NULL | UNIQUE index_parameters | PRIMARY KEY index_parameters | CHECK ( expression ) | @@ -423,11 +423,10 @@ and table_constraint is: contain values that match values in the referenced column(s) of some row of the referenced table. If refcolumn is omitted, the - primary key of the reftable 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 reftable + 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. @@ -534,9 +533,11 @@ and table_constraint is: after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction (using the command). - NOT DEFERRABLE is the default. Only foreign - key constraints currently accept this clause. All other - constraint types are not deferrable. + NOT DEFERRABLE is the default. + Currently, only UNIQUE, PRIMARY KEY, and + REFERENCES (foreign key) constraints accept this + clause. NOT NULL and CHECK constraints are not + deferrable. @@ -1140,6 +1141,23 @@ CREATE TABLE cinemas ( + + Non-deferred Uniqueness Constraints + + + When a UNIQUE or PRIMARY KEY constraint is + not deferrable, PostgreSQL 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 + DEFERRABLE but not deferred (i.e., INITIALLY + IMMEDIATE). Be aware that this can be significantly slower than + immediate uniqueness checking. + + + Column Check Constraints 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 @@ - + SET CONSTRAINTS @@ -48,7 +48,7 @@ SET CONSTRAINTS { ALL | name [, ... SET CONSTRAINTS 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. SET CONSTRAINTS ALL + no schema name is specified. SET CONSTRAINTS ALL changes the mode of all deferrable constraints. @@ -66,10 +66,19 @@ SET CONSTRAINTS { ALL | name [, ... - Currently, only foreign key constraints are affected by this - setting. Check and unique constraints are always effectively - not deferrable. Triggers that are declared as constraint - triggers are also affected. + Currently, only UNIQUE, PRIMARY KEY, and + REFERENCES (foreign key) constraints are affected by this + setting. NOT NULL and CHECK constraints are + always checked immediately when a row is inserted or modified + (not at the end of the statement). + Uniqueness constraints that have not been declared DEFERRABLE + are also checked immediately. + + + + The firing of triggers that are declared as constraint triggers + is also controlled by this setting — they fire at the same time + that the associated constraint should be checked. @@ -92,7 +101,7 @@ SET CONSTRAINTS { ALL | name [, ... This command complies with the behavior defined in the SQL standard, except for the limitation that, in PostgreSQL, it only applies to - foreign-key constraints. + foreign-key and uniqueness constraints. -- cgit v1.2.3