*** ./src/backend/access/heap/tuptoaster.c.orig 2009-07-04 15:43:47.000000000 +0100 --- ./src/backend/access/heap/tuptoaster.c 2009-07-04 17:11:08.000000000 +0100 *************** *** 1225,1231 **** */ index_insert(toastidx, t_values, t_isnull, &(toasttup->t_self), ! toastrel, toastidx->rd_index->indisunique); /* * Free memory --- 1225,1233 ---- */ index_insert(toastidx, t_values, t_isnull, &(toasttup->t_self), ! toastrel, ! toastidx->rd_index->indisunique ? ! UNIQUE_CHECK_YES : UNIQUE_CHECK_NO); /* * Free memory *** ./src/backend/access/index/indexam.c.orig 2009-07-04 15:43:47.000000000 +0100 --- ./src/backend/access/index/indexam.c 2009-07-04 17:08:26.000000000 +0100 *************** *** 185,191 **** bool *isnull, ItemPointer heap_t_ctid, Relation heapRelation, ! bool check_uniqueness) { FmgrInfo *procedure; --- 185,191 ---- bool *isnull, ItemPointer heap_t_ctid, Relation heapRelation, ! IndexUniqueCheck check_uniqueness) { FmgrInfo *procedure; *************** *** 201,207 **** PointerGetDatum(isnull), PointerGetDatum(heap_t_ctid), PointerGetDatum(heapRelation), ! BoolGetDatum(check_uniqueness))); } /* --- 201,207 ---- PointerGetDatum(isnull), PointerGetDatum(heap_t_ctid), PointerGetDatum(heapRelation), ! Int32GetDatum((int32) check_uniqueness))); } /* *** ./src/backend/access/nbtree/nbtinsert.c.orig 2009-07-04 15:43:47.000000000 +0100 --- ./src/backend/access/nbtree/nbtinsert.c 2009-07-05 23:02:09.000000000 +0100 *************** *** 50,56 **** static TransactionId _bt_check_unique(Relation rel, IndexTuple itup, Relation heapRel, Buffer buf, OffsetNumber ioffset, ! ScanKey itup_scankey); static void _bt_findinsertloc(Relation rel, Buffer *bufptr, OffsetNumber *offsetptr, --- 50,57 ---- static TransactionId _bt_check_unique(Relation rel, IndexTuple itup, Relation heapRel, Buffer buf, OffsetNumber ioffset, ! ScanKey itup_scankey, IndexUniqueCheck unique_check, ! bool *is_unique); static void _bt_findinsertloc(Relation rel, Buffer *bufptr, OffsetNumber *offsetptr, *************** *** 85,95 **** * * This routine is called by the public interface routines, btbuild * and btinsert. By here, itup is filled in, including the TID. */ ! void _bt_doinsert(Relation rel, IndexTuple itup, ! bool index_is_unique, Relation heapRel) { int natts = rel->rd_rel->relnatts; ScanKey itup_scankey; BTStack stack; --- 86,107 ---- * * This routine is called by the public interface routines, btbuild * and btinsert. By here, itup is filled in, including the TID. + * + * The return value is always true, unless unique_check is + * UNIQUE_CHECK_PARTIAL, in which case it may return false if the + * tuple is potentially non-unique. This is only a partial uniqueness + * check, which doesn't block on other transactions, so it may report + * false conflicts. + * + * If full uniqueness checking is being done (UNIQUE_CHECK_YES or + * UNIQUE_CHECK_EXISTING) then this will error if duplicates are + * found. */ ! bool _bt_doinsert(Relation rel, IndexTuple itup, ! IndexUniqueCheck unique_check, Relation heapRel) { + bool ok = true; int natts = rel->rd_rel->relnatts; ScanKey itup_scankey; BTStack stack; *************** *** 134,165 **** * * If we must wait for another xact, we release the lock while waiting, * and then must start over completely. */ ! if (index_is_unique) { TransactionId xwait; offset = _bt_binsrch(rel, buf, natts, itup_scankey, false); ! xwait = _bt_check_unique(rel, itup, heapRel, buf, offset, itup_scankey); ! if (TransactionIdIsValid(xwait)) { ! /* Have to wait for the other guy ... */ ! _bt_relbuf(rel, buf); ! XactLockTableWait(xwait); ! /* start over... */ ! _bt_freestack(stack); ! goto top; } } ! /* do the insertion */ ! _bt_findinsertloc(rel, &buf, &offset, natts, itup_scankey, itup); ! _bt_insertonpg(rel, buf, stack, itup, offset, false); /* be tidy */ _bt_freestack(stack); _bt_freeskey(itup_scankey); } /* --- 146,209 ---- * * If we must wait for another xact, we release the lock while waiting, * and then must start over completely. + * + * For a partial uniqueness check, don't wait for the other xact. Just let + * the tuple in and return false for possibly non-unique, or true for + * definitely unique. */ ! if (unique_check != UNIQUE_CHECK_NO) { TransactionId xwait; + bool is_unique; offset = _bt_binsrch(rel, buf, natts, itup_scankey, false); ! xwait = _bt_check_unique(rel, itup, heapRel, buf, offset, itup_scankey, ! unique_check, &is_unique); ! if (!is_unique) { ! if (TransactionIdIsValid(xwait)) ! { ! /* Have to wait for the other guy ... */ ! _bt_relbuf(rel, buf); ! XactLockTableWait(xwait); ! /* start over... */ ! _bt_freestack(stack); ! goto top; ! } ! ! /* ! * This is a partial uniqueness check and it appears to be a ! * duplicate. Proceed with the insert, but return false - the ! * caller will need to re-check the constraint later. ! */ ! ok = false; } } ! /* ! * If unique_check is UNIQUE_CHECK_EXISTING, then this is a re-check ! * of a previous potential conflict, rather than a real insert. So ! * either the check above will have thrown an error or the insert has ! * been done already and uniquess is satisfied. Don't re-insert. ! */ ! if (unique_check == UNIQUE_CHECK_EXISTING) ! { ! /* just release the buffer */ ! _bt_relbuf(rel, buf); ! } ! else ! { ! /* do the insertion */ ! _bt_findinsertloc(rel, &buf, &offset, natts, itup_scankey, itup); ! _bt_insertonpg(rel, buf, stack, itup, offset, false); ! } /* be tidy */ _bt_freestack(stack); _bt_freeskey(itup_scankey); + + return ok; } /* *************** *** 169,182 **** * also point to end-of-page, which means that the first tuple to check * is the first tuple on the next page. * ! * Returns InvalidTransactionId if there is no conflict, else an xact ID ! * we must wait for to see if it commits a conflicting tuple. If an actual ! * conflict is detected, no return --- just ereport(). */ static TransactionId _bt_check_unique(Relation rel, IndexTuple itup, Relation heapRel, ! Buffer buf, OffsetNumber offset, ScanKey itup_scankey) { TupleDesc itupdesc = RelationGetDescr(rel); int natts = rel->rd_rel->relnatts; SnapshotData SnapshotDirty; --- 213,234 ---- * also point to end-of-page, which means that the first tuple to check * is the first tuple on the next page. * ! * For a full uniqueness check, this returns InvalidTransactionId if ! * there is no conflict, else an xact ID we must wait for to see if it ! * commits a conflicting tuple. If an actual conflict is detected, ! * no return --- just ereport(). ! * ! * For a partial uniqueness check, we just set is_unique to false, ! * indicating a potential conflict. _bt_doinsert() will then not block, ! * but just go ahead with the insert. The proper check will be done later ! * (at the end of the statement or transaction). */ static TransactionId _bt_check_unique(Relation rel, IndexTuple itup, Relation heapRel, ! Buffer buf, OffsetNumber offset, ScanKey itup_scankey, ! IndexUniqueCheck unique_check, bool *is_unique) { + TransactionId xwait = InvalidTransactionId; TupleDesc itupdesc = RelationGetDescr(rel); int natts = rel->rd_rel->relnatts; SnapshotData SnapshotDirty; *************** *** 184,189 **** --- 236,242 ---- Page page; BTPageOpaque opaque; Buffer nbuf = InvalidBuffer; + bool found = false; InitDirtySnapshot(SnapshotDirty); *************** *** 191,196 **** --- 244,252 ---- opaque = (BTPageOpaque) PageGetSpecialPointer(page); maxoff = PageGetMaxOffsetNumber(page); + /* Assume unique unless we find a duplicate */ + *is_unique = true; + /* * Scan over all equal tuples, looking for live conflicts. */ *************** *** 247,263 **** */ if (heap_hot_search(&htid, heapRel, &SnapshotDirty, &all_dead)) { ! /* it is a duplicate */ ! TransactionId xwait = ! (TransactionIdIsValid(SnapshotDirty.xmin)) ? ! SnapshotDirty.xmin : SnapshotDirty.xmax; /* ! * If this tuple is being updated by other transaction ! * then we have to wait for its commit/abort. */ ! if (TransactionIdIsValid(xwait)) { if (nbuf != InvalidBuffer) _bt_relbuf(rel, nbuf); /* Tell _bt_doinsert to wait... */ --- 303,357 ---- */ if (heap_hot_search(&htid, heapRel, &SnapshotDirty, &all_dead)) { ! /* ! * We have a duplicate. If we are only doing a partial ! * check, then don't bother checking if the tuple is ! * being updated in another transaction. Just return ! * the fact that it is a potential conflict and leave ! * the full check till later. This is used for deferrable ! * unique constraints. ! */ ! if (unique_check == UNIQUE_CHECK_PARTIAL) ! { ! *is_unique = false; ! if (nbuf != InvalidBuffer) ! _bt_relbuf(rel, nbuf); ! return InvalidTransactionId; ! } /* ! * Otherwise we need to do a proper check to see if ! * it really is a duplicate. First check if it is ! * being updated by another transaction, in which case ! * we may have to wait for it's commit/abort. */ ! if (TransactionIdIsValid(SnapshotDirty.xmin) || ! TransactionIdIsValid(SnapshotDirty.xmax)) { + xwait = TransactionIdIsValid(SnapshotDirty.xmin) ? + SnapshotDirty.xmin : SnapshotDirty.xmax; + + /* + * If this is a deferred uniqueness check, of a + * tuple that we have already inserted, then there + * may be other conflicting tuples which we can + * see without waiting, so reset the snapshot and + * keep looking, but remember that we may need to + * wait on this one after all. + */ + if (unique_check == UNIQUE_CHECK_EXISTING) + { + SnapshotDirty.xmin = InvalidTransactionId; + SnapshotDirty.xmax = InvalidTransactionId; + + goto next_tuple; + } + + /* + * Otherwise return the other transaction ID + * that we have to wait for. + */ + *is_unique = false; if (nbuf != InvalidBuffer) _bt_relbuf(rel, nbuf); /* Tell _bt_doinsert to wait... */ *************** *** 265,270 **** --- 359,377 ---- } /* + * We have a definite match. If this is a deferred check, + * of a tuple that we have already inserted, the first + * match is not a conflict, so look for another. This + * is used for the final check of a deferrable uniquenes + * constraint. + */ + if (unique_check == UNIQUE_CHECK_EXISTING && !found) + { + found = true; + goto next_tuple; + } + + /* * Otherwise we have a definite conflict. But before * complaining, look to see if the tuple we want to insert * is itself now committed dead --- if so, don't complain. *************** *** 280,298 **** * we have a unique key conflict. The other live tuple is * not part of this chain because it had a different index * entry. */ ! htid = itup->t_tid; ! if (heap_hot_search(&htid, heapRel, SnapshotSelf, NULL)) ! { ! /* Normal case --- it's still live */ ! } ! else { ! /* ! * It's been deleted, so no error, and no need to ! * continue searching ! */ ! break; } ereport(ERROR, --- 387,411 ---- * we have a unique key conflict. The other live tuple is * not part of this chain because it had a different index * entry. + * + * NOTE: Unique indexes for deferrable constraints can't + * be built concurrently, so we can skip this for those. */ ! if (unique_check == UNIQUE_CHECK_YES) { ! htid = itup->t_tid; ! if (heap_hot_search(&htid, heapRel, SnapshotSelf, NULL)) ! { ! /* Normal case --- it's still live */ ! } ! else ! { ! /* ! * It's been deleted, so no error, and no need to ! * continue searching ! */ ! break; ! } } ereport(ERROR, *************** *** 318,323 **** --- 431,437 ---- } } + next_tuple: /* * Advance to next tuple to continue checking. */ *************** *** 352,357 **** --- 466,483 ---- if (nbuf != InvalidBuffer) _bt_relbuf(rel, nbuf); + /* + * If we reach here, then we didn't find a duplicate. However, in a + * deferred uniqueness check, we may have found 1 visible tuple plus + * another being updated in another transaction, in which case we need + * to wait for it's commit/abort. + */ + if (found && TransactionIdIsValid(xwait)) + { + *is_unique = false; + return xwait; + } + return InvalidTransactionId; } *** ./src/backend/access/nbtree/nbtree.c.orig 2009-07-04 15:43:47.000000000 +0100 --- ./src/backend/access/nbtree/nbtree.c 2009-07-04 17:19:10.000000000 +0100 *************** *** 212,234 **** Datum btinsert(PG_FUNCTION_ARGS) { ! Relation rel = (Relation) PG_GETARG_POINTER(0); ! Datum *values = (Datum *) PG_GETARG_POINTER(1); ! bool *isnull = (bool *) PG_GETARG_POINTER(2); ! ItemPointer ht_ctid = (ItemPointer) PG_GETARG_POINTER(3); ! Relation heapRel = (Relation) PG_GETARG_POINTER(4); ! bool checkUnique = PG_GETARG_BOOL(5); ! IndexTuple itup; /* generate an index tuple */ itup = index_form_tuple(RelationGetDescr(rel), values, isnull); itup->t_tid = *ht_ctid; ! _bt_doinsert(rel, itup, checkUnique, heapRel); pfree(itup); ! PG_RETURN_BOOL(true); } /* --- 212,235 ---- Datum btinsert(PG_FUNCTION_ARGS) { ! Relation rel = (Relation) PG_GETARG_POINTER(0); ! Datum *values = (Datum *) PG_GETARG_POINTER(1); ! bool *isnull = (bool *) PG_GETARG_POINTER(2); ! ItemPointer ht_ctid = (ItemPointer) PG_GETARG_POINTER(3); ! Relation heapRel = (Relation) PG_GETARG_POINTER(4); ! IndexUniqueCheck checkUnique = (IndexUniqueCheck) PG_GETARG_INT32(5); ! IndexTuple itup; ! bool ok; /* generate an index tuple */ itup = index_form_tuple(RelationGetDescr(rel), values, isnull); itup->t_tid = *ht_ctid; ! ok = _bt_doinsert(rel, itup, checkUnique, heapRel); pfree(itup); ! PG_RETURN_BOOL(ok); } /* *** ./src/backend/bootstrap/bootparse.y.orig 2009-07-04 15:43:47.000000000 +0100 --- ./src/backend/bootstrap/bootparse.y 2009-07-04 16:54:25.000000000 +0100 *************** *** 266,272 **** NULL, $10, NULL, NIL, ! false, false, false, false, false, true, false, false); do_end(); } --- 266,272 ---- NULL, $10, NULL, NIL, ! false, false, false, false, false, false, false, true, false, false); do_end(); } *************** *** 284,290 **** NULL, $11, NULL, NIL, ! true, false, false, false, false, true, false, false); do_end(); } --- 284,290 ---- NULL, $11, NULL, NIL, ! true, false, false, false, false, false, false, true, false, false); do_end(); } *** ./src/backend/catalog/index.c.orig 2009-07-04 15:43:47.000000000 +0100 --- ./src/backend/catalog/index.c 2009-07-04 19:13:10.000000000 +0100 *************** *** 40,53 **** --- 40,57 ---- #include "catalog/pg_operator.h" #include "catalog/pg_opclass.h" #include "catalog/pg_tablespace.h" + #include "catalog/pg_trigger.h" #include "catalog/pg_type.h" #include "catalog/storage.h" #include "commands/tablecmds.h" + #include "commands/trigger.h" #include "executor/executor.h" #include "miscadmin.h" + #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" #include "optimizer/clauses.h" #include "optimizer/var.h" + #include "parser/gramparse.h" #include "storage/bufmgr.h" #include "storage/lmgr.h" #include "storage/procarray.h" *************** *** 726,733 **** conOid = CreateConstraintEntry(indexRelationName, namespaceId, constraintType, ! false, /* isDeferrable */ ! false, /* isDeferred */ heapRelationId, indexInfo->ii_KeyAttrNumbers, indexInfo->ii_NumIndexAttrs, --- 730,737 ---- conOid = CreateConstraintEntry(indexRelationName, namespaceId, constraintType, ! indexInfo->ii_Deferrable, ! indexInfo->ii_InitDeferred, heapRelationId, indexInfo->ii_KeyAttrNumbers, indexInfo->ii_NumIndexAttrs, *************** *** 753,758 **** --- 757,807 ---- referenced.objectSubId = 0; recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL); + + /* + * If the constraint is deferrable, define the deferred uniqueness + * checking triggers. The index will temporarily allow duplicates and + * then these triggers will re-check for duplicates at the end of the + * statement or transaction. These triggers are only queued if + * potentially non-unique entries are detected during index insertion. + */ + if (indexInfo->ii_Deferrable) + { + char *nsname = get_namespace_name(namespaceId); + RangeVar *heapRel = makeRangeVar(nsname, pstrdup(RelationGetRelationName(heapRelation)), -1); + RangeVar *indexRel = makeRangeVar(nsname, pstrdup(indexRelationName), -1); + CreateTrigStmt *trigger; + + /* + * Advance the command counter so that we can see the + * newly-entered catalog tuples for the index. + */ + CommandCounterIncrement(); + + trigger = makeNode(CreateTrigStmt); + trigger->trigname = pstrdup(indexRelationName); + trigger->relation = heapRel; + trigger->funcname = SystemFuncName("unique_key_check_ins"); + trigger->args = NIL; + trigger->before = false; + trigger->row = true; + trigger->events = TRIGGER_TYPE_INSERT; + trigger->isconstraint = true; + trigger->deferrable = true; + trigger->initdeferred = indexInfo->ii_InitDeferred; + trigger->constrrel = indexRel; + + (void) CreateTrigger(trigger, conOid, false); + + CommandCounterIncrement(); + + trigger->funcname = SystemFuncName("unique_key_check_upd"); + trigger->events = TRIGGER_TYPE_UPDATE; + + (void) CreateTrigger(trigger, conOid, false); + + CommandCounterIncrement(); + } } else { *************** *** 1026,1031 **** --- 1075,1104 ---- ii->ii_Concurrent = false; ii->ii_BrokenHotChain = false; + /* fetch the details of the index constraint if any */ + ii->ii_ConstraintId = get_index_constraint(RelationGetRelid(index)); + if (OidIsValid(ii->ii_ConstraintId)) + { + HeapTuple ht_constr = SearchSysCache(CONSTROID, + ObjectIdGetDatum(ii->ii_ConstraintId), + 0, 0, 0); + Form_pg_constraint conrec; + + if (!HeapTupleIsValid(ht_constr)) + elog(ERROR, "cache lookup failed for constraint %u", ii->ii_ConstraintId); + conrec = (Form_pg_constraint) GETSTRUCT(ht_constr); + + ii->ii_Deferrable = conrec->condeferrable; + ii->ii_InitDeferred = conrec->condeferred; + + ReleaseSysCache(ht_constr); + } + else + { + ii->ii_Deferrable = false; + ii->ii_InitDeferred = false; + } + return ii; } *************** *** 2190,2196 **** isnull, &rootTuple, heapRelation, ! indexInfo->ii_Unique); state->tups_inserted += 1; } --- 2263,2270 ---- isnull, &rootTuple, heapRelation, ! indexInfo->ii_Unique ? ! UNIQUE_CHECK_YES : UNIQUE_CHECK_NO); state->tups_inserted += 1; } *** ./src/backend/catalog/indexing.c.orig 2009-07-04 15:43:47.000000000 +0100 --- ./src/backend/catalog/indexing.c 2009-07-04 16:57:21.000000000 +0100 *************** *** 134,140 **** isnull, /* is-null flags */ &(heapTuple->t_self), /* tid of heap tuple */ heapRelation, ! relationDescs[i]->rd_index->indisunique); } ExecDropSingleTupleTableSlot(slot); --- 134,141 ---- isnull, /* is-null flags */ &(heapTuple->t_self), /* tid of heap tuple */ heapRelation, ! relationDescs[i]->rd_index->indisunique ? ! UNIQUE_CHECK_YES : UNIQUE_CHECK_NO); } ExecDropSingleTupleTableSlot(slot); *** ./src/backend/catalog/toasting.c.orig 2009-07-04 15:43:47.000000000 +0100 --- ./src/backend/catalog/toasting.c 2009-07-04 16:56:10.000000000 +0100 *************** *** 241,246 **** --- 241,249 ---- indexInfo->ii_ReadyForInserts = true; indexInfo->ii_Concurrent = false; indexInfo->ii_BrokenHotChain = false; + indexInfo->ii_ConstraintId = InvalidOid; + indexInfo->ii_Deferrable = false; + indexInfo->ii_InitDeferred = false; classObjectId[0] = OID_BTREE_OPS_OID; classObjectId[1] = INT4_BTREE_OPS_OID; *** ./src/backend/commands/copy.c.orig 2009-07-04 15:44:36.000000000 +0100 --- ./src/backend/commands/copy.c 2009-07-04 18:15:59.000000000 +0100 *************** *** 2119,2124 **** --- 2119,2126 ---- if (!skip_tuple) { + List *problemIndexList = NIL; + /* Place tuple in tuple slot */ ExecStoreTuple(tuple, slot, InvalidBuffer, false); *************** *** 2130,2139 **** heap_insert(cstate->rel, tuple, mycid, hi_options, bistate); if (resultRelInfo->ri_NumIndices > 0) ! ExecInsertIndexTuples(slot, &(tuple->t_self), estate, false); /* AFTER ROW INSERT Triggers */ ! ExecARInsertTriggers(estate, resultRelInfo, tuple); /* * We count only tuples not suppressed by a BEFORE INSERT trigger; --- 2132,2141 ---- heap_insert(cstate->rel, tuple, mycid, hi_options, bistate); if (resultRelInfo->ri_NumIndices > 0) ! problemIndexList = ExecInsertIndexTuples(slot, &(tuple->t_self), estate, false); /* AFTER ROW INSERT Triggers */ ! ExecARInsertTriggers(estate, resultRelInfo, tuple, problemIndexList); /* * We count only tuples not suppressed by a BEFORE INSERT trigger; *** ./src/backend/commands/indexcmds.c.orig 2009-07-04 15:44:36.000000000 +0100 --- ./src/backend/commands/indexcmds.c 2009-07-04 18:37:43.000000000 +0100 *************** *** 87,92 **** --- 87,94 ---- * 'primary': mark the index as a primary key in the catalogs. * 'isconstraint': index is for a PRIMARY KEY or UNIQUE constraint, * so build a pg_constraint entry for it. + * 'constrDeferrable': constraint is DEFERRABLE (UNIQUE constraint). + * 'constrInitDeferred': constraint is INITIALLY DEFERRED (UNIQUE constraint). * 'is_alter_table': this is due to an ALTER rather than a CREATE operation. * 'check_rights': check for CREATE rights in the namespace. (This should * be true except when ALTER is deleting/recreating an index.) *************** *** 107,112 **** --- 109,116 ---- bool unique, bool primary, bool isconstraint, + bool constrDeferrable, + bool constrInitDeferred, bool is_alter_table, bool check_rights, bool skip_build, *************** *** 419,424 **** --- 423,431 ---- indexInfo->ii_ReadyForInserts = !concurrent; indexInfo->ii_Concurrent = concurrent; indexInfo->ii_BrokenHotChain = false; + indexInfo->ii_ConstraintId = InvalidOid; /* index_create() may create this */ + indexInfo->ii_Deferrable = constrDeferrable; + indexInfo->ii_InitDeferred = constrInitDeferred; classObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid)); coloptions = (int16 *) palloc(numberOfAttributes * sizeof(int16)); *** ./src/backend/commands/tablecmds.c.orig 2009-07-04 15:44:36.000000000 +0100 --- ./src/backend/commands/tablecmds.c 2009-07-04 18:11:06.000000000 +0100 *************** *** 4371,4376 **** --- 4371,4378 ---- stmt->unique, stmt->primary, stmt->isconstraint, + stmt->constrDeferrable, + stmt->constrInitDeferred, true, /* is_alter_table */ check_rights, skip_build, *** ./src/backend/commands/trigger.c.orig 2009-07-04 15:44:36.000000000 +0100 --- ./src/backend/commands/trigger.c 2009-07-05 22:55:24.000000000 +0100 *************** *** 63,69 **** Instrumentation *instr, MemoryContext per_tuple_context); static void AfterTriggerSaveEvent(ResultRelInfo *relinfo, int event, ! bool row_trigger, HeapTuple oldtup, HeapTuple newtup); /* --- 63,70 ---- Instrumentation *instr, MemoryContext per_tuple_context); static void AfterTriggerSaveEvent(ResultRelInfo *relinfo, int event, ! bool row_trigger, HeapTuple oldtup, HeapTuple newtup, ! List *problemIndexList); /* *************** *** 211,224 **** trigoid = GetNewOid(tgrel); /* ! * If trigger is for an RI constraint, the passed-in name is the ! * constraint name; save that and build a unique trigger name to avoid ! * collisions with user-selected trigger names. */ if (OidIsValid(constraintOid)) { snprintf(constrtrigname, sizeof(constrtrigname), ! "RI_ConstraintTrigger_%u", trigoid); trigname = constrtrigname; constrname = stmt->trigname; } --- 212,258 ---- trigoid = GetNewOid(tgrel); /* ! * If trigger is for a constraint, the passed-in name is the constraint ! * name; save that and build a unique trigger name based on the type of ! * the constraint to avoid collisions with user-selected trigger names. */ if (OidIsValid(constraintOid)) { + HeapTuple ht_constr = SearchSysCache(CONSTROID, + ObjectIdGetDatum(constraintOid), + 0, 0, 0); + Form_pg_constraint conrec; + char *prefix; + + if (!HeapTupleIsValid(ht_constr)) + elog(ERROR, "cache lookup failed for constraint %u", constraintOid); + conrec = (Form_pg_constraint) GETSTRUCT(ht_constr); + + /* + * Prefix for trigger name, based on constraint type. + */ + switch (conrec->contype) + { + case CONSTRAINT_CHECK: + prefix = "Check_ConstraintTrigger"; + break; + case CONSTRAINT_FOREIGN: + prefix = "RI_ConstraintTrigger"; + break; + case CONSTRAINT_PRIMARY: + prefix = "PK_ConstraintTrigger"; + break; + case CONSTRAINT_UNIQUE: + prefix = "Unique_ConstraintTrigger"; + break; + default: + prefix = "ConstraintTrigger"; + break; + } + ReleaseSysCache(ht_constr); + snprintf(constrtrigname, sizeof(constrtrigname), ! "%s_%u", prefix, trigoid); trigname = constrtrigname; constrname = stmt->trigname; } *************** *** 1639,1645 **** if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_INSERT] > 0) AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_INSERT, ! false, NULL, NULL); } HeapTuple --- 1673,1679 ---- if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_INSERT] > 0) AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_INSERT, ! false, NULL, NULL, NIL); } HeapTuple *************** *** 1695,1707 **** void ExecARInsertTriggers(EState *estate, ResultRelInfo *relinfo, ! HeapTuple trigtuple) { TriggerDesc *trigdesc = relinfo->ri_TrigDesc; if (trigdesc && trigdesc->n_after_row[TRIGGER_EVENT_INSERT] > 0) AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_INSERT, ! true, NULL, trigtuple); } void --- 1729,1741 ---- void ExecARInsertTriggers(EState *estate, ResultRelInfo *relinfo, ! HeapTuple trigtuple, List *problemIndexList) { TriggerDesc *trigdesc = relinfo->ri_TrigDesc; if (trigdesc && trigdesc->n_after_row[TRIGGER_EVENT_INSERT] > 0) AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_INSERT, ! true, NULL, trigtuple, problemIndexList); } void *************** *** 1770,1776 **** if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_DELETE] > 0) AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_DELETE, ! false, NULL, NULL); } bool --- 1804,1810 ---- if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_DELETE] > 0) AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_DELETE, ! false, NULL, NULL, NIL); } bool *************** *** 1847,1853 **** tupleid, NULL); AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_DELETE, ! true, trigtuple, NULL); heap_freetuple(trigtuple); } } --- 1881,1887 ---- tupleid, NULL); AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_DELETE, ! true, trigtuple, NULL, NIL); heap_freetuple(trigtuple); } } *************** *** 1918,1924 **** if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_UPDATE] > 0) AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_UPDATE, ! false, NULL, NULL); } HeapTuple --- 1952,1958 ---- if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_UPDATE] > 0) AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_UPDATE, ! false, NULL, NULL, NIL); } HeapTuple *************** *** 1988,1994 **** void ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo, ! ItemPointer tupleid, HeapTuple newtuple) { TriggerDesc *trigdesc = relinfo->ri_TrigDesc; --- 2022,2029 ---- void ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo, ! ItemPointer tupleid, HeapTuple newtuple, ! List *problemIndexList) { TriggerDesc *trigdesc = relinfo->ri_TrigDesc; *************** *** 1998,2004 **** tupleid, NULL); AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_UPDATE, ! true, trigtuple, newtuple); heap_freetuple(trigtuple); } } --- 2033,2039 ---- tupleid, NULL); AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_UPDATE, ! true, trigtuple, newtuple, problemIndexList); heap_freetuple(trigtuple); } } *************** *** 2069,2075 **** if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_TRUNCATE] > 0) AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_TRUNCATE, ! false, NULL, NULL); } --- 2104,2110 ---- if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_TRUNCATE] > 0) AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_TRUNCATE, ! false, NULL, NULL, NIL); } *************** *** 3790,3796 **** */ static void AfterTriggerSaveEvent(ResultRelInfo *relinfo, int event, bool row_trigger, ! HeapTuple oldtup, HeapTuple newtup) { Relation rel = relinfo->ri_RelationDesc; TriggerDesc *trigdesc = relinfo->ri_TrigDesc; --- 3825,3832 ---- */ static void AfterTriggerSaveEvent(ResultRelInfo *relinfo, int event, bool row_trigger, ! HeapTuple oldtup, HeapTuple newtup, ! List *problemIndexList) { Relation rel = relinfo->ri_RelationDesc; TriggerDesc *trigdesc = relinfo->ri_TrigDesc; *************** *** 3951,3956 **** --- 3987,4004 ---- } /* + * If the trigger is a deferred unique constraint check trigger, + * only execute it if the unique constraint was potentially + * violated, which we know from index insertion time. + */ + if (trigger->tgfoid == F_UNIQUE_KEY_CHECK_INS || + trigger->tgfoid == F_UNIQUE_KEY_CHECK_UPD) + { + if (!list_member_oid(problemIndexList, trigger->tgconstrrelid)) + continue; /* Uniqueness definitely not violated */ + } + + /* * Fill in event structure and add it to the current query's queue. */ new_shared.ats_event = *** ./src/backend/executor/execMain.c.orig 2009-07-04 15:43:47.000000000 +0100 --- ./src/backend/executor/execMain.c 2009-07-04 17:45:41.000000000 +0100 *************** *** 1753,1758 **** --- 1753,1759 ---- ResultRelInfo *resultRelInfo; Relation resultRelationDesc; Oid newId; + List *problemIndexList = NIL; /* * get the heap tuple out of the tuple table slot, making sure we have a *************** *** 1834,1843 **** * insert index entries for tuple */ if (resultRelInfo->ri_NumIndices > 0) ! ExecInsertIndexTuples(slot, &(tuple->t_self), estate, false); /* AFTER ROW INSERT Triggers */ ! ExecARInsertTriggers(estate, resultRelInfo, tuple); /* Process RETURNING if present */ if (resultRelInfo->ri_projectReturning) --- 1835,1845 ---- * insert index entries for tuple */ if (resultRelInfo->ri_NumIndices > 0) ! problemIndexList = ExecInsertIndexTuples(slot, &(tuple->t_self), ! estate, false); /* AFTER ROW INSERT Triggers */ ! ExecARInsertTriggers(estate, resultRelInfo, tuple, problemIndexList); /* Process RETURNING if present */ if (resultRelInfo->ri_projectReturning) *************** *** 1999,2004 **** --- 2001,2007 ---- HTSU_Result result; ItemPointerData update_ctid; TransactionId update_xmax; + List *problemIndexList = NIL; /* * abort the operation if not running transactions *************** *** 2132,2141 **** * 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); /* AFTER ROW UPDATE Triggers */ ! ExecARUpdateTriggers(estate, resultRelInfo, tupleid, tuple); /* Process RETURNING if present */ if (resultRelInfo->ri_projectReturning) --- 2135,2145 ---- * If it's a HOT update, we mustn't insert new index entries. */ if (resultRelInfo->ri_NumIndices > 0 && !HeapTupleIsHeapOnly(tuple)) ! problemIndexList = ExecInsertIndexTuples(slot, &(tuple->t_self), ! estate, false); /* AFTER ROW UPDATE Triggers */ ! ExecARUpdateTriggers(estate, resultRelInfo, tupleid, tuple, problemIndexList); /* Process RETURNING if present */ if (resultRelInfo->ri_projectReturning) *** ./src/backend/executor/execUtils.c.orig 2009-07-04 15:43:47.000000000 +0100 --- ./src/backend/executor/execUtils.c 2009-07-04 17:40:14.000000000 +0100 *************** *** 1030,1038 **** * 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 ExecInsertIndexTuples(TupleTableSlot *slot, ItemPointer tupleid, EState *estate, --- 1030,1042 ---- * 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? + * + * This returns a list of OIDs for any unique indexes + * whose constraint check was deferred and which had + * potential (unconfirmed) conflicts. * ---------------------------------------------------------------- */ ! List * ExecInsertIndexTuples(TupleTableSlot *slot, ItemPointer tupleid, EState *estate, *************** *** 1047,1052 **** --- 1051,1057 ---- ExprContext *econtext; Datum values[INDEX_MAX_KEYS]; bool isnull[INDEX_MAX_KEYS]; + List *result = NIL; /* * Get information from the result relation info structure. *************** *** 1072,1077 **** --- 1077,1084 ---- for (i = 0; i < numIndices; i++) { IndexInfo *indexInfo; + bool defer_unique_check; + bool ok; if (relationDescs[i] == NULL) continue; *************** *** 1119,1137 **** * 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. */ ! 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); /* * keep track of index inserts for debugging */ IncrIndexInserted(); } } /* --- 1126,1165 ---- * 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. + * + * If there is a deferrable unique constraint associated with the + * index, then we don't enforce uniqueness here, we just record if + * the tuple was possibly not unique. */ ! defer_unique_check = relationDescs[i]->rd_index->indisunique && ! !is_vacuum && indexInfo->ii_Deferrable; ! ! ok = index_insert(relationDescs[i], /* index relation */ ! values, /* array of index Datums */ ! isnull, /* null flags */ ! tupleid, /* tid of heap tuple */ ! heapRelation, ! is_vacuum ? UNIQUE_CHECK_NO : ! defer_unique_check ? UNIQUE_CHECK_PARTIAL : ! relationDescs[i]->rd_index->indisunique ? ! UNIQUE_CHECK_YES : UNIQUE_CHECK_NO); ! ! if (defer_unique_check && !ok) ! { ! /* ! * The tuple is potentially violates the uniqueness constraint. ! * Make a note of this index so that we can re-check it later. ! */ ! result = lappend_oid(result, relationDescs[i]->rd_index->indexrelid); ! } /* * keep track of index inserts for debugging */ IncrIndexInserted(); } + + return result; } /* *** ./src/backend/nodes/copyfuncs.c.orig 2009-07-04 15:44:36.000000000 +0100 --- ./src/backend/nodes/copyfuncs.c 2009-07-04 17:54:33.000000000 +0100 *************** *** 2091,2096 **** --- 2091,2098 ---- COPY_NODE_FIELD(keys); COPY_NODE_FIELD(options); COPY_STRING_FIELD(indexspace); + COPY_SCALAR_FIELD(deferrable); + COPY_SCALAR_FIELD(initdeferred); return newnode; } *************** *** 2509,2514 **** --- 2511,2518 ---- COPY_SCALAR_FIELD(unique); COPY_SCALAR_FIELD(primary); COPY_SCALAR_FIELD(isconstraint); + COPY_SCALAR_FIELD(constrDeferrable); + COPY_SCALAR_FIELD(constrInitDeferred); COPY_SCALAR_FIELD(concurrent); return newnode; *** ./src/backend/nodes/equalfuncs.c.orig 2009-07-04 15:44:36.000000000 +0100 --- ./src/backend/nodes/equalfuncs.c 2009-07-04 17:52:16.000000000 +0100 *************** *** 1159,1164 **** --- 1159,1166 ---- COMPARE_SCALAR_FIELD(unique); COMPARE_SCALAR_FIELD(primary); COMPARE_SCALAR_FIELD(isconstraint); + COMPARE_SCALAR_FIELD(constrDeferrable); + COMPARE_SCALAR_FIELD(constrInitDeferred); COMPARE_SCALAR_FIELD(concurrent); return true; *************** *** 2068,2073 **** --- 2070,2077 ---- COMPARE_NODE_FIELD(keys); COMPARE_NODE_FIELD(options); COMPARE_STRING_FIELD(indexspace); + COMPARE_SCALAR_FIELD(deferrable); + COMPARE_SCALAR_FIELD(initdeferred); return true; } *** ./src/backend/nodes/outfuncs.c.orig 2009-07-04 15:44:36.000000000 +0100 --- ./src/backend/nodes/outfuncs.c 2009-07-04 17:50:04.000000000 +0100 *************** *** 1734,1739 **** --- 1734,1741 ---- WRITE_BOOL_FIELD(unique); WRITE_BOOL_FIELD(primary); WRITE_BOOL_FIELD(isconstraint); + WRITE_BOOL_FIELD(constrDeferrable); + WRITE_BOOL_FIELD(constrInitDeferred); WRITE_BOOL_FIELD(concurrent); } *************** *** 2292,2297 **** --- 2294,2301 ---- WRITE_NODE_FIELD(keys); WRITE_NODE_FIELD(options); WRITE_STRING_FIELD(indexspace); + WRITE_BOOL_FIELD(deferrable); + WRITE_BOOL_FIELD(initdeferred); break; case CONSTR_CHECK: *** ./src/backend/parser/gram.y.orig 2009-07-04 15:43:47.000000000 +0100 --- ./src/backend/parser/gram.y 2009-07-04 17:37:32.000000000 +0100 *************** *** 2173,2178 **** --- 2173,2180 ---- n->cooked_expr = NULL; n->keys = NULL; n->indexspace = NULL; + n->deferrable = FALSE; + n->initdeferred = FALSE; $$ = (Node *)n; } | NULL_P *************** *** 2184,2189 **** --- 2186,2193 ---- n->cooked_expr = NULL; n->keys = NULL; n->indexspace = NULL; + n->deferrable = FALSE; + n->initdeferred = FALSE; $$ = (Node *)n; } | UNIQUE opt_definition OptConsTableSpace *************** *** 2196,2201 **** --- 2200,2207 ---- n->keys = NULL; n->options = $2; n->indexspace = $3; + n->deferrable = FALSE; + n->initdeferred = FALSE; $$ = (Node *)n; } | PRIMARY KEY opt_definition OptConsTableSpace *************** *** 2208,2213 **** --- 2214,2221 ---- n->keys = NULL; n->options = $3; n->indexspace = $4; + n->deferrable = FALSE; + n->initdeferred = FALSE; $$ = (Node *)n; } | CHECK '(' a_expr ')' *************** *** 2219,2224 **** --- 2227,2234 ---- n->cooked_expr = NULL; n->keys = NULL; n->indexspace = NULL; + n->deferrable = FALSE; + n->initdeferred = FALSE; $$ = (Node *)n; } | DEFAULT b_expr *************** *** 2230,2235 **** --- 2240,2247 ---- n->cooked_expr = NULL; n->keys = NULL; n->indexspace = NULL; + n->deferrable = FALSE; + n->initdeferred = FALSE; $$ = (Node *)n; } | REFERENCES qualified_name opt_column_list key_match key_actions *************** *** 2359,2367 **** --- 2371,2382 ---- n->raw_expr = $3; n->cooked_expr = NULL; n->indexspace = NULL; + n->deferrable = FALSE; + n->initdeferred = FALSE; $$ = (Node *)n; } | UNIQUE '(' columnList ')' opt_definition OptConsTableSpace + ConstraintAttributeSpec { Constraint *n = makeNode(Constraint); n->contype = CONSTR_UNIQUE; *************** *** 2371,2376 **** --- 2386,2393 ---- n->keys = $3; n->options = $5; n->indexspace = $6; + n->deferrable = ($7 & 1) != 0; + n->initdeferred = ($7 & 2) != 0; $$ = (Node *)n; } | PRIMARY KEY '(' columnList ')' opt_definition OptConsTableSpace *************** *** 2383,2388 **** --- 2400,2407 ---- n->keys = $4; n->options = $6; n->indexspace = $7; + n->deferrable = FALSE; + n->initdeferred = FALSE; $$ = (Node *)n; } | FOREIGN KEY '(' columnList ')' REFERENCES qualified_name *** ./src/backend/parser/parse_utilcmd.c.orig 2009-07-04 15:43:47.000000000 +0100 --- ./src/backend/parser/parse_utilcmd.c 2009-07-04 17:25:31.000000000 +0100 *************** *** 34,39 **** --- 34,40 ---- #include "catalog/index.h" #include "catalog/namespace.h" #include "catalog/pg_opclass.h" + #include "catalog/pg_constraint.h" #include "catalog/pg_type.h" #include "commands/defrem.h" #include "commands/tablecmds.h" *************** *** 809,815 **** else if (!index->unique) index->isconstraint = false; else ! index->isconstraint = OidIsValid(get_index_constraint(source_relid)); /* Get the index expressions, if any */ datum = SysCacheGetAttr(INDEXRELID, ht_idx, --- 810,843 ---- else if (!index->unique) index->isconstraint = false; else ! { ! /* ! * The index is marked UNIQUE, so there may be an associated unique ! * constraint. If there is, we need to fetch its DEFERRABLE and ! * INITIALLY DEFERRED attributes. ! */ ! Oid constraintId = get_index_constraint(source_relid); ! ! if (OidIsValid(constraintId)) ! { ! HeapTuple ht_constr = SearchSysCache(CONSTROID, ! ObjectIdGetDatum(constraintId), ! 0, 0, 0); ! Form_pg_constraint conrec; ! ! if (!HeapTupleIsValid(ht_constr)) ! elog(ERROR, "cache lookup failed for constraint %u", constraintId); ! conrec = (Form_pg_constraint) GETSTRUCT(ht_constr); ! ! index->isconstraint = true; ! index->constrDeferrable = conrec->condeferrable; ! index->constrInitDeferred = conrec->condeferred; ! ! ReleaseSysCache(ht_constr); ! } ! else ! index->isconstraint = false; ! } /* Get the index expressions, if any */ datum = SysCacheGetAttr(INDEXRELID, ht_idx, *************** *** 1092,1097 **** --- 1120,1127 ---- */ } index->isconstraint = true; + index->constrDeferrable = constraint->deferrable; + index->constrInitDeferred = constraint->initdeferred; if (constraint->name != NULL) index->idxname = pstrdup(constraint->name); *************** *** 1853,1861 **** * to attach constraint attributes to their primary constraint nodes * and detect inconsistent/misplaced constraint attributes. * ! * NOTE: currently, attributes are only supported for FOREIGN KEY primary ! * constraints, but someday they ought to be supported for other constraints. */ static void transformConstraintAttrs(List *constraintList) { --- 1883,1898 ---- * to attach constraint attributes to their primary constraint nodes * and detect inconsistent/misplaced constraint attributes. * ! * NOTE: currently, attributes are only supported for FOREIGN KEY and ! * UNIQUE primary constraints, but someday they ought to be supported ! * for other constraints. */ + #define SUPPORTS_ATTRS(node) \ + ((node) != NULL && \ + (IsA((node), FkConstraint) || \ + (IsA((node), Constraint) && \ + ((Constraint *)(node))->contype == CONSTR_UNIQUE))) + static void transformConstraintAttrs(List *constraintList) { *************** *** 1882,1889 **** switch (con->contype) { case CONSTR_ATTR_DEFERRABLE: ! if (lastprimarynode == NULL || ! !IsA(lastprimarynode, FkConstraint)) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("misplaced DEFERRABLE clause"))); --- 1919,1925 ---- switch (con->contype) { case CONSTR_ATTR_DEFERRABLE: ! if (!SUPPORTS_ATTRS(lastprimarynode)) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("misplaced DEFERRABLE clause"))); *************** *** 1892,1902 **** (errcode(ERRCODE_SYNTAX_ERROR), errmsg("multiple DEFERRABLE/NOT DEFERRABLE clauses not allowed"))); saw_deferrability = true; ! ((FkConstraint *) lastprimarynode)->deferrable = true; break; case CONSTR_ATTR_NOT_DEFERRABLE: ! if (lastprimarynode == NULL || ! !IsA(lastprimarynode, FkConstraint)) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("misplaced NOT DEFERRABLE clause"))); --- 1928,1941 ---- (errcode(ERRCODE_SYNTAX_ERROR), errmsg("multiple DEFERRABLE/NOT DEFERRABLE clauses not allowed"))); saw_deferrability = true; ! if (IsA(lastprimarynode, FkConstraint)) ! ((FkConstraint *) lastprimarynode)->deferrable = true; ! else ! ((Constraint *) lastprimarynode)->deferrable = true; break; + case CONSTR_ATTR_NOT_DEFERRABLE: ! if (!SUPPORTS_ATTRS(lastprimarynode)) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("misplaced NOT DEFERRABLE clause"))); *************** *** 1905,1920 **** (errcode(ERRCODE_SYNTAX_ERROR), errmsg("multiple DEFERRABLE/NOT DEFERRABLE clauses not allowed"))); saw_deferrability = true; ! ((FkConstraint *) lastprimarynode)->deferrable = false; ! if (saw_initially && ! ((FkConstraint *) lastprimarynode)->initdeferred) ! ereport(ERROR, ! (errcode(ERRCODE_SYNTAX_ERROR), ! errmsg("constraint declared INITIALLY DEFERRED must be DEFERRABLE"))); break; case CONSTR_ATTR_DEFERRED: ! if (lastprimarynode == NULL || ! !IsA(lastprimarynode, FkConstraint)) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("misplaced INITIALLY DEFERRED clause"))); --- 1944,1971 ---- (errcode(ERRCODE_SYNTAX_ERROR), errmsg("multiple DEFERRABLE/NOT DEFERRABLE clauses not allowed"))); saw_deferrability = true; ! if (IsA(lastprimarynode, FkConstraint)) ! { ! ((FkConstraint *) lastprimarynode)->deferrable = false; ! if (saw_initially && ! ((FkConstraint *) lastprimarynode)->initdeferred) ! ereport(ERROR, ! (errcode(ERRCODE_SYNTAX_ERROR), ! errmsg("constraint declared INITIALLY DEFERRED must be DEFERRABLE"))); ! } ! else ! { ! ((Constraint *) lastprimarynode)->deferrable = false; ! if (saw_initially && ! ((Constraint *) lastprimarynode)->initdeferred) ! ereport(ERROR, ! (errcode(ERRCODE_SYNTAX_ERROR), ! errmsg("constraint declared INITIALLY DEFERRED must be DEFERRABLE"))); ! } break; + case CONSTR_ATTR_DEFERRED: ! if (!SUPPORTS_ATTRS(lastprimarynode)) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("misplaced INITIALLY DEFERRED clause"))); *************** *** 1923,1943 **** (errcode(ERRCODE_SYNTAX_ERROR), errmsg("multiple INITIALLY IMMEDIATE/DEFERRED clauses not allowed"))); saw_initially = true; - ((FkConstraint *) lastprimarynode)->initdeferred = true; /* * If only INITIALLY DEFERRED appears, assume DEFERRABLE */ ! if (!saw_deferrability) ! ((FkConstraint *) lastprimarynode)->deferrable = true; ! else if (!((FkConstraint *) lastprimarynode)->deferrable) ! ereport(ERROR, ! (errcode(ERRCODE_SYNTAX_ERROR), ! errmsg("constraint declared INITIALLY DEFERRED must be DEFERRABLE"))); break; case CONSTR_ATTR_IMMEDIATE: ! if (lastprimarynode == NULL || ! !IsA(lastprimarynode, FkConstraint)) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("misplaced INITIALLY IMMEDIATE clause"))); --- 1974,2009 ---- (errcode(ERRCODE_SYNTAX_ERROR), errmsg("multiple INITIALLY IMMEDIATE/DEFERRED clauses not allowed"))); saw_initially = true; /* * If only INITIALLY DEFERRED appears, assume DEFERRABLE */ ! if (IsA(lastprimarynode, FkConstraint)) ! { ! ((FkConstraint *) lastprimarynode)->initdeferred = true; ! ! if (!saw_deferrability) ! ((FkConstraint *) lastprimarynode)->deferrable = true; ! else if (!((FkConstraint *) lastprimarynode)->deferrable) ! ereport(ERROR, ! (errcode(ERRCODE_SYNTAX_ERROR), ! errmsg("constraint declared INITIALLY DEFERRED must be DEFERRABLE"))); ! } ! else ! { ! ((Constraint *) lastprimarynode)->initdeferred = true; ! ! if (!saw_deferrability) ! ((Constraint *) lastprimarynode)->deferrable = true; ! else if (!((Constraint *) lastprimarynode)->deferrable) ! ereport(ERROR, ! (errcode(ERRCODE_SYNTAX_ERROR), ! errmsg("constraint declared INITIALLY DEFERRED must be DEFERRABLE"))); ! } break; + case CONSTR_ATTR_IMMEDIATE: ! if (!SUPPORTS_ATTRS(lastprimarynode)) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("misplaced INITIALLY IMMEDIATE clause"))); *************** *** 1946,1953 **** (errcode(ERRCODE_SYNTAX_ERROR), errmsg("multiple INITIALLY IMMEDIATE/DEFERRED clauses not allowed"))); saw_initially = true; ! ((FkConstraint *) lastprimarynode)->initdeferred = false; break; default: /* Otherwise it's not an attribute */ lastprimarynode = node; --- 2012,2023 ---- (errcode(ERRCODE_SYNTAX_ERROR), errmsg("multiple INITIALLY IMMEDIATE/DEFERRED clauses not allowed"))); saw_initially = true; ! if (IsA(lastprimarynode, FkConstraint)) ! ((FkConstraint *) lastprimarynode)->initdeferred = false; ! else ! ((Constraint *) lastprimarynode)->initdeferred = false; break; + default: /* Otherwise it's not an attribute */ lastprimarynode = node; *** ./src/backend/tcop/utility.c.orig 2009-07-04 15:42:49.000000000 +0100 --- ./src/backend/tcop/utility.c 2009-07-04 16:51:25.000000000 +0100 *************** *** 795,800 **** --- 795,802 ---- stmt->unique, stmt->primary, stmt->isconstraint, + stmt->constrDeferrable, + stmt->constrInitDeferred, false, /* is_alter_table */ true, /* check_rights */ false, /* skip_build */ *** ./src/backend/utils/adt/ri_triggers.c.orig 2009-07-04 15:44:36.000000000 +0100 --- ./src/backend/utils/adt/ri_triggers.c 2009-07-06 17:28:50.000000000 +0100 *************** *** 30,40 **** --- 30,43 ---- #include "postgres.h" + #include "access/genam.h" #include "access/xact.h" + #include "catalog/index.h" #include "catalog/pg_constraint.h" #include "catalog/pg_operator.h" #include "catalog/pg_type.h" #include "commands/trigger.h" + #include "executor/executor.h" #include "executor/spi.h" #include "parser/parse_coerce.h" #include "parser/parse_relation.h" *************** *** 4020,4022 **** --- 4023,4174 ---- return RI_TRIGGER_NONE; } + + /* ------------------------------------------------------------------------- + * + * Triggers to check deferred UNIQUE constraints. + * + * These probably belong in a new source file, since they actually have + * little in common with the referential integrity triggers. + * + * ------------------------------------------------------------------------- + */ + + /* ---------- + * unique_key_check - + * + * Deferred check of a uniqueness constraint (for both INSERT and UPDATE). + * ---------- + */ + static Datum + unique_key_check(PG_FUNCTION_ARGS) + { + TriggerData *trigdata = (TriggerData *) fcinfo->context; + HeapTuple new_row; + Buffer new_row_buf; + Relation indexRel; + IndexInfo *indexInfo; + EState *estate; + ExprContext *econtext; + TupleTableSlot *slot; + Datum values[INDEX_MAX_KEYS]; + bool isnull[INDEX_MAX_KEYS]; + + /* + * Get the new data that was inserted/updated. + */ + if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) + { + new_row = trigdata->tg_newtuple; + new_row_buf = trigdata->tg_newtuplebuf; + } + else + { + new_row = trigdata->tg_trigtuple; + new_row_buf = trigdata->tg_trigtuplebuf; + } + + Assert(new_row_buf != InvalidBuffer); + + /* + * Open the unique index, acquiring a RowExclusiveLock, as if we were + * going to update it. This does not actually protect us against + * concurrent insertions - that is done by the index itself which + * acquires a write lock on the target page during the uniqueness check. + */ + indexRel = index_open(trigdata->tg_trigger->tgconstrrelid, + RowExclusiveLock); + indexInfo = BuildIndexInfo(indexRel); + + /* + * Typically the index won't have expressions, but if it does we need + * an EState to evaluate them. Also need a slot for the tuple we are + * going to test. + */ + if (indexInfo->ii_Expressions != NIL) + { + estate = CreateExecutorState(); + econtext = GetPerTupleExprContext(estate); + } + else + { + estate = NULL; + econtext = NULL; + } + + slot = MakeSingleTupleTableSlot(RelationGetDescr(trigdata->tg_relation)); + + if (econtext != NULL) + { + econtext->ecxt_scantuple = slot; + MemoryContextReset(econtext->ecxt_per_tuple_memory); + } + + ExecStoreTuple(new_row, slot, InvalidBuffer, false); + + /* + * Form the index values and is null flags for the index entry that + * we need to check. + */ + FormIndexDatum(indexInfo, slot, estate, values, isnull); + + /* + * Now do the uniqueness check. This is not a real insert; it is a + * check that the index entry that has already been inserted is + * unique. + */ + index_insert(indexRel, values, isnull, &(new_row->t_self), + trigdata->tg_relation, UNIQUE_CHECK_EXISTING); + + /* + * If that worked, then this index entry is unique, and we are done. + */ + ExecDropSingleTupleTableSlot(slot); + + index_close(indexRel, RowExclusiveLock); + + if (estate != NULL) + FreeExecutorState(estate); + + return PointerGetDatum(NULL); + } + + + /* ---------- + * unique_key_check_ins - + * + * Check uniqueness constraint at insert event. + * + * This applies only to UNIQUE constraints marked DEFERRABLE. + * ---------- + */ + Datum + unique_key_check_ins(PG_FUNCTION_ARGS) + { + /* + * Check that this is a valid trigger call on the right time and event. + */ + ri_CheckTrigger(fcinfo, "unique_key_check_ins", RI_TRIGTYPE_INSERT); + + return unique_key_check(fcinfo); + } + + + /* ---------- + * unique_key_check_upd - + * + * Check uniquesness constraint at update event. + * + * This applies only to UNIQUE constraints marked DEFERRABLE. + * ---------- + */ + Datum + unique_key_check_upd(PG_FUNCTION_ARGS) + { + /* + * Check that this is a valid trigger call on the right time and event. + */ + ri_CheckTrigger(fcinfo, "unique_key_check_upd", RI_TRIGTYPE_UPDATE); + + return unique_key_check(fcinfo); + } *** ./src/backend/utils/adt/ruleutils.c.orig 2009-07-04 15:44:36.000000000 +0100 --- ./src/backend/utils/adt/ruleutils.c 2009-07-04 18:03:05.000000000 +0100 *************** *** 1096,1101 **** --- 1096,1106 ---- quote_identifier(get_tablespace_name(tblspc))); } + if (conForm->condeferrable) + appendStringInfo(&buf, " DEFERRABLE"); + if (conForm->condeferred) + appendStringInfo(&buf, " INITIALLY DEFERRED"); + break; } case CONSTRAINT_CHECK: *** ./src/include/access/genam.h.orig 2009-07-04 15:42:49.000000000 +0100 --- ./src/include/access/genam.h 2009-07-04 16:25:47.000000000 +0100 *************** *** 85,90 **** --- 85,118 ---- typedef struct IndexScanDescData *IndexScanDesc; typedef struct SysScanDescData *SysScanDesc; + /* + * Enumeration specifying the type of uniqueness check to perform for + * index_insert(). + * + * For deferrable unique constraints, UNIQUE_CHECK_PARTIAL is used at + * insertion time. This tests if the tuple is unique but doesn't error, + * block or prevent the insertion if the tuple appears not to be unique. + * Since it isn't blocking, it can't know for sure if the tuple is not + * unique (the other transaction might be rolled back). The return value + * of index_insert() in this case is true if the tuple is definitely + * unique and false if it is possibly non-unique. + * + * UNIQUE_CHECK_EXISTING is for deferred unqiue constraint checks. The + * tuple is already in the index when the check is performed, so it is + * not inserted again. This just checks if the tuple in the index is + * unique. + */ + typedef enum IndexUniqueCheck + { + UNIQUE_CHECK_NO, /* Don't do any uniqueness checking */ + UNIQUE_CHECK_YES, /* Enforce uniqueness at insertion time */ + /* (possibly blocking on another xact) */ + UNIQUE_CHECK_PARTIAL, /* Test uniqueness, but don't block and */ + /* no error if potentially not unique */ + UNIQUE_CHECK_EXISTING /* Don't do the insert, just check if */ + /* the existing tuple is unique */ + } IndexUniqueCheck; + /* * generalized index_ interface routines (in indexam.c) *************** *** 103,109 **** Datum *values, bool *isnull, ItemPointer heap_t_ctid, Relation heapRelation, ! bool check_uniqueness); extern IndexScanDesc index_beginscan(Relation heapRelation, Relation indexRelation, --- 131,137 ---- Datum *values, bool *isnull, ItemPointer heap_t_ctid, Relation heapRelation, ! IndexUniqueCheck check_uniqueness); extern IndexScanDesc index_beginscan(Relation heapRelation, Relation indexRelation, *** ./src/include/access/nbtree.h.orig 2009-07-04 15:42:49.000000000 +0100 --- ./src/include/access/nbtree.h 2009-07-04 16:28:12.000000000 +0100 *************** *** 517,524 **** /* * prototypes for functions in nbtinsert.c */ ! extern void _bt_doinsert(Relation rel, IndexTuple itup, ! bool index_is_unique, Relation heapRel); extern Buffer _bt_getstackbuf(Relation rel, BTStack stack, int access); extern void _bt_insert_parent(Relation rel, Buffer buf, Buffer rbuf, BTStack stack, bool is_root, bool is_only); --- 517,524 ---- /* * prototypes for functions in nbtinsert.c */ ! extern bool _bt_doinsert(Relation rel, IndexTuple itup, ! IndexUniqueCheck unique_check, Relation heapRel); extern Buffer _bt_getstackbuf(Relation rel, BTStack stack, int access); extern void _bt_insert_parent(Relation rel, Buffer buf, Buffer rbuf, BTStack stack, bool is_root, bool is_only); *** ./src/include/catalog/pg_proc.h.orig 2009-07-04 15:41:37.000000000 +0100 --- ./src/include/catalog/pg_proc.h 2009-07-04 16:07:27.000000000 +0100 *************** *** 2346,2351 **** --- 2346,2357 ---- DATA(insert OID = 1655 ( RI_FKey_noaction_upd PGNSP PGUID 12 1 0 0 f f f t f v 0 0 2279 "" _null_ _null_ _null_ _null_ RI_FKey_noaction_upd _null_ _null_ _null_ )); DESCR("referential integrity ON UPDATE NO ACTION"); + /* Deferrable unique constraint triggers */ + DATA(insert OID = 1029 ( unique_key_check_ins PGNSP PGUID 12 1 0 0 f f f t f v 0 0 2279 "" _null_ _null_ _null_ _null_ unique_key_check_ins _null_ _null_ _null_ )); + DESCR("deferred check UNIQUE ... DEFERRABLE"); + DATA(insert OID = 1030 ( unique_key_check_upd PGNSP PGUID 12 1 0 0 f f f t f v 0 0 2279 "" _null_ _null_ _null_ _null_ unique_key_check_upd _null_ _null_ _null_ )); + DESCR("deferred check UNIQUE ... DEFERRABLE"); + DATA(insert OID = 1666 ( varbiteq PGNSP PGUID 12 1 0 0 f f f t f i 2 0 16 "1562 1562" _null_ _null_ _null_ _null_ biteq _null_ _null_ _null_ )); DESCR("equal"); DATA(insert OID = 1667 ( varbitne PGNSP PGUID 12 1 0 0 f f f t f i 2 0 16 "1562 1562" _null_ _null_ _null_ _null_ bitne _null_ _null_ _null_ )); *** ./src/include/commands/defrem.h.orig 2009-07-04 15:42:49.000000000 +0100 --- ./src/include/commands/defrem.h 2009-07-04 16:48:30.000000000 +0100 *************** *** 29,34 **** --- 29,36 ---- bool unique, bool primary, bool isconstraint, + bool constrDeferrable, + bool constrInitDeferred, bool is_alter_table, bool check_rights, bool skip_build, *** ./src/include/commands/trigger.h.orig 2009-07-04 15:42:49.000000000 +0100 --- ./src/include/commands/trigger.h 2009-07-04 16:46:36.000000000 +0100 *************** *** 131,137 **** HeapTuple trigtuple); extern void ExecARInsertTriggers(EState *estate, ResultRelInfo *relinfo, ! HeapTuple trigtuple); extern void ExecBSDeleteTriggers(EState *estate, ResultRelInfo *relinfo); extern void ExecASDeleteTriggers(EState *estate, --- 131,138 ---- HeapTuple trigtuple); extern void ExecARInsertTriggers(EState *estate, ResultRelInfo *relinfo, ! HeapTuple trigtuple, ! List *problemIndexList); extern void ExecBSDeleteTriggers(EState *estate, ResultRelInfo *relinfo); extern void ExecASDeleteTriggers(EState *estate, *************** *** 153,159 **** extern void ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo, ItemPointer tupleid, ! HeapTuple newtuple); extern void ExecBSTruncateTriggers(EState *estate, ResultRelInfo *relinfo); extern void ExecASTruncateTriggers(EState *estate, --- 154,161 ---- extern void ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo, ItemPointer tupleid, ! HeapTuple newtuple, ! List *problemIndexList); extern void ExecBSTruncateTriggers(EState *estate, ResultRelInfo *relinfo); extern void ExecASTruncateTriggers(EState *estate, *** ./src/include/executor/executor.h.orig 2009-07-04 15:42:49.000000000 +0100 --- ./src/include/executor/executor.h 2009-07-04 16:32:11.000000000 +0100 *************** *** 301,307 **** extern void ExecOpenIndices(ResultRelInfo *resultRelInfo); extern void ExecCloseIndices(ResultRelInfo *resultRelInfo); ! extern void ExecInsertIndexTuples(TupleTableSlot *slot, ItemPointer tupleid, EState *estate, bool is_vacuum); extern void RegisterExprContextCallback(ExprContext *econtext, --- 301,307 ---- extern void ExecOpenIndices(ResultRelInfo *resultRelInfo); extern void ExecCloseIndices(ResultRelInfo *resultRelInfo); ! extern List *ExecInsertIndexTuples(TupleTableSlot *slot, ItemPointer tupleid, EState *estate, bool is_vacuum); extern void RegisterExprContextCallback(ExprContext *econtext, *** ./src/include/nodes/execnodes.h.orig 2009-07-04 15:42:49.000000000 +0100 --- ./src/include/nodes/execnodes.h 2009-07-04 16:34:51.000000000 +0100 *************** *** 44,49 **** --- 44,52 ---- * ReadyForInserts is it valid for inserts? * Concurrent are we doing a concurrent index build? * BrokenHotChain did we detect any broken HOT chains? + * ConstraintId OID of constraint associated with this index + * Deferrable is the constraint DEFERRABLE? + * InitDeferred is the constraint INITIALLY DEFERRED? * * ii_Concurrent and ii_BrokenHotChain are used only during index build; * they're conventionally set to false otherwise. *************** *** 62,67 **** --- 65,73 ---- bool ii_ReadyForInserts; bool ii_Concurrent; bool ii_BrokenHotChain; + Oid ii_ConstraintId; + bool ii_Deferrable; + bool ii_InitDeferred; } IndexInfo; /* ---------------- *** ./src/include/nodes/parsenodes.h.orig 2009-07-04 15:42:49.000000000 +0100 --- ./src/include/nodes/parsenodes.h 2009-07-04 16:40:06.000000000 +0100 *************** *** 1354,1360 **** * Constraint attributes (DEFERRABLE etc) are initially represented as * separate Constraint nodes for simplicity of parsing. parse_utilcmd.c makes * a pass through the constraints list to attach the info to the appropriate ! * FkConstraint node (and, perhaps, someday to other kinds of constraints). * ---------- */ --- 1354,1360 ---- * Constraint attributes (DEFERRABLE etc) are initially represented as * separate Constraint nodes for simplicity of parsing. parse_utilcmd.c makes * a pass through the constraints list to attach the info to the appropriate ! * Constraint and FkConstraint nodes. * ---------- */ *************** *** 1384,1389 **** --- 1384,1391 ---- List *options; /* options from WITH clause */ char *indexspace; /* index tablespace for PKEY/UNIQUE * constraints; NULL for default */ + bool deferrable; /* DEFERRABLE */ + bool initdeferred; /* INITIALLY DEFERRED */ } Constraint; /* ---------- *************** *** 1863,1868 **** --- 1865,1872 ---- bool unique; /* is index unique? */ bool primary; /* is index on primary key? */ bool isconstraint; /* is it from a CONSTRAINT clause? */ + bool constrDeferrable; /* is the constraint DEFERRABLE? */ + bool constrInitDeferred; /* is the constraint INITIALLY DEFERRED? */ bool concurrent; /* should this be a concurrent index build? */ } IndexStmt; *** ./src/include/utils/builtins.h.orig 2009-07-04 15:42:49.000000000 +0100 --- ./src/include/utils/builtins.h 2009-07-04 16:43:48.000000000 +0100 *************** *** 932,940 **** extern Datum RI_FKey_setnull_upd(PG_FUNCTION_ARGS); extern Datum RI_FKey_setdefault_del(PG_FUNCTION_ARGS); extern Datum RI_FKey_setdefault_upd(PG_FUNCTION_ARGS); ! ! /* trigfuncs.c */ ! extern Datum suppress_redundant_updates_trigger(PG_FUNCTION_ARGS); /* encoding support functions */ extern Datum getdatabaseencoding(PG_FUNCTION_ARGS); --- 932,939 ---- extern Datum RI_FKey_setnull_upd(PG_FUNCTION_ARGS); extern Datum RI_FKey_setdefault_del(PG_FUNCTION_ARGS); extern Datum RI_FKey_setdefault_upd(PG_FUNCTION_ARGS); ! extern Datum unique_key_check_ins(PG_FUNCTION_ARGS); ! extern Datum unique_key_check_upd(PG_FUNCTION_ARGS); /* encoding support functions */ extern Datum getdatabaseencoding(PG_FUNCTION_ARGS);