Skip to content

Commit 25d9bf2

Browse files
tglsfdctglsfdc
committed
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
1 parent 8504905 commit 25d9bf2

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

51 files changed

+1241
-245
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.202 2009/07/28 02:56:29 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.203 2009/07/29 20:56:17 tgl Exp $ -->
22
<!--
33
Documentation of the system catalogs, directed toward PostgreSQL developers
44
-->
@@ -2675,6 +2675,14 @@
26752675
(<structfield>indisunique</> should always be true when this is true)</entry>
26762676
</row>
26772677

2678+
<row>
2679+
<entry><structfield>indimmediate</structfield></entry>
2680+
<entry><type>bool</type></entry>
2681+
<entry></entry>
2682+
<entry>If true, the uniqueness check is enforced immediately on insertion
2683+
(<structfield>indisunique</> should always be true when this is true)</entry>
2684+
</row>
2685+
26782686
<row>
26792687
<entry><structfield>indisclustered</structfield></entry>
26802688
<entry><type>bool</type></entry>

‎doc/src/sgml/indexam.sgml

Lines changed: 95 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/indexam.sgml,v 2.30 2009/03/24 20:17:08 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/indexam.sgml,v 2.31 2009/07/29 20:56:17 tgl Exp $ -->
22

33
<chapter id="indexam">
44
<title>Index Access Method Interface Definition</title>
@@ -172,20 +172,32 @@ aminsert (Relation indexRelation,
172172
bool *isnull,
173173
ItemPointer heap_tid,
174174
Relation heapRelation,
175-
bool check_uniqueness);
175+
IndexUniqueCheck checkUnique);
176176
</programlisting>
177177
Insert a new tuple into an existing index. The <literal>values</> and
178178
<literal>isnull</> arrays give the key values to be indexed, and
179179
<literal>heap_tid</> is the TID to be indexed.
180180
If the access method supports unique indexes (its
181181
<structname>pg_am</>.<structfield>amcanunique</> flag is true) then
182-
<literal>check_uniqueness</> might be true, in which case the access method
183-
must verify that there is no conflicting row; this is the only situation in
184-
which the access method normally needs the <literal>heapRelation</>
185-
parameter. See <xref linkend="index-unique-checks"> for details.
186-
The result is TRUE if an index entry was inserted, FALSE if not. (A FALSE
187-
result does not denote an error condition, but is used for cases such
188-
as an index method refusing to index a NULL.)
182+
<literal>checkUnique</> indicates the type of uniqueness check to
183+
perform. This varies depending on whether the unique constraint is
184+
deferrable; see <xref linkend="index-unique-checks"> for details.
185+
Normally the access method only needs the <literal>heapRelation</>
186+
parameter when performing uniqueness checking (since then it will have to
187+
look into the heap to verify tuple liveness).
188+
</para>
189+
190+
<para>
191+
The function's boolean result value is significant only when
192+
<literal>checkUnique</> is <literal>UNIQUE_CHECK_PARTIAL</>.
193+
In this case a TRUE result means the new entry is known unique, whereas
194+
FALSE means it might be non-unique (and a deferred uniqueness check must
195+
be scheduled). For other cases a constant FALSE result is recommended.
196+
</para>
197+
198+
<para>
199+
Some indexes might not index all tuples. If the tuple is not to be
200+
indexed, <function>aminsert</> should just return without doing anything.
189201
</para>
190202

191203
<para>
@@ -706,10 +718,10 @@ amrestrpos (IndexScanDesc scan);
706718
</para>
707719

708720
<para>
709-
Furthermore, immediately before raising a uniqueness violation
721+
Furthermore, immediately before reporting a uniqueness violation
710722
according to the above rules, the access method must recheck the
711723
liveness of the row being inserted. If it is committed dead then
712-
no error should be raised. (This case cannot occur during the
724+
no violation should be reported. (This case cannot occur during the
713725
ordinary scenario of inserting a row that's just been created by
714726
the current transaction. It can happen during
715727
<command>CREATE UNIQUE INDEX CONCURRENTLY</>, however.)
@@ -728,8 +740,78 @@ amrestrpos (IndexScanDesc scan);
728740
</para>
729741

730742
<para>
731-
The main limitation of this scheme is that it has no convenient way
732-
to support deferred uniqueness checks.
743+
If the unique constraint is deferrable, there is additional complexity:
744+
we need to be able to insert an index entry for a new row, but defer any
745+
uniqueness-violation error until end of statement or even later. To
746+
avoid unnecessary repeat searches of the index, the index access method
747+
should do a preliminary uniqueness check during the initial insertion.
748+
If this shows that there is definitely no conflicting live tuple, we
749+
are done. Otherwise, we schedule a recheck to occur when it is time to
750+
enforce the constraint. If, at the time of the recheck, both the inserted
751+
tuple and some other tuple with the same key are live, then the error
752+
must be reported. (Note that for this purpose, <quote>live</> actually
753+
means <quote>any tuple in the index entry's HOT chain is live</>.)
754+
To implement this, the <function>aminsert</> function is passed a
755+
<literal>checkUnique</> parameter having one of the following values:
756+
757+
<itemizedlist>
758+
<listitem>
759+
<para>
760+
<literal>UNIQUE_CHECK_NO</> indicates that no uniqueness checking
761+
should be done (this is not a unique index).
762+
</para>
763+
</listitem>
764+
<listitem>
765+
<para>
766+
<literal>UNIQUE_CHECK_YES</> indicates that this is a non-deferrable
767+
unique index, and the uniqueness check must be done immediately, as
768+
described above.
769+
</para>
770+
</listitem>
771+
<listitem>
772+
<para>
773+
<literal>UNIQUE_CHECK_PARTIAL</> indicates that the unique
774+
constraint is deferrable. <productname>PostgreSQL</productname>
775+
will use this mode to insert each row's index entry. The access
776+
method must allow duplicate entries into the index, and report any
777+
potential duplicates by returning FALSE from <function>aminsert</>.
778+
For each row for which FALSE is returned, a deferred recheck will
779+
be scheduled.
780+
</para>
781+
782+
<para>
783+
The access method must identify any rows which might violate the
784+
unique constraint, but it is not an error for it to report false
785+
positives. This allows the check to be done without waiting for other
786+
transactions to finish; conflicts reported here are not treated as
787+
errors and will be rechecked later, by which time they may no longer
788+
be conflicts.
789+
</para>
790+
</listitem>
791+
<listitem>
792+
<para>
793+
<literal>UNIQUE_CHECK_EXISTING</> indicates that this is a deferred
794+
recheck of a row that was reported as a potential uniqueness violation.
795+
Although this is implemented by calling <function>aminsert</>, the
796+
access method must <emphasis>not</> insert a new index entry in this
797+
case. The index entry is already present. Rather, the access method
798+
must check to see if there is another live index entry. If so, and
799+
if the target row is also still live, report error.
800+
</para>
801+
802+
<para>
803+
It is recommended that in a <literal>UNIQUE_CHECK_EXISTING</> call,
804+
the access method further verify that the target row actually does
805+
have an existing entry in the index, and report error if not. This
806+
is a good idea because the index tuple values passed to
807+
<function>aminsert</> will have been recomputed. If the index
808+
definition involves functions that are not really immutable, we
809+
might be checking the wrong area of the index. Checking that the
810+
target row is found in the recheck verifies that we are scanning
811+
for the same tuple values as were used in the original insertion.
812+
</para>
813+
</listitem>
814+
</itemizedlist>
733815
</para>
734816

735817
</sect1>

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 29 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.114 2009/02/12 13:25:33 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_table.sgml,v 1.115 2009/07/29 20:56:17 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -35,8 +35,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PAR
3535
where <replaceable class="PARAMETER">column_constraint</replaceable> is:
3636

3737
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
38-
{ NOT NULL |
39-
NULL |
38+
{ NOT NULL |
39+
NULL |
4040
UNIQUE <replaceable class="PARAMETER">index_parameters</replaceable> |
4141
PRIMARY KEY <replaceable class="PARAMETER">index_parameters</replaceable> |
4242
CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) |
@@ -423,11 +423,10 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
423423
contain values that match values in the referenced
424424
column(s) of some row of the referenced table. If <replaceable
425425
class="parameter">refcolumn</replaceable> is omitted, the
426-
primary key of the <replaceable
427-
class="parameter">reftable</replaceable> is used. The
428-
referenced columns must be the columns of a unique or primary
429-
key constraint in the referenced table. Note that foreign key
430-
constraints cannot be defined between temporary tables and
426+
primary key of the <replaceable class="parameter">reftable</replaceable>
427+
is used. The referenced columns must be the columns of a non-deferrable
428+
unique or primary key constraint in the referenced table. Note that
429+
foreign key constraints cannot be defined between temporary tables and
431430
permanent tables.
432431
</para>
433432

@@ -534,9 +533,11 @@ and <replaceable class="PARAMETER">table_constraint</replaceable> is:
534533
after every command. Checking of constraints that are
535534
deferrable can be postponed until the end of the transaction
536535
(using the <xref linkend="sql-set-constraints" endterm="sql-set-constraints-title"> command).
537-
<literal>NOT DEFERRABLE</literal> is the default. Only foreign
538-
key constraints currently accept this clause. All other
539-
constraint types are not deferrable.
536+
<literal>NOT DEFERRABLE</literal> is the default.
537+
Currently, only <literal>UNIQUE</>, <literal>PRIMARY KEY</>, and
538+
<literal>REFERENCES</> (foreign key) constraints accept this
539+
clause. <literal>NOT NULL</> and <literal>CHECK</> constraints are not
540+
deferrable.
540541
</para>
541542
</listitem>
542543
</varlistentry>
@@ -1140,6 +1141,23 @@ CREATE TABLE cinemas (
11401141
</para>
11411142
</refsect2>
11421143

1144+
<refsect2>
1145+
<title>Non-deferred Uniqueness Constraints</title>
1146+
1147+
<para>
1148+
When a <literal>UNIQUE</> or <literal>PRIMARY KEY</> constraint is
1149+
not deferrable, <productname>PostgreSQL</productname> checks for
1150+
uniqueness immediately whenever a row is inserted or modified.
1151+
The SQL standard says that uniqueness should be enforced only at
1152+
the end of the statement; this makes a difference when, for example,
1153+
a single command updates multiple key values. To obtain
1154+
standard-compliant behavior, declare the constraint as
1155+
<literal>DEFERRABLE</> but not deferred (i.e., <literal>INITIALLY
1156+
IMMEDIATE</>). Be aware that this can be significantly slower than
1157+
immediate uniqueness checking.
1158+
</para>
1159+
</refsect2>
1160+
11431161
<refsect2>
11441162
<title>Column Check Constraints</title>
11451163

‎doc/src/sgml/ref/set_constraints.sgml

Lines changed: 16 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/ref/set_constraints.sgml,v 1.16 2008/11/14 10:22:47 petere Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/ref/set_constraints.sgml,v 1.17 2009/07/29 20:56:17 tgl Exp $ -->
22
<refentry id="SQL-SET-CONSTRAINTS">
33
<refmeta>
44
<refentrytitle id="SQL-SET-CONSTRAINTS-title">SET CONSTRAINTS</refentrytitle>
@@ -48,7 +48,7 @@ SET CONSTRAINTS { ALL | <replaceable class="parameter">name</replaceable> [, ...
4848
<command>SET CONSTRAINTS</command> with a list of constraint names changes
4949
the mode of just those constraints (which must all be deferrable). The
5050
current schema search path is used to find the first matching name if
51-
no schema name is specified. <command>SET CONSTRAINTS ALL</command>
51+
no schema name is specified. <command>SET CONSTRAINTS ALL</command>
5252
changes the mode of all deferrable constraints.
5353
</para>
5454

@@ -66,10 +66,19 @@ SET CONSTRAINTS { ALL | <replaceable class="parameter">name</replaceable> [, ...
6666
</para>
6767

6868
<para>
69-
Currently, only foreign key constraints are affected by this
70-
setting. Check and unique constraints are always effectively
71-
not deferrable. Triggers that are declared as <quote>constraint
72-
triggers</> are also affected.
69+
Currently, only <literal>UNIQUE</>, <literal>PRIMARY KEY</>, and
70+
<literal>REFERENCES</> (foreign key) constraints are affected by this
71+
setting. <literal>NOT NULL</> and <literal>CHECK</> constraints are
72+
always checked immediately when a row is inserted or modified
73+
(<emphasis>not</> at the end of the statement).
74+
Uniqueness constraints that have not been declared <literal>DEFERRABLE</>
75+
are also checked immediately.
76+
</para>
77+
78+
<para>
79+
The firing of triggers that are declared as <quote>constraint triggers</>
80+
is also controlled by this setting &mdash; they fire at the same time
81+
that the associated constraint should be checked.
7382
</para>
7483
</refsect1>
7584

@@ -92,7 +101,7 @@ SET CONSTRAINTS { ALL | <replaceable class="parameter">name</replaceable> [, ...
92101
This command complies with the behavior defined in the SQL
93102
standard, except for the limitation that, in
94103
<productname>PostgreSQL</productname>, it only applies to
95-
foreign-key constraints.
104+
foreign-key and uniqueness constraints.
96105
</para>
97106

98107
</refsect1>

‎src/backend/access/gin/gininsert.c

Lines changed: 5 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/gin/gininsert.c,v 1.22 2009/06/11 14:48:53 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/gin/gininsert.c,v 1.23 2009/07/29 20:56:17 tgl Exp $
1212
*-------------------------------------------------------------------------
1313
*/
1414

@@ -415,12 +415,11 @@ gininsert(PG_FUNCTION_ARGS)
415415

416416
#ifdef NOT_USED
417417
Relation heapRel = (Relation) PG_GETARG_POINTER(4);
418-
bool checkUnique = PG_GETARG_BOOL(5);
418+
IndexUniqueCheck checkUnique = (IndexUniqueCheck) PG_GETARG_INT32(5);
419419
#endif
420420
GinState ginstate;
421421
MemoryContext oldCtx;
422422
MemoryContext insertCtx;
423-
uint32 res = 0;
424423
int i;
425424

426425
insertCtx = AllocSetContextCreate(CurrentMemoryContext,
@@ -440,7 +439,7 @@ gininsert(PG_FUNCTION_ARGS)
440439
memset(&collector, 0, sizeof(GinTupleCollector));
441440
for (i = 0; i < ginstate.origTupdesc->natts; i++)
442441
if (!isnull[i])
443-
res += ginHeapTupleFastCollect(index, &ginstate, &collector,
442+
ginHeapTupleFastCollect(index, &ginstate, &collector,
444443
(OffsetNumber) (i + 1), values[i], ht_ctid);
445444

446445
ginHeapTupleFastInsert(index, &ginstate, &collector);
@@ -449,13 +448,13 @@ gininsert(PG_FUNCTION_ARGS)
449448
{
450449
for (i = 0; i < ginstate.origTupdesc->natts; i++)
451450
if (!isnull[i])
452-
res += ginHeapTupleInsert(index, &ginstate,
451+
ginHeapTupleInsert(index, &ginstate,
453452
(OffsetNumber) (i + 1), values[i], ht_ctid);
454453

455454
}
456455

457456
MemoryContextSwitchTo(oldCtx);
458457
MemoryContextDelete(insertCtx);
459458

460-
PG_RETURN_BOOL(res > 0);
459+
PG_RETURN_BOOL(false);
461460
}

‎src/backend/access/gist/gist.c

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/gist/gist.c,v 1.156 2009/01/01 17:23:34 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/gist/gist.c,v 1.157 2009/07/29 20:56:18 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -225,7 +225,7 @@ gistinsert(PG_FUNCTION_ARGS)
225225

226226
#ifdef NOT_USED
227227
Relation heapRel = (Relation) PG_GETARG_POINTER(4);
228-
bool checkUnique = PG_GETARG_BOOL(5);
228+
IndexUniqueCheck checkUnique = (IndexUniqueCheck) PG_GETARG_INT32(5);
229229
#endif
230230
IndexTuple itup;
231231
GISTSTATE giststate;
@@ -248,7 +248,7 @@ gistinsert(PG_FUNCTION_ARGS)
248248
MemoryContextSwitchTo(oldCtx);
249249
MemoryContextDelete(insertCtx);
250250

251-
PG_RETURN_BOOL(true);
251+
PG_RETURN_BOOL(false);
252252
}
253253

254254

‎src/backend/access/hash/hash.c

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/hash/hash.c,v 1.112 2009/06/11 14:48:53 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/hash/hash.c,v 1.113 2009/07/29 20:56:18 tgl Exp $
1212
*
1313
* NOTES
1414
* This file contains only the public interface routines.
@@ -165,7 +165,7 @@ hashinsert(PG_FUNCTION_ARGS)
165165

166166
#ifdef NOT_USED
167167
Relation heapRel = (Relation) PG_GETARG_POINTER(4);
168-
bool checkUnique = PG_GETARG_BOOL(5);
168+
IndexUniqueCheck checkUnique = (IndexUniqueCheck) PG_GETARG_INT32(5);
169169
#endif
170170
IndexTuple itup;
171171

@@ -192,7 +192,7 @@ hashinsert(PG_FUNCTION_ARGS)
192192

193193
pfree(itup);
194194

195-
PG_RETURN_BOOL(true);
195+
PG_RETURN_BOOL(false);
196196
}
197197

198198

0 commit comments

Comments
 (0)