From 3a47c7c6d031774bda136097fe0eaf278133cbe9 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Mon, 28 Jun 2021 17:33:27 -0700 Subject: [PATCH v9 2/4] Add temporal PRIMARY KEY and UNIQUE constraints - Added WITHOUT OVERLAPS to the bison grammar. We permit either range columns or PERIODs. - Temporal PRIMARY KEYs and UNIQUE constraints are backed by GiST indexes instead of B-tree indexes, since they are essentially exclusion constraints with = for the scalar parts of the key and && for the temporal part. With PERIODs we index a range expression using the PERIOD start and end columns. With ranges we can just index the column, so no expression is needed. - Added pg_index.indperiod to record when a PERIOD is used in an index constraint. - Added pg_constraint.contemporal to say whether a constraint is a temporal constraint. - Added pg_constraint.conperiod to record the PERIOD oid if a PERIOD is used. For range columns we can just include the column attnum, like any other column appearing in a constraint. - Added docs and tests. - Added pg_dump support. --- doc/src/sgml/ddl.sgml | 6 + doc/src/sgml/ref/create_table.sgml | 39 ++- src/backend/catalog/Catalog.pm | 1 + src/backend/catalog/heap.c | 2 + src/backend/catalog/index.c | 57 +++- src/backend/catalog/pg_constraint.c | 15 +- src/backend/catalog/toasting.c | 1 + src/backend/commands/indexcmds.c | 41 ++- src/backend/commands/tablecmds.c | 17 +- src/backend/commands/trigger.c | 2 + src/backend/commands/typecmds.c | 2 + src/backend/nodes/copyfuncs.c | 2 + src/backend/nodes/makefuncs.c | 4 +- src/backend/parser/gram.y | 32 +- src/backend/parser/parse_utilcmd.c | 228 +++++++++++++- src/backend/utils/adt/ruleutils.c | 55 +++- src/backend/utils/cache/lsyscache.c | 26 ++ src/backend/utils/cache/relcache.c | 22 +- src/bin/pg_dump/pg_dump.c | 39 ++- src/bin/pg_dump/pg_dump.h | 1 + src/bin/pg_dump/t/002_pg_dump.pl | 22 ++ src/bin/psql/describe.c | 2 + src/include/catalog/index.h | 1 + src/include/catalog/pg_constraint.h | 12 +- src/include/commands/defrem.h | 3 +- src/include/nodes/execnodes.h | 4 + src/include/nodes/makefuncs.h | 3 +- src/include/nodes/parsenodes.h | 4 + src/include/utils/lsyscache.h | 1 + src/test/regress/expected/sanity_check.out | 2 + .../regress/expected/without_overlaps.out | 286 +++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/without_overlaps.sql | 295 ++++++++++++++++++ 33 files changed, 1169 insertions(+), 60 deletions(-) create mode 100644 src/test/regress/expected/without_overlaps.out create mode 100644 src/test/regress/sql/without_overlaps.sql diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 1b8f524191..8bc60eb843 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1182,6 +1182,12 @@ CREATE TABLE billing_addresses ( Application periods can be used to define temporal primary and foreign keys. Any table with a temporal primary key is a temporal table and supports temporal update and delete commands. + + + Temporal primary keys enforce a modified version of referential integrity called temporal referential integrity. They have two kinds of element: first one or more columns that behave as in ordinary primary keys, uniquely determiniing an entity, + and second a period (or range column) that qualifies when the row applies. So a temporal primary permits multiple rows with equal values in the ordinary key parts, as long as those rows don't have overlapping periods. Each row makes a statement about the entity identified by the ordinary key parts, but applying only to the span given by the period. + Temporal primary keys are essentially exclusion constraints where the first key parts are compared for equality and the last part for overlaps. + diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 7d3ef82f6a..fcf15013a7 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -85,8 +85,8 @@ PERIOD FOR { period_name | SYSTEM_T [ CONSTRAINT constraint_name ] { CHECK ( expression ) [ NO INHERIT ] | - UNIQUE ( column_name [, ... ] ) index_parameters | - PRIMARY KEY ( column_name [, ... ] ) index_parameters | + UNIQUE ( column_name [, ... ] [, temporal_interval WITHOUT OVERLAPS ] ) index_parameters | + PRIMARY KEY ( column_name [, ... ] [, temporal_interval WITHOUT OVERLAPS ] ) index_parameters | EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE numeric_literal, REM { column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] +temporal_interval in a PRIMARY KEY, UNIQUE, or FOREIGN KEY constraint is: + +range_column_name | period_name + referential_action in a FOREIGN KEY/REFERENCES constraint is: { NO ACTION | RESTRICT | CASCADE | SET NULL | SET DEFAULT } + @@ -992,7 +997,10 @@ WITH ( MODULUS numeric_literal, REM Adding a unique constraint will automatically create a unique btree - index on the column or group of columns used in the constraint. + index on the column or group of columns used in the constraint. But if + the constraint includes a WITHOUT OVERLAPS clause, + it will use a GiST index and behave like a temporal PRIMARY + KEY: preventing duplicates only in overlapping time periods. @@ -1010,7 +1018,8 @@ WITH ( MODULUS numeric_literal, REM PRIMARY KEY (column constraint) - PRIMARY KEY ( column_name [, ... ] ) + PRIMARY KEY ( column_name [, ... ] + [, temporal_interval WITHOUT OVERLAPS ] ) INCLUDE ( column_name [, ...]) (table constraint) @@ -1044,8 +1053,8 @@ WITH ( MODULUS numeric_literal, REM Adding a PRIMARY KEY constraint will automatically - create a unique btree index on the column or group of columns used in the - constraint. + create a unique btree index (or GiST if temporal) on the column or group of + columns used in the constraint. @@ -1058,6 +1067,24 @@ WITH ( MODULUS numeric_literal, REM (e.g., DROP COLUMN) can cause cascaded constraint and index deletion. + + + A PRIMARY KEY with a WITHOUT OVERLAPS option + is a temporal primary key. + The WITHOUT OVERLAPS value + must be a period or range type and is used to constrain the record's applicability + to just that interval (usually a range of dates or timestamps). + The main part of the primary key may be repeated in other rows of the table, + as long as records with the same key don't overlap in the + WITHOUT OVERLAPS column. + + + + A temporal PRIMARY KEY is enforced with an + EXCLUDE constraint rather than a UNIQUE + constraint, backed by a GiST index. You may need to install the + extension to create temporal primary keys. + diff --git a/src/backend/catalog/Catalog.pm b/src/backend/catalog/Catalog.pm index 6eef0bc680..992eb4b415 100644 --- a/src/backend/catalog/Catalog.pm +++ b/src/backend/catalog/Catalog.pm @@ -266,6 +266,7 @@ sub ParseData # Scan the input file. while (<$ifd>) { + next if /^#/; my $hash_ref; if (/{/) diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 49cb627f5e..d8b99ade5c 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -2558,6 +2558,8 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr, is_local, /* conislocal */ inhcount, /* coninhcount */ is_no_inherit, /* connoinherit */ + false, /* contemporal */ + InvalidOid, /* conperiod */ is_internal); /* internally constructed? */ pfree(ccbin); diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index c255806e38..68c5700f6c 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -50,6 +50,7 @@ #include "catalog/pg_inherits.h" #include "catalog/pg_opclass.h" #include "catalog/pg_operator.h" +#include "catalog/pg_period.h" #include "catalog/pg_tablespace.h" #include "catalog/pg_trigger.h" #include "catalog/pg_type.h" @@ -234,13 +235,16 @@ index_check_primary_key(Relation heapRel, HeapTuple atttuple; Form_pg_attribute attform; - if (attnum == 0) + if (attnum == 0 && !(stmt->istemporal && i > 0)) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("primary keys cannot be expressions"))); - /* System attributes are never null, so no need to check */ - if (attnum < 0) + /* + * System attributes are never null, so no need to check. + * Also skip expressions. + */ + if (attnum <= 0) continue; atttuple = SearchSysCache2(ATTNUM, @@ -546,6 +550,7 @@ UpdateIndexRelation(Oid indexoid, bool isready) { int2vector *indkey; + Oid indperiod; oidvector *indcollation; oidvector *indclass; int2vector *indoption; @@ -564,6 +569,7 @@ UpdateIndexRelation(Oid indexoid, indkey = buildint2vector(NULL, indexInfo->ii_NumIndexAttrs); for (i = 0; i < indexInfo->ii_NumIndexAttrs; i++) indkey->values[i] = indexInfo->ii_IndexAttrNumbers[i]; + indperiod = indexInfo->ii_Period ? ((Period *) indexInfo->ii_Period)->oid : InvalidOid; indcollation = buildoidvector(collationOids, indexInfo->ii_NumIndexKeyAttrs); indclass = buildoidvector(classOids, indexInfo->ii_NumIndexKeyAttrs); indoption = buildint2vector(coloptions, indexInfo->ii_NumIndexKeyAttrs); @@ -623,6 +629,7 @@ UpdateIndexRelation(Oid indexoid, values[Anum_pg_index_indislive - 1] = BoolGetDatum(true); values[Anum_pg_index_indisreplident - 1] = BoolGetDatum(false); values[Anum_pg_index_indkey - 1] = PointerGetDatum(indkey); + values[Anum_pg_index_indperiod - 1] = ObjectIdGetDatum(indperiod); values[Anum_pg_index_indcollation - 1] = PointerGetDatum(indcollation); values[Anum_pg_index_indclass - 1] = PointerGetDatum(indclass); values[Anum_pg_index_indoption - 1] = PointerGetDatum(indoption); @@ -1263,6 +1270,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, Datum indclassDatum, colOptionDatum, optionDatum; + Oid periodid; oidvector *indclass; int2vector *indcoloptions; bool isnull; @@ -1298,6 +1306,9 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, Assert(!isnull); indcoloptions = (int2vector *) DatumGetPointer(colOptionDatum); + /* Get the period */ + periodid = oldInfo->ii_Period ? ((Period *) oldInfo->ii_Period)->oid : InvalidOid; + /* Fetch options of index if any */ classTuple = SearchSysCache1(RELOID, oldIndexId); if (!HeapTupleIsValid(classTuple)) @@ -1349,6 +1360,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, indexExprs, indexPreds, oldInfo->ii_Unique, + oldInfo->ii_Temporal, false, /* not ready for inserts */ true); @@ -1375,6 +1387,16 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, newInfo->ii_OpclassOptions[i] = get_attoptions(oldIndexId, i + 1); } + /* Set the period */ + if (periodid == InvalidOid) + newInfo->ii_Period = NULL; + else + { + Period *p = makeNode(Period); + p->oid = periodid; + newInfo->ii_Period = (Node *) p; + } + /* * Now create the new index. * @@ -1874,6 +1896,7 @@ index_concurrently_set_dead(Oid heapId, Oid indexId) * INDEX_CONSTR_CREATE_UPDATE_INDEX: update the pg_index row * INDEX_CONSTR_CREATE_REMOVE_OLD_DEPS: remove existing dependencies * of index on table's columns + * INDEX_CONSTR_CREATE_TEMPORAL: constraint is for a temporal primary key * allow_system_table_mods: allow table to be a system catalog * is_internal: index is constructed due to internal process */ @@ -1892,16 +1915,19 @@ index_constraint_create(Relation heapRelation, ObjectAddress myself, idxaddr; Oid conOid; + Oid periodid; bool deferrable; bool initdeferred; bool mark_as_primary; bool islocal; bool noinherit; + bool is_temporal; int inhcount; deferrable = (constr_flags & INDEX_CONSTR_CREATE_DEFERRABLE) != 0; initdeferred = (constr_flags & INDEX_CONSTR_CREATE_INIT_DEFERRED) != 0; mark_as_primary = (constr_flags & INDEX_CONSTR_CREATE_MARK_AS_PRIMARY) != 0; + is_temporal = (constr_flags & INDEX_CONSTR_CREATE_TEMPORAL) != 0; /* constraint creation support doesn't work while bootstrapping */ Assert(!IsBootstrapProcessingMode()); @@ -1916,7 +1942,8 @@ index_constraint_create(Relation heapRelation, /* primary/unique constraints shouldn't have any expressions */ if (indexInfo->ii_Expressions && - constraintType != CONSTRAINT_EXCLUSION) + constraintType != CONSTRAINT_EXCLUSION && + !indexInfo->ii_Temporal) elog(ERROR, "constraints cannot have index expressions"); /* @@ -1945,6 +1972,11 @@ index_constraint_create(Relation heapRelation, noinherit = true; } + if (indexInfo->ii_Period != NULL) + periodid = ((Period *)indexInfo->ii_Period)->oid; + else + periodid = InvalidOid; + /* * Construct a pg_constraint entry. */ @@ -1976,6 +2008,8 @@ index_constraint_create(Relation heapRelation, islocal, inhcount, noinherit, + is_temporal, /* contemporal */ + periodid, /* conperiod */ is_internal); /* @@ -2408,6 +2442,7 @@ BuildIndexInfo(Relation index) RelationGetIndexExpressions(index), RelationGetIndexPredicate(index), indexStruct->indisunique, + false, indexStruct->indisready, false); @@ -2415,6 +2450,16 @@ BuildIndexInfo(Relation index) for (i = 0; i < numAtts; i++) ii->ii_IndexAttrNumbers[i] = indexStruct->indkey.values[i]; + /* set the period */ + if (indexStruct->indperiod == InvalidOid) + ii->ii_Period = NULL; + else + { + Period *p = makeNode(Period); + p->oid = indexStruct->indperiod; + ii->ii_Period = (Node *) p; + } + /* fetch exclusion constraint info if any */ if (indexStruct->indisexclusion) { @@ -2467,6 +2512,7 @@ BuildDummyIndexInfo(Relation index) RelationGetDummyIndexExpressions(index), NIL, indexStruct->indisunique, + false, indexStruct->indisready, false); @@ -2474,6 +2520,9 @@ BuildDummyIndexInfo(Relation index) for (i = 0; i < numAtts; i++) ii->ii_IndexAttrNumbers[i] = indexStruct->indkey.values[i]; + /* no need for a period */ + ii->ii_Period = NULL; + /* We ignore the exclusion constraint if any */ return ii; diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c index a4e890020f..7927fbde88 100644 --- a/src/backend/catalog/pg_constraint.c +++ b/src/backend/catalog/pg_constraint.c @@ -25,6 +25,7 @@ #include "catalog/objectaccess.h" #include "catalog/pg_constraint.h" #include "catalog/pg_operator.h" +#include "catalog/pg_period.h" #include "catalog/pg_type.h" #include "commands/defrem.h" #include "commands/tablecmds.h" @@ -75,6 +76,8 @@ CreateConstraintEntry(const char *constraintName, bool conIsLocal, int conInhCount, bool conNoInherit, + bool conTemporal, + Oid period, bool is_internal) { Relation conDesc; @@ -185,6 +188,8 @@ CreateConstraintEntry(const char *constraintName, values[Anum_pg_constraint_conislocal - 1] = BoolGetDatum(conIsLocal); values[Anum_pg_constraint_coninhcount - 1] = Int32GetDatum(conInhCount); values[Anum_pg_constraint_connoinherit - 1] = BoolGetDatum(conNoInherit); + values[Anum_pg_constraint_contemporal - 1] = BoolGetDatum(conTemporal); + values[Anum_pg_constraint_conperiod - 1] = ObjectIdGetDatum(period); if (conkeyArray) values[Anum_pg_constraint_conkey - 1] = PointerGetDatum(conkeyArray); @@ -236,7 +241,7 @@ CreateConstraintEntry(const char *constraintName, { /* * Register auto dependency from constraint to owning relation, or to - * specific column(s) if any are mentioned. + * specific column(s) and period if any are mentioned. */ ObjectAddress relobject; @@ -254,6 +259,14 @@ CreateConstraintEntry(const char *constraintName, ObjectAddressSet(relobject, RelationRelationId, relId); add_exact_object_address(&relobject, addrs_auto); } + + if (OidIsValid(period)) + { + ObjectAddress periodobject; + + ObjectAddressSet(periodobject, PeriodRelationId, period); + add_exact_object_address(&periodobject, addrs_auto); + } } if (OidIsValid(domainId)) diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c index 0db90c2011..d6d6fcd524 100644 --- a/src/backend/catalog/toasting.c +++ b/src/backend/catalog/toasting.c @@ -292,6 +292,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid, indexInfo->ii_NumIndexKeyAttrs = 2; indexInfo->ii_IndexAttrNumbers[0] = 1; indexInfo->ii_IndexAttrNumbers[1] = 2; + indexInfo->ii_Period = NULL; indexInfo->ii_Expressions = NIL; indexInfo->ii_ExpressionsState = NIL; indexInfo->ii_Predicate = NIL; diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index c14ca27c5e..670024762e 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -81,6 +81,9 @@ static void ComputeIndexAttrs(IndexInfo *indexInfo, const char *accessMethodName, Oid accessMethodId, bool amcanorder, bool isconstraint); +static void ComputeIndexPeriod(IndexInfo *indexInfo, + Oid relId, + const char *periodName); static char *ChooseIndexName(const char *tabname, Oid namespaceId, List *colnames, List *exclusionOpNames, bool primary, bool isconstraint); @@ -164,7 +167,8 @@ bool CheckIndexCompatible(Oid oldId, const char *accessMethodName, List *attributeList, - List *exclusionOpNames) + List *exclusionOpNames, + const char *indexPeriodName) { bool isconstraint; Oid *typeObjectId; @@ -183,6 +187,8 @@ CheckIndexCompatible(Oid oldId, int old_natts; bool isnull; bool ret = true; + Oid old_periodid; + Oid new_periodid; oidvector *old_indclass; oidvector *old_indcollation; Relation irel; @@ -226,7 +232,7 @@ CheckIndexCompatible(Oid oldId, * ii_NumIndexKeyAttrs with same value. */ indexInfo = makeIndexInfo(numberOfAttributes, numberOfAttributes, - accessMethodId, NIL, NIL, false, false, false); + accessMethodId, NIL, NIL, false, false, false, false); typeObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid)); collationObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid)); classObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid)); @@ -238,6 +244,7 @@ CheckIndexCompatible(Oid oldId, accessMethodName, accessMethodId, amcanorder, isconstraint); + ComputeIndexPeriod(indexInfo, relationId, indexPeriodName); /* Get the soon-obsolete pg_index tuple. */ tuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(oldId)); @@ -257,6 +264,12 @@ CheckIndexCompatible(Oid oldId, return false; } + /* The two indexes should have the same period. */ + old_periodid = indexForm->indperiod; + new_periodid = indexInfo->ii_Period ? ((Period *) indexInfo->ii_Period)->oid : InvalidOid; + if (old_periodid != new_periodid) + return false; + /* Any change in operator class or collation breaks compatibility. */ old_natts = indexForm->indnkeyatts; Assert(old_natts == numberOfAttributes); @@ -525,6 +538,7 @@ DefineIndex(Oid relationId, Oid tablespaceId; Oid createdConstraintId = InvalidOid; List *indexColNames; + char *indexPeriodName; List *allIndexParams; Relation rel; HeapTuple tuple; @@ -771,6 +785,11 @@ DefineIndex(Oid relationId, */ indexColNames = ChooseIndexColumnNames(allIndexParams); + /* + * Choose the index period name. + */ + indexPeriodName = stmt->period ? stmt->period->periodname : NULL; + /* * Select name for index if caller didn't specify */ @@ -867,6 +886,7 @@ DefineIndex(Oid relationId, NIL, /* expressions, NIL for now */ make_ands_implicit((Expr *) stmt->whereClause), stmt->unique, + stmt->istemporal, !concurrent, concurrent); @@ -881,6 +901,8 @@ DefineIndex(Oid relationId, accessMethodName, accessMethodId, amcanorder, stmt->isconstraint); + ComputeIndexPeriod(indexInfo, relationId, indexPeriodName); + /* * Extra checks when creating a PRIMARY KEY index. */ @@ -1123,6 +1145,8 @@ DefineIndex(Oid relationId, constr_flags |= INDEX_CONSTR_CREATE_DEFERRABLE; if (stmt->initdeferred) constr_flags |= INDEX_CONSTR_CREATE_INIT_DEFERRED; + if (stmt->istemporal) + constr_flags |= INDEX_CONSTR_CREATE_TEMPORAL; indexRelationId = index_create(rel, indexRelationName, indexRelationId, parentIndexId, @@ -1994,6 +2018,19 @@ ComputeIndexAttrs(IndexInfo *indexInfo, } } +static void +ComputeIndexPeriod(IndexInfo *indexInfo, Oid relId, const char *periodName) +{ + if (periodName == NULL) + indexInfo->ii_Period = NULL; + else + { + Period *p = makeNode(Period); + p->oid = get_period_oid(relId, periodName, true); + indexInfo->ii_Period = (Node *) p; + } +} + /* * Resolve possibly-defaulted operator class specification * diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 745e78cdcd..469f2042f1 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -205,6 +205,7 @@ 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 */ + bool contemporal; /* Whether the new constraint is temporal */ Node *qual; /* Check expr or CONSTR_FOREIGN Constraint */ ExprState *qualstate; /* Execution state for CHECK expr */ } NewConstraint; @@ -9950,6 +9951,8 @@ addFkRecurseReferenced(List **wqueue, Constraint *fkconstraint, Relation rel, conislocal, /* islocal */ coninhcount, /* inhcount */ connoinherit, /* conNoInherit */ + false, /* conTemporal */ + InvalidOid, false); /* is_internal */ ObjectAddressSet(address, ConstraintRelationId, constrOid); @@ -10218,6 +10221,8 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, false, 1, false, + false, /* conTemporal */ + InvalidOid, false); /* @@ -10620,6 +10625,8 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel) false, /* islocal */ 1, /* inhcount */ false, /* conNoInherit */ + false, /* conTemporal */ + InvalidOid, true); /* Set up partition dependencies for the new constraint */ @@ -11151,6 +11158,7 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName, newcon->refrelid = con->confrelid; newcon->refindid = con->conindid; newcon->conid = con->oid; + newcon->contemporal = con->contemporal; newcon->qual = (Node *) fkconstraint; /* Find or create work queue entry for this table */ @@ -11309,10 +11317,12 @@ transformColumnNameList(Oid relId, List *colList, * * Look up the names, attnums, and types of the primary key attributes * for the pkrel. Also return the index OID and index opclasses of the - * index supporting the primary key. + * index supporting the primary key. If this is a temporal primary key, + * also set the WITHOUT OVERLAPS attribute name, attnum, and atttypid. * * All parameters except pkrel are output parameters. Also, the function - * return value is the number of attributes in the primary key. + * return value is the number of attributes in the primary key, + * not including the WITHOUT OVERLAPS if any. * * Used when the column list in the REFERENCES specification is omitted. */ @@ -13563,7 +13573,8 @@ TryReuseIndex(Oid oldId, IndexStmt *stmt) if (CheckIndexCompatible(oldId, stmt->accessMethod, stmt->indexParams, - stmt->excludeOpNames)) + stmt->excludeOpNames, + stmt->period ? stmt->period->periodname : NULL)) { Relation irel = index_open(oldId, NoLock); diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c index d8890d2c74..313ba9cdf6 100644 --- a/src/backend/commands/trigger.c +++ b/src/backend/commands/trigger.c @@ -836,6 +836,8 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString, true, /* islocal */ 0, /* inhcount */ true, /* noinherit */ + false, /* contemporal */ + InvalidOid, /* conperiod */ isInternal); /* is_internal */ } diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index 9ab4034179..ec48ac32c7 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -3552,6 +3552,8 @@ domainAddConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid, true, /* is local */ 0, /* inhcount */ false, /* connoinherit */ + false, /* contemporal */ + InvalidOid, /* conperiod */ false); /* is_internal */ if (constrAddr) ObjectAddressSet(*constrAddr, ConstraintRelationId, ccoid); diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index bbaee19905..f476009d37 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -3083,6 +3083,7 @@ _copyConstraint(const Constraint *from) COPY_SCALAR_FIELD(fk_del_action); COPY_NODE_FIELD(old_conpfeqop); COPY_SCALAR_FIELD(old_pktable_oid); + COPY_NODE_FIELD(without_overlaps); COPY_SCALAR_FIELD(skip_validation); COPY_SCALAR_FIELD(initially_valid); @@ -3675,6 +3676,7 @@ _copyIndexStmt(const IndexStmt *from) COPY_SCALAR_FIELD(unique); COPY_SCALAR_FIELD(primary); COPY_SCALAR_FIELD(isconstraint); + COPY_SCALAR_FIELD(istemporal); COPY_SCALAR_FIELD(deferrable); COPY_SCALAR_FIELD(initdeferred); COPY_SCALAR_FIELD(transformed); diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c index 7d1a01d1ed..891e477bc6 100644 --- a/src/backend/nodes/makefuncs.c +++ b/src/backend/nodes/makefuncs.c @@ -741,7 +741,8 @@ make_ands_implicit(Expr *clause) */ IndexInfo * makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions, - List *predicates, bool unique, bool isready, bool concurrent) + List *predicates, bool unique, bool temporal, bool isready, + bool concurrent) { IndexInfo *n = makeNode(IndexInfo); @@ -750,6 +751,7 @@ makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions, Assert(n->ii_NumIndexKeyAttrs != 0); Assert(n->ii_NumIndexKeyAttrs <= n->ii_NumIndexAttrs); n->ii_Unique = unique; + n->ii_Temporal = temporal; n->ii_ReadyForInserts = isready; n->ii_Concurrent = concurrent; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 8514d5f5ce..62c545f8fc 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -498,7 +498,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type TableElement TypedTableElement ConstraintElem TableFuncElement %type columnDef columnOptions %type def_elem reloption_elem old_aggr_elem operator_def_elem -%type def_arg columnElem where_clause where_or_current_clause +%type def_arg columnElem withoutOverlapsClause + where_clause where_or_current_clause a_expr b_expr c_expr AexprConst indirection_el opt_slice_bound columnref in_expr having_clause func_table xmltable array_expr OptWhereClause operator_def_arg @@ -3643,6 +3644,7 @@ ColConstraintElem: n->contype = CONSTR_PRIMARY; n->location = @1; n->keys = NULL; + n->without_overlaps = NULL; n->options = $3; n->indexname = NULL; n->indexspace = $4; @@ -3843,18 +3845,19 @@ ConstraintElem: n->initially_valid = !n->skip_validation; $$ = (Node *)n; } - | UNIQUE '(' columnList ')' opt_c_include opt_definition OptConsTableSpace + | UNIQUE '(' columnList withoutOverlapsClause ')' opt_c_include opt_definition OptConsTableSpace ConstraintAttributeSpec { Constraint *n = makeNode(Constraint); n->contype = CONSTR_UNIQUE; n->location = @1; n->keys = $3; - n->including = $5; - n->options = $6; + n->without_overlaps = $4; + n->including = $6; + n->options = $7; n->indexname = NULL; - n->indexspace = $7; - processCASbits($8, @8, "UNIQUE", + n->indexspace = $8; + processCASbits($9, @9, "UNIQUE", &n->deferrable, &n->initdeferred, NULL, NULL, yyscanner); $$ = (Node *)n; @@ -3874,18 +3877,19 @@ ConstraintElem: NULL, yyscanner); $$ = (Node *)n; } - | PRIMARY KEY '(' columnList ')' opt_c_include opt_definition OptConsTableSpace + | PRIMARY KEY '(' columnList withoutOverlapsClause ')' opt_c_include opt_definition OptConsTableSpace ConstraintAttributeSpec { Constraint *n = makeNode(Constraint); n->contype = CONSTR_PRIMARY; n->location = @1; n->keys = $4; - n->including = $6; - n->options = $7; + n->without_overlaps = $5; + n->including = $7; + n->options = $8; n->indexname = NULL; - n->indexspace = $8; - processCASbits($9, @9, "PRIMARY KEY", + n->indexspace = $9; + processCASbits($10, @10, "PRIMARY KEY", &n->deferrable, &n->initdeferred, NULL, NULL, yyscanner); $$ = (Node *)n; @@ -3896,6 +3900,7 @@ ConstraintElem: n->contype = CONSTR_PRIMARY; n->location = @1; n->keys = NIL; + n->without_overlaps = NULL; n->including = NIL; n->options = NIL; n->indexname = $3; @@ -3959,6 +3964,11 @@ columnList: | columnList ',' columnElem { $$ = lappend($1, $3); } ; +withoutOverlapsClause: + ',' columnElem WITHOUT OVERLAPS { $$ = $2; } + | /*EMPTY*/ { $$ = NULL; } + ; + columnElem: ColId { $$ = (Node *) makeString($1); diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index ad95ba1f90..eb17522ca3 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -37,6 +37,7 @@ #include "catalog/pg_constraint.h" #include "catalog/pg_opclass.h" #include "catalog/pg_operator.h" +#include "catalog/pg_period.h" #include "catalog/pg_statistic_ext.h" #include "catalog/pg_type.h" #include "commands/comment.h" @@ -128,6 +129,8 @@ static List *get_opclass(Oid opclass, Oid actual_datatype); static void transformIndexConstraints(CreateStmtContext *cxt); static IndexStmt *transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt); +static bool findNewOrOldColumn(CreateStmtContext *cxt, char *colname, char **typname, + Oid *typid); static void transformExtendedStatistics(CreateStmtContext *cxt); static void transformFKConstraints(CreateStmtContext *cxt, bool skipValidation, @@ -1668,6 +1671,7 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx, Oid keycoltype; Datum datum; bool isnull; + Period *period; if (constraintOid) *constraintOid = InvalidOid; @@ -1721,15 +1725,16 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx, index->oldFirstRelfilenodeSubid = InvalidSubTransactionId; index->unique = idxrec->indisunique; index->primary = idxrec->indisprimary; + index->istemporal = idxrec->indisprimary && idxrec->indisexclusion; index->transformed = true; /* don't need transformIndexStmt */ index->concurrent = false; index->if_not_exists = false; index->reset_default_tblspc = false; /* Copy the period */ - Period *p = makeNode(Period); - p->oid = idxrec->indperiod; - index->period = p; + period = makeNode(Period); + period->oid = idxrec->indperiod; + index->period = period; /* * We don't try to preserve the name of the source index; instead, just @@ -2305,7 +2310,7 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) index = makeNode(IndexStmt); - index->unique = (constraint->contype != CONSTR_EXCLUSION); + index->unique = (constraint->contype != CONSTR_EXCLUSION && constraint->without_overlaps == NULL); index->primary = (constraint->contype == CONSTR_PRIMARY); if (index->primary) { @@ -2323,6 +2328,7 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) */ } index->isconstraint = true; + index->istemporal = constraint->without_overlaps != NULL; index->deferrable = constraint->deferrable; index->initdeferred = constraint->initdeferred; @@ -2416,6 +2422,11 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) errmsg("index \"%s\" is not valid", index_name), parser_errposition(cxt->pstate, constraint->location))); + /* + * Today we forbid non-unique indexes, but we could permit GiST + * indexes whose last entry is a range type and use that to create a + * WITHOUT OVERLAPS constraint (i.e. a temporal constraint). + */ if (!index_form->indisunique) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), @@ -2703,6 +2714,166 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) notnullcmd->name = pstrdup(key); notnullcmds = lappend(notnullcmds, notnullcmd); } + + if (constraint->without_overlaps != NULL) + { + /* + * We are building the index like for an EXCLUSION constraint, + * so use the equality operator for these elements. + */ + List *opname = list_make1(makeString("=")); + index->excludeOpNames = lappend(index->excludeOpNames, opname); + } + } + + /* + * Anything in without_overlaps should be included, + * but with the overlaps operator (&&) instead of equality. + */ + if (constraint->without_overlaps != NULL) { + // char *without_overlaps_str = nodeToString(constraint->without_overlaps); + char *without_overlaps_str = strVal(constraint->without_overlaps); + IndexElem *iparam = makeNode(IndexElem); + char *typname; + Oid typid; + + /* + * Iterate through the table's columns + * (like just a little bit above). + * If we find one whose name is the same as without_overlaps, + * validate that it's a range type. + * + * Otherwise iterate through the table's non-system PERIODs, + * and if we find one then use its start/end columns + * to construct a range expression. + * + * Otherwise report an error. + */ + + if (findNewOrOldColumn(cxt, without_overlaps_str, &typname, &typid)) + { + if (type_is_range(typid)) + { + AlterTableCmd *notnullcmd; + + iparam->name = pstrdup(without_overlaps_str); + iparam->expr = NULL; + + /* + * Force the column to NOT NULL since it is part of the primary key. + */ + notnullcmd = makeNode(AlterTableCmd); + + notnullcmd->subtype = AT_SetNotNull; + notnullcmd->name = pstrdup(without_overlaps_str); + notnullcmds = lappend(notnullcmds, notnullcmd); + } + else + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("column \"%s\" named in WITHOUT OVERLAPS is not a range type", + without_overlaps_str))); + } + else + { + /* Look for a PERIOD, first newly-defined */ + char *startcolname = NULL; + char *endcolname = NULL; + ListCell *periods = NULL; + foreach(periods, cxt->periods) + { + Period *period = castNode(Period, lfirst(periods)); + if (strcmp(period->periodname, without_overlaps_str) == 0) + { + startcolname = period->startcolname; + endcolname = period->endcolname; + /* The period has no oid yet, but transformIndexStmt will look it up */ + index->period = period; + index->period->oid = InvalidOid; + index->period->periodname = without_overlaps_str; + break; + } + } + + if (startcolname == NULL && cxt->rel) + { + /* Look for an already-existing PERIOD */ + // TODO: locking? releasing? + HeapTuple perTuple; + Oid relid = RelationGetRelid(cxt->rel); + perTuple = SearchSysCache2(PERIODNAME, + ObjectIdGetDatum(relid), + PointerGetDatum(without_overlaps_str)); + if (HeapTupleIsValid(perTuple)) + { + Form_pg_period per = (Form_pg_period) GETSTRUCT(perTuple); + startcolname = get_attname(relid, per->perstart, false); + endcolname = get_attname(relid, per->perend, false); + index->period = makeNode(Period); + index->period->oid = per->oid; + index->period->periodname = without_overlaps_str; + + ReleaseSysCache(perTuple); + } + } + if (startcolname != NULL) + { + ColumnRef *start, *end; + Oid rngtypid; + char *range_type_name; + + if (!findNewOrOldColumn(cxt, startcolname, &typname, &typid)) + elog(ERROR, "Missing startcol %s for period %s", + startcolname, without_overlaps_str); + if (!findNewOrOldColumn(cxt, endcolname, &typname, &typid)) + elog(ERROR, "Missing endcol %s for period %s", + endcolname, without_overlaps_str); + + /* Use the start/end columns */ + + start = makeNode(ColumnRef); + start->fields = list_make1(makeString(startcolname)); + start->location = constraint->location; + + end = makeNode(ColumnRef); + end->fields = list_make1(makeString(endcolname)); + end->location = constraint->location; + + rngtypid = get_subtype_range(typid); + if (rngtypid == InvalidOid) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("PERIOD \"%s\" cannot be used in a constraint without a corresponding range type", + without_overlaps_str))); + + range_type_name = get_typname(rngtypid); + + /* Build a range to represent the PERIOD. */ + iparam->name = NULL; + iparam->expr = (Node *) makeFuncCall(SystemFuncName(range_type_name), + list_make2(start, end), + COERCE_EXPLICIT_CALL, + -1); + } + else + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("range or PERIOD \"%s\" named in WITHOUT OVERLAPS does not exist", + without_overlaps_str))); + } + { + iparam->indexcolname = NULL; + iparam->collation = NIL; + iparam->opclass = NIL; + iparam->ordering = SORTBY_DEFAULT; + iparam->nulls_ordering = SORTBY_NULLS_DEFAULT; + index->indexParams = lappend(index->indexParams, iparam); + + index->excludeOpNames = lappend(index->excludeOpNames, + list_make1(makeString("&&"))); + index->accessMethod = "gist"; + constraint->access_method = "gist"; + } } } @@ -2823,6 +2994,55 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) return index; } +/* + * Tries to find a column by name among the existing ones (if it's an ALTER TABLE) + * and the new ones. Sets typname and typid if one is found. Returns false if we + * couldn't find a match. + */ +static bool +findNewOrOldColumn(CreateStmtContext *cxt, char *colname, char **typname, Oid *typid) +{ + /* Check the new columns first in case their type is changing. */ + + ColumnDef *column = NULL; + ListCell *columns; + + foreach(columns, cxt->columns) + { + column = lfirst_node(ColumnDef, columns); + if (strcmp(column->colname, colname) == 0) + { + *typid = typenameTypeId(NULL, column->typeName); + *typname = TypeNameToString(column->typeName); + return true; + } + } + + // TODO: should I consider DROP COLUMN? + + /* Look up columns on existing table. */ + + if (cxt->isalter) + { + Relation rel = cxt->rel; + for (int i = 0; i < rel->rd_att->natts; i++) + { + Form_pg_attribute attr = TupleDescAttr(rel->rd_att, i); + const char *attname = NameStr(attr->attname); + if (strcmp(attname, colname) == 0) + { + Type type = typeidType(attr->atttypid); + *typid = attr->atttypid; + *typname = pstrdup(typeTypeName(type)); + ReleaseSysCache(type); + return true; + } + } + } + + return false; +} + /* * transformExtendedStatistics * Handle extended statistic objects diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 1bb25738a5..a768e4f55f 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -332,8 +332,8 @@ static char *deparse_expression_pretty(Node *expr, List *dpcontext, static char *pg_get_viewdef_worker(Oid viewoid, int prettyFlags, int wrapColumn); static char *pg_get_triggerdef_worker(Oid trigid, bool pretty); -static int decompile_column_index_array(Datum column_index_array, Oid relId, - StringInfo buf); +static int decompile_column_index_array(Datum column_index_array, Oid relId, Oid indexId, + bool withoutOverlaps, Oid periodid, StringInfo buf); static char *pg_get_ruledef_worker(Oid ruleoid, int prettyFlags); static char *pg_get_indexdef_worker(Oid indexrelid, int colno, const Oid *excludeOps, @@ -2196,7 +2196,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, elog(ERROR, "null conkey for constraint %u", constraintId); - decompile_column_index_array(val, conForm->conrelid, &buf); + decompile_column_index_array(val, conForm->conrelid, conForm->conindid, false, InvalidOid, &buf); /* add foreign relation name */ appendStringInfo(&buf, ") REFERENCES %s(", @@ -2210,7 +2210,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, elog(ERROR, "null confkey for constraint %u", constraintId); - decompile_column_index_array(val, conForm->confrelid, &buf); + decompile_column_index_array(val, conForm->confrelid, conForm->conindid, false, InvalidOid, &buf); appendStringInfoChar(&buf, ')'); @@ -2311,12 +2311,17 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, elog(ERROR, "null conkey for constraint %u", constraintId); - keyatts = decompile_column_index_array(val, conForm->conrelid, &buf); + /* + * If it has exclusion-style operator OIDs + * then it uses WITHOUT OVERLAPS. + */ + indexId = conForm->conindid; + SysCacheGetAttr(CONSTROID, tup, + Anum_pg_constraint_conexclop, &isnull); + keyatts = decompile_column_index_array(val, conForm->conrelid, indexId, !isnull, InvalidOid, &buf); appendStringInfoChar(&buf, ')'); - indexId = conForm->conindid; - /* Build including column list (from pg_index.indkeys) */ indtup = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexId)); if (!HeapTupleIsValid(indtup)) @@ -2512,8 +2517,8 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, * of keys. */ static int -decompile_column_index_array(Datum column_index_array, Oid relId, - StringInfo buf) +decompile_column_index_array(Datum column_index_array, Oid relId, Oid indexId, + bool withoutOverlaps, Oid periodid, StringInfo buf) { Datum *keys; int nKeys; @@ -2527,11 +2532,41 @@ decompile_column_index_array(Datum column_index_array, Oid relId, for (j = 0; j < nKeys; j++) { char *colName; + int colid = DatumGetInt16(keys[j]); + + /* The key might contain a PERIOD instead of an attribute */ + if (colid == 0) + { + /* First try the given periodid, then fall back on the index */ + if (periodid == InvalidOid) + { + HeapTuple indtup; + bool isnull; + Datum periodidDatum; + + indtup = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexId)); + if (!HeapTupleIsValid(indtup)) + elog(ERROR, "cache lookup failed for index %u", indexId); - colName = get_attname(relId, DatumGetInt16(keys[j]), false); + periodidDatum = SysCacheGetAttr(INDEXRELID, indtup, + Anum_pg_index_indperiod, &isnull); + if (isnull) + elog(ERROR, "missing period for index %u", indexId); + + periodid = DatumGetObjectId(periodidDatum); + ReleaseSysCache(indtup); + } + colName = get_periodname(periodid, false); + } + else + { + colName = get_attname(relId, colid, false); + } if (j == 0) appendStringInfoString(buf, quote_identifier(colName)); + else if (withoutOverlaps && j == nKeys - 1) + appendStringInfo(buf, ", %s WITHOUT OVERLAPS", quote_identifier(colName)); else appendStringInfo(buf, ", %s", quote_identifier(colName)); } diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index 73ccea4a69..362092368f 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -2198,6 +2198,32 @@ get_typisdefined(Oid typid) return false; } +/* + * get_typname + * + * Returns the name of a given type + * + * Returns a palloc'd copy of the string, or NULL if no such type. + */ +char * +get_typname(Oid typid) +{ + HeapTuple tp; + + tp = SearchSysCache1(TYPEOID, ObjectIdGetDatum(typid)); + if (HeapTupleIsValid(tp)) + { + Form_pg_type typtup = (Form_pg_type) GETSTRUCT(tp); + char *result; + + result = pstrdup(NameStr(typtup->typname)); + ReleaseSysCache(tp); + return result; + } + else + return NULL; +} + /* * get_typlen * diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index 9fa9e671a1..aab41779db 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -4729,11 +4729,17 @@ RelationGetIndexList(Relation relation) * interesting for either oid indexes or replication identity indexes, * so don't check them. */ - if (!index->indisvalid || !index->indisunique || - !index->indimmediate || + if (!index->indisvalid || !index->indimmediate || !heap_attisnull(htup, Anum_pg_index_indpred, NULL)) continue; + /* + * Non-unique indexes aren't interesting either, + * except when they are temporal primary keys. + */ + if (!index->indisunique && !index->indisprimary) + continue; + /* remember primary key index if any */ if (index->indisprimary) pkeyIndex = index->indexrelid; @@ -5415,8 +5421,9 @@ RelationGetIdentityKeyBitmap(Relation relation) * RelationGetExclusionInfo -- get info about index's exclusion constraint * * This should be called only for an index that is known to have an - * associated exclusion constraint. It returns arrays (palloc'd in caller's - * context) of the exclusion operator OIDs, their underlying functions' + * associated exclusion constraint or temporal primary key. + * It returns arrays (palloc'd in caller's * context) + * of the exclusion operator OIDs, their underlying functions' * OIDs, and their strategy numbers in the index's opclasses. We cache * all this information since it requires a fair amount of work to get. */ @@ -5482,7 +5489,12 @@ RelationGetExclusionInfo(Relation indexRelation, int nelem; /* We want the exclusion constraint owning the index */ - if (conform->contype != CONSTRAINT_EXCLUSION || + /* + * TODO: Is this too permissive? + * Maybe it needs to be (!= CONSTRAINT_PRIMARY || !has_excl_operators) + */ + if ((conform->contype != CONSTRAINT_EXCLUSION && + conform->contype != CONSTRAINT_PRIMARY) || conform->conindid != RelationGetRelid(indexRelation)) continue; diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 1392ca09c8..9816cc326d 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -7026,7 +7026,8 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) i_tablespace, i_indreloptions, i_indstatcols, - i_indstatvals; + i_indstatvals, + i_withoutoverlaps; int ntups; for (i = 0; i < numTables; i++) @@ -7087,7 +7088,8 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) "(SELECT pg_catalog.array_agg(attstattarget ORDER BY attnum) " " FROM pg_catalog.pg_attribute " " WHERE attrelid = i.indexrelid AND " - " attstattarget >= 0) AS indstatvals " + " attstattarget >= 0) AS indstatvals, " + "c.conexclop IS NOT NULL AS withoutoverlaps " "FROM pg_catalog.pg_index i " "JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) " "JOIN pg_catalog.pg_class t2 ON (t2.oid = i.indrelid) " @@ -7126,7 +7128,8 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) "(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, " "t.reloptions AS indreloptions, " "'' AS indstatcols, " - "'' AS indstatvals " + "'' AS indstatvals, " + "null AS withoutoverlaps " "FROM pg_catalog.pg_index i " "JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) " "LEFT JOIN pg_catalog.pg_constraint c " @@ -7161,7 +7164,8 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) "(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, " "t.reloptions AS indreloptions, " "'' AS indstatcols, " - "'' AS indstatvals " + "'' AS indstatvals, " + "null AS withoutoverlaps " "FROM pg_catalog.pg_index i " "JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) " "LEFT JOIN pg_catalog.pg_constraint c " @@ -7192,7 +7196,8 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) "(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, " "t.reloptions AS indreloptions, " "'' AS indstatcols, " - "'' AS indstatvals " + "'' AS indstatvals, " + "null AS withoutoverlaps " "FROM pg_catalog.pg_index i " "JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) " "LEFT JOIN pg_catalog.pg_depend d " @@ -7226,7 +7231,8 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) "(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, " "null AS indreloptions, " "'' AS indstatcols, " - "'' AS indstatvals " + "'' AS indstatvals, " + "null AS withoutoverlaps " "FROM pg_catalog.pg_index i " "JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) " "LEFT JOIN pg_catalog.pg_depend d " @@ -7266,6 +7272,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) i_indreloptions = PQfnumber(res, "indreloptions"); i_indstatcols = PQfnumber(res, "indstatcols"); i_indstatvals = PQfnumber(res, "indstatvals"); + i_withoutoverlaps = PQfnumber(res, "withoutoverlaps"); tbinfo->indexes = indxinfo = (IndxInfo *) pg_malloc(ntups * sizeof(IndxInfo)); @@ -7331,6 +7338,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) constrinfo->condeferred = *(PQgetvalue(res, j, i_condeferred)) == 't'; constrinfo->conislocal = true; constrinfo->separate = true; + constrinfo->withoutoverlaps = *(PQgetvalue(res, j, i_withoutoverlaps)) == 't'; indxinfo[j].indexconstraint = constrinfo->dobj.dumpId; } @@ -17091,9 +17099,22 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo) break; attname = getAttrName(indkey, tbinfo); - appendPQExpBuffer(q, "%s%s", - (k == 0) ? "" : ", ", - fmtId(attname)); + if (k == 0) + { + appendPQExpBuffer(q, "%s", + fmtId(attname)); + } + else if (k == indxinfo->indnkeyattrs - 1 && + coninfo->withoutoverlaps) + { + appendPQExpBuffer(q, ", %s WITHOUT OVERLAPS", + fmtId(attname)); + } + else + { + appendPQExpBuffer(q, ", %s", + fmtId(attname)); + } } if (indxinfo->indnkeyattrs < indxinfo->indnattrs) diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index db989ba021..630be50784 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -470,6 +470,7 @@ typedef struct _constraintInfo bool condeferred; /* true if constraint is INITIALLY DEFERRED */ bool conislocal; /* true if constraint has local definition */ bool separate; /* true if must dump as separate item */ + bool withoutoverlaps; /* true if the last elem is WITHOUT OVERLAPS */ } ConstraintInfo; typedef struct _periodInfo diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index d293f52b05..d8caea7bd4 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -699,6 +699,28 @@ my %tests = ( }, }, + 'ALTER TABLE ONLY test_table ADD CONSTRAINT ... PRIMARY KEY (..., ... WITHOUT OVERLAPS)' => { + create_sql => 'CREATE TABLE dump_test.test_table_tpk ( + col1 int4range, + col2 tstzrange, + CONSTRAINT test_table_tpk_pkey PRIMARY KEY + (col1, col2 WITHOUT OVERLAPS));', + regexp => qr/^ + \QALTER TABLE ONLY dump_test.test_table_tpk\E \n^\s+ + \QADD CONSTRAINT test_table_tpk_pkey PRIMARY KEY (col1, col2 WITHOUT OVERLAPS);\E + /xm, + like => { + %full_runs, + %dump_test_schema_runs, + section_post_data => 1, + exclude_test_table => 1, + }, + unlike => { + only_dump_test_table => 1, + exclude_dump_test_schema => 1, + }, + }, + 'ALTER TABLE (partitioned) ADD CONSTRAINT ... FOREIGN KEY' => { create_order => 4, create_sql => 'CREATE TABLE dump_test.test_table_fk ( diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index c6358f91b1..825128ccf7 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -2624,6 +2624,8 @@ describeOneTableDetails(const char *schemaname, } /* Everything after "USING" is echoed verbatim */ + // TODO: Show WITHOUT OVERLAPS info here? + // It is not really part of the *index*. indexdef = PQgetvalue(result, i, 5); usingpos = strstr(indexdef, " USING "); if (usingpos) diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index 008f723e10..8bc2b0be91 100644 --- a/src/include/catalog/index.h +++ b/src/include/catalog/index.h @@ -91,6 +91,7 @@ extern Oid index_create(Relation heapRelation, #define INDEX_CONSTR_CREATE_INIT_DEFERRED (1 << 2) #define INDEX_CONSTR_CREATE_UPDATE_INDEX (1 << 3) #define INDEX_CONSTR_CREATE_REMOVE_OLD_DEPS (1 << 4) +#define INDEX_CONSTR_CREATE_TEMPORAL (1 << 5) extern Oid index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h index e75baa8e1e..6c6675b0f7 100644 --- a/src/include/catalog/pg_constraint.h +++ b/src/include/catalog/pg_constraint.h @@ -107,6 +107,14 @@ CATALOG(pg_constraint,2606,ConstraintRelationId) /* Has a local definition and cannot be inherited */ bool connoinherit; + /* + * For primary and foreign keys, signifies the last column is a range + * and should use overlaps instead of equals. + */ + bool contemporal; + + Oid conperiod; /* local PERIOD used in PK/FK constraint */ + #ifdef CATALOG_VARLEN /* variable-length fields start here */ /* @@ -140,7 +148,7 @@ CATALOG(pg_constraint,2606,ConstraintRelationId) /* * If an exclusion constraint, the OIDs of the exclusion operators for - * each column of the constraint + * each column of the constraint. Also set for temporal primary keys. */ Oid conexclop[1] BKI_LOOKUP(pg_operator); @@ -227,6 +235,8 @@ extern Oid CreateConstraintEntry(const char *constraintName, bool conIsLocal, int conInhCount, bool conNoInherit, + bool conTemporal, + Oid period, bool is_internal); extern void RemoveConstraintById(Oid conId); diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h index f84d09959c..b6e2d16968 100644 --- a/src/include/commands/defrem.h +++ b/src/include/commands/defrem.h @@ -43,7 +43,8 @@ extern char *ChooseRelationName(const char *name1, const char *name2, extern bool CheckIndexCompatible(Oid oldId, const char *accessMethodName, List *attributeList, - List *exclusionOpNames); + List *exclusionOpNames, + const char *indexPeriodName); extern Oid GetDefaultOpClass(Oid type_id, Oid am_id); extern Oid ResolveOpClass(List *opclass, Oid attrType, const char *accessMethodName, Oid accessMethodId); diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 2e8cbee69f..62cb6f5054 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -129,6 +129,7 @@ typedef struct ExprState * IndexAttrNumbers underlying-rel attribute numbers used as keys * (zeroes indicate expressions). It also contains * info about included columns. + * Period period used in the index, or NULL if none * Expressions expr trees for expression entries, or NIL if none * ExpressionsState exec state for expressions, or NIL if none * Predicate partial-index predicate, or NIL if none @@ -140,6 +141,7 @@ typedef struct ExprState * UniqueProcs * UniqueStrats * Unique is it a unique index? + * Temporal is it for a temporal constraint? * OpclassOptions opclass-specific options, or NULL if none * ReadyForInserts is it valid for inserts? * Concurrent are we doing a concurrent index build? @@ -159,6 +161,7 @@ typedef struct IndexInfo int ii_NumIndexAttrs; /* total number of columns in index */ int ii_NumIndexKeyAttrs; /* number of key columns in index */ AttrNumber ii_IndexAttrNumbers[INDEX_MAX_KEYS]; + Node *ii_Period; /* period used in the index */ List *ii_Expressions; /* list of Expr */ List *ii_ExpressionsState; /* list of ExprState */ List *ii_Predicate; /* list of Expr */ @@ -171,6 +174,7 @@ typedef struct IndexInfo uint16 *ii_UniqueStrats; /* array with one entry per column */ Datum *ii_OpclassOptions; /* array with one entry per column */ bool ii_Unique; + bool ii_Temporal; bool ii_ReadyForInserts; bool ii_Concurrent; bool ii_BrokenHotChain; diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h index eea87f847d..2ad1a48d94 100644 --- a/src/include/nodes/makefuncs.h +++ b/src/include/nodes/makefuncs.h @@ -96,7 +96,8 @@ extern List *make_ands_implicit(Expr *clause); extern IndexInfo *makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions, List *predicates, - bool unique, bool isready, bool concurrent); + bool unique, bool temporal, bool isready, + bool concurrent); extern DefElem *makeDefElem(char *name, Node *arg, int location); extern DefElem *makeDefElemExtended(char *nameSpace, char *name, Node *arg, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index f4462c1114..647dfdc144 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2329,6 +2329,9 @@ typedef struct Constraint Oid old_pktable_oid; /* pg_constraint.confrelid of my former * self */ + /* Fields used for temporal PRIMARY KEY and FOREIGN KEY constraints: */ + Node *without_overlaps; /* String node naming PERIOD or range column */ + /* Fields used for constraints that allow a NOT VALID specification */ bool skip_validation; /* skip validation of existing rows? */ bool initially_valid; /* mark the new constraint as valid? */ @@ -2930,6 +2933,7 @@ typedef struct IndexStmt bool unique; /* is index unique? */ bool primary; /* is index a primary key? */ bool isconstraint; /* is it for a pkey/unique constraint? */ + bool istemporal; /* is it for a temporal pkey? */ bool deferrable; /* is the constraint DEFERRABLE? */ bool initdeferred; /* is the constraint INITIALLY DEFERRED? */ bool transformed; /* true when transformIndexStmt is finished */ diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h index bb8c1b6742..b3aad218f0 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -144,6 +144,7 @@ extern char get_rel_persistence(Oid relid); extern Oid get_transform_fromsql(Oid typid, Oid langid, List *trftypes); extern Oid get_transform_tosql(Oid typid, Oid langid, List *trftypes); extern bool get_typisdefined(Oid typid); +extern char *get_typname(Oid typid); extern int16 get_typlen(Oid typid); extern bool get_typbyval(Oid typid); extern void get_typlenbyval(Oid typid, int16 *typlen, bool *typbyval); diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out index eed75441c7..6836f41f09 100644 --- a/src/test/regress/expected/sanity_check.out +++ b/src/test/regress/expected/sanity_check.out @@ -222,6 +222,8 @@ trigger_parted_p2|t trigger_parted_p2_2|t varchar_tbl|f view_base_table|t +without_overlaps_test|t +without_overlaps_uq_test|t -- restore normal output mode \a\t -- diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out new file mode 100644 index 0000000000..708b5d3528 --- /dev/null +++ b/src/test/regress/expected/without_overlaps.out @@ -0,0 +1,286 @@ +-- Tests for WITHOUT OVERLAPS. +-- +-- test input parser +-- +-- PK with no columns just WITHOUT OVERLAPS: +CREATE TABLE without_overlaps_test ( + valid_at tsrange, + CONSTRAINT without_overlaps_pk PRIMARY KEY (valid_at WITHOUT OVERLAPS) +); +ERROR: syntax error at or near "WITHOUT" +LINE 3: ...STRAINT without_overlaps_pk PRIMARY KEY (valid_at WITHOUT OV... + ^ +-- PK with a range column/PERIOD that isn't there: +CREATE TABLE without_overlaps_test ( + id INTEGER, + CONSTRAINT without_overlaps_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +ERROR: range or PERIOD "valid_at" named in WITHOUT OVERLAPS does not exist +-- PK with a non-range column: +CREATE TABLE without_overlaps_test ( + id INTEGER, + valid_at TEXT, + CONSTRAINT without_overlaps_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +ERROR: column "valid_at" named in WITHOUT OVERLAPS is not a range type +-- PK with one column plus a range: +CREATE TABLE without_overlaps_test ( + -- Since we can't depend on having btree_gist here, + -- use an int4range instead of an int. + -- (The rangetypes regression test uses the same trick.) + id int4range, + valid_at tsrange, + CONSTRAINT without_overlaps_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +-- PK with two columns plus a range: +CREATE TABLE without_overlaps_test2 ( + id1 int4range, + id2 int4range, + valid_at tsrange, + CONSTRAINT without_overlaps2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) +); +DROP TABLE without_overlaps_test2; +-- PK with one column plus a PERIOD: +CREATE TABLE without_overlaps_test2 ( + id int4range, + valid_from date, + valid_til date, + PERIOD FOR valid_at (valid_from, valid_til), + CONSTRAINT without_overlaps2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'without_overlaps2_pk'; + pg_get_constraintdef +--------------------------------------------- + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +(1 row) + +DROP TABLE without_overlaps_test2; +-- PK with two columns plus a PERIOD: +CREATE TABLE without_overlaps_test2 ( + id1 int4range, + id2 int4range, + valid_from date, + valid_til date, + PERIOD FOR valid_at (valid_from, valid_til), + CONSTRAINT without_overlaps2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) +); +DROP TABLE without_overlaps_test2; +-- PK with a custom range type: +CREATE TYPE textrange2 AS range (subtype=text, collation="C"); +CREATE TABLE without_overlaps_test2 ( + id int4range, + valid_at textrange2, + CONSTRAINT without_overlaps2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +ALTER TABLE without_overlaps_test2 DROP CONSTRAINT without_overlaps2_pk; +DROP TABLE without_overlaps_test2; +DROP TYPE textrange2; +-- UNIQUE with no columns just WITHOUT OVERLAPS: +CREATE TABLE without_overlaps_uq_test ( + valid_at tsrange, + CONSTRAINT without_overlaps_uq UNIQUE (valid_at WITHOUT OVERLAPS) +); +ERROR: syntax error at or near "WITHOUT" +LINE 3: CONSTRAINT without_overlaps_uq UNIQUE (valid_at WITHOUT OVE... + ^ +-- UNIQUE with a range column/PERIOD that isn't there: +CREATE TABLE without_overlaps_uq_test ( + id INTEGER, + CONSTRAINT without_overlaps_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) +); +ERROR: range or PERIOD "valid_at" named in WITHOUT OVERLAPS does not exist +-- UNIQUE with a non-range column: +CREATE TABLE without_overlaps_uq_test ( + id INTEGER, + valid_at TEXT, + CONSTRAINT without_overlaps_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) +); +ERROR: column "valid_at" named in WITHOUT OVERLAPS is not a range type +-- UNIQUE with one column plus a range: +CREATE TABLE without_overlaps_uq_test ( + -- Since we can't depend on having btree_gist here, + -- use an int4range instead of an int. + -- (The rangetypes regression test uses the same trick.) + id int4range, + valid_at tsrange, + CONSTRAINT without_overlaps_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) +); +-- UNIQUE with two columns plus a range: +CREATE TABLE without_overlaps_uq_test2 ( + id1 int4range, + id2 int4range, + valid_at tsrange, + CONSTRAINT without_overlaps2_uq UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS) +); +DROP TABLE without_overlaps_uq_test2; +-- UNIQUE with one column plus a PERIOD: +CREATE TABLE without_overlaps_uq_test2 ( + id int4range, + valid_from timestamp, + valid_til timestamp, + PERIOD FOR valid_at (valid_from, valid_til), + CONSTRAINT without_overlaps2_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) +); +DROP TABLE without_overlaps_uq_test2; +-- UNIQUE with two columns plus a PERIOD: +CREATE TABLE without_overlaps_uq_test2 ( + id1 int4range, + id2 int4range, + valid_from timestamp, + valid_til timestamp, + PERIOD FOR valid_at (valid_from, valid_til), + CONSTRAINT without_overlaps2_uq UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS) +); +DROP TABLE without_overlaps_uq_test2; +-- UNIQUE with a custom range type: +CREATE TYPE textrange2 AS range (subtype=text, collation="C"); +CREATE TABLE without_overlaps_uq_test2 ( + id int4range, + valid_at textrange2, + CONSTRAINT without_overlaps2_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) +); +ALTER TABLE without_overlaps_uq_test2 DROP CONSTRAINT without_overlaps2_uq; +DROP TABLE without_overlaps_uq_test2; +DROP TYPE textrange2; +-- +-- test ALTER TABLE ADD CONSTRAINT +-- +DROP TABLE without_overlaps_test; +CREATE TABLE without_overlaps_test ( + id int4range, + valid_at tsrange +); +ALTER TABLE without_overlaps_test + ADD CONSTRAINT without_overlaps_pk + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +-- PK with USING INDEX (not yet allowed): +CREATE TABLE without_overlaps_test2 ( + id int4range, + valid_at tsrange +); +CREATE INDEX idx_without_overlaps2 ON without_overlaps_test2 USING gist (id, valid_at); +ALTER TABLE without_overlaps_test2 + ADD CONSTRAINT without_overlaps2_pk + PRIMARY KEY USING INDEX idx_without_overlaps2; +ERROR: "idx_without_overlaps2" is not a unique index +LINE 2: ADD CONSTRAINT without_overlaps2_pk + ^ +DETAIL: Cannot create a primary key or unique constraint using such an index. +DROP TABLE without_overlaps_test2; +-- UNIQUE with USING INDEX (not yet allowed): +CREATE TABLE without_overlaps_uq_test2 ( + id int4range, + valid_at tsrange +); +CREATE INDEX idx_without_overlaps_uq ON without_overlaps_uq_test2 USING gist (id, valid_at); +ALTER TABLE without_overlaps_uq_test2 + ADD CONSTRAINT without_overlaps2_uq + UNIQUE USING INDEX idx_without_overlaps_uq; +ERROR: "idx_without_overlaps_uq" is not a unique index +LINE 2: ADD CONSTRAINT without_overlaps2_uq + ^ +DETAIL: Cannot create a primary key or unique constraint using such an index. +DROP TABLE without_overlaps_uq_test2; +-- Add range column and the PK at the same time +CREATE TABLE without_overlaps_test2 ( + id int4range +); +ALTER TABLE without_overlaps_test2 + ADD COLUMN valid_at tsrange, + ADD CONSTRAINT without_overlaps2_pk + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +DROP TABLE without_overlaps_test2; +-- Add PERIOD and the PK at the same time +CREATE TABLE without_overlaps_test2 ( + id int4range, + valid_from date, + valid_til date +); +ALTER TABLE without_overlaps_test2 + ADD PERIOD FOR valid_at (valid_from, valid_til), + ADD CONSTRAINT without_overlaps2_pk + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +DROP TABLE without_overlaps_test2; +-- Add range column and UNIQUE constraint at the same time +CREATE TABLE without_overlaps_test2 ( + id int4range +); +ALTER TABLE without_overlaps_test2 + ADD COLUMN valid_at tsrange, + ADD CONSTRAINT without_overlaps2_uq + UNIQUE (id, valid_at WITHOUT OVERLAPS); +DROP TABLE without_overlaps_test2; +-- Add PERIOD column and UNIQUE constraint at the same time +CREATE TABLE without_overlaps_test2 ( + id int4range, + valid_from date, + valid_til date +); +ALTER TABLE without_overlaps_test2 + ADD PERIOD FOR valid_at (valid_from, valid_til), + ADD CONSTRAINT without_overlaps2_uq + UNIQUE (id, valid_at WITHOUT OVERLAPS); +DROP TABLE without_overlaps_test2; +-- Add date columns, PERIOD, and the PK at the same time +CREATE TABLE without_overlaps_test2 ( + id int4range +); +ALTER TABLE without_overlaps_test2 + ADD COLUMN valid_from date, + ADD COLUMN valid_til date, + ADD PERIOD FOR valid_at (valid_from, valid_til), + ADD CONSTRAINT without_overlaps2_pk + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +DROP TABLE without_overlaps_test2; +-- Add date columns, PERIOD, and UNIQUE constraint at the same time +CREATE TABLE without_overlaps_test2 ( + id int4range +); +ALTER TABLE without_overlaps_test2 + ADD COLUMN valid_from date, + ADD COLUMN valid_til date, + ADD PERIOD FOR valid_at (valid_from, valid_til), + ADD CONSTRAINT without_overlaps2_uq + UNIQUE (id, valid_at WITHOUT OVERLAPS); +DROP TABLE without_overlaps_test2; +-- +-- test pg_get_constraintdef +-- +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'without_overlaps_pk'; + pg_get_constraintdef +--------------------------------------------- + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +(1 row) + +-- +-- test PK inserts +-- +-- okay: +INSERT INTO without_overlaps_test VALUES ('[1,1]', tsrange('2018-01-02', '2018-02-03')); +INSERT INTO without_overlaps_test VALUES ('[1,1]', tsrange('2018-03-03', '2018-04-04')); +INSERT INTO without_overlaps_test VALUES ('[2,2]', tsrange('2018-01-01', '2018-01-05')); +INSERT INTO without_overlaps_test VALUES ('[3,3]', tsrange('2018-01-01', NULL)); +-- should fail: +INSERT INTO without_overlaps_test VALUES ('[1,1]', tsrange('2018-01-01', '2018-01-05')); +ERROR: conflicting key value violates exclusion constraint "without_overlaps_pk" +DETAIL: Key (id, valid_at)=([1,2), ["Mon Jan 01 00:00:00 2018","Fri Jan 05 00:00:00 2018")) conflicts with existing key (id, valid_at)=([1,2), ["Tue Jan 02 00:00:00 2018","Sat Feb 03 00:00:00 2018")). +INSERT INTO without_overlaps_test VALUES (NULL, tsrange('2018-01-01', '2018-01-05')); +ERROR: null value in column "id" of relation "without_overlaps_test" violates not-null constraint +DETAIL: Failing row contains (null, ["Mon Jan 01 00:00:00 2018","Fri Jan 05 00:00:00 2018")). +INSERT INTO without_overlaps_test VALUES ('[3,3]', NULL); +ERROR: null value in column "valid_at" of relation "without_overlaps_test" violates not-null constraint +DETAIL: Failing row contains ([3,4), null). +-- +-- test changing the PK's dependencies +-- +CREATE TABLE without_overlaps_test2 ( + id int4range, + valid_at tsrange, + CONSTRAINT without_overlaps2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +ALTER TABLE without_overlaps_test2 ALTER COLUMN valid_at DROP NOT NULL; +ERROR: column "valid_at" is in a primary key +ALTER TABLE without_overlaps_test2 ALTER COLUMN valid_at TYPE tstzrange USING tstzrange(lower(valid_at), upper(valid_at)); +ALTER TABLE without_overlaps_test2 RENAME COLUMN valid_at TO valid_thru; +ALTER TABLE without_overlaps_test2 DROP COLUMN valid_thru; +DROP TABLE without_overlaps_test2; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 7a3b6fb145..088387281a 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -30,7 +30,7 @@ test: strings numerology point lseg line box path polygon circle date time timet # horology depends on interval, timetz, timestamp, timestamptz # opr_sanity depends on create_function_0 # ---------- -test: geometry horology tstypes regex type_sanity opr_sanity misc_sanity comments expressions unicode xid mvcc +test: geometry horology tstypes regex type_sanity opr_sanity misc_sanity comments expressions unicode xid mvcc without_overlaps # ---------- # These four each depend on the previous one diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql new file mode 100644 index 0000000000..e64ec69f0b --- /dev/null +++ b/src/test/regress/sql/without_overlaps.sql @@ -0,0 +1,295 @@ +-- Tests for WITHOUT OVERLAPS. + +-- +-- test input parser +-- + +-- PK with no columns just WITHOUT OVERLAPS: + +CREATE TABLE without_overlaps_test ( + valid_at tsrange, + CONSTRAINT without_overlaps_pk PRIMARY KEY (valid_at WITHOUT OVERLAPS) +); + +-- PK with a range column/PERIOD that isn't there: + +CREATE TABLE without_overlaps_test ( + id INTEGER, + CONSTRAINT without_overlaps_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); + +-- PK with a non-range column: + +CREATE TABLE without_overlaps_test ( + id INTEGER, + valid_at TEXT, + CONSTRAINT without_overlaps_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); + +-- PK with one column plus a range: + +CREATE TABLE without_overlaps_test ( + -- Since we can't depend on having btree_gist here, + -- use an int4range instead of an int. + -- (The rangetypes regression test uses the same trick.) + id int4range, + valid_at tsrange, + CONSTRAINT without_overlaps_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); + +-- PK with two columns plus a range: +CREATE TABLE without_overlaps_test2 ( + id1 int4range, + id2 int4range, + valid_at tsrange, + CONSTRAINT without_overlaps2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) +); +DROP TABLE without_overlaps_test2; + + +-- PK with one column plus a PERIOD: +CREATE TABLE without_overlaps_test2 ( + id int4range, + valid_from date, + valid_til date, + PERIOD FOR valid_at (valid_from, valid_til), + CONSTRAINT without_overlaps2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'without_overlaps2_pk'; +DROP TABLE without_overlaps_test2; + +-- PK with two columns plus a PERIOD: +CREATE TABLE without_overlaps_test2 ( + id1 int4range, + id2 int4range, + valid_from date, + valid_til date, + PERIOD FOR valid_at (valid_from, valid_til), + CONSTRAINT without_overlaps2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) +); +DROP TABLE without_overlaps_test2; + +-- PK with a custom range type: +CREATE TYPE textrange2 AS range (subtype=text, collation="C"); +CREATE TABLE without_overlaps_test2 ( + id int4range, + valid_at textrange2, + CONSTRAINT without_overlaps2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +ALTER TABLE without_overlaps_test2 DROP CONSTRAINT without_overlaps2_pk; +DROP TABLE without_overlaps_test2; +DROP TYPE textrange2; + +-- UNIQUE with no columns just WITHOUT OVERLAPS: + +CREATE TABLE without_overlaps_uq_test ( + valid_at tsrange, + CONSTRAINT without_overlaps_uq UNIQUE (valid_at WITHOUT OVERLAPS) +); + +-- UNIQUE with a range column/PERIOD that isn't there: + +CREATE TABLE without_overlaps_uq_test ( + id INTEGER, + CONSTRAINT without_overlaps_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) +); + +-- UNIQUE with a non-range column: + +CREATE TABLE without_overlaps_uq_test ( + id INTEGER, + valid_at TEXT, + CONSTRAINT without_overlaps_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) +); + +-- UNIQUE with one column plus a range: + +CREATE TABLE without_overlaps_uq_test ( + -- Since we can't depend on having btree_gist here, + -- use an int4range instead of an int. + -- (The rangetypes regression test uses the same trick.) + id int4range, + valid_at tsrange, + CONSTRAINT without_overlaps_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) +); + +-- UNIQUE with two columns plus a range: +CREATE TABLE without_overlaps_uq_test2 ( + id1 int4range, + id2 int4range, + valid_at tsrange, + CONSTRAINT without_overlaps2_uq UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS) +); +DROP TABLE without_overlaps_uq_test2; + +-- UNIQUE with one column plus a PERIOD: +CREATE TABLE without_overlaps_uq_test2 ( + id int4range, + valid_from timestamp, + valid_til timestamp, + PERIOD FOR valid_at (valid_from, valid_til), + CONSTRAINT without_overlaps2_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) +); +DROP TABLE without_overlaps_uq_test2; + +-- UNIQUE with two columns plus a PERIOD: +CREATE TABLE without_overlaps_uq_test2 ( + id1 int4range, + id2 int4range, + valid_from timestamp, + valid_til timestamp, + PERIOD FOR valid_at (valid_from, valid_til), + CONSTRAINT without_overlaps2_uq UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS) +); +DROP TABLE without_overlaps_uq_test2; + +-- UNIQUE with a custom range type: +CREATE TYPE textrange2 AS range (subtype=text, collation="C"); +CREATE TABLE without_overlaps_uq_test2 ( + id int4range, + valid_at textrange2, + CONSTRAINT without_overlaps2_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) +); +ALTER TABLE without_overlaps_uq_test2 DROP CONSTRAINT without_overlaps2_uq; +DROP TABLE without_overlaps_uq_test2; +DROP TYPE textrange2; + +-- +-- test ALTER TABLE ADD CONSTRAINT +-- + +DROP TABLE without_overlaps_test; +CREATE TABLE without_overlaps_test ( + id int4range, + valid_at tsrange +); +ALTER TABLE without_overlaps_test + ADD CONSTRAINT without_overlaps_pk + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); + +-- PK with USING INDEX (not yet allowed): +CREATE TABLE without_overlaps_test2 ( + id int4range, + valid_at tsrange +); +CREATE INDEX idx_without_overlaps2 ON without_overlaps_test2 USING gist (id, valid_at); +ALTER TABLE without_overlaps_test2 + ADD CONSTRAINT without_overlaps2_pk + PRIMARY KEY USING INDEX idx_without_overlaps2; +DROP TABLE without_overlaps_test2; + +-- UNIQUE with USING INDEX (not yet allowed): +CREATE TABLE without_overlaps_uq_test2 ( + id int4range, + valid_at tsrange +); +CREATE INDEX idx_without_overlaps_uq ON without_overlaps_uq_test2 USING gist (id, valid_at); +ALTER TABLE without_overlaps_uq_test2 + ADD CONSTRAINT without_overlaps2_uq + UNIQUE USING INDEX idx_without_overlaps_uq; +DROP TABLE without_overlaps_uq_test2; + +-- Add range column and the PK at the same time +CREATE TABLE without_overlaps_test2 ( + id int4range +); +ALTER TABLE without_overlaps_test2 + ADD COLUMN valid_at tsrange, + ADD CONSTRAINT without_overlaps2_pk + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +DROP TABLE without_overlaps_test2; + +-- Add PERIOD and the PK at the same time +CREATE TABLE without_overlaps_test2 ( + id int4range, + valid_from date, + valid_til date +); +ALTER TABLE without_overlaps_test2 + ADD PERIOD FOR valid_at (valid_from, valid_til), + ADD CONSTRAINT without_overlaps2_pk + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +DROP TABLE without_overlaps_test2; + +-- Add range column and UNIQUE constraint at the same time +CREATE TABLE without_overlaps_test2 ( + id int4range +); +ALTER TABLE without_overlaps_test2 + ADD COLUMN valid_at tsrange, + ADD CONSTRAINT without_overlaps2_uq + UNIQUE (id, valid_at WITHOUT OVERLAPS); +DROP TABLE without_overlaps_test2; + +-- Add PERIOD column and UNIQUE constraint at the same time +CREATE TABLE without_overlaps_test2 ( + id int4range, + valid_from date, + valid_til date +); +ALTER TABLE without_overlaps_test2 + ADD PERIOD FOR valid_at (valid_from, valid_til), + ADD CONSTRAINT without_overlaps2_uq + UNIQUE (id, valid_at WITHOUT OVERLAPS); +DROP TABLE without_overlaps_test2; + +-- Add date columns, PERIOD, and the PK at the same time +CREATE TABLE without_overlaps_test2 ( + id int4range +); +ALTER TABLE without_overlaps_test2 + ADD COLUMN valid_from date, + ADD COLUMN valid_til date, + ADD PERIOD FOR valid_at (valid_from, valid_til), + ADD CONSTRAINT without_overlaps2_pk + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +DROP TABLE without_overlaps_test2; + +-- Add date columns, PERIOD, and UNIQUE constraint at the same time +CREATE TABLE without_overlaps_test2 ( + id int4range +); +ALTER TABLE without_overlaps_test2 + ADD COLUMN valid_from date, + ADD COLUMN valid_til date, + ADD PERIOD FOR valid_at (valid_from, valid_til), + ADD CONSTRAINT without_overlaps2_uq + UNIQUE (id, valid_at WITHOUT OVERLAPS); +DROP TABLE without_overlaps_test2; + +-- +-- test pg_get_constraintdef +-- + +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'without_overlaps_pk'; + +-- +-- test PK inserts +-- + +-- okay: +INSERT INTO without_overlaps_test VALUES ('[1,1]', tsrange('2018-01-02', '2018-02-03')); +INSERT INTO without_overlaps_test VALUES ('[1,1]', tsrange('2018-03-03', '2018-04-04')); +INSERT INTO without_overlaps_test VALUES ('[2,2]', tsrange('2018-01-01', '2018-01-05')); +INSERT INTO without_overlaps_test VALUES ('[3,3]', tsrange('2018-01-01', NULL)); + +-- should fail: +INSERT INTO without_overlaps_test VALUES ('[1,1]', tsrange('2018-01-01', '2018-01-05')); +INSERT INTO without_overlaps_test VALUES (NULL, tsrange('2018-01-01', '2018-01-05')); +INSERT INTO without_overlaps_test VALUES ('[3,3]', NULL); + +-- +-- test changing the PK's dependencies +-- + +CREATE TABLE without_overlaps_test2 ( + id int4range, + valid_at tsrange, + CONSTRAINT without_overlaps2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); + +ALTER TABLE without_overlaps_test2 ALTER COLUMN valid_at DROP NOT NULL; +ALTER TABLE without_overlaps_test2 ALTER COLUMN valid_at TYPE tstzrange USING tstzrange(lower(valid_at), upper(valid_at)); +ALTER TABLE without_overlaps_test2 RENAME COLUMN valid_at TO valid_thru; +ALTER TABLE without_overlaps_test2 DROP COLUMN valid_thru; +DROP TABLE without_overlaps_test2; -- 2.25.1