On 5/5/24 20:01, jian he wrote:
hi.
I hope I understand the problem correctly.
my understanding is that we are trying to solve a corner case:
create table t(a int4range, b int4range, primary key(a, b WITHOUT OVERLAPS));
insert into t values ('[1,2]','empty'), ('[1,2]','empty');
I think the entry point is ATAddCheckNNConstraint and index_create.
in a chain of DDL commands, you cannot be sure which one
(primary key constraint or check constraint) is being created first,
you just want to make sure that after both constraints are created,
then add a dependency between primary key and check constraint.
so you need to validate at different functions
(ATAddCheckNNConstraint, index_create)
that these two constraints are indeed created,
only after that we have a dependency linking these two constraints.
I've attached a patch trying to solve this problem.
the patch is not totally polished, but works as expected, and also has
lots of comments.
Thanks for this! I've incorporated it into the CHECK constraint patch with some changes. In
particular I thought index_create was a strange place to change the conperiod value of a
pg_constraint record, and it is not actually needed if we are copying that value correctly.
Some other comments on the patch file:
> N.B. we also need to have special care for case
> where check constraint was readded, e.g. ALTER TYPE.
> if ALTER TYPE is altering the PERIOD column of the primary key,
> alter column of primary key makes the index recreate, check constraint
recreate,
> however, former interally also including add a check constraint.
> so we need to take care of merging two check constraint.
This is a good point. I've included tests for this based on your patch.
> N.B. the check constraint name is hard-wired, so if you create the constraint
> with the same name, PERIOD primary key cannot be created.
Yes, it may be worth doing something like other auto-named constraints and trying to avoid
duplicates. I haven't taken that on yet; I'm curious what others have to say about it.
> N.B. what about UNIQUE constraint?
See my previous posts on this thread about allowing 'empty' in UNIQUE
constraints.
> N.B. seems ok to not care about FOREIGN KEY regarding this corner case?
Agreed.
v3 patches attached, rebased to 3ca43dbbb6.
Yours,
--
Paul ~{:-)
p...@illuminatedcomputing.comFrom 4f4428fb41ea79056a13e425826fdac9c7b5d349 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <p...@illuminatedcomputing.com>
Date: Tue, 2 Apr 2024 15:39:04 -0700
Subject: [PATCH v3 1/2] Don't treat WITHOUT OVERLAPS indexes as unique in
planner
Because the special rangetype 'empty' never overlaps another value, it
is possible for WITHOUT OVERLAPS tables to have two rows with the same
key, despite being indisunique, if the application-time range is
'empty'. So to be safe we should not treat WITHOUT OVERLAPS indexes as
unique in any proofs.
This still needs a test, but I'm having trouble finding a query that
gives wrong results.
---
src/backend/optimizer/path/indxpath.c | 5 +++--
src/backend/optimizer/plan/analyzejoins.c | 6 +++---
src/backend/optimizer/util/plancat.c | 1 +
src/include/nodes/pathnodes.h | 2 ++
4 files changed, 9 insertions(+), 5 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index c0fcc7d78df..72346f78ebe 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -3498,13 +3498,14 @@ relation_has_unique_index_for(PlannerInfo *root, RelOptInfo *rel,
/*
* If the index is not unique, or not immediately enforced, or if it's
- * a partial index, it's useless here. We're unable to make use of
+ * a partial index, or if it's a WITHOUT OVERLAPS index (so not
+ * literally unique), it's useless here. We're unable to make use of
* predOK partial unique indexes due to the fact that
* check_index_predicates() also makes use of join predicates to
* determine if the partial index is usable. Here we need proofs that
* hold true before any joins are evaluated.
*/
- if (!ind->unique || !ind->immediate || ind->indpred != NIL)
+ if (!ind->unique || !ind->immediate || ind->indpred != NIL || ind->hasperiod)
continue;
/*
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index c3fd4a81f8a..dc8327d5769 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -814,8 +814,8 @@ rel_supports_distinctness(PlannerInfo *root, RelOptInfo *rel)
* For a plain relation, we only know how to prove uniqueness by
* reference to unique indexes. Make sure there's at least one
* suitable unique index. It must be immediately enforced, and not a
- * partial index. (Keep these conditions in sync with
- * relation_has_unique_index_for!)
+ * partial index, and not WITHOUT OVERLAPS (Keep these conditions
+ * in sync with relation_has_unique_index_for!)
*/
ListCell *lc;
@@ -823,7 +823,7 @@ rel_supports_distinctness(PlannerInfo *root, RelOptInfo *rel)
{
IndexOptInfo *ind = (IndexOptInfo *) lfirst(lc);
- if (ind->unique && ind->immediate && ind->indpred == NIL)
+ if (ind->unique && ind->immediate && ind->indpred == NIL && !ind->hasperiod)
return true;
}
}
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 775c3e26cd8..146029577bd 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -457,6 +457,7 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
info->predOK = false; /* set later, in indxpath.c */
info->unique = index->indisunique;
info->immediate = index->indimmediate;
+ info->hasperiod = index->indisunique && index->indisexclusion;
info->hypothetical = false;
/*
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 14ef296ab72..e24a45f0cd5 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -1176,6 +1176,8 @@ struct IndexOptInfo
bool unique;
/* is uniqueness enforced immediately? */
bool immediate;
+ /* true if index has WITHOUT OVERLAPS */
+ bool hasperiod;
/* true if index doesn't really exist */
bool hypothetical;
--
2.42.0
From 11ef535bc78ef9b50d8af089e6d71f20532a693c Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <p...@illuminatedcomputing.com>
Date: Tue, 9 Apr 2024 20:52:23 -0700
Subject: [PATCH v3 2/2] Add CHECK (NOT isempty) constraint to PRIMARY KEYs
WITHOUT OVERLAPS
This is necessary because 'empty' && 'empty' is false (likewise with
multiranges), which means you can get multiple identical rows like (5,
'empty'). That will give wrong results using the PK to treat other
columns as functional dependencies in a GROUP BY, and maybe elsewhere.
We don't add such a constraint for UNIQUE constraints, just as we don't
force all their columns to be NOT NULL. (The situation is analogous.)
This updates the docs too which previously said you could use any type
in WITHOUT OVERLAPS, if its GiST opclass implemented stratnum. Now only
range and multirange types are allowed, since only they have isempty.
(We still need stratnum for the non-WITHOUT OVERLAPS columns though.)
---
doc/src/sgml/catalogs.sgml | 4 +-
doc/src/sgml/gist.sgml | 3 -
doc/src/sgml/ref/create_table.sgml | 5 +-
src/backend/access/common/tupdesc.c | 4 +-
src/backend/catalog/heap.c | 30 +-
src/backend/catalog/index.c | 30 ++
src/backend/catalog/pg_constraint.c | 1 +
src/backend/commands/tablecmds.c | 154 +++++-
src/backend/parser/parse_utilcmd.c | 91 +++-
src/backend/utils/cache/relcache.c | 1 +
src/include/access/tupdesc.h | 1 +
src/include/catalog/heap.h | 1 +
src/include/commands/tablecmds.h | 2 +
.../regress/expected/without_overlaps.out | 479 ++++++++++++++++--
src/test/regress/sql/without_overlaps.sql | 248 ++++++++-
15 files changed, 964 insertions(+), 90 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index b530c030f01..31a55d56891 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -2733,7 +2733,9 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
<para>
This constraint is defined with <literal>WITHOUT OVERLAPS</literal>
(for primary keys and unique constraints) or <literal>PERIOD</literal>
- (for foreign keys).
+ (for foreign keys). This is also true for the internal
+ <literal>CHECK</literal> constraint used by <literal>WITHOUT
+ OVERLAPS</literal> primary keys to prevent empty range/multirange values.
</para></entry>
</row>
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
<literal>RTEqualStrategyNumber</literal>, it can be used in the
non-<literal>WITHOUT OVERLAPS</literal> part(s) of an index constraint.
- If it returns results for <literal>RTOverlapStrategyNumber</literal>,
- the operator class can be used in the <literal>WITHOUT
- OVERLAPS</literal> part of an index constraint.
</para>
<para>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 75f06bc49cc..1209fda2451 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -992,10 +992,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<literal>UNIQUE (id, valid_at WITHOUT OVERLAPS)</literal> behaves like
<literal>EXCLUDE USING GIST (id WITH =, valid_at WITH
&&)</literal>. The <literal>WITHOUT OVERLAPS</literal> column
- must have a range or multirange type. (Technically, any type is allowed
- whose default GiST opclass includes an overlaps operator. See the
- <literal>stratnum</literal> support function under <xref
- linkend="gist-extensibility"/> for details.) The non-<literal>WITHOUT
+ must have a range or multirange type. The non-<literal>WITHOUT
OVERLAPS</literal> 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 <xref
diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c
index 47379fef220..194633d5c0b 100644
--- a/src/backend/access/common/tupdesc.c
+++ b/src/backend/access/common/tupdesc.c
@@ -226,6 +226,7 @@ CreateTupleDescCopyConstr(TupleDesc tupdesc)
cpy->check[i].ccbin = pstrdup(constr->check[i].ccbin);
cpy->check[i].ccvalid = constr->check[i].ccvalid;
cpy->check[i].ccnoinherit = constr->check[i].ccnoinherit;
+ cpy->check[i].ccperiod = constr->check[i].ccperiod;
}
}
@@ -548,7 +549,8 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2)
if (!(strcmp(check1->ccname, check2->ccname) == 0 &&
strcmp(check1->ccbin, check2->ccbin) == 0 &&
check1->ccvalid == check2->ccvalid &&
- check1->ccnoinherit == check2->ccnoinherit))
+ check1->ccnoinherit == check2->ccnoinherit &&
+ check1->ccperiod == check2->ccperiod))
return false;
}
}
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 922ba79ac25..88900ef7867 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -103,13 +103,13 @@ static ObjectAddress AddNewRelationType(const char *typeName,
static void RelationRemoveInheritance(Oid relid);
static Oid StoreRelCheck(Relation rel, const char *ccname, Node *expr,
bool is_validated, bool is_local, int inhcount,
- bool is_no_inherit, bool is_internal);
+ bool is_no_inherit, bool is_internal, bool without_overlaps);
static void StoreConstraints(Relation rel, List *cooked_constraints,
bool is_internal);
static bool MergeWithExistingConstraint(Relation rel, const char *ccname, Node *expr,
bool allow_merge, bool is_local,
bool is_initially_valid,
- bool is_no_inherit);
+ bool is_no_inherit, bool conperiod);
static void SetRelationNumChecks(Relation rel, int numchecks);
static Node *cookConstraint(ParseState *pstate,
Node *raw_constraint,
@@ -2065,7 +2065,7 @@ SetAttrMissing(Oid relid, char *attname, char *value)
static Oid
StoreRelCheck(Relation rel, const char *ccname, Node *expr,
bool is_validated, bool is_local, int inhcount,
- bool is_no_inherit, bool is_internal)
+ bool is_no_inherit, bool is_internal, bool without_overlaps)
{
char *ccbin;
List *varList;
@@ -2155,7 +2155,7 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr,
is_local, /* conislocal */
inhcount, /* coninhcount */
is_no_inherit, /* connoinherit */
- false, /* conperiod */
+ without_overlaps, /* conperiod */
is_internal); /* internally constructed? */
pfree(ccbin);
@@ -2252,7 +2252,7 @@ StoreConstraints(Relation rel, List *cooked_constraints, bool is_internal)
StoreRelCheck(rel, con->name, con->expr,
!con->skip_validation, con->is_local,
con->inhcount, con->is_no_inherit,
- is_internal);
+ is_internal, con->conperiod);
numchecks++;
break;
@@ -2399,6 +2399,7 @@ AddRelationNewConstraints(Relation rel,
cooked->is_local = is_local;
cooked->inhcount = is_local ? 0 : 1;
cooked->is_no_inherit = false;
+ cooked->conperiod = false;
cookedConstraints = lappend(cookedConstraints, cooked);
}
@@ -2470,7 +2471,7 @@ AddRelationNewConstraints(Relation rel,
if (MergeWithExistingConstraint(rel, ccname, expr,
allow_merge, is_local,
cdef->initially_valid,
- cdef->is_no_inherit))
+ cdef->is_no_inherit, cdef->without_overlaps))
continue;
}
else
@@ -2518,7 +2519,8 @@ AddRelationNewConstraints(Relation rel,
*/
constrOid =
StoreRelCheck(rel, ccname, expr, cdef->initially_valid, is_local,
- is_local ? 0 : 1, cdef->is_no_inherit, is_internal);
+ is_local ? 0 : 1, cdef->is_no_inherit, is_internal,
+ cdef->without_overlaps);
numchecks++;
@@ -2532,6 +2534,7 @@ AddRelationNewConstraints(Relation rel,
cooked->is_local = is_local;
cooked->inhcount = is_local ? 0 : 1;
cooked->is_no_inherit = cdef->is_no_inherit;
+ cooked->conperiod = cdef->without_overlaps;
cookedConstraints = lappend(cookedConstraints, cooked);
}
else if (cdef->contype == CONSTR_NOTNULL)
@@ -2632,6 +2635,7 @@ AddRelationNewConstraints(Relation rel,
nncooked->is_local = is_local;
nncooked->inhcount = cdef->inhcount;
nncooked->is_no_inherit = cdef->is_no_inherit;
+ nncooked->conperiod = false;
cookedConstraints = lappend(cookedConstraints, nncooked);
}
@@ -2663,7 +2667,7 @@ static bool
MergeWithExistingConstraint(Relation rel, const char *ccname, Node *expr,
bool allow_merge, bool is_local,
bool is_initially_valid,
- bool is_no_inherit)
+ bool is_no_inherit, bool conperiod)
{
bool found;
Relation conDesc;
@@ -2758,6 +2762,16 @@ MergeWithExistingConstraint(Relation rel, const char *ccname, Node *expr,
errmsg("constraint \"%s\" conflicts with NOT VALID constraint on relation \"%s\"",
ccname, RelationGetRelationName(rel))));
+ /*
+ * If either inherited or inheriting is a conperiod CHECK constraint,
+ * the other should be too.
+ */
+ if (conperiod != con->conperiod)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("constraint \"%s\" conflicts with inherited constraint on relation \"%s\"",
+ ccname, RelationGetRelationName(rel))));
+
/* OK to update the tuple */
ereport(NOTICE,
(errmsg("merging constraint \"%s\" with inherited definition",
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 5a8568c55c9..fcba959c646 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1224,6 +1224,36 @@ index_create(Relation heapRelation,
*/
CommandCounterIncrement();
+ /*
+ * If the index is a PRIMARY KEY with WITHOUT OVERLAPS,
+ * we must create a CHECK constraint to prevent range/multirange
+ * values of 'empty'. Since empty doesn't overlap itself,
+ * duplicates would be allowed.
+ */
+ if (isprimary)
+ {
+ ObjectAddress pk_address = InvalidObjectAddress;
+ ObjectAddress check_address = InvalidObjectAddress;
+ Oid pk_oid = InvalidOid;
+ Oid check_oid = InvalidOid;
+
+ if (get_pk_period_check_constraint(heapRelation, &check_oid, &pk_oid))
+ {
+ Assert(OidIsValid(check_oid));
+ Assert(OidIsValid(pk_oid));
+
+ ObjectAddressSet(check_address, ConstraintRelationId, check_oid);
+ ObjectAddressSet(pk_address, ConstraintRelationId, pk_oid);
+
+ /*
+ * Register the CHECK constraint as an INTERNAL dependency of the PK
+ * so that it can't be dropped by hand and is dropped automatically
+ * with the PK.
+ */
+ recordDependencyOn(&check_address, &pk_address, DEPENDENCY_INTERNAL);
+ }
+ }
+
/*
* In bootstrap mode, we have to fill in the index strategy structure with
* information from the catalogs. If we aren't bootstrapping, then the
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index 12a73d5a309..925dacca10e 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -903,6 +903,7 @@ RelationGetNotNullConstraints(Oid relid, bool cooked)
cooked->skip_validation = false;
cooked->is_local = true;
cooked->inhcount = 0;
+ cooked->conperiod = false;
cooked->is_no_inherit = conForm->connoinherit;
notnulls = lappend(notnulls, cooked);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index de0d911b468..356c244257e 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -364,7 +364,7 @@ static void RangeVarCallbackForTruncate(const RangeVar *relation,
static List *MergeAttributes(List *columns, const List *supers, char relpersistence,
bool is_partition, List **supconstr,
List **supnotnulls);
-static List *MergeCheckConstraint(List *constraints, const char *name, Node *expr);
+static List *MergeCheckConstraint(List *constraints, const char *name, Node *expr, bool conperiod);
static void MergeChildAttribute(List *inh_columns, int exist_attno, int newcol_attno, const ColumnDef *newdef);
static ColumnDef *MergeInheritedAttribute(List *inh_columns, int exist_attno, const ColumnDef *newdef);
static void MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel, bool ispartition);
@@ -584,7 +584,7 @@ static void RememberIndexForRebuilding(Oid indoid, AlteredTableInfo *tab);
static void RememberStatisticsForRebuilding(Oid stxoid, AlteredTableInfo *tab);
static void ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab,
LOCKMODE lockmode);
-static void ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId,
+static void ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, bool conperiod,
char *cmd, List **wqueue, LOCKMODE lockmode,
bool rewrite);
static void RebuildConstraintComment(AlteredTableInfo *tab, AlterTablePass pass,
@@ -960,6 +960,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
cooked->is_local = true; /* not used for defaults */
cooked->inhcount = 0; /* ditto */
cooked->is_no_inherit = false;
+ cooked->conperiod = false;
cookedDefaults = lappend(cookedDefaults, cooked);
attr->atthasdef = true;
}
@@ -2811,6 +2812,7 @@ MergeAttributes(List *columns, const List *supers, char relpersistence,
nn->is_local = false;
nn->inhcount = 1;
nn->is_no_inherit = false;
+ nn->conperiod = false;
nnconstraints = lappend(nnconstraints, nn);
}
@@ -2922,7 +2924,7 @@ MergeAttributes(List *columns, const List *supers, char relpersistence,
name,
RelationGetRelationName(relation))));
- constraints = MergeCheckConstraint(constraints, name, expr);
+ constraints = MergeCheckConstraint(constraints, name, expr, check[i].ccperiod);
}
}
@@ -3140,7 +3142,7 @@ MergeAttributes(List *columns, const List *supers, char relpersistence,
* the list.
*/
static List *
-MergeCheckConstraint(List *constraints, const char *name, Node *expr)
+MergeCheckConstraint(List *constraints, const char *name, Node *expr, bool conperiod)
{
ListCell *lc;
CookedConstraint *newcon;
@@ -3155,7 +3157,8 @@ MergeCheckConstraint(List *constraints, const char *name, Node *expr)
if (strcmp(ccon->name, name) != 0)
continue;
- if (equal(expr, ccon->expr))
+ /* Expressions match, and conperiod matches */
+ if (equal(expr, ccon->expr) && ccon->conperiod == conperiod)
{
/* OK to merge constraint with existing */
ccon->inhcount++;
@@ -3181,6 +3184,7 @@ MergeCheckConstraint(List *constraints, const char *name, Node *expr)
newcon->name = pstrdup(name);
newcon->expr = expr;
newcon->inhcount = 1;
+ newcon->conperiod = conperiod;
return lappend(constraints, newcon);
}
@@ -9727,6 +9731,94 @@ ChooseForeignKeyConstraintNameAddition(List *colnames)
return pstrdup(buf);
}
+/*
+ * Gets the temporal PRIMARY KEY constraint oid and its not-empty CHECK constraint.
+ * Returns true if we found both, or else false (e.g. if the table has no PK
+ * or it doesn't use WITHOUT OVERLAPS).
+ *
+ * We may create the PRIMARY KEY first or the CHECK constraint first,
+ * depending on the operation (create-vs-alter table, with-vs-without partitioning
+ * or inheritance, re-adding an index from ALTER TYPE but keeping the CHECK constraint),
+ * so we do nothing unless both are found.
+ */
+bool
+get_pk_period_check_constraint(Relation heapRel, Oid *check_oid, Oid *pk_oid)
+{
+ Relation pg_constraint;
+ HeapTuple conTup;
+ SysScanDesc scan;
+ ScanKeyData key;
+ ArrayType *arr;
+ bool isNull;
+ Datum adatum;
+ int numkeys;
+ int16 *attnums;
+ int16 pk_period_attnum = -1;
+ int16 check_period_attnum = -1;
+
+ pg_constraint = table_open(ConstraintRelationId, AccessShareLock);
+ ScanKeyInit(&key,
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ RelationGetRelid(heapRel));
+
+ scan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId,
+ true, NULL, 1, &key);
+
+ while (HeapTupleIsValid(conTup = systable_getnext(scan)))
+ {
+ Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(conTup);
+
+ /*
+ * Find both the PRIMARY KEY and the CHECK constraint.
+ * They should be validated and with true conperiod.
+ */
+ if (con->contype != CONSTRAINT_PRIMARY && con->contype != CONSTRAINT_CHECK)
+ continue;
+ if (!con->convalidated)
+ continue;
+ if (!con->conperiod)
+ continue;
+
+ adatum = heap_getattr(conTup, Anum_pg_constraint_conkey,
+ RelationGetDescr(pg_constraint), &isNull);
+ if (isNull)
+ elog(ERROR, "null conkey for constraint %u", con->oid);
+
+ arr = DatumGetArrayTypeP(adatum);
+ numkeys = ARR_DIMS(arr)[0];
+ if (ARR_NDIM(arr) != 1 ||
+ numkeys < 0 ||
+ ARR_HASNULL(arr) ||
+ ARR_ELEMTYPE(arr) != INT2OID)
+ elog(ERROR, "conkey is not a 1-D smallint array");
+
+ attnums = (int16 *) ARR_DATA_PTR(arr);
+ if (con->contype == CONSTRAINT_PRIMARY)
+ {
+ pk_period_attnum = attnums[numkeys - 1];
+ *pk_oid = con->oid;
+ }
+ else
+ {
+ check_period_attnum = attnums[numkeys - 1];
+ *check_oid = con->oid;
+ }
+ }
+ systable_endscan(scan);
+ table_close(pg_constraint, AccessShareLock);
+
+ if (pk_period_attnum != -1 && check_period_attnum != -1)
+ {
+ if (check_period_attnum != pk_period_attnum)
+ elog(ERROR, "WITHOUT OVERLAPS CHECK constraint should match the PRIMARY KEY attribute");
+
+ return true;
+ }
+ else
+ return false;
+}
+
/*
* Add a check or not-null constraint to a single table and its children.
* Returns the address of the constraint added to the parent relation,
@@ -9819,6 +9911,34 @@ ATAddCheckNNConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* Advance command counter in case same table is visited multiple times */
CommandCounterIncrement();
+ /*
+ * If this is a not-empty CHECK constraint for a WITHOUT OVERLAPS PK,
+ * we must record it as an INTERNAL dependency.
+ */
+ if (constr->without_overlaps)
+ {
+ ObjectAddress pk_address = InvalidObjectAddress;
+ ObjectAddress check_address = InvalidObjectAddress;
+ Oid pk_oid = InvalidOid;
+ Oid check_oid = InvalidOid;
+
+ if (get_pk_period_check_constraint(rel, &check_oid, &pk_oid))
+ {
+ Assert(OidIsValid(check_oid));
+ Assert(OidIsValid(pk_oid));
+
+ ObjectAddressSet(check_address, ConstraintRelationId, check_oid);
+ ObjectAddressSet(pk_address, ConstraintRelationId, pk_oid);
+
+ /*
+ * Register the CHECK constraint as an INTERNAL dependency of the PK
+ * so that it can't be dropped by hand and is dropped automatically
+ * with the PK.
+ */
+ recordDependencyOn(&check_address, &pk_address, DEPENDENCY_INTERNAL);
+ }
+ }
+
/*
* If the constraint got merged with an existing constraint, we're done.
* We mustn't recurse to child tables in this case, because they've
@@ -14455,6 +14575,7 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
Oid confrelid;
char contype;
bool conislocal;
+ bool conperiod;
tup = SearchSysCache1(CONSTROID, ObjectIdGetDatum(oldId));
if (!HeapTupleIsValid(tup)) /* should not happen */
@@ -14472,6 +14593,7 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
confrelid = con->confrelid;
contype = con->contype;
conislocal = con->conislocal;
+ conperiod = con->conperiod;
ReleaseSysCache(tup);
ObjectAddressSet(obj, ConstraintRelationId, oldId);
@@ -14496,7 +14618,7 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
if (relid != tab->relid && contype == CONSTRAINT_FOREIGN)
LockRelationOid(relid, AccessExclusiveLock);
- ATPostAlterTypeParse(oldId, relid, confrelid,
+ ATPostAlterTypeParse(oldId, relid, confrelid, conperiod,
(char *) lfirst(def_item),
wqueue, lockmode, tab->rewrite);
}
@@ -14507,7 +14629,7 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
Oid relid;
relid = IndexGetRelation(oldId, false);
- ATPostAlterTypeParse(oldId, relid, InvalidOid,
+ ATPostAlterTypeParse(oldId, relid, InvalidOid, false,
(char *) lfirst(def_item),
wqueue, lockmode, tab->rewrite);
@@ -14523,7 +14645,7 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
Oid relid;
relid = StatisticsGetRelation(oldId, false);
- ATPostAlterTypeParse(oldId, relid, InvalidOid,
+ ATPostAlterTypeParse(oldId, relid, InvalidOid, false,
(char *) lfirst(def_item),
wqueue, lockmode, tab->rewrite);
@@ -14587,8 +14709,8 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
* operator that's not available for the new column type.
*/
static void
-ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
- List **wqueue, LOCKMODE lockmode, bool rewrite)
+ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, bool conperiod,
+ char *cmd, List **wqueue, LOCKMODE lockmode, bool rewrite)
{
List *raw_parsetree_list;
List *querytree_list;
@@ -14717,6 +14839,8 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
!rewrite && tab->rewrite == 0)
TryReuseForeignKey(oldId, con);
con->reset_default_tblspc = true;
+ /* preserve conperiod */
+ con->without_overlaps = conperiod;
cmd->subtype = AT_ReAddConstraint;
tab->subcmds[AT_PASS_OLD_CONSTR] =
lappend(tab->subcmds[AT_PASS_OLD_CONSTR], cmd);
@@ -16699,6 +16823,16 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel)
errmsg("constraint \"%s\" conflicts with NOT VALID constraint on child table \"%s\"",
NameStr(child_con->conname), RelationGetRelationName(child_rel))));
+ /*
+ * If the parent and child CHECK constraints have different conperiod values,
+ * don't merge them.
+ */
+ if (parent_con->contype == CONSTRAINT_CHECK &&
+ parent_con->conperiod != child_con->conperiod)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("constraint \"%s\" conflicts with inherited constraint on child table \"%s\"",
+ NameStr(child_con->conname), RelationGetRelationName(child_rel))));
/*
* OK, bump the child constraint's inheritance count. (If we fail
* later on, this change will just roll back.)
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 0598e897d90..db7bd53d6b5 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -2301,6 +2301,8 @@ transformIndexConstraints(CreateStmtContext *cxt)
*
* For a PRIMARY KEY constraint, we additionally force the columns to be
* marked as not-null, without producing a not-null constraint.
+ * If the PRIMARY KEY has WITHOUT OVERLAPS we also add an internal
+ * CHECK constraint to prevent empty ranges/multiranges.
*/
static IndexStmt *
transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
@@ -2564,7 +2566,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
{
@@ -2575,6 +2578,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)
@@ -2642,6 +2646,7 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
if (strcmp(key, inhname) == 0)
{
found = true;
+ typid = inhattr->atttypid;
break;
}
}
@@ -2683,6 +2688,49 @@ 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);
@@ -2719,8 +2767,47 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
/* WITHOUT OVERLAPS requires a GiST index */
index->accessMethod = "gist";
- }
+ if (constraint->contype == CONSTR_PRIMARY)
+ {
+ /*
+ * If the PRIMARY KEY has WITHOUT OVERLAPS, we must
+ * prevent empties as well as NULLs. Since
+ * 'empty' && 'empty' is false, you could insert a value
+ * like (5, 'empty') more than once. For convenience
+ * we add this to notnullcmds (by analogy).
+ */
+ char *key = strVal(llast(constraint->keys));
+ AlterTableCmd *notemptycmd = makeNode(AlterTableCmd);
+ Constraint *checkcon = makeNode(Constraint);
+ ColumnRef *col;
+ FuncCall *func;
+ Node *expr;
+
+ col = makeNode(ColumnRef);
+ col->fields = list_make1(makeString(key));
+ func = makeFuncCall(SystemFuncName("isempty"), list_make1(col),
+ COERCE_EXPLICIT_CALL, -1);
+ expr = (Node *) makeBoolExpr(NOT_EXPR, list_make1(func), -1);
+
+ checkcon->conname = psprintf("%s_not_empty", key);
+ checkcon->contype = CONSTR_CHECK;
+ checkcon->without_overlaps = true;
+ checkcon->raw_expr = expr;
+ checkcon->cooked_expr = NULL;
+ checkcon->is_no_inherit = false;
+ checkcon->deferrable = false;
+ checkcon->initdeferred = false;
+ checkcon->skip_validation = false;
+ checkcon->initially_valid = true;
+ checkcon->location = -1;
+
+ notemptycmd->subtype = AT_AddConstraint;
+ notemptycmd->def = (Node *) checkcon;
+
+ notnullcmds = lappend(notnullcmds, notemptycmd);
+ }
+ }
}
/*
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 262c9878dd3..1b4590e1212 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4581,6 +4581,7 @@ CheckConstraintFetch(Relation relation)
check[found].ccvalid = conform->convalidated;
check[found].ccnoinherit = conform->connoinherit;
+ check[found].ccperiod = conform->conperiod;
check[found].ccname = MemoryContextStrdup(CacheMemoryContext,
NameStr(conform->conname));
diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h
index 8930a28d660..f2adffe4bd1 100644
--- a/src/include/access/tupdesc.h
+++ b/src/include/access/tupdesc.h
@@ -31,6 +31,7 @@ typedef struct ConstrCheck
char *ccbin; /* nodeToString representation of expr */
bool ccvalid;
bool ccnoinherit; /* this is a non-inheritable constraint */
+ bool ccperiod; /* used by WITHOUT OVERLAPS PRIMARY KEY */
} ConstrCheck;
/* This structure contains constraints of a tuple */
diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h
index e446d49b3ea..54f5b792e7b 100644
--- a/src/include/catalog/heap.h
+++ b/src/include/catalog/heap.h
@@ -45,6 +45,7 @@ typedef struct CookedConstraint
int inhcount; /* number of times constraint is inherited */
bool is_no_inherit; /* constraint has local def and cannot be
* inherited */
+ bool conperiod; /* constraint is for WITHOUT OVERLAPS */
} CookedConstraint;
extern Relation heap_create(const char *relname,
diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h
index 85cbad3d0c2..6a92e55988b 100644
--- a/src/include/commands/tablecmds.h
+++ b/src/include/commands/tablecmds.h
@@ -27,6 +27,8 @@ struct AlterTableUtilityContext; /* avoid including tcop/utility.h here */
extern ObjectAddress DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
ObjectAddress *typaddress, const char *queryString);
+extern bool get_pk_period_check_constraint(Relation heapRel, Oid *check_oid, Oid *pk_oid);
+
extern TupleDesc BuildDescForRelation(const List *columns);
extern void RemoveRelations(DropStmt *drop);
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index e2f2a1cbe20..8f16af2f7b0 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,
@@ -46,6 +47,8 @@ CREATE TABLE temporal_rng (
valid_at | daterange | | not null |
Indexes:
"temporal_rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng_pk';
pg_get_constraintdef
@@ -59,6 +62,108 @@ SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'te
CREATE UNIQUE INDEX temporal_rng_pk ON temporal_rng USING gist (id, valid_at)
(1 row)
+-- can't drop the CHECK constraint:
+ALTER TABLE temporal_rng DROP CONSTRAINT valid_at_not_empty;
+ERROR: cannot drop constraint valid_at_not_empty on table temporal_rng because constraint temporal_rng_pk on table temporal_rng requires it
+HINT: You can drop constraint temporal_rng_pk on table temporal_rng instead.
+-- dropping the PK drops the CHECK constraint:
+ALTER TABLE temporal_rng DROP CONSTRAINT temporal_rng_pk;
+\d temporal_rng
+ Table "public.temporal_rng"
+ Column | Type | Collation | Nullable | Default
+----------+-----------+-----------+----------+---------
+ id | int4range | | |
+ valid_at | daterange | | |
+
+-- fail if a constraint with that name already exists:
+ALTER TABLE temporal_rng ADD CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at));
+ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+ERROR: constraint "valid_at_not_empty" for relation "temporal_rng" already exists
+DROP TABLE temporal_rng;
+-- PK from LIKE:
+CREATE TABLE temporal_rng (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+CREATE TABLE temporal_rng2 (LIKE temporal_rng INCLUDING ALL);
+\d temporal_rng2
+ Table "public.temporal_rng2"
+ Column | Type | Collation | Nullable | Default
+----------+-----------+-----------+----------+---------
+ id | int4range | | not null |
+ valid_at | daterange | | not null |
+Indexes:
+ "temporal_rng2_pkey" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
+
+DROP TABLE temporal_rng2;
+CREATE TABLE temporal_rng2 (LIKE temporal_rng INCLUDING ALL, CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at)));
+ERROR: constraint "valid_at_not_empty" for relation "temporal_rng2" already exists
+DROP TABLE temporal_rng;
+-- PK from INHERITS:
+CREATE TABLE temporal_rng (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+CREATE TABLE temporal_rng2 () INHERITS (temporal_rng);
+\d temporal_rng2
+ Table "public.temporal_rng2"
+ Column | Type | Collation | Nullable | Default
+----------+-----------+-----------+----------+---------
+ id | int4range | | not null |
+ valid_at | daterange | | not null |
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
+Inherits: temporal_rng
+
+DROP TABLE temporal_rng2;
+CREATE TABLE temporal_rng2 (CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at))) INHERITS (temporal_rng);
+ERROR: constraint "valid_at_not_empty" conflicts with inherited constraint on relation "temporal_rng2"
+DROP TABLE temporal_rng;
+-- PK in inheriting table:
+CREATE TABLE temporal_rng (
+ id int4range,
+ valid_at daterange
+);
+CREATE TABLE temporal_rng2 (CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)) INHERITS (temporal_rng);
+\d temporal_rng2
+ Table "public.temporal_rng2"
+ Column | Type | Collation | Nullable | Default
+----------+-----------+-----------+----------+---------
+ id | int4range | | not null |
+ valid_at | daterange | | not null |
+Indexes:
+ "temporal_rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
+Inherits: temporal_rng
+
+DROP TABLE temporal_rng CASCADE;
+NOTICE: drop cascades to table temporal_rng2
+-- PK in inheriting table with conflicting CHECK constraint in parent:
+CREATE TABLE temporal_rng (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at))
+);
+CREATE TABLE temporal_rng2 (CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)) INHERITS (temporal_rng);
+ERROR: constraint "valid_at_not_empty" conflicts with inherited constraint on relation "temporal_rng2"
+DROP TABLE temporal_rng;
+-- add PK to already inheriting table:
+CREATE TABLE temporal_rng (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at))
+);
+CREATE TABLE temporal_rng2 () INHERITS (temporal_rng);
+ALTER TABLE temporal_rng2
+ ADD CONSTRAINT temporal_rng2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+ERROR: constraint "valid_at_not_empty" conflicts with inherited constraint on relation "temporal_rng2"
+DROP TABLE temporal_rng2;
+DROP TABLE temporal_rng;
-- PK with two columns plus a range:
-- We don't drop this table because tests below also need multiple scalar columns.
CREATE TABLE temporal_rng2 (
@@ -76,6 +181,8 @@ CREATE TABLE temporal_rng2 (
valid_at | daterange | | not null |
Indexes:
"temporal_rng2_pk" PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng2_pk';
pg_get_constraintdef
@@ -113,7 +220,33 @@ CREATE TABLE temporal_mltrng (
valid_at | datemultirange | | not null |
Indexes:
"temporal_mltrng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
+-- can't drop the CHECK constraint:
+ALTER TABLE temporal_mltrng DROP CONSTRAINT valid_at_not_empty;
+ERROR: cannot drop constraint valid_at_not_empty on table temporal_mltrng because constraint temporal_mltrng_pk on table temporal_mltrng requires it
+HINT: You can drop constraint temporal_mltrng_pk on table temporal_mltrng instead.
+-- dropping the PK drops the CHECK constraint:
+ALTER TABLE temporal_mltrng DROP CONSTRAINT temporal_rng_pk;
+ERROR: constraint "temporal_rng_pk" of relation "temporal_mltrng" does not exist
+\d temporal_mltrng
+ Table "public.temporal_mltrng"
+ Column | Type | Collation | Nullable | Default
+----------+----------------+-----------+----------+---------
+ id | int4range | | not null |
+ valid_at | datemultirange | | not null |
+Indexes:
+ "temporal_mltrng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
+
+-- fail if a constraint with that name already exists:
+ALTER TABLE temporal_mltrng ADD CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at));
+ERROR: constraint "valid_at_not_empty" for relation "temporal_mltrng" already exists
+ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+ERROR: multiple primary keys for table "temporal_mltrng" are not allowed
+DROP TABLE temporal_mltrng;
-- PK with two columns plus a multirange:
-- We don't drop this table because tests below also need multiple scalar columns.
CREATE TABLE temporal_mltrng2 (
@@ -131,6 +264,8 @@ CREATE TABLE temporal_mltrng2 (
valid_at | datemultirange | | not null |
Indexes:
"temporal_mltrng2_pk" PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_mltrng2_pk';
pg_get_constraintdef
@@ -144,6 +279,72 @@ SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'te
CREATE UNIQUE INDEX temporal_mltrng2_pk ON temporal_mltrng2 USING gist (id1, id2, valid_at)
(1 row)
+-- test when the WITHOUT OVERLAPS column type changes:
+CREATE TABLE temporal_rng (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+ALTER TABLE temporal_rng
+ ALTER COLUMN valid_at TYPE daterange USING daterange(lower(valid_at), upper(valid_at));
+NOTICE: merging constraint "valid_at_not_empty" with inherited definition
+\d temporal_rng
+ Table "public.temporal_rng"
+ Column | Type | Collation | Nullable | Default
+----------+-----------+-----------+----------+---------
+ id | int4range | | not null |
+ valid_at | daterange | | not null |
+Indexes:
+ "temporal_rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
+
+-- can't drop the CHECK constraint:
+ALTER TABLE temporal_rng DROP CONSTRAINT valid_at_not_empty;
+ERROR: cannot drop constraint valid_at_not_empty on table temporal_rng because constraint temporal_rng_pk on table temporal_rng requires it
+HINT: You can drop constraint temporal_rng_pk on table temporal_rng instead.
+-- dropping the PK drops the CHECK constraint:
+ALTER TABLE temporal_rng DROP CONSTRAINT temporal_rng_pk;
+\d temporal_rng
+ Table "public.temporal_rng"
+ Column | Type | Collation | Nullable | Default
+----------+-----------+-----------+----------+---------
+ id | int4range | | |
+ valid_at | daterange | | |
+
+DROP TABLE temporal_rng;
+-- test when the WITHOUT OVERLAPS column name changes:
+CREATE TABLE temporal_rng (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+ALTER TABLE temporal_rng RENAME valid_at TO valid_at1;
+\d temporal_rng
+ Table "public.temporal_rng"
+ Column | Type | Collation | Nullable | Default
+-----------+-----------+-----------+----------+---------
+ id | int4range | | not null |
+ valid_at1 | daterange | | not null |
+Indexes:
+ "temporal_rng_pk" PRIMARY KEY (id, valid_at1 WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at1))
+
+-- can't drop the CHECK constraint:
+ALTER TABLE temporal_rng DROP CONSTRAINT valid_at_not_empty;
+ERROR: cannot drop constraint valid_at_not_empty on table temporal_rng because constraint temporal_rng_pk on table temporal_rng requires it
+HINT: You can drop constraint temporal_rng_pk on table temporal_rng instead.
+-- dropping the PK drops the CHECK constraint:
+ALTER TABLE temporal_rng DROP CONSTRAINT temporal_rng_pk;
+\d temporal_rng
+ Table "public.temporal_rng"
+ Column | Type | Collation | Nullable | Default
+-----------+-----------+-----------+----------+---------
+ id | int4range | | |
+ valid_at1 | daterange | | |
+
+DROP TABLE temporal_rng;
-- UNIQUE with no columns just WITHOUT OVERLAPS:
CREATE TABLE temporal_rng3 (
valid_at daterange,
@@ -164,8 +365,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,
@@ -237,7 +439,6 @@ DROP TYPE textrange2;
--
-- test ALTER TABLE ADD CONSTRAINT
--
-DROP TABLE temporal_rng;
CREATE TABLE temporal_rng (
id int4range,
valid_at daterange
@@ -245,6 +446,49 @@ CREATE TABLE temporal_rng (
ALTER TABLE temporal_rng
ADD CONSTRAINT temporal_rng_pk
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+-- can't drop the CHECK constraint:
+ALTER TABLE temporal_rng DROP CONSTRAINT valid_at_not_empty;
+ERROR: cannot drop constraint valid_at_not_empty on table temporal_rng because constraint temporal_rng_pk on table temporal_rng requires it
+HINT: You can drop constraint temporal_rng_pk on table temporal_rng instead.
+-- dropping the PK drops the CHECK constraint:
+ALTER TABLE temporal_rng DROP CONSTRAINT temporal_rng_pk;
+\d temporal_rng
+ Table "public.temporal_rng"
+ Column | Type | Collation | Nullable | Default
+----------+-----------+-----------+----------+---------
+ id | int4range | | |
+ valid_at | daterange | | |
+
+-- fail if a constraint with that name already exists:
+ALTER TABLE temporal_rng ADD CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at));
+ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+ERROR: constraint "valid_at_not_empty" for relation "temporal_rng" already exists
+DROP TABLE temporal_rng;
+CREATE TABLE temporal_mltrng (
+ id int4range,
+ valid_at datemultirange
+);
+ALTER TABLE temporal_mltrng
+ ADD CONSTRAINT temporal_mltrng_pk
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+-- can't drop the CHECK constraint:
+ALTER TABLE temporal_mltrng DROP CONSTRAINT valid_at_not_empty;
+ERROR: cannot drop constraint valid_at_not_empty on table temporal_mltrng because constraint temporal_mltrng_pk on table temporal_mltrng requires it
+HINT: You can drop constraint temporal_mltrng_pk on table temporal_mltrng instead.
+-- dropping the PK drops the CHECK constraint:
+ALTER TABLE temporal_mltrng DROP CONSTRAINT temporal_mltrng_pk;
+\d temporal_mltrng
+ Table "public.temporal_mltrng"
+ Column | Type | Collation | Nullable | Default
+----------+----------------+-----------+----------+---------
+ id | int4range | | |
+ valid_at | datemultirange | | |
+
+-- fail if a constraint with that name already exists:
+ALTER TABLE temporal_mltrng ADD CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at));
+ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+ERROR: constraint "valid_at_not_empty" for relation "temporal_mltrng" already exists
+DROP TABLE temporal_mltrng;
-- PK with USING INDEX (not possible):
CREATE TABLE temporal3 (
id int4range,
@@ -292,6 +536,23 @@ ALTER TABLE temporal3
ADD COLUMN valid_at daterange,
ADD CONSTRAINT temporal3_pk
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+-- can't drop the CHECK constraint:
+ALTER TABLE temporal3 DROP CONSTRAINT valid_at_not_empty;
+ERROR: cannot drop constraint valid_at_not_empty on table temporal3 because constraint temporal3_pk on table temporal3 requires it
+HINT: You can drop constraint temporal3_pk on table temporal3 instead.
+-- dropping the PK drops the CHECK constraint:
+ALTER TABLE temporal3 DROP CONSTRAINT temporal3_pk;
+\d temporal3
+ Table "public.temporal3"
+ Column | Type | Collation | Nullable | Default
+----------+-----------+-----------+----------+---------
+ id | int4range | | |
+ valid_at | daterange | | |
+
+-- fail if a constraint with that name already exists:
+ALTER TABLE temporal3 ADD CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at));
+ALTER TABLE temporal3 ADD CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+ERROR: constraint "valid_at_not_empty" for relation "temporal3" already exists
DROP TABLE temporal3;
-- Add range column and UNIQUE constraint at the same time
CREATE TABLE temporal3 (
@@ -305,6 +566,11 @@ DROP TABLE temporal3;
--
-- test PK inserts
--
+CREATE TABLE temporal_rng (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
-- okay:
INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-03'));
INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018-04-04'));
@@ -320,6 +586,14 @@ 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).
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', 'empty');
+ERROR: new row for relation "temporal_rng" violates check constraint "valid_at_not_empty"
+DETAIL: Failing row contains ([3,4), empty).
+CREATE TABLE temporal_mltrng (
+ id int4range,
+ valid_at datemultirange,
+ CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
-- okay:
INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03')));
INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04')));
@@ -335,6 +609,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).
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', '{}');
+ERROR: new row for relation "temporal_mltrng" violates check constraint "valid_at_not_empty"
+DETAIL: Failing row contains ([3,4), {}).
SELECT * FROM temporal_mltrng ORDER BY id, valid_at;
id | valid_at
-------+---------------------------
@@ -344,6 +621,57 @@ SELECT * FROM temporal_mltrng ORDER BY id, valid_at;
[3,4) | {[2018-01-01,)}
(4 rows)
+--
+-- test UNIQUE inserts
+--
+CREATE TABLE temporal_rng3 (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+-- okay:
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-03'));
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018-04-04'));
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[2,3)', daterange('2018-01-01', '2018-01-05'));
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', daterange('2018-01-01', NULL));
+INSERT INTO temporal_rng3 (id, valid_at) VALUES (NULL, daterange('2018-01-01', '2018-01-05'));
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', NULL);
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', 'empty');
+-- should fail:
+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)).
+DROP TABLE temporal_rng3;
+CREATE TABLE temporal_mltrng3 (
+ id int4range,
+ valid_at datemultirange,
+ CONSTRAINT temporal_mltrng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+-- okay:
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03')));
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04')));
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2018-01-01', '2018-01-05')));
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', datemultirange(daterange('2018-01-01', NULL)));
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES (NULL, datemultirange(daterange('2018-01-01', '2018-01-05')));
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', NULL);
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', '{}');
+-- should fail:
+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)}).
+SELECT * FROM temporal_mltrng3 ORDER BY id, valid_at;
+ id | valid_at
+-------+---------------------------
+ [1,2) | {[2018-01-02,2018-02-03)}
+ [1,2) | {[2018-03-03,2018-04-04)}
+ [2,3) | {[2018-01-01,2018-01-05)}
+ [3,4) | {}
+ [3,4) | {[2018-01-01,)}
+ [3,4) |
+ | {[2018-01-01,2018-01-05)}
+(7 rows)
+
+DROP TABLE temporal_mltrng3;
--
-- test a range with both a PK and a UNIQUE constraint
--
@@ -372,6 +700,7 @@ CREATE TABLE temporal3 (
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));
+NOTICE: merging constraint "valid_at_not_empty" with inherited definition
ALTER TABLE temporal3 RENAME COLUMN valid_at TO valid_thru;
ALTER TABLE temporal3 DROP COLUMN valid_thru;
DROP TABLE temporal3;
@@ -383,10 +712,106 @@ CREATE TABLE temporal_partitioned (
id int4range,
valid_at daterange,
name text,
- CONSTRAINT temporal_paritioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+ CONSTRAINT temporal_partitioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
) 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)');
+-- the CHECK constraint is copied:
+\d tp1
+ Table "public.tp1"
+ Column | Type | Collation | Nullable | Default
+----------+-----------+-----------+----------+---------
+ id | int4range | | not null |
+ valid_at | daterange | | not null |
+ name | text | | |
+Partition of: temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)')
+Indexes:
+ "tp1_pkey" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
+
+-- can't drop the CHECK constraint:
+ALTER TABLE tp1 DROP CONSTRAINT valid_at_not_empty;
+ERROR: cannot drop inherited constraint "valid_at_not_empty" of relation "tp1"
+-- dropping the PK drops all the CHECK constraints:
+ALTER TABLE temporal_partitioned DROP CONSTRAINT temporal_partitioned_pk;
+\d temporal_partitioned
+ Partitioned table "public.temporal_partitioned"
+ Column | Type | Collation | Nullable | Default
+----------+-----------+-----------+----------+---------
+ id | int4range | | |
+ valid_at | daterange | | |
+ name | text | | |
+Partition key: LIST (id)
+Number of partitions: 2 (Use \d+ to list them.)
+
+\d tp1
+ Table "public.tp1"
+ Column | Type | Collation | Nullable | Default
+----------+-----------+-----------+----------+---------
+ id | int4range | | |
+ valid_at | daterange | | |
+ name | text | | |
+Partition of: temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)')
+
+-- Now create the temporal PK with ALTER:
+ALTER TABLE temporal_partitioned
+ ADD CONSTRAINT temporal_partitioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+-- the CHECK constraint is copied:
+\d temporal_partitioned
+ Partitioned table "public.temporal_partitioned"
+ Column | Type | Collation | Nullable | Default
+----------+-----------+-----------+----------+---------
+ id | int4range | | not null |
+ valid_at | daterange | | not null |
+ name | text | | |
+Partition key: LIST (id)
+Indexes:
+ "temporal_partitioned_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
+Number of partitions: 2 (Use \d+ to list them.)
+
+\d tp1
+ Table "public.tp1"
+ Column | Type | Collation | Nullable | Default
+----------+-----------+-----------+----------+---------
+ id | int4range | | not null |
+ valid_at | daterange | | not null |
+ name | text | | |
+Partition of: temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)')
+Indexes:
+ "tp1_pkey" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
+
+-- can't drop the CHECK constraint:
+ALTER TABLE tp1 DROP CONSTRAINT valid_at_not_empty;
+ERROR: cannot drop inherited constraint "valid_at_not_empty" of relation "tp1"
+-- dropping the PK drops all the CHECK constraints:
+ALTER TABLE temporal_partitioned DROP CONSTRAINT temporal_partitioned_pk;
+\d temporal_partitioned
+ Partitioned table "public.temporal_partitioned"
+ Column | Type | Collation | Nullable | Default
+----------+-----------+-----------+----------+---------
+ id | int4range | | |
+ valid_at | daterange | | |
+ name | text | | |
+Partition key: LIST (id)
+Number of partitions: 2 (Use \d+ to list them.)
+
+\d tp1
+ Table "public.tp1"
+ Column | Type | Collation | Nullable | Default
+----------+-----------+-----------+----------+---------
+ id | int4range | | |
+ valid_at | daterange | | |
+ name | text | | |
+Partition of: temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)')
+
+-- Restore the PK and test some INSERTs:
+ALTER TABLE temporal_partitioned
+ ADD CONSTRAINT temporal_partitioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
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'),
@@ -418,7 +843,7 @@ CREATE TABLE temporal_partitioned (
id int4range,
valid_at daterange,
name text,
- CONSTRAINT temporal_paritioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+ CONSTRAINT temporal_partitioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
) 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)');
@@ -806,6 +1231,8 @@ CREATE TABLE temporal_fk2_rng2rng (
parent_id2 | int4range | | |
Indexes:
"temporal_fk2_rng2rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
Foreign-key constraints:
"temporal_fk2_rng2rng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_rng2(id1, id2, PERIOD valid_at)
@@ -845,6 +1272,8 @@ ALTER TABLE temporal_fk2_rng2rng
parent_id2 | int4range | | |
Indexes:
"temporal_fk2_rng2rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
Foreign-key constraints:
"temporal_fk2_rng2rng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_rng2(id1, id2, PERIOD valid_at)
@@ -852,6 +1281,7 @@ Foreign-key constraints:
ALTER TABLE temporal_fk_rng2rng
DROP CONSTRAINT temporal_fk_rng2rng_fk,
ALTER COLUMN valid_at TYPE tsrange USING tsrange(lower(valid_at), upper(valid_at));
+NOTICE: merging constraint "valid_at_not_empty" with inherited definition
ALTER TABLE temporal_fk_rng2rng
ADD CONSTRAINT temporal_fk_rng2rng_fk
FOREIGN KEY (parent_id, PERIOD valid_at)
@@ -860,6 +1290,7 @@ ERROR: foreign key constraint "temporal_fk_rng2rng_fk" cannot be implemented
DETAIL: Key columns "valid_at" and "valid_at" are of incompatible types: tsrange and daterange.
ALTER TABLE temporal_fk_rng2rng
ALTER COLUMN valid_at TYPE daterange USING daterange(lower(valid_at)::date, upper(valid_at)::date);
+NOTICE: merging constraint "valid_at_not_empty" with inherited definition
-- with inferred PK on the referenced table:
ALTER TABLE temporal_fk_rng2rng
ADD CONSTRAINT temporal_fk_rng2rng_fk
@@ -1276,6 +1707,8 @@ CREATE TABLE temporal_fk2_mltrng2mltrng (
parent_id2 | int4range | | |
Indexes:
"temporal_fk2_mltrng2mltrng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
Foreign-key constraints:
"temporal_fk2_mltrng2mltrng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_mltrng2(id1, id2, PERIOD valid_at)
@@ -1315,6 +1748,8 @@ ALTER TABLE temporal_fk2_mltrng2mltrng
parent_id2 | int4range | | |
Indexes:
"temporal_fk2_mltrng2mltrng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
Foreign-key constraints:
"temporal_fk2_mltrng2mltrng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_mltrng2(id1, id2, PERIOD valid_at)
@@ -1536,41 +1971,13 @@ ERROR: update or delete on table "temporal_mltrng" violates foreign key constra
DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
ROLLBACK;
--
--- test FOREIGN KEY, box references box
--- (not allowed: PERIOD part must be a range or multirange)
---
-CREATE TABLE temporal_box (
- id int4range,
- valid_at box,
- CONSTRAINT temporal_box_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
-);
-\d temporal_box
- Table "public.temporal_box"
- Column | Type | Collation | Nullable | Default
-----------+-----------+-----------+----------+---------
- id | int4range | | not null |
- valid_at | box | | not null |
-Indexes:
- "temporal_box_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
-
-CREATE TABLE temporal_fk_box2box (
- id int4range,
- valid_at box,
- parent_id int4range,
- CONSTRAINT temporal_fk_box2box_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
- CONSTRAINT temporal_fk_box2box_fk FOREIGN KEY (parent_id, PERIOD valid_at)
- REFERENCES temporal_box (id, PERIOD valid_at)
-);
-ERROR: invalid type for PERIOD part of foreign key
-DETAIL: Only range and multirange are supported.
---
-- FK between partitioned tables
--
CREATE TABLE temporal_partitioned_rng (
id int4range,
valid_at daterange,
name text,
- CONSTRAINT temporal_paritioned_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+ CONSTRAINT temporal_partitioned_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
) PARTITION BY LIST (id);
CREATE TABLE tp1 partition OF temporal_partitioned_rng FOR VALUES IN ('[1,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)');
CREATE TABLE tp2 partition OF temporal_partitioned_rng FOR VALUES IN ('[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)');
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index 5d41a6bd628..7d32d410f0c 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -45,6 +45,68 @@ CREATE TABLE temporal_rng (
\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';
+-- can't drop the CHECK constraint:
+ALTER TABLE temporal_rng DROP CONSTRAINT valid_at_not_empty;
+-- dropping the PK drops the CHECK constraint:
+ALTER TABLE temporal_rng DROP CONSTRAINT temporal_rng_pk;
+\d temporal_rng
+-- fail if a constraint with that name already exists:
+ALTER TABLE temporal_rng ADD CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at));
+ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+DROP TABLE temporal_rng;
+
+-- PK from LIKE:
+CREATE TABLE temporal_rng (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+CREATE TABLE temporal_rng2 (LIKE temporal_rng INCLUDING ALL);
+\d temporal_rng2
+DROP TABLE temporal_rng2;
+CREATE TABLE temporal_rng2 (LIKE temporal_rng INCLUDING ALL, CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at)));
+DROP TABLE temporal_rng;
+
+-- PK from INHERITS:
+CREATE TABLE temporal_rng (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+CREATE TABLE temporal_rng2 () INHERITS (temporal_rng);
+\d temporal_rng2
+DROP TABLE temporal_rng2;
+CREATE TABLE temporal_rng2 (CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at))) INHERITS (temporal_rng);
+DROP TABLE temporal_rng;
+
+-- PK in inheriting table:
+CREATE TABLE temporal_rng (
+ id int4range,
+ valid_at daterange
+);
+CREATE TABLE temporal_rng2 (CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)) INHERITS (temporal_rng);
+\d temporal_rng2
+DROP TABLE temporal_rng CASCADE;
+-- PK in inheriting table with conflicting CHECK constraint in parent:
+CREATE TABLE temporal_rng (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at))
+);
+CREATE TABLE temporal_rng2 (CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)) INHERITS (temporal_rng);
+DROP TABLE temporal_rng;
+
+-- add PK to already inheriting table:
+CREATE TABLE temporal_rng (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at))
+);
+CREATE TABLE temporal_rng2 () INHERITS (temporal_rng);
+ALTER TABLE temporal_rng2
+ ADD CONSTRAINT temporal_rng2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+DROP TABLE temporal_rng2;
+DROP TABLE temporal_rng;
-- PK with two columns plus a range:
-- We don't drop this table because tests below also need multiple scalar columns.
@@ -76,6 +138,15 @@ CREATE TABLE temporal_mltrng (
CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
);
\d temporal_mltrng
+-- can't drop the CHECK constraint:
+ALTER TABLE temporal_mltrng DROP CONSTRAINT valid_at_not_empty;
+-- dropping the PK drops the CHECK constraint:
+ALTER TABLE temporal_mltrng DROP CONSTRAINT temporal_rng_pk;
+\d temporal_mltrng
+-- fail if a constraint with that name already exists:
+ALTER TABLE temporal_mltrng ADD CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at));
+ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+DROP TABLE temporal_mltrng;
-- PK with two columns plus a multirange:
-- We don't drop this table because tests below also need multiple scalar columns.
@@ -89,6 +160,37 @@ CREATE TABLE temporal_mltrng2 (
SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_mltrng2_pk';
SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_mltrng2_pk';
+-- test when the WITHOUT OVERLAPS column type changes:
+CREATE TABLE temporal_rng (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+ALTER TABLE temporal_rng
+ ALTER COLUMN valid_at TYPE daterange USING daterange(lower(valid_at), upper(valid_at));
+\d temporal_rng
+-- can't drop the CHECK constraint:
+ALTER TABLE temporal_rng DROP CONSTRAINT valid_at_not_empty;
+-- dropping the PK drops the CHECK constraint:
+ALTER TABLE temporal_rng DROP CONSTRAINT temporal_rng_pk;
+\d temporal_rng
+DROP TABLE temporal_rng;
+
+-- test when the WITHOUT OVERLAPS column name changes:
+CREATE TABLE temporal_rng (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+ALTER TABLE temporal_rng RENAME valid_at TO valid_at1;
+\d temporal_rng
+-- can't drop the CHECK constraint:
+ALTER TABLE temporal_rng DROP CONSTRAINT valid_at_not_empty;
+-- dropping the PK drops the CHECK constraint:
+ALTER TABLE temporal_rng DROP CONSTRAINT temporal_rng_pk;
+\d temporal_rng
+DROP TABLE temporal_rng;
+
-- UNIQUE with no columns just WITHOUT OVERLAPS:
CREATE TABLE temporal_rng3 (
@@ -150,7 +252,6 @@ DROP TYPE textrange2;
-- test ALTER TABLE ADD CONSTRAINT
--
-DROP TABLE temporal_rng;
CREATE TABLE temporal_rng (
id int4range,
valid_at daterange
@@ -158,8 +259,35 @@ CREATE TABLE temporal_rng (
ALTER TABLE temporal_rng
ADD CONSTRAINT temporal_rng_pk
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+-- can't drop the CHECK constraint:
+ALTER TABLE temporal_rng DROP CONSTRAINT valid_at_not_empty;
+-- dropping the PK drops the CHECK constraint:
+ALTER TABLE temporal_rng DROP CONSTRAINT temporal_rng_pk;
+\d temporal_rng
+-- fail if a constraint with that name already exists:
+ALTER TABLE temporal_rng ADD CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at));
+ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+DROP TABLE temporal_rng;
+
+CREATE TABLE temporal_mltrng (
+ id int4range,
+ valid_at datemultirange
+);
+ALTER TABLE temporal_mltrng
+ ADD CONSTRAINT temporal_mltrng_pk
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+-- can't drop the CHECK constraint:
+ALTER TABLE temporal_mltrng DROP CONSTRAINT valid_at_not_empty;
+-- dropping the PK drops the CHECK constraint:
+ALTER TABLE temporal_mltrng DROP CONSTRAINT temporal_mltrng_pk;
+\d temporal_mltrng
+-- fail if a constraint with that name already exists:
+ALTER TABLE temporal_mltrng ADD CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at));
+ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+DROP TABLE temporal_mltrng;
-- PK with USING INDEX (not possible):
+
CREATE TABLE temporal3 (
id int4range,
valid_at daterange
@@ -200,6 +328,14 @@ ALTER TABLE temporal3
ADD COLUMN valid_at daterange,
ADD CONSTRAINT temporal3_pk
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+-- can't drop the CHECK constraint:
+ALTER TABLE temporal3 DROP CONSTRAINT valid_at_not_empty;
+-- dropping the PK drops the CHECK constraint:
+ALTER TABLE temporal3 DROP CONSTRAINT temporal3_pk;
+\d temporal3
+-- fail if a constraint with that name already exists:
+ALTER TABLE temporal3 ADD CONSTRAINT valid_at_not_empty CHECK (NOT isempty(valid_at));
+ALTER TABLE temporal3 ADD CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
DROP TABLE temporal3;
-- Add range column and UNIQUE constraint at the same time
@@ -216,6 +352,12 @@ DROP TABLE temporal3;
-- test PK inserts
--
+CREATE TABLE temporal_rng (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+
-- okay:
INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-03'));
INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018-04-04'));
@@ -226,6 +368,13 @@ 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);
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', 'empty');
+
+CREATE TABLE temporal_mltrng (
+ id int4range,
+ valid_at datemultirange,
+ CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
-- okay:
INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03')));
@@ -237,9 +386,56 @@ 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);
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', '{}');
SELECT * FROM temporal_mltrng ORDER BY id, valid_at;
+--
+-- test UNIQUE inserts
+--
+
+CREATE TABLE temporal_rng3 (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+
+-- okay:
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-03'));
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018-04-04'));
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[2,3)', daterange('2018-01-01', '2018-01-05'));
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', daterange('2018-01-01', NULL));
+INSERT INTO temporal_rng3 (id, valid_at) VALUES (NULL, daterange('2018-01-01', '2018-01-05'));
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', NULL);
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', 'empty');
+
+-- should fail:
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-01-01', '2018-01-05'));
+
+DROP TABLE temporal_rng3;
+
+CREATE TABLE temporal_mltrng3 (
+ id int4range,
+ valid_at datemultirange,
+ CONSTRAINT temporal_mltrng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+
+-- okay:
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03')));
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04')));
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2018-01-01', '2018-01-05')));
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', datemultirange(daterange('2018-01-01', NULL)));
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES (NULL, datemultirange(daterange('2018-01-01', '2018-01-05')));
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', NULL);
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', '{}');
+
+-- should fail:
+INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-01', '2018-01-05')));
+
+SELECT * FROM temporal_mltrng3 ORDER BY id, valid_at;
+
+DROP TABLE temporal_mltrng3;
+
--
-- test a range with both a PK and a UNIQUE constraint
--
@@ -284,10 +480,33 @@ CREATE TABLE temporal_partitioned (
id int4range,
valid_at daterange,
name text,
- CONSTRAINT temporal_paritioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+ CONSTRAINT temporal_partitioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
) 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)');
+-- the CHECK constraint is copied:
+\d tp1
+-- can't drop the CHECK constraint:
+ALTER TABLE tp1 DROP CONSTRAINT valid_at_not_empty;
+-- dropping the PK drops all the CHECK constraints:
+ALTER TABLE temporal_partitioned DROP CONSTRAINT temporal_partitioned_pk;
+\d temporal_partitioned
+\d tp1
+-- Now create the temporal PK with ALTER:
+ALTER TABLE temporal_partitioned
+ ADD CONSTRAINT temporal_partitioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+-- the CHECK constraint is copied:
+\d temporal_partitioned
+\d tp1
+-- can't drop the CHECK constraint:
+ALTER TABLE tp1 DROP CONSTRAINT valid_at_not_empty;
+-- dropping the PK drops all the CHECK constraints:
+ALTER TABLE temporal_partitioned DROP CONSTRAINT temporal_partitioned_pk;
+\d temporal_partitioned
+\d tp1
+-- Restore the PK and test some INSERTs:
+ALTER TABLE temporal_partitioned
+ ADD CONSTRAINT temporal_partitioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
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'),
@@ -302,7 +521,7 @@ CREATE TABLE temporal_partitioned (
id int4range,
valid_at daterange,
name text,
- CONSTRAINT temporal_paritioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+ CONSTRAINT temporal_partitioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
) 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)');
@@ -1273,27 +1492,6 @@ BEGIN;
DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
ROLLBACK;
---
--- test FOREIGN KEY, box references box
--- (not allowed: PERIOD part must be a range or multirange)
---
-
-CREATE TABLE temporal_box (
- id int4range,
- valid_at box,
- CONSTRAINT temporal_box_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
-);
-\d temporal_box
-
-CREATE TABLE temporal_fk_box2box (
- id int4range,
- valid_at box,
- parent_id int4range,
- CONSTRAINT temporal_fk_box2box_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
- CONSTRAINT temporal_fk_box2box_fk FOREIGN KEY (parent_id, PERIOD valid_at)
- REFERENCES temporal_box (id, PERIOD valid_at)
-);
-
--
-- FK between partitioned tables
--
@@ -1302,7 +1500,7 @@ CREATE TABLE temporal_partitioned_rng (
id int4range,
valid_at daterange,
name text,
- CONSTRAINT temporal_paritioned_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+ CONSTRAINT temporal_partitioned_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
) PARTITION BY LIST (id);
CREATE TABLE tp1 partition OF temporal_partitioned_rng FOR VALUES IN ('[1,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)');
CREATE TABLE tp2 partition OF temporal_partitioned_rng FOR VALUES IN ('[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)');
--
2.42.0
