operator-exclusion-constraints-20090927.context.patch
text/x-patch
Filename: operator-exclusion-constraints-20090927.context.patch
Type: text/x-patch
Part: 1
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/alter_table.sgml | 7 | 0 |
| doc/src/sgml/ref/create_table.sgml | 77 | 0 |
| src/backend/access/index/indexam.c | 5 | 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 | 55 | 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/indexcmds.c | 32 | 0 |
| src/backend/commands/tablecmds.c | 206 | 0 |
| src/backend/commands/typecmds.c | 1 | 0 |
| src/backend/executor/execUtils.c | 253 | 0 |
| 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 | 28 | 0 |
| src/backend/parser/parse_utilcmd.c | 69 | 0 |
| src/backend/storage/ipc/ipci.c | 1 | 0 |
| src/backend/tcop/utility.c | 1 | 0 |
| src/backend/utils/adt/ruleutils.c | 71 | 0 |
| src/backend/utils/cache/relcache.c | 72 | 0 |
| src/bin/psql/describe.c | 52 | 0 |
| src/include/access/genam.h | 2 | 0 |
| src/include/catalog/indexing.h | 2 | 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/nodes/execnodes.h | 1 | 0 |
| src/include/nodes/parsenodes.h | 7 | 0 |
| src/include/parser/kwlist.h | 1 | 0 |
| src/include/storage/lwlock.h | 1 | 0 |
| src/include/utils/relcache.h | 2 | 0 |
| src/test/regress/input/constraints.source | 24 | 0 |
| src/test/regress/output/constraints.source | 22 | 0 |
*** doc/src/sgml/ref/alter_table.sgml
--- doc/src/sgml/ref/alter_table.sgml
***************
*** 61,66 **** ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
--- 61,73 ----
NO INHERIT <replaceable class="PARAMETER">parent_table</replaceable>
OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable>
+
+ and <replaceable class="PARAMETER">index_constraint</replaceable> is:
+
+ [ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
+ ( <replaceable class="PARAMETER">column_name operator</replaceable> [, ...] ) USING INDEX <replaceable class="PARAMETER">index_name</replaceable>
+
+
</synopsis>
</refsynopsisdiv>
***************
*** 905,911 **** ALTER TABLE distributors SET TABLESPACE fasttablespace;
ALTER TABLE myschema.distributors SET SCHEMA yourschema;
</programlisting>
</para>
-
</refsect1>
<refsect1>
--- 912,917 ----
*** doc/src/sgml/ref/create_table.sgml
--- doc/src/sgml/ref/create_table.sgml
***************
*** 51,57 **** 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>
--- 51,58 ----
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> ] |
! EXCLUSION [USING <replaceable class="parameter">index_method</replaceable>] (<replaceable class="parameter">expression</replaceable> CHECK 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>
***************
*** 524,529 **** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable class="PAR
--- 525,593 ----
</varlistentry>
<varlistentry>
+ <term><literal>EXCLUSION [USING <replaceable class="parameter">index_method</replaceable>] (<replaceable class="parameter">expression</replaceable> CHECK WITH <replaceable class="parameter">operator</replaceable> [, ...]) <replaceable class="parameter">index_parameters</replaceable> [WHERE <replaceable class="parameter">predicate</replaceable>]</literal></term>
+ <listitem>
+ <para>
+ The <literal>EXCLUSION</> clause specifies an operator exclusion
+ constraint. An operator exclusion constraint is more general
+ than a <literal>UNIQUE</literal> constraint, and can use any
+ operator to detect conflicts. 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>&&</literal> operator.
+ </para>
+
+ <para>
+ Note that the constraint is specifying the conflict condition,
+ so the operator should return <literal>TRUE</literal> when
+ applied to two conflicting values.
+ </para>
+
+ <para>
+ Internally, operator exclusion constraints use an index to
+ perform a search looking for conflicting values, and handle
+ concurrent operations similar to a <literal>UNIQUE</literal>
+ constraint. The operator specified must be commutative (that is,
+ the commutator of the operator must be the operator itself),
+ must be a boolean operator, and must be associated with an
+ operator class (see <xref linkend="SQL-CREATEOPCLASS">) using
+ <replaceable class="parameter">index_method</replaceable>. The
+ constraint is violated if, and only if, there exist two tuples
+ where all corresponding expressions between the tuples conflict
+ according
+ to <replaceable class="parameter">operator</replaceable>
+ (i.e. the operator returns <literal>TRUE</literal>).
+ </para>
+
+ <para>
+ The <replaceable class="parameter">index_parameters</replaceable>
+ are the same as for a <literal>UNIQUE</literal> constraint, and
+ the <replaceable class="parameter">predicate</replaceable>
+ allows you to specify the constraint on a subset of the table
+ (internally using a partial index;
+ see <xref linkend="SQL-CREATEINDEX">). The <replaceable class="parameter">expression</replaceable>
+ is normally just a column name, but can also be an expression
+ and the constraint will be on the result of the function
+ (similar to creating a unique index over an expression).
+ </para>
+
+ <para>
+ If all of the operators are specified as the equality operator
+ (usually <literal>=</literal>), this constraint behaves
+ identically to a <literal>UNIQUE</literal> constraint, but may
+ exhibit slightly worse performance than
+ specifying <literal>UNIQUE</literal> (operator exclusion
+ constraints require one additional index search). The advantage
+ of operator exclusion constraints is the ability to specify more
+ general constraints (like a non-overlapping constraint for
+ circles), and also the ability to use index methods other
+ than <literal>btree</literal>, like <literal>GiST</literal>
+ (see <xref linkend="GiST">).
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>DEFERRABLE</literal></term>
<term><literal>NOT DEFERRABLE</literal></term>
<listitem>
***************
*** 1087,1092 **** CREATE TABLE cinemas (
--- 1151,1168 ----
</programlisting>
</para>
+ <para>
+ Create table <structname>circles</> with an operator exclusion
+ constraint that prevents overlapping circles within it:
+
+ <programlisting>
+ CREATE TABLE circles (
+ c circle,
+ EXCLUSION USING gist (c CHECK WITH &&)
+ );
+ </programlisting>
+ </para>
+
</refsect1>
<refsect1 id="SQL-CREATETABLE-compatibility">
*** src/backend/access/index/indexam.c
--- src/backend/access/index/indexam.c
***************
*** 26,31 ****
--- 26,32 ----
* index_vacuum_cleanup - post-deletion cleanup of an index
* index_getprocid - get a support procedure OID
* index_getprocinfo - get a support procedure's lookup info
+ * index_check_constraint - check operator exclusion constraints
*
* NOTES
* This file contains the index_ routines which used
***************
*** 64,72 ****
--- 65,77 ----
#include "access/relscan.h"
#include "access/transam.h"
+ #include "miscadmin.h"
#include "pgstat.h"
#include "storage/bufmgr.h"
#include "storage/lmgr.h"
+ #include "storage/lwlock.h"
+ #include "storage/procarray.h"
+ #include "utils/lsyscache.h"
#include "utils/relcache.h"
#include "utils/snapmgr.h"
#include "utils/tqual.h"
***************
*** 116,122 **** do { \
static IndexScanDesc index_beginscan_internal(Relation indexRelation,
int nkeys, ScanKey key);
-
/* ----------------------------------------------------------------
* index_ interface functions
* ----------------------------------------------------------------
--- 121,126 ----
*** src/backend/bootstrap/bootparse.y
--- src/backend/bootstrap/bootparse.y
***************
*** 265,271 **** Boot_DeclareIndexStmt:
LexIDStr($8),
NULL,
$10,
! NULL, NIL,
false, false, false, false, false,
false, false, true, false, false);
do_end();
--- 265,271 ----
LexIDStr($8),
NULL,
$10,
! NULL, NIL, NULL,
false, false, false, false, false,
false, false, true, false, false);
do_end();
***************
*** 283,289 **** Boot_DeclareUniqueIndexStmt:
LexIDStr($9),
NULL,
$11,
! NULL, NIL,
true, false, false, false, false,
false, false, true, false, false);
do_end();
--- 283,289 ----
LexIDStr($9),
NULL,
$11,
! NULL, NIL, NULL,
true, false, false, false, false,
false, false, true, false, false);
do_end();
*** src/backend/bootstrap/bootstrap.c
--- src/backend/bootstrap/bootstrap.c
***************
*** 1279,1284 **** index_register(Oid heap,
--- 1279,1287 ----
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;
*** src/backend/catalog/heap.c
--- src/backend/catalog/heap.c
***************
*** 671,676 **** InsertPgClassTuple(Relation pg_class_desc,
--- 671,677 ----
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);
***************
*** 1671,1676 **** StoreRelCheck(Relation rel, char *ccname, Node *expr,
--- 1672,1678 ----
' ',
' ',
' ',
+ NULL,
expr, /* Tree form check constraint */
ccbin, /* Binary form check constraint */
ccsrc, /* Source form check constraint */
*** src/backend/catalog/index.c
--- src/backend/catalog/index.c
***************
*** 727,742 **** 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,
--- 727,749 ----
constraintType = CONSTRAINT_PRIMARY;
else if (indexInfo->ii_Unique)
constraintType = CONSTRAINT_UNIQUE;
+ else if (indexInfo->ii_ExclusionConstraint != NULL)
+ constraintType = CONSTRAINT_OPX;
else
{
! elog(ERROR, "constraint must be PRIMARY, UNIQUE or EXCLUSION");
constraintType = 0; /* keep compiler quiet */
}
/* Shouldn't have any expressions */
! if (indexInfo->ii_Expressions &&
! constraintType != CONSTRAINT_OPX)
elog(ERROR, "constraints cannot have index expressions");
+ if (constraintType == CONSTRAINT_OPX && concurrent)
+ elog(ERROR, "concurrent index builds not supported for "
+ "operator exclusion constraints");
+
conOid = CreateConstraintEntry(indexRelationName,
namespaceId,
constraintType,
***************
*** 756,761 **** index_create(Oid heapRelationId,
--- 763,769 ----
' ',
' ',
' ',
+ indexInfo->ii_ExclusionConstraint,
NULL, /* no check constraint */
NULL,
NULL,
***************
*** 801,806 **** index_create(Oid heapRelationId,
--- 809,848 ----
"Unique_ConstraintTrigger",
false);
}
+
+ CommandCounterIncrement();
+ /* Increment pg_class.relopxconstraints for the index. */
+ if (constraintType == CONSTRAINT_OPX)
+ {
+ Relation pgrel;
+ Form_pg_class heap_class;
+ HeapTuple relTup;
+
+ pgrel = heap_open(RelationRelationId, RowExclusiveLock);
+
+ /* Increment the count for the heap. */
+ relTup = SearchSysCacheCopy(RELOID,
+ ObjectIdGetDatum(indexRelationId),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(relTup))
+ elog(ERROR, "cache lookup failed for relation %u",
+ indexRelationId);
+ heap_class = (Form_pg_class) GETSTRUCT(relTup);
+
+ if (heap_class->relopxconstraints != 0)
+ elog(ERROR, "relation \"%s\" has relopxconstraints = %d",
+ indexRelationName, heap_class->relopxconstraints);
+
+ heap_class->relopxconstraints++;
+
+ simple_heap_update(pgrel, &relTup->t_self, relTup);
+
+ CatalogUpdateIndexes(pgrel, relTup);
+
+ heap_freetuple(relTup);
+
+ heap_close(pgrel, RowExclusiveLock);
+ }
}
else
{
***************
*** 1080,1085 **** BuildIndexInfo(Relation index)
--- 1122,1131 ----
/* 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;
***************
*** 1891,1896 **** IndexBuildHeapScan(Relation heapRelation,
--- 1937,1945 ----
indexInfo->ii_ExpressionsState = NIL;
indexInfo->ii_PredicateState = NIL;
+ /* operator exclusion constraints aren't supported at index build time. */
+ indexInfo->ii_ExclusionConstraint = NULL;
+
return reltuples;
}
***************
*** 2265,2270 **** validate_index_heapscan(Relation heapRelation,
--- 2314,2322 ----
/* These may have been pointing to the now-gone estate */
indexInfo->ii_ExpressionsState = NIL;
indexInfo->ii_PredicateState = NIL;
+
+ /* operator exclusion constraints aren't supported at index build time. */
+ indexInfo->ii_ExclusionConstraint = NULL;
}
***************
*** 2520,2522 **** reindex_relation(Oid relid, bool toast_too)
--- 2572,2575 ----
return result;
}
+
*** src/backend/catalog/information_schema.sql
--- 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')
*** src/backend/catalog/pg_constraint.c
--- 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);
*** src/backend/catalog/toasting.c
--- src/backend/catalog/toasting.c
***************
*** 242,247 **** create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
--- 242,250 ----
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;
*** src/backend/commands/indexcmds.c
--- 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,
***************
*** 251,257 **** DefineIndex(RangeVar *heapRelation,
NULL,
"pkey",
namespaceId);
! else
{
IndexElem *iparam = (IndexElem *) linitial(attributeList);
--- 250,256 ----
NULL,
"pkey",
namespaceId);
! else if (unique)
{
IndexElem *iparam = (IndexElem *) linitial(attributeList);
***************
*** 260,265 **** DefineIndex(RangeVar *heapRelation,
--- 259,273 ----
"key",
namespaceId);
}
+ else if (exclusion_constraint != NULL)
+ {
+ IndexElem *iparam = (IndexElem *) linitial(attributeList);
+
+ indexRelationName = ChooseRelationName(RelationGetRelationName(rel),
+ iparam->name,
+ "exclusion",
+ namespaceId);
+ }
}
/*
***************
*** 424,429 **** DefineIndex(RangeVar *heapRelation,
--- 432,440 ----
indexInfo->ii_Concurrent = concurrent;
indexInfo->ii_BrokenHotChain = false;
+ /* operator exclusion constraints aren't supported at index build time. */
+ indexInfo->ii_ExclusionConstraint = exclusion_constraint;
+
classObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid));
coloptions = (int16 *) palloc(numberOfAttributes * sizeof(int16));
ComputeIndexAttrs(indexInfo, classObjectId, coloptions, attributeList,
***************
*** 435,445 **** DefineIndex(RangeVar *heapRelation,
* error checks)
*/
if (isconstraint && !quiet)
ereport(NOTICE,
(errmsg("%s %s will create implicit index \"%s\" for table \"%s\"",
is_alter_table ? "ALTER TABLE / ADD" : "CREATE TABLE /",
! primary ? "PRIMARY KEY" : "UNIQUE",
indexRelationName, RelationGetRelationName(rel))));
/* save lockrelid and locktag for below, then close rel */
heaprelid = rel->rd_lockInfo.lockRelId;
--- 446,468 ----
* error checks)
*/
if (isconstraint && !quiet)
+ {
+ char *constraint_type;
+ if (primary)
+ constraint_type = "PRIMARY KEY";
+ else if (unique)
+ constraint_type = "UNIQUE";
+ else if (exclusion_constraint != NULL)
+ constraint_type = "EXCLUSION";
+ else
+ elog(ERROR, "unknown constraint type");
+
ereport(NOTICE,
(errmsg("%s %s will create implicit index \"%s\" for table \"%s\"",
is_alter_table ? "ALTER TABLE / ADD" : "CREATE TABLE /",
! constraint_type,
indexRelationName, RelationGetRelationName(rel))));
+ }
/* save lockrelid and locktag for below, then close rel */
heaprelid = rel->rd_lockInfo.lockRelId;
***************
*** 455,461 **** DefineIndex(RangeVar *heapRelation,
isconstraint, deferrable, initdeferred,
allowSystemTableMods, skip_build, concurrent);
! return; /* We're done, in the standard case */
}
/*
--- 478,484 ----
isconstraint, deferrable, initdeferred,
allowSystemTableMods, skip_build, concurrent);
! return indexRelationId; /* We're done, in the standard case */
}
/*
***************
*** 750,755 **** DefineIndex(RangeVar *heapRelation,
--- 773,780 ----
* 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)
{
--- 964,970 ----
/*
* Resolve possibly-defaulted operator class specification
*/
! Oid
GetIndexOpClass(List *opclass, Oid attrType,
char *accessMethodName, Oid accessMethodId)
{
*** src/backend/commands/tablecmds.c
--- src/backend/commands/tablecmds.c
***************
*** 304,309 **** static void ATAddCheckConstraint(List **wqueue,
--- 304,312 ----
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,
***************
*** 4515,4520 **** ATExecAddIndex(AlteredTableInfo *tab, Relation rel,
--- 4518,4524 ----
stmt->indexParams, /* parameters */
(Expr *) stmt->whereClause,
stmt->options,
+ NULL,
stmt->unique,
stmt->primary,
stmt->isconstraint,
***************
*** 4578,4583 **** ATExecAddConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
--- 4582,4622 ----
ATAddForeignKeyConstraint(tab, rel, newConstraint);
break;
+ case CONSTR_OPERATOR_EXCLUSION:
+ /*
+ * We don't recurse for operator exclusion constraints, either.
+ */
+ if (newConstraint->conname)
+ {
+ if (ConstraintNameIsUsed(CONSTRAINT_OPX,
+ RelationGetRelid(rel),
+ RelationGetNamespace(rel),
+ newConstraint->conname))
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("constraint \"%s\" for relation \"%s\" already exists",
+ newConstraint->conname,
+ RelationGetRelationName(rel))));
+ }
+ else
+ {
+ char *choose_name2 = "";
+ IndexElem *ie = linitial(newConstraint->operator_exclusion);
+
+ if (ie->name != NULL)
+ choose_name2 = ie->name;
+
+ newConstraint->conname =
+ ChooseConstraintName(RelationGetRelationName(rel),
+ choose_name2,
+ "exclusion",
+ RelationGetNamespace(rel),
+ NIL);
+ }
+
+ ATAddOperatorExclusionConstraint(tab, rel, newConstraint);
+ break;
+
default:
elog(ERROR, "unrecognized constraint type: %d",
(int) newConstraint->contype);
***************
*** 4947,4952 **** ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
--- 4986,4992 ----
fkconstraint->fk_upd_action,
fkconstraint->fk_del_action,
fkconstraint->fk_matchtype,
+ NULL,
NULL, /* no check constraint */
NULL,
NULL,
***************
*** 4983,4988 **** ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
--- 5023,5194 ----
heap_close(pkrel, NoLock);
}
+ static void
+ ATAddOperatorExclusionConstraint(AlteredTableInfo *tab, Relation rel,
+ Constraint *constraint)
+ {
+ Oid constrOid;
+ Oid indexOid;
+ int natts;
+ ListCell *lc;
+ HeapTuple tup;
+ Oid methodOid;
+ List *indexElems = NIL;
+ int16 *exclusion_constraint;
+ Oid gettupleOid;
+ Form_pg_am am;
+ RangeVar *rv;
+ int i;
+
+ /*
+ * Find access method oid, and make sure it supports gettuple.
+ */
+ tup = SearchSysCache(AMNAME,
+ CStringGetDatum(constraint->using_method),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(tup))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("access method \"%s\" does not exist",
+ constraint->using_method)));
+
+ methodOid = HeapTupleGetOid(tup);
+ am = (Form_pg_am) GETSTRUCT(tup);
+ gettupleOid = am->amgettuple;
+
+ ReleaseSysCache(tup);
+
+ if (!OidIsValid(gettupleOid))
+ ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("method \"%s\" does not support gettuple",
+ constraint->using_method)));
+
+ natts = list_length(constraint->operator_exclusion);
+
+ exclusion_constraint = palloc(sizeof(int16) * natts);
+
+ /*
+ * Create the strategies array from the input (IndexElem, Operator)
+ * pairs. Also, make an array of IndexElems to pass to DefineIndex().
+ */
+ i = 0;
+ foreach (lc, constraint->operator_exclusion)
+ {
+ List *pair = lfirst(lc);
+ List *opname;
+ IndexElem *elem;
+ Oid opfamily;
+ Oid opclassid;
+ Oid typoid;
+ Oid opid;
+
+ Assert(list_length(pair) == 2);
+
+ elem = linitial(pair);
+ Assert(IsA(elem, IndexElem));
+ opname = lsecond(pair);
+ Assert(IsA(opname, List));
+
+ indexElems = lappend(indexElems, elem);
+
+ if (elem->name != NULL)
+ {
+ AttrNumber heapatt = get_attnum(RelationGetRelid(rel), elem->name);
+ typoid = rel->rd_att->attrs[heapatt - 1]->atttypid;
+ }
+ else
+ typoid = exprType(elem->expr);
+
+ opid = LookupOperName(NULL, opname, typoid, typoid, false, -1);
+
+ opclassid = GetIndexOpClass(elem->opclass, typoid,
+ constraint->using_method, methodOid);
+
+ opfamily = get_opclass_family(opclassid);
+
+ /*
+ * Only allow commutative operators to be used for operator
+ * exclusion constraints. If X conflicts with Y, but Y does
+ * not conflict with X, bad things will happen.
+ */
+ if (get_commutator(opid) != opid)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("operator %s for exclusion constraint must be "
+ "commutative", quote_identifier(get_opname(opid))),
+ errdetail("Set the operator's COMMUTATOR to be itself, "
+ "or choose a different operator.")
+ ));
+ }
+
+ exclusion_constraint[i] = get_op_opfamily_strategy(opid, opfamily);
+
+ if (exclusion_constraint[i] == InvalidStrategy)
+ elog(ERROR, "no strategy found for operator %d "
+ "in operator family %d", opid, opfamily);
+
+ i++;
+ }
+
+ rv = makeRangeVar(get_namespace_name(RelationGetNamespace(rel)),
+ pstrdup(RelationGetRelationName(rel)),
+ -1);
+ /*
+ * Build index to enforce the constraint. Set isconstraint to
+ * false because we're building the constraint entry ourselves.
+ */
+ DefineIndex(rv, /* relation range var */
+ NULL, /* index name */
+ InvalidOid, /* predefined OID */
+ constraint->using_method, /* am name */
+ constraint->indexspace, /* index tablespace */
+ indexElems, /* parameters */
+ (Expr *) constraint->where_clause, /* where */
+ constraint->options, /* options */
+ exclusion_constraint, /* exclusion constraint */
+ false, /* unique */
+ false, /* primary */
+ true, /* is constraint? */
+ constraint->deferrable, /* deferrable */
+ constraint->initdeferred, /* init deferred */
+ true, /* is_alter_table? */
+ true, /* check rights? */
+ false, /* skip build? */
+ false, /* quiet? */
+ false); /* concurrent? */
+
+ /*
+ * Increment pg_class.relopxconstraints for heap only, the index's
+ * entry should already have been incremented by index_create().
+ */
+ {
+ Relation pgrel;
+ Form_pg_class heap_class;
+ HeapTuple relTup;
+
+ pgrel = heap_open(RelationRelationId, RowExclusiveLock);
+
+ /* Increment the count for the heap. */
+ relTup = SearchSysCacheCopy(RELOID,
+ ObjectIdGetDatum(RelationGetRelid(rel)),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(relTup))
+ elog(ERROR, "cache lookup failed for relation %u",
+ RelationGetRelid(rel));
+ heap_class = (Form_pg_class) GETSTRUCT(relTup);
+
+ heap_class->relopxconstraints++;
+
+ simple_heap_update(pgrel, &relTup->t_self, relTup);
+
+ CatalogUpdateIndexes(pgrel, relTup);
+
+ heap_freetuple(relTup);
+
+ heap_close(pgrel, RowExclusiveLock);
+ }
+ }
/*
* transformColumnNameList - transform list of column names
*** src/backend/commands/typecmds.c
--- 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 */
*** src/backend/executor/execUtils.c
--- 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"
***************
*** 69,75 **** int NIndexTupleProcessed;
static bool get_last_attnums(Node *node, ProjectionInfo *projInfo);
static void ShutdownExprContext(ExprContext *econtext, bool isCommit);
!
/* ----------------------------------------------------------------
* statistic functions
--- 73,87 ----
static bool get_last_attnums(Node *node, ProjectionInfo *projInfo);
static void ShutdownExprContext(ExprContext *econtext, bool isCommit);
! static void index_check_constraint(Relation heap, Relation index,
! TupleTableSlot *slot, ItemPointer tupleid,
! Datum *values, bool *isnull,
! int16 *exclusion_constraint,
! List *index_exprs, ExprContext *econtext);
! static bool index_recheck_constraint(Relation index, TupleTableSlot *slot,
! ExprContext *econtext, List *index_exprs,
! Datum *new_values, Oid *constr_procs);
! static char * tuple_as_string(TupleTableSlot *slot);
/* ----------------------------------------------------------------
* statistic functions
***************
*** 1167,1172 **** ExecInsertIndexTuples(TupleTableSlot *slot,
--- 1179,1191 ----
result = lappend_oid(result, RelationGetRelid(indexRelation));
}
+ if (indexInfo->ii_ExclusionConstraint != NULL)
+ index_check_constraint(heapRelation, indexRelation, slot,
+ tupleid, values, isnull,
+ indexInfo->ii_ExclusionConstraint,
+ indexInfo->ii_ExpressionsState,
+ econtext);
+
/*
* keep track of index inserts for debugging
*/
***************
*** 1297,1299 **** ShutdownExprContext(ExprContext *econtext, bool isCommit)
--- 1316,1551 ----
MemoryContextSwitchTo(oldcontext);
}
+
+ static void
+ index_check_constraint(Relation heap, Relation index, TupleTableSlot *new_slot,
+ ItemPointer tupleid, Datum *values, bool *isnull,
+ int16 *exclusion_constraint, List *index_exprs,
+ ExprContext *econtext)
+ {
+ IndexScanDesc index_scan;
+ HeapTuple tup;
+ ScanKeyData *scankeys;
+ int2 index_natts = index->rd_index->indnatts;
+ Oid *constr_procs;
+ SnapshotData DirtySnapshot;
+ int nkeys = 0;
+ int i;
+ bool found_self = false;
+ TupleTableSlot *existing_slot;
+
+
+
+ /*
+ * If any of the input values are NULL, the constraint check must
+ * pass.
+ */
+ for (i = 0; i < index_natts; i++)
+ if (isnull[i])
+ return;
+
+ /*
+ * Find the function that tests for a conflict based on the
+ * strategy number, operator family, and types.
+ */
+ constr_procs = palloc(sizeof(Oid) * index_natts);
+ for (i = 0; i < index_natts; i++)
+ {
+ /*
+ * Find the procedure implementing the strategy for the
+ * index for two arguments both with the type of the
+ * indexed attribute.
+ */
+ Oid oper;
+ Oid opfamily = index->rd_opfamily[i];
+ Oid typoid = index->rd_opcintype[i];
+ StrategyNumber strategy = exclusion_constraint[i];
+
+ if (strategy == InvalidStrategy)
+ continue;
+
+ oper = get_opfamily_member(opfamily, typoid, typoid, strategy);
+
+ if(OidIsValid(oper))
+ constr_procs[i] = get_opcode(oper);
+ else
+ elog(ERROR, "cannot determine operator for type %d and "
+ "strategy %d", typoid, strategy);
+ }
+
+ InitDirtySnapshot(DirtySnapshot);
+
+ /*
+ * Now search the tuples that are actually in the index for
+ * any violations.
+ */
+
+ scankeys = palloc(index_natts * sizeof(ScanKeyData));
+
+ for (i = 0; i < index_natts; i++)
+ {
+ Datum key_datum;
+
+ key_datum = values[i];
+
+ if (exclusion_constraint[i] == InvalidStrategy)
+ continue;
+
+ ScanKeyInit(&scankeys[nkeys], i + 1, exclusion_constraint[i],
+ constr_procs[i], key_datum);
+ nkeys++;
+ }
+
+ /*
+ * We have to find all tuples, even those not visible yet.
+ */
+ existing_slot = MakeSingleTupleTableSlot(RelationGetDescr(heap));
+ index_scan = index_beginscan(heap, index, &DirtySnapshot, nkeys,
+ scankeys);
+ while((tup = index_getnext(index_scan,
+ ForwardScanDirection)) != NULL)
+ {
+ if(ItemPointerEquals(tupleid, &tup->t_data->t_ctid))
+ {
+ found_self = true;
+ continue;
+ }
+
+ ExecStoreTuple(tup, existing_slot, index_scan->xs_cbuf, false);
+
+ if (index_scan->xs_recheck)
+ {
+ bool matches;
+
+ matches = index_recheck_constraint(
+ index, existing_slot, econtext, index_exprs, values,
+ constr_procs);
+
+ if (!matches)
+ continue; /* tuple doesn't actually match, so no conflict */
+ }
+
+ /* If the in-progress inserting transaction aborts, proceed. */
+ if (TransactionIdIsValid(DirtySnapshot.xmin))
+ {
+ XactLockTableWait(DirtySnapshot.xmin);
+ if (TransactionIdDidAbort(DirtySnapshot.xmin))
+ continue;
+ }
+
+ /* If the in-progress deleting transaction commits, proceed. */
+ if (TransactionIdIsValid(DirtySnapshot.xmax))
+ {
+ XactLockTableWait(DirtySnapshot.xmax);
+ if (TransactionIdDidCommit(DirtySnapshot.xmax))
+ continue;
+ }
+
+ ereport(ERROR,
+ (errcode(ERRCODE_INTEGRITY_CONSTRAINT_VIOLATION),
+ errmsg("operator exclusion constraint violation detected: "
+ "\"%s\"", RelationGetRelationName(index)),
+ errdetail("Existing tuple \"%s\" conflicts with new tuple "
+ "\"%s\".", tuple_as_string(existing_slot),
+ tuple_as_string(new_slot))));
+ }
+
+ ExecDropSingleTupleTableSlot(existing_slot);
+
+ index_endscan(index_scan);
+
+ pfree(scankeys);
+
+ pfree(constr_procs);
+
+ Assert(found_self);
+
+ return;
+ }
+
+ static bool
+ index_recheck_constraint(Relation index, TupleTableSlot *slot,
+ ExprContext *econtext, List *index_exprs,
+ Datum *new_values, Oid *constr_procs)
+ {
+ int index_natts = index->rd_index->indnatts;
+ int2 *index_keys = index->rd_index->indkey.values;
+ ListCell *lc = list_head(index_exprs);
+ int i;
+
+ for (i = 0; i < index_natts; i++)
+ {
+ Datum old_value;
+ bool isnull;
+
+ if (index_keys[i] == 0)
+ {
+ ExprState *exprstate;
+
+ Assert(lc != NULL);
+ exprstate = (ExprState *) lfirst(lc);
+
+ old_value = ExecEvalExpr(exprstate, econtext, &isnull, NULL);
+ lc = lnext(lc);
+ }
+ else
+ {
+ old_value = slot_getattr(slot, index_keys[i], &isnull);
+
+ /*
+ * Any null should cause the constraint to pass, so this
+ * recheck should immediately return false. Note: This
+ * isn't consistent in the case where the index search
+ * does match NULLs.
+ */
+ if (isnull)
+ return false;
+ }
+
+ if (!DatumGetBool(OidFunctionCall2(constr_procs[i], old_value,
+ new_values[i])))
+ return false;
+ }
+
+ return true;
+ }
+
+ static char *
+ tuple_as_string(TupleTableSlot *slot)
+ {
+ TupleDesc tupdesc = slot->tts_tupleDescriptor;
+ StringInfoData buf;
+ int i;
+
+ initStringInfo(&buf);
+ appendStringInfoString(&buf, "(");
+
+ for (i = 0; i < tupdesc->natts; i++)
+ {
+ char *strval;
+ Datum value;
+ bool isnull;
+
+ value = slot_getattr(slot, i + 1, &isnull);
+
+ if (isnull)
+ strval = "null";
+ else
+ {
+ Oid foutoid;
+ bool typisvarlena;
+
+ getTypeOutputInfo(tupdesc->attrs[i]->atttypid, &foutoid,
+ &typisvarlena);
+ strval = DatumGetCString(OidOutputFunctionCall(foutoid, value));
+ }
+
+ if (i > 0)
+ appendStringInfoString(&buf, ", ");
+ appendStringInfoString(&buf, strval);
+ }
+
+ appendStringInfoChar(&buf, ')');
+
+ return buf.data;
+ }
*** src/backend/nodes/copyfuncs.c
--- src/backend/nodes/copyfuncs.c
***************
*** 2075,2080 **** _copyConstraint(Constraint *from)
--- 2075,2083 ----
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);
*** src/backend/nodes/equalfuncs.c
--- src/backend/nodes/equalfuncs.c
***************
*** 2082,2087 **** _equalConstraint(Constraint *a, Constraint *b)
--- 2082,2090 ----
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);
*** src/backend/nodes/outfuncs.c
--- src/backend/nodes/outfuncs.c
***************
*** 2340,2345 **** _outConstraint(StringInfo str, Constraint *node)
--- 2340,2353 ----
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;
*** src/backend/parser/gram.y
--- src/backend/parser/gram.y
***************
*** 427,432 **** static TypeName *TableFuncTypeName(List *columns);
--- 427,433 ----
%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.
***************
*** 470,476 **** 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
--- 471,477 ----
DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP
EACH ELSE ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ESCAPE EXCEPT
! EXCLUDING EXCLUSION EXCLUSIVE EXECUTE EXISTS EXPLAIN EXTERNAL EXTRACT
FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD
FREEZE FROM FULL FUNCTION
***************
*** 2480,2485 **** ConstraintElem:
--- 2481,2501 ----
n->initdeferred = ($11 & 2) != 0;
$$ = (Node *)n;
}
+ | EXCLUSION access_method_clause '(' ExclusionConstraintList ')'
+ opt_definition OptConsTableSpace ConstraintAttributeSpec
+ where_clause
+ {
+ Constraint *n = makeNode(Constraint);
+ n->contype = CONSTR_OPERATOR_EXCLUSION;
+ n->using_method = $2;
+ n->operator_exclusion = $4;
+ n->options = $6;
+ n->indexspace = $7;
+ n->deferrable = ($8 & 1) != 0;
+ n->initdeferred = ($8 & 2) != 0;
+ n->where_clause = $9;
+ $$ = (Node *)n;
+ }
;
opt_column_list:
***************
*** 2520,2525 **** key_match: MATCH FULL
--- 2536,2552 ----
}
;
+ ExclusionConstraintList:
+ ExclusionConstraintElem { $$ = list_make1($1); }
+ | ExclusionConstraintList ',' ExclusionConstraintElem
+ { $$ = lappend($1, $3); }
+ ;
+
+ ExclusionConstraintElem: index_elem CHECK WITH any_operator
+ {
+ $$ = list_make2($1, $4);
+ }
+ ;
/*
* We combine the update and delete actions into one value temporarily
* for simplicity of parsing, and then break them down again in the
*** src/backend/parser/parse_utilcmd.c
--- src/backend/parser/parse_utilcmd.c
***************
*** 69,75 **** typedef struct
List *columns; /* ColumnDef items */
List *ckconstraints; /* CHECK constraints */
List *fkconstraints; /* FOREIGN KEY constraints */
! List *ixconstraints; /* index-creating constraints */
List *inh_indexes; /* cloned indexes from INCLUDING INDEXES */
List *blist; /* "before list" of things to do before
* creating the table */
--- 69,76 ----
List *columns; /* ColumnDef items */
List *ckconstraints; /* CHECK constraints */
List *fkconstraints; /* FOREIGN KEY constraints */
! List *idxconstraints; /* index-creating constraints */
! List *opxconstraints; /* operator exclusion constraints */
List *inh_indexes; /* cloned indexes from INCLUDING INDEXES */
List *blist; /* "before list" of things to do before
* creating the table */
***************
*** 172,178 **** transformCreateStmt(CreateStmt *stmt, const char *queryString)
cxt.columns = NIL;
cxt.ckconstraints = NIL;
cxt.fkconstraints = NIL;
! cxt.ixconstraints = NIL;
cxt.inh_indexes = NIL;
cxt.blist = NIL;
cxt.alist = NIL;
--- 173,180 ----
cxt.columns = NIL;
cxt.ckconstraints = NIL;
cxt.fkconstraints = NIL;
! cxt.idxconstraints = NIL;
! cxt.opxconstraints = NIL;
cxt.inh_indexes = NIL;
cxt.blist = NIL;
cxt.alist = NIL;
***************
*** 458,464 **** transformColumnDefinition(ParseState *pstate, CreateStmtContext *cxt,
case CONSTR_UNIQUE:
if (constraint->keys == NIL)
constraint->keys = list_make1(makeString(column->colname));
! cxt->ixconstraints = lappend(cxt->ixconstraints, constraint);
break;
case CONSTR_CHECK:
--- 460,466 ----
case CONSTR_UNIQUE:
if (constraint->keys == NIL)
constraint->keys = list_make1(makeString(column->colname));
! cxt->idxconstraints = lappend(cxt->idxconstraints, constraint);
break;
case CONSTR_CHECK:
***************
*** 497,507 **** static void
transformTableConstraint(ParseState *pstate, CreateStmtContext *cxt,
Constraint *constraint)
{
switch (constraint->contype)
{
case CONSTR_PRIMARY:
case CONSTR_UNIQUE:
! cxt->ixconstraints = lappend(cxt->ixconstraints, constraint);
break;
case CONSTR_CHECK:
--- 499,511 ----
transformTableConstraint(ParseState *pstate, CreateStmtContext *cxt,
Constraint *constraint)
{
+ ListCell *l;
+
switch (constraint->contype)
{
case CONSTR_PRIMARY:
case CONSTR_UNIQUE:
! cxt->idxconstraints = lappend(cxt->idxconstraints, constraint);
break;
case CONSTR_CHECK:
***************
*** 512,517 **** transformTableConstraint(ParseState *pstate, CreateStmtContext *cxt,
--- 516,557 ----
cxt->fkconstraints = lappend(cxt->fkconstraints, constraint);
break;
+ case CONSTR_OPERATOR_EXCLUSION:
+ /* preprocess index expressions and predicate */
+ foreach(l, constraint->operator_exclusion)
+ {
+ List *pair = lfirst(l);
+ 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")
+ ));
+ }
+ }
+ if (constraint->where_clause)
+ constraint->where_clause = transformWhereClause(
+ pstate, constraint->where_clause, "WHERE");
+ cxt->opxconstraints = lappend(cxt->opxconstraints, constraint);
+ break;
+
case CONSTR_NULL:
case CONSTR_NOTNULL:
case CONSTR_DEFAULT:
***************
*** 1000,1006 **** transformIndexConstraints(ParseState *pstate, CreateStmtContext *cxt)
* KEY, mark each column as NOT NULL and create an index. For UNIQUE,
* create an index as for PRIMARY KEY, but do not insist on NOT NULL.
*/
! foreach(lc, cxt->ixconstraints)
{
Constraint *constraint = (Constraint *) lfirst(lc);
--- 1040,1046 ----
* KEY, mark each column as NOT NULL and create an index. For UNIQUE,
* create an index as for PRIMARY KEY, but do not insist on NOT NULL.
*/
! foreach(lc, cxt->idxconstraints)
{
Constraint *constraint = (Constraint *) lfirst(lc);
***************
*** 1738,1744 **** transformAlterTableStmt(AlterTableStmt *stmt, const char *queryString)
cxt.columns = NIL;
cxt.ckconstraints = NIL;
cxt.fkconstraints = NIL;
! cxt.ixconstraints = NIL;
cxt.inh_indexes = NIL;
cxt.blist = NIL;
cxt.alist = NIL;
--- 1778,1785 ----
cxt.columns = NIL;
cxt.ckconstraints = NIL;
cxt.fkconstraints = NIL;
! cxt.idxconstraints = NIL;
! cxt.opxconstraints = NIL;
cxt.inh_indexes = NIL;
cxt.blist = NIL;
cxt.alist = NIL;
***************
*** 1752,1757 **** transformAlterTableStmt(AlterTableStmt *stmt, const char *queryString)
--- 1793,1799 ----
foreach(lcmd, stmt->cmds)
{
AlterTableCmd *cmd = (AlterTableCmd *) lfirst(lcmd);
+ RangeTblEntry *rte;
switch (cmd->subtype)
{
***************
*** 1784,1789 **** transformAlterTableStmt(AlterTableStmt *stmt, const char *queryString)
--- 1826,1841 ----
/*
* The original AddConstraint cmd node doesn't go to newcmds
*/
+
+ /*
+ * Put the parent table into the rtable so that the
+ * expressions can refer to its fields without
+ * qualification.
+ */
+ rte = addRangeTableEntry(pstate, stmt->relation, NULL, false,
+ true);
+ addRTEtoQuery(pstate, rte, false, true, true);
+
if (IsA(cmd->def, Constraint))
{
transformTableConstraint(pstate, &cxt,
***************
*** 1844,1850 **** 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);
--- 1896,1905 ----
}
cxt.alist = NIL;
! /*
! * Append any CHECK, FK or operator exclusion constraints to the
! * commands list
! */
foreach(l, cxt.ckconstraints)
{
newcmd = makeNode(AlterTableCmd);
***************
*** 1859,1864 **** transformAlterTableStmt(AlterTableStmt *stmt, const char *queryString)
--- 1914,1926 ----
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);
*** src/backend/storage/ipc/ipci.c
--- src/backend/storage/ipc/ipci.c
***************
*** 15,20 ****
--- 15,21 ----
#include "postgres.h"
#include "access/clog.h"
+ #include "access/genam.h"
#include "access/heapam.h"
#include "access/multixact.h"
#include "access/nbtree.h"
*** src/backend/tcop/utility.c
--- src/backend/tcop/utility.c
***************
*** 792,797 **** ProcessUtility(Node *parsetree,
--- 792,798 ----
stmt->indexParams, /* parameters */
(Expr *) stmt->whereClause,
stmt->options,
+ NULL,
stmt->unique,
stmt->primary,
stmt->isconstraint,
*** src/backend/utils/adt/ruleutils.c
--- src/backend/utils/adt/ruleutils.c
***************
*** 141,146 **** static char *deparse_expression_pretty(Node *expr, List *dpcontext,
--- 141,148 ----
static char *pg_get_viewdef_worker(Oid viewoid, int prettyFlags);
static void decompile_column_index_array(Datum column_index_array, Oid relId,
StringInfo buf);
+ static void decompile_column_strategy_array(Datum column_strategy_array,
+ Oid indexOid, StringInfo buf);
static char *pg_get_ruledef_worker(Oid ruleoid, int prettyFlags);
static char *pg_get_indexdef_worker(Oid indexrelid, int colno,
bool attrsOnly, bool showTblSpc,
***************
*** 1153,1158 **** pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
--- 1155,1181 ----
break;
}
+ case CONSTRAINT_OPX:
+ {
+ Datum val;
+ bool isnull;
+ Oid indexOid = conForm->conindid;
+
+ /* Fetch constraint expression in parsetree form */
+ val = SysCacheGetAttr(CONSTROID, tup,
+ Anum_pg_constraint_constrategies,
+ &isnull);
+ if (isnull)
+ elog(ERROR, "null conbin for constraint %u",
+ constraintId);
+
+ appendStringInfo(&buf, "(");
+ decompile_column_strategy_array(val, indexOid, &buf);
+ appendStringInfo(&buf, ") USING INDEX %s",
+ quote_identifier(get_rel_name(indexOid)));
+
+ break;
+ }
default:
elog(ERROR, "invalid constraint type \"%c\"", conForm->contype);
break;
***************
*** 1200,1205 **** decompile_column_index_array(Datum column_index_array, Oid relId,
--- 1223,1276 ----
}
}
+ /*
+ * Convert an int16[] Datum into a comma-separated list of column
+ * names and operators for the indicated index; append the list to
+ * buf.
+ */
+ static void
+ decompile_column_strategy_array(Datum column_strategy_array, Oid indexOid,
+ StringInfo buf)
+ {
+ Datum *keys;
+ int nKeys;
+ int j;
+ Relation indexRelation;
+
+ /* Extract data from array of int16 */
+ deconstruct_array(DatumGetArrayTypeP(column_strategy_array),
+ INT2OID, 2, true, 's',
+ &keys, NULL, &nKeys);
+
+ indexRelation = relation_open(indexOid, AccessShareLock);
+
+ for (j = 0; j < nKeys; j++)
+ {
+ Oid opid;
+ char *opName;
+ Oid opfamily = indexRelation->rd_opfamily[j];
+ char *colName = get_relid_attribute_name(indexOid, j + 1);
+ Oid colType = indexRelation->rd_opcintype[j];
+ int16 strategy = DatumGetInt16(keys[j]);
+
+ opid = get_opfamily_member(opfamily, colType, colType, strategy);
+ opName = get_opname(opid);
+
+ if (colName == NULL || opName == NULL)
+ elog(ERROR, "unexpected error: cannot determine column and "
+ "operator names");
+
+ if (j == 0)
+ appendStringInfo(buf, "%s %s", quote_identifier(colName),
+ quote_identifier(opName));
+ else
+ appendStringInfo(buf, ", %s %s", quote_identifier(colName),
+ quote_identifier(opName));
+ }
+
+ relation_close(indexRelation, NoLock);
+ }
+
/* ----------
* get_expr - Decompile an expression tree
*** src/backend/utils/cache/relcache.c
--- 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"
***************
*** 3029,3034 **** CheckConstraintFetch(Relation relation)
--- 3030,3106 ----
}
/*
+ * 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;
+
+ ScanKeyInit(&skey[0],
+ Anum_pg_constraint_conindid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(indexRelation)));
+
+ conrel = heap_open(ConstraintRelationId, AccessShareLock);
+ conscan = systable_beginscan(conrel, ConstraintIndidIndexId, 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 (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
*** src/bin/psql/describe.c
--- src/bin/psql/describe.c
***************
*** 1033,1038 **** describeOneTableDetails(const char *schemaname,
--- 1033,1039 ----
struct
{
int16 checks;
+ int16 opxconstraints;
char relkind;
bool hasindex;
bool hasrules;
***************
*** 1054,1060 **** 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, "
--- 1055,1076 ----
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, "
***************
*** 1122,1127 **** describeOneTableDetails(const char *schemaname,
--- 1138,1145 ----
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;
***************
*** 1575,1580 **** describeOneTableDetails(const char *schemaname,
--- 1593,1631 ----
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)
{
*** src/include/access/genam.h
--- src/include/access/genam.h
***************
*** 16,21 ****
--- 16,23 ----
#include "access/sdir.h"
#include "access/skey.h"
+ #include "access/xact.h"
+ #include "executor/tuptable.h"
#include "nodes/tidbitmap.h"
#include "storage/buf.h"
#include "storage/lock.h"
*** src/include/catalog/indexing.h
--- src/include/catalog/indexing.h
***************
*** 113,118 **** DECLARE_INDEX(pg_constraint_conname_nsp_index, 2664, on pg_constraint using btre
--- 113,120 ----
/* This following index is not used for a cache and is not unique */
DECLARE_INDEX(pg_constraint_conrelid_index, 2665, on pg_constraint using btree(conrelid oid_ops));
#define ConstraintRelidIndexId 2665
+ DECLARE_INDEX(pg_constraint_conindid_index, 2730, on pg_constraint using btree(conindid oid_ops));
+ #define ConstraintIndidIndexId 2730
/* This following index is not used for a cache and is not unique */
DECLARE_INDEX(pg_constraint_contypid_index, 2666, on pg_constraint using btree(contypid oid_ops));
#define ConstraintTypidIndexId 2666
*** src/include/catalog/pg_attribute.h
--- src/include/catalog/pg_attribute.h
***************
*** 423,436 **** 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_));
--- 423,437 ----
{ 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_));
***************
*** 449,462 **** 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_));
--- 450,464 ----
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_));
*** src/include/catalog/pg_class.h
--- src/include/catalog/pg_class.h
***************
*** 53,58 **** CATALOG(pg_class,1259) BKI_BOOTSTRAP
--- 53,59 ----
* 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 */
***************
*** 86,92 **** 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
--- 87,93 ----
* ----------------
*/
! #define Natts_pg_class 26
#define Anum_pg_class_relname 1
#define Anum_pg_class_relnamespace 2
#define Anum_pg_class_reltype 3
***************
*** 104,117 **** 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
--- 105,119 ----
#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
***************
*** 123,135 **** 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 */
--- 125,137 ----
*/
/* 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 */
*** src/include/catalog/pg_constraint.h
--- 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,
*** src/include/commands/defrem.h
--- 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);
*** src/include/nodes/execnodes.h
--- 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;
*** src/include/nodes/parsenodes.h
--- src/include/nodes/parsenodes.h
***************
*** 1377,1382 **** typedef enum ConstrType /* types of constraints */
--- 1377,1383 ----
CONSTR_CHECK,
CONSTR_PRIMARY,
CONSTR_UNIQUE,
+ CONSTR_OPERATOR_EXCLUSION,
CONSTR_FOREIGN,
CONSTR_ATTR_DEFERRABLE, /* attributes for previous constraint node */
CONSTR_ATTR_NOT_DEFERRABLE,
***************
*** 1411,1421 **** 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 */
--- 1412,1427 ----
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 */
*** src/include/parser/kwlist.h
--- src/include/parser/kwlist.h
***************
*** 143,148 **** PG_KEYWORD("enum", ENUM_P, UNRESERVED_KEYWORD)
--- 143,149 ----
PG_KEYWORD("escape", ESCAPE, UNRESERVED_KEYWORD)
PG_KEYWORD("except", EXCEPT, RESERVED_KEYWORD)
PG_KEYWORD("excluding", EXCLUDING, UNRESERVED_KEYWORD)
+ PG_KEYWORD("exclusion", EXCLUSION, UNRESERVED_KEYWORD)
PG_KEYWORD("exclusive", EXCLUSIVE, UNRESERVED_KEYWORD)
PG_KEYWORD("execute", EXECUTE, UNRESERVED_KEYWORD)
PG_KEYWORD("exists", EXISTS, COL_NAME_KEYWORD)
*** src/include/storage/lwlock.h
--- src/include/storage/lwlock.h
***************
*** 67,72 **** typedef enum LWLockId
--- 67,73 ----
AutovacuumLock,
AutovacuumScheduleLock,
SyncScanLock,
+ IndexConstraintLock,
/* Individual lock IDs end here */
FirstBufMappingLock,
FirstLockMgrLock = FirstBufMappingLock + NUM_BUFFER_PARTITIONS,
*** src/include/utils/relcache.h
--- src/include/utils/relcache.h
***************
*** 15,20 ****
--- 15,21 ----
#define RELCACHE_H
#include "access/tupdesc.h"
+ #include "access/skey.h"
#include "nodes/bitmapset.h"
#include "nodes/pg_list.h"
***************
*** 43,48 **** extern Oid RelationGetOidIndex(Relation relation);
--- 44,50 ----
extern List *RelationGetIndexExpressions(Relation relation);
extern List *RelationGetIndexPredicate(Relation relation);
extern Bitmapset *RelationGetIndexAttrBitmap(Relation relation);
+ extern int16 *RelationGetOpExclusionConstraints(Relation indexRelation);
extern void RelationSetIndexList(Relation relation,
List *indexIds, Oid oidIndex);
*** src/test/regress/input/constraints.source
--- src/test/regress/input/constraints.source
***************
*** 366,368 **** COMMIT;
--- 366,392 ----
SELECT * FROM unique_tbl;
DROP TABLE unique_tbl;
+
+ CREATE TABLE circles (c1 CIRCLE, c2 TEXT);
+
+ ALTER TABLE circles
+ ADD EXCLUSION USING gist
+ (c1 CHECK WITH &&, (c2::circle) CHECK WITH ~=)
+ WHERE circle_center(c1) <> '(0,0)';
+
+ -- these should succeed because they don't match the index predicate
+ INSERT INTO circles VALUES('<(0,0), 5>', '<(0,0), 5>');
+ INSERT INTO circles VALUES('<(0,0), 5>', '<(0,0), 5>');
+
+ -- succeed
+ INSERT INTO circles VALUES('<(10,10), 10>', '<(0,0), 5>');
+ -- should fail
+ INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 5>');
+ -- succeed because c1 doesn't overlap
+ INSERT INTO circles VALUES('<(20,20), 1>', '<(0,0), 5>');
+ -- succeed because c2 is not the same
+ INSERT INTO circles VALUES('<(20,20), 10>', '<(1,1), 5>');
+
+ DROP TABLE circles;
+
+
*** src/test/regress/output/constraints.source
--- src/test/regress/output/constraints.source
***************
*** 267,273 **** SELECT * FROM INSERT_TBL;
CREATE TABLE COPY_TBL (x INT, y TEXT, z INT,
CONSTRAINT COPY_CON
CHECK (x > 3 AND y <> 'check failed' AND x < 7 ));
! COPY COPY_TBL FROM '@abs_srcdir@/data/constro.data';
SELECT '' AS two, * FROM COPY_TBL;
two | x | y | z
-----+---+---------------+---
--- 267,273 ----
CREATE TABLE COPY_TBL (x INT, y TEXT, z INT,
CONSTRAINT COPY_CON
CHECK (x > 3 AND y <> 'check failed' AND x < 7 ));
! COPY COPY_TBL FROM '/home/jdavis/wd/git/postgresql/src/test/regress/data/constro.data';
SELECT '' AS two, * FROM COPY_TBL;
two | x | y | z
-----+---+---------------+---
***************
*** 275,281 **** SELECT '' AS two, * FROM COPY_TBL;
| 6 | OK | 4
(2 rows)
! COPY COPY_TBL FROM '@abs_srcdir@/data/constrf.data';
ERROR: new row for relation "copy_tbl" violates check constraint "copy_con"
CONTEXT: COPY copy_tbl, line 2: "7 check failed 6"
SELECT * FROM COPY_TBL;
--- 275,281 ----
| 6 | OK | 4
(2 rows)
! COPY COPY_TBL FROM '/home/jdavis/wd/git/postgresql/src/test/regress/data/constrf.data';
ERROR: new row for relation "copy_tbl" violates check constraint "copy_con"
CONTEXT: COPY copy_tbl, line 2: "7 check failed 6"
SELECT * FROM COPY_TBL;
***************
*** 512,514 **** SELECT * FROM unique_tbl;
--- 512,534 ----
(5 rows)
DROP TABLE unique_tbl;
+ CREATE TABLE circles (c1 CIRCLE, c2 TEXT);
+ ALTER TABLE circles
+ ADD EXCLUSION USING gist
+ (c1 CHECK WITH &&, (c2::circle) CHECK WITH ~=)
+ WHERE circle_center(c1) <> '(0,0)';
+ NOTICE: ALTER TABLE / ADD EXCLUSION will create implicit index "circles_c1_exclusion" for table "circles"
+ -- these should succeed because they don't match the index predicate
+ INSERT INTO circles VALUES('<(0,0), 5>', '<(0,0), 5>');
+ INSERT INTO circles VALUES('<(0,0), 5>', '<(0,0), 5>');
+ -- succeed
+ INSERT INTO circles VALUES('<(10,10), 10>', '<(0,0), 5>');
+ -- should fail
+ INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 5>');
+ ERROR: operator exclusion constraint violation detected: "circles_c1_exclusion"
+ DETAIL: Existing tuple "(<(10,10),10>, <(0,0), 5>)" conflicts with new tuple "(<(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>');
+ DROP TABLE circles;