*** a/doc/src/sgml/ref/alter_table.sgml --- b/doc/src/sgml/ref/alter_table.sgml *************** *** 61,66 **** ALTER TABLE name --- 61,73 ---- NO INHERIT parent_table OWNER TO new_owner SET TABLESPACE new_tablespace + + and index_constraint is: + + [ CONSTRAINT constraint_name ] + ( column_name operator [, ...] ) USING INDEX index_name + + *************** *** 905,911 **** ALTER TABLE distributors SET TABLESPACE fasttablespace; ALTER TABLE myschema.distributors SET SCHEMA yourschema; - --- 912,917 ---- *** a/doc/src/sgml/ref/create_table.sgml --- b/doc/src/sgml/ref/create_table.sgml *************** *** 51,57 **** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE column_name [, ... ] ) index_parameters | CHECK ( expression ) | FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] ! [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] index_parameters in UNIQUE and PRIMARY KEY constraints are: --- 51,58 ---- PRIMARY KEY ( column_name [, ... ] ) index_parameters | CHECK ( expression ) | FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] ! [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] | ! EXCLUSION [ USING index_method ] ( expression CHECK WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] index_parameters in UNIQUE and PRIMARY KEY constraints are: *************** *** 547,552 **** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE index_method ] ( expression CHECK WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] + + + The EXCLUSION clause specifies an operator exclusion + constraint. An operator exclusion constraint is more general + than a UNIQUE constraint, and can use an + arbitrary operator to detect conflicts. For instance, you can + specify the constraint that no two tuples in the table contain + overlapping circles (see ) by + using the && operator. + + + + The constraint specifies the conflict condition, so the operator + should return TRUE when applied to two + conflicting values. Also, the operator specified must be + commutative (that is, the commutator of the operator must be the + operator itself), must be a boolean operator, and must be + associated with an operator class + (see ) using + index_method. The + constraint is violated if, and only if, there exist two tuples + where all corresponding expressions between the tuples conflict + according + to operator + (i.e. the operator returns TRUE). + + + + Internally, operator exclusion constraints use an index to + perform a search looking for conflicting values, and handle + concurrent operations similar to a UNIQUE + constraint. If all of the operators are specified as the + equality operator (usually =), this + constraint behaves identically to a UNIQUE + constraint. However, it may exhibit slightly worse performance + than specifying UNIQUE, because operator + exclusion constraints require one additional index search. The + advantage of operator exclusion constraints is the ability to + specify more general constraints (like a non-overlapping + constraint for circles), and also the ability to use index + methods other than btree, such + as GiST (see ). + + + + The index_parameters + are the same as for a UNIQUE constraint. The predicate + allows you to specify the constraint on a subset of the table + (note the reqiuired parentheses around the predicate + expression), internally using a partial index + (see ). The expression + is normally just a column name, but can also be an expression or + function call and the constraint will check the result (similar + to creating a unique index over an expression). + + + + + DEFERRABLE NOT DEFERRABLE *************** *** 1110,1115 **** CREATE TABLE cinemas ( --- 1171,1188 ---- + + Create table circles with an operator exclusion + constraint that prevents overlapping circles within it: + + + CREATE TABLE circles ( + c circle, + EXCLUSION USING gist (c CHECK WITH &&) + ); + + + *** a/src/backend/access/index/indexam.c --- b/src/backend/access/index/indexam.c *************** *** 26,31 **** --- 26,32 ---- * index_vacuum_cleanup - post-deletion cleanup of an index * index_getprocid - get a support procedure OID * index_getprocinfo - get a support procedure's lookup info + * index_check_constraint - check operator exclusion constraints * * NOTES * This file contains the index_ routines which used *************** *** 64,72 **** --- 65,77 ---- #include "access/relscan.h" #include "access/transam.h" + #include "miscadmin.h" #include "pgstat.h" #include "storage/bufmgr.h" #include "storage/lmgr.h" + #include "storage/lwlock.h" + #include "storage/procarray.h" + #include "utils/lsyscache.h" #include "utils/relcache.h" #include "utils/snapmgr.h" #include "utils/tqual.h" *************** *** 116,122 **** do { \ static IndexScanDesc index_beginscan_internal(Relation indexRelation, int nkeys, ScanKey key); - /* ---------------------------------------------------------------- * index_ interface functions * ---------------------------------------------------------------- --- 121,126 ---- *** a/src/backend/bootstrap/bootparse.y --- b/src/backend/bootstrap/bootparse.y *************** *** 267,273 **** Boot_DeclareIndexStmt: $8, NULL, $10, ! NULL, NIL, false, false, false, false, false, false, false, true, false, false); do_end(); --- 267,273 ---- $8, NULL, $10, ! NULL, NIL, NULL, false, false, false, false, false, false, false, true, false, false); do_end(); *************** *** 285,291 **** Boot_DeclareUniqueIndexStmt: $9, NULL, $11, ! NULL, NIL, true, false, false, false, false, false, false, true, false, false); do_end(); --- 285,291 ---- $9, NULL, $11, ! NULL, NIL, NULL, true, false, false, false, false, false, false, true, false, false); do_end(); *** a/src/backend/bootstrap/bootstrap.c --- b/src/backend/bootstrap/bootstrap.c *************** *** 1101,1106 **** index_register(Oid heap, --- 1101,1109 ---- copyObject(indexInfo->ii_Predicate); newind->il_info->ii_PredicateState = NIL; + /* no operator exclusion constraints exist at bootstrap time */ + newind->il_info->ii_ExclusionConstraint = NULL; + newind->il_next = ILHead; ILHead = newind; *** a/src/backend/catalog/heap.c --- b/src/backend/catalog/heap.c *************** *** 676,681 **** InsertPgClassTuple(Relation pg_class_desc, --- 676,682 ---- values[Anum_pg_class_relkind - 1] = CharGetDatum(rd_rel->relkind); values[Anum_pg_class_relnatts - 1] = Int16GetDatum(rd_rel->relnatts); values[Anum_pg_class_relchecks - 1] = Int16GetDatum(rd_rel->relchecks); + values[Anum_pg_class_relopxconstraints - 1] = Int16GetDatum(rd_rel->relopxconstraints); values[Anum_pg_class_relhasoids - 1] = BoolGetDatum(rd_rel->relhasoids); values[Anum_pg_class_relhaspkey - 1] = BoolGetDatum(rd_rel->relhaspkey); values[Anum_pg_class_relhasrules - 1] = BoolGetDatum(rd_rel->relhasrules); *************** *** 1748,1753 **** StoreRelCheck(Relation rel, char *ccname, Node *expr, --- 1749,1755 ---- ' ', ' ', ' ', + NULL, expr, /* Tree form check constraint */ ccbin, /* Binary form check constraint */ ccsrc, /* Source form check constraint */ *** a/src/backend/catalog/index.c --- b/src/backend/catalog/index.c *************** *** 728,743 **** index_create(Oid heapRelationId, constraintType = CONSTRAINT_PRIMARY; else if (indexInfo->ii_Unique) constraintType = CONSTRAINT_UNIQUE; else { ! elog(ERROR, "constraint must be PRIMARY or UNIQUE"); constraintType = 0; /* keep compiler quiet */ } /* Shouldn't have any expressions */ ! if (indexInfo->ii_Expressions) elog(ERROR, "constraints cannot have index expressions"); conOid = CreateConstraintEntry(indexRelationName, namespaceId, constraintType, --- 728,750 ---- constraintType = CONSTRAINT_PRIMARY; else if (indexInfo->ii_Unique) constraintType = CONSTRAINT_UNIQUE; + else if (indexInfo->ii_ExclusionConstraint != NULL) + constraintType = CONSTRAINT_OPX; else { ! elog(ERROR, "constraint must be PRIMARY, UNIQUE or EXCLUSION"); constraintType = 0; /* keep compiler quiet */ } /* Shouldn't have any expressions */ ! if (indexInfo->ii_Expressions && ! constraintType != CONSTRAINT_OPX) elog(ERROR, "constraints cannot have index expressions"); + if (constraintType == CONSTRAINT_OPX && concurrent) + elog(ERROR, "concurrent index builds not supported for " + "operator exclusion constraints"); + conOid = CreateConstraintEntry(indexRelationName, namespaceId, constraintType, *************** *** 757,762 **** index_create(Oid heapRelationId, --- 764,770 ---- ' ', ' ', ' ', + indexInfo->ii_ExclusionConstraint, NULL, /* no check constraint */ NULL, NULL, *************** *** 803,808 **** index_create(Oid heapRelationId, --- 811,874 ---- "Unique_ConstraintTrigger", false); } + + CommandCounterIncrement(); + + /* Increment pg_class.relopxconstraints for the heap and index. */ + if (constraintType == CONSTRAINT_OPX) + { + Relation pgrel; + Form_pg_class heap_class; + HeapTuple relTup; + HeapTuple idxTup; + + pgrel = heap_open(RelationRelationId, RowExclusiveLock); + + /* Increment the count for the heap. */ + relTup = SearchSysCacheCopy(RELOID, + ObjectIdGetDatum(heapRelationId), + 0, 0, 0); + if (!HeapTupleIsValid(relTup)) + elog(ERROR, "cache lookup failed for relation %u", + heapRelationId); + heap_class = (Form_pg_class) GETSTRUCT(relTup); + + if (heap_class->relopxconstraints < 0) + elog(ERROR, "relation \"%s\" has relopxconstraints = %d", + RelationGetRelationName(heapRelation), + heap_class->relopxconstraints); + + heap_class->relopxconstraints++; + + simple_heap_update(pgrel, &relTup->t_self, relTup); + + CatalogUpdateIndexes(pgrel, relTup); + + heap_freetuple(relTup); + + /* Increment the count for the index. */ + idxTup = SearchSysCacheCopy(RELOID, + ObjectIdGetDatum(indexRelationId), + 0, 0, 0); + if (!HeapTupleIsValid(idxTup)) + elog(ERROR, "cache lookup failed for relation %u", + indexRelationId); + heap_class = (Form_pg_class) GETSTRUCT(idxTup); + + if (heap_class->relopxconstraints != 0) + elog(ERROR, "index \"%s\" has relopxconstraints = %d", + indexRelationName, heap_class->relopxconstraints); + + heap_class->relopxconstraints++; + + simple_heap_update(pgrel, &idxTup->t_self, idxTup); + + CatalogUpdateIndexes(pgrel, idxTup); + + heap_freetuple(idxTup); + + heap_close(pgrel, RowExclusiveLock); + } } else { *************** *** 1082,1087 **** BuildIndexInfo(Relation index) --- 1148,1157 ---- /* other info */ ii->ii_Unique = indexStruct->indisunique; ii->ii_ReadyForInserts = indexStruct->indisready; + if (index->rd_rel->relopxconstraints > 0) + ii->ii_ExclusionConstraint = RelationGetOpExclusionConstraints(index); + else + ii->ii_ExclusionConstraint = NULL; /* initialize index-build state to default */ ii->ii_Concurrent = false; *************** *** 1893,1898 **** IndexBuildHeapScan(Relation heapRelation, --- 1963,1971 ---- indexInfo->ii_ExpressionsState = NIL; indexInfo->ii_PredicateState = NIL; + /* operator exclusion constraints aren't supported at index build time. */ + indexInfo->ii_ExclusionConstraint = NULL; + return reltuples; } *************** *** 2267,2272 **** validate_index_heapscan(Relation heapRelation, --- 2340,2348 ---- /* These may have been pointing to the now-gone estate */ indexInfo->ii_ExpressionsState = NIL; indexInfo->ii_PredicateState = NIL; + + /* operator exclusion constraints aren't supported at index build time. */ + indexInfo->ii_ExclusionConstraint = NULL; } *************** *** 2522,2524 **** reindex_relation(Oid relid, bool toast_too) --- 2598,2601 ---- return result; } + *** a/src/backend/catalog/information_schema.sql --- b/src/backend/catalog/information_schema.sql *************** *** 1779,1784 **** CREATE VIEW table_constraints AS --- 1779,1785 ---- WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace AND c.conrelid = r.oid + AND c.contype IN ('c','f','p','u') AND r.relkind = 'r' AND (NOT pg_is_other_temp_schema(nr.oid)) AND (pg_has_role(r.relowner, 'USAGE') *** a/src/backend/catalog/pg_constraint.c --- b/src/backend/catalog/pg_constraint.c *************** *** 59,64 **** CreateConstraintEntry(const char *constraintName, --- 59,65 ---- char foreignUpdateType, char foreignDeleteType, char foreignMatchType, + const int16 *exclusion_constraint, Node *conExpr, const char *conBin, const char *conSrc, *************** *** 75,80 **** CreateConstraintEntry(const char *constraintName, --- 76,82 ---- ArrayType *conpfeqopArray; ArrayType *conppeqopArray; ArrayType *conffeqopArray; + ArrayType *constrategiesArray = NULL; NameData cname; int i; ObjectAddress conobject; *************** *** 130,135 **** CreateConstraintEntry(const char *constraintName, --- 132,149 ---- conffeqopArray = NULL; } + if (exclusion_constraint != NULL) + { + Datum *strategyDatums = palloc(sizeof(Datum) * constraintNKeys); + + for (i = 0; i < constraintNKeys; i++) + strategyDatums[i] = Int16GetDatum(exclusion_constraint[i]); + constrategiesArray = construct_array(strategyDatums, + constraintNKeys, + INT2OID, + sizeof(int16), true, 's'); + } + /* initialize nulls and values */ for (i = 0; i < Natts_pg_constraint; i++) { *************** *** 177,182 **** CreateConstraintEntry(const char *constraintName, --- 191,201 ---- else nulls[Anum_pg_constraint_conffeqop - 1] = true; + if (constrategiesArray) + values[Anum_pg_constraint_constrategies - 1] = PointerGetDatum(constrategiesArray); + else + nulls[Anum_pg_constraint_constrategies - 1] = true; + /* * initialize the binary form of the check constraint. */ *************** *** 389,394 **** ConstraintNameIsUsed(ConstraintCategory conCat, Oid objId, --- 408,418 ---- found = true; break; } + else if (conCat == CONSTRAINT_OPX && con->conrelid == objId) + { + found = true; + break; + } } systable_endscan(conscan); *************** *** 524,530 **** RemoveConstraintById(Oid conId) * being dropped. This update will force backends to rebuild relcache * entries when we commit. */ ! if (con->contype == CONSTRAINT_CHECK) { Relation pgrel; HeapTuple relTup; --- 548,555 ---- * being dropped. This update will force backends to rebuild relcache * entries when we commit. */ ! if (con->contype == CONSTRAINT_CHECK || ! con->contype == CONSTRAINT_OPX) { Relation pgrel; HeapTuple relTup; *************** *** 539,548 **** RemoveConstraintById(Oid conId) con->conrelid); classForm = (Form_pg_class) GETSTRUCT(relTup); ! if (classForm->relchecks == 0) /* should not happen */ ! elog(ERROR, "relation \"%s\" has relchecks = 0", ! RelationGetRelationName(rel)); ! classForm->relchecks--; simple_heap_update(pgrel, &relTup->t_self, relTup); --- 564,583 ---- con->conrelid); classForm = (Form_pg_class) GETSTRUCT(relTup); ! if (con->contype == CONSTRAINT_CHECK) ! { ! if (classForm->relchecks == 0) /* should not happen */ ! elog(ERROR, "relation \"%s\" has relchecks = 0", ! RelationGetRelationName(rel)); ! classForm->relchecks--; ! } ! else ! { ! if (classForm->relopxconstraints == 0) /* should not happen */ ! elog(ERROR, "relation \"%s\" has relopxconstraints = 0", ! RelationGetRelationName(rel)); ! classForm->relopxconstraints--; ! } simple_heap_update(pgrel, &relTup->t_self, relTup); *** a/src/backend/catalog/toasting.c --- b/src/backend/catalog/toasting.c *************** *** 244,249 **** create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid, --- 244,252 ---- indexInfo->ii_Concurrent = false; indexInfo->ii_BrokenHotChain = false; + /* toast tables don't have operator exclusion constraints */ + indexInfo->ii_ExclusionConstraint = NULL; + classObjectId[0] = OID_BTREE_OPS_OID; classObjectId[1] = INT4_BTREE_OPS_OID; *** a/src/backend/commands/constraint.c --- b/src/backend/commands/constraint.c *************** *** 149,156 **** unique_key_recheck(PG_FUNCTION_ARGS) * 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. --- 149,167 ---- * 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. */ ! if (indexInfo->ii_ExclusionConstraint == NULL) ! { ! index_insert(indexRel, values, isnull, &(new_row->t_self), ! trigdata->tg_relation, UNIQUE_CHECK_EXISTING); ! } ! else ! { ! index_check_constraint(trigdata->tg_relation, indexRel, ! slot, &(new_row->t_self), values, isnull, ! indexInfo->ii_ExclusionConstraint, ! indexInfo->ii_ExpressionsState, ! econtext, false); ! } /* * If that worked, then this index entry is unique, and we are done. *** a/src/backend/commands/indexcmds.c --- b/src/backend/commands/indexcmds.c *************** *** 62,69 **** static void ComputeIndexAttrs(IndexInfo *indexInfo, char *accessMethodName, Oid accessMethodId, bool amcanorder, bool isconstraint); - static Oid GetIndexOpClass(List *opclass, Oid attrType, - char *accessMethodName, Oid accessMethodId); static bool relationHasPrimaryKey(Relation rel); --- 62,67 ---- *************** *** 97,103 **** static bool relationHasPrimaryKey(Relation rel); * 'quiet': suppress the NOTICE chatter ordinarily provided for constraints. * 'concurrent': avoid blocking writers to the table while building. */ ! void DefineIndex(RangeVar *heapRelation, char *indexRelationName, Oid indexRelationId, --- 95,101 ---- * 'quiet': suppress the NOTICE chatter ordinarily provided for constraints. * 'concurrent': avoid blocking writers to the table while building. */ ! Oid DefineIndex(RangeVar *heapRelation, char *indexRelationName, Oid indexRelationId, *************** *** 106,111 **** DefineIndex(RangeVar *heapRelation, --- 104,110 ---- List *attributeList, Expr *predicate, List *options, + int16 *exclusion_constraint, bool unique, bool primary, bool isconstraint, *************** *** 247,256 **** DefineIndex(RangeVar *heapRelation, --- 246,266 ---- if (indexRelationName == NULL) { if (primary) + { indexRelationName = ChooseRelationName(RelationGetRelationName(rel), NULL, "pkey", namespaceId); + } + else if (exclusion_constraint != NULL) + { + IndexElem *iparam = (IndexElem *) linitial(attributeList); + + indexRelationName = ChooseRelationName(RelationGetRelationName(rel), + iparam->name, + "exclusion", + namespaceId); + } else { IndexElem *iparam = (IndexElem *) linitial(attributeList); *************** *** 424,429 **** DefineIndex(RangeVar *heapRelation, --- 434,442 ---- indexInfo->ii_Concurrent = concurrent; indexInfo->ii_BrokenHotChain = false; + /* operator exclusion constraints aren't supported at index build time. */ + indexInfo->ii_ExclusionConstraint = exclusion_constraint; + classObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid)); coloptions = (int16 *) palloc(numberOfAttributes * sizeof(int16)); ComputeIndexAttrs(indexInfo, classObjectId, coloptions, attributeList, *************** *** 435,445 **** DefineIndex(RangeVar *heapRelation, * error checks) */ if (isconstraint && !quiet) ereport(NOTICE, (errmsg("%s %s will create implicit index \"%s\" for table \"%s\"", is_alter_table ? "ALTER TABLE / ADD" : "CREATE TABLE /", ! primary ? "PRIMARY KEY" : "UNIQUE", indexRelationName, RelationGetRelationName(rel)))); /* save lockrelid and locktag for below, then close rel */ heaprelid = rel->rd_lockInfo.lockRelId; --- 448,470 ---- * error checks) */ if (isconstraint && !quiet) + { + char *constraint_type; + if (primary) + constraint_type = "PRIMARY KEY"; + else if (unique) + constraint_type = "UNIQUE"; + else if (exclusion_constraint != NULL) + constraint_type = "EXCLUSION"; + else + elog(ERROR, "unknown constraint type"); + ereport(NOTICE, (errmsg("%s %s will create implicit index \"%s\" for table \"%s\"", is_alter_table ? "ALTER TABLE / ADD" : "CREATE TABLE /", ! constraint_type, indexRelationName, RelationGetRelationName(rel)))); + } /* save lockrelid and locktag for below, then close rel */ heaprelid = rel->rd_lockInfo.lockRelId; *************** *** 455,461 **** DefineIndex(RangeVar *heapRelation, isconstraint, deferrable, initdeferred, allowSystemTableMods, skip_build, concurrent); ! return; /* We're done, in the standard case */ } /* --- 480,486 ---- isconstraint, deferrable, initdeferred, allowSystemTableMods, skip_build, concurrent); ! return indexRelationId; /* We're done, in the standard case */ } /* *************** *** 750,755 **** DefineIndex(RangeVar *heapRelation, --- 775,782 ---- * Last thing to do is release the session-level lock on the parent table. */ UnlockRelationIdForSession(&heaprelid, ShareUpdateExclusiveLock); + + return indexRelationId; } *************** *** 939,945 **** ComputeIndexAttrs(IndexInfo *indexInfo, /* * Resolve possibly-defaulted operator class specification */ ! static Oid GetIndexOpClass(List *opclass, Oid attrType, char *accessMethodName, Oid accessMethodId) { --- 966,972 ---- /* * Resolve possibly-defaulted operator class specification */ ! Oid GetIndexOpClass(List *opclass, Oid attrType, char *accessMethodName, Oid accessMethodId) { *** a/src/backend/commands/tablecmds.c --- b/src/backend/commands/tablecmds.c *************** *** 155,161 **** typedef struct NewConstraint Oid refrelid; /* PK rel, if FOREIGN */ Oid refindid; /* OID of PK's index, if FOREIGN */ Oid conid; /* OID of pg_constraint entry, if FOREIGN */ ! Node *qual; /* Check expr or CONSTR_FOREIGN Constraint */ List *qualstate; /* Execution state for CHECK */ } NewConstraint; --- 155,162 ---- Oid refrelid; /* PK rel, if FOREIGN */ Oid refindid; /* OID of PK's index, if FOREIGN */ Oid conid; /* OID of pg_constraint entry, if FOREIGN */ ! Oid conindid; /* OID of constraint index, if EXCLUSION */ ! Node *qual; /* Check expr if CHECK else Constraint */ List *qualstate; /* Execution state for CHECK */ } NewConstraint; *************** *** 305,310 **** static void ATAddCheckConstraint(List **wqueue, --- 306,314 ---- bool recurse, bool recursing); static void ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel, Constraint *fkconstraint); + static void ATAddOperatorExclusionConstraint(AlteredTableInfo *tab, + Relation rel, + Constraint *constraint); static void ATExecDropConstraint(Relation rel, const char *constrName, DropBehavior behavior, bool recurse, bool recursing, *************** *** 3037,3042 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap) --- 3041,3048 ---- int i; ListCell *l; EState *estate; + List *opxList = NIL; + int max_index_atts = 0; /* * Open the relation(s). We have surely already locked the existing *************** *** 3077,3082 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap) --- 3083,3091 ---- switch (con->contype) { + Relation indexRelation = NULL; + IndexInfo *indexInfo = NULL; + case CONSTR_CHECK: needscan = true; con->qualstate = (List *) *************** *** 3085,3090 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap) --- 3094,3127 ---- case CONSTR_FOREIGN: /* Nothing to do here */ break; + case CONSTR_OPERATOR_EXCLUSION: + needscan = true; + + if (newrel != NULL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot rewrite table while adding " + "operator exclusion constraint"))); + + indexRelation = index_open(con->conindid, AccessShareLock); + + indexInfo = BuildIndexInfo(indexRelation); + indexInfo->ii_PredicateState = (List *) + ExecPrepareExpr((Expr *) indexInfo->ii_Predicate, estate); + + opxList = lappend(opxList, + list_make2(indexRelation, indexInfo)); + + /* + * Keep track of the greatest number of index + * attributes for any operator exclusion constraint so + * that we can preallocate the idxvals/idxnulls + * arrays. + */ + if (indexInfo->ii_NumIndexAttrs > max_index_atts) + max_index_atts = indexInfo->ii_NumIndexAttrs; + + break; default: elog(ERROR, "unrecognized constraint type: %d", (int) con->contype); *************** *** 3119,3134 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap) if (newrel || needscan) { ! ExprContext *econtext; ! Datum *values; ! bool *isnull; ! TupleTableSlot *oldslot; ! TupleTableSlot *newslot; ! HeapScanDesc scan; ! HeapTuple tuple; ! MemoryContext oldCxt; ! List *dropped_attrs = NIL; ! ListCell *lc; econtext = GetPerTupleExprContext(estate); --- 3156,3173 ---- if (newrel || needscan) { ! ExprContext *econtext; ! Datum *values; ! bool *isnull; ! TupleTableSlot *oldslot; ! TupleTableSlot *newslot; ! HeapScanDesc scan; ! HeapTuple tuple; ! MemoryContext oldCxt; ! List *dropped_attrs = NIL; ! ListCell *lc; ! Datum *idxvals = NULL; ! bool *idxnulls = NULL; econtext = GetPerTupleExprContext(estate); *************** *** 3147,3152 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap) --- 3186,3200 ---- memset(values, 0, i * sizeof(Datum)); memset(isnull, true, i * sizeof(bool)); + /* Preallocate idxvals/idxnulls arrays */ + if (opxList != NIL) + { + idxvals = (Datum *) palloc(max_index_atts * sizeof(Datum)); + idxnulls = (bool *) palloc(max_index_atts * sizeof(bool)); + memset(idxvals, 0, max_index_atts * sizeof(Datum)); + memset(idxnulls, true, max_index_atts * sizeof(bool)); + } + /* * Any attributes that are dropped according to the new tuple * descriptor can be set to NULL. We precompute the list of dropped *************** *** 3172,3177 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap) --- 3220,3226 ---- while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL) { + if (newrel) { Oid tupOid = InvalidOid; *************** *** 3242,3247 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap) --- 3291,3297 ---- con->name))); break; case CONSTR_FOREIGN: + case CONSTR_OPERATOR_EXCLUSION: /* Nothing to do here */ break; default: *************** *** 3250,3255 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap) --- 3300,3325 ---- } } + foreach (l, opxList) + { + List *pair = (List *) lfirst(l); + Relation indexRelation = (Relation) linitial(pair); + IndexInfo *indexInfo = (IndexInfo *) lsecond(pair); + + FormIndexDatum(indexInfo, newslot, estate, idxvals, idxnulls); + + /* ignore tuples that don't match the constraint predicate */ + if (!ExecQual(indexInfo->ii_PredicateState, econtext, true)) + continue; + + /* check operator exclusion constraint */ + index_check_constraint(oldrel, indexRelation, newslot, + &tuple->t_self, idxvals, idxnulls, + indexInfo->ii_ExclusionConstraint, + indexInfo->ii_ExpressionsState, + econtext, false); + } + /* Write the tuple out to the new relation */ if (newrel) simple_heap_insert(newrel, tuple); *************** *** 3264,3269 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap) --- 3334,3352 ---- ExecDropSingleTupleTableSlot(oldslot); ExecDropSingleTupleTableSlot(newslot); + + if (idxvals != NULL) + pfree(idxvals); + if (idxnulls != NULL) + pfree(idxnulls); + + foreach (l, opxList) + { + List *pair = (List *) lfirst(l); + Relation indexRelation = (Relation) linitial(pair); + + index_close(indexRelation, NoLock); + } } FreeExecutorState(estate); *************** *** 3271,3276 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap) --- 3354,3360 ---- heap_close(oldrel, NoLock); if (newrel) heap_close(newrel, NoLock); + } /* *************** *** 4569,4574 **** ATExecAddIndex(AlteredTableInfo *tab, Relation rel, --- 4653,4659 ---- stmt->indexParams, /* parameters */ (Expr *) stmt->whereClause, stmt->options, + NULL, stmt->unique, stmt->primary, stmt->isconstraint, *************** *** 4632,4637 **** ATExecAddConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, --- 4717,4757 ---- ATAddForeignKeyConstraint(tab, rel, newConstraint); break; + case CONSTR_OPERATOR_EXCLUSION: + /* + * We don't recurse for operator exclusion constraints, either. + */ + if (newConstraint->conname) + { + if (ConstraintNameIsUsed(CONSTRAINT_OPX, + RelationGetRelid(rel), + RelationGetNamespace(rel), + newConstraint->conname)) + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("constraint \"%s\" for relation \"%s\" already exists", + newConstraint->conname, + RelationGetRelationName(rel)))); + } + else + { + char *choose_name2 = ""; + IndexElem *ie = linitial(newConstraint->operator_exclusion); + + if (ie->name != NULL) + choose_name2 = ie->name; + + newConstraint->conname = + ChooseConstraintName(RelationGetRelationName(rel), + choose_name2, + "exclusion", + RelationGetNamespace(rel), + NIL); + } + + ATAddOperatorExclusionConstraint(tab, rel, newConstraint); + break; + default: elog(ERROR, "unrecognized constraint type: %d", (int) newConstraint->contype); *************** *** 5001,5006 **** ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel, --- 5121,5127 ---- fkconstraint->fk_upd_action, fkconstraint->fk_del_action, fkconstraint->fk_matchtype, + NULL, NULL, /* no check constraint */ NULL, NULL, *************** *** 5037,5042 **** ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel, --- 5158,5322 ---- heap_close(pkrel, NoLock); } + static void + ATAddOperatorExclusionConstraint(AlteredTableInfo *tab, Relation rel, + Constraint *constraint) + { + int natts; + ListCell *lc; + HeapTuple tup; + Oid methodOid; + List *indexElems = NIL; + int16 *exclusion_constraint; + Oid gettupleOid; + Oid index_oid = InvalidOid; + Form_pg_am am; + RangeVar *rv; + int i; + + /* + * Find access method oid, and make sure it supports gettuple. + */ + tup = SearchSysCache(AMNAME, + CStringGetDatum(constraint->using_method), + 0, 0, 0); + if (!HeapTupleIsValid(tup)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("access method \"%s\" does not exist", + constraint->using_method))); + + methodOid = HeapTupleGetOid(tup); + am = (Form_pg_am) GETSTRUCT(tup); + gettupleOid = am->amgettuple; + + ReleaseSysCache(tup); + + if (!OidIsValid(gettupleOid)) + ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("method \"%s\" does not support gettuple", + constraint->using_method))); + + natts = list_length(constraint->operator_exclusion); + + exclusion_constraint = palloc(sizeof(int16) * natts); + + /* + * Create the strategies array from the input (IndexElem, Operator) + * pairs. Also, make an array of IndexElems to pass to DefineIndex(). + */ + i = 0; + foreach (lc, constraint->operator_exclusion) + { + List *pair = lfirst(lc); + List *opname; + IndexElem *elem; + Oid opfamily; + Oid opclassid; + Oid typoid; + Oid opid; + + Assert(list_length(pair) == 2); + + elem = linitial(pair); + Assert(IsA(elem, IndexElem)); + opname = lsecond(pair); + Assert(IsA(opname, List)); + + indexElems = lappend(indexElems, elem); + + if (elem->name != NULL) + { + AttrNumber heapatt = get_attnum(RelationGetRelid(rel), elem->name); + if (heapatt < 1) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("column \"%s\" does not exist", elem->name), + errhint("Cannot specify system column."))); + + typoid = rel->rd_att->attrs[heapatt - 1]->atttypid; + } + else + typoid = exprType(elem->expr); + + opid = LookupOperName(NULL, opname, typoid, typoid, false, -1); + + opclassid = GetIndexOpClass(elem->opclass, typoid, + constraint->using_method, methodOid); + + opfamily = get_opclass_family(opclassid); + + /* + * Only allow commutative operators to be used for operator + * exclusion constraints. If X conflicts with Y, but Y does + * not conflict with X, bad things will happen. + */ + if (get_commutator(opid) != opid) + { + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("operator %s for exclusion constraint must be " + "commutative", quote_identifier(get_opname(opid))), + errdetail("Set the operator's COMMUTATOR to be itself, " + "or choose a different operator.") + )); + } + + exclusion_constraint[i] = get_op_opfamily_strategy(opid, opfamily); + + if (exclusion_constraint[i] == InvalidStrategy) + elog(ERROR, "no strategy found for operator %d " + "in operator family %d", opid, opfamily); + + i++; + } + + rv = makeRangeVar(get_namespace_name(RelationGetNamespace(rel)), + pstrdup(RelationGetRelationName(rel)), + -1); + /* + * Build index to enforce the constraint. Set isconstraint to + * false because we're building the constraint entry ourselves. + */ + index_oid = DefineIndex(rv, /* relation range var */ + NULL, /* index name */ + InvalidOid, /* predefined OID */ + constraint->using_method, /* am name */ + constraint->indexspace, /* index tablespace */ + indexElems, /* parameters */ + (Expr *) constraint->where_clause, /* where */ + constraint->options, /* options */ + exclusion_constraint, /* exclusion constraint */ + false, /* unique */ + false, /* primary */ + true, /* is constraint? */ + constraint->deferrable, /* deferrable */ + constraint->initdeferred, /* init deferred */ + true, /* is_alter_table? */ + true, /* check rights? */ + false, /* skip build? */ + false, /* quiet? */ + false); /* concurrent? */ + + /* + * Tell Phase 3 to check that the constraint is satisfied by existing rows + * (we can skip this during table creation). + */ + if (!constraint->skip_validation) + { + NewConstraint *newcon; + + newcon = (NewConstraint *) palloc0(sizeof(NewConstraint)); + newcon->name = constraint->conname; + newcon->contype = CONSTR_OPERATOR_EXCLUSION; + newcon->conindid = index_oid; + newcon->qual = (Node *) constraint; + + tab->constraints = lappend(tab->constraints, newcon); + } + + + } /* * transformColumnNameList - transform list of column names *** a/src/backend/commands/typecmds.c --- b/src/backend/commands/typecmds.c *************** *** 2297,2302 **** domainAddConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid, --- 2297,2303 ---- ' ', ' ', ' ', + NULL, expr, /* Tree form check constraint */ ccbin, /* Binary form check constraint */ ccsrc, /* Source form check constraint */ *** a/src/backend/executor/execUtils.c --- b/src/backend/executor/execUtils.c *************** *** 44,53 **** --- 44,57 ---- #include "access/genam.h" #include "access/heapam.h" + #include "access/relscan.h" + #include "access/transam.h" #include "catalog/index.h" #include "executor/execdebug.h" #include "nodes/nodeFuncs.h" #include "parser/parsetree.h" + #include "storage/lmgr.h" + #include "utils/lsyscache.h" #include "utils/memutils.h" #include "utils/relcache.h" #include "utils/tqual.h" *************** *** 55,61 **** static bool get_last_attnums(Node *node, ProjectionInfo *projInfo); static void ShutdownExprContext(ExprContext *econtext, bool isCommit); ! /* ---------------------------------------------------------------- * Executor state and memory management functions --- 59,68 ---- static bool get_last_attnums(Node *node, ProjectionInfo *projInfo); static void ShutdownExprContext(ExprContext *econtext, bool isCommit); ! static bool index_recheck_constraint(Relation index, TupleTableSlot *slot, ! ExprContext *econtext, List *index_exprs, ! Datum *new_values, Oid *constr_procs); ! static char * tuple_as_string(TupleTableSlot *slot); /* ---------------------------------------------------------------- * Executor state and memory management functions *************** *** 1010,1016 **** ExecInsertIndexTuples(TupleTableSlot *slot, Relation indexRelation = relationDescs[i]; IndexInfo *indexInfo; IndexUniqueCheck checkUnique; ! bool isUnique; if (indexRelation == NULL) continue; --- 1017,1023 ---- Relation indexRelation = relationDescs[i]; IndexInfo *indexInfo; IndexUniqueCheck checkUnique; ! bool satisfiesConstraint; if (indexRelation == NULL) continue; *************** *** 1075,1081 **** ExecInsertIndexTuples(TupleTableSlot *slot, else checkUnique = UNIQUE_CHECK_PARTIAL; ! isUnique = index_insert(indexRelation, /* index relation */ values, /* array of index Datums */ isnull, /* null flags */ --- 1082,1088 ---- else checkUnique = UNIQUE_CHECK_PARTIAL; ! satisfiesConstraint = index_insert(indexRelation, /* index relation */ values, /* array of index Datums */ isnull, /* null flags */ *************** *** 1083,1089 **** ExecInsertIndexTuples(TupleTableSlot *slot, heapRelation, /* heap relation */ checkUnique); /* type of uniqueness check to do */ ! if (checkUnique == UNIQUE_CHECK_PARTIAL && !isUnique) { /* * The tuple potentially violates the uniqueness constraint, --- 1090,1118 ---- heapRelation, /* heap relation */ checkUnique); /* type of uniqueness check to do */ ! /* ! * Operator exclusion constraint check is simpler, because the ! * check is separated from the index insert. ! */ ! if (indexInfo->ii_ExclusionConstraint != NULL) ! { ! bool errorOK = !indexRelation->rd_index->indimmediate; ! ! /* ! * An index for an operator exclusion constraint can't ! * also be UNIQUE. ! */ ! satisfiesConstraint = ! index_check_constraint(heapRelation, indexRelation, ! slot, tupleid, values, isnull, ! indexInfo->ii_ExclusionConstraint, ! indexInfo->ii_ExpressionsState, ! econtext, errorOK); ! } ! ! if ((checkUnique == UNIQUE_CHECK_PARTIAL || ! indexInfo->ii_ExclusionConstraint != NULL) && ! !satisfiesConstraint) { /* * The tuple potentially violates the uniqueness constraint, *************** *** 1217,1219 **** ShutdownExprContext(ExprContext *econtext, bool isCommit) --- 1246,1492 ---- MemoryContextSwitchTo(oldcontext); } + + bool + index_check_constraint(Relation heap, Relation index, TupleTableSlot *new_slot, + ItemPointer tupleid, Datum *values, bool *isnull, + int16 *exclusion_constraint, List *index_exprs, + ExprContext *econtext, bool errorOK) + { + IndexScanDesc index_scan; + HeapTuple tup; + ScanKeyData *scankeys; + int2 index_natts = index->rd_index->indnatts; + Oid *constr_procs; + SnapshotData DirtySnapshot; + int nkeys = 0; + int i; + bool found_self = false; + bool conflict = false; + TupleTableSlot *existing_slot; + + /* + * If any of the input values are NULL, the constraint check must + * pass. + */ + for (i = 0; i < index_natts; i++) + if (isnull[i]) + return true; + + /* + * Find the function that tests for a conflict based on the + * strategy number, operator family, and types. + */ + constr_procs = palloc(sizeof(Oid) * index_natts); + for (i = 0; i < index_natts; i++) + { + /* + * Find the procedure implementing the strategy for the + * index for two arguments both with the type of the + * indexed attribute. + */ + Oid oper; + Oid opfamily = index->rd_opfamily[i]; + Oid typoid = index->rd_opcintype[i]; + StrategyNumber strategy = exclusion_constraint[i]; + + if (strategy == InvalidStrategy) + continue; + + oper = get_opfamily_member(opfamily, typoid, typoid, strategy); + + if(OidIsValid(oper)) + constr_procs[i] = get_opcode(oper); + else + elog(ERROR, "cannot determine operator for type %d and " + "strategy %d", typoid, strategy); + } + + InitDirtySnapshot(DirtySnapshot); + + /* + * Now search the tuples that are actually in the index for + * any violations. + */ + + scankeys = palloc(index_natts * sizeof(ScanKeyData)); + + for (i = 0; i < index_natts; i++) + { + Datum key_datum; + + key_datum = values[i]; + + if (exclusion_constraint[i] == InvalidStrategy) + continue; + + ScanKeyInit(&scankeys[nkeys], i + 1, exclusion_constraint[i], + constr_procs[i], key_datum); + nkeys++; + } + + /* + * We have to find all tuples, even those not visible yet. + */ + existing_slot = MakeSingleTupleTableSlot(RelationGetDescr(heap)); + index_scan = index_beginscan(heap, index, &DirtySnapshot, nkeys, + scankeys); + while((tup = index_getnext(index_scan, + ForwardScanDirection)) != NULL) + { + if(ItemPointerEquals(tupleid, &tup->t_data->t_ctid)) + { + found_self = true; + continue; + } + + ExecStoreTuple(tup, existing_slot, index_scan->xs_cbuf, false); + + if (index_scan->xs_recheck) + { + bool matches; + + matches = index_recheck_constraint( + index, existing_slot, econtext, index_exprs, values, + constr_procs); + + if (!matches) + continue; /* tuple doesn't actually match, so no conflict */ + } + + /* + * At this point we have either a conflict or a potential + * conflict. + */ + + if (errorOK) + { + conflict = true; + break; + } + + /* If the in-progress inserting transaction aborts, proceed. */ + if (TransactionIdIsValid(DirtySnapshot.xmin)) + { + XactLockTableWait(DirtySnapshot.xmin); + if (TransactionIdDidAbort(DirtySnapshot.xmin)) + continue; + } + + /* If the in-progress deleting transaction commits, proceed. */ + if (TransactionIdIsValid(DirtySnapshot.xmax)) + { + XactLockTableWait(DirtySnapshot.xmax); + if (TransactionIdDidCommit(DirtySnapshot.xmax)) + continue; + } + + ereport(ERROR, + (errcode(ERRCODE_INTEGRITY_CONSTRAINT_VIOLATION), + errmsg("operator exclusion constraint violation detected: " + "\"%s\"", RelationGetRelationName(index)), + errdetail("Tuple \"%s\" conflicts with existing tuple " + "\"%s\".", tuple_as_string(new_slot), + tuple_as_string(existing_slot)))); + } + + if (!conflict) + Assert(found_self); + + ExecDropSingleTupleTableSlot(existing_slot); + + index_endscan(index_scan); + + pfree(scankeys); + + pfree(constr_procs); + + return !conflict; + } + + static bool + index_recheck_constraint(Relation index, TupleTableSlot *slot, + ExprContext *econtext, List *index_exprs, + Datum *new_values, Oid *constr_procs) + { + int index_natts = index->rd_index->indnatts; + int2 *index_keys = index->rd_index->indkey.values; + ListCell *lc = list_head(index_exprs); + int i; + + for (i = 0; i < index_natts; i++) + { + Datum old_value; + bool isnull; + + if (index_keys[i] == 0) + { + ExprState *exprstate; + + Assert(lc != NULL); + exprstate = (ExprState *) lfirst(lc); + + old_value = ExecEvalExpr(exprstate, econtext, &isnull, NULL); + lc = lnext(lc); + } + else + { + old_value = slot_getattr(slot, index_keys[i], &isnull); + + /* + * Any null should cause the constraint to pass, so this + * recheck should immediately return false. Note: This + * isn't consistent in the case where the index search + * does match NULLs. + */ + if (isnull) + return false; + } + + if (!DatumGetBool(OidFunctionCall2(constr_procs[i], old_value, + new_values[i]))) + return false; + } + + return true; + } + + static char * + tuple_as_string(TupleTableSlot *slot) + { + TupleDesc tupdesc = slot->tts_tupleDescriptor; + StringInfoData buf; + int i; + + initStringInfo(&buf); + appendStringInfoString(&buf, "("); + + for (i = 0; i < tupdesc->natts; i++) + { + char *strval; + Datum value; + bool isnull; + + value = slot_getattr(slot, i + 1, &isnull); + + if (isnull) + strval = "null"; + else + { + Oid foutoid; + bool typisvarlena; + + getTypeOutputInfo(tupdesc->attrs[i]->atttypid, &foutoid, + &typisvarlena); + strval = DatumGetCString(OidOutputFunctionCall(foutoid, value)); + } + + if (i > 0) + appendStringInfoString(&buf, ", "); + appendStringInfoString(&buf, strval); + } + + appendStringInfoChar(&buf, ')'); + + return buf.data; + } *** a/src/backend/nodes/copyfuncs.c --- b/src/backend/nodes/copyfuncs.c *************** *** 2138,2143 **** _copyConstraint(Constraint *from) --- 2138,2146 ---- COPY_NODE_FIELD(keys); COPY_NODE_FIELD(options); COPY_STRING_FIELD(indexspace); + COPY_STRING_FIELD(using_method); + COPY_NODE_FIELD(operator_exclusion); + COPY_NODE_FIELD(where_clause); COPY_NODE_FIELD(pktable); COPY_NODE_FIELD(fk_attrs); COPY_NODE_FIELD(pk_attrs); *** a/src/backend/nodes/equalfuncs.c --- b/src/backend/nodes/equalfuncs.c *************** *** 2106,2111 **** _equalConstraint(Constraint *a, Constraint *b) --- 2106,2114 ---- COMPARE_NODE_FIELD(keys); COMPARE_NODE_FIELD(options); COMPARE_STRING_FIELD(indexspace); + COMPARE_STRING_FIELD(using_method); + COMPARE_NODE_FIELD(operator_exclusion); + COMPARE_NODE_FIELD(where_clause); COMPARE_NODE_FIELD(pktable); COMPARE_NODE_FIELD(fk_attrs); COMPARE_NODE_FIELD(pk_attrs); *** a/src/backend/nodes/outfuncs.c --- b/src/backend/nodes/outfuncs.c *************** *** 2379,2384 **** _outConstraint(StringInfo str, Constraint *node) --- 2379,2392 ---- WRITE_BOOL_FIELD(skip_validation); break; + case CONSTR_OPERATOR_EXCLUSION: + appendStringInfo(str, "OPERATOR_EXCLUSION"); + WRITE_STRING_FIELD(indexspace); + WRITE_STRING_FIELD(using_method); + WRITE_NODE_FIELD(operator_exclusion); + WRITE_NODE_FIELD(where_clause); + break; + case CONSTR_ATTR_DEFERRABLE: appendStringInfo(str, "ATTR_DEFERRABLE"); break; *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *************** *** 355,360 **** static TypeName *TableFuncTypeName(List *columns); --- 355,361 ---- %type def_arg columnElem where_clause where_or_current_clause a_expr b_expr c_expr func_expr AexprConst indirection_el columnref in_expr having_clause func_table array_expr + exclusion_where_clause %type func_arg_list %type func_arg_expr %type row type_list array_expr_list *************** *** 435,440 **** static TypeName *TableFuncTypeName(List *columns); --- 436,442 ---- %type opt_existing_window_name %type opt_frame_clause frame_extent frame_bound + %type ExclusionConstraintList ExclusionConstraintElem /* * Non-keyword token types. These are hard-wired into the "flex" lexer. *************** *** 478,484 **** static TypeName *TableFuncTypeName(List *columns); DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EXCEPT ! EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD FREEZE FROM FULL FUNCTION FUNCTIONS --- 480,486 ---- DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EXCEPT ! EXCLUDING EXCLUSION EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD FREEZE FROM FULL FUNCTION FUNCTIONS *************** *** 2506,2511 **** ConstraintElem: --- 2508,2528 ---- n->initdeferred = ($11 & 2) != 0; $$ = (Node *)n; } + | EXCLUSION access_method_clause '(' ExclusionConstraintList ')' + opt_definition OptConsTableSpace exclusion_where_clause + ConstraintAttributeSpec + { + Constraint *n = makeNode(Constraint); + n->contype = CONSTR_OPERATOR_EXCLUSION; + n->using_method = $2; + n->operator_exclusion = $4; + n->options = $6; + n->indexspace = $7; + n->where_clause = $8; + n->deferrable = ($9 & 1) != 0; + n->initdeferred = ($9 & 2) != 0; + $$ = (Node *)n; + } ; opt_column_list: *************** *** 2546,2551 **** key_match: MATCH FULL --- 2563,2585 ---- } ; + ExclusionConstraintList: + ExclusionConstraintElem { $$ = list_make1($1); } + | ExclusionConstraintList ',' ExclusionConstraintElem + { $$ = lappend($1, $3); } + ; + + ExclusionConstraintElem: index_elem CHECK WITH any_operator + { + $$ = list_make2($1, $4); + } + ; + + exclusion_where_clause: + WHERE '(' a_expr ')' { $$ = $3; } + | /*EMPTY*/ { $$ = NULL; } + ; + /* * We combine the update and delete actions into one value temporarily * for simplicity of parsing, and then break them down again in the *** a/src/backend/parser/parse_utilcmd.c --- b/src/backend/parser/parse_utilcmd.c *************** *** 70,76 **** typedef struct List *columns; /* ColumnDef items */ List *ckconstraints; /* CHECK constraints */ List *fkconstraints; /* FOREIGN KEY constraints */ ! List *ixconstraints; /* index-creating constraints */ List *inh_indexes; /* cloned indexes from INCLUDING INDEXES */ List *blist; /* "before list" of things to do before * creating the table */ --- 70,77 ---- List *columns; /* ColumnDef items */ List *ckconstraints; /* CHECK constraints */ List *fkconstraints; /* FOREIGN KEY constraints */ ! List *idxconstraints; /* index-creating constraints */ ! List *opxconstraints; /* operator exclusion constraints */ List *inh_indexes; /* cloned indexes from INCLUDING INDEXES */ List *blist; /* "before list" of things to do before * creating the table */ *************** *** 117,122 **** static void transformFKConstraints(ParseState *pstate, --- 118,127 ---- static void transformConstraintAttrs(ParseState *pstate, List *constraintList); static void transformColumnType(ParseState *pstate, ColumnDef *column); static void setSchemaName(char *context_schema, char **stmt_schema_name); + static void preprocessOpExConstraints(ParseState *pstate, + CreateStmtContext *cxt, + RangeVar *relation, + Constraint *constraint); /* *************** *** 141,146 **** transformCreateStmt(CreateStmt *stmt, const char *queryString) --- 146,153 ---- List *result; List *save_alist; ListCell *elements; + ListCell *lc; + List *opxlist = NIL; /* * We must not scribble on the passed-in CreateStmt, so copy it. (This is *************** *** 174,180 **** transformCreateStmt(CreateStmt *stmt, const char *queryString) cxt.columns = NIL; cxt.ckconstraints = NIL; cxt.fkconstraints = NIL; ! cxt.ixconstraints = NIL; cxt.inh_indexes = NIL; cxt.blist = NIL; cxt.alist = NIL; --- 181,188 ---- cxt.columns = NIL; cxt.ckconstraints = NIL; cxt.fkconstraints = NIL; ! cxt.idxconstraints = NIL; ! cxt.opxconstraints = NIL; cxt.inh_indexes = NIL; cxt.blist = NIL; cxt.alist = NIL; *************** *** 233,238 **** transformCreateStmt(CreateStmt *stmt, const char *queryString) --- 241,281 ---- transformFKConstraints(pstate, &cxt, true, false); /* + * Transform operator exclusion constraints into an + * AlterTableStmt. + */ + if (cxt.opxconstraints != NIL) + { + AlterTableStmt *alterstmt = makeNode(AlterTableStmt); + + alterstmt->relation = cxt.relation; + alterstmt->cmds = NIL; + alterstmt->relkind = OBJECT_TABLE; + + foreach (lc, cxt.opxconstraints) + { + Constraint *constraint = (Constraint *) lfirst(lc); + AlterTableCmd *altercmd = makeNode(AlterTableCmd); + + Assert(IsA(constraint, Constraint)); + Assert(constraint->contype == CONSTR_OPERATOR_EXCLUSION); + + /* + * Don't need to validate against existing rows during + * creation. + */ + constraint->skip_validation = true; + + altercmd->subtype = AT_AddConstraint; + altercmd->name = NULL; + altercmd->def = (Node *) constraint; + alterstmt->cmds = lappend(alterstmt->cmds, altercmd); + } + + opxlist = list_make1(alterstmt); + } + + /* * Output results. */ stmt->tableElts = cxt.columns; *************** *** 241,246 **** transformCreateStmt(CreateStmt *stmt, const char *queryString) --- 284,290 ---- result = lappend(cxt.blist, stmt); result = list_concat(result, cxt.alist); result = list_concat(result, save_alist); + result = list_concat(result, opxlist); return result; } *************** *** 460,466 **** transformColumnDefinition(ParseState *pstate, CreateStmtContext *cxt, case CONSTR_UNIQUE: if (constraint->keys == NIL) constraint->keys = list_make1(makeString(column->colname)); ! cxt->ixconstraints = lappend(cxt->ixconstraints, constraint); break; case CONSTR_CHECK: --- 504,510 ---- case CONSTR_UNIQUE: if (constraint->keys == NIL) constraint->keys = list_make1(makeString(column->colname)); ! cxt->idxconstraints = lappend(cxt->idxconstraints, constraint); break; case CONSTR_CHECK: *************** *** 503,509 **** transformTableConstraint(ParseState *pstate, CreateStmtContext *cxt, { case CONSTR_PRIMARY: case CONSTR_UNIQUE: ! cxt->ixconstraints = lappend(cxt->ixconstraints, constraint); break; case CONSTR_CHECK: --- 547,553 ---- { case CONSTR_PRIMARY: case CONSTR_UNIQUE: ! cxt->idxconstraints = lappend(cxt->idxconstraints, constraint); break; case CONSTR_CHECK: *************** *** 514,519 **** transformTableConstraint(ParseState *pstate, CreateStmtContext *cxt, --- 558,567 ---- cxt->fkconstraints = lappend(cxt->fkconstraints, constraint); break; + case CONSTR_OPERATOR_EXCLUSION: + cxt->opxconstraints = lappend(cxt->opxconstraints, constraint); + break; + case CONSTR_NULL: case CONSTR_NOTNULL: case CONSTR_DEFAULT: *************** *** 730,735 **** transformInhRelation(ParseState *pstate, CreateStmtContext *cxt, --- 778,789 ---- /* Build CREATE INDEX statement to recreate the parent_index */ index_stmt = generateClonedIndexStmt(cxt, parent_index, attmap); + if (index_stmt == NULL) + { + index_close(parent_index, AccessShareLock); + continue; + } + /* Copy comment on index */ if (inhRelation->options & CREATE_TABLE_LIKE_COMMENTS) { *************** *** 868,873 **** generateClonedIndexStmt(CreateStmtContext *cxt, Relation source_idx, --- 922,937 ---- elog(ERROR, "cache lookup failed for relation %u", source_relid); idxrelrec = (Form_pg_class) GETSTRUCT(ht_idxrel); + /* + * Skip indexes for operator exclusion constraints, those should + * not be copied when INCLUDING INDEXES is specified. + */ + if (idxrelrec->relopxconstraints != 0) + { + ReleaseSysCache(ht_idxrel); + return NULL; + } + /* Fetch pg_index tuple for source index from relcache entry */ ht_idx = source_idx->rd_indextuple; idxrec = (Form_pg_index) GETSTRUCT(ht_idx); *************** *** 1099,1105 **** transformIndexConstraints(ParseState *pstate, CreateStmtContext *cxt) * KEY, mark each column as NOT NULL and create an index. For UNIQUE, * create an index as for PRIMARY KEY, but do not insist on NOT NULL. */ ! foreach(lc, cxt->ixconstraints) { Constraint *constraint = (Constraint *) lfirst(lc); --- 1163,1169 ---- * KEY, mark each column as NOT NULL and create an index. For UNIQUE, * create an index as for PRIMARY KEY, but do not insist on NOT NULL. */ ! foreach(lc, cxt->idxconstraints) { Constraint *constraint = (Constraint *) lfirst(lc); *************** *** 1837,1843 **** transformAlterTableStmt(AlterTableStmt *stmt, const char *queryString) cxt.columns = NIL; cxt.ckconstraints = NIL; cxt.fkconstraints = NIL; ! cxt.ixconstraints = NIL; cxt.inh_indexes = NIL; cxt.blist = NIL; cxt.alist = NIL; --- 1901,1908 ---- cxt.columns = NIL; cxt.ckconstraints = NIL; cxt.fkconstraints = NIL; ! cxt.idxconstraints = NIL; ! cxt.opxconstraints = NIL; cxt.inh_indexes = NIL; cxt.blist = NIL; cxt.alist = NIL; *************** *** 1885,1890 **** transformAlterTableStmt(AlterTableStmt *stmt, const char *queryString) --- 1950,1958 ---- */ if (IsA(cmd->def, Constraint)) { + preprocessOpExConstraints(pstate, &cxt, stmt->relation, + (Constraint *) cmd->def); + transformTableConstraint(pstate, &cxt, (Constraint *) cmd->def); if (((Constraint *) cmd->def)->contype == CONSTR_FOREIGN) *************** *** 1943,1949 **** transformAlterTableStmt(AlterTableStmt *stmt, const char *queryString) } cxt.alist = NIL; ! /* Append any CHECK or FK constraints to the commands list */ foreach(l, cxt.ckconstraints) { newcmd = makeNode(AlterTableCmd); --- 2011,2020 ---- } cxt.alist = NIL; ! /* ! * Append any CHECK, FK or operator exclusion constraints to the ! * commands list ! */ foreach(l, cxt.ckconstraints) { newcmd = makeNode(AlterTableCmd); *************** *** 1958,1963 **** transformAlterTableStmt(AlterTableStmt *stmt, const char *queryString) --- 2029,2041 ---- newcmd->def = (Node *) lfirst(l); newcmds = lappend(newcmds, newcmd); } + foreach(l, cxt.opxconstraints) + { + newcmd = makeNode(AlterTableCmd); + newcmd->subtype = AT_AddConstraint; + newcmd->def = (Node *) lfirst(l); + newcmds = lappend(newcmds, newcmd); + } /* Close rel but keep lock */ relation_close(rel, NoLock); *************** *** 2250,2252 **** setSchemaName(char *context_schema, char **stmt_schema_name) --- 2328,2381 ---- "different from the one being created (%s)", *stmt_schema_name, context_schema))); } + + static void + preprocessOpExConstraints(ParseState *pstate, CreateStmtContext *cxt, + RangeVar *relation, Constraint *constraint) + { + ListCell *lc; + RangeTblEntry *rte; + + /* + * Put the parent table into the rtable so that the expressions can refer + * to its fields without qualification. + */ + rte = addRangeTableEntry(pstate, relation, NULL, false, true); + + addRTEtoQuery(pstate, rte, false, true, true); + + /* preprocess index expressions */ + foreach(lc, constraint->operator_exclusion) + { + List *pair = lfirst(lc); + IndexElem *ielem; + + Assert(list_length(pair) == 2); + + ielem = linitial(pair); + Assert(IsA(ielem, IndexElem)); + + if (ielem->expr) + { + ielem->expr = transformExpr(pstate, ielem->expr); + + /* + * We check only that the result type is legitimate; this + * is for consistency with what transformWhereClause() + * checks for the predicate. DefineIndex() will make more + * checks. + */ + if (expression_returns_set(ielem->expr)) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("index expression cannot return a set") + )); + } + } + + /* preprocess index predicate */ + if (constraint->where_clause) + constraint->where_clause = transformWhereClause( + pstate, constraint->where_clause, "WHERE"); + } + *** a/src/backend/storage/ipc/ipci.c --- b/src/backend/storage/ipc/ipci.c *************** *** 15,20 **** --- 15,21 ---- #include "postgres.h" #include "access/clog.h" + #include "access/genam.h" #include "access/heapam.h" #include "access/multixact.h" #include "access/nbtree.h" *** a/src/backend/tcop/utility.c --- b/src/backend/tcop/utility.c *************** *** 797,802 **** ProcessUtility(Node *parsetree, --- 797,803 ---- stmt->indexParams, /* parameters */ (Expr *) stmt->whereClause, stmt->options, + NULL, stmt->unique, stmt->primary, stmt->isconstraint, *** a/src/backend/utils/adt/ruleutils.c --- b/src/backend/utils/adt/ruleutils.c *************** *** 142,147 **** static char *pg_get_viewdef_worker(Oid viewoid, int prettyFlags); --- 142,149 ---- static char *pg_get_triggerdef_worker(Oid trigid, bool pretty); static void decompile_column_index_array(Datum column_index_array, Oid relId, StringInfo buf); + static void decompile_column_strategy_array(Datum column_strategy_array, + Oid indexOid, StringInfo buf); static char *pg_get_ruledef_worker(Oid ruleoid, int prettyFlags); static char *pg_get_indexdef_worker(Oid indexrelid, int colno, bool attrsOnly, bool showTblSpc, *************** *** 1193,1198 **** pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, --- 1195,1221 ---- break; } + case CONSTRAINT_OPX: + { + Datum val; + bool isnull; + Oid indexOid = conForm->conindid; + + /* Fetch constraint expression in parsetree form */ + val = SysCacheGetAttr(CONSTROID, tup, + Anum_pg_constraint_constrategies, + &isnull); + if (isnull) + elog(ERROR, "null conbin for constraint %u", + constraintId); + + appendStringInfo(&buf, "("); + decompile_column_strategy_array(val, indexOid, &buf); + appendStringInfo(&buf, ") USING INDEX %s", + quote_identifier(get_rel_name(indexOid))); + + break; + } default: elog(ERROR, "invalid constraint type \"%c\"", conForm->contype); break; *************** *** 1240,1245 **** decompile_column_index_array(Datum column_index_array, Oid relId, --- 1263,1316 ---- } } + /* + * Convert an int16[] Datum into a comma-separated list of column + * names and operators for the indicated index; append the list to + * buf. + */ + static void + decompile_column_strategy_array(Datum column_strategy_array, Oid indexOid, + StringInfo buf) + { + Datum *keys; + int nKeys; + int j; + Relation indexRelation; + + /* Extract data from array of int16 */ + deconstruct_array(DatumGetArrayTypeP(column_strategy_array), + INT2OID, 2, true, 's', + &keys, NULL, &nKeys); + + indexRelation = relation_open(indexOid, AccessShareLock); + + for (j = 0; j < nKeys; j++) + { + Oid opid; + char *opName; + Oid opfamily = indexRelation->rd_opfamily[j]; + char *colName = get_relid_attribute_name(indexOid, j + 1); + Oid colType = indexRelation->rd_opcintype[j]; + int16 strategy = DatumGetInt16(keys[j]); + + opid = get_opfamily_member(opfamily, colType, colType, strategy); + opName = get_opname(opid); + + if (colName == NULL || opName == NULL) + elog(ERROR, "unexpected error: cannot determine column and " + "operator names"); + + if (j == 0) + appendStringInfo(buf, "%s %s", quote_identifier(colName), + quote_identifier(opName)); + else + appendStringInfo(buf, ", %s %s", quote_identifier(colName), + quote_identifier(opName)); + } + + relation_close(indexRelation, NoLock); + } + /* ---------- * get_expr - Decompile an expression tree *** a/src/backend/utils/cache/relcache.c --- b/src/backend/utils/cache/relcache.c *************** *** 60,65 **** --- 60,66 ---- #include "storage/fd.h" #include "storage/lmgr.h" #include "storage/smgr.h" + #include "utils/array.h" #include "utils/builtins.h" #include "utils/fmgroids.h" #include "utils/inval.h" *************** *** 3038,3043 **** CheckConstraintFetch(Relation relation) --- 3039,3119 ---- } /* + * Load any operator exclusion constraints for the relation. + */ + int16 * + RelationGetOpExclusionConstraints(Relation indexRelation) + { + Relation conrel; + SysScanDesc conscan; + ScanKeyData skey[1]; + HeapTuple htup; + Datum val; + bool isnull; + bool found = false; + int16 *constraints; + Oid relid = indexRelation->rd_index->indrelid; + + ScanKeyInit(&skey[0], + Anum_pg_constraint_conrelid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(relid)); + + conrel = heap_open(ConstraintRelationId, AccessShareLock); + conscan = systable_beginscan(conrel, ConstraintRelidIndexId, true, + SnapshotNow, 1, skey); + + while (HeapTupleIsValid(htup = systable_getnext(conscan))) + { + Form_pg_constraint conform = (Form_pg_constraint) GETSTRUCT(htup); + ArrayType *arr; + int nelem; + + /* We want check constraints only */ + if (conform->contype != CONSTRAINT_OPX) + continue; + + if (conform->conindid != indexRelation->rd_id) + continue; + + if (found) + elog(ERROR, "unexpected operator exclusion constraint record " + "found for rel %s", RelationGetRelationName(indexRelation)); + + val = fastgetattr(htup, + Anum_pg_constraint_constrategies, + conrel->rd_att, &isnull); + if (isnull) + elog(ERROR, "null constrategies for rel %s", + RelationGetRelationName(indexRelation)); + + arr = DatumGetArrayTypeP(val); /* ensure not toasted */ + nelem = ARR_DIMS(arr)[0]; + if (ARR_NDIM(arr) != 1 || + nelem != indexRelation->rd_rel->relnatts || + nelem > INDEX_MAX_KEYS || + ARR_HASNULL(arr) || + ARR_ELEMTYPE(arr) != INT2OID) + elog(ERROR, "constrategies is not a 1-D smallint array"); + constraints = palloc(sizeof(int16) * nelem); + memcpy(constraints, ARR_DATA_PTR(arr), nelem * sizeof(int16)); + if ((Pointer) arr != DatumGetPointer(val)) + pfree(arr); /* free de-toasted copy, if any */ + + found = true; + } + + systable_endscan(conscan); + heap_close(conrel, AccessShareLock); + + if (!found) + elog(ERROR, "constraint record missing for rel %s", + RelationGetRelationName(indexRelation)); + + return constraints; + } + + /* * RelationGetIndexList -- get a list of OIDs of indexes on this relation * * The index list is created only if someone requests it. We scan pg_index *** a/src/bin/psql/describe.c --- b/src/bin/psql/describe.c *************** *** 1100,1105 **** describeOneTableDetails(const char *schemaname, --- 1100,1106 ---- struct { int16 checks; + int16 opxconstraints; char relkind; bool hasindex; bool hasrules; *************** *** 1121,1127 **** describeOneTableDetails(const char *schemaname, initPQExpBuffer(&tmpbuf); /* Get general table info */ ! if (pset.sversion >= 80400) { printfPQExpBuffer(&buf, "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " --- 1122,1143 ---- initPQExpBuffer(&tmpbuf); /* Get general table info */ ! if (pset.sversion >= 80500) ! { ! printfPQExpBuffer(&buf, ! "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " ! "c.relhastriggers, c.relhasoids, " ! "%s, c.reltablespace, c.relopxconstraints \n" ! "FROM pg_catalog.pg_class c\n " ! "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n" ! "WHERE c.oid = '%s'\n", ! (verbose ? ! "pg_catalog.array_to_string(c.reloptions || " ! "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n" ! : "''"), ! oid); ! } ! else if (pset.sversion >= 80400) { printfPQExpBuffer(&buf, "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " *************** *** 1189,1194 **** describeOneTableDetails(const char *schemaname, --- 1205,1212 ---- strdup(PQgetvalue(res, 0, 6)) : 0; tableinfo.tablespace = (pset.sversion >= 80000) ? atooid(PQgetvalue(res, 0, 7)) : 0; + tableinfo.opxconstraints = pset.sversion >= 80500 ? + atoi(PQgetvalue(res, 0, 8)) : 0; PQclear(res); res = NULL; *************** *** 1642,1647 **** describeOneTableDetails(const char *schemaname, --- 1660,1698 ---- PQclear(result); } + /* print operator exclusion constraints */ + if (tableinfo.opxconstraints) + { + printfPQExpBuffer(&buf, + "SELECT r.conname, " + "pg_catalog.pg_get_constraintdef(r.oid, true)\n" + "FROM pg_catalog.pg_constraint r\n" + "WHERE r.conrelid = '%s' AND r.contype = 'x'\n" + "ORDER BY 1", + oid); + result = PSQLexec(buf.data, false); + if (!result) + goto error_return; + else + tuples = PQntuples(result); + + if (tuples > 0) + { + printTableAddFooter(&cont, + _("Operator exclusion constraints:")); + for (i = 0; i < tuples; i++) + { + /* untranslated contraint name and def */ + printfPQExpBuffer(&buf, " \"%s\" %s", + PQgetvalue(result, i, 0), + PQgetvalue(result, i, 1)); + + printTableAddFooter(&cont, buf.data); + } + } + PQclear(result); + } + /* print foreign-key constraints (there are none if no triggers) */ if (tableinfo.hastriggers) { *** a/src/include/access/genam.h --- b/src/include/access/genam.h *************** *** 16,21 **** --- 16,23 ---- #include "access/sdir.h" #include "access/skey.h" + #include "access/xact.h" + #include "executor/tuptable.h" #include "nodes/tidbitmap.h" #include "storage/buf.h" #include "storage/lock.h" *** a/src/include/catalog/pg_attribute.h --- b/src/include/catalog/pg_attribute.h *************** *** 424,437 **** DATA(insert ( 1249 tableoid 26 0 0 4 -7 0 -1 -1 t p i t f f t 0 _null_)); { 1259, {"relkind"}, 18, -1, 0, 1, 15, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0, { 0 } }, \ { 1259, {"relnatts"}, 21, -1, 0, 2, 16, 0, -1, -1, true, 'p', 's', true, false, false, true, 0, { 0 } }, \ { 1259, {"relchecks"}, 21, -1, 0, 2, 17, 0, -1, -1, true, 'p', 's', true, false, false, true, 0, { 0 } }, \ ! { 1259, {"relhasoids"}, 16, -1, 0, 1, 18, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0, { 0 } }, \ ! { 1259, {"relhaspkey"}, 16, -1, 0, 1, 19, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0, { 0 } }, \ ! { 1259, {"relhasrules"}, 16, -1, 0, 1, 20, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0, { 0 } }, \ ! { 1259, {"relhastriggers"},16, -1, 0, 1, 21, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0, { 0 } }, \ ! { 1259, {"relhassubclass"},16, -1, 0, 1, 22, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0, { 0 } }, \ ! { 1259, {"relfrozenxid"}, 28, -1, 0, 4, 23, 0, -1, -1, true, 'p', 'i', true, false, false, true, 0, { 0 } }, \ ! { 1259, {"relacl"}, 1034, -1, 0, -1, 24, 1, -1, -1, false, 'x', 'i', false, false, false, true, 0, { 0 } }, \ ! { 1259, {"reloptions"}, 1009, -1, 0, -1, 25, 1, -1, -1, false, 'x', 'i', false, false, false, true, 0, { 0 } } DATA(insert ( 1259 relname 19 -1 0 NAMEDATALEN 1 0 -1 -1 f p c t f f t 0 _null_)); DATA(insert ( 1259 relnamespace 26 -1 0 4 2 0 -1 -1 t p i t f f t 0 _null_)); --- 424,438 ---- { 1259, {"relkind"}, 18, -1, 0, 1, 15, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0, { 0 } }, \ { 1259, {"relnatts"}, 21, -1, 0, 2, 16, 0, -1, -1, true, 'p', 's', true, false, false, true, 0, { 0 } }, \ { 1259, {"relchecks"}, 21, -1, 0, 2, 17, 0, -1, -1, true, 'p', 's', true, false, false, true, 0, { 0 } }, \ ! { 1259, {"relopxconstraints"}, 21, -1, 0, 2, 18, 0, -1, -1, true, 'p', 's', true, false, false, true, 0, { 0 } }, \ ! { 1259, {"relhasoids"}, 16, -1, 0, 1, 19, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0, { 0 } }, \ ! { 1259, {"relhaspkey"}, 16, -1, 0, 1, 20, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0, { 0 } }, \ ! { 1259, {"relhasrules"}, 16, -1, 0, 1, 21, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0, { 0 } }, \ ! { 1259, {"relhastriggers"},16, -1, 0, 1, 22, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0, { 0 } }, \ ! { 1259, {"relhassubclass"},16, -1, 0, 1, 23, 0, -1, -1, true, 'p', 'c', true, false, false, true, 0, { 0 } }, \ ! { 1259, {"relfrozenxid"}, 28, -1, 0, 4, 24, 0, -1, -1, true, 'p', 'i', true, false, false, true, 0, { 0 } }, \ ! { 1259, {"relacl"}, 1034, -1, 0, -1, 25, 1, -1, -1, false, 'x', 'i', false, false, false, true, 0, { 0 } }, \ ! { 1259, {"reloptions"}, 1009, -1, 0, -1, 26, 1, -1, -1, false, 'x', 'i', false, false, false, true, 0, { 0 } } DATA(insert ( 1259 relname 19 -1 0 NAMEDATALEN 1 0 -1 -1 f p c t f f t 0 _null_)); DATA(insert ( 1259 relnamespace 26 -1 0 4 2 0 -1 -1 t p i t f f t 0 _null_)); *************** *** 450,463 **** DATA(insert ( 1259 relistemp 16 -1 0 1 14 0 -1 -1 t p c t f f t 0 _null_)); DATA(insert ( 1259 relkind 18 -1 0 1 15 0 -1 -1 t p c t f f t 0 _null_)); DATA(insert ( 1259 relnatts 21 -1 0 2 16 0 -1 -1 t p s t f f t 0 _null_)); DATA(insert ( 1259 relchecks 21 -1 0 2 17 0 -1 -1 t p s t f f t 0 _null_)); ! DATA(insert ( 1259 relhasoids 16 -1 0 1 18 0 -1 -1 t p c t f f t 0 _null_)); ! DATA(insert ( 1259 relhaspkey 16 -1 0 1 19 0 -1 -1 t p c t f f t 0 _null_)); ! DATA(insert ( 1259 relhasrules 16 -1 0 1 20 0 -1 -1 t p c t f f t 0 _null_)); ! DATA(insert ( 1259 relhastriggers 16 -1 0 1 21 0 -1 -1 t p c t f f t 0 _null_)); ! DATA(insert ( 1259 relhassubclass 16 -1 0 1 22 0 -1 -1 t p c t f f t 0 _null_)); ! DATA(insert ( 1259 relfrozenxid 28 -1 0 4 23 0 -1 -1 t p i t f f t 0 _null_)); ! DATA(insert ( 1259 relacl 1034 -1 0 -1 24 1 -1 -1 f x i f f f t 0 _null_)); ! DATA(insert ( 1259 reloptions 1009 -1 0 -1 25 1 -1 -1 f x i f f f t 0 _null_)); DATA(insert ( 1259 ctid 27 0 0 6 -1 0 -1 -1 f p s t f f t 0 _null_)); DATA(insert ( 1259 oid 26 0 0 4 -2 0 -1 -1 t p i t f f t 0 _null_)); DATA(insert ( 1259 xmin 28 0 0 4 -3 0 -1 -1 t p i t f f t 0 _null_)); --- 451,465 ---- DATA(insert ( 1259 relkind 18 -1 0 1 15 0 -1 -1 t p c t f f t 0 _null_)); DATA(insert ( 1259 relnatts 21 -1 0 2 16 0 -1 -1 t p s t f f t 0 _null_)); DATA(insert ( 1259 relchecks 21 -1 0 2 17 0 -1 -1 t p s t f f t 0 _null_)); ! DATA(insert ( 1259 relopxconstraints 21 -1 0 2 18 0 -1 -1 t p s t f f t 0 _null_)); ! DATA(insert ( 1259 relhasoids 16 -1 0 1 19 0 -1 -1 t p c t f f t 0 _null_)); ! DATA(insert ( 1259 relhaspkey 16 -1 0 1 20 0 -1 -1 t p c t f f t 0 _null_)); ! DATA(insert ( 1259 relhasrules 16 -1 0 1 21 0 -1 -1 t p c t f f t 0 _null_)); ! DATA(insert ( 1259 relhastriggers 16 -1 0 1 22 0 -1 -1 t p c t f f t 0 _null_)); ! DATA(insert ( 1259 relhassubclass 16 -1 0 1 23 0 -1 -1 t p c t f f t 0 _null_)); ! DATA(insert ( 1259 relfrozenxid 28 -1 0 4 24 0 -1 -1 t p i t f f t 0 _null_)); ! DATA(insert ( 1259 relacl 1034 -1 0 -1 25 1 -1 -1 f x i f f f t 0 _null_)); ! DATA(insert ( 1259 reloptions 1009 -1 0 -1 26 1 -1 -1 f x i f f f t 0 _null_)); DATA(insert ( 1259 ctid 27 0 0 6 -1 0 -1 -1 f p s t f f t 0 _null_)); DATA(insert ( 1259 oid 26 0 0 4 -2 0 -1 -1 t p i t f f t 0 _null_)); DATA(insert ( 1259 xmin 28 0 0 4 -3 0 -1 -1 t p i t f f t 0 _null_)); *** a/src/include/catalog/pg_class.h --- b/src/include/catalog/pg_class.h *************** *** 54,59 **** CATALOG(pg_class,1259) BKI_BOOTSTRAP BKI_ROWTYPE_OID(83) --- 54,60 ---- * contain entries with negative attnums for system attributes. */ int2 relchecks; /* # of CHECK constraints for class */ + int2 relopxconstraints; /* # of opx constraints for class */ bool relhasoids; /* T if we generate OIDs for rows of rel */ bool relhaspkey; /* has (or has had) PRIMARY KEY index */ bool relhasrules; /* has (or has had) any rules */ *************** *** 87,93 **** typedef FormData_pg_class *Form_pg_class; * ---------------- */ ! #define Natts_pg_class 25 #define Anum_pg_class_relname 1 #define Anum_pg_class_relnamespace 2 #define Anum_pg_class_reltype 3 --- 88,94 ---- * ---------------- */ ! #define Natts_pg_class 26 #define Anum_pg_class_relname 1 #define Anum_pg_class_relnamespace 2 #define Anum_pg_class_reltype 3 *************** *** 105,118 **** typedef FormData_pg_class *Form_pg_class; #define Anum_pg_class_relkind 15 #define Anum_pg_class_relnatts 16 #define Anum_pg_class_relchecks 17 ! #define Anum_pg_class_relhasoids 18 ! #define Anum_pg_class_relhaspkey 19 ! #define Anum_pg_class_relhasrules 20 ! #define Anum_pg_class_relhastriggers 21 ! #define Anum_pg_class_relhassubclass 22 ! #define Anum_pg_class_relfrozenxid 23 ! #define Anum_pg_class_relacl 24 ! #define Anum_pg_class_reloptions 25 /* ---------------- * initial contents of pg_class --- 106,120 ---- #define Anum_pg_class_relkind 15 #define Anum_pg_class_relnatts 16 #define Anum_pg_class_relchecks 17 ! #define Anum_pg_class_relopxconstraints 18 ! #define Anum_pg_class_relhasoids 19 ! #define Anum_pg_class_relhaspkey 20 ! #define Anum_pg_class_relhasrules 21 ! #define Anum_pg_class_relhastriggers 22 ! #define Anum_pg_class_relhassubclass 23 ! #define Anum_pg_class_relfrozenxid 24 ! #define Anum_pg_class_relacl 25 ! #define Anum_pg_class_reloptions 26 /* ---------------- * initial contents of pg_class *************** *** 124,136 **** typedef FormData_pg_class *Form_pg_class; */ /* Note: "3" in the relfrozenxid column stands for FirstNormalTransactionId */ ! DATA(insert OID = 1247 ( pg_type PGNSP 71 PGUID 0 1247 0 0 0 0 0 f f f r 28 0 t f f f f 3 _null_ _null_ )); DESCR(""); ! DATA(insert OID = 1249 ( pg_attribute PGNSP 75 PGUID 0 1249 0 0 0 0 0 f f f r 19 0 f f f f f 3 _null_ _null_ )); DESCR(""); ! DATA(insert OID = 1255 ( pg_proc PGNSP 81 PGUID 0 1255 0 0 0 0 0 f f f r 25 0 t f f f f 3 _null_ _null_ )); DESCR(""); ! DATA(insert OID = 1259 ( pg_class PGNSP 83 PGUID 0 1259 0 0 0 0 0 f f f r 25 0 t f f f f 3 _null_ _null_ )); DESCR(""); #define RELKIND_INDEX 'i' /* secondary index */ --- 126,138 ---- */ /* Note: "3" in the relfrozenxid column stands for FirstNormalTransactionId */ ! DATA(insert OID = 1247 ( pg_type PGNSP 71 PGUID 0 1247 0 0 0 0 0 f f f r 28 0 0 t f f f f 3 _null_ _null_ )); DESCR(""); ! DATA(insert OID = 1249 ( pg_attribute PGNSP 75 PGUID 0 1249 0 0 0 0 0 f f f r 19 0 0 f f f f f 3 _null_ _null_ )); DESCR(""); ! DATA(insert OID = 1255 ( pg_proc PGNSP 81 PGUID 0 1255 0 0 0 0 0 f f f r 25 0 0 t f f f f 3 _null_ _null_ )); DESCR(""); ! DATA(insert OID = 1259 ( pg_class PGNSP 83 PGUID 0 1259 0 0 0 0 0 f f f r 26 0 0 t f f f f 3 _null_ _null_ )); DESCR(""); #define RELKIND_INDEX 'i' /* secondary index */ *** a/src/include/catalog/pg_constraint.h --- b/src/include/catalog/pg_constraint.h *************** *** 120,125 **** CATALOG(pg_constraint,2606) --- 120,133 ---- Oid conffeqop[1]; /* + * If constraint is an operator exclusion constraint, these are + * the strategy numbers used for constraint. The size of the array + * is equal to the number of attributes in the index referenced by + * conindid. + */ + int2 constrategies[1]; + + /* * If a check constraint, nodeToString representation of expression */ text conbin; *************** *** 141,147 **** typedef FormData_pg_constraint *Form_pg_constraint; * compiler constants for pg_constraint * ---------------- */ ! #define Natts_pg_constraint 21 #define Anum_pg_constraint_conname 1 #define Anum_pg_constraint_connamespace 2 #define Anum_pg_constraint_contype 3 --- 149,155 ---- * compiler constants for pg_constraint * ---------------- */ ! #define Natts_pg_constraint 22 #define Anum_pg_constraint_conname 1 #define Anum_pg_constraint_connamespace 2 #define Anum_pg_constraint_contype 3 *************** *** 161,168 **** typedef FormData_pg_constraint *Form_pg_constraint; #define Anum_pg_constraint_conpfeqop 17 #define Anum_pg_constraint_conppeqop 18 #define Anum_pg_constraint_conffeqop 19 ! #define Anum_pg_constraint_conbin 20 ! #define Anum_pg_constraint_consrc 21 /* Valid values for contype */ --- 169,177 ---- #define Anum_pg_constraint_conpfeqop 17 #define Anum_pg_constraint_conppeqop 18 #define Anum_pg_constraint_conffeqop 19 ! #define Anum_pg_constraint_constrategies 20 ! #define Anum_pg_constraint_conbin 21 ! #define Anum_pg_constraint_consrc 22 /* Valid values for contype */ *************** *** 170,175 **** typedef FormData_pg_constraint *Form_pg_constraint; --- 179,185 ---- #define CONSTRAINT_FOREIGN 'f' #define CONSTRAINT_PRIMARY 'p' #define CONSTRAINT_UNIQUE 'u' + #define CONSTRAINT_OPX 'x' /* * Valid values for confupdtype and confdeltype are the FKCONSTR_ACTION_xxx *************** *** 209,214 **** extern Oid CreateConstraintEntry(const char *constraintName, --- 219,225 ---- char foreignUpdateType, char foreignDeleteType, char foreignMatchType, + const int16 *exclusion_constraint, Node *conExpr, const char *conBin, const char *conSrc, *** a/src/include/commands/defrem.h --- b/src/include/commands/defrem.h *************** *** 18,24 **** /* commands/indexcmds.c */ ! extern void DefineIndex(RangeVar *heapRelation, char *indexRelationName, Oid indexRelationId, char *accessMethodName, --- 18,24 ---- /* commands/indexcmds.c */ ! extern Oid DefineIndex(RangeVar *heapRelation, char *indexRelationName, Oid indexRelationId, char *accessMethodName, *************** *** 26,31 **** extern void DefineIndex(RangeVar *heapRelation, --- 26,32 ---- List *attributeList, Expr *predicate, List *options, + int16 *exclusion_constraint, bool unique, bool primary, bool isconstraint, *************** *** 45,50 **** extern char *makeObjectName(const char *name1, const char *name2, --- 46,53 ---- extern char *ChooseRelationName(const char *name1, const char *name2, const char *label, Oid namespaceid); extern Oid GetDefaultOpClass(Oid type_id, Oid am_id); + extern Oid GetIndexOpClass(List *opclass, Oid attrType, + char *accessMethodName, Oid accessMethodId); /* commands/functioncmds.c */ extern void CreateFunction(CreateFunctionStmt *stmt, const char *queryString); *** a/src/include/executor/executor.h --- b/src/include/executor/executor.h *************** *** 318,322 **** extern void RegisterExprContextCallback(ExprContext *econtext, --- 318,328 ---- extern void UnregisterExprContextCallback(ExprContext *econtext, ExprContextCallbackFunction function, Datum arg); + extern bool index_check_constraint(Relation heap, Relation index, + TupleTableSlot *new_slot, + ItemPointer tupleid, Datum *values, + bool *isnull, int16 *exclusion_constraint, + List *index_exprs, ExprContext *econtext, + bool errorOK); #endif /* EXECUTOR_H */ *** a/src/include/nodes/execnodes.h --- b/src/include/nodes/execnodes.h *************** *** 58,63 **** typedef struct IndexInfo --- 58,64 ---- List *ii_ExpressionsState; /* list of ExprState */ List *ii_Predicate; /* list of Expr */ List *ii_PredicateState; /* list of ExprState */ + int16 *ii_ExclusionConstraint; bool ii_Unique; bool ii_ReadyForInserts; bool ii_Concurrent; *** a/src/include/nodes/parsenodes.h --- b/src/include/nodes/parsenodes.h *************** *** 1399,1404 **** typedef enum ConstrType /* types of constraints */ --- 1399,1405 ---- CONSTR_CHECK, CONSTR_PRIMARY, CONSTR_UNIQUE, + CONSTR_OPERATOR_EXCLUSION, CONSTR_FOREIGN, CONSTR_ATTR_DEFERRABLE, /* attributes for previous constraint node */ CONSTR_ATTR_NOT_DEFERRABLE, *************** *** 1433,1443 **** typedef struct Constraint Node *raw_expr; /* expr, as untransformed parse tree */ char *cooked_expr; /* expr, as nodeToString representation */ ! /* Fields used for index constraints (UNIQUE and PRIMARY KEY): */ List *keys; /* String nodes naming referenced column(s) */ List *options; /* options from WITH clause */ char *indexspace; /* index tablespace; NULL for default */ /* Fields used for FOREIGN KEY constraints: */ RangeVar *pktable; /* Primary key table */ List *fk_attrs; /* Attributes of foreign key */ --- 1434,1449 ---- Node *raw_expr; /* expr, as untransformed parse tree */ char *cooked_expr; /* expr, as nodeToString representation */ ! /* Fields used for unique constraints (UNIQUE and PRIMARY KEY): */ List *keys; /* String nodes naming referenced column(s) */ List *options; /* options from WITH clause */ char *indexspace; /* index tablespace; NULL for default */ + /* Fields used for index constraints: */ + List *operator_exclusion; /* list of (colname, operator) pairs */ + char *using_method; /* access method for this constraint */ + Node *where_clause; /* predicate for exclusion constraint */ + /* Fields used for FOREIGN KEY constraints: */ RangeVar *pktable; /* Primary key table */ List *fk_attrs; /* Attributes of foreign key */ *** a/src/include/parser/kwlist.h --- b/src/include/parser/kwlist.h *************** *** 144,149 **** PG_KEYWORD("enum", ENUM_P, UNRESERVED_KEYWORD) --- 144,150 ---- PG_KEYWORD("escape", ESCAPE, UNRESERVED_KEYWORD) PG_KEYWORD("except", EXCEPT, RESERVED_KEYWORD) PG_KEYWORD("excluding", EXCLUDING, UNRESERVED_KEYWORD) + PG_KEYWORD("exclusion", EXCLUSION, UNRESERVED_KEYWORD) PG_KEYWORD("exclusive", EXCLUSIVE, UNRESERVED_KEYWORD) PG_KEYWORD("execute", EXECUTE, UNRESERVED_KEYWORD) PG_KEYWORD("exists", EXISTS, COL_NAME_KEYWORD) *** a/src/include/storage/lwlock.h --- b/src/include/storage/lwlock.h *************** *** 67,72 **** typedef enum LWLockId --- 67,73 ---- AutovacuumLock, AutovacuumScheduleLock, SyncScanLock, + IndexConstraintLock, /* Individual lock IDs end here */ FirstBufMappingLock, FirstLockMgrLock = FirstBufMappingLock + NUM_BUFFER_PARTITIONS, *** a/src/include/utils/relcache.h --- b/src/include/utils/relcache.h *************** *** 15,20 **** --- 15,21 ---- #define RELCACHE_H #include "access/tupdesc.h" + #include "access/skey.h" #include "nodes/bitmapset.h" #include "nodes/pg_list.h" *************** *** 43,48 **** extern Oid RelationGetOidIndex(Relation relation); --- 44,50 ---- extern List *RelationGetIndexExpressions(Relation relation); extern List *RelationGetIndexPredicate(Relation relation); extern Bitmapset *RelationGetIndexAttrBitmap(Relation relation); + extern int16 *RelationGetOpExclusionConstraints(Relation indexRelation); extern void RelationSetIndexList(Relation relation, List *indexIds, Oid oidIndex); *** a/src/test/regress/input/constraints.source --- b/src/test/regress/input/constraints.source *************** *** 366,368 **** COMMIT; --- 366,397 ---- SELECT * FROM unique_tbl; DROP TABLE unique_tbl; + + CREATE TABLE circles ( + c1 CIRCLE, + c2 TEXT, + EXCLUSION USING gist + (c1 CHECK WITH &&, (c2::circle) CHECK WITH ~=) + WHERE (circle_center(c1) <> '(0,0)') + ); + + -- these should succeed because they don't match the index predicate + INSERT INTO circles VALUES('<(0,0), 5>', '<(0,0), 5>'); + INSERT INTO circles VALUES('<(0,0), 5>', '<(0,0), 5>'); + + -- succeed + INSERT INTO circles VALUES('<(10,10), 10>', '<(0,0), 5>'); + -- should fail + INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 5>'); + -- succeed because c1 doesn't overlap + INSERT INTO circles VALUES('<(20,20), 1>', '<(0,0), 5>'); + -- succeed because c2 is not the same + INSERT INTO circles VALUES('<(20,20), 10>', '<(1,1), 5>'); + + -- should fail on existing data without the WHERE clause + ALTER TABLE circles ADD EXCLUSION USING gist + (c1 CHECK WITH &&, (c2::circle) CHECK WITH ~=); + + DROP TABLE circles; + + *** a/src/test/regress/output/constraints.source --- b/src/test/regress/output/constraints.source *************** *** 267,273 **** SELECT * FROM INSERT_TBL; CREATE TABLE COPY_TBL (x INT, y TEXT, z INT, CONSTRAINT COPY_CON CHECK (x > 3 AND y <> 'check failed' AND x < 7 )); ! COPY COPY_TBL FROM '@abs_srcdir@/data/constro.data'; SELECT '' AS two, * FROM COPY_TBL; two | x | y | z -----+---+---------------+--- --- 267,273 ---- CREATE TABLE COPY_TBL (x INT, y TEXT, z INT, CONSTRAINT COPY_CON CHECK (x > 3 AND y <> 'check failed' AND x < 7 )); ! COPY COPY_TBL FROM '/home/jdavis/wd/git/postgresql/src/test/regress/data/constro.data'; SELECT '' AS two, * FROM COPY_TBL; two | x | y | z -----+---+---------------+--- *************** *** 275,281 **** SELECT '' AS two, * FROM COPY_TBL; | 6 | OK | 4 (2 rows) ! COPY COPY_TBL FROM '@abs_srcdir@/data/constrf.data'; ERROR: new row for relation "copy_tbl" violates check constraint "copy_con" CONTEXT: COPY copy_tbl, line 2: "7 check failed 6" SELECT * FROM COPY_TBL; --- 275,281 ---- | 6 | OK | 4 (2 rows) ! COPY COPY_TBL FROM '/home/jdavis/wd/git/postgresql/src/test/regress/data/constrf.data'; ERROR: new row for relation "copy_tbl" violates check constraint "copy_con" CONTEXT: COPY copy_tbl, line 2: "7 check failed 6" SELECT * FROM COPY_TBL; *************** *** 512,514 **** SELECT * FROM unique_tbl; --- 512,542 ---- (5 rows) DROP TABLE unique_tbl; + CREATE TABLE circles ( + c1 CIRCLE, + c2 TEXT, + EXCLUSION USING gist + (c1 CHECK WITH &&, (c2::circle) CHECK WITH ~=) + WHERE (circle_center(c1) <> '(0,0)') + ); + NOTICE: ALTER TABLE / ADD EXCLUSION will create implicit index "circles_c1_exclusion" for table "circles" + -- these should succeed because they don't match the index predicate + INSERT INTO circles VALUES('<(0,0), 5>', '<(0,0), 5>'); + INSERT INTO circles VALUES('<(0,0), 5>', '<(0,0), 5>'); + -- succeed + INSERT INTO circles VALUES('<(10,10), 10>', '<(0,0), 5>'); + -- should fail + INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 5>'); + ERROR: operator exclusion constraint violation detected: "circles_c1_exclusion" + DETAIL: Tuple "(<(20,20),10>, <(0,0), 5>)" conflicts with existing tuple "(<(10,10),10>, <(0,0), 5>)". + -- succeed because c1 doesn't overlap + INSERT INTO circles VALUES('<(20,20), 1>', '<(0,0), 5>'); + -- succeed because c2 is not the same + INSERT INTO circles VALUES('<(20,20), 10>', '<(1,1), 5>'); + -- should fail on existing data without the WHERE clause + ALTER TABLE circles ADD EXCLUSION USING gist + (c1 CHECK WITH &&, (c2::circle) CHECK WITH ~=); + NOTICE: ALTER TABLE / ADD EXCLUSION will create implicit index "circles_c1_exclusion1" for table "circles" + ERROR: operator exclusion constraint violation detected: "circles_c1_exclusion1" + DETAIL: Tuple "(<(0,0),5>, <(0,0), 5>)" conflicts with existing tuple "(<(0,0),5>, <(0,0), 5>)". + DROP TABLE circles;