operator-exclusion-constraints-20091126.context.patch
text/x-patch
Filename: operator-exclusion-constraints-20091126.context.patch
Type: text/x-patch
Part: 0
Message:
Re: operator exclusion constraints
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: context
| File | + | − |
|---|---|---|
| doc/src/sgml/ref/create_table.sgml | 59 | 0 |
| src/backend/access/index/genam.c | 5 | 0 |
| src/backend/access/index/indexam.c | 1 | 0 |
| src/backend/bootstrap/bootparse.y | 2 | 0 |
| src/backend/bootstrap/bootstrap.c | 3 | 0 |
| src/backend/catalog/heap.c | 2 | 0 |
| src/backend/catalog/index.c | 81 | 0 |
| src/backend/catalog/information_schema.sql | 1 | 0 |
| src/backend/catalog/pg_constraint.c | 40 | 0 |
| src/backend/catalog/toasting.c | 3 | 0 |
| src/backend/commands/constraint.c | 14 | 0 |
| src/backend/commands/indexcmds.c | 32 | 0 |
| src/backend/commands/tablecmds.c | 293 | 0 |
| src/backend/commands/typecmds.c | 1 | 0 |
| src/backend/executor/execUtils.c | 298 | 1 |
| src/backend/nodes/copyfuncs.c | 3 | 0 |
| src/backend/nodes/equalfuncs.c | 3 | 0 |
| src/backend/nodes/outfuncs.c | 8 | 0 |
| src/backend/parser/gram.y | 46 | 0 |
| src/backend/parser/parse_utilcmd.c | 130 | 0 |
| src/backend/tcop/utility.c | 1 | 0 |
| src/backend/utils/adt/ruleutils.c | 265 | 0 |
| src/backend/utils/cache/relcache.c | 76 | 0 |
| src/bin/pg_dump/pg_backup_archiver.c | 1 | 0 |
| src/bin/pg_dump/pg_dump.c | 61 | 0 |
| src/bin/psql/describe.c | 52 | 0 |
| src/include/catalog/pg_attribute.h | 18 | 0 |
| src/include/catalog/pg_class.h | 15 | 0 |
| src/include/catalog/pg_constraint.h | 14 | 0 |
| src/include/commands/defrem.h | 4 | 1 |
| src/include/executor/executor.h | 5 | 0 |
| src/include/nodes/execnodes.h | 1 | 0 |
| src/include/nodes/parsenodes.h | 7 | 0 |
| src/include/parser/kwlist.h | 1 | 0 |
| src/include/utils/errcodes.h | 1 | 0 |
| src/include/utils/relcache.h | 1 | 0 |
| src/pl/plpgsql/src/plerrcodes.h | 4 | 0 |
| src/test/regress/input/constraints.source | 29 | 0 |
| src/test/regress/output/constraints.source | 28 | 0 |
*** a/doc/src/sgml/ref/create_table.sgml
--- b/doc/src/sgml/ref/create_table.sgml
***************
*** 51,63 **** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PAR
PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> |
CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) |
FOREIGN KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> [, ... ] ) ]
! [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
<phrase><replaceable class="PARAMETER">index_parameters</replaceable> in <literal>UNIQUE</literal> and <literal>PRIMARY KEY</literal> constraints are:</phrase>
[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) ]
[ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ]
</synopsis>
</refsynopsisdiv>
--- 51,69 ----
PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) <replaceable class="PARAMETER">index_parameters</replaceable> |
CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) |
FOREIGN KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> [, ... ] ) ]
! [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ] |
! EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
<phrase><replaceable class="PARAMETER">index_parameters</replaceable> in <literal>UNIQUE</literal> and <literal>PRIMARY KEY</literal> constraints are:</phrase>
[ WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] ) ]
[ USING INDEX TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable> ]
+
+ <phrase>and <replaceable class="PARAMETER">exclude_element</replaceable> in an <literal>EXCLUDE</literal> constraint is:</phrase>
+
+ { column | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
+
</synopsis>
</refsynopsisdiv>
***************
*** 547,552 **** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PAR
--- 553,598 ----
</varlistentry>
<varlistentry>
+ <term><literal>EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ]</literal></term>
+ <listitem>
+ <para>
+ The <literal>EXCLUDE</> clause specifies an operator exclusion
+ constraint. An operator exclusion constraint guarantees that if
+ any two tuples are compared on the specified columns or
+ expressions using the specified operators, at least one such
+ comparison will return <literal>FALSE</>. If all of the
+ specified operators test for equality, it is equivalent to a
+ UNIQUE constraint, although an ordinary unique constraint will
+ normally be faster. However, operator exclusion constraints can
+ use index methods other than btree (such as <literal>GiST</>
+ (see <xref linkend="GiST">), and can specify more general
+ constraints. For instance, you can specify the constraint that
+ no two tuples in the table contain overlapping circles
+ (see <xref linkend="datatype-geometric">) by using the
+ <literal>&&</> operator.
+ </para>
+
+ <para>
+ Operator exclusion constraints are implemented internally using
+ an index, so the specified operators must be associated with an
+ appropriate operator class
+ (see <xref linkend="SQL-CREATEOPCLASS">) for access
+ method <replaceable>index_method</>, and the access method must
+ support <literal>amgettuple</> (see <xref linkend="indexam"> for details).
+ The operators are also required to be their own commutators
+ (see <xref linkend="sql-createoperator">).
+ </para>
+
+ <para>
+ The <replaceable class="parameter">predicate</> allows you to
+ specify a constraint on a subset of the table, internally using
+ a partial index. Note the require perentheses around the
+ predicate.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>DEFERRABLE</literal></term>
<term><literal>NOT DEFERRABLE</literal></term>
<listitem>
***************
*** 1111,1116 **** CREATE TABLE cinemas (
--- 1157,1174 ----
</programlisting>
</para>
+ <para>
+ Create table <structname>circles</> with an operator exclusion
+ constraint that prevents overlapping circles within it:
+
+ <programlisting>
+ CREATE TABLE circles (
+ c circle,
+ EXCLUDE USING gist (c WITH &&)
+ );
+ </programlisting>
+ </para>
+
</refsect1>
<refsect1 id="SQL-CREATETABLE-compatibility">
*** a/src/backend/access/index/genam.c
--- b/src/backend/access/index/genam.c
***************
*** 144,155 **** char *
BuildIndexValueDescription(Relation indexRelation,
Datum *values, bool *isnull)
{
- /*
- * XXX for the moment we use the index's tupdesc as a guide to the
- * datatypes of the values. This is okay for btree indexes but is in
- * fact the wrong thing in general. This will have to be fixed if we
- * are ever to support non-btree unique indexes.
- */
TupleDesc tupdesc = RelationGetDescr(indexRelation);
StringInfoData buf;
int i;
--- 144,149 ----
***************
*** 170,176 **** BuildIndexValueDescription(Relation indexRelation,
Oid foutoid;
bool typisvarlena;
! getTypeOutputInfo(tupdesc->attrs[i]->atttypid,
&foutoid, &typisvarlena);
val = OidOutputFunctionCall(foutoid, values[i]);
}
--- 164,174 ----
Oid foutoid;
bool typisvarlena;
! /*
! * Don't use the tupdesc for the type information, because
! * that might be different for non-btree opclasses.
! */
! getTypeOutputInfo(indexRelation->rd_opcintype[i],
&foutoid, &typisvarlena);
val = OidOutputFunctionCall(foutoid, values[i]);
}
*** 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
*** 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 EXCLUDE");
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,
***************
*** 804,809 **** index_create(Oid heapRelationId,
--- 812,875 ----
"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
{
***************
*** 1083,1088 **** BuildIndexInfo(Relation index)
--- 1149,1158 ----
/* 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;
***************
*** 1894,1899 **** IndexBuildHeapScan(Relation heapRelation,
--- 1964,1972 ----
indexInfo->ii_ExpressionsState = NIL;
indexInfo->ii_PredicateState = NIL;
+ /* operator exclusion constraints aren't checked at index build time */
+ indexInfo->ii_ExclusionConstraint = NULL;
+
return reltuples;
}
***************
*** 2268,2273 **** validate_index_heapscan(Relation heapRelation,
--- 2341,2352 ----
/* These may have been pointing to the now-gone estate */
indexInfo->ii_ExpressionsState = NIL;
indexInfo->ii_PredicateState = NIL;
+
+ /*
+ * Operator exclusion constraints aren't supported for concurrent
+ * index builds.
+ */
+ indexInfo->ii_ExclusionConstraint = NULL;
}
*** 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
***************
*** 40,46 **** unique_key_recheck(PG_FUNCTION_ARGS)
Relation indexRel;
IndexInfo *indexInfo;
EState *estate;
! ExprContext *econtext;
TupleTableSlot *slot;
Datum values[INDEX_MAX_KEYS];
bool isnull[INDEX_MAX_KEYS];
--- 40,46 ----
Relation indexRel;
IndexInfo *indexInfo;
EState *estate;
! ExprContext *econtext = NULL;
TupleTableSlot *slot;
Datum values[INDEX_MAX_KEYS];
bool isnull[INDEX_MAX_KEYS];
***************
*** 125,131 **** unique_key_recheck(PG_FUNCTION_ARGS)
* 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);
--- 125,132 ----
* Typically the index won't have expressions, but if it does we need
* an EState to evaluate them.
*/
! if (indexInfo->ii_Expressions != NIL ||
! indexInfo->ii_ExclusionConstraint != NULL)
{
estate = CreateExecutorState();
econtext = GetPerTupleExprContext(estate);
***************
*** 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.
--- 150,166 ----
* 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, estate, 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);
***************
*** 423,428 **** DefineIndex(RangeVar *heapRelation,
--- 433,439 ----
indexInfo->ii_ReadyForInserts = !concurrent;
indexInfo->ii_Concurrent = concurrent;
indexInfo->ii_BrokenHotChain = false;
+ indexInfo->ii_ExclusionConstraint = exclusion_constraint;
classObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid));
coloptions = (int16 *) palloc(numberOfAttributes * sizeof(int16));
***************
*** 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;
--- 446,469 ----
* error checks)
*/
if (isconstraint && !quiet)
+ {
+ char *constraint_type = NULL;
+
+ if (primary)
+ constraint_type = "PRIMARY KEY";
+ else if (unique)
+ constraint_type = "UNIQUE";
+ else if (exclusion_constraint != NULL)
+ constraint_type = "EXCLUDE";
+ 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 */
}
/*
--- 479,485 ----
isconstraint, deferrable, initdeferred,
allowSystemTableMods, skip_build, concurrent);
! return indexRelationId; /* We're done, in the standard case */
}
/*
***************
*** 750,755 **** DefineIndex(RangeVar *heapRelation,
--- 774,781 ----
* 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)
{
--- 965,971 ----
/*
* 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 EXCLUDE */
! 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;
CommandId mycid;
BulkInsertState bistate;
int hi_options;
***************
*** 3103,3108 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
--- 3109,3117 ----
switch (con->contype)
{
+ Relation indexRelation = NULL;
+ IndexInfo *indexInfo = NULL;
+
case CONSTR_CHECK:
needscan = true;
con->qualstate = (List *)
***************
*** 3111,3116 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
--- 3120,3153 ----
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);
***************
*** 3155,3160 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
--- 3192,3199 ----
MemoryContext oldCxt;
List *dropped_attrs = NIL;
ListCell *lc;
+ Datum *idxvals = NULL;
+ bool *idxnulls = NULL;
econtext = GetPerTupleExprContext(estate);
***************
*** 3173,3178 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
--- 3212,3226 ----
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
***************
*** 3198,3203 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
--- 3246,3252 ----
while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
{
+
if (newrel)
{
Oid tupOid = InvalidOid;
***************
*** 3268,3273 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
--- 3317,3323 ----
con->name)));
break;
case CONSTR_FOREIGN:
+ case CONSTR_OPERATOR_EXCLUSION:
/* Nothing to do here */
break;
default:
***************
*** 3276,3281 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
--- 3326,3349 ----
}
}
+ 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, estate, false);
+ }
+
/* Write the tuple out to the new relation */
if (newrel)
heap_insert(newrel, tuple, mycid, hi_options, bistate);
***************
*** 3290,3295 **** ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
--- 3358,3376 ----
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);
***************
*** 4603,4608 **** ATExecAddIndex(AlteredTableInfo *tab, Relation rel,
--- 4684,4690 ----
stmt->indexParams, /* parameters */
(Expr *) stmt->whereClause,
stmt->options,
+ NULL,
stmt->unique,
stmt->primary,
stmt->isconstraint,
***************
*** 4666,4671 **** ATExecAddConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
--- 4748,4773 ----
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))));
+ }
+
+ ATAddOperatorExclusionConstraint(tab, rel, newConstraint);
+ break;
+
default:
elog(ERROR, "unrecognized constraint type: %d",
(int) newConstraint->contype);
***************
*** 5035,5040 **** ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
--- 5137,5143 ----
fkconstraint->fk_upd_action,
fkconstraint->fk_del_action,
fkconstraint->fk_matchtype,
+ NULL,
NULL, /* no check constraint */
NULL,
NULL,
***************
*** 5071,5076 **** ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
--- 5174,5368 ----
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 operator "
+ "exclusion constraints",
+ constraint->using_method),
+ errdetail("The index access method must support the "
+ "gettuple() interface to be used with an "
+ "operator exclusion constraint.")));
+
+ 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 = compatible_oper_opid(opname, typoid, typoid, false);
+
+ 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", format_operator(opid)),
+ errdetail("Set the operator's COMMUTATOR to be itself, "
+ "or choose a different operator.")
+ ));
+ }
+
+ if (!op_in_opfamily(opid, opfamily))
+ {
+ char *opclass_name;
+ HeapTuple tuple;
+ Form_pg_opclass opctup;
+
+ tuple = SearchSysCache(CLAOID,
+ ObjectIdGetDatum(opclassid),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("operator class with OID %u does not exist",
+ opclassid)));
+
+ opctup = (Form_pg_opclass) GETSTRUCT(tuple);
+ opclass_name = pstrdup(opctup->opcname.data);
+ ReleaseSysCache(tuple);
+
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("no streategy found for operator %s in operator "
+ "class %s", format_operator(opid),
+ quote_identifier(opclass_name)),
+ errdetail("The operator class must provide a strategy "
+ "number for the given operator.")
+ ));
+ }
+
+ exclusion_constraint[i] = get_op_opfamily_strategy(opid, opfamily);
+
+ Assert(exclusion_constraint[i] != InvalidStrategy);
+
+ i++;
+ }
+
+ rv = makeRangeVar(get_namespace_name(RelationGetNamespace(rel)),
+ pstrdup(RelationGetRelationName(rel)),
+ -1);
+ /*
+ * Build index to enforce the constraint.
+ */
+ index_oid = DefineIndex(rv, /* relation range var */
+ constraint->conname, /* 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,
! EState *estate, List *index_exprs,
! Datum *new_values, Oid *constr_procs);
! static char * tuple_as_string(TupleTableSlot *slot);
/* ----------------------------------------------------------------
* Executor state and memory management functions
***************
*** 1011,1017 **** ExecInsertIndexTuples(TupleTableSlot *slot,
Relation indexRelation = relationDescs[i];
IndexInfo *indexInfo;
IndexUniqueCheck checkUnique;
! bool isUnique;
if (indexRelation == NULL)
continue;
--- 1018,1024 ----
Relation indexRelation = relationDescs[i];
IndexInfo *indexInfo;
IndexUniqueCheck checkUnique;
! bool satisfiesConstraint;
if (indexRelation == NULL)
continue;
***************
*** 1076,1082 **** ExecInsertIndexTuples(TupleTableSlot *slot,
else
checkUnique = UNIQUE_CHECK_PARTIAL;
! isUnique =
index_insert(indexRelation, /* index relation */
values, /* array of index Datums */
isnull, /* null flags */
--- 1083,1089 ----
else
checkUnique = UNIQUE_CHECK_PARTIAL;
! satisfiesConstraint =
index_insert(indexRelation, /* index relation */
values, /* array of index Datums */
isnull, /* null flags */
***************
*** 1084,1090 **** 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,
--- 1091,1117 ----
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, estate, errorOK);
! }
!
! if ((checkUnique == UNIQUE_CHECK_PARTIAL ||
! indexInfo->ii_ExclusionConstraint != NULL) &&
! !satisfiesConstraint)
{
/*
* The tuple potentially violates the uniqueness constraint,
***************
*** 1218,1220 **** ShutdownExprContext(ExprContext *econtext, bool isCommit)
--- 1245,1514 ----
MemoryContextSwitchTo(oldcontext);
}
+
+ bool
+ index_check_constraint(Relation heap, Relation index, TupleTableSlot *new_slot,
+ ItemPointer tupleid, Datum *values, bool *isnull,
+ IndexInfo *indexInfo, EState *estate, 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;
+ bool conflict = false;
+ TupleTableSlot *existing_slot;
+ int16 *exclusion_constraint = indexInfo->ii_ExclusionConstraint;
+ List *index_exprs = indexInfo->ii_ExpressionsState;
+
+ /*
+ * 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);
+ }
+
+ /*
+ * 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++;
+ }
+
+ existing_slot = MakeSingleTupleTableSlot(RelationGetDescr(heap));
+
+ /*
+ * We have to find all tuples, even those not visible yet.
+ */
+ InitDirtySnapshot(DirtySnapshot);
+
+ retry:
+ found_self = false;
+ index_scan = index_beginscan(heap, index, &DirtySnapshot, nkeys,
+ scankeys);
+ while((tup = index_getnext(index_scan,
+ ForwardScanDirection)) != NULL)
+ {
+ TransactionId xwait;
+ char *error_new = NULL;
+ char *error_existing = NULL;
+
+ if(ItemPointerEquals(tupleid, &tup->t_self))
+ {
+ Assert(!found_self);
+ 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, estate, 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 an in-progress transaction is affecting the visibility
+ * of this tuple, we need to wait for it to complete and
+ * restart the scan.
+ */
+ xwait = TransactionIdIsValid(DirtySnapshot.xmin) ?
+ DirtySnapshot.xmin : DirtySnapshot.xmax;
+
+ if (TransactionIdIsValid(xwait))
+ {
+ index_endscan(index_scan);
+ XactLockTableWait(xwait);
+ goto retry;
+ }
+
+ error_new = BuildIndexValueDescription(index, values, isnull);
+
+ {
+ Datum *existing_values = palloc(sizeof(Datum) * index_natts);
+ bool *existing_isnull = palloc(sizeof(bool) * index_natts);
+ ExprContext *econtext = GetPerTupleExprContext(estate);
+
+ /* Going to error soon, so it's OK to change the scan tuple. */
+ econtext->ecxt_scantuple = existing_slot;
+ FormIndexDatum(indexInfo, existing_slot, estate, existing_values,
+ existing_isnull);
+
+ error_existing = BuildIndexValueDescription(index, existing_values,
+ existing_isnull);
+ }
+
+ ereport(ERROR,
+ (errcode(ERRCODE_EXCLUSION_VIOLATION),
+ errmsg("operator exclusion constraint violation detected: "
+ "\"%s\"", RelationGetRelationName(index)),
+ errdetail("Tuple \"%s\" conflicts with existing tuple "
+ "\"%s\".", error_new, error_existing)));
+ }
+
+ Assert(conflict || 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,
+ EState *estate, 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);
+ ExprContext *econtext = GetPerTupleExprContext(estate);
+ 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
***************
*** 2159,2164 **** _copyConstraint(Constraint *from)
--- 2159,2167 ----
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
***************
*** 2105,2110 **** _equalConstraint(Constraint *a, Constraint *b)
--- 2105,2113 ----
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
***************
*** 2397,2402 **** _outConstraint(StringInfo str, Constraint *node)
--- 2397,2410 ----
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
***************
*** 352,357 **** static TypeName *TableFuncTypeName(List *columns);
--- 352,358 ----
%type <node> 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 <list> func_arg_list
%type <node> func_arg_expr
%type <list> row type_list array_expr_list
***************
*** 432,437 **** static TypeName *TableFuncTypeName(List *columns);
--- 433,439 ----
%type <str> opt_existing_window_name
%type <ival> opt_frame_clause frame_extent frame_bound
+ %type <list> ExclusionConstraintList ExclusionConstraintElem
/*
* Non-keyword token types. These are hard-wired into the "flex" lexer.
***************
*** 475,481 **** 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
--- 477,483 ----
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
! EXCLUDE 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
***************
*** 1591,1598 **** alter_table_cmds:
;
alter_table_cmd:
! /* ALTER TABLE <name> ADD [COLUMN] <coldef> */
! ADD_P opt_column columnDef
{
AlterTableCmd *n = makeNode(AlterTableCmd);
n->subtype = AT_AddColumn;
--- 1593,1608 ----
;
alter_table_cmd:
! /* ALTER TABLE <name> ADD <coldef> */
! ADD_P columnDef
! {
! AlterTableCmd *n = makeNode(AlterTableCmd);
! n->subtype = AT_AddColumn;
! n->def = $2;
! $$ = (Node *)n;
! }
! /* ALTER TABLE <name> ADD COLUMN <coldef> */
! | ADD_P COLUMN columnDef
{
AlterTableCmd *n = makeNode(AlterTableCmd);
n->subtype = AT_AddColumn;
***************
*** 2504,2509 **** ConstraintElem:
--- 2514,2534 ----
n->initdeferred = ($11 & 2) != 0;
$$ = (Node *)n;
}
+ | EXCLUDE 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:
***************
*** 2544,2549 **** key_match: MATCH FULL
--- 2569,2591 ----
}
;
+ ExclusionConstraintList:
+ ExclusionConstraintElem { $$ = list_make1($1); }
+ | ExclusionConstraintList ',' ExclusionConstraintElem
+ { $$ = lappend($1, $3); }
+ ;
+
+ ExclusionConstraintElem: index_elem WITH any_operator
+ {
+ $$ = list_make2($1, $3);
+ }
+ ;
+
+ 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
***************
*** 10619,10624 **** unreserved_keyword:
--- 10661,10667 ----
| ENCRYPTED
| ENUM_P
| ESCAPE
+ | EXCLUDE
| EXCLUDING
| EXCLUSIVE
| EXECUTE
*** a/src/backend/parser/parse_utilcmd.c
--- b/src/backend/parser/parse_utilcmd.c
***************
*** 71,76 **** typedef struct
--- 71,77 ----
List *ckconstraints; /* CHECK constraints */
List *fkconstraints; /* FOREIGN KEY constraints */
List *ixconstraints; /* 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 transformOpxConstraints(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
***************
*** 175,180 **** transformCreateStmt(CreateStmt *stmt, const char *queryString)
--- 182,188 ----
cxt.ckconstraints = NIL;
cxt.fkconstraints = NIL;
cxt.ixconstraints = 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;
}
***************
*** 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:
***************
*** 734,739 **** transformInhRelation(ParseState *pstate, CreateStmtContext *cxt,
--- 782,793 ----
/* 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)
{
***************
*** 872,877 **** generateClonedIndexStmt(CreateStmtContext *cxt, Relation source_idx,
--- 926,941 ----
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);
***************
*** 1842,1847 **** transformAlterTableStmt(AlterTableStmt *stmt, const char *queryString)
--- 1906,1912 ----
cxt.ckconstraints = NIL;
cxt.fkconstraints = NIL;
cxt.ixconstraints = NIL;
+ cxt.opxconstraints = NIL;
cxt.inh_indexes = NIL;
cxt.blist = NIL;
cxt.alist = NIL;
***************
*** 1889,1894 **** transformAlterTableStmt(AlterTableStmt *stmt, const char *queryString)
--- 1954,1962 ----
*/
if (IsA(cmd->def, Constraint))
{
+ transformOpxConstraints(pstate, &cxt, stmt->relation,
+ (Constraint *) cmd->def);
+
transformTableConstraint(pstate, &cxt,
(Constraint *) cmd->def);
if (((Constraint *) cmd->def)->contype == CONSTR_FOREIGN)
***************
*** 1947,1953 **** 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);
--- 2015,2024 ----
}
cxt.alist = NIL;
! /*
! * Append any CHECK, FK or operator exclusion constraints to the
! * commands list
! */
foreach(l, cxt.ckconstraints)
{
newcmd = makeNode(AlterTableCmd);
***************
*** 1962,1967 **** transformAlterTableStmt(AlterTableStmt *stmt, const char *queryString)
--- 2033,2045 ----
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);
***************
*** 2254,2256 **** setSchemaName(char *context_schema, char **stmt_schema_name)
--- 2332,2385 ----
"different from the one being created (%s)",
*stmt_schema_name, context_schema)));
}
+
+ static void
+ transformOpxConstraints(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/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
***************
*** 148,153 **** static char *pg_get_indexdef_worker(Oid indexrelid, int colno,
--- 148,155 ----
int prettyFlags);
static char *pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
int prettyFlags);
+ static char *pg_get_opxdef_worker(Oid indexrelid, uint16 *strategies,
+ int prettyFlags);
static text *pg_get_expr_worker(text *expr, Oid relid, const char *relname,
int prettyFlags);
static int print_function_arguments(StringInfo buf, HeapTuple proctup,
***************
*** 1244,1249 **** pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
--- 1246,1282 ----
break;
}
+ case CONSTRAINT_OPX:
+ {
+ bool isnull;
+ Oid indexOid = conForm->conindid;
+ Datum val;
+ Datum *keys;
+ int nKeys;
+ int i;
+ uint16 *strategies;
+
+ val = SysCacheGetAttr(CONSTROID, tup,
+ Anum_pg_constraint_constrategies,
+ &isnull);
+ if (isnull)
+ elog(ERROR, "null constrategies for constraint %u",
+ constraintId);
+
+ deconstruct_array(DatumGetArrayTypeP(val),
+ INT2OID, 2, true, 's',
+ &keys, NULL, &nKeys);
+
+ strategies = palloc(nKeys * sizeof(uint16));
+ for(i = 0; i < nKeys; i++)
+ strategies[i] = DatumGetInt16(keys[i]);
+
+ appendStringInfo(&buf, pg_get_opxdef_worker(indexOid,
+ strategies,
+ prettyFlags));
+
+ break;
+ }
default:
elog(ERROR, "invalid constraint type \"%c\"", conForm->contype);
break;
***************
*** 1291,1296 **** decompile_column_index_array(Datum column_index_array, Oid relId,
--- 1324,1561 ----
}
}
+ static char *
+ pg_get_opxdef_worker(Oid indexrelid, uint16_t *strategies, int prettyFlags)
+ {
+ HeapTuple ht_idx;
+ HeapTuple ht_idxrel;
+ HeapTuple ht_am;
+ Form_pg_index idxrec;
+ Form_pg_class idxrelrec;
+ Form_pg_am amrec;
+ List *indexprs;
+ ListCell *indexpr_item;
+ List *context;
+ Oid indrelid;
+ int keyno;
+ Oid keycoltype;
+ Datum indclassDatum;
+ Datum indoptionDatum;
+ bool isnull;
+ oidvector *indclass;
+ int2vector *indoption;
+ StringInfoData buf;
+ char *str;
+ char *sep;
+ Oid tblspc;
+
+ /*
+ * Fetch the pg_index tuple by the Oid of the index
+ */
+ ht_idx = SearchSysCache(INDEXRELID,
+ ObjectIdGetDatum(indexrelid),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(ht_idx))
+ elog(ERROR, "cache lookup failed for index %u", indexrelid);
+ idxrec = (Form_pg_index) GETSTRUCT(ht_idx);
+
+ indrelid = idxrec->indrelid;
+ Assert(indexrelid == idxrec->indexrelid);
+
+ /* Must get indclass and indoption the hard way */
+ indclassDatum = SysCacheGetAttr(INDEXRELID, ht_idx,
+ Anum_pg_index_indclass, &isnull);
+ Assert(!isnull);
+ indclass = (oidvector *) DatumGetPointer(indclassDatum);
+ indoptionDatum = SysCacheGetAttr(INDEXRELID, ht_idx,
+ Anum_pg_index_indoption, &isnull);
+ Assert(!isnull);
+ indoption = (int2vector *) DatumGetPointer(indoptionDatum);
+
+ /*
+ * Fetch the pg_class tuple of the index relation
+ */
+ ht_idxrel = SearchSysCache(RELOID,
+ ObjectIdGetDatum(indexrelid),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(ht_idxrel))
+ elog(ERROR, "cache lookup failed for relation %u", indexrelid);
+ idxrelrec = (Form_pg_class) GETSTRUCT(ht_idxrel);
+
+ /*
+ * Fetch the pg_am tuple of the index' access method
+ */
+ ht_am = SearchSysCache(AMOID,
+ ObjectIdGetDatum(idxrelrec->relam),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(ht_am))
+ elog(ERROR, "cache lookup failed for access method %u",
+ idxrelrec->relam);
+ amrec = (Form_pg_am) GETSTRUCT(ht_am);
+
+ /*
+ * Get the index expressions, if any. (NOTE: we do not use the relcache
+ * versions of the expressions and predicate, because we want to display
+ * non-const-folded expressions.)
+ */
+ if (!heap_attisnull(ht_idx, Anum_pg_index_indexprs))
+ {
+ Datum exprsDatum;
+ bool isnull;
+ char *exprsString;
+
+ exprsDatum = SysCacheGetAttr(INDEXRELID, ht_idx,
+ Anum_pg_index_indexprs, &isnull);
+ Assert(!isnull);
+ exprsString = TextDatumGetCString(exprsDatum);
+ indexprs = (List *) stringToNode(exprsString);
+ pfree(exprsString);
+ }
+ else
+ indexprs = NIL;
+
+ indexpr_item = list_head(indexprs);
+
+ context = deparse_context_for(get_rel_name(indrelid), indrelid);
+
+ /*
+ * Start the index definition. Note that the index's name should never be
+ * schema-qualified, but the indexed rel's name may be.
+ */
+ initStringInfo(&buf);
+
+ appendStringInfo(&buf, "EXCLUDE USING %s (",
+ quote_identifier(NameStr(amrec->amname)));
+
+ /*
+ * Report the indexed attributes
+ */
+ sep = "";
+ for (keyno = 0; keyno < idxrec->indnatts; keyno++)
+ {
+ AttrNumber attnum = idxrec->indkey.values[keyno];
+ int16 opt = indoption->values[keyno];
+ Oid opfamily = get_opclass_family(indclass->values[keyno]);
+ Oid opid;
+ char *opName;
+
+ appendStringInfoString(&buf, sep);
+ sep = ", ";
+
+ if (attnum != 0)
+ {
+ /* Simple index column */
+ char *attname;
+
+ attname = get_relid_attribute_name(indrelid, attnum);
+ appendStringInfoString(&buf, quote_identifier(attname));
+ keycoltype = get_atttype(indrelid, attnum);
+ }
+ else
+ {
+ /* expressional index */
+ Node *indexkey;
+
+ if (indexpr_item == NULL)
+ elog(ERROR, "too few entries in indexprs list");
+ indexkey = (Node *) lfirst(indexpr_item);
+ indexpr_item = lnext(indexpr_item);
+ /* Deparse */
+ str = deparse_expression_pretty(indexkey, context, false, false,
+ prettyFlags, 0);
+
+ /* Need parens if it's not a bare function call */
+ if (indexkey && IsA(indexkey, FuncExpr) &&
+ ((FuncExpr *) indexkey)->funcformat == COERCE_EXPLICIT_CALL)
+ appendStringInfoString(&buf, str);
+ else
+ appendStringInfo(&buf, "(%s)", str);
+
+ keycoltype = exprType(indexkey);
+ }
+
+ /* Add the operator class name, if not default */
+ get_opclass_name(indclass->values[keyno], keycoltype, &buf);
+
+ /* Add options if relevant */
+ if (amrec->amcanorder)
+ {
+ /* if it supports sort ordering, report DESC and NULLS opts */
+ if (opt & INDOPTION_DESC)
+ {
+ appendStringInfo(&buf, " DESC");
+ /* NULLS FIRST is the default in this case */
+ if (!(opt & INDOPTION_NULLS_FIRST))
+ appendStringInfo(&buf, " NULLS LAST");
+ }
+ else
+ {
+ if (opt & INDOPTION_NULLS_FIRST)
+ appendStringInfo(&buf, " NULLS FIRST");
+ }
+ }
+
+ /* Add operator exclusion constraint */
+ appendStringInfo(&buf, " WITH ");
+
+ opid = get_opfamily_member(opfamily, keycoltype, keycoltype,
+ strategies[keyno]);
+ opName = generate_operator_name(opid, keycoltype, keycoltype);
+
+ appendStringInfo(&buf, "%s", opName);
+ }
+
+ appendStringInfoChar(&buf, ')');
+
+ /*
+ * If it has options, append "WITH (options)"
+ */
+ str = flatten_reloptions(indexrelid);
+ if (str)
+ {
+ appendStringInfo(&buf, " WITH (%s)", str);
+ pfree(str);
+ }
+
+ /*
+ * If it's in a nondefault tablespace, say so, but only if requested
+ */
+ tblspc = get_rel_tablespace(indexrelid);
+ if (OidIsValid(tblspc))
+ appendStringInfo(&buf, " USING INDEX TABLESPACE %s",
+ quote_identifier(get_tablespace_name(tblspc)));
+
+ /*
+ * If it's a partial index, decompile and append the predicate
+ */
+ if (!heap_attisnull(ht_idx, Anum_pg_index_indpred))
+ {
+ Node *node;
+ Datum predDatum;
+ bool isnull;
+ char *predString;
+
+ /* Convert text string to node tree */
+ predDatum = SysCacheGetAttr(INDEXRELID, ht_idx,
+ Anum_pg_index_indpred, &isnull);
+ Assert(!isnull);
+ predString = TextDatumGetCString(predDatum);
+ node = (Node *) stringToNode(predString);
+ pfree(predString);
+
+ /* Deparse */
+ str = deparse_expression_pretty(node, context, false, false,
+ prettyFlags, 0);
+ appendStringInfo(&buf, " WHERE (%s)", str);
+ }
+
+ /* Clean up */
+ ReleaseSysCache(ht_idx);
+ ReleaseSysCache(ht_idxrel);
+ ReleaseSysCache(ht_am);
+
+ return buf.data;
+ }
/* ----------
* 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 = NULL;
+ 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/pg_dump/pg_backup_archiver.c
--- b/src/bin/pg_dump/pg_backup_archiver.c
***************
*** 2855,2860 **** _printTocEntry(ArchiveHandle *AH, TocEntry *te, RestoreOptions *ropt, bool isDat
--- 2855,2861 ----
strcmp(te->desc, "CONSTRAINT") == 0 ||
strcmp(te->desc, "DEFAULT") == 0 ||
strcmp(te->desc, "FK CONSTRAINT") == 0 ||
+ strcmp(te->desc, "EXCLUSION CONSTRAINT") == 0 ||
strcmp(te->desc, "INDEX") == 0 ||
strcmp(te->desc, "RULE") == 0 ||
strcmp(te->desc, "TRIGGER") == 0 ||
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
***************
*** 3680,3685 **** getIndexes(TableInfo tblinfo[], int numTables)
--- 3680,3686 ----
i_condeferred,
i_contableoid,
i_conoid,
+ i_condef,
i_tablespace,
i_options;
int ntups;
***************
*** 3710,3716 **** getIndexes(TableInfo tblinfo[], int numTables)
* assume an index won't have more than one internal dependency.
*/
resetPQExpBuffer(query);
! if (g_fout->remoteVersion >= 80200)
{
appendPQExpBuffer(query,
"SELECT t.tableoid, t.oid, "
--- 3711,3745 ----
* assume an index won't have more than one internal dependency.
*/
resetPQExpBuffer(query);
! if (g_fout->remoteVersion >= 80500)
! {
! appendPQExpBuffer(query,
! "SELECT t.tableoid, t.oid, "
! "t.relname AS indexname, "
! "pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef, "
! "t.relnatts AS indnkeys, "
! "i.indkey, i.indisclustered, "
! "c.contype, c.conname, "
! "c.condeferrable, c.condeferred, "
! "c.tableoid AS contableoid, "
! "pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, "
! "c.oid AS conoid, "
! "(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, "
! "array_to_string(t.reloptions, ', ') AS options "
! "FROM pg_catalog.pg_index i "
! "JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) "
! "LEFT JOIN pg_catalog.pg_depend d "
! "ON (d.classid = t.tableoid "
! "AND d.objid = t.oid "
! "AND d.deptype = 'i') "
! "LEFT JOIN pg_catalog.pg_constraint c "
! "ON (d.refclassid = c.tableoid "
! "AND d.refobjid = c.oid) "
! "WHERE i.indrelid = '%u'::pg_catalog.oid "
! "ORDER BY indexname",
! tbinfo->dobj.catId.oid);
! }
! else if (g_fout->remoteVersion >= 80200)
{
appendPQExpBuffer(query,
"SELECT t.tableoid, t.oid, "
***************
*** 3858,3863 **** getIndexes(TableInfo tblinfo[], int numTables)
--- 3887,3893 ----
i_condeferred = PQfnumber(res, "condeferred");
i_contableoid = PQfnumber(res, "contableoid");
i_conoid = PQfnumber(res, "conoid");
+ i_condef = PQfnumber(res, "condef");
i_tablespace = PQfnumber(res, "tablespace");
i_options = PQfnumber(res, "options");
***************
*** 3895,3901 **** getIndexes(TableInfo tblinfo[], int numTables)
indxinfo[j].indisclustered = (PQgetvalue(res, j, i_indisclustered)[0] == 't');
contype = *(PQgetvalue(res, j, i_contype));
! if (contype == 'p' || contype == 'u')
{
/*
* If we found a constraint matching the index, create an
--- 3925,3931 ----
indxinfo[j].indisclustered = (PQgetvalue(res, j, i_indisclustered)[0] == 't');
contype = *(PQgetvalue(res, j, i_contype));
! if (contype == 'p' || contype == 'u' || contype == 'x')
{
/*
* If we found a constraint matching the index, create an
***************
*** 3913,3919 **** getIndexes(TableInfo tblinfo[], int numTables)
constrinfo[j].contable = tbinfo;
constrinfo[j].condomain = NULL;
constrinfo[j].contype = contype;
! constrinfo[j].condef = NULL;
constrinfo[j].confrelid = InvalidOid;
constrinfo[j].conindex = indxinfo[j].dobj.dumpId;
constrinfo[j].condeferrable = *(PQgetvalue(res, j, i_condeferrable)) == 't';
--- 3943,3952 ----
constrinfo[j].contable = tbinfo;
constrinfo[j].condomain = NULL;
constrinfo[j].contype = contype;
! if (contype == 'x')
! constrinfo[j].condef = strdup(PQgetvalue(res, j, i_condef));
! else
! constrinfo[j].condef = NULL;
constrinfo[j].confrelid = InvalidOid;
constrinfo[j].conindex = indxinfo[j].dobj.dumpId;
constrinfo[j].condeferrable = *(PQgetvalue(res, j, i_condeferrable)) == 't';
***************
*** 10912,10917 **** dumpConstraint(Archive *fout, ConstraintInfo *coninfo)
--- 10945,10975 ----
NULL, NULL);
}
}
+ else if (coninfo->contype == 'x')
+ {
+ appendPQExpBuffer(q, "ALTER TABLE ONLY %s\n",
+ fmtId(tbinfo->dobj.name));
+ appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n",
+ fmtId(coninfo->dobj.name),
+ coninfo->condef);
+
+ appendPQExpBuffer(delq, "ALTER TABLE ONLY %s.",
+ fmtId(tbinfo->dobj.namespace->dobj.name));
+ appendPQExpBuffer(delq, "%s ",
+ fmtId(tbinfo->dobj.name));
+ appendPQExpBuffer(delq, "DROP CONSTRAINT %s;\n",
+ fmtId(coninfo->dobj.name));
+
+ ArchiveEntry(fout, coninfo->dobj.catId, coninfo->dobj.dumpId,
+ coninfo->dobj.name,
+ tbinfo->dobj.namespace->dobj.name,
+ NULL,
+ tbinfo->rolname, false,
+ "EXCLUSION CONSTRAINT", SECTION_POST_DATA,
+ q->data, delq->data, NULL,
+ coninfo->dobj.dependencies, coninfo->dobj.nDeps,
+ NULL, NULL);
+ }
else
{
write_msg(NULL, "unrecognized constraint type: %c\n", coninfo->contype);
*** 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/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
***************
*** 328,332 **** extern void RegisterExprContextCallback(ExprContext *econtext,
--- 328,337 ----
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, IndexInfo *indexInfo,
+ EState *estate, 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
***************
*** 1395,1400 **** typedef enum ConstrType /* types of constraints */
--- 1395,1401 ----
CONSTR_CHECK,
CONSTR_PRIMARY,
CONSTR_UNIQUE,
+ CONSTR_OPERATOR_EXCLUSION,
CONSTR_FOREIGN,
CONSTR_ATTR_DEFERRABLE, /* attributes for previous constraint node */
CONSTR_ATTR_NOT_DEFERRABLE,
***************
*** 1429,1439 **** 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 */
--- 1430,1445 ----
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
***************
*** 143,148 **** PG_KEYWORD("end", END_P, RESERVED_KEYWORD)
--- 143,149 ----
PG_KEYWORD("enum", ENUM_P, UNRESERVED_KEYWORD)
PG_KEYWORD("escape", ESCAPE, UNRESERVED_KEYWORD)
PG_KEYWORD("except", EXCEPT, RESERVED_KEYWORD)
+ PG_KEYWORD("exclude", EXCLUDE, UNRESERVED_KEYWORD)
PG_KEYWORD("excluding", EXCLUDING, UNRESERVED_KEYWORD)
PG_KEYWORD("exclusive", EXCLUSIVE, UNRESERVED_KEYWORD)
PG_KEYWORD("execute", EXECUTE, UNRESERVED_KEYWORD)
*** a/src/include/utils/errcodes.h
--- b/src/include/utils/errcodes.h
***************
*** 167,172 ****
--- 167,173 ----
#define ERRCODE_FOREIGN_KEY_VIOLATION MAKE_SQLSTATE('2','3', '5','0','3')
#define ERRCODE_UNIQUE_VIOLATION MAKE_SQLSTATE('2','3', '5','0','5')
#define ERRCODE_CHECK_VIOLATION MAKE_SQLSTATE('2','3', '5','1','4')
+ #define ERRCODE_EXCLUSION_VIOLATION MAKE_SQLSTATE('2','3', 'P','0','1')
/* Class 24 - Invalid Cursor State */
#define ERRCODE_INVALID_CURSOR_STATE MAKE_SQLSTATE('2','4', '0','0','0')
*** a/src/include/utils/relcache.h
--- b/src/include/utils/relcache.h
***************
*** 43,48 **** extern Oid RelationGetOidIndex(Relation relation);
--- 43,49 ----
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/pl/plpgsql/src/plerrcodes.h
--- b/src/pl/plpgsql/src/plerrcodes.h
***************
*** 304,309 ****
--- 304,313 ----
},
{
+ "exclusion_violation", ERRCODE_EXCLUSION_VIOLATION
+ },
+
+ {
"invalid_cursor_state", ERRCODE_INVALID_CURSOR_STATE
},
*** 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,
+ EXCLUDE USING gist
+ (c1 WITH &&, (c2::circle) 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 EXCLUDE USING gist
+ (c1 WITH &&, (c2::circle) WITH ~=);
+
+ DROP TABLE circles;
+
+
*** a/src/test/regress/output/constraints.source
--- b/src/test/regress/output/constraints.source
***************
*** 512,514 **** SELECT * FROM unique_tbl;
--- 512,542 ----
(5 rows)
DROP TABLE unique_tbl;
+ CREATE TABLE circles (
+ c1 CIRCLE,
+ c2 TEXT,
+ EXCLUDE USING gist
+ (c1 WITH &&, (c2::circle) WITH ~=)
+ WHERE (circle_center(c1) <> '(0,0)')
+ );
+ NOTICE: ALTER TABLE / ADD EXCLUDE 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 "(c1, (c2::circle))=(<(20,20),10>, <(0,0),5>)" conflicts with existing tuple "(c1, (c2::circle))=(<(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 EXCLUDE USING gist
+ (c1 WITH &&, (c2::circle) WITH ~=);
+ NOTICE: ALTER TABLE / ADD EXCLUDE will create implicit index "circles_c1_exclusion1" for table "circles"
+ ERROR: operator exclusion constraint violation detected: "circles_c1_exclusion1"
+ DETAIL: Tuple "(c1, (c2::circle))=(<(0,0),5>, <(0,0),5>)" conflicts with existing tuple "(c1, (c2::circle))=(<(0,0),5>, <(0,0),5>)".
+ DROP TABLE circles;