diff options
| author | Tom Lane <tgl@sss.pgh.pa.us> | 2009-07-29 20:56:21 +0000 |
|---|---|---|
| committer | Tom Lane <tgl@sss.pgh.pa.us> | 2009-07-29 20:56:21 +0000 |
| commit | 25d9bf2e3e66ee2e546c5c523d148ecab6ee1dcc (patch) | |
| tree | b0dee0f1d6111fd6658d432ec30e5ddb88adc02f /src | |
| parent | 850490579318ff52097eec92ce535357dd0c7a3a (diff) | |
| download | postgresql-25d9bf2e3e66ee2e546c5c523d148ecab6ee1dcc.tar.gz postgresql-25d9bf2e3e66ee2e546c5c523d148ecab6ee1dcc.zip | |
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
Diffstat (limited to 'src')
47 files changed, 1092 insertions, 213 deletions
diff --git a/src/backend/access/gin/gininsert.c b/src/backend/access/gin/gininsert.c index 2adaed43d48..d175a5a99e6 100644 --- a/src/backend/access/gin/gininsert.c +++ b/src/backend/access/gin/gininsert.c @@ -8,7 +8,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/access/gin/gininsert.c,v 1.22 2009/06/11 14:48:53 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/access/gin/gininsert.c,v 1.23 2009/07/29 20:56:17 tgl Exp $ *------------------------------------------------------------------------- */ @@ -415,12 +415,11 @@ gininsert(PG_FUNCTION_ARGS) #ifdef NOT_USED Relation heapRel = (Relation) PG_GETARG_POINTER(4); - bool checkUnique = PG_GETARG_BOOL(5); + IndexUniqueCheck checkUnique = (IndexUniqueCheck) PG_GETARG_INT32(5); #endif GinState ginstate; MemoryContext oldCtx; MemoryContext insertCtx; - uint32 res = 0; int i; insertCtx = AllocSetContextCreate(CurrentMemoryContext, @@ -440,7 +439,7 @@ gininsert(PG_FUNCTION_ARGS) memset(&collector, 0, sizeof(GinTupleCollector)); for (i = 0; i < ginstate.origTupdesc->natts; i++) if (!isnull[i]) - res += ginHeapTupleFastCollect(index, &ginstate, &collector, + ginHeapTupleFastCollect(index, &ginstate, &collector, (OffsetNumber) (i + 1), values[i], ht_ctid); ginHeapTupleFastInsert(index, &ginstate, &collector); @@ -449,7 +448,7 @@ gininsert(PG_FUNCTION_ARGS) { for (i = 0; i < ginstate.origTupdesc->natts; i++) if (!isnull[i]) - res += ginHeapTupleInsert(index, &ginstate, + ginHeapTupleInsert(index, &ginstate, (OffsetNumber) (i + 1), values[i], ht_ctid); } @@ -457,5 +456,5 @@ gininsert(PG_FUNCTION_ARGS) MemoryContextSwitchTo(oldCtx); MemoryContextDelete(insertCtx); - PG_RETURN_BOOL(res > 0); + PG_RETURN_BOOL(false); } diff --git a/src/backend/access/gist/gist.c b/src/backend/access/gist/gist.c index 2742969c6af..ef6febef85c 100644 --- a/src/backend/access/gist/gist.c +++ b/src/backend/access/gist/gist.c @@ -8,7 +8,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/access/gist/gist.c,v 1.156 2009/01/01 17:23:34 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/access/gist/gist.c,v 1.157 2009/07/29 20:56:18 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -225,7 +225,7 @@ gistinsert(PG_FUNCTION_ARGS) #ifdef NOT_USED Relation heapRel = (Relation) PG_GETARG_POINTER(4); - bool checkUnique = PG_GETARG_BOOL(5); + IndexUniqueCheck checkUnique = (IndexUniqueCheck) PG_GETARG_INT32(5); #endif IndexTuple itup; GISTSTATE giststate; @@ -248,7 +248,7 @@ gistinsert(PG_FUNCTION_ARGS) MemoryContextSwitchTo(oldCtx); MemoryContextDelete(insertCtx); - PG_RETURN_BOOL(true); + PG_RETURN_BOOL(false); } diff --git a/src/backend/access/hash/hash.c b/src/backend/access/hash/hash.c index 49b6594f1eb..35056838364 100644 --- a/src/backend/access/hash/hash.c +++ b/src/backend/access/hash/hash.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/access/hash/hash.c,v 1.112 2009/06/11 14:48:53 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/access/hash/hash.c,v 1.113 2009/07/29 20:56:18 tgl Exp $ * * NOTES * This file contains only the public interface routines. @@ -165,7 +165,7 @@ hashinsert(PG_FUNCTION_ARGS) #ifdef NOT_USED Relation heapRel = (Relation) PG_GETARG_POINTER(4); - bool checkUnique = PG_GETARG_BOOL(5); + IndexUniqueCheck checkUnique = (IndexUniqueCheck) PG_GETARG_INT32(5); #endif IndexTuple itup; @@ -192,7 +192,7 @@ hashinsert(PG_FUNCTION_ARGS) pfree(itup); - PG_RETURN_BOOL(true); + PG_RETURN_BOOL(false); } diff --git a/src/backend/access/heap/tuptoaster.c b/src/backend/access/heap/tuptoaster.c index 781bfd2e486..2fbf8f43bbd 100644 --- a/src/backend/access/heap/tuptoaster.c +++ b/src/backend/access/heap/tuptoaster.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/access/heap/tuptoaster.c,v 1.94 2009/07/22 01:21:22 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/access/heap/tuptoaster.c,v 1.95 2009/07/29 20:56:18 tgl Exp $ * * * INTERFACE ROUTINES @@ -1229,7 +1229,9 @@ toast_save_datum(Relation rel, Datum value, int options) */ index_insert(toastidx, t_values, t_isnull, &(toasttup->t_self), - toastrel, toastidx->rd_index->indisunique); + toastrel, + toastidx->rd_index->indisunique ? + UNIQUE_CHECK_YES : UNIQUE_CHECK_NO); /* * Free memory diff --git a/src/backend/access/index/indexam.c b/src/backend/access/index/indexam.c index 32623965c78..f4ffeccd328 100644 --- a/src/backend/access/index/indexam.c +++ b/src/backend/access/index/indexam.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/access/index/indexam.c,v 1.114 2009/06/11 14:48:54 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/access/index/indexam.c,v 1.115 2009/07/29 20:56:18 tgl Exp $ * * INTERFACE ROUTINES * index_open - open an index relation by relation OID @@ -185,7 +185,7 @@ index_insert(Relation indexRelation, bool *isnull, ItemPointer heap_t_ctid, Relation heapRelation, - bool check_uniqueness) + IndexUniqueCheck checkUnique) { FmgrInfo *procedure; @@ -201,7 +201,7 @@ index_insert(Relation indexRelation, PointerGetDatum(isnull), PointerGetDatum(heap_t_ctid), PointerGetDatum(heapRelation), - BoolGetDatum(check_uniqueness))); + Int32GetDatum((int32) checkUnique))); } /* diff --git a/src/backend/access/nbtree/nbtinsert.c b/src/backend/access/nbtree/nbtinsert.c index a06faa20203..f0137182249 100644 --- a/src/backend/access/nbtree/nbtinsert.c +++ b/src/backend/access/nbtree/nbtinsert.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/access/nbtree/nbtinsert.c,v 1.170 2009/06/11 14:48:54 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/access/nbtree/nbtinsert.c,v 1.171 2009/07/29 20:56:18 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -49,8 +49,9 @@ typedef struct static Buffer _bt_newroot(Relation rel, Buffer lbuf, Buffer rbuf); static TransactionId _bt_check_unique(Relation rel, IndexTuple itup, - Relation heapRel, Buffer buf, OffsetNumber ioffset, - ScanKey itup_scankey); + Relation heapRel, Buffer buf, OffsetNumber offset, + ScanKey itup_scankey, + IndexUniqueCheck checkUnique, bool *is_unique); static void _bt_findinsertloc(Relation rel, Buffer *bufptr, OffsetNumber *offsetptr, @@ -85,11 +86,24 @@ static void _bt_vacuum_one_page(Relation rel, Buffer buffer); * * This routine is called by the public interface routines, btbuild * and btinsert. By here, itup is filled in, including the TID. + * + * If checkUnique is UNIQUE_CHECK_NO or UNIQUE_CHECK_PARTIAL, this + * will allow duplicates. Otherwise (UNIQUE_CHECK_YES or + * UNIQUE_CHECK_EXISTING) it will throw error for a duplicate. + * For UNIQUE_CHECK_EXISTING we merely run the duplicate check, and + * don't actually insert. + * + * The result value is only significant for UNIQUE_CHECK_PARTIAL: + * it must be TRUE if the entry is known unique, else FALSE. + * (In the current implementation we'll also return TRUE after a + * successful UNIQUE_CHECK_YES or UNIQUE_CHECK_EXISTING call, but + * that's just a coding artifact.) */ -void +bool _bt_doinsert(Relation rel, IndexTuple itup, - bool index_is_unique, Relation heapRel) + IndexUniqueCheck checkUnique, Relation heapRel) { + bool is_unique = false; int natts = rel->rd_rel->relnatts; ScanKey itup_scankey; BTStack stack; @@ -134,13 +148,18 @@ top: * * If we must wait for another xact, we release the lock while waiting, * and then must start over completely. + * + * For a partial uniqueness check, we 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 (index_is_unique) + if (checkUnique != UNIQUE_CHECK_NO) { TransactionId xwait; offset = _bt_binsrch(rel, buf, natts, itup_scankey, false); - xwait = _bt_check_unique(rel, itup, heapRel, buf, offset, itup_scankey); + xwait = _bt_check_unique(rel, itup, heapRel, buf, offset, itup_scankey, + checkUnique, &is_unique); if (TransactionIdIsValid(xwait)) { @@ -153,13 +172,23 @@ top: } } - /* do the insertion */ - _bt_findinsertloc(rel, &buf, &offset, natts, itup_scankey, itup); - _bt_insertonpg(rel, buf, stack, itup, offset, false); + if (checkUnique != UNIQUE_CHECK_EXISTING) + { + /* do the insertion */ + _bt_findinsertloc(rel, &buf, &offset, natts, itup_scankey, itup); + _bt_insertonpg(rel, buf, stack, itup, offset, false); + } + else + { + /* just release the buffer */ + _bt_relbuf(rel, buf); + } /* be tidy */ _bt_freestack(stack); _bt_freeskey(itup_scankey); + + return is_unique; } /* @@ -172,10 +201,16 @@ top: * 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(). + * + * However, if checkUnique == UNIQUE_CHECK_PARTIAL, we always return + * InvalidTransactionId because we don't want to wait. In this case we + * set *is_unique to false if there is a potential conflict, and the + * core code must redo the uniqueness check later. */ static TransactionId _bt_check_unique(Relation rel, IndexTuple itup, Relation heapRel, - Buffer buf, OffsetNumber offset, ScanKey itup_scankey) + Buffer buf, OffsetNumber offset, ScanKey itup_scankey, + IndexUniqueCheck checkUnique, bool *is_unique) { TupleDesc itupdesc = RelationGetDescr(rel); int natts = rel->rd_rel->relnatts; @@ -184,6 +219,10 @@ _bt_check_unique(Relation rel, IndexTuple itup, Relation heapRel, Page page; BTPageOpaque opaque; Buffer nbuf = InvalidBuffer; + bool found = false; + + /* Assume unique until we find a duplicate */ + *is_unique = true; InitDirtySnapshot(SnapshotDirty); @@ -241,21 +280,48 @@ _bt_check_unique(Relation rel, IndexTuple itup, Relation heapRel, htid = curitup->t_tid; /* + * If we are doing a recheck, we expect to find the tuple we + * are rechecking. It's not a duplicate, but we have to keep + * scanning. + */ + if (checkUnique == UNIQUE_CHECK_EXISTING && + ItemPointerCompare(&htid, &itup->t_tid) == 0) + { + found = true; + } + + /* * We check the whole HOT-chain to see if there is any tuple * that satisfies SnapshotDirty. This is necessary because we * have just a single index entry for the entire chain. */ - if (heap_hot_search(&htid, heapRel, &SnapshotDirty, &all_dead)) + else if (heap_hot_search(&htid, heapRel, &SnapshotDirty, + &all_dead)) { - /* it is a duplicate */ - TransactionId xwait = - (TransactionIdIsValid(SnapshotDirty.xmin)) ? - SnapshotDirty.xmin : SnapshotDirty.xmax; + TransactionId xwait; + + /* + * It is 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. + */ + if (checkUnique == UNIQUE_CHECK_PARTIAL) + { + if (nbuf != InvalidBuffer) + _bt_relbuf(rel, nbuf); + *is_unique = false; + return InvalidTransactionId; + } /* * If this tuple is being updated by other transaction * then we have to wait for its commit/abort. */ + xwait = (TransactionIdIsValid(SnapshotDirty.xmin)) ? + SnapshotDirty.xmin : SnapshotDirty.xmax; + if (TransactionIdIsValid(xwait)) { if (nbuf != InvalidBuffer) @@ -295,6 +361,9 @@ _bt_check_unique(Relation rel, IndexTuple itup, Relation heapRel, break; } + /* + * This is a definite conflict. + */ ereport(ERROR, (errcode(ERRCODE_UNIQUE_VIOLATION), errmsg("duplicate key value violates unique constraint \"%s\"", @@ -349,6 +418,18 @@ _bt_check_unique(Relation rel, IndexTuple itup, Relation heapRel, } } + /* + * If we are doing a recheck then we should have found the tuple we + * are checking. Otherwise there's something very wrong --- probably, + * the index is on a non-immutable expression. + */ + if (checkUnique == UNIQUE_CHECK_EXISTING && !found) + ereport(ERROR, + (errcode(ERRCODE_INTERNAL_ERROR), + errmsg("failed to re-find tuple within index \"%s\"", + RelationGetRelationName(rel)), + errhint("This may be because of a non-immutable index expression."))); + if (nbuf != InvalidBuffer) _bt_relbuf(rel, nbuf); diff --git a/src/backend/access/nbtree/nbtree.c b/src/backend/access/nbtree/nbtree.c index 2b76e7cd453..87a8a225dbf 100644 --- a/src/backend/access/nbtree/nbtree.c +++ b/src/backend/access/nbtree/nbtree.c @@ -12,7 +12,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/access/nbtree/nbtree.c,v 1.171 2009/06/11 14:48:54 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/access/nbtree/nbtree.c,v 1.172 2009/07/29 20:56:18 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -217,18 +217,19 @@ btinsert(PG_FUNCTION_ARGS) 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); + IndexUniqueCheck checkUnique = (IndexUniqueCheck) PG_GETARG_INT32(5); + bool result; 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); + result = _bt_doinsert(rel, itup, checkUnique, heapRel); pfree(itup); - PG_RETURN_BOOL(true); + PG_RETURN_BOOL(result); } /* diff --git a/src/backend/bootstrap/bootparse.y b/src/backend/bootstrap/bootparse.y index 1510614d5ad..1670e462bc3 100644 --- a/src/backend/bootstrap/bootparse.y +++ b/src/backend/bootstrap/bootparse.y @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/bootstrap/bootparse.y,v 1.96 2009/01/01 17:23:36 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/bootstrap/bootparse.y,v 1.97 2009/07/29 20:56:18 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -266,7 +266,7 @@ Boot_DeclareIndexStmt: NULL, $10, NULL, NIL, - false, false, false, + false, false, false, false, false, false, false, true, false, false); do_end(); } @@ -284,7 +284,7 @@ Boot_DeclareUniqueIndexStmt: NULL, $11, NULL, NIL, - true, false, false, + true, false, false, false, false, false, false, true, false, false); do_end(); } diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 0199ca67303..73472d1568e 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/catalog/index.c,v 1.319 2009/07/28 02:56:29 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/index.c,v 1.320 2009/07/29 20:56:18 tgl Exp $ * * * INTERFACE ROUTINES @@ -40,14 +40,18 @@ #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/parser.h" #include "storage/bufmgr.h" #include "storage/lmgr.h" #include "storage/procarray.h" @@ -87,6 +91,7 @@ static void UpdateIndexRelation(Oid indexoid, Oid heapoid, Oid *classOids, int16 *coloptions, bool primary, + bool immediate, bool isvalid); static void index_update_stats(Relation rel, bool hasindex, bool isprimary, Oid reltoastidxid, double reltuples); @@ -372,6 +377,7 @@ UpdateIndexRelation(Oid indexoid, Oid *classOids, int16 *coloptions, bool primary, + bool immediate, bool isvalid) { int2vector *indkey; @@ -439,6 +445,7 @@ UpdateIndexRelation(Oid indexoid, values[Anum_pg_index_indnatts - 1] = Int16GetDatum(indexInfo->ii_NumIndexAttrs); values[Anum_pg_index_indisunique - 1] = BoolGetDatum(indexInfo->ii_Unique); values[Anum_pg_index_indisprimary - 1] = BoolGetDatum(primary); + values[Anum_pg_index_indimmediate - 1] = BoolGetDatum(immediate); values[Anum_pg_index_indisclustered - 1] = BoolGetDatum(false); values[Anum_pg_index_indisvalid - 1] = BoolGetDatum(isvalid); values[Anum_pg_index_indcheckxmin - 1] = BoolGetDatum(false); @@ -488,6 +495,8 @@ UpdateIndexRelation(Oid indexoid, * reloptions: AM-specific options * isprimary: index is a PRIMARY KEY * isconstraint: index is owned by a PRIMARY KEY or UNIQUE constraint + * deferrable: constraint is DEFERRABLE + * initdeferred: constraint is INITIALLY DEFERRED * allow_system_table_mods: allow table to be a system catalog * skip_build: true to skip the index_build() step for the moment; caller * must do it later (typically via reindex_index()) @@ -509,6 +518,8 @@ index_create(Oid heapRelationId, Datum reloptions, bool isprimary, bool isconstraint, + bool deferrable, + bool initdeferred, bool allow_system_table_mods, bool skip_build, bool concurrent) @@ -679,7 +690,9 @@ index_create(Oid heapRelationId, * ---------------- */ UpdateIndexRelation(indexRelationId, heapRelationId, indexInfo, - classObjectId, coloptions, isprimary, !concurrent); + classObjectId, coloptions, isprimary, + !deferrable, + !concurrent); /* * Register constraint and dependencies for the index. @@ -726,8 +739,8 @@ index_create(Oid heapRelationId, conOid = CreateConstraintEntry(indexRelationName, namespaceId, constraintType, - false, /* isDeferrable */ - false, /* isDeferred */ + deferrable, + initdeferred, heapRelationId, indexInfo->ii_KeyAttrNumbers, indexInfo->ii_NumIndexAttrs, @@ -753,6 +766,40 @@ index_create(Oid heapRelationId, referenced.objectSubId = 0; recordDependencyOn(&myself, &referenced, DEPENDENCY_INTERNAL); + + /* + * If the constraint is deferrable, create the deferred uniqueness + * checking trigger. (The trigger will be given an internal + * dependency on the constraint by CreateTrigger, so there's no + * need to do anything more here.) + */ + if (deferrable) + { + RangeVar *heapRel; + CreateTrigStmt *trigger; + + heapRel = makeRangeVar(get_namespace_name(namespaceId), + pstrdup(RelationGetRelationName(heapRelation)), + -1); + + trigger = makeNode(CreateTrigStmt); + trigger->trigname = pstrdup(indexRelationName); + trigger->relation = heapRel; + trigger->funcname = SystemFuncName("unique_key_recheck"); + trigger->args = NIL; + trigger->before = false; + trigger->row = true; + trigger->events = TRIGGER_TYPE_INSERT | TRIGGER_TYPE_UPDATE; + trigger->isconstraint = true; + trigger->deferrable = true; + trigger->initdeferred = initdeferred; + trigger->constrrel = NULL; + + (void) CreateTrigger(trigger, conOid, indexRelationId, + isprimary ? "PK_ConstraintTrigger" : + "Unique_ConstraintTrigger", + false); + } } else { @@ -791,6 +838,10 @@ index_create(Oid heapRelationId, recordDependencyOn(&myself, &referenced, DEPENDENCY_AUTO); } + + /* Non-constraint indexes can't be deferrable */ + Assert(!deferrable); + Assert(!initdeferred); } /* Store dependency on operator classes */ @@ -823,6 +874,13 @@ index_create(Oid heapRelationId, DEPENDENCY_AUTO); } } + else + { + /* Bootstrap mode - assert we weren't asked for constraint support */ + Assert(!isconstraint); + Assert(!deferrable); + Assert(!initdeferred); + } /* * Advance the command counter so that we can see the newly-entered @@ -2190,7 +2248,8 @@ validate_index_heapscan(Relation heapRelation, isnull, &rootTuple, heapRelation, - indexInfo->ii_Unique); + indexInfo->ii_Unique ? + UNIQUE_CHECK_YES : UNIQUE_CHECK_NO); state->tups_inserted += 1; } diff --git a/src/backend/catalog/indexing.c b/src/backend/catalog/indexing.c index b7079722312..65fe6d5efde 100644 --- a/src/backend/catalog/indexing.c +++ b/src/backend/catalog/indexing.c @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/catalog/indexing.c,v 1.117 2009/01/01 17:23:37 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/indexing.c,v 1.118 2009/07/29 20:56:18 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -134,7 +134,8 @@ CatalogIndexInsert(CatalogIndexState indstate, HeapTuple heapTuple) isnull, /* is-null flags */ &(heapTuple->t_self), /* tid of heap tuple */ heapRelation, - relationDescs[i]->rd_index->indisunique); + relationDescs[i]->rd_index->indisunique ? + UNIQUE_CHECK_YES : UNIQUE_CHECK_NO); } ExecDropSingleTupleTableSlot(slot); diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index 3970f88f7dd..37f8f2332be 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -289,7 +289,7 @@ F695 Translation support NO F696 Additional translation documentation NO F701 Referential update actions YES F711 ALTER domain YES -F721 Deferrable constraints NO foreign keys only +F721 Deferrable constraints NO foreign and unique keys only F731 INSERT column privileges YES F741 Referential MATCH types NO no partial match yet F751 View CHECK enhancements NO diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c index b284cd23aa9..9e2f20e3bf0 100644 --- a/src/backend/catalog/toasting.c +++ b/src/backend/catalog/toasting.c @@ -8,7 +8,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/catalog/toasting.c,v 1.17 2009/06/11 20:46:11 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/toasting.c,v 1.18 2009/07/29 20:56:18 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -253,7 +253,8 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid, BTREE_AM_OID, rel->rd_rel->reltablespace, classObjectId, coloptions, (Datum) 0, - true, false, true, false, false); + true, false, false, false, + true, false, false); /* * Store the toast table's OID in the parent relation's pg_class row diff --git a/src/backend/commands/Makefile b/src/backend/commands/Makefile index e455e62c9aa..29a29ab7f4c 100644 --- a/src/backend/commands/Makefile +++ b/src/backend/commands/Makefile @@ -4,7 +4,7 @@ # Makefile for backend/commands # # IDENTIFICATION -# $PostgreSQL: pgsql/src/backend/commands/Makefile,v 1.39 2008/12/19 16:25:17 petere Exp $ +# $PostgreSQL: pgsql/src/backend/commands/Makefile,v 1.40 2009/07/29 20:56:18 tgl Exp $ # #------------------------------------------------------------------------- @@ -13,7 +13,7 @@ top_builddir = ../../.. include $(top_builddir)/src/Makefile.global OBJS = aggregatecmds.o alter.o analyze.o async.o cluster.o comment.o \ - conversioncmds.o copy.o \ + constraint.o conversioncmds.o copy.o \ dbcommands.o define.o discard.o explain.o foreigncmds.o functioncmds.o \ indexcmds.o lockcmds.o operatorcmds.o opclasscmds.o \ portalcmds.o prepare.o proclang.o \ diff --git a/src/backend/commands/constraint.c b/src/backend/commands/constraint.c new file mode 100644 index 00000000000..42d4d4e1f9c --- /dev/null +++ b/src/backend/commands/constraint.c @@ -0,0 +1,166 @@ +/*------------------------------------------------------------------------- + * + * constraint.c + * PostgreSQL CONSTRAINT support code. + * + * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * IDENTIFICATION + * $PostgreSQL: pgsql/src/backend/commands/constraint.c,v 1.1 2009/07/29 20:56:18 tgl Exp $ + * + *------------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "catalog/index.h" +#include "commands/trigger.h" +#include "executor/executor.h" +#include "utils/builtins.h" +#include "utils/tqual.h" + + +/* + * unique_key_recheck - trigger function to do a deferred uniqueness check. + * + * This is invoked as an AFTER ROW trigger for both INSERT and UPDATE, + * for any rows recorded as potentially violating a deferrable unique + * constraint. + * + * This may be an end-of-statement check, a commit-time check, or a + * check triggered by a SET CONSTRAINTS command. + */ +Datum +unique_key_recheck(PG_FUNCTION_ARGS) +{ + TriggerData *trigdata = (TriggerData *) fcinfo->context; + const char *funcname = "unique_key_recheck"; + HeapTuple new_row; + ItemPointerData tmptid; + Relation indexRel; + IndexInfo *indexInfo; + EState *estate; + ExprContext *econtext; + TupleTableSlot *slot; + Datum values[INDEX_MAX_KEYS]; + bool isnull[INDEX_MAX_KEYS]; + + /* + * Make sure this is being called as an AFTER ROW trigger. Note: + * translatable error strings are shared with ri_triggers.c, so + * resist the temptation to fold the function name into them. + */ + if (!CALLED_AS_TRIGGER(fcinfo)) + ereport(ERROR, + (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED), + errmsg("function \"%s\" was not called by trigger manager", + funcname))); + + if (!TRIGGER_FIRED_AFTER(trigdata->tg_event) || + !TRIGGER_FIRED_FOR_ROW(trigdata->tg_event)) + ereport(ERROR, + (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED), + errmsg("function \"%s\" must be fired AFTER ROW", + funcname))); + + /* + * Get the new data that was inserted/updated. + */ + if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event)) + new_row = trigdata->tg_trigtuple; + else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) + new_row = trigdata->tg_newtuple; + else + { + ereport(ERROR, + (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED), + errmsg("function \"%s\" must be fired for INSERT or UPDATE", + funcname))); + new_row = NULL; /* keep compiler quiet */ + } + + /* + * If the new_row is now dead (ie, inserted and then deleted within our + * transaction), we can skip the check. However, we have to be careful, + * because this trigger gets queued only in response to index insertions; + * which means it does not get queued for HOT updates. The row we are + * called for might now be dead, but have a live HOT child, in which case + * we still need to make the uniqueness check. Therefore we have to use + * heap_hot_search, not just HeapTupleSatisfiesVisibility as is done in + * the comparable test in RI_FKey_check. + * + * This might look like just an optimization, because the index AM will + * make this identical test before throwing an error. But it's actually + * needed for correctness, because the index AM will also throw an error + * if it doesn't find the index entry for the row. If the row's dead then + * it's possible the index entry has also been marked dead, and even + * removed. + */ + tmptid = new_row->t_self; + if (!heap_hot_search(&tmptid, trigdata->tg_relation, SnapshotSelf, NULL)) + { + /* + * All rows in the HOT chain are dead, so skip the check. + */ + return PointerGetDatum(NULL); + } + + /* + * Open the index, acquiring a RowExclusiveLock, just as if we were + * going to update it. (This protects against possible changes of the + * index schema, not against concurrent updates.) + */ + indexRel = index_open(trigdata->tg_trigger->tgconstrindid, + RowExclusiveLock); + indexInfo = BuildIndexInfo(indexRel); + + /* + * The heap tuple must be put into a slot for FormIndexDatum. + */ + slot = MakeSingleTupleTableSlot(RelationGetDescr(trigdata->tg_relation)); + + ExecStoreTuple(new_row, slot, InvalidBuffer, false); + + /* + * Typically the index won't have expressions, but if it does we need + * an EState to evaluate them. + */ + if (indexInfo->ii_Expressions != NIL) + { + estate = CreateExecutorState(); + econtext = GetPerTupleExprContext(estate); + econtext->ecxt_scantuple = slot; + } + else + estate = NULL; + + /* + * Form the index values and isnull flags for the index entry that + * we need to check. + * + * Note: if the index uses functions that are not as immutable as they + * are supposed to be, this could produce an index tuple different from + * the original. The index AM can catch such errors by verifying that + * it finds a matching index entry with the tuple's TID. + */ + 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. + */ + if (estate != NULL) + FreeExecutorState(estate); + + ExecDropSingleTupleTableSlot(slot); + + index_close(indexRel, RowExclusiveLock); + + return PointerGetDatum(NULL); +} diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index b41da6385db..21f7b94d546 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/copy.c,v 1.315 2009/07/25 17:04:19 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/commands/copy.c,v 1.316 2009/07/29 20:56:18 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -2130,6 +2130,8 @@ CopyFrom(CopyState cstate) if (!skip_tuple) { + List *recheckIndexes = NIL; + /* Place tuple in tuple slot */ ExecStoreTuple(tuple, slot, InvalidBuffer, false); @@ -2141,10 +2143,12 @@ CopyFrom(CopyState cstate) heap_insert(cstate->rel, tuple, mycid, hi_options, bistate); 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); /* * We count only tuples not suppressed by a BEFORE INSERT trigger; diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index ef879a3df2b..96272ab998d 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/indexcmds.c,v 1.186 2009/07/16 06:33:42 petere Exp $ + * $PostgreSQL: pgsql/src/backend/commands/indexcmds.c,v 1.187 2009/07/29 20:56:18 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -87,6 +87,8 @@ static bool relationHasPrimaryKey(Relation rel); * '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. + * 'deferrable': constraint is DEFERRABLE. + * 'initdeferred': constraint is INITIALLY DEFERRED. * '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,6 +109,8 @@ DefineIndex(RangeVar *heapRelation, bool unique, bool primary, bool isconstraint, + bool deferrable, + bool initdeferred, bool is_alter_table, bool check_rights, bool skip_build, @@ -447,7 +451,8 @@ DefineIndex(RangeVar *heapRelation, indexRelationId = index_create(relationId, indexRelationName, indexRelationId, indexInfo, accessMethodId, tablespaceId, classObjectId, - coloptions, reloptions, primary, isconstraint, + coloptions, reloptions, primary, + isconstraint, deferrable, initdeferred, allowSystemTableMods, skip_build, concurrent); return; /* We're done, in the standard case */ @@ -465,7 +470,8 @@ DefineIndex(RangeVar *heapRelation, indexRelationId = index_create(relationId, indexRelationName, indexRelationId, indexInfo, accessMethodId, tablespaceId, classObjectId, - coloptions, reloptions, primary, isconstraint, + coloptions, reloptions, primary, + isconstraint, deferrable, initdeferred, allowSystemTableMods, true, concurrent); /* diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index e883e8ed91f..0d3e3bc1670 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.292 2009/07/28 02:56:30 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.293 2009/07/29 20:56:18 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -4391,6 +4391,8 @@ ATExecAddIndex(AlteredTableInfo *tab, Relation rel, stmt->unique, stmt->primary, stmt->isconstraint, + stmt->deferrable, + stmt->initdeferred, true, /* is_alter_table */ check_rights, skip_build, @@ -4955,6 +4957,17 @@ transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid, indexStruct = (Form_pg_index) GETSTRUCT(indexTuple); if (indexStruct->indisprimary) { + /* + * Refuse to use a deferrable primary key. This is per SQL spec, + * and there would be a lot of interesting semantic problems if + * we tried to allow it. + */ + if (!indexStruct->indimmediate) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("cannot use a deferrable primary key for referenced table \"%s\"", + RelationGetRelationName(pkrel)))); + *indexOid = indexoid; break; } @@ -5040,11 +5053,12 @@ transformFkeyCheckAttrs(Relation pkrel, indexStruct = (Form_pg_index) GETSTRUCT(indexTuple); /* - * Must have the right number of columns; must be unique and not a - * partial index; forget it if there are any expressions, too + * Must have the right number of columns; must be unique (non + * deferrable) and not a partial index; forget it if there are any + * expressions, too */ if (indexStruct->indnatts == numattrs && - indexStruct->indisunique && + indexStruct->indisunique && indexStruct->indimmediate && heap_attisnull(indexTuple, Anum_pg_index_indpred) && heap_attisnull(indexTuple, Anum_pg_index_indexprs)) { @@ -5243,7 +5257,8 @@ CreateFKCheckTrigger(RangeVar *myRel, FkConstraint *fkconstraint, fk_trigger->constrrel = fkconstraint->pktable; fk_trigger->args = NIL; - (void) CreateTrigger(fk_trigger, constraintOid, indexOid, false); + (void) CreateTrigger(fk_trigger, constraintOid, indexOid, + "RI_ConstraintTrigger", false); /* Make changes-so-far visible */ CommandCounterIncrement(); @@ -5322,7 +5337,8 @@ createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint, } fk_trigger->args = NIL; - (void) CreateTrigger(fk_trigger, constraintOid, indexOid, false); + (void) CreateTrigger(fk_trigger, constraintOid, indexOid, + "RI_ConstraintTrigger", false); /* Make changes-so-far visible */ CommandCounterIncrement(); @@ -5373,7 +5389,8 @@ createForeignKeyTriggers(Relation rel, FkConstraint *fkconstraint, } fk_trigger->args = NIL; - (void) CreateTrigger(fk_trigger, constraintOid, indexOid, false); + (void) CreateTrigger(fk_trigger, constraintOid, indexOid, + "RI_ConstraintTrigger", false); } /* diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c index 0cc33aae6b6..b84731126a1 100644 --- a/src/backend/commands/trigger.c +++ b/src/backend/commands/trigger.c @@ -7,7 +7,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/trigger.c,v 1.249 2009/07/28 02:56:30 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/commands/trigger.c,v 1.250 2009/07/29 20:56:18 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -63,7 +63,8 @@ static HeapTuple ExecCallTriggerFunc(TriggerData *trigdata, Instrumentation *instr, MemoryContext per_tuple_context); static void AfterTriggerSaveEvent(ResultRelInfo *relinfo, int event, - bool row_trigger, HeapTuple oldtup, HeapTuple newtup); + bool row_trigger, HeapTuple oldtup, HeapTuple newtup, + List *recheckIndexes); /* @@ -77,6 +78,10 @@ static void AfterTriggerSaveEvent(ResultRelInfo *relinfo, int event, * indexOid, if nonzero, is the OID of an index associated with the constraint. * We do nothing with this except store it into pg_trigger.tgconstrindid. * + * prefix is NULL for user-created triggers. For internally generated + * constraint triggers, it is a prefix string to use in building the + * trigger name. (stmt->trigname is the constraint name in such cases.) + * * If checkPermissions is true we require ACL_TRIGGER permissions on the * relation. If not, the caller already checked permissions. (This is * currently redundant with constraintOid being zero, but it's clearer to @@ -87,7 +92,7 @@ static void AfterTriggerSaveEvent(ResultRelInfo *relinfo, int event, */ Oid CreateTrigger(CreateTrigStmt *stmt, - Oid constraintOid, Oid indexOid, + Oid constraintOid, Oid indexOid, const char *prefix, bool checkPermissions) { int16 tgtype; @@ -216,20 +221,20 @@ CreateTrigger(CreateTrigStmt *stmt, 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 trigger is for a constraint, stmt->trigname is the constraint + * name; save that and build a unique trigger name based on the supplied + * prefix, to avoid collisions with user-selected trigger names. */ - if (OidIsValid(constraintOid)) + if (prefix != NULL) { snprintf(constrtrigname, sizeof(constrtrigname), - "RI_ConstraintTrigger_%u", trigoid); + "%s_%u", prefix, trigoid); trigname = constrtrigname; constrname = stmt->trigname; } else if (stmt->isconstraint) { - /* constraint trigger: trigger name is also constraint name */ + /* user constraint trigger: trigger name is also constraint name */ trigname = stmt->trigname; constrname = stmt->trigname; } @@ -1650,7 +1655,7 @@ ExecASInsertTriggers(EState *estate, ResultRelInfo *relinfo) if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_INSERT] > 0) AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_INSERT, - false, NULL, NULL); + false, NULL, NULL, NIL); } HeapTuple @@ -1706,13 +1711,13 @@ ExecBRInsertTriggers(EState *estate, ResultRelInfo *relinfo, void ExecARInsertTriggers(EState *estate, ResultRelInfo *relinfo, - HeapTuple trigtuple) + HeapTuple trigtuple, List *recheckIndexes) { TriggerDesc *trigdesc = relinfo->ri_TrigDesc; if (trigdesc && trigdesc->n_after_row[TRIGGER_EVENT_INSERT] > 0) AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_INSERT, - true, NULL, trigtuple); + true, NULL, trigtuple, recheckIndexes); } void @@ -1781,7 +1786,7 @@ ExecASDeleteTriggers(EState *estate, ResultRelInfo *relinfo) if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_DELETE] > 0) AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_DELETE, - false, NULL, NULL); + false, NULL, NULL, NIL); } bool @@ -1858,7 +1863,7 @@ ExecARDeleteTriggers(EState *estate, ResultRelInfo *relinfo, tupleid, NULL); AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_DELETE, - true, trigtuple, NULL); + true, trigtuple, NULL, NIL); heap_freetuple(trigtuple); } } @@ -1929,7 +1934,7 @@ ExecASUpdateTriggers(EState *estate, ResultRelInfo *relinfo) if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_UPDATE] > 0) AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_UPDATE, - false, NULL, NULL); + false, NULL, NULL, NIL); } HeapTuple @@ -1999,7 +2004,8 @@ ExecBRUpdateTriggers(EState *estate, ResultRelInfo *relinfo, void ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo, - ItemPointer tupleid, HeapTuple newtuple) + ItemPointer tupleid, HeapTuple newtuple, + List *recheckIndexes) { TriggerDesc *trigdesc = relinfo->ri_TrigDesc; @@ -2009,7 +2015,7 @@ ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo, tupleid, NULL); AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_UPDATE, - true, trigtuple, newtuple); + true, trigtuple, newtuple, recheckIndexes); heap_freetuple(trigtuple); } } @@ -2080,7 +2086,7 @@ ExecASTruncateTriggers(EState *estate, ResultRelInfo *relinfo) if (trigdesc && trigdesc->n_after_statement[TRIGGER_EVENT_TRUNCATE] > 0) AfterTriggerSaveEvent(relinfo, TRIGGER_EVENT_TRUNCATE, - false, NULL, NULL); + false, NULL, NULL, NIL); } @@ -3793,15 +3799,18 @@ AfterTriggerPendingOnRel(Oid relid) /* ---------- * AfterTriggerSaveEvent() * - * Called by ExecA[RS]...Triggers() to add the event to the queue. + * Called by ExecA[RS]...Triggers() to queue up the triggers that should + * be fired for an event. * - * NOTE: should be called only if we've determined that an event must - * be added to the queue. + * NOTE: this is called whenever there are any triggers associated with + * the event (even if they are disabled). This function decides which + * triggers actually need to be queued. * ---------- */ static void AfterTriggerSaveEvent(ResultRelInfo *relinfo, int event, bool row_trigger, - HeapTuple oldtup, HeapTuple newtup) + HeapTuple oldtup, HeapTuple newtup, + List *recheckIndexes) { Relation rel = relinfo->ri_RelationDesc; TriggerDesc *trigdesc = relinfo->ri_TrigDesc; @@ -3962,6 +3971,17 @@ AfterTriggerSaveEvent(ResultRelInfo *relinfo, int event, bool row_trigger, } /* + * If the trigger is a deferred unique constraint check trigger, + * only queue it if the unique constraint was potentially violated, + * which we know from index insertion time. + */ + if (trigger->tgfoid == F_UNIQUE_KEY_RECHECK) + { + if (!list_member_oid(recheckIndexes, trigger->tgconstrindid)) + continue; /* Uniqueness definitely not violated */ + } + + /* * Fill in event structure and add it to the current query's queue. */ new_shared.ats_event = 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; } /* diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 0752cbfc00e..d10a9eb6cc5 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -15,7 +15,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.435 2009/07/26 23:34:17 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.436 2009/07/29 20:56:19 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -2091,6 +2091,8 @@ _copyConstraint(Constraint *from) COPY_NODE_FIELD(keys); COPY_NODE_FIELD(options); COPY_STRING_FIELD(indexspace); + COPY_SCALAR_FIELD(deferrable); + COPY_SCALAR_FIELD(initdeferred); return newnode; } @@ -2510,6 +2512,8 @@ _copyIndexStmt(IndexStmt *from) COPY_SCALAR_FIELD(unique); COPY_SCALAR_FIELD(primary); COPY_SCALAR_FIELD(isconstraint); + COPY_SCALAR_FIELD(deferrable); + COPY_SCALAR_FIELD(initdeferred); COPY_SCALAR_FIELD(concurrent); return newnode; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 121fbd0d2ee..6fceab27855 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -22,7 +22,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.358 2009/07/26 23:34:18 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.359 2009/07/29 20:56:19 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -1160,6 +1160,8 @@ _equalIndexStmt(IndexStmt *a, IndexStmt *b) COMPARE_SCALAR_FIELD(unique); COMPARE_SCALAR_FIELD(primary); COMPARE_SCALAR_FIELD(isconstraint); + COMPARE_SCALAR_FIELD(deferrable); + COMPARE_SCALAR_FIELD(initdeferred); COMPARE_SCALAR_FIELD(concurrent); return true; @@ -2068,6 +2070,8 @@ _equalConstraint(Constraint *a, Constraint *b) COMPARE_NODE_FIELD(keys); COMPARE_NODE_FIELD(options); COMPARE_STRING_FIELD(indexspace); + COMPARE_SCALAR_FIELD(deferrable); + COMPARE_SCALAR_FIELD(initdeferred); return true; } diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 4e808a5868e..e4de1c5aee2 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.361 2009/07/16 06:33:42 petere Exp $ + * $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.362 2009/07/29 20:56:19 tgl Exp $ * * NOTES * Every node type that can appear in stored rules' parsetrees *must* @@ -1734,6 +1734,8 @@ _outIndexStmt(StringInfo str, IndexStmt *node) WRITE_BOOL_FIELD(unique); WRITE_BOOL_FIELD(primary); WRITE_BOOL_FIELD(isconstraint); + WRITE_BOOL_FIELD(deferrable); + WRITE_BOOL_FIELD(initdeferred); WRITE_BOOL_FIELD(concurrent); } @@ -2285,6 +2287,8 @@ _outConstraint(StringInfo str, Constraint *node) WRITE_NODE_FIELD(keys); WRITE_NODE_FIELD(options); WRITE_STRING_FIELD(indexspace); + WRITE_BOOL_FIELD(deferrable); + WRITE_BOOL_FIELD(initdeferred); break; case CONSTR_UNIQUE: @@ -2292,6 +2296,8 @@ _outConstraint(StringInfo str, Constraint *node) WRITE_NODE_FIELD(keys); WRITE_NODE_FIELD(options); WRITE_STRING_FIELD(indexspace); + WRITE_BOOL_FIELD(deferrable); + WRITE_BOOL_FIELD(initdeferred); break; case CONSTR_CHECK: diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index c44bbe75c3b..0547b64a6e7 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -11,7 +11,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.673 2009/07/26 23:34:18 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.674 2009/07/29 20:56:19 tgl Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -2220,6 +2220,8 @@ ColConstraintElem: n->cooked_expr = NULL; n->keys = NULL; n->indexspace = NULL; + n->deferrable = FALSE; + n->initdeferred = FALSE; $$ = (Node *)n; } | NULL_P @@ -2231,6 +2233,8 @@ ColConstraintElem: n->cooked_expr = NULL; n->keys = NULL; n->indexspace = NULL; + n->deferrable = FALSE; + n->initdeferred = FALSE; $$ = (Node *)n; } | UNIQUE opt_definition OptConsTableSpace @@ -2243,6 +2247,8 @@ ColConstraintElem: n->keys = NULL; n->options = $2; n->indexspace = $3; + n->deferrable = FALSE; + n->initdeferred = FALSE; $$ = (Node *)n; } | PRIMARY KEY opt_definition OptConsTableSpace @@ -2255,6 +2261,8 @@ ColConstraintElem: n->keys = NULL; n->options = $3; n->indexspace = $4; + n->deferrable = FALSE; + n->initdeferred = FALSE; $$ = (Node *)n; } | CHECK '(' a_expr ')' @@ -2266,6 +2274,8 @@ ColConstraintElem: n->cooked_expr = NULL; n->keys = NULL; n->indexspace = NULL; + n->deferrable = FALSE; + n->initdeferred = FALSE; $$ = (Node *)n; } | DEFAULT b_expr @@ -2277,6 +2287,8 @@ ColConstraintElem: 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 @@ -2398,7 +2410,7 @@ TableConstraint: ; ConstraintElem: - CHECK '(' a_expr ')' + CHECK '(' a_expr ')' ConstraintAttributeSpec { Constraint *n = makeNode(Constraint); n->contype = CONSTR_CHECK; @@ -2406,9 +2418,17 @@ ConstraintElem: n->raw_expr = $3; n->cooked_expr = NULL; n->indexspace = NULL; + if ($5 != 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("CHECK constraints cannot be deferred"), + parser_errposition(@5))); + n->deferrable = FALSE; + n->initdeferred = FALSE; $$ = (Node *)n; } | UNIQUE '(' columnList ')' opt_definition OptConsTableSpace + ConstraintAttributeSpec { Constraint *n = makeNode(Constraint); n->contype = CONSTR_UNIQUE; @@ -2418,9 +2438,12 @@ ConstraintElem: 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 + ConstraintAttributeSpec { Constraint *n = makeNode(Constraint); n->contype = CONSTR_PRIMARY; @@ -2430,6 +2453,8 @@ ConstraintElem: n->keys = $4; n->options = $6; n->indexspace = $7; + n->deferrable = ($8 & 1) != 0; + n->initdeferred = ($8 & 2) != 0; $$ = (Node *)n; } | FOREIGN KEY '(' columnList ')' REFERENCES qualified_name diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index a5d805aa98b..94c8c5977bc 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -19,7 +19,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/backend/parser/parse_utilcmd.c,v 2.23 2009/07/16 06:33:43 petere Exp $ + * $PostgreSQL: pgsql/src/backend/parser/parse_utilcmd.c,v 2.24 2009/07/29 20:56:19 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -33,6 +33,7 @@ #include "catalog/heap.h" #include "catalog/index.h" #include "catalog/namespace.h" +#include "catalog/pg_constraint.h" #include "catalog/pg_opclass.h" #include "catalog/pg_type.h" #include "commands/defrem.h" @@ -801,15 +802,38 @@ generateClonedIndexStmt(CreateStmtContext *cxt, Relation source_idx, /* * If the index is marked PRIMARY, it's certainly from a constraint; else, - * if it's not marked UNIQUE, it certainly isn't; else, we have to search - * pg_depend to see if there's an associated unique constraint. + * if it's not marked UNIQUE, it certainly isn't. If it is or might be + * from a constraint, we have to fetch the constraint to check for + * deferrability attributes. */ - if (index->primary) - index->isconstraint = true; - else if (!index->unique) - index->isconstraint = false; + if (index->primary || index->unique) + { + Oid constraintId = get_index_constraint(source_relid); + + if (OidIsValid(constraintId)) + { + HeapTuple ht_constr; + Form_pg_constraint conrec; + + ht_constr = SearchSysCache(CONSTROID, + ObjectIdGetDatum(constraintId), + 0, 0, 0); + if (!HeapTupleIsValid(ht_constr)) + elog(ERROR, "cache lookup failed for constraint %u", + constraintId); + conrec = (Form_pg_constraint) GETSTRUCT(ht_constr); + + index->isconstraint = true; + index->deferrable = conrec->condeferrable; + index->initdeferred = conrec->condeferred; + + ReleaseSysCache(ht_constr); + } + else + index->isconstraint = false; + } else - index->isconstraint = OidIsValid(get_index_constraint(source_relid)); + index->isconstraint = false; /* Get the index expressions, if any */ datum = SysCacheGetAttr(INDEXRELID, ht_idx, @@ -1039,7 +1063,9 @@ transformIndexConstraints(ParseState *pstate, CreateStmtContext *cxt) if (equal(index->indexParams, priorindex->indexParams) && equal(index->whereClause, priorindex->whereClause) && - strcmp(index->accessMethod, priorindex->accessMethod) == 0) + strcmp(index->accessMethod, priorindex->accessMethod) == 0 && + index->deferrable == priorindex->deferrable && + index->initdeferred == priorindex->initdeferred) { priorindex->unique |= index->unique; @@ -1092,6 +1118,8 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) */ } index->isconstraint = true; + index->deferrable = constraint->deferrable; + index->initdeferred = constraint->initdeferred; if (constraint->name != NULL) index->idxname = pstrdup(constraint->name); @@ -1853,8 +1881,9 @@ transformAlterTableStmt(AlterTableStmt *stmt, const char *queryString) * 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. + * NOTE: currently, attributes are only supported for FOREIGN KEY, UNIQUE, + * and PRIMARY KEY constraints, but someday they ought to be supported + * for other constraint types. */ static void transformConstraintAttrs(List *constraintList) @@ -1864,6 +1893,13 @@ transformConstraintAttrs(List *constraintList) bool saw_initially = false; ListCell *clist; +#define SUPPORTS_ATTRS(node) \ + ((node) != NULL && \ + (IsA((node), FkConstraint) || \ + (IsA((node), Constraint) && \ + (((Constraint *) (node))->contype == CONSTR_PRIMARY || \ + ((Constraint *) (node))->contype == CONSTR_UNIQUE)))) + foreach(clist, constraintList) { Node *node = lfirst(clist); @@ -1882,8 +1918,7 @@ transformConstraintAttrs(List *constraintList) switch (con->contype) { case CONSTR_ATTR_DEFERRABLE: - if (lastprimarynode == NULL || - !IsA(lastprimarynode, FkConstraint)) + if (!SUPPORTS_ATTRS(lastprimarynode)) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("misplaced DEFERRABLE clause"))); @@ -1892,11 +1927,14 @@ transformConstraintAttrs(List *constraintList) (errcode(ERRCODE_SYNTAX_ERROR), errmsg("multiple DEFERRABLE/NOT DEFERRABLE clauses not allowed"))); saw_deferrability = true; - ((FkConstraint *) lastprimarynode)->deferrable = true; + if (IsA(lastprimarynode, FkConstraint)) + ((FkConstraint *) lastprimarynode)->deferrable = true; + else + ((Constraint *) lastprimarynode)->deferrable = true; break; + case CONSTR_ATTR_NOT_DEFERRABLE: - if (lastprimarynode == NULL || - !IsA(lastprimarynode, FkConstraint)) + if (!SUPPORTS_ATTRS(lastprimarynode)) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("misplaced NOT DEFERRABLE clause"))); @@ -1905,16 +1943,28 @@ transformConstraintAttrs(List *constraintList) (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"))); + 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 (lastprimarynode == NULL || - !IsA(lastprimarynode, FkConstraint)) + if (!SUPPORTS_ATTRS(lastprimarynode)) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("misplaced INITIALLY DEFERRED clause"))); @@ -1923,21 +1973,36 @@ transformConstraintAttrs(List *constraintList) (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"))); + 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 (lastprimarynode == NULL || - !IsA(lastprimarynode, FkConstraint)) + if (!SUPPORTS_ATTRS(lastprimarynode)) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("misplaced INITIALLY IMMEDIATE clause"))); @@ -1946,8 +2011,12 @@ transformConstraintAttrs(List *constraintList) (errcode(ERRCODE_SYNTAX_ERROR), errmsg("multiple INITIALLY IMMEDIATE/DEFERRED clauses not allowed"))); saw_initially = true; - ((FkConstraint *) lastprimarynode)->initdeferred = false; + if (IsA(lastprimarynode, FkConstraint)) + ((FkConstraint *) lastprimarynode)->initdeferred = false; + else + ((Constraint *) lastprimarynode)->initdeferred = false; break; + default: /* Otherwise it's not an attribute */ lastprimarynode = node; diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 126a079f3e4..c6fefccfc6f 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -10,7 +10,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/tcop/utility.c,v 1.312 2009/07/28 02:56:30 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/tcop/utility.c,v 1.313 2009/07/29 20:56:19 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -795,6 +795,8 @@ ProcessUtility(Node *parsetree, stmt->unique, stmt->primary, stmt->isconstraint, + stmt->deferrable, + stmt->initdeferred, false, /* is_alter_table */ true, /* check_rights */ false, /* skip_build */ @@ -929,7 +931,7 @@ ProcessUtility(Node *parsetree, case T_CreateTrigStmt: CreateTrigger((CreateTrigStmt *) parsetree, - InvalidOid, InvalidOid, true); + InvalidOid, InvalidOid, NULL, true); break; case T_DropPropertyStmt: diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 9767b9f1f50..752e84dbfea 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.304 2009/07/24 21:08:42 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.305 2009/07/29 20:56:19 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -1044,11 +1044,6 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, if (string) appendStringInfo(&buf, " ON DELETE %s", string); - if (conForm->condeferrable) - appendStringInfo(&buf, " DEFERRABLE"); - if (conForm->condeferred) - appendStringInfo(&buf, " INITIALLY DEFERRED"); - break; } case CONSTRAINT_PRIMARY: @@ -1150,6 +1145,11 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, break; } + if (conForm->condeferrable) + appendStringInfo(&buf, " DEFERRABLE"); + if (conForm->condeferred) + appendStringInfo(&buf, " INITIALLY DEFERRED"); + /* Cleanup */ ReleaseSysCache(tup); diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index 775865d5696..639593743d0 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/cache/relcache.c,v 1.287 2009/06/11 14:49:05 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/utils/cache/relcache.c,v 1.288 2009/07/29 20:56:19 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -2938,7 +2938,7 @@ RelationGetIndexList(Relation relation) /* Check to see if it is a unique, non-partial btree index on OID */ if (index->indnatts == 1 && - index->indisunique && + index->indisunique && index->indimmediate && index->indkey.values[0] == ObjectIdAttributeNumber && index->indclass.values[0] == OID_BTREE_OPS_OID && heap_attisnull(htup, Anum_pg_index_indpred)) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index e05bd53d9ca..b1883efe755 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -12,7 +12,7 @@ * by PostgreSQL * * IDENTIFICATION - * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.542 2009/07/23 22:59:40 tgl Exp $ + * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.543 2009/07/29 20:56:19 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -3655,6 +3655,8 @@ getIndexes(TableInfo tblinfo[], int numTables) i_indisclustered, i_contype, i_conname, + i_condeferrable, + i_condeferred, i_contableoid, i_conoid, i_tablespace, @@ -3696,6 +3698,7 @@ getIndexes(TableInfo tblinfo[], int numTables) "t.relnatts AS indnkeys, " "i.indkey, i.indisclustered, " "c.contype, c.conname, " + "c.condeferrable, c.condeferred, " "c.tableoid AS contableoid, " "c.oid AS conoid, " "(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, " @@ -3722,6 +3725,7 @@ getIndexes(TableInfo tblinfo[], int numTables) "t.relnatts AS indnkeys, " "i.indkey, i.indisclustered, " "c.contype, c.conname, " + "c.condeferrable, c.condeferred, " "c.tableoid AS contableoid, " "c.oid AS conoid, " "(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, " @@ -3748,6 +3752,7 @@ getIndexes(TableInfo tblinfo[], int numTables) "t.relnatts AS indnkeys, " "i.indkey, i.indisclustered, " "c.contype, c.conname, " + "c.condeferrable, c.condeferred, " "c.tableoid AS contableoid, " "c.oid AS conoid, " "NULL AS tablespace, " @@ -3776,6 +3781,8 @@ getIndexes(TableInfo tblinfo[], int numTables) "CASE WHEN i.indisprimary THEN 'p'::char " "ELSE '0'::char END AS contype, " "t.relname AS conname, " + "false AS condeferrable, " + "false AS condeferred, " "0::oid AS contableoid, " "t.oid AS conoid, " "NULL AS tablespace, " @@ -3799,6 +3806,8 @@ getIndexes(TableInfo tblinfo[], int numTables) "CASE WHEN i.indisprimary THEN 'p'::char " "ELSE '0'::char END AS contype, " "t.relname AS conname, " + "false AS condeferrable, " + "false AS condeferred, " "0::oid AS contableoid, " "t.oid AS conoid, " "NULL AS tablespace, " @@ -3824,6 +3833,8 @@ getIndexes(TableInfo tblinfo[], int numTables) i_indisclustered = PQfnumber(res, "indisclustered"); i_contype = PQfnumber(res, "contype"); i_conname = PQfnumber(res, "conname"); + i_condeferrable = PQfnumber(res, "condeferrable"); + i_condeferred = PQfnumber(res, "condeferred"); i_contableoid = PQfnumber(res, "contableoid"); i_conoid = PQfnumber(res, "conoid"); i_tablespace = PQfnumber(res, "tablespace"); @@ -3884,6 +3895,8 @@ getIndexes(TableInfo tblinfo[], int numTables) constrinfo[j].condef = NULL; constrinfo[j].confrelid = InvalidOid; constrinfo[j].conindex = indxinfo[j].dobj.dumpId; + constrinfo[j].condeferrable = *(PQgetvalue(res, j, i_condeferrable)) == 't'; + constrinfo[j].condeferred = *(PQgetvalue(res, j, i_condeferred)) == 't'; constrinfo[j].conislocal = true; constrinfo[j].separate = true; @@ -3988,6 +4001,8 @@ getConstraints(TableInfo tblinfo[], int numTables) constrinfo[j].condef = strdup(PQgetvalue(res, j, i_condef)); constrinfo[j].confrelid = atooid(PQgetvalue(res, j, i_confrelid)); constrinfo[j].conindex = 0; + constrinfo[j].condeferrable = false; + constrinfo[j].condeferred = false; constrinfo[j].conislocal = true; constrinfo[j].separate = true; } @@ -4072,6 +4087,8 @@ getDomainConstraints(TypeInfo *tinfo) constrinfo[i].condef = strdup(PQgetvalue(res, i, i_consrc)); constrinfo[i].confrelid = InvalidOid; constrinfo[i].conindex = 0; + constrinfo[i].condeferrable = false; + constrinfo[i].condeferred = false; constrinfo[i].conislocal = true; constrinfo[i].separate = false; @@ -5071,6 +5088,8 @@ getTableAttrs(TableInfo *tblinfo, int numTables) constrs[j].condef = strdup(PQgetvalue(res, j, 3)); constrs[j].confrelid = InvalidOid; constrs[j].conindex = 0; + constrs[j].condeferrable = false; + constrs[j].condeferred = false; constrs[j].conislocal = (PQgetvalue(res, j, 4)[0] == 't'); constrs[j].separate = false; @@ -10454,6 +10473,13 @@ dumpConstraint(Archive *fout, ConstraintInfo *coninfo) if (indxinfo->options && strlen(indxinfo->options) > 0) appendPQExpBuffer(q, " WITH (%s)", indxinfo->options); + if (coninfo->condeferrable) + { + appendPQExpBuffer(q, " DEFERRABLE"); + if (coninfo->condeferred) + appendPQExpBuffer(q, " INITIALLY DEFERRED"); + } + appendPQExpBuffer(q, ";\n"); /* If the index is clustered, we need to record that. */ diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 3339bf75623..b6702cf5186 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.h,v 1.154 2009/06/11 14:49:07 momjian Exp $ + * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.h,v 1.155 2009/07/29 20:56:19 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -332,6 +332,9 @@ typedef struct _triggerInfo * struct ConstraintInfo is used for all constraint types. However we * use a different objType for foreign key constraints, to make it easier * to sort them the way we want. + * + * Note: condeferrable and condeferred are currently only valid for + * unique/primary-key constraints. Otherwise that info is in condef. */ typedef struct _constraintInfo { @@ -342,6 +345,8 @@ typedef struct _constraintInfo char *condef; /* definition, if CHECK or FOREIGN KEY */ Oid confrelid; /* referenced table, if FOREIGN KEY */ DumpId conindex; /* identifies associated index if any */ + bool condeferrable; /* TRUE if constraint is DEFERRABLE */ + bool condeferred; /* TRUE if constraint is INITIALLY DEFERRED */ bool conislocal; /* TRUE if constraint has local definition */ bool separate; /* TRUE if must dump as separate item */ } ConstraintInfo; diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 968b4218189..6e288da67a8 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -8,7 +8,7 @@ * * Copyright (c) 2000-2009, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.225 2009/07/20 03:46:45 tgl Exp $ + * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.226 2009/07/29 20:56:19 tgl Exp $ */ #include "postgres_fe.h" @@ -1321,11 +1321,32 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT i.indisunique, i.indisprimary, i.indisclustered, "); if (pset.sversion >= 80200) - appendPQExpBuffer(&buf, "i.indisvalid, "); + appendPQExpBuffer(&buf, "i.indisvalid,\n"); else - appendPQExpBuffer(&buf, "true as indisvalid, "); - appendPQExpBuffer(&buf, "a.amname, c2.relname,\n" - " pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n" + appendPQExpBuffer(&buf, "true AS indisvalid,\n"); + if (pset.sversion >= 80500) + appendPQExpBuffer(&buf, + " (NOT i.indimmediate) AND " + "EXISTS (SELECT 1 FROM pg_catalog.pg_depend d, " + "pg_catalog.pg_constraint con WHERE " + "d.classid = 'pg_catalog.pg_class'::pg_catalog.regclass AND " + "d.objid = i.indexrelid AND " + "d.refclassid = 'pg_catalog.pg_constraint'::pg_catalog.regclass AND " + "d.refobjid = con.oid AND d.deptype = 'i' AND " + "con.condeferrable) AS condeferrable,\n" + " (NOT i.indimmediate) AND " + "EXISTS (SELECT 1 FROM pg_catalog.pg_depend d, " + "pg_catalog.pg_constraint con WHERE " + "d.classid = 'pg_catalog.pg_class'::pg_catalog.regclass AND " + "d.objid = i.indexrelid AND " + "d.refclassid = 'pg_catalog.pg_constraint'::pg_catalog.regclass AND " + "d.refobjid = con.oid AND d.deptype = 'i' AND " + "con.condeferred) AS condeferred,\n"); + else + appendPQExpBuffer(&buf, + " false AS condeferrable, false AS condeferred,\n"); + appendPQExpBuffer(&buf, " a.amname, c2.relname, " + "pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n" "FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n" "WHERE i.indexrelid = c.oid AND c.oid = '%s' AND c.relam = a.oid\n" "AND i.indrelid = c2.oid", @@ -1345,9 +1366,11 @@ describeOneTableDetails(const char *schemaname, char *indisprimary = PQgetvalue(result, 0, 1); char *indisclustered = PQgetvalue(result, 0, 2); char *indisvalid = PQgetvalue(result, 0, 3); - char *indamname = PQgetvalue(result, 0, 4); - char *indtable = PQgetvalue(result, 0, 5); - char *indpred = PQgetvalue(result, 0, 6); + char *deferrable = PQgetvalue(result, 0, 4); + char *deferred = PQgetvalue(result, 0, 5); + char *indamname = PQgetvalue(result, 0, 6); + char *indtable = PQgetvalue(result, 0, 7); + char *indpred = PQgetvalue(result, 0, 8); if (strcmp(indisprimary, "t") == 0) printfPQExpBuffer(&tmpbuf, _("primary key, ")); @@ -1370,6 +1393,12 @@ describeOneTableDetails(const char *schemaname, if (strcmp(indisvalid, "t") != 0) appendPQExpBuffer(&tmpbuf, _(", invalid")); + if (strcmp(deferrable, "t") == 0) + appendPQExpBuffer(&tmpbuf, _(", deferrable")); + + if (strcmp(deferred, "t") == 0) + appendPQExpBuffer(&tmpbuf, _(", initially deferred")); + printTableAddFooter(&cont, tmpbuf.data); add_tablespace_footer(&cont, tableinfo.relkind, tableinfo.tablespace, true); @@ -1431,6 +1460,26 @@ describeOneTableDetails(const char *schemaname, else appendPQExpBuffer(&buf, "true as indisvalid, "); appendPQExpBuffer(&buf, "pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)"); + if (pset.sversion >= 80500) + appendPQExpBuffer(&buf, + ",\n (NOT i.indimmediate) AND " + "EXISTS (SELECT 1 FROM pg_catalog.pg_depend d, " + "pg_catalog.pg_constraint con WHERE " + "d.classid = 'pg_catalog.pg_class'::pg_catalog.regclass AND " + "d.objid = i.indexrelid AND " + "d.refclassid = 'pg_catalog.pg_constraint'::pg_catalog.regclass AND " + "d.refobjid = con.oid AND d.deptype = 'i' AND " + "con.condeferrable) AS condeferrable" + ",\n (NOT i.indimmediate) AND " + "EXISTS (SELECT 1 FROM pg_catalog.pg_depend d, " + "pg_catalog.pg_constraint con WHERE " + "d.classid = 'pg_catalog.pg_class'::pg_catalog.regclass AND " + "d.objid = i.indexrelid AND " + "d.refclassid = 'pg_catalog.pg_constraint'::pg_catalog.regclass AND " + "d.refobjid = con.oid AND d.deptype = 'i' AND " + "con.condeferred) AS condeferred"); + else + appendPQExpBuffer(&buf, ", false AS condeferrable, false AS condeferred"); if (pset.sversion >= 80000) appendPQExpBuffer(&buf, ", c2.reltablespace"); appendPQExpBuffer(&buf, @@ -1477,12 +1526,18 @@ describeOneTableDetails(const char *schemaname, if (strcmp(PQgetvalue(result, i, 4), "t") != 0) appendPQExpBuffer(&buf, " INVALID"); + if (strcmp(PQgetvalue(result, i, 6), "t") == 0) + appendPQExpBuffer(&buf, " DEFERRABLE"); + + if (strcmp(PQgetvalue(result, i, 7), "t") == 0) + appendPQExpBuffer(&buf, " INITIALLY DEFERRED"); + printTableAddFooter(&cont, buf.data); /* Print tablespace of the index on the same line */ if (pset.sversion >= 80000) add_tablespace_footer(&cont, 'i', - atooid(PQgetvalue(result, i, 6)), + atooid(PQgetvalue(result, i, 8)), false); } } @@ -1677,7 +1732,7 @@ describeOneTableDetails(const char *schemaname, PQclear(result); } - /* print triggers (but ignore foreign-key triggers) */ + /* print triggers (but ignore RI and unique constraint triggers) */ if (tableinfo.hastriggers) { printfPQExpBuffer(&buf, diff --git a/src/include/access/genam.h b/src/include/access/genam.h index bf3fe96e26d..a8c0e029296 100644 --- a/src/include/access/genam.h +++ b/src/include/access/genam.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/access/genam.h,v 1.78 2009/06/11 14:49:08 momjian Exp $ + * $PostgreSQL: pgsql/src/include/access/genam.h,v 1.79 2009/07/29 20:56:19 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -85,6 +85,34 @@ typedef bool (*IndexBulkDeleteCallback) (ItemPointer itemptr, void *state); typedef struct IndexScanDescData *IndexScanDesc; typedef struct SysScanDescData *SysScanDesc; +/* + * Enumeration specifying the type of uniqueness check to perform in + * index_insert(). + * + * UNIQUE_CHECK_YES is the traditional Postgres immediate check, possibly + * blocking to see if a conflicting transaction commits. + * + * For deferrable unique constraints, UNIQUE_CHECK_PARTIAL is specified at + * insertion time. The index AM should test if the tuple is unique, but + * should not throw error, block, or prevent the insertion if the tuple + * appears not to be unique. We'll recheck later when it is time for the + * constraint to be enforced. The AM must return true if the tuple is + * known unique, false if it is possibly non-unique. In the "true" case + * it is safe to omit the later recheck. + * + * When it is time to recheck the deferred constraint, a pseudo-insertion + * call is made with UNIQUE_CHECK_EXISTING. The tuple is already in the + * index in this case, so it should not be inserted again. Rather, just + * check for conflicting live tuples (possibly blocking). + */ +typedef enum IndexUniqueCheck +{ + UNIQUE_CHECK_NO, /* Don't do any uniqueness checking */ + UNIQUE_CHECK_YES, /* Enforce uniqueness at insertion time */ + UNIQUE_CHECK_PARTIAL, /* Test uniqueness, but no error */ + UNIQUE_CHECK_EXISTING /* Check if existing tuple is unique */ +} IndexUniqueCheck; + /* * generalized index_ interface routines (in indexam.c) @@ -103,7 +131,7 @@ extern bool index_insert(Relation indexRelation, Datum *values, bool *isnull, ItemPointer heap_t_ctid, Relation heapRelation, - bool check_uniqueness); + IndexUniqueCheck checkUnique); extern IndexScanDesc index_beginscan(Relation heapRelation, Relation indexRelation, diff --git a/src/include/access/nbtree.h b/src/include/access/nbtree.h index 1d3e42d99bc..ed5ec57e47f 100644 --- a/src/include/access/nbtree.h +++ b/src/include/access/nbtree.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/access/nbtree.h,v 1.124 2009/06/11 14:49:08 momjian Exp $ + * $PostgreSQL: pgsql/src/include/access/nbtree.h,v 1.125 2009/07/29 20:56:19 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -517,8 +517,8 @@ extern Datum btoptions(PG_FUNCTION_ARGS); /* * prototypes for functions in nbtinsert.c */ -extern void _bt_doinsert(Relation rel, IndexTuple itup, - bool index_is_unique, Relation heapRel); +extern bool _bt_doinsert(Relation rel, IndexTuple itup, + IndexUniqueCheck checkUnique, 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); diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index ecbf1056063..847362ad273 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.533 2009/07/28 02:56:30 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.534 2009/07/29 20:56:19 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 200907271 +#define CATALOG_VERSION_NO 200907291 #endif diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index 18064889aa1..a432260058f 100644 --- a/src/include/catalog/index.h +++ b/src/include/catalog/index.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/index.h,v 1.77 2009/01/01 17:23:56 momjian Exp $ + * $PostgreSQL: pgsql/src/include/catalog/index.h,v 1.78 2009/07/29 20:56:20 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -39,6 +39,8 @@ extern Oid index_create(Oid heapRelationId, Datum reloptions, bool isprimary, bool isconstraint, + bool deferrable, + bool initdeferred, bool allow_system_table_mods, bool skip_build, bool concurrent); diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_attribute.h index b852a28cd51..e1c6fc83731 100644 --- a/src/include/catalog/pg_attribute.h +++ b/src/include/catalog/pg_attribute.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_attribute.h,v 1.148 2009/06/11 14:49:09 momjian Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_attribute.h,v 1.149 2009/07/29 20:56:20 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -469,14 +469,15 @@ DATA(insert ( 1259 tableoid 26 0 4 -7 0 -1 -1 t p i t f f t 0 _null_)); { 0, {"indnatts"}, 21, -1, 2, 3, 0, -1, -1, true, 'p', 's', true, false, false, true, 0, { 0 } }, \ { 0, {"indisunique"}, 16, -1, 1, 4, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0, { 0 } }, \ { 0, {"indisprimary"}, 16, -1, 1, 5, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0, { 0 } }, \ -{ 0, {"indisclustered"}, 16, -1, 1, 6, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0, { 0 } }, \ -{ 0, {"indisvalid"}, 16, -1, 1, 7, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0, { 0 } }, \ -{ 0, {"indcheckxmin"}, 16, -1, 1, 8, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0, { 0 } }, \ -{ 0, {"indisready"}, 16, -1, 1, 9, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0, { 0 } }, \ -{ 0, {"indkey"}, 22, -1, -1, 10, 1, -1, -1, false, 'p', 'i', true, false, false, true, 0, { 0 } }, \ -{ 0, {"indclass"}, 30, -1, -1, 11, 1, -1, -1, false, 'p', 'i', true, false, false, true, 0, { 0 } }, \ -{ 0, {"indoption"}, 22, -1, -1, 12, 1, -1, -1, false, 'p', 'i', true, false, false, true, 0, { 0 } }, \ -{ 0, {"indexprs"}, 25, -1, -1, 13, 0, -1, -1, false, 'x', 'i', false, false, false, true, 0, { 0 } }, \ -{ 0, {"indpred"}, 25, -1, -1, 14, 0, -1, -1, false, 'x', 'i', false, false, false, true, 0, { 0 } } +{ 0, {"indimmediate"}, 16, -1, 1, 6, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0, { 0 } }, \ +{ 0, {"indisclustered"}, 16, -1, 1, 7, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0, { 0 } }, \ +{ 0, {"indisvalid"}, 16, -1, 1, 8, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0, { 0 } }, \ +{ 0, {"indcheckxmin"}, 16, -1, 1, 9, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0, { 0 } }, \ +{ 0, {"indisready"}, 16, -1, 1, 10, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0, { 0 } }, \ +{ 0, {"indkey"}, 22, -1, -1, 11, 1, -1, -1, false, 'p', 'i', true, false, false, true, 0, { 0 } }, \ +{ 0, {"indclass"}, 30, -1, -1, 12, 1, -1, -1, false, 'p', 'i', true, false, false, true, 0, { 0 } }, \ +{ 0, {"indoption"}, 22, -1, -1, 13, 1, -1, -1, false, 'p', 'i', true, false, false, true, 0, { 0 } }, \ +{ 0, {"indexprs"}, 25, -1, -1, 14, 0, -1, -1, false, 'x', 'i', false, false, false, true, 0, { 0 } }, \ +{ 0, {"indpred"}, 25, -1, -1, 15, 0, -1, -1, false, 'x', 'i', false, false, false, true, 0, { 0 } } #endif /* PG_ATTRIBUTE_H */ diff --git a/src/include/catalog/pg_index.h b/src/include/catalog/pg_index.h index 1ec2e49a59a..36ffef5ed87 100644 --- a/src/include/catalog/pg_index.h +++ b/src/include/catalog/pg_index.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_index.h,v 1.47 2009/01/01 17:23:57 momjian Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_index.h,v 1.48 2009/07/29 20:56:20 tgl Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -35,6 +35,7 @@ CATALOG(pg_index,2610) BKI_WITHOUT_OIDS int2 indnatts; /* number of columns in index */ bool indisunique; /* is this a unique index? */ bool indisprimary; /* is this index for primary key? */ + bool indimmediate; /* is uniqueness enforced immediately? */ bool indisclustered; /* is this the index last clustered by? */ bool indisvalid; /* is this index valid for use by queries? */ bool indcheckxmin; /* must we wait for xmin to be old? */ @@ -62,21 +63,22 @@ typedef FormData_pg_index *Form_pg_index; * compiler constants for pg_index * ---------------- */ -#define Natts_pg_index 14 +#define Natts_pg_index 15 #define Anum_pg_index_indexrelid 1 #define Anum_pg_index_indrelid 2 #define Anum_pg_index_indnatts 3 #define Anum_pg_index_indisunique 4 #define Anum_pg_index_indisprimary 5 -#define Anum_pg_index_indisclustered 6 -#define Anum_pg_index_indisvalid 7 -#define Anum_pg_index_indcheckxmin 8 -#define Anum_pg_index_indisready 9 -#define Anum_pg_index_indkey 10 -#define Anum_pg_index_indclass 11 -#define Anum_pg_index_indoption 12 -#define Anum_pg_index_indexprs 13 -#define Anum_pg_index_indpred 14 +#define Anum_pg_index_indimmediate 6 +#define Anum_pg_index_indisclustered 7 +#define Anum_pg_index_indisvalid 8 +#define Anum_pg_index_indcheckxmin 9 +#define Anum_pg_index_indisready 10 +#define Anum_pg_index_indkey 11 +#define Anum_pg_index_indclass 12 +#define Anum_pg_index_indoption 13 +#define Anum_pg_index_indexprs 14 +#define Anum_pg_index_indpred 15 /* * Index AMs that support ordered scans must support these two indoption diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 218ae741766..d5884faa4a6 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.546 2009/07/07 18:49:16 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.547 2009/07/29 20:56:20 tgl Exp $ * * NOTES * The script catalog/genbki.sh reads this file and generates .bki @@ -2323,6 +2323,10 @@ DESCR("convert generic options array to name/value table"); DATA(insert OID = 1619 ( pg_typeof PGNSP PGUID 12 1 0 0 f f f f f s 1 0 2206 "2276" _null_ _null_ _null_ _null_ pg_typeof _null_ _null_ _null_ )); DESCR("returns the type of the argument"); +/* Deferrable unique constraint trigger */ +DATA(insert OID = 1250 ( unique_key_recheck PGNSP PGUID 12 1 0 0 f f f t f v 0 0 2279 "" _null_ _null_ _null_ _null_ unique_key_recheck _null_ _null_ _null_ )); +DESCR("deferred UNIQUE constraint check"); + /* Generic referential integrity constraint triggers */ DATA(insert OID = 1644 ( RI_FKey_check_ins PGNSP PGUID 12 1 0 0 f f f t f v 0 0 2279 "" _null_ _null_ _null_ _null_ RI_FKey_check_ins _null_ _null_ _null_ )); DESCR("referential integrity FOREIGN KEY ... REFERENCES"); diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h index 1d6ab7bbf4f..4396a497385 100644 --- a/src/include/commands/defrem.h +++ b/src/include/commands/defrem.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/commands/defrem.h,v 1.95 2009/07/16 06:33:45 petere Exp $ + * $PostgreSQL: pgsql/src/include/commands/defrem.h,v 1.96 2009/07/29 20:56:20 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -29,6 +29,8 @@ extern void DefineIndex(RangeVar *heapRelation, bool unique, bool primary, bool isconstraint, + bool deferrable, + bool initdeferred, bool is_alter_table, bool check_rights, bool skip_build, diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h index 3e14bbe3baa..c92337a5a1b 100644 --- a/src/include/commands/trigger.h +++ b/src/include/commands/trigger.h @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/commands/trigger.h,v 1.74 2009/07/28 02:56:31 tgl Exp $ + * $PostgreSQL: pgsql/src/include/commands/trigger.h,v 1.75 2009/07/29 20:56:20 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -105,7 +105,7 @@ extern PGDLLIMPORT int SessionReplicationRole; #define TRIGGER_DISABLED 'D' extern Oid CreateTrigger(CreateTrigStmt *stmt, - Oid constraintOid, Oid indexOid, + Oid constraintOid, Oid indexOid, const char *prefix, bool checkPermissions); extern void DropTrigger(Oid relid, const char *trigname, @@ -132,7 +132,8 @@ extern HeapTuple ExecBRInsertTriggers(EState *estate, HeapTuple trigtuple); extern void ExecARInsertTriggers(EState *estate, ResultRelInfo *relinfo, - HeapTuple trigtuple); + HeapTuple trigtuple, + List *recheckIndexes); extern void ExecBSDeleteTriggers(EState *estate, ResultRelInfo *relinfo); extern void ExecASDeleteTriggers(EState *estate, @@ -154,7 +155,8 @@ extern HeapTuple ExecBRUpdateTriggers(EState *estate, extern void ExecARUpdateTriggers(EState *estate, ResultRelInfo *relinfo, ItemPointer tupleid, - HeapTuple newtuple); + HeapTuple newtuple, + List *recheckIndexes); extern void ExecBSTruncateTriggers(EState *estate, ResultRelInfo *relinfo); extern void ExecASTruncateTriggers(EState *estate, diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h index 86148ed1354..2ad1e26b2a1 100644 --- a/src/include/executor/executor.h +++ b/src/include/executor/executor.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/executor/executor.h,v 1.157 2009/07/22 17:00:23 tgl Exp $ + * $PostgreSQL: pgsql/src/include/executor/executor.h,v 1.158 2009/07/29 20:56:20 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -302,8 +302,8 @@ extern void ExecCloseScanRelation(Relation scanrel); extern void ExecOpenIndices(ResultRelInfo *resultRelInfo); extern void ExecCloseIndices(ResultRelInfo *resultRelInfo); -extern void ExecInsertIndexTuples(TupleTableSlot *slot, ItemPointer tupleid, - EState *estate, bool is_vacuum); +extern List *ExecInsertIndexTuples(TupleTableSlot *slot, ItemPointer tupleid, + EState *estate, bool is_vacuum_full); extern void RegisterExprContextCallback(ExprContext *econtext, ExprContextCallbackFunction function, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 5947c6acc98..ae2f0873608 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -13,7 +13,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.398 2009/07/26 23:34:18 tgl Exp $ + * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.399 2009/07/29 20:56:21 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -1355,7 +1355,7 @@ typedef struct CreateStmt * 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). + * Constraint and FkConstraint nodes. * ---------- */ @@ -1385,6 +1385,8 @@ typedef struct Constraint 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; /* ---------- @@ -1555,12 +1557,11 @@ typedef struct CreateTrigStmt /* events uses the TRIGGER_TYPE bits defined in catalog/pg_trigger.h */ int16 events; /* INSERT/UPDATE/DELETE/TRUNCATE */ - /* The following are used for referential */ - /* integrity constraint triggers */ - bool isconstraint; /* This is an RI trigger */ + /* The following are used for constraint triggers (RI and unique checks) */ + bool isconstraint; /* This is a constraint trigger */ bool deferrable; /* [NOT] DEFERRABLE */ bool initdeferred; /* INITIALLY {DEFERRED|IMMEDIATE} */ - RangeVar *constrrel; /* opposite relation */ + RangeVar *constrrel; /* opposite relation, if RI trigger */ } CreateTrigStmt; /* ---------------------- @@ -1864,6 +1865,8 @@ typedef struct IndexStmt bool unique; /* is index unique? */ bool primary; /* is index on primary key? */ bool isconstraint; /* is it from a CONSTRAINT clause? */ + bool deferrable; /* is the constraint DEFERRABLE? */ + bool initdeferred; /* is the constraint INITIALLY DEFERRED? */ bool concurrent; /* should this be a concurrent index build? */ } IndexStmt; diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h index 5547b6c8c93..72b39f70685 100644 --- a/src/include/utils/builtins.h +++ b/src/include/utils/builtins.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.334 2009/07/16 06:33:46 petere Exp $ + * $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.335 2009/07/29 20:56:21 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -1029,6 +1029,9 @@ extern Datum window_nth_value(PG_FUNCTION_ARGS); /* access/transam/twophase.c */ extern Datum pg_prepared_xact(PG_FUNCTION_ARGS); +/* commands/constraint.c */ +extern Datum unique_key_recheck(PG_FUNCTION_ARGS); + /* commands/prepare.c */ extern Datum pg_prepared_statement(PG_FUNCTION_ARGS); diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out index c6f1f158ee5..7213192d5f3 100644 --- a/src/test/regress/expected/sanity_check.out +++ b/src/test/regress/expected/sanity_check.out @@ -164,7 +164,8 @@ FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = relnamespace WHERE relhasoids AND ((nspname ~ '^pg_') IS NOT FALSE) AND NOT EXISTS (SELECT 1 FROM pg_index i WHERE indrelid = c.oid - AND indkey[0] = -2 AND indnatts = 1 AND indisunique); + AND indkey[0] = -2 AND indnatts = 1 + AND indisunique AND indimmediate); relname | nspname ---------+--------- (0 rows) diff --git a/src/test/regress/input/constraints.source b/src/test/regress/input/constraints.source index 350f29152a2..178b159c701 100644 --- a/src/test/regress/input/constraints.source +++ b/src/test/regress/input/constraints.source @@ -259,3 +259,110 @@ SELECT '' AS five, * FROM UNIQUE_TBL; DROP TABLE UNIQUE_TBL; +-- +-- Deferrable unique constraints +-- + +CREATE TABLE unique_tbl (i int UNIQUE DEFERRABLE, t text); + +INSERT INTO unique_tbl VALUES (0, 'one'); +INSERT INTO unique_tbl VALUES (1, 'two'); +INSERT INTO unique_tbl VALUES (2, 'tree'); +INSERT INTO unique_tbl VALUES (3, 'four'); +INSERT INTO unique_tbl VALUES (4, 'five'); + +BEGIN; + +-- default is immediate so this should fail right away +UPDATE unique_tbl SET i = 1 WHERE i = 0; + +ROLLBACK; + +-- check is done at end of statement, so this should succeed +UPDATE unique_tbl SET i = i+1; + +SELECT * FROM unique_tbl; + +-- explicitly defer the constraint +BEGIN; + +SET CONSTRAINTS unique_tbl_i_key DEFERRED; + +INSERT INTO unique_tbl VALUES (3, 'three'); +DELETE FROM unique_tbl WHERE t = 'tree'; -- makes constraint valid again + +COMMIT; -- should succeed + +SELECT * FROM unique_tbl; + +-- try adding an initially deferred constraint +ALTER TABLE unique_tbl DROP CONSTRAINT unique_tbl_i_key; +ALTER TABLE unique_tbl ADD CONSTRAINT unique_tbl_i_key + UNIQUE (i) DEFERRABLE INITIALLY DEFERRED; + +BEGIN; + +INSERT INTO unique_tbl VALUES (1, 'five'); +INSERT INTO unique_tbl VALUES (5, 'one'); +UPDATE unique_tbl SET i = 4 WHERE i = 2; +UPDATE unique_tbl SET i = 2 WHERE i = 4 AND t = 'four'; +DELETE FROM unique_tbl WHERE i = 1 AND t = 'one'; +DELETE FROM unique_tbl WHERE i = 5 AND t = 'five'; + +COMMIT; + +SELECT * FROM unique_tbl; + +-- should fail at commit-time +BEGIN; +INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now +COMMIT; -- should fail + +-- make constraint check immediate +BEGIN; + +SET CONSTRAINTS ALL IMMEDIATE; + +INSERT INTO unique_tbl VALUES (3, 'Three'); -- should fail + +COMMIT; + +-- forced check when SET CONSTRAINTS is called +BEGIN; + +SET CONSTRAINTS ALL DEFERRED; + +INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now + +SET CONSTRAINTS ALL IMMEDIATE; -- should fail + +COMMIT; + +-- test a HOT update that invalidates the conflicting tuple. +-- the trigger should still fire and catch the violation + +BEGIN; + +INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now +UPDATE unique_tbl SET t = 'THREE' WHERE i = 3 AND t = 'Three'; + +COMMIT; -- should fail + +SELECT * FROM unique_tbl; + +-- test a HOT update that modifies the newly inserted tuple, +-- but should succeed because we then remove the other conflicting tuple. + +BEGIN; + +INSERT INTO unique_tbl VALUES(3, 'tree'); -- should succeed for now +UPDATE unique_tbl SET t = 'threex' WHERE t = 'tree'; +DELETE FROM unique_tbl WHERE t = 'three'; + +SELECT * FROM unique_tbl; + +COMMIT; + +SELECT * FROM unique_tbl; + +DROP TABLE unique_tbl; diff --git a/src/test/regress/output/constraints.source b/src/test/regress/output/constraints.source index 654e8d99535..e21aa6b7e16 100644 --- a/src/test/regress/output/constraints.source +++ b/src/test/regress/output/constraints.source @@ -375,3 +375,132 @@ SELECT '' AS five, * FROM UNIQUE_TBL; (5 rows) DROP TABLE UNIQUE_TBL; +-- +-- Deferrable unique constraints +-- +CREATE TABLE unique_tbl (i int UNIQUE DEFERRABLE, t text); +NOTICE: CREATE TABLE / UNIQUE will create implicit index "unique_tbl_i_key" for table "unique_tbl" +INSERT INTO unique_tbl VALUES (0, 'one'); +INSERT INTO unique_tbl VALUES (1, 'two'); +INSERT INTO unique_tbl VALUES (2, 'tree'); +INSERT INTO unique_tbl VALUES (3, 'four'); +INSERT INTO unique_tbl VALUES (4, 'five'); +BEGIN; +-- default is immediate so this should fail right away +UPDATE unique_tbl SET i = 1 WHERE i = 0; +ERROR: duplicate key value violates unique constraint "unique_tbl_i_key" +ROLLBACK; +-- check is done at end of statement, so this should succeed +UPDATE unique_tbl SET i = i+1; +SELECT * FROM unique_tbl; + i | t +---+------ + 1 | one + 2 | two + 3 | tree + 4 | four + 5 | five +(5 rows) + +-- explicitly defer the constraint +BEGIN; +SET CONSTRAINTS unique_tbl_i_key DEFERRED; +INSERT INTO unique_tbl VALUES (3, 'three'); +DELETE FROM unique_tbl WHERE t = 'tree'; -- makes constraint valid again +COMMIT; -- should succeed +SELECT * FROM unique_tbl; + i | t +---+------- + 1 | one + 2 | two + 4 | four + 5 | five + 3 | three +(5 rows) + +-- try adding an initially deferred constraint +ALTER TABLE unique_tbl DROP CONSTRAINT unique_tbl_i_key; +ALTER TABLE unique_tbl ADD CONSTRAINT unique_tbl_i_key + UNIQUE (i) DEFERRABLE INITIALLY DEFERRED; +NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "unique_tbl_i_key" for table "unique_tbl" +BEGIN; +INSERT INTO unique_tbl VALUES (1, 'five'); +INSERT INTO unique_tbl VALUES (5, 'one'); +UPDATE unique_tbl SET i = 4 WHERE i = 2; +UPDATE unique_tbl SET i = 2 WHERE i = 4 AND t = 'four'; +DELETE FROM unique_tbl WHERE i = 1 AND t = 'one'; +DELETE FROM unique_tbl WHERE i = 5 AND t = 'five'; +COMMIT; +SELECT * FROM unique_tbl; + i | t +---+------- + 3 | three + 1 | five + 5 | one + 4 | two + 2 | four +(5 rows) + +-- should fail at commit-time +BEGIN; +INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now +COMMIT; -- should fail +ERROR: duplicate key value violates unique constraint "unique_tbl_i_key" +-- make constraint check immediate +BEGIN; +SET CONSTRAINTS ALL IMMEDIATE; +INSERT INTO unique_tbl VALUES (3, 'Three'); -- should fail +ERROR: duplicate key value violates unique constraint "unique_tbl_i_key" +COMMIT; +-- forced check when SET CONSTRAINTS is called +BEGIN; +SET CONSTRAINTS ALL DEFERRED; +INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now +SET CONSTRAINTS ALL IMMEDIATE; -- should fail +ERROR: duplicate key value violates unique constraint "unique_tbl_i_key" +COMMIT; +-- test a HOT update that invalidates the conflicting tuple. +-- the trigger should still fire and catch the violation +BEGIN; +INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now +UPDATE unique_tbl SET t = 'THREE' WHERE i = 3 AND t = 'Three'; +COMMIT; -- should fail +ERROR: duplicate key value violates unique constraint "unique_tbl_i_key" +SELECT * FROM unique_tbl; + i | t +---+------- + 3 | three + 1 | five + 5 | one + 4 | two + 2 | four +(5 rows) + +-- test a HOT update that modifies the newly inserted tuple, +-- but should succeed because we then remove the other conflicting tuple. +BEGIN; +INSERT INTO unique_tbl VALUES(3, 'tree'); -- should succeed for now +UPDATE unique_tbl SET t = 'threex' WHERE t = 'tree'; +DELETE FROM unique_tbl WHERE t = 'three'; +SELECT * FROM unique_tbl; + i | t +---+-------- + 1 | five + 5 | one + 4 | two + 2 | four + 3 | threex +(5 rows) + +COMMIT; +SELECT * FROM unique_tbl; + i | t +---+-------- + 1 | five + 5 | one + 4 | two + 2 | four + 3 | threex +(5 rows) + +DROP TABLE unique_tbl; diff --git a/src/test/regress/sql/sanity_check.sql b/src/test/regress/sql/sanity_check.sql index b0d800bc797..7ab0c91dfb9 100644 --- a/src/test/regress/sql/sanity_check.sql +++ b/src/test/regress/sql/sanity_check.sql @@ -22,4 +22,5 @@ FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = relnamespace WHERE relhasoids AND ((nspname ~ '^pg_') IS NOT FALSE) AND NOT EXISTS (SELECT 1 FROM pg_index i WHERE indrelid = c.oid - AND indkey[0] = -2 AND indnatts = 1 AND indisunique); + AND indkey[0] = -2 AND indnatts = 1 + AND indisunique AND indimmediate); |