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

2 files changed, 59 insertions, 18 deletions

diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 1fddf10bc9a..1bfe48eaac4 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -26,7 +26,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/executor/execMain.c,v 1.326 2009/06/11 20:46:11 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/executor/execMain.c,v 1.327 2009/07/29 20:56:18 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -1753,6 +1753,7 @@ ExecInsert(TupleTableSlot *slot,
 	ResultRelInfo *resultRelInfo;
 	Relation	resultRelationDesc;
 	Oid			newId;
+	List	   *recheckIndexes = NIL;
 
 	/*
 	 * get the heap tuple out of the tuple table slot, making sure we have a
@@ -1834,10 +1835,11 @@ ExecInsert(TupleTableSlot *slot,
 	 * insert index entries for tuple
 	 */
 	if (resultRelInfo->ri_NumIndices > 0)
-		ExecInsertIndexTuples(slot, &(tuple->t_self), estate, false);
+		recheckIndexes = ExecInsertIndexTuples(slot, &(tuple->t_self),
+											   estate, false);
 
 	/* AFTER ROW INSERT Triggers */
-	ExecARInsertTriggers(estate, resultRelInfo, tuple);
+	ExecARInsertTriggers(estate, resultRelInfo, tuple, recheckIndexes);
 
 	/* Process RETURNING if present */
 	if (resultRelInfo->ri_projectReturning)
@@ -1999,6 +2001,7 @@ ExecUpdate(TupleTableSlot *slot,
 	HTSU_Result result;
 	ItemPointerData update_ctid;
 	TransactionId update_xmax;
+	List *recheckIndexes = NIL;
 
 	/*
 	 * abort the operation if not running transactions
@@ -2132,10 +2135,12 @@ lreplace:;
 	 * If it's a HOT update, we mustn't insert new index entries.
 	 */
 	if (resultRelInfo->ri_NumIndices > 0 && !HeapTupleIsHeapOnly(tuple))
-		ExecInsertIndexTuples(slot, &(tuple->t_self), estate, false);
+		recheckIndexes = ExecInsertIndexTuples(slot, &(tuple->t_self),
+											   estate, false);
 
 	/* AFTER ROW UPDATE Triggers */
-	ExecARUpdateTriggers(estate, resultRelInfo, tupleid, tuple);
+	ExecARUpdateTriggers(estate, resultRelInfo, tupleid, tuple,
+						 recheckIndexes);
 
 	/* Process RETURNING if present */
 	if (resultRelInfo->ri_projectReturning)
diff --git a/src/backend/executor/execUtils.c b/src/backend/executor/execUtils.c
index 9411718667f..43c0e540653 100644
--- a/src/backend/executor/execUtils.c
+++ b/src/backend/executor/execUtils.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/executor/execUtils.c,v 1.160 2009/07/18 19:15:41 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/executor/execUtils.c,v 1.161 2009/07/29 20:56:18 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -1033,17 +1033,22 @@ ExecCloseIndices(ResultRelInfo *resultRelInfo)
  *		doesn't provide the functionality needed by the
  *		executor.. -cim 9/27/89
  *
+ *		This returns a list of OIDs for any unique indexes
+ *		whose constraint check was deferred and which had
+ *		potential (unconfirmed) conflicts.
+ *
  *		CAUTION: this must not be called for a HOT update.
  *		We can't defend against that here for lack of info.
  *		Should we change the API to make it safer?
  * ----------------------------------------------------------------
  */
-void
+List *
 ExecInsertIndexTuples(TupleTableSlot *slot,
 					  ItemPointer tupleid,
 					  EState *estate,
-					  bool is_vacuum)
+					  bool is_vacuum_full)
 {
+	List	   *result = NIL;
 	ResultRelInfo *resultRelInfo;
 	int			i;
 	int			numIndices;
@@ -1077,9 +1082,12 @@ ExecInsertIndexTuples(TupleTableSlot *slot,
 	 */
 	for (i = 0; i < numIndices; i++)
 	{
+		Relation	indexRelation = relationDescs[i];
 		IndexInfo  *indexInfo;
+		IndexUniqueCheck checkUnique;
+		bool		isUnique;
 
-		if (relationDescs[i] == NULL)
+		if (indexRelation == NULL)
 			continue;
 
 		indexInfo = indexInfoArray[i];
@@ -1122,22 +1130,50 @@ ExecInsertIndexTuples(TupleTableSlot *slot,
 					   isnull);
 
 		/*
-		 * The index AM does the rest.	Note we suppress unique-index checks
-		 * if we are being called from VACUUM, since VACUUM may need to move
-		 * dead tuples that have the same keys as live ones.
+		 * The index AM does the rest, including uniqueness checking.
+		 *
+		 * For an immediate-mode unique index, we just tell the index AM to
+		 * throw error if not unique.
+		 *
+		 * For a deferrable unique index, we tell the index AM to just detect
+		 * possible non-uniqueness, and we add the index OID to the result
+		 * list if further checking is needed.
+		 *
+		 * Special hack: we suppress unique-index checks if we are being
+		 * called from VACUUM FULL, since VACUUM FULL may need to move dead
+		 * tuples that have the same keys as live ones.
 		 */
-		index_insert(relationDescs[i],	/* index relation */
-					 values,	/* array of index Datums */
-					 isnull,	/* null flags */
-					 tupleid,	/* tid of heap tuple */
-					 heapRelation,
-					 relationDescs[i]->rd_index->indisunique && !is_vacuum);
+		if (is_vacuum_full || !indexRelation->rd_index->indisunique)
+			checkUnique = UNIQUE_CHECK_NO;
+		else if (indexRelation->rd_index->indimmediate)
+			checkUnique = UNIQUE_CHECK_YES;
+		else
+			checkUnique = UNIQUE_CHECK_PARTIAL;
+
+		isUnique =
+			index_insert(indexRelation,	/* index relation */
+						 values,		/* array of index Datums */
+						 isnull,		/* null flags */
+						 tupleid,		/* tid of heap tuple */
+						 heapRelation,	/* heap relation */
+						 checkUnique);	/* type of uniqueness check to do */
+
+		if (checkUnique == UNIQUE_CHECK_PARTIAL && !isUnique)
+		{
+			/*
+			 * The tuple potentially violates the uniqueness constraint,
+			 * so make a note of the index so that we can re-check it later.
+			 */
+			result = lappend_oid(result, RelationGetRelid(indexRelation));
+		}
 
 		/*
 		 * keep track of index inserts for debugging
 		 */
 		IncrIndexInserted();
 	}
+
+	return result;
 }
 
 /*