From 26bb9452a86b2293f26806cbbde13bbe1a15adee Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Mon, 28 Jun 2021 17:33:27 -0700 Subject: [PATCH v12 1/5] Add temporal PRIMARY KEY and UNIQUE constraints - Added WITHOUT OVERLAPS to the bison grammar. We permit either range columns but not yets PERIODs (which we'll add soon). - 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. - Added pg_constraint.contemporal to say whether a constraint is a temporal constraint. - Added docs and tests. - Added pg_dump support. --- doc/src/sgml/ref/create_table.sgml | 38 ++- src/backend/catalog/heap.c | 1 + src/backend/catalog/index.c | 17 +- src/backend/catalog/pg_constraint.c | 2 + src/backend/commands/indexcmds.c | 137 +++++++- src/backend/commands/tablecmds.c | 14 +- src/backend/commands/trigger.c | 1 + src/backend/commands/typecmds.c | 1 + src/backend/nodes/makefuncs.c | 3 +- src/backend/parser/gram.y | 32 +- src/backend/parser/parse_utilcmd.c | 120 ++++++- src/backend/utils/adt/ruleutils.c | 29 +- src/backend/utils/cache/relcache.c | 20 +- src/bin/pg_dump/pg_dump.c | 34 +- src/bin/pg_dump/pg_dump.h | 1 + src/bin/pg_dump/t/002_pg_dump.pl | 46 +++ src/bin/psql/describe.c | 12 +- src/include/catalog/index.h | 1 + src/include/catalog/pg_constraint.h | 9 +- src/include/commands/defrem.h | 3 +- src/include/nodes/execnodes.h | 2 + src/include/nodes/makefuncs.h | 2 +- src/include/nodes/parsenodes.h | 4 + .../regress/expected/without_overlaps.out | 296 ++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/without_overlaps.sql | 233 ++++++++++++++ 26 files changed, 997 insertions(+), 63 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/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 10ef699fab..58c77d09b4 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -77,8 +77,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI [ CONSTRAINT constraint_name ] { CHECK ( expression ) [ NO INHERIT ] | - UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters | - PRIMARY KEY ( column_name [, ... ] ) index_parameters | + UNIQUE [ NULLS [ NOT ] DISTINCT ] ( 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 + referential_action in a FOREIGN KEY/REFERENCES constraint is: { NO ACTION | RESTRICT | CASCADE | SET NULL [ ( column_name [, ... ] ) ] | SET DEFAULT [ ( column_name [, ... ] ) ] } @@ -1000,7 +1004,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. @@ -1018,7 +1025,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) @@ -1052,8 +1060,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. @@ -1066,6 +1074,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 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/heap.c b/src/backend/catalog/heap.c index 2a0d82aedd..b67fb8400c 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -2153,6 +2153,7 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr, is_local, /* conislocal */ inhcount, /* coninhcount */ is_no_inherit, /* connoinherit */ + false, /* contemporal */ is_internal); /* internally constructed? */ pfree(ccbin); diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 67b743e251..8715231605 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -255,8 +255,8 @@ index_check_primary_key(Relation heapRel, (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. */ + if (attnum <= 0) continue; atttuple = SearchSysCache2(ATTNUM, @@ -1381,7 +1381,8 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, oldInfo->ii_NullsNotDistinct, false, /* not ready for inserts */ true, - indexRelation->rd_indam->amsummarizing); + indexRelation->rd_indam->amsummarizing, + oldInfo->ii_Temporal); /* * Extract the list of column names and the column numbers for the new @@ -1901,6 +1902,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 */ @@ -1924,11 +1926,13 @@ index_constraint_create(Relation heapRelation, 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()); @@ -2005,6 +2009,7 @@ index_constraint_create(Relation heapRelation, islocal, inhcount, noinherit, + is_temporal, /* contemporal */ is_internal); /* @@ -2454,7 +2459,8 @@ BuildIndexInfo(Relation index) indexStruct->indnullsnotdistinct, indexStruct->indisready, false, - index->rd_indam->amsummarizing); + index->rd_indam->amsummarizing, + false); /* fill in attribute numbers */ for (i = 0; i < numAtts; i++) @@ -2515,7 +2521,8 @@ BuildDummyIndexInfo(Relation index) indexStruct->indnullsnotdistinct, indexStruct->indisready, false, - index->rd_indam->amsummarizing); + index->rd_indam->amsummarizing, + false); /* fill in attribute numbers */ for (i = 0; i < numAtts; i++) diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c index 4002317f70..edce9f4b75 100644 --- a/src/backend/catalog/pg_constraint.c +++ b/src/backend/catalog/pg_constraint.c @@ -77,6 +77,7 @@ CreateConstraintEntry(const char *constraintName, bool conIsLocal, int conInhCount, bool conNoInherit, + bool conTemporal, bool is_internal) { Relation conDesc; @@ -192,6 +193,7 @@ CreateConstraintEntry(const char *constraintName, values[Anum_pg_constraint_conislocal - 1] = BoolGetDatum(conIsLocal); values[Anum_pg_constraint_coninhcount - 1] = Int16GetDatum(conInhCount); values[Anum_pg_constraint_connoinherit - 1] = BoolGetDatum(conNoInherit); + values[Anum_pg_constraint_contemporal - 1] = BoolGetDatum(conTemporal); if (conkeyArray) values[Anum_pg_constraint_conkey - 1] = PointerGetDatum(conkeyArray); diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 403f5fc143..7c47884e9c 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -74,6 +74,11 @@ /* non-export function prototypes */ static bool CompareOpclassOptions(Datum *opts1, Datum *opts2, int natts); static void CheckPredicate(Expr *predicate); +static void get_index_attr_temporal_operator(Oid opclass, + Oid atttype, + bool isoverlaps, + Oid *opid, + int *strat); static void ComputeIndexAttrs(IndexInfo *indexInfo, Oid *typeOidP, Oid *collationOidP, @@ -85,6 +90,7 @@ static void ComputeIndexAttrs(IndexInfo *indexInfo, const char *accessMethodName, Oid accessMethodId, bool amcanorder, bool isconstraint, + bool istemporal, Oid ddl_userid, int ddl_sec_context, int *ddl_save_nestlevel); @@ -142,6 +148,7 @@ typedef struct ReindexErrorInfo * to index on. * 'exclusionOpNames': list of names of exclusion-constraint operators, * or NIL if not an exclusion constraint. + * 'istemporal': true iff this index has a WITHOUT OVERLAPS clause. * * This is tailored to the needs of ALTER TABLE ALTER TYPE, which recreates * any indexes that depended on a changing column from their pg_get_indexdef @@ -171,7 +178,8 @@ bool CheckIndexCompatible(Oid oldId, const char *accessMethodName, List *attributeList, - List *exclusionOpNames) + List *exclusionOpNames, + bool istemporal) { bool isconstraint; Oid *typeObjectId; @@ -234,7 +242,7 @@ CheckIndexCompatible(Oid oldId, */ indexInfo = makeIndexInfo(numberOfAttributes, numberOfAttributes, accessMethodId, NIL, NIL, false, false, - false, false, amsummarizing); + false, false, amsummarizing, false); typeObjectId = palloc_array(Oid, numberOfAttributes); collationObjectId = palloc_array(Oid, numberOfAttributes); classObjectId = palloc_array(Oid, numberOfAttributes); @@ -244,8 +252,8 @@ CheckIndexCompatible(Oid oldId, coloptions, attributeList, exclusionOpNames, relationId, accessMethodName, accessMethodId, - amcanorder, isconstraint, InvalidOid, 0, NULL); - + amcanorder, isconstraint, istemporal, InvalidOid, + 0, NULL); /* Get the soon-obsolete pg_index tuple. */ tuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(oldId)); @@ -847,7 +855,7 @@ DefineIndex(Oid relationId, pgstat_progress_update_param(PROGRESS_CREATEIDX_ACCESS_METHOD_OID, accessMethodId); - if (stmt->unique && !amRoutine->amcanunique) + if (stmt->unique && !stmt->istemporal && !amRoutine->amcanunique) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("access method \"%s\" does not support unique indexes", @@ -903,7 +911,8 @@ DefineIndex(Oid relationId, stmt->nulls_not_distinct, !concurrent, concurrent, - amissummarizing); + amissummarizing, + stmt->istemporal); typeObjectId = palloc_array(Oid, numberOfAttributes); collationObjectId = palloc_array(Oid, numberOfAttributes); @@ -914,8 +923,9 @@ DefineIndex(Oid relationId, coloptions, allIndexParams, stmt->excludeOpNames, relationId, accessMethodName, accessMethodId, - amcanorder, stmt->isconstraint, root_save_userid, - root_save_sec_context, &root_save_nestlevel); + amcanorder, stmt->isconstraint, stmt->istemporal, + root_save_userid, root_save_sec_context, + &root_save_nestlevel); /* * Extra checks when creating a PRIMARY KEY index. @@ -940,6 +950,8 @@ DefineIndex(Oid relationId, if (stmt->primary) constraint_type = "PRIMARY KEY"; + else if (stmt->unique && stmt->istemporal) + constraint_type = "temporal UNIQUE"; else if (stmt->unique) constraint_type = "UNIQUE"; else if (stmt->excludeOpNames != NIL) @@ -1159,6 +1171,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, @@ -1828,6 +1842,91 @@ CheckPredicate(Expr *predicate) errmsg("functions in index predicate must be marked IMMUTABLE"))); } +/* + * get_index_attr_temporal_operator + * + * Finds an operator for a temporal index attribute. + * We need an equality operator for normal keys + * and an overlaps operator for the range. + * Returns the operator oid and strategy in opid and strat, + * respectively. + */ +static void +get_index_attr_temporal_operator(Oid opclass, + Oid atttype, + bool isoverlaps, + Oid *opid, + int *strat) +{ + Oid opfamily; + const char *opname; + + opfamily = get_opclass_family(opclass); + /* + * If we have a range type, fall back on anyrange. + * This seems like a hack + * but I can't find any existing lookup function + * that knows about pseudotypes. + * compatible_oper is close but wants a *name*, + * and the point here is to avoid hardcoding a name + * (although it should always be = and &&). + * + * In addition for the normal key elements + * try both RTEqualStrategyNumber and BTEqualStrategyNumber. + * If you're using btree_gist then you'll need the latter. + */ + if (isoverlaps) + { + *strat = RTOverlapStrategyNumber; + opname = "overlaps"; + *opid = get_opfamily_member(opfamily, atttype, atttype, *strat); + if (!OidIsValid(*opid) && type_is_range(atttype)) + *opid = get_opfamily_member(opfamily, ANYRANGEOID, ANYRANGEOID, *strat); + } + else + { + *strat = RTEqualStrategyNumber; + opname = "equality"; + *opid = get_opfamily_member(opfamily, atttype, atttype, *strat); + if (!OidIsValid(*opid) && type_is_range(atttype)) + *opid = get_opfamily_member(opfamily, ANYRANGEOID, ANYRANGEOID, *strat); + + if (!OidIsValid(*opid)) + { + *strat = BTEqualStrategyNumber; + *opid = get_opfamily_member(opfamily, atttype, atttype, *strat); + if (!OidIsValid(*opid) && type_is_range(atttype)) + *opid = get_opfamily_member(opfamily, ANYRANGEOID, ANYRANGEOID, *strat); + } + } + + if (!OidIsValid(*opid)) + { + HeapTuple opftuple; + Form_pg_opfamily opfform; + + /* + * attribute->opclass might not explicitly name the opfamily, + * so fetch the name of the selected opfamily for use in the + * error message. + */ + opftuple = SearchSysCache1(OPFAMILYOID, + ObjectIdGetDatum(opfamily)); + if (!HeapTupleIsValid(opftuple)) + elog(ERROR, "cache lookup failed for opfamily %u", + opfamily); + opfform = (Form_pg_opfamily) GETSTRUCT(opftuple); + + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("no %s operator found for WITHOUT OVERLAPS constraint", opname), + errdetail("There must be an %s operator within opfamily \"%s\" for type \"%s\".", + opname, + NameStr(opfform->opfname), + format_type_be(atttype)))); + } +} + /* * Compute per-index-column information, including indexed column numbers * or index expressions, opclasses and their options. Note, all output vectors @@ -1850,6 +1949,7 @@ ComputeIndexAttrs(IndexInfo *indexInfo, Oid accessMethodId, bool amcanorder, bool isconstraint, + bool istemporal, Oid ddl_userid, int ddl_sec_context, int *ddl_save_nestlevel) @@ -1873,6 +1973,14 @@ ComputeIndexAttrs(IndexInfo *indexInfo, else nextExclOp = NULL; + if (istemporal) + { + Assert(exclusionOpNames == NIL); + indexInfo->ii_ExclusionOps = palloc_array(Oid, nkeycols); + indexInfo->ii_ExclusionProcs = palloc_array(Oid, nkeycols); + indexInfo->ii_ExclusionStrats = palloc_array(uint16, nkeycols); + } + if (OidIsValid(ddl_userid)) GetUserIdAndSecContext(&save_userid, &save_sec_context); @@ -2148,6 +2256,19 @@ ComputeIndexAttrs(IndexInfo *indexInfo, indexInfo->ii_ExclusionStrats[attn] = strat; nextExclOp = lnext(exclusionOpNames, nextExclOp); } + else if (istemporal) + { + int strat; + Oid opid; + get_index_attr_temporal_operator(classOidP[attn], + atttype, + attn == nkeycols - 1, /* TODO: Don't assume it's last? */ + &opid, + &strat); + indexInfo->ii_ExclusionOps[attn] = opid; + indexInfo->ii_ExclusionProcs[attn] = get_opcode(opid); + indexInfo->ii_ExclusionStrats[attn] = strat; + } /* * Set up the per-column options (indoption field). For now, this is diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index fce5e6f220..9e3a88a7dd 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -208,6 +208,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; @@ -9696,6 +9697,7 @@ addFkRecurseReferenced(List **wqueue, Constraint *fkconstraint, Relation rel, conislocal, /* islocal */ coninhcount, /* inhcount */ connoinherit, /* conNoInherit */ + false, /* conTemporal */ false); /* is_internal */ ObjectAddressSet(address, ConstraintRelationId, constrOid); @@ -9994,6 +9996,7 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, false, 1, false, + false, /* conTemporal */ false); /* @@ -10499,6 +10502,7 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel) false, /* islocal */ 1, /* inhcount */ false, /* conNoInherit */ + false, /* conTemporal */ true); /* Set up partition dependencies for the new constraint */ @@ -11173,6 +11177,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 */ @@ -11339,10 +11344,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. */ @@ -13635,7 +13642,8 @@ TryReuseIndex(Oid oldId, IndexStmt *stmt) if (CheckIndexCompatible(oldId, stmt->accessMethod, stmt->indexParams, - stmt->excludeOpNames)) + stmt->excludeOpNames, + stmt->istemporal)) { Relation irel = index_open(oldId, NoLock); diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c index 52177759ab..0577b60415 100644 --- a/src/backend/commands/trigger.c +++ b/src/backend/commands/trigger.c @@ -841,6 +841,7 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString, true, /* islocal */ 0, /* inhcount */ true, /* noinherit */ + false, /* contemporal */ isInternal); /* is_internal */ } diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index 216482095d..4fff196477 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -3544,6 +3544,7 @@ domainAddConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid, true, /* is local */ 0, /* inhcount */ false, /* connoinherit */ + false, /* contemporal */ false); /* is_internal */ if (constrAddr) ObjectAddressSet(*constrAddr, ConstraintRelationId, ccoid); diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c index 39e1884cf4..25e8d914f1 100644 --- a/src/backend/nodes/makefuncs.c +++ b/src/backend/nodes/makefuncs.c @@ -745,7 +745,7 @@ make_ands_implicit(Expr *clause) IndexInfo * makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions, List *predicates, bool unique, bool nulls_not_distinct, - bool isready, bool concurrent, bool summarizing) + bool isready, bool concurrent, bool summarizing, bool temporal) { IndexInfo *n = makeNode(IndexInfo); @@ -755,6 +755,7 @@ makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions, Assert(n->ii_NumIndexKeyAttrs <= n->ii_NumIndexAttrs); n->ii_Unique = unique; n->ii_NullsNotDistinct = nulls_not_distinct; + n->ii_Temporal = temporal; n->ii_ReadyForInserts = isready; n->ii_CheckedUnchanged = false; n->ii_IndexUnchanged = false; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 39ab7eac0d..a5d51e21f8 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -523,7 +523,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 @@ -3875,6 +3876,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; @@ -4081,7 +4083,7 @@ ConstraintElem: n->initially_valid = !n->skip_validation; $$ = (Node *) n; } - | UNIQUE opt_unique_null_treatment '(' columnList ')' opt_c_include opt_definition OptConsTableSpace + | UNIQUE opt_unique_null_treatment '(' columnList withoutOverlapsClause ')' opt_c_include opt_definition OptConsTableSpace ConstraintAttributeSpec { Constraint *n = makeNode(Constraint); @@ -4090,11 +4092,12 @@ ConstraintElem: n->location = @1; n->nulls_not_distinct = !$2; 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, "UNIQUE", + n->indexspace = $9; + processCASbits($10, @10, "UNIQUE", &n->deferrable, &n->initdeferred, NULL, NULL, yyscanner); $$ = (Node *) n; @@ -4115,7 +4118,7 @@ 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); @@ -4123,11 +4126,12 @@ ConstraintElem: 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; @@ -4139,6 +4143,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; @@ -4205,6 +4210,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 d67580fc77..04ca087973 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -123,6 +123,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, @@ -1588,6 +1590,7 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx, index->unique = idxrec->indisunique; index->nulls_not_distinct = idxrec->indnullsnotdistinct; 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; @@ -1637,7 +1640,7 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx, int nElems; int i; - Assert(conrec->contype == CONSTRAINT_EXCLUSION); + Assert(conrec->contype == CONSTRAINT_EXCLUSION || (index->istemporal && conrec->contype == CONSTRAINT_PRIMARY)); /* Extract operator OIDs from the pg_constraint tuple */ datum = SysCacheGetAttrNotNull(CONSTROID, ht_constr, Anum_pg_constraint_conexclop); @@ -2179,6 +2182,7 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) } index->nulls_not_distinct = constraint->nulls_not_distinct; index->isconstraint = true; + index->istemporal = constraint->without_overlaps != NULL; index->deferrable = constraint->deferrable; index->initdeferred = constraint->initdeferred; @@ -2271,6 +2275,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), @@ -2559,6 +2568,66 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) notnullcmds = lappend(notnullcmds, notnullcmd); } } + + /* + * Anything in without_overlaps should be included, + * but with the overlaps operator (&&) instead of equality. + */ + if (constraint->without_overlaps != NULL) { + 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 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 + 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->accessMethod = "gist"; + constraint->access_method = "gist"; + } } /* @@ -2678,6 +2747,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 d3a973d86b..0ef2038e5d 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -338,8 +338,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, 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, @@ -2247,7 +2247,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, val = SysCacheGetAttrNotNull(CONSTROID, tup, Anum_pg_constraint_conkey); - decompile_column_index_array(val, conForm->conrelid, &buf); + decompile_column_index_array(val, conForm->conrelid, conForm->conindid, false, &buf); /* add foreign relation name */ appendStringInfo(&buf, ") REFERENCES %s(", @@ -2258,7 +2258,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, val = SysCacheGetAttrNotNull(CONSTROID, tup, Anum_pg_constraint_confkey); - decompile_column_index_array(val, conForm->confrelid, &buf); + decompile_column_index_array(val, conForm->confrelid, conForm->conindid, false, &buf); appendStringInfoChar(&buf, ')'); @@ -2344,7 +2344,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, if (!isnull) { appendStringInfoString(&buf, " ("); - decompile_column_index_array(val, conForm->conrelid, &buf); + decompile_column_index_array(val, conForm->conrelid, InvalidOid, false, &buf); appendStringInfoChar(&buf, ')'); } @@ -2357,6 +2357,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, Oid indexId; int keyatts; HeapTuple indtup; + bool isnull; /* Start off the constraint definition */ if (conForm->contype == CONSTRAINT_PRIMARY) @@ -2379,7 +2380,14 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, val = SysCacheGetAttrNotNull(CONSTROID, tup, Anum_pg_constraint_conkey); - 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, &buf); appendStringInfoChar(&buf, ')'); @@ -2560,8 +2568,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, StringInfo buf) { Datum *keys; int nKeys; @@ -2574,11 +2582,14 @@ decompile_column_index_array(Datum column_index_array, Oid relId, for (j = 0; j < nKeys; j++) { char *colName; + int colid = DatumGetInt16(keys[j]); - colName = get_attname(relId, DatumGetInt16(keys[j]), false); + 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/relcache.c b/src/backend/utils/cache/relcache.c index 8a08463c2b..98ac1e4c1e 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -4792,11 +4792,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; @@ -5507,8 +5513,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/unique + * constraint. 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. */ @@ -5574,7 +5581,10 @@ RelationGetExclusionInfo(Relation indexRelation, int nelem; /* We want the exclusion constraint owning the index */ - if (conform->contype != CONSTRAINT_EXCLUSION || + if ((conform->contype != CONSTRAINT_EXCLUSION && + !(conform->contemporal && ( + conform->contype == CONSTRAINT_PRIMARY + || conform->contype == CONSTRAINT_UNIQUE))) || conform->conindid != RelationGetRelid(indexRelation)) continue; diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 5dab1ba9ea..a95e1d3696 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -6964,7 +6964,8 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) i_tablespace, i_indreloptions, i_indstatcols, - i_indstatvals; + i_indstatvals, + i_withoutoverlaps; /* * We want to perform just one query against pg_index. However, we @@ -7028,21 +7029,23 @@ 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, "); else appendPQExpBufferStr(query, "0 AS parentidx, " "i.indnatts AS indnkeyatts, " "i.indnatts AS indnatts, " "'' AS indstatcols, " - "'' AS indstatvals, "); + "'' AS indstatvals, " + "null AS withoutoverlaps, "); if (fout->remoteVersion >= 150000) appendPQExpBufferStr(query, - "i.indnullsnotdistinct "); + "i.indnullsnotdistinct, "); else appendPQExpBufferStr(query, - "false AS indnullsnotdistinct "); + "false AS indnullsnotdistinct, "); /* * The point of the messy-looking outer join is to find a constraint that @@ -7117,6 +7120,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"); indxinfo = (IndxInfo *) pg_malloc(ntups * sizeof(IndxInfo)); @@ -7219,6 +7223,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; } @@ -16657,9 +16662,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 bc8f2ec36d..2afb5d5294 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -484,6 +484,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 _procLangInfo diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index 63bb4689d4..095a3e4d25 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -1015,6 +1015,52 @@ my %tests = ( }, }, + 'ALTER TABLE ONLY test_table_tpk 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, + only_dump_measurement => 1, + }, + }, + + 'ALTER TABLE ONLY test_table_tuq ADD CONSTRAINT ... UNIQUE (..., ... WITHOUT OVERLAPS)' => { + create_sql => 'CREATE TABLE dump_test.test_table_tuq ( + col1 int4range, + col2 tstzrange, + CONSTRAINT test_table_tuq_uq UNIQUE + (col1, col2 WITHOUT OVERLAPS));', + regexp => qr/^ + \QALTER TABLE ONLY dump_test.test_table_tuq\E \n^\s+ + \QADD CONSTRAINT test_table_tuq_uq UNIQUE (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, + only_dump_measurement => 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 9325a46b8f..2846489eb0 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -2384,6 +2384,10 @@ describeOneTableDetails(const char *schemaname, else appendPQExpBufferStr(&buf, ", false AS indisreplident"); appendPQExpBufferStr(&buf, ", c2.reltablespace"); + if (pset.sversion >= 160000) + appendPQExpBufferStr(&buf, ", con.contemporal"); + else + appendPQExpBufferStr(&buf, ", false AS contemporal"); appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n" " LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))\n" @@ -2405,8 +2409,12 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, " \"%s\"", PQgetvalue(result, i, 0)); - /* If exclusion constraint, print the constraintdef */ - if (strcmp(PQgetvalue(result, i, 7), "x") == 0) + /* + * If exclusion constraint or temporal PK/UNIQUE constraint, + * print the constraintdef + */ + if (strcmp(PQgetvalue(result, i, 7), "x") == 0 || + strcmp(PQgetvalue(result, i, 12), "t") == 0) { appendPQExpBuffer(&buf, " %s", PQgetvalue(result, i, 6)); diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index c8532fb97c..7afbc8cfc4 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 16bf5f5576..67048e3006 100644 --- a/src/include/catalog/pg_constraint.h +++ b/src/include/catalog/pg_constraint.h @@ -107,6 +107,12 @@ 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; + #ifdef CATALOG_VARLEN /* variable-length fields start here */ /* @@ -146,7 +152,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); @@ -235,6 +241,7 @@ extern Oid CreateConstraintEntry(const char *constraintName, bool conIsLocal, int conInhCount, bool conNoInherit, + bool conTemporal, bool is_internal); extern void RemoveConstraintById(Oid conId); diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h index 478203ed4c..9a21717a3e 100644 --- a/src/include/commands/defrem.h +++ b/src/include/commands/defrem.h @@ -44,7 +44,8 @@ extern char *ChooseRelationName(const char *name1, const char *name2, extern bool CheckIndexCompatible(Oid oldId, const char *accessMethodName, List *attributeList, - List *exclusionOpNames); + List *exclusionOpNames, + bool istemporal); 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 cb714f4a19..2327b55f15 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -155,6 +155,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? * CheckedUnchanged IndexUnchanged status determined yet? @@ -190,6 +191,7 @@ typedef struct IndexInfo Datum *ii_OpclassOptions; /* array with one entry per column */ bool ii_Unique; bool ii_NullsNotDistinct; + bool ii_Temporal; bool ii_ReadyForInserts; bool ii_CheckedUnchanged; bool ii_IndexUnchanged; diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h index 06d991b725..4ed3af1864 100644 --- a/src/include/nodes/makefuncs.h +++ b/src/include/nodes/makefuncs.h @@ -98,7 +98,7 @@ extern IndexInfo *makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions, List *predicates, bool unique, bool nulls_not_distinct, bool isready, bool concurrent, - bool summarizing); + bool summarizing, bool temporal); 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 88b03cc472..76ad76ddd7 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2601,6 +2601,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 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? */ @@ -3202,6 +3205,7 @@ typedef struct IndexStmt bool nulls_not_distinct; /* null treatment for UNIQUE constraints */ 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/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out new file mode 100644 index 0000000000..c911c716ca --- /dev/null +++ b/src/test/regress/expected/without_overlaps.out @@ -0,0 +1,296 @@ +-- Tests for WITHOUT OVERLAPS. +-- +-- We leave behind several tables to test pg_dump etc: +-- temporal_rng, temporal_rng2, +-- temporal_fk_rng2rng. +-- +-- test input parser +-- +-- PK with no columns just WITHOUT OVERLAPS: +CREATE TABLE temporal_rng ( + valid_at tsrange, + CONSTRAINT temporal_rng_pk PRIMARY KEY (valid_at WITHOUT OVERLAPS) +); +ERROR: syntax error at or near "WITHOUT" +LINE 3: CONSTRAINT temporal_rng_pk PRIMARY KEY (valid_at WITHOUT OV... + ^ +-- PK with a range column/PERIOD that isn't there: +CREATE TABLE temporal_rng ( + id INTEGER, + CONSTRAINT temporal_rng_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 temporal_rng ( + id INTEGER, + valid_at TEXT, + CONSTRAINT temporal_rng_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 temporal_rng ( + -- 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 temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +\d temporal_rng + Table "public.temporal_rng" + Column | Type | Collation | Nullable | Default +----------+-----------+-----------+----------+--------- + id | int4range | | not null | + valid_at | tsrange | | not null | +Indexes: + "temporal_rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) + +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng_pk'; + pg_get_constraintdef +--------------------------------------------- + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +(1 row) + +SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng_pk'; + pg_get_indexdef +------------------------------------------------------------------------------- + CREATE UNIQUE INDEX temporal_rng_pk ON temporal_rng USING gist (id, valid_at) +(1 row) + +-- PK with two columns plus a range: +CREATE TABLE temporal_rng2 ( + id1 int4range, + id2 int4range, + valid_at tsrange, + CONSTRAINT temporal_rng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) +); +\d temporal_rng2 + Table "public.temporal_rng2" + Column | Type | Collation | Nullable | Default +----------+-----------+-----------+----------+--------- + id1 | int4range | | not null | + id2 | int4range | | not null | + valid_at | tsrange | | not null | +Indexes: + "temporal_rng2_pk" PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) + +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng2_pk'; + pg_get_constraintdef +--------------------------------------------------- + PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) +(1 row) + +SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng2_pk'; + pg_get_indexdef +--------------------------------------------------------------------------------------- + CREATE UNIQUE INDEX temporal_rng2_pk ON temporal_rng2 USING gist (id1, id2, valid_at) +(1 row) + +DROP TABLE temporal_rng2; +-- PK with a custom range type: +CREATE TYPE textrange2 AS range (subtype=text, collation="C"); +CREATE TABLE temporal_rng2 ( + id int4range, + valid_at textrange2, + CONSTRAINT temporal_rng2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +ALTER TABLE temporal_rng2 DROP CONSTRAINT temporal_rng2_pk; +DROP TABLE temporal_rng2; +DROP TYPE textrange2; +-- UNIQUE with no columns just WITHOUT OVERLAPS: +CREATE TABLE temporal_rng2 ( + valid_at tsrange, + CONSTRAINT temporal_rng2_uq UNIQUE (valid_at WITHOUT OVERLAPS) +); +ERROR: syntax error at or near "WITHOUT" +LINE 3: CONSTRAINT temporal_rng2_uq UNIQUE (valid_at WITHOUT OVERLA... + ^ +-- UNIQUE with a range column/PERIOD that isn't there: +CREATE TABLE temporal_rng2 ( + id INTEGER, + CONSTRAINT temporal_rng2_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 temporal_rng2 ( + id INTEGER, + valid_at TEXT, + CONSTRAINT temporal_rng2_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 temporal_rng2 ( + -- 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 temporal_rng2_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) +); +\d temporal_rng2 + Table "public.temporal_rng2" + Column | Type | Collation | Nullable | Default +----------+-----------+-----------+----------+--------- + id | int4range | | | + valid_at | tsrange | | not null | +Indexes: + "temporal_rng2_uq" UNIQUE (id, valid_at WITHOUT OVERLAPS) + +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng2_uq'; + pg_get_constraintdef +---------------------------------------- + UNIQUE (id, valid_at WITHOUT OVERLAPS) +(1 row) + +SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng2_uq'; + pg_get_indexdef +--------------------------------------------------------------------------------- + CREATE UNIQUE INDEX temporal_rng2_uq ON temporal_rng2 USING gist (id, valid_at) +(1 row) + +-- UNIQUE with two columns plus a range: +CREATE TABLE temporal_rng3 ( + id1 int4range, + id2 int4range, + valid_at tsrange, + CONSTRAINT temporal_rng3_uq UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS) +); +\d temporal_rng3 + Table "public.temporal_rng3" + Column | Type | Collation | Nullable | Default +----------+-----------+-----------+----------+--------- + id1 | int4range | | | + id2 | int4range | | | + valid_at | tsrange | | not null | +Indexes: + "temporal_rng3_uq" UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS) + +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng3_uq'; + pg_get_constraintdef +---------------------------------------------- + UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS) +(1 row) + +SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng3_uq'; + pg_get_indexdef +--------------------------------------------------------------------------------------- + CREATE UNIQUE INDEX temporal_rng3_uq ON temporal_rng3 USING gist (id1, id2, valid_at) +(1 row) + +DROP TABLE temporal_rng3; +-- +-- test ALTER TABLE ADD CONSTRAINT +-- +DROP TABLE temporal_rng; +CREATE TABLE temporal_rng ( + id int4range, + valid_at tsrange +); +ALTER TABLE temporal_rng + ADD CONSTRAINT temporal_rng_pk + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +-- PK with USING INDEX (not possible): +CREATE TABLE temporal3 ( + id int4range, + valid_at tsrange +); +CREATE INDEX idx_temporal3_uq ON temporal3 USING gist (id, valid_at); +ALTER TABLE temporal3 + ADD CONSTRAINT temporal3_pk + PRIMARY KEY USING INDEX idx_temporal3_uq; +ERROR: "idx_temporal3_uq" is not a unique index +LINE 2: ADD CONSTRAINT temporal3_pk + ^ +DETAIL: Cannot create a primary key or unique constraint using such an index. +DROP TABLE temporal3; +-- UNIQUE with USING INDEX (not possible): +CREATE TABLE temporal3 ( + id int4range, + valid_at tsrange +); +CREATE INDEX idx_temporal3_uq ON temporal3 USING gist (id, valid_at); +ALTER TABLE temporal3 + ADD CONSTRAINT temporal3_uq + UNIQUE USING INDEX idx_temporal3_uq; +ERROR: "idx_temporal3_uq" is not a unique index +LINE 2: ADD CONSTRAINT temporal3_uq + ^ +DETAIL: Cannot create a primary key or unique constraint using such an index. +DROP TABLE temporal3; +-- Add range column and the PK at the same time +CREATE TABLE temporal3 ( + id int4range +); +ALTER TABLE temporal3 + ADD COLUMN valid_at tsrange, + ADD CONSTRAINT temporal3_pk + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +DROP TABLE temporal3; +-- Add range column and UNIQUE constraint at the same time +CREATE TABLE temporal3 ( + id int4range +); +ALTER TABLE temporal3 + ADD COLUMN valid_at tsrange, + ADD CONSTRAINT temporal3_uq + UNIQUE (id, valid_at WITHOUT OVERLAPS); +DROP TABLE temporal3; +-- +-- test PK inserts +-- +-- okay: +INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-01-02', '2018-02-03')); +INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-03-03', '2018-04-04')); +INSERT INTO temporal_rng VALUES ('[2,2]', tsrange('2018-01-01', '2018-01-05')); +INSERT INTO temporal_rng VALUES ('[3,3]', tsrange('2018-01-01', NULL)); +-- should fail: +INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-01-01', '2018-01-05')); +ERROR: conflicting key value violates exclusion constraint "temporal_rng_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 temporal_rng VALUES (NULL, tsrange('2018-01-01', '2018-01-05')); +ERROR: null value in column "id" of relation "temporal_rng" 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 temporal_rng VALUES ('[3,3]', NULL); +ERROR: null value in column "valid_at" of relation "temporal_rng" violates not-null constraint +DETAIL: Failing row contains ([3,4), null). +-- +-- test a range with both a PK and a UNIQUE constraint +-- +CREATE TABLE temporal3 ( + id int4range, + valid_at daterange, + id2 int8range, + name TEXT, + CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal3_uniq UNIQUE (id2, valid_at WITHOUT OVERLAPS) +); +INSERT INTO temporal3 (id, valid_at, id2, name) + VALUES + ('[1,1]', daterange('2000-01-01', '2010-01-01'), '[7,7]', 'foo'), + ('[2,2]', daterange('2000-01-01', '2010-01-01'), '[9,9]', 'bar') +; +DROP TABLE temporal3; +-- +-- test changing the PK's dependencies +-- +CREATE TABLE temporal3 ( + id int4range, + valid_at tsrange, + CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +ALTER TABLE temporal3 ALTER COLUMN valid_at DROP NOT NULL; +ERROR: column "valid_at" is in a primary key +ALTER TABLE temporal3 ALTER COLUMN valid_at TYPE tstzrange USING tstzrange(lower(valid_at), upper(valid_at)); +ALTER TABLE temporal3 RENAME COLUMN valid_at TO valid_thru; +ALTER TABLE temporal3 DROP COLUMN valid_thru; +DROP TABLE temporal3; +-- +-- test PARTITION BY for ranges +-- +CREATE TABLE temporal_partitioned ( + id int4range, + valid_at daterange, + CONSTRAINT temporal_paritioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +) PARTITION BY LIST (id); +ERROR: cannot match partition key to an index using access method "gist" +-- TODO: attach some partitions, insert into them, update them with and without FOR PORTION OF, delete them the same way. diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index cf46fa3359..b02b8dd4f6 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -28,7 +28,7 @@ test: strings md5 numerology point lseg line box path polygon circle date time t # geometry depends on point, lseg, line, box, path, polygon, circle # horology depends on date, time, timetz, timestamp, timestamptz, interval # ---------- -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 # ---------- # Load huge amounts of data diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql new file mode 100644 index 0000000000..41a094f580 --- /dev/null +++ b/src/test/regress/sql/without_overlaps.sql @@ -0,0 +1,233 @@ +-- Tests for WITHOUT OVERLAPS. +-- +-- We leave behind several tables to test pg_dump etc: +-- temporal_rng, temporal_rng2, +-- temporal_fk_rng2rng. + +-- +-- test input parser +-- + +-- PK with no columns just WITHOUT OVERLAPS: + +CREATE TABLE temporal_rng ( + valid_at tsrange, + CONSTRAINT temporal_rng_pk PRIMARY KEY (valid_at WITHOUT OVERLAPS) +); + +-- PK with a range column/PERIOD that isn't there: + +CREATE TABLE temporal_rng ( + id INTEGER, + CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); + +-- PK with a non-range column: + +CREATE TABLE temporal_rng ( + id INTEGER, + valid_at TEXT, + CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); + +-- PK with one column plus a range: + +CREATE TABLE temporal_rng ( + -- 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 temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +\d temporal_rng +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng_pk'; +SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng_pk'; + +-- PK with two columns plus a range: +CREATE TABLE temporal_rng2 ( + id1 int4range, + id2 int4range, + valid_at tsrange, + CONSTRAINT temporal_rng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) +); +\d temporal_rng2 +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng2_pk'; +SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng2_pk'; +DROP TABLE temporal_rng2; + + +-- PK with a custom range type: +CREATE TYPE textrange2 AS range (subtype=text, collation="C"); +CREATE TABLE temporal_rng2 ( + id int4range, + valid_at textrange2, + CONSTRAINT temporal_rng2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +ALTER TABLE temporal_rng2 DROP CONSTRAINT temporal_rng2_pk; +DROP TABLE temporal_rng2; +DROP TYPE textrange2; + +-- UNIQUE with no columns just WITHOUT OVERLAPS: + +CREATE TABLE temporal_rng2 ( + valid_at tsrange, + CONSTRAINT temporal_rng2_uq UNIQUE (valid_at WITHOUT OVERLAPS) +); + +-- UNIQUE with a range column/PERIOD that isn't there: + +CREATE TABLE temporal_rng2 ( + id INTEGER, + CONSTRAINT temporal_rng2_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) +); + +-- UNIQUE with a non-range column: + +CREATE TABLE temporal_rng2 ( + id INTEGER, + valid_at TEXT, + CONSTRAINT temporal_rng2_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) +); + +-- UNIQUE with one column plus a range: + +CREATE TABLE temporal_rng2 ( + -- 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 temporal_rng2_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) +); +\d temporal_rng2 +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng2_uq'; +SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng2_uq'; + +-- UNIQUE with two columns plus a range: +CREATE TABLE temporal_rng3 ( + id1 int4range, + id2 int4range, + valid_at tsrange, + CONSTRAINT temporal_rng3_uq UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS) +); +\d temporal_rng3 +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng3_uq'; +SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng3_uq'; +DROP TABLE temporal_rng3; + +-- +-- test ALTER TABLE ADD CONSTRAINT +-- + +DROP TABLE temporal_rng; +CREATE TABLE temporal_rng ( + id int4range, + valid_at tsrange +); +ALTER TABLE temporal_rng + ADD CONSTRAINT temporal_rng_pk + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); + +-- PK with USING INDEX (not possible): +CREATE TABLE temporal3 ( + id int4range, + valid_at tsrange +); +CREATE INDEX idx_temporal3_uq ON temporal3 USING gist (id, valid_at); +ALTER TABLE temporal3 + ADD CONSTRAINT temporal3_pk + PRIMARY KEY USING INDEX idx_temporal3_uq; +DROP TABLE temporal3; + +-- UNIQUE with USING INDEX (not possible): +CREATE TABLE temporal3 ( + id int4range, + valid_at tsrange +); +CREATE INDEX idx_temporal3_uq ON temporal3 USING gist (id, valid_at); +ALTER TABLE temporal3 + ADD CONSTRAINT temporal3_uq + UNIQUE USING INDEX idx_temporal3_uq; +DROP TABLE temporal3; + +-- Add range column and the PK at the same time +CREATE TABLE temporal3 ( + id int4range +); +ALTER TABLE temporal3 + ADD COLUMN valid_at tsrange, + ADD CONSTRAINT temporal3_pk + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +DROP TABLE temporal3; + +-- Add range column and UNIQUE constraint at the same time +CREATE TABLE temporal3 ( + id int4range +); +ALTER TABLE temporal3 + ADD COLUMN valid_at tsrange, + ADD CONSTRAINT temporal3_uq + UNIQUE (id, valid_at WITHOUT OVERLAPS); +DROP TABLE temporal3; + +-- +-- test PK inserts +-- + +-- okay: +INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-01-02', '2018-02-03')); +INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-03-03', '2018-04-04')); +INSERT INTO temporal_rng VALUES ('[2,2]', tsrange('2018-01-01', '2018-01-05')); +INSERT INTO temporal_rng VALUES ('[3,3]', tsrange('2018-01-01', NULL)); + +-- should fail: +INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-01-01', '2018-01-05')); +INSERT INTO temporal_rng VALUES (NULL, tsrange('2018-01-01', '2018-01-05')); +INSERT INTO temporal_rng VALUES ('[3,3]', NULL); + +-- +-- test a range with both a PK and a UNIQUE constraint +-- + +CREATE TABLE temporal3 ( + id int4range, + valid_at daterange, + id2 int8range, + name TEXT, + CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal3_uniq UNIQUE (id2, valid_at WITHOUT OVERLAPS) +); +INSERT INTO temporal3 (id, valid_at, id2, name) + VALUES + ('[1,1]', daterange('2000-01-01', '2010-01-01'), '[7,7]', 'foo'), + ('[2,2]', daterange('2000-01-01', '2010-01-01'), '[9,9]', 'bar') +; +DROP TABLE temporal3; + +-- +-- test changing the PK's dependencies +-- + +CREATE TABLE temporal3 ( + id int4range, + valid_at tsrange, + CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); + +ALTER TABLE temporal3 ALTER COLUMN valid_at DROP NOT NULL; +ALTER TABLE temporal3 ALTER COLUMN valid_at TYPE tstzrange USING tstzrange(lower(valid_at), upper(valid_at)); +ALTER TABLE temporal3 RENAME COLUMN valid_at TO valid_thru; +ALTER TABLE temporal3 DROP COLUMN valid_thru; +DROP TABLE temporal3; + +-- +-- test PARTITION BY for ranges +-- + +CREATE TABLE temporal_partitioned ( + id int4range, + valid_at daterange, + CONSTRAINT temporal_paritioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +) PARTITION BY LIST (id); +-- TODO: attach some partitions, insert into them, update them with and without FOR PORTION OF, delete them the same way. -- 2.32.0 (Apple Git-132)