From c612caff74c665c059e9f935a08d345be4f1e474 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Sat, 1 Jun 2024 15:24:56 -0700 Subject: [PATCH v34 3/8] Forbid empty ranges/multiranges in WITHOUT OVERLAPS columns Because 'empty' && 'empty' is false, the temporal PK/UQ constraint allows duplicates, which is confusing to users and breaks internal expectations. For instance when GROUP BY checks functional dependencies on the PK, it allows selecting other columns from the table, but in the presence of duplicate keys you could get the value from any of their rows. So we need to forbid empties. Added pg_class.relperiods to track the number of WITHOUT OVERLAPS index constraints on the table (similar to relchecks). Increment relperiods when a WITHOUT OVERLAPS constraint is added, and decrement it when removed. In the relcache, if relperiods > 0 then get the attnos of all columns used as the WITHOUT OVERLAPS part of a constraint. Check for empty values in WITHOUT OVERLAPS columns when recording an inserted/updated row. Check for already-existing empties when adding a new temporal PK/UQ. This all means we can only support ranges and multiranges for temporal PK/UQs. So I added a check and updated the docs and tests. --- doc/src/sgml/gist.sgml | 3 - doc/src/sgml/ref/create_table.sgml | 15 +-- src/backend/access/common/tupdesc.c | 20 +++ src/backend/catalog/heap.c | 35 +++++ src/backend/catalog/index.c | 56 +++++++- src/backend/catalog/pg_constraint.c | 33 +++++ src/backend/commands/indexcmds.c | 5 +- src/backend/executor/execIndexing.c | 35 +++++ src/backend/executor/execMain.c | 29 ++++ src/backend/nodes/makefuncs.c | 4 +- src/backend/parser/parse_utilcmd.c | 44 +++++- src/backend/utils/cache/relcache.c | 113 +++++++++++++++- src/include/access/tupdesc.h | 2 + src/include/catalog/heap.h | 1 + src/include/catalog/pg_class.h | 3 + src/include/executor/executor.h | 2 + src/include/nodes/execnodes.h | 1 + src/include/nodes/makefuncs.h | 2 +- .../regress/expected/without_overlaps.out | 126 +++++++++++++++++- src/test/regress/sql/without_overlaps.sql | 68 ++++++++++ 20 files changed, 571 insertions(+), 26 deletions(-) diff --git a/doc/src/sgml/gist.sgml b/doc/src/sgml/gist.sgml index dcf9433fa78..638d912dc2d 100644 --- a/doc/src/sgml/gist.sgml +++ b/doc/src/sgml/gist.sgml @@ -1186,9 +1186,6 @@ my_sortsupport(PG_FUNCTION_ARGS) provides this function and it returns results for RTEqualStrategyNumber, it can be used in the non-WITHOUT OVERLAPS part(s) of an index constraint. - If it returns results for RTOverlapStrategyNumber, - the operator class can be used in the WITHOUT - OVERLAPS part of an index constraint. diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index f2cda0c0e94..9844d23be40 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -991,15 +991,12 @@ WITH ( MODULUS numeric_literal, REM UNIQUE (id, valid_at WITHOUT OVERLAPS) behaves like EXCLUDE USING GIST (id WITH =, valid_at WITH &&). The WITHOUT OVERLAPS column - must have a range or multirange type. (Technically, any type is allowed - whose default GiST opclass includes an overlaps operator. See the - stratnum support function under for details.) The non-WITHOUT - OVERLAPS columns of the constraint can be any type that can be - compared for equality in a GiST index. By default, only range types are - supported, but you can use other types by adding the extension (which is the expected way to use this - feature). + must have a range or multirange type. Empty ranges/multiranges are + not permitted. The non-WITHOUT OVERLAPS columns of + the constraint can be any type that can be compared for equality in a + GiST index. By default, only range types are supported, but you can use + other types by adding the extension (which + is the expected way to use this feature). diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c index 47379fef220..4647bd5f64c 100644 --- a/src/backend/access/common/tupdesc.c +++ b/src/backend/access/common/tupdesc.c @@ -229,6 +229,12 @@ CreateTupleDescCopyConstr(TupleDesc tupdesc) } } + if ((cpy->num_periods = constr->num_periods) > 0) + { + cpy->periods = (AttrNumber *) palloc(cpy->num_periods * sizeof(AttrNumber)); + memcpy(cpy->periods, constr->periods, cpy->num_periods * sizeof(AttrNumber)); + } + desc->constr = cpy; } @@ -551,6 +557,20 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2) check1->ccnoinherit == check2->ccnoinherit)) return false; } + + /* + * They should have the same number of periods, + * with the same attnos. + */ + n = constr1->num_periods; + if (n != constr2->num_periods) + return false; + for (i = 0; i < n; i++) + { + if (constr1->periods[i] != constr2->periods[i]) + return false; + } + } else if (tupdesc2->constr != NULL) return false; diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 08b8362d64d..3ba148c7c25 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -934,6 +934,7 @@ InsertPgClassTuple(Relation pg_class_desc, values[Anum_pg_class_relispopulated - 1] = BoolGetDatum(rd_rel->relispopulated); values[Anum_pg_class_relreplident - 1] = CharGetDatum(rd_rel->relreplident); values[Anum_pg_class_relispartition - 1] = BoolGetDatum(rd_rel->relispartition); + values[Anum_pg_class_relperiods - 1] = Int16GetDatum(rd_rel->relperiods); values[Anum_pg_class_relrewrite - 1] = ObjectIdGetDatum(rd_rel->relrewrite); values[Anum_pg_class_relfrozenxid - 1] = TransactionIdGetDatum(rd_rel->relfrozenxid); values[Anum_pg_class_relminmxid - 1] = MultiXactIdGetDatum(rd_rel->relminmxid); @@ -2681,6 +2682,40 @@ SetRelationNumChecks(Relation rel, int numchecks) table_close(relrel, RowExclusiveLock); } +/* + * Update the count of WITHOUT OVERLAPS constraints + * in the relation's pg_class tuple. + * + * Caller had better hold exclusive lock on the relation. + * + * An important side effect is that a SI update message will be sent out for + * the pg_class tuple, which will force other backends to rebuild their + * relcache entries for the rel. Also, this backend will rebuild its + * own relcache entry at the next CommandCounterIncrement. + */ +void +IncrementRelationNumPeriods(Relation rel) +{ + Relation relrel; + HeapTuple reltup; + Form_pg_class relStruct; + + relrel = table_open(RelationRelationId, RowExclusiveLock); + reltup = SearchSysCacheCopy1(RELOID, + ObjectIdGetDatum(RelationGetRelid(rel))); + if (!HeapTupleIsValid(reltup)) + elog(ERROR, "cache lookup failed for relation %u", + RelationGetRelid(rel)); + relStruct = (Form_pg_class) GETSTRUCT(reltup); + + relStruct->relperiods = relStruct->relperiods + 1; + + CatalogTupleUpdate(relrel, &reltup->t_self, reltup); + + heap_freetuple(reltup); + table_close(relrel, RowExclusiveLock); +} + /* * Check for references to generated columns */ diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 5a8568c55c9..44f4a33ad23 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -1393,7 +1393,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_WithoutOverlaps); /* * Extract the list of column names and the column numbers for the new @@ -1993,6 +1994,13 @@ index_constraint_create(Relation heapRelation, ObjectAddressSet(idxaddr, RelationRelationId, indexRelationId); recordDependencyOn(&idxaddr, &myself, DEPENDENCY_INTERNAL); + /* + * If this constraint has WITHOUT OVERLAPS, + * update relperiods in the table's pg_class record. + */ + if (is_without_overlaps) + IncrementRelationNumPeriods(heapRelation); + /* * Also, if this is a constraint on a partition, give it partition-type * dependencies on the parent constraint as well as the table. @@ -2430,7 +2438,8 @@ BuildIndexInfo(Relation index) indexStruct->indnullsnotdistinct, indexStruct->indisready, false, - index->rd_indam->amsummarizing); + index->rd_indam->amsummarizing, + indexStruct->indisexclusion && indexStruct->indisunique); /* fill in attribute numbers */ for (i = 0; i < numAtts; i++) @@ -2489,7 +2498,8 @@ BuildDummyIndexInfo(Relation index) indexStruct->indnullsnotdistinct, indexStruct->indisready, false, - index->rd_indam->amsummarizing); + index->rd_indam->amsummarizing, + indexStruct->indisexclusion && indexStruct->indisunique); /* fill in attribute numbers */ for (i = 0; i < numAtts; i++) @@ -3147,6 +3157,9 @@ IndexCheckExclusion(Relation heapRelation, EState *estate; ExprContext *econtext; Snapshot snapshot; + AttrNumber withoutOverlapsAttno = InvalidAttrNumber; + char withoutOverlapsTyptype = '\0'; + Oid withoutOverlapsTypid = InvalidOid; /* * If we are reindexing the target index, mark it as no longer being @@ -3156,6 +3169,27 @@ IndexCheckExclusion(Relation heapRelation, if (ReindexIsCurrentlyProcessingIndex(RelationGetRelid(indexRelation))) ResetReindexProcessing(); + /* + * If this is for a WITHOUT OVERLAPS constraint, + * then we can check for empty ranges/multiranges in the same pass, + * rather than scanning the table all over again. + * Look up what we need about the WITHOUT OVERLAPS attribute. + */ + if (indexInfo->ii_WithoutOverlaps) + { + TupleDesc tupdesc = RelationGetDescr(heapRelation); + Form_pg_attribute att; + TypeCacheEntry *typcache; + + withoutOverlapsAttno = indexInfo->ii_IndexAttrNumbers[indexInfo->ii_NumIndexKeyAttrs - 1]; + att = TupleDescAttr(tupdesc, withoutOverlapsAttno - 1); + typcache = lookup_type_cache(att->atttypid, 0); + + withoutOverlapsTyptype = typcache->typtype; + withoutOverlapsTypid = att->atttypid; + } + + /* * Need an EState for evaluation of index expressions and partial-index * predicates. Also a slot to hold the current tuple. @@ -3194,6 +3228,21 @@ IndexCheckExclusion(Relation heapRelation, continue; } + if (indexInfo->ii_WithoutOverlaps) + { + bool attisnull; + Datum attval = slot_getattr(slot, withoutOverlapsAttno, &attisnull); + /* Nulls are allowed for UNIQUE but not PRIMARY KEY. */ + if (attisnull) + continue; + /* + * Check that this tuple doesn't have an empty value. + */ + ExecWithoutOverlapsNotEmpty(heapRelation, attval, + withoutOverlapsTyptype, + withoutOverlapsTypid); + } + /* * Extract index column values, including computing expressions. */ @@ -3226,7 +3275,6 @@ IndexCheckExclusion(Relation heapRelation, indexInfo->ii_PredicateState = NULL; } - /* * validate_index - support code for concurrent index builds * diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c index 9be050ccee8..7d3dd7b6448 100644 --- a/src/backend/catalog/pg_constraint.c +++ b/src/backend/catalog/pg_constraint.c @@ -666,6 +666,39 @@ RemoveConstraintById(Oid conId) table_close(pgrel, RowExclusiveLock); } + /* + * Similarly we need to update the relperiods count if it is a check + * constraint PRIMARY KEY or UNIQUE constraint using WIHOUT OVERLAPS. + * This update will force backends to rebuild relcache entries when + * we commit. + */ + else if (con->conperiod && + (con->contype == CONSTRAINT_PRIMARY || con->contype == CONSTRAINT_UNIQUE)) + { + Relation pgrel; + HeapTuple relTup; + Form_pg_class classForm; + + pgrel = table_open(RelationRelationId, RowExclusiveLock); + relTup = SearchSysCacheCopy1(RELOID, + ObjectIdGetDatum(con->conrelid)); + if (!HeapTupleIsValid(relTup)) + elog(ERROR, "cache lookup failed for relation %u", + con->conrelid); + classForm = (Form_pg_class) GETSTRUCT(relTup); + + if (classForm->relperiods == 0) /* should not happen */ + elog(ERROR, "relation \"%s\" has relperiods = 0", + RelationGetRelationName(rel)); + classForm->relperiods--; + + CatalogTupleUpdate(pgrel, &relTup->t_self, relTup); + + heap_freetuple(relTup); + + table_close(pgrel, RowExclusiveLock); + } + /* Keep lock on constraint's rel until end of xact */ table_close(rel, NoLock); diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 7b20d103c86..69c3fb64718 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -242,7 +242,7 @@ CheckIndexCompatible(Oid oldId, */ indexInfo = makeIndexInfo(numberOfAttributes, numberOfAttributes, accessMethodId, NIL, NIL, false, false, - false, false, amsummarizing); + false, false, amsummarizing, isWithoutOverlaps); typeIds = palloc_array(Oid, numberOfAttributes); collationIds = palloc_array(Oid, numberOfAttributes); opclassIds = palloc_array(Oid, numberOfAttributes); @@ -915,7 +915,8 @@ DefineIndex(Oid tableId, stmt->nulls_not_distinct, !concurrent, concurrent, - amissummarizing); + amissummarizing, + stmt->iswithoutoverlaps); typeIds = palloc_array(Oid, numberOfAttributes); collationIds = palloc_array(Oid, numberOfAttributes); diff --git a/src/backend/executor/execIndexing.c b/src/backend/executor/execIndexing.c index 59acf67a36a..010f0ce3a9c 100644 --- a/src/backend/executor/execIndexing.c +++ b/src/backend/executor/execIndexing.c @@ -114,6 +114,8 @@ #include "executor/executor.h" #include "nodes/nodeFuncs.h" #include "storage/lmgr.h" +#include "utils/multirangetypes.h" +#include "utils/rangetypes.h" #include "utils/snapmgr.h" /* waitMode argument to check_exclusion_or_unique_constraint() */ @@ -1097,3 +1099,36 @@ index_expression_changed_walker(Node *node, Bitmapset *allUpdatedCols) return expression_tree_walker(node, index_expression_changed_walker, (void *) allUpdatedCols); } + +/* + * ExecWithoutOverlapsNotEmpty - raise an error if the tuple has an empty + * range or multirange in the given attribute. + */ +void +ExecWithoutOverlapsNotEmpty(Relation rel, Datum attval, Oid typtype, Oid atttypid) +{ + bool isempty; + RangeType *r; + MultirangeType *mr; + + switch (typtype) + { + case TYPTYPE_RANGE: + r = DatumGetRangeTypeP(attval); + isempty = RangeIsEmpty(r); + break; + case TYPTYPE_MULTIRANGE: + mr = DatumGetMultirangeTypeP(attval); + isempty = MultirangeIsEmpty(mr); + break; + default: + elog(ERROR, "Got unknown type for WITHOUT OVERLAPS column: %d", atttypid); + } + + /* Report a CHECK_VIOLATION */ + if (isempty) + ereport(ERROR, + (errcode(ERRCODE_CHECK_VIOLATION), + errmsg("new row for relation \"%s\" contains empty WITHOUT OVERLAPS value", + RelationGetRelationName(rel)))); +} diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index 4d7c92d63c1..378d469b52e 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -59,6 +59,7 @@ #include "utils/partcache.h" #include "utils/rls.h" #include "utils/snapmgr.h" +#include "utils/typcache.h" /* Hooks for plugins to get control in ExecutorStart/Run/Finish/End */ @@ -1987,6 +1988,34 @@ ExecConstraints(ResultRelInfo *resultRelInfo, } } + /* + * If there are WITHOUT OVERLAPS constraints, + * we must forbid empty range/multirange values. + */ + if (constr->num_periods > 0) + { + uint16 i; + + for (i = 0; i < constr->num_periods; i++) + { + AttrNumber attno = constr->periods[i]; + Form_pg_attribute att; + TypeCacheEntry *typcache; + Datum attval; + bool isnull; + + attval = slot_getattr(slot, attno, &isnull); + if (isnull) + continue; + + att = TupleDescAttr(tupdesc, attno - 1); + typcache = lookup_type_cache(att->atttypid, 0); + ExecWithoutOverlapsNotEmpty(rel, attval, + typcache->typtype, + att->atttypid); + } + } + if (rel->rd_rel->relchecks > 0) { const char *failed; diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c index 61ac172a857..9cac3c1c27b 100644 --- a/src/backend/nodes/makefuncs.c +++ b/src/backend/nodes/makefuncs.c @@ -760,7 +760,8 @@ 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 withoutoverlaps) { IndexInfo *n = makeNode(IndexInfo); @@ -775,6 +776,7 @@ makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions, n->ii_IndexUnchanged = false; n->ii_Concurrent = concurrent; n->ii_Summarizing = summarizing; + n->ii_WithoutOverlaps = withoutoverlaps; /* summarizing indexes cannot contain non-key attributes */ Assert(!summarizing || (numkeyattrs == numattrs)); diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 53492c644a9..b3b500efc46 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -2397,7 +2397,8 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) * For UNIQUE and PRIMARY KEY, we just have a list of column names. * * Make sure referenced keys exist. If we are making a PRIMARY KEY index, - * also make sure they are NOT NULL. + * also make sure they are NOT NULL. For WITHOUT OVERLAPS constraints, + * we make sure the last part is a range or multirange. */ else { @@ -2409,6 +2410,7 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) ColumnDef *column = NULL; ListCell *columns; IndexElem *iparam; + Oid typid = InvalidOid; /* Make sure referenced column exists. */ foreach(columns, cxt->columns) @@ -2475,6 +2477,7 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) if (strcmp(key, inhname) == 0) { found = true; + typid = inhattr->atttypid; /* * It's tempting to set forced_not_null if the @@ -2524,6 +2527,45 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) } } + /* The WITHOUT OVERLAPS part (if any) must be a range or multirange type. */ + if (constraint->without_overlaps && lc == list_last_cell(constraint->keys)) + { + if (!found && cxt->isalter) + { + /* + * Look up the column type on existing table. + * If we can't find it, let things fail in DefineIndex. + */ + 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; + + if (attr->attisdropped) + break; + + attname = NameStr(attr->attname); + if (strcmp(attname, key) == 0) + { + typid = attr->atttypid; + break; + } + } + } + if (found) + { + if (!OidIsValid(typid)) + typid = typenameTypeId(NULL, column->typeName); + + if (!OidIsValid(typid) || !(type_is_range(typid) || type_is_multirange(typid))) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("column \"%s\" in WITHOUT OVERLAPS is not a range or multirange type", key), + parser_errposition(cxt->pstate, constraint->location))); + } + } + /* OK, add it to the index definition */ iparam = makeNode(IndexElem); iparam->name = pstrdup(key); diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index fcdda8658b3..06f16321d29 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -307,6 +307,7 @@ static TupleDesc GetPgIndexDescriptor(void); static void AttrDefaultFetch(Relation relation, int ndef); static int AttrDefaultCmp(const void *a, const void *b); static void CheckConstraintFetch(Relation relation); +static void WithoutOverlapsFetch(Relation relation); static int CheckConstraintCmp(const void *a, const void *b); static void InitIndexAmRoutine(Relation relation); static void IndexSupportInitialize(oidvector *indclass, @@ -689,7 +690,8 @@ RelationBuildTupleDesc(Relation relation) constr->has_generated_stored || ndef > 0 || attrmiss || - relation->rd_rel->relchecks > 0) + relation->rd_rel->relchecks > 0 || + relation->rd_rel->relperiods > 0) { relation->rd_att->constr = constr; @@ -704,6 +706,15 @@ RelationBuildTupleDesc(Relation relation) CheckConstraintFetch(relation); else constr->num_check = 0; + + /* + * Remember if any attributes have a PK or UNIQUE constraint + * using WITHOUT OVERLAPS. We must forbid empties for them. + */ + if (relation->rd_rel->relperiods > 0) /* WITHOUT OVERLAPS */ + WithoutOverlapsFetch(relation); + else + constr->num_periods = 0; } else { @@ -4664,6 +4675,106 @@ CheckConstraintFetch(Relation relation) relation->rd_att->constr->num_check = found; } +/* + * Load any WITHOUT OVERLAPS attributes for the relation. + * + * These are not allowed to hold empty values. + */ +static void +WithoutOverlapsFetch(Relation relation) +{ + Bitmapset *periods = NULL; + AttrNumber *result; + int nperiods = relation->rd_rel->relperiods; + Relation conrel; + SysScanDesc conscan; + ScanKeyData skey[1]; + HeapTuple htup; + int found = 0; + AttrNumber attno; + + /* Search pg_constraint for relevant entries */ + ScanKeyInit(&skey[0], + Anum_pg_constraint_conrelid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(RelationGetRelid(relation))); + + conrel = table_open(ConstraintRelationId, AccessShareLock); + conscan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId, true, + NULL, 1, skey); + + while (HeapTupleIsValid(htup = systable_getnext(conscan))) + { + Form_pg_constraint conform = (Form_pg_constraint) GETSTRUCT(htup); + Datum val; + bool isnull; + ArrayType *arr; + int numcols; + int16 *attnums; + + /* We want conperiod constraints only */ + if (!conform->conperiod) + continue; + + /* We want PRIMARY KEY and UNIQUE constraints only */ + if (conform->contype != CONSTRAINT_PRIMARY && + conform->contype != CONSTRAINT_UNIQUE) + continue; + + /* protect limited size of array */ + if (found >= nperiods) + { + elog(WARNING, "unexpected pg_constraint record found for relation \"%s\"", + RelationGetRelationName(relation)); + break; + } + + /* Get the attno of the WITHOUT OVERLAPS column */ + val = heap_getattr(htup, Anum_pg_constraint_conkey, + RelationGetDescr(conrel), &isnull); + if (isnull) + elog(ERROR, "found null conkey for WITHOUT OVERLAPS constraint"); + + arr = DatumGetArrayTypeP(val); /* ensure not toasted */ + numcols = ARR_DIMS(arr)[0]; + if (ARR_NDIM(arr) != 1 || + numcols < 0 || + ARR_HASNULL(arr) || + ARR_ELEMTYPE(arr) != INT2OID) + elog(ERROR, "conkey is not a 1-D smallint array"); + attnums = (int16 *) ARR_DATA_PTR(arr); + + /* + * Use a Bitmapset in case there are two constraints + * using the same WITHOUT OVERLAPS attribute. + */ + periods = bms_add_member(periods, attnums[numcols - 1]); + found++; + } + + systable_endscan(conscan); + table_close(conrel, AccessShareLock); + + if (found != nperiods) + elog(WARNING, "%d pg_constraint record(s) missing for relation \"%s\"", + nperiods - found, RelationGetRelationName(relation)); + + /* Put everything we found into an array */ + found = bms_num_members(periods); + result = (AttrNumber *) + MemoryContextAllocZero(CacheMemoryContext, + found * sizeof(AttrNumber)); + attno = -1; + found = 0; + while ((attno = bms_next_member(periods, attno)) >= 0) + result[found++] = attno; + bms_free(periods); + + /* Install array only after it's fully valid */ + relation->rd_att->constr->periods = result; + relation->rd_att->constr->num_periods = found; +} + /* * qsort comparator to sort ConstrCheck entries by name */ diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h index 8930a28d660..5e983f4de69 100644 --- a/src/include/access/tupdesc.h +++ b/src/include/access/tupdesc.h @@ -38,9 +38,11 @@ typedef struct TupleConstr { AttrDefault *defval; /* array */ ConstrCheck *check; /* array */ + AttrNumber *periods; /* array */ struct AttrMissing *missing; /* missing attributes values, NULL if none */ uint16 num_defval; uint16 num_check; + uint16 num_periods; bool has_not_null; bool has_generated_stored; } TupleConstr; diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h index c512824cd1c..14f75c8a33d 100644 --- a/src/include/catalog/heap.h +++ b/src/include/catalog/heap.h @@ -143,6 +143,7 @@ extern void CheckAttributeType(const char *attname, Oid atttypid, Oid attcollation, List *containing_rowtypes, int flags); +extern void IncrementRelationNumPeriods(Relation rel); /* pg_partitioned_table catalog manipulation functions */ extern void StorePartitionKey(Relation rel, diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h index 0fc2c093b0d..a337df6a76f 100644 --- a/src/include/catalog/pg_class.h +++ b/src/include/catalog/pg_class.h @@ -119,6 +119,9 @@ CATALOG(pg_class,1259,RelationRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(83,Relat /* is relation a partition? */ bool relispartition BKI_DEFAULT(f); + /* # of WITHOUT OVERLAPS constraints for class */ + int16 relperiods BKI_DEFAULT(0); + /* link to original rel during table rewrite; otherwise 0 */ Oid relrewrite BKI_DEFAULT(0) BKI_LOOKUP_OPT(pg_class); diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h index 9770752ea3c..315dd225efc 100644 --- a/src/include/executor/executor.h +++ b/src/include/executor/executor.h @@ -642,6 +642,8 @@ extern void check_exclusion_constraint(Relation heap, Relation index, ItemPointer tupleid, const Datum *values, const bool *isnull, EState *estate, bool newIndex); +extern void ExecWithoutOverlapsNotEmpty(Relation rel, Datum attval, + Oid typtype, Oid typtypid); /* * prototypes from functions in execReplication.c diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 8bc421e7c05..20236af39d4 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -202,6 +202,7 @@ typedef struct IndexInfo bool ii_Concurrent; bool ii_BrokenHotChain; bool ii_Summarizing; + bool ii_WithoutOverlaps; int ii_ParallelWorkers; Oid ii_Am; void *ii_AmCache; diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h index 5209d3de89c..0765e5c57b4 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 withoutoverlaps); extern Node *makeStringConst(char *str, int location); extern DefElem *makeDefElem(char *name, Node *arg, int location); diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out index 94234758598..a0e1bd4a29c 100644 --- a/src/test/regress/expected/without_overlaps.out +++ b/src/test/regress/expected/without_overlaps.out @@ -27,8 +27,9 @@ CREATE TABLE temporal_rng ( valid_at TEXT, CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ); -ERROR: data type text has no default operator class for access method "gist" -HINT: You must specify an operator class for the index or define a default operator class for the data type. +ERROR: column "valid_at" in WITHOUT OVERLAPS is not a range or multirange type +LINE 4: CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOU... + ^ -- PK with one column plus a range: CREATE TABLE temporal_rng ( -- Since we can't depend on having btree_gist here, @@ -238,8 +239,9 @@ CREATE TABLE temporal_rng3 ( valid_at TEXT, CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) ); -ERROR: data type text has no default operator class for access method "gist" -HINT: You must specify an operator class for the index or define a default operator class for the data type. +ERROR: column "valid_at" in WITHOUT OVERLAPS is not a range or multirange type +LINE 4: CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OV... + ^ -- UNIQUE with one column plus a range: CREATE TABLE temporal_rng3 ( id int4range, @@ -393,6 +395,12 @@ BEGIN; ERROR: could not create exclusion constraint "temporal_rng_pk" DETAIL: Key (id, valid_at)=([1,2), [2018-01-02,2018-02-03)) conflicts with key (id, valid_at)=([1,2), [2018-01-01,2018-01-05)). ROLLBACK; +-- rejects empty: +BEGIN; + INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', 'empty'); + ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +ERROR: new row for relation "temporal_rng" contains empty WITHOUT OVERLAPS value +ROLLBACK; ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); DELETE FROM temporal_rng; -- @@ -413,6 +421,9 @@ DETAIL: Failing row contains (null, [2018-01-01,2018-01-05)). INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', NULL); ERROR: null value in column "valid_at" of relation "temporal_rng" violates not-null constraint DETAIL: Failing row contains ([3,4), null). +-- rejects empty: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', 'empty'); +ERROR: new row for relation "temporal_rng" contains empty WITHOUT OVERLAPS value SELECT * FROM temporal_rng ORDER BY id, valid_at; id | valid_at -------+------------------------- @@ -471,6 +482,12 @@ SET id = '[1,2)', WHERE id = '[21,22)'; ERROR: null value in column "valid_at" of relation "temporal_rng" violates not-null constraint DETAIL: Failing row contains ([1,2), null). +-- rejects empty: +UPDATE temporal_rng +SET id = '[1,2)', + valid_at = 'empty' +WHERE id = '[21,22)'; +ERROR: new row for relation "temporal_rng" contains empty WITHOUT OVERLAPS value SELECT * FROM temporal_rng ORDER BY id, valid_at; id | valid_at ---------+------------------------- @@ -503,6 +520,12 @@ BEGIN; ERROR: could not create exclusion constraint "temporal_rng3_uq" DETAIL: Key (id, valid_at)=([1,2), [2018-01-02,2018-02-03)) conflicts with key (id, valid_at)=([1,2), [2018-01-01,2018-01-05)). ROLLBACK; +-- rejects empty: +BEGIN; + INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', 'empty'); + ALTER TABLE temporal_rng3 ADD CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS); +ERROR: new row for relation "temporal_rng3" contains empty WITHOUT OVERLAPS value +ROLLBACK; ALTER TABLE temporal_rng3 ADD CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS); DELETE FROM temporal_rng3; -- @@ -519,6 +542,9 @@ INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', NULL); INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-01-01', '2018-01-05')); ERROR: conflicting key value violates exclusion constraint "temporal_rng3_uq" DETAIL: Key (id, valid_at)=([1,2), [2018-01-01,2018-01-05)) conflicts with existing key (id, valid_at)=([1,2), [2018-01-02,2018-02-03)). +-- rejects empty: +INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', 'empty'); +ERROR: new row for relation "temporal_rng3" contains empty WITHOUT OVERLAPS value SELECT * FROM temporal_rng3 ORDER BY id, valid_at; id | valid_at -------+------------------------- @@ -576,6 +602,17 @@ SET valid_at = daterange('2018-03-01', '2018-05-05') WHERE id = '[1,2)' AND valid_at IS NULL; ERROR: conflicting key value violates exclusion constraint "temporal_rng3_uq" DETAIL: Key (id, valid_at)=([1,2), [2018-03-01,2018-05-05)) conflicts with existing key (id, valid_at)=([1,2), [2018-03-03,2018-04-04)). +-- rejects empty: +UPDATE temporal_rng3 +SET valid_at = 'empty' +WHERE id = '[1,2)' AND valid_at IS NULL; +ERROR: new row for relation "temporal_rng3" contains empty WITHOUT OVERLAPS value +-- still rejects empty when scalar part is NULL: +UPDATE temporal_rng3 +SET id = NULL, + valid_at = 'empty' +WHERE id = '[1,2)' AND valid_at IS NULL; +ERROR: new row for relation "temporal_rng3" contains empty WITHOUT OVERLAPS value SELECT * FROM temporal_rng3 ORDER BY id, valid_at; id | valid_at -------+------------------------- @@ -606,6 +643,12 @@ BEGIN; ERROR: could not create exclusion constraint "temporal_mltrng_pk" DETAIL: Key (id, valid_at)=([1,2), {[2018-01-02,2018-02-03)}) conflicts with key (id, valid_at)=([1,2), {[2018-01-01,2018-01-05)}). ROLLBACK; +-- rejects empty: +BEGIN; + INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', '{}'); + ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +ERROR: new row for relation "temporal_mltrng" contains empty WITHOUT OVERLAPS value +ROLLBACK; ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); DELETE FROM temporal_mltrng; -- @@ -626,6 +669,9 @@ DETAIL: Failing row contains (null, {[2018-01-01,2018-01-05)}). INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', NULL); ERROR: null value in column "valid_at" of relation "temporal_mltrng" violates not-null constraint DETAIL: Failing row contains ([3,4), null). +-- rejects empty: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', '{}'); +ERROR: new row for relation "temporal_mltrng" contains empty WITHOUT OVERLAPS value SELECT * FROM temporal_mltrng ORDER BY id, valid_at; id | valid_at -------+--------------------------- @@ -684,6 +730,12 @@ SET id = '[1,2)', WHERE id = '[21,22)'; ERROR: null value in column "valid_at" of relation "temporal_mltrng" violates not-null constraint DETAIL: Failing row contains ([1,2), null). +-- rejects empty: +UPDATE temporal_mltrng +SET id = '[1,2)', + valid_at = '{}' +WHERE id = '[21,22)'; +ERROR: new row for relation "temporal_mltrng" contains empty WITHOUT OVERLAPS value SELECT * FROM temporal_mltrng ORDER BY id, valid_at; id | valid_at ---------+--------------------------- @@ -716,6 +768,12 @@ BEGIN; ERROR: could not create exclusion constraint "temporal_mltrng3_uq" DETAIL: Key (id, valid_at)=([1,2), {[2018-01-02,2018-02-03)}) conflicts with key (id, valid_at)=([1,2), {[2018-01-01,2018-01-05)}). ROLLBACK; +-- rejects empty: +BEGIN; + INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', '{}'); + ALTER TABLE temporal_mltrng3 ADD CONSTRAINT temporal_mltrng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS); +ERROR: new row for relation "temporal_mltrng3" contains empty WITHOUT OVERLAPS value +ROLLBACK; ALTER TABLE temporal_mltrng3 ADD CONSTRAINT temporal_mltrng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS); DELETE FROM temporal_mltrng3; -- @@ -732,6 +790,9 @@ INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', NULL); INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-01', '2018-01-05'))); ERROR: conflicting key value violates exclusion constraint "temporal_mltrng3_uq" DETAIL: Key (id, valid_at)=([1,2), {[2018-01-01,2018-01-05)}) conflicts with existing key (id, valid_at)=([1,2), {[2018-01-02,2018-02-03)}). +-- rejects empty: +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', '{}'); +ERROR: new row for relation "temporal_mltrng3" contains empty WITHOUT OVERLAPS value SELECT * FROM temporal_mltrng3 ORDER BY id, valid_at; id | valid_at -------+--------------------------- @@ -789,6 +850,17 @@ SET valid_at = datemultirange(daterange('2018-03-01', '2018-05-05')) WHERE id = '[1,2)' AND valid_at IS NULL; ERROR: conflicting key value violates exclusion constraint "temporal_mltrng3_uq" DETAIL: Key (id, valid_at)=([1,2), {[2018-03-01,2018-05-05)}) conflicts with existing key (id, valid_at)=([1,2), {[2018-03-03,2018-04-04)}). +-- rejects empty: +UPDATE temporal_mltrng3 +SET valid_at = '{}' +WHERE id = '[1,2)' AND valid_at IS NULL; +ERROR: new row for relation "temporal_mltrng3" contains empty WITHOUT OVERLAPS value +-- still rejects empty when scalar part is NULL: +UPDATE temporal_mltrng3 +SET id = NULL, + valid_at = '{}' +WHERE id = '[1,2)' AND valid_at IS NULL; +ERROR: new row for relation "temporal_mltrng3" contains empty WITHOUT OVERLAPS value SELECT * FROM temporal_mltrng3 ORDER BY id, valid_at; id | valid_at -------+--------------------------- @@ -844,6 +916,29 @@ CREATE TABLE temporal_partitioned ( ) PARTITION BY LIST (id); CREATE TABLE tp1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)'); CREATE TABLE tp2 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5)'); +SELECT relname, relperiods FROM pg_class WHERE relname IN ('temporal_partitioned', 'tp1', 'tp2') ORDER BY relname; + relname | relperiods +----------------------+------------ + temporal_partitioned | 1 + tp1 | 1 + tp2 | 1 +(3 rows) + +ALTER TABLE temporal_partitioned DETACH PARTITION tp1; +SELECT relname, relperiods FROM pg_class WHERE relname = 'tp1'; + relname | relperiods +---------+------------ + tp1 | 1 +(1 row) + +ALTER TABLE tp1 DROP CONSTRAINT tp1_pkey; +ALTER TABLE temporal_partitioned ATTACH PARTITION tp1 FOR VALUES IN ('[1,2)', '[2,3)'); +SELECT relname, relperiods FROM pg_class WHERE relname = 'tp1'; + relname | relperiods +---------+------------ + tp1 | 1 +(1 row) + INSERT INTO temporal_partitioned (id, valid_at, name) VALUES ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), @@ -879,6 +974,29 @@ CREATE TABLE temporal_partitioned ( ) PARTITION BY LIST (id); CREATE TABLE tp1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)'); CREATE TABLE tp2 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5)'); +SELECT relname, relperiods FROM pg_class WHERE relname IN ('temporal_partitioned', 'tp1', 'tp2') ORDER BY relname; + relname | relperiods +----------------------+------------ + temporal_partitioned | 1 + tp1 | 1 + tp2 | 1 +(3 rows) + +ALTER TABLE temporal_partitioned DETACH PARTITION tp1; +SELECT relname, relperiods FROM pg_class WHERE relname = 'tp1'; + relname | relperiods +---------+------------ + tp1 | 1 +(1 row) + +ALTER TABLE tp1 DROP CONSTRAINT tp1_id_valid_at_excl; +ALTER TABLE temporal_partitioned ATTACH PARTITION tp1 FOR VALUES IN ('[1,2)', '[2,3)'); +SELECT relname, relperiods FROM pg_class WHERE relname = 'tp1'; + relname | relperiods +---------+------------ + tp1 | 1 +(1 row) + INSERT INTO temporal_partitioned (id, valid_at, name) VALUES ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql index 36b3e6dc02a..bf39685a5d5 100644 --- a/src/test/regress/sql/without_overlaps.sql +++ b/src/test/regress/sql/without_overlaps.sql @@ -264,6 +264,11 @@ BEGIN; INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-01', '2018-01-05')); ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); ROLLBACK; +-- rejects empty: +BEGIN; + INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', 'empty'); + ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +ROLLBACK; ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); DELETE FROM temporal_rng; @@ -281,6 +286,8 @@ INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', daterange('2018-01-01', INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-01', '2018-01-05')); INSERT INTO temporal_rng (id, valid_at) VALUES (NULL, daterange('2018-01-01', '2018-01-05')); INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', NULL); +-- rejects empty: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', 'empty'); SELECT * FROM temporal_rng ORDER BY id, valid_at; -- @@ -319,6 +326,11 @@ UPDATE temporal_rng SET id = '[1,2)', valid_at = NULL WHERE id = '[21,22)'; +-- rejects empty: +UPDATE temporal_rng +SET id = '[1,2)', + valid_at = 'empty' +WHERE id = '[21,22)'; SELECT * FROM temporal_rng ORDER BY id, valid_at; -- @@ -345,6 +357,11 @@ BEGIN; INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-01-01', '2018-01-05')); ALTER TABLE temporal_rng3 ADD CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS); ROLLBACK; +-- rejects empty: +BEGIN; + INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', 'empty'); + ALTER TABLE temporal_rng3 ADD CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS); +ROLLBACK; ALTER TABLE temporal_rng3 ADD CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS); DELETE FROM temporal_rng3; @@ -362,6 +379,8 @@ INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', NULL); -- should fail: INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-01-01', '2018-01-05')); +-- rejects empty: +INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', 'empty'); SELECT * FROM temporal_rng3 ORDER BY id, valid_at; -- @@ -399,6 +418,15 @@ SELECT * FROM temporal_rng3 ORDER BY id, valid_at; UPDATE temporal_rng3 SET valid_at = daterange('2018-03-01', '2018-05-05') WHERE id = '[1,2)' AND valid_at IS NULL; +-- rejects empty: +UPDATE temporal_rng3 +SET valid_at = 'empty' +WHERE id = '[1,2)' AND valid_at IS NULL; +-- still rejects empty when scalar part is NULL: +UPDATE temporal_rng3 +SET id = NULL, + valid_at = 'empty' +WHERE id = '[1,2)' AND valid_at IS NULL; SELECT * FROM temporal_rng3 ORDER BY id, valid_at; DROP TABLE temporal_rng3; @@ -421,6 +449,11 @@ BEGIN; INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-01', '2018-01-05'))); ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); ROLLBACK; +-- rejects empty: +BEGIN; + INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', '{}'); + ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +ROLLBACK; ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); DELETE FROM temporal_mltrng; @@ -438,6 +471,8 @@ INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', datemultirange(dater INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-01', '2018-01-05'))); INSERT INTO temporal_mltrng (id, valid_at) VALUES (NULL, datemultirange(daterange('2018-01-01', '2018-01-05'))); INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', NULL); +-- rejects empty: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', '{}'); SELECT * FROM temporal_mltrng ORDER BY id, valid_at; -- @@ -476,6 +511,11 @@ UPDATE temporal_mltrng SET id = '[1,2)', valid_at = NULL WHERE id = '[21,22)'; +-- rejects empty: +UPDATE temporal_mltrng +SET id = '[1,2)', + valid_at = '{}' +WHERE id = '[21,22)'; SELECT * FROM temporal_mltrng ORDER BY id, valid_at; -- @@ -502,6 +542,11 @@ BEGIN; INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-01', '2018-01-05'))); ALTER TABLE temporal_mltrng3 ADD CONSTRAINT temporal_mltrng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS); ROLLBACK; +-- rejects empty: +BEGIN; + INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', '{}'); + ALTER TABLE temporal_mltrng3 ADD CONSTRAINT temporal_mltrng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS); +ROLLBACK; ALTER TABLE temporal_mltrng3 ADD CONSTRAINT temporal_mltrng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS); DELETE FROM temporal_mltrng3; @@ -519,6 +564,8 @@ INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', NULL); -- should fail: INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-01', '2018-01-05'))); +-- rejects empty: +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', '{}'); SELECT * FROM temporal_mltrng3 ORDER BY id, valid_at; -- @@ -556,6 +603,15 @@ SELECT * FROM temporal_mltrng3 ORDER BY id, valid_at; UPDATE temporal_mltrng3 SET valid_at = datemultirange(daterange('2018-03-01', '2018-05-05')) WHERE id = '[1,2)' AND valid_at IS NULL; +-- rejects empty: +UPDATE temporal_mltrng3 +SET valid_at = '{}' +WHERE id = '[1,2)' AND valid_at IS NULL; +-- still rejects empty when scalar part is NULL: +UPDATE temporal_mltrng3 +SET id = NULL, + valid_at = '{}' +WHERE id = '[1,2)' AND valid_at IS NULL; SELECT * FROM temporal_mltrng3 ORDER BY id, valid_at; DROP TABLE temporal_mltrng3; @@ -607,6 +663,12 @@ CREATE TABLE temporal_partitioned ( ) PARTITION BY LIST (id); CREATE TABLE tp1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)'); CREATE TABLE tp2 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5)'); +SELECT relname, relperiods FROM pg_class WHERE relname IN ('temporal_partitioned', 'tp1', 'tp2') ORDER BY relname; +ALTER TABLE temporal_partitioned DETACH PARTITION tp1; +SELECT relname, relperiods FROM pg_class WHERE relname = 'tp1'; +ALTER TABLE tp1 DROP CONSTRAINT tp1_pkey; +ALTER TABLE temporal_partitioned ATTACH PARTITION tp1 FOR VALUES IN ('[1,2)', '[2,3)'); +SELECT relname, relperiods FROM pg_class WHERE relname = 'tp1'; INSERT INTO temporal_partitioned (id, valid_at, name) VALUES ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), @@ -625,6 +687,12 @@ CREATE TABLE temporal_partitioned ( ) PARTITION BY LIST (id); CREATE TABLE tp1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)'); CREATE TABLE tp2 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5)'); +SELECT relname, relperiods FROM pg_class WHERE relname IN ('temporal_partitioned', 'tp1', 'tp2') ORDER BY relname; +ALTER TABLE temporal_partitioned DETACH PARTITION tp1; +SELECT relname, relperiods FROM pg_class WHERE relname = 'tp1'; +ALTER TABLE tp1 DROP CONSTRAINT tp1_id_valid_at_excl; +ALTER TABLE temporal_partitioned ATTACH PARTITION tp1 FOR VALUES IN ('[1,2)', '[2,3)'); +SELECT relname, relperiods FROM pg_class WHERE relname = 'tp1'; INSERT INTO temporal_partitioned (id, valid_at, name) VALUES ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), -- 2.42.0