On Wed, May 1, 2024 at 12:39 AM Paul Jungwirth
<p...@illuminatedcomputing.com> wrote:
>
> On 4/30/24 09:24, Robert Haas wrote:
> > Peter, could you have a look at
> > http://postgr.es/m/47550967-260b-4180-9791-b224859fe...@illuminatedcomputing.com
> > and express an opinion about whether each of those proposals are (a)
> > good or bad ideas and (b) whether they need to be fixed for the
> > current release?
>
> Here are the same patches but rebased. I've added a fourth which is my
> progress on adding the CHECK
> constraint. I don't really consider it finished though, because it has these
> problems:
>
> - The CHECK constraint should be marked as an internal dependency of the PK,
> so that you can't drop
> it, and it gets dropped when you drop the PK. I don't see a good way to tie
> the two together though,
> so I'd appreciate any advice there. They are separate AlterTableCmds, so how
> do I get the
> ObjectAddress of both constraints at the same time? I wanted to store the
> PK's ObjectAddress on the
> Constraint node, but since ObjectAddress isn't a Node it doesn't work.
>
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.
From 2028de0384b81bb9b2bff53fd391b08f57aba242 Mon Sep 17 00:00:00 2001
From: jian he <jian.universal...@gmail.com>
Date: Mon, 6 May 2024 10:12:26 +0800
Subject: [PATCH v4 1/1] add a special check constrint for PERIOD primary key
last column of PERIOD primary key cannot have empty value,
otherwise primary key may lost uniqueness property.
corner case demo:
create table t(a int4range, b int4range, primary key(a, b WITHOUT OVERLAPS));
insert into t values ('[1,2]','empty'), ('[1,2]','empty');
this patch makes it fails by internally add a check constraint:
CHECK (NOT isempty(period_column))
after that, the table `t` will look like:
Column | Type | Collation | Nullable | Default
--------+-----------+-----------+----------+---------
a | int4range | | not null |
b | int4range | | not null |
Indexes:
"t_pkey" PRIMARY KEY (a, b WITHOUT OVERLAPS)
Check constraints:
"b_not_empty" CHECK (NOT isempty(b))
to distinguish this constraint with other check constraint, we
make it conperiod as true in pg_constraint catalog.
we aslo add a internal dependency between the primary key constraint
and this check constraint.
so you cannot drop the check constraint itself,
if you drop the primary key constraint, this check constraint
will be dropped automatically.
generally we add check constraint within the function ATAddCheckNNConstraint,
primary key constraint within the function index_create.
in a chain of DDL command, we are not sure which one is be
first created, to make it safe, we do cross check at these two
functions, after both check constraint and primary key constraint
are created, then we add a internal dependencies on it.
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.
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.
N.B. what about UNIQUE constraint?
N.B. seems ok to not care about FOREIGN KEY regarding this corner case?
Discussion: https://postgr.es/m/3775839b-3f0f-4c8a-ac03-a253222e6...@illuminatedcomputing.com
---
doc/src/sgml/gist.sgml | 3 -
doc/src/sgml/ref/create_table.sgml | 5 +-
src/backend/catalog/heap.c | 10 +-
src/backend/catalog/index.c | 22 ++
src/backend/commands/tablecmds.c | 188 +++++++++++++
src/backend/parser/parse_utilcmd.c | 88 +++++-
src/include/commands/tablecmds.h | 4 +
.../regress/expected/without_overlaps.out | 251 +++++++++++++++++-
src/test/regress/sql/without_overlaps.sql | 120 +++++++++
9 files changed, 664 insertions(+), 27 deletions(-)
diff --git a/doc/src/sgml/gist.sgml b/doc/src/sgml/gist.sgml
index dcf9433f..638d912d 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 02f31d2d..8022e0e1 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/catalog/heap.c b/src/backend/catalog/heap.c
index 136cc42a..22e818e0 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -103,7 +103,7 @@ 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,
@@ -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, false);
numchecks++;
break;
@@ -2518,7 +2518,7 @@ 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++;
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 5a8568c5..2ec78777 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1224,6 +1224,28 @@ index_create(Relation heapRelation,
*/
CommandCounterIncrement();
+ if (isprimary)
+ {
+ ObjectAddress pk_period_address = InvalidObjectAddress;
+ ObjectAddress check_period_address = InvalidObjectAddress;
+ Oid check_conperiod_oid = InvalidOid;
+ Oid pk_conperiod_oid = InvalidOid;
+
+ if (validate_period_check_constr(heapRelation, &check_conperiod_oid, &pk_conperiod_oid))
+ {
+ Assert(OidIsValid(check_conperiod_oid));
+ Assert(OidIsValid(pk_conperiod_oid));
+ ObjectAddressSet(check_period_address, ConstraintRelationId, check_conperiod_oid);
+ ObjectAddressSet(pk_period_address, ConstraintRelationId, pk_conperiod_oid);
+ /*
+ * Register this special check constraint as internally dependent on the
+ * primary key constraint.
+ * Note that we also have direct () dependency from the
+ * special check constraint to the table.
+ */
+ recordDependencyOn(&check_period_address, &pk_period_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/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 3309332f..14bf0b53 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -9724,6 +9724,105 @@ ChooseForeignKeyConstraintNameAddition(List *colnames)
return pstrdup(buf);
}
+/*
+ * for PERIOD PRIMARY KEY, we need our last key column be a range data type
+ * also cannot be as empty range, since empty range itself not overlaps,
+ * so it can have a duplicated entry for the PERIOD primary key.
+ * To deal with it, we add a check constraint to enforce the last PERIOD column
+ * cannot have empty range value, we also make an internal dependency
+ * between primary key constraint and check constraint.
+ * With this dependency, the PERIOD primary key constraint drops,
+ * this special check constraint will drop automatically, but we cannot drop itself.
+ *
+ * we also mark this special check constraint conperiod as true.
+ * in a chain of command, we are not sure primary key constraint
+ * created first or check constraint, so we call this function within index_create
+ * and ATAddCheckNNConstraint.
+ *
+ * return true means both PERIOD PRIMARY KEY, and special check constraint are existed
+ * in our pg_constraint catalog.
+*/
+bool
+validate_period_check_constr(Relation heaprel, Oid *check_conperiod_oid, Oid *pk_conperiod_oid)
+{
+ Oid pk_constr_oid = InvalidOid;
+ Oid check_constr_oid = InvalidOid;
+ 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);
+ /*
+ * We're looking for both CHECK constraint and primary key constraint
+ * that are marked as validated and conperiod is true
+ */
+ 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_constr_oid = con->oid;
+ }
+ else
+ {
+ check_period_attnum = attnums[numkeys - 1];
+ check_constr_oid = con->oid;
+ }
+ }
+ systable_endscan(scan);
+ table_close(pg_constraint, AccessShareLock);
+
+ if (check_period_attnum != -1 && pk_period_attnum != -1)
+ {
+ if (check_period_attnum != pk_period_attnum)
+ elog(ERROR, "PERIOD check constraint associated attribute number should be same as PERIOD column's");
+
+ *check_conperiod_oid = check_constr_oid;
+ *pk_conperiod_oid = pk_constr_oid;
+ 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,
@@ -9816,6 +9915,95 @@ ATAddCheckNNConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* Advance command counter in case same table is visited multiple times */
CommandCounterIncrement();
+ /*
+ * deal with ALTER TYPE or other command where is_readd is true.
+ * when is_readd (eg. ALTER TYPE), PERIOD primary key will be reconstructed, it also
+ * instruct to append the add notempty check subcommand, see transformIndexConstraint.
+ * change the PERIOD column also readd the check constraint, obviously the readded one
+ * conperiod attribute will set to false.
+ * these two check constraint will be merged into one, see MergeWithExistingConstraint.
+ * MergeWithExistingConstraint don't deal with conperiod is true CHECK constraint, so
+ * we need reset it true manually
+ */
+ if (constr->without_overlaps && is_readd && constr->contype == CONSTR_CHECK)
+ {
+ Relation pg_constraint;
+ HeapTuple conTup;
+ SysScanDesc scan;
+ ScanKeyData skey[3];
+ HeapTuple copyTuple;
+ Form_pg_constraint copy_con;
+
+ pg_constraint = table_open(ConstraintRelationId, RowExclusiveLock);
+ /*
+ * Find and check the target constraint
+ */
+ ScanKeyInit(&skey[0],
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+ ScanKeyInit(&skey[1],
+ Anum_pg_constraint_contypid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(InvalidOid));
+ ScanKeyInit(&skey[2],
+ Anum_pg_constraint_conname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(constr->conname));
+
+ scan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId,
+ true, NULL, 1, skey);
+
+ while (HeapTupleIsValid(conTup = systable_getnext(scan)))
+ {
+ Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(conTup);
+
+ if (con->contype != CONSTRAINT_CHECK)
+ continue;
+
+ if (!con->convalidated)
+ continue;
+ if (con->conperiod)
+ break;
+ copyTuple = heap_copytuple(conTup);
+ copy_con = (Form_pg_constraint) GETSTRUCT(copyTuple);
+ copy_con->conperiod = true;
+
+ /* Reset conperiod */
+ CatalogTupleUpdate(pg_constraint, ©Tuple->t_self, copyTuple);
+ heap_freetuple(copyTuple);
+
+ CommandCounterIncrement();
+ }
+ systable_endscan(scan);
+ table_close(pg_constraint, RowExclusiveLock);
+ }
+
+ /* validate and record the internal dependency between PERIOD primary key and CHECK */
+ if (constr->without_overlaps)
+ {
+ ObjectAddress pk_period_address = InvalidObjectAddress;
+ ObjectAddress check_period_address = InvalidObjectAddress;
+ Oid check_conperiod_oid = InvalidOid;
+ Oid pk_conperiod_oid = InvalidOid;
+
+ if (validate_period_check_constr(rel, &check_conperiod_oid, &pk_conperiod_oid))
+ {
+ Assert(OidIsValid(check_conperiod_oid));
+ Assert(OidIsValid(pk_conperiod_oid));
+
+ ObjectAddressSet(pk_period_address,ConstraintRelationId, pk_conperiod_oid);
+ ObjectAddressSet(check_period_address,ConstraintRelationId, check_conperiod_oid);
+ /*
+ * Register this special check constraint as internally dependent on the
+ * primary key constraint.
+ * Note that we also have direct dependency from the
+ * special check constraint to the table.
+ */
+ recordDependencyOn(&check_period_address, &pk_period_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
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 9fb6ff86..ff996072 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -36,6 +36,7 @@
#include "catalog/pg_am.h"
#include "catalog/pg_collation.h"
#include "catalog/pg_constraint.h"
+#include "catalog/pg_namespace.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
#include "catalog/pg_statistic_ext.h"
@@ -2301,6 +2302,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)
@@ -2683,6 +2686,47 @@ 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))
+ {
+ Oid typid = InvalidOid;
+
+ 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++)
+ {
+ const char *attname;
+ Form_pg_attribute attr = TupleDescAttr(rel->rd_att, i);
+
+ if (attr->attisdropped)
+ continue;
+
+ attname = NameStr(attr->attname);
+ if (strcmp(attname, key) == 0)
+ {
+ typid = attr->atttypid;
+ break;
+ }
+ }
+ }
+ else
+ typid = typenameTypeId(NULL, column->typeName);
+
+ if (!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 +2763,50 @@ 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.
+ */
+ char *key = strVal(llast(constraint->keys));
+ AlterTableCmd *notemptycmd = makeNode(AlterTableCmd);
+ Constraint *checkcon = makeNode(Constraint);
+ ColumnRef *col;
+ FuncCall *func;
+ Node *expr;
+ char *conname;
+
+ 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);
+ conname = psprintf("%s_not_empty", key);
+
+ if (! checkcon->conname)
+ checkcon->conname = conname;
+ checkcon->contype = CONSTR_CHECK;
+ 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->without_overlaps = true;
+ checkcon->location = -1;
+
+ notemptycmd->subtype = AT_AddConstraint;
+ notemptycmd->def = (Node *) checkcon;
+ notemptycmd->name = psprintf("%s_not_empty", key);
+
+ notnullcmds = lcons(notemptycmd, notnullcmds);
+ }
}
-
}
/*
diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h
index 85cbad3d..081d2fac 100644
--- a/src/include/commands/tablecmds.h
+++ b/src/include/commands/tablecmds.h
@@ -27,6 +27,10 @@ struct AlterTableUtilityContext; /* avoid including tcop/utility.h here */
extern ObjectAddress DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
ObjectAddress *typaddress, const char *queryString);
+extern bool validate_period_check_constr(Relation heaprel,
+ Oid *check_conperiod_oid,
+ Oid *pk_conperiod_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 f6fe8f09..0f8b9db4 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
@@ -76,6 +79,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 +118,215 @@ 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))
+--for PERIOD primary key and check constraint dependency.
+create or replace function validate_period_empty_dependency(regclass)
+returns table
+(
+dependent_conname name,dependent_con_type "char",
+both_con_are_period bool,referenced_conname name,
+referenced_con_type "char",deptype "char"
+)
+as $$
+begin
+ return query
+ select
+ pc.conname as dependent_conname,
+ pc.contype as dependent_con_type,
+ pc.conperiod = pc1.conperiod as both_con_are_period,
+ pc1.conname as referenced_conname,
+ pc1.contype as referenced_con_type,
+ pd.deptype
+ from pg_depend pd join pg_constraint pc on pc.oid = pd.objid
+ join pg_constraint pc1 on pc1.oid = pd.refobjid
+ where pd.refclassid = pd.classid
+ and pd.classid = (select oid
+ from pg_class
+ where relname = 'pg_constraint'
+ and relnamespace = 'pg_catalog'::regnamespace)
+ and pc.conrelid = pc1.conrelid
+ and pc.conrelid = $1
+ ;
+end; $$ language plpgsql;
+--pk with not_empty check constraint
+CREATE TABLE temporal_t1 (id int4range, valid_at daterange);
+ALTER TABLE temporal_t1
+ ADD CONSTRAINT temporal_t1_pk
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+select * from validate_period_empty_dependency('temporal_t1'::regclass);
+ dependent_conname | dependent_con_type | both_con_are_period | referenced_conname | referenced_con_type | deptype
+--------------------+--------------------+---------------------+--------------------+---------------------+---------
+ valid_at_not_empty | c | t | temporal_t1_pk | p | i
+(1 row)
+
+select pg_get_constraintdef(oid) from pg_constraint
+where conrelid = 'temporal_t1'::regclass
+and contype = 'c'
+and conperiod;
+ pg_get_constraintdef
+---------------------------------
+ CHECK ((NOT isempty(valid_at)))
+(1 row)
+
+--should fail, primary key depend on it
+alter table temporal_t1 drop constraint valid_at_not_empty;
+ERROR: cannot drop constraint valid_at_not_empty on table temporal_t1 because constraint temporal_t1_pk on table temporal_t1 requires it
+HINT: You can drop constraint temporal_t1_pk on table temporal_t1 instead.
+--ok, also valid_at_not_empty chek constraint will also be dropped.
+alter table temporal_t1 drop constraint temporal_t1_pk;
+--expect zero row
+select * from validate_period_empty_dependency('temporal_t1'::regclass);
+ dependent_conname | dependent_con_type | both_con_are_period | referenced_conname | referenced_con_type | deptype
+-------------------+--------------------+---------------------+--------------------+---------------------+---------
+(0 rows)
+
+DROP TABLE temporal_t1;
+CREATE TABLE temporal_t2 (id int4range, valid_at daterange);
+alter table temporal_t2 add constraint valid_at_not_empty CHECK (NOT isempty(valid_at));
+-- fail for now
+ALTER TABLE temporal_t2
+ ADD CONSTRAINT temporal_t2_pk
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+ERROR: constraint "valid_at_not_empty" for relation "temporal_t2" already exists
+DROP TABLE temporal_t2;
+CREATE TABLE temporal_t3 (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_t3_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS));
+--should fail.
+alter table temporal_t3 drop constraint valid_at_not_empty;
+ERROR: cannot drop constraint valid_at_not_empty on table temporal_t3 because constraint temporal_t3_rng_pk on table temporal_t3 requires it
+HINT: You can drop constraint temporal_t3_rng_pk on table temporal_t3 instead.
+begin;
+ALTER TABLE temporal_t3
+ 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
+--should be fine with data type change.
+select * from validate_period_empty_dependency('temporal_t3'::regclass);
+ dependent_conname | dependent_con_type | both_con_are_period | referenced_conname | referenced_con_type | deptype
+--------------------+--------------------+---------------------+--------------------+---------------------+---------
+ valid_at_not_empty | c | t | temporal_t3_rng_pk | p | i
+(1 row)
+
+alter table temporal_t3 drop constraint temporal_t3_rng_pk;
+rollback;
+alter table temporal_t3 rename valid_at to valid_at1;
+select * from validate_period_empty_dependency('temporal_t3'::regclass);
+ dependent_conname | dependent_con_type | both_con_are_period | referenced_conname | referenced_con_type | deptype
+--------------------+--------------------+---------------------+--------------------+---------------------+---------
+ valid_at_not_empty | c | t | temporal_t3_rng_pk | p | i
+(1 row)
+
+select pg_get_constraintdef(oid) from pg_constraint
+where conrelid = 'temporal_t3'::regclass
+and contype = 'c'
+and conperiod;
+ pg_get_constraintdef
+----------------------------------
+ CHECK ((NOT isempty(valid_at1)))
+(1 row)
+
+alter table temporal_t3 drop constraint temporal_t3_rng_pk;
+select * from validate_period_empty_dependency('temporal_t3'::regclass);
+ dependent_conname | dependent_con_type | both_con_are_period | referenced_conname | referenced_con_type | deptype
+-------------------+--------------------+---------------------+--------------------+---------------------+---------
+(0 rows)
+
+DROP TABLE temporal_t3;
+-- Add range column and the PK at the same time
+CREATE TABLE temporal_t4 (id int4range);
+ALTER TABLE temporal_t4
+ ADD COLUMN valid_at daterange,
+ ADD CONSTRAINT temporal_t4_pk
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+\d temporal_t4
+ Table "public.temporal_t4"
+ Column | Type | Collation | Nullable | Default
+----------+-----------+-----------+----------+---------
+ id | int4range | | not null |
+ valid_at | daterange | | not null |
+Indexes:
+ "temporal_t4_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
+
+select * from validate_period_empty_dependency('temporal_t4'::regclass);
+ dependent_conname | dependent_con_type | both_con_are_period | referenced_conname | referenced_con_type | deptype
+--------------------+--------------------+---------------------+--------------------+---------------------+---------
+ valid_at_not_empty | c | t | temporal_t4_pk | p | i
+(1 row)
+
+DROP TABLE temporal_t4;
+-- temporal partition table with check constraint
+CREATE TABLE temporal_p (
+ id int4range,
+ valid_at daterange,
+ name text,
+ CONSTRAINT temporal_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+) PARTITION BY LIST (id);
+CREATE TABLE temporal_p1 PARTITION OF temporal_p FOR VALUES IN ('[1,2)', '[2,3)');
+CREATE TABLE temporal_p2 PARTITION OF temporal_p FOR VALUES IN ('[3,4)', '[4,5)');
+\d+ temporal_p
+ Partitioned table "public.temporal_p"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+----------+-----------+-----------+----------+---------+----------+--------------+-------------
+ id | int4range | | not null | | extended | |
+ valid_at | daterange | | not null | | extended | |
+ name | text | | | | extended | |
+Partition key: LIST (id)
+Indexes:
+ "temporal_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
+Partitions: temporal_p1 FOR VALUES IN ('[1,2)', '[2,3)'),
+ temporal_p2 FOR VALUES IN ('[3,4)', '[4,5)')
+
+\d temporal_p1
+ Table "public.temporal_p1"
+ Column | Type | Collation | Nullable | Default
+----------+-----------+-----------+----------+---------
+ id | int4range | | not null |
+ valid_at | daterange | | not null |
+ name | text | | |
+Partition of: temporal_p FOR VALUES IN ('[1,2)', '[2,3)')
+Indexes:
+ "temporal_p1_pkey" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
+
+alter table temporal_p1 drop constraint valid_at_not_empty;
+ERROR: cannot drop inherited constraint "valid_at_not_empty" of relation "temporal_p1"
+\d temporal_p2
+ Table "public.temporal_p2"
+ Column | Type | Collation | Nullable | Default
+----------+-----------+-----------+----------+---------
+ id | int4range | | not null |
+ valid_at | daterange | | not null |
+ name | text | | |
+Partition of: temporal_p FOR VALUES IN ('[3,4)', '[4,5)')
+Indexes:
+ "temporal_p2_pkey" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Check constraints:
+ "valid_at_not_empty" CHECK (NOT isempty(valid_at))
+
+alter table temporal_p2 drop constraint valid_at_not_empty;
+ERROR: cannot drop inherited constraint "valid_at_not_empty" of relation "temporal_p2"
+alter table temporal_p drop constraint temporal_pk;
+\d+ temporal_p
+ Partitioned table "public.temporal_p"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+----------+-----------+-----------+----------+---------+----------+--------------+-------------
+ id | int4range | | | | extended | |
+ valid_at | daterange | | | | extended | |
+ name | text | | | | extended | |
+Partition key: LIST (id)
+Partitions: temporal_p1 FOR VALUES IN ('[1,2)', '[2,3)'),
+ temporal_p2 FOR VALUES IN ('[3,4)', '[4,5)')
+
+DROP TABLE temporal_p;
+DROP FUNCTION validate_period_empty_dependency;
-- 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 +344,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
@@ -164,8 +379,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,
@@ -372,6 +588,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;
@@ -626,6 +843,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)
@@ -665,6 +884,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)
@@ -672,6 +893,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)
@@ -680,6 +902,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
@@ -1096,6 +1319,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)
@@ -1135,6 +1360,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)
@@ -1364,15 +1591,10 @@ CREATE TABLE temporal_box (
valid_at box,
CONSTRAINT temporal_box_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
);
+ERROR: column "valid_at" in WITHOUT OVERLAPS is not a range or multirange type
+LINE 4: CONSTRAINT temporal_box_pk PRIMARY KEY (id, valid_at WITHO...
+ ^
\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,
@@ -1381,8 +1603,9 @@ CREATE TABLE temporal_fk_box2box (
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.
+ERROR: column "valid_at" in WITHOUT OVERLAPS is not a range or multirange type
+LINE 5: CONSTRAINT temporal_fk_box2box_pk PRIMARY KEY (id, valid_a...
+ ^
--
-- FK between partitioned tables
--
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index da2b7f19..05cb5a14 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -77,6 +77,126 @@ CREATE TABLE temporal_mltrng (
);
\d temporal_mltrng
+--for PERIOD primary key and check constraint dependency.
+create or replace function validate_period_empty_dependency(regclass)
+returns table
+(
+dependent_conname name,dependent_con_type "char",
+both_con_are_period bool,referenced_conname name,
+referenced_con_type "char",deptype "char"
+)
+as $$
+begin
+ return query
+ select
+ pc.conname as dependent_conname,
+ pc.contype as dependent_con_type,
+ pc.conperiod = pc1.conperiod as both_con_are_period,
+ pc1.conname as referenced_conname,
+ pc1.contype as referenced_con_type,
+ pd.deptype
+ from pg_depend pd join pg_constraint pc on pc.oid = pd.objid
+ join pg_constraint pc1 on pc1.oid = pd.refobjid
+ where pd.refclassid = pd.classid
+ and pd.classid = (select oid
+ from pg_class
+ where relname = 'pg_constraint'
+ and relnamespace = 'pg_catalog'::regnamespace)
+ and pc.conrelid = pc1.conrelid
+ and pc.conrelid = $1
+ ;
+end; $$ language plpgsql;
+
+--pk with not_empty check constraint
+CREATE TABLE temporal_t1 (id int4range, valid_at daterange);
+ALTER TABLE temporal_t1
+ ADD CONSTRAINT temporal_t1_pk
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+select * from validate_period_empty_dependency('temporal_t1'::regclass);
+select pg_get_constraintdef(oid) from pg_constraint
+where conrelid = 'temporal_t1'::regclass
+and contype = 'c'
+and conperiod;
+
+--should fail, primary key depend on it
+alter table temporal_t1 drop constraint valid_at_not_empty;
+
+--ok, also valid_at_not_empty chek constraint will also be dropped.
+alter table temporal_t1 drop constraint temporal_t1_pk;
+
+--expect zero row
+select * from validate_period_empty_dependency('temporal_t1'::regclass);
+DROP TABLE temporal_t1;
+
+
+CREATE TABLE temporal_t2 (id int4range, valid_at daterange);
+alter table temporal_t2 add constraint valid_at_not_empty CHECK (NOT isempty(valid_at));
+-- fail for now
+ALTER TABLE temporal_t2
+ ADD CONSTRAINT temporal_t2_pk
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+DROP TABLE temporal_t2;
+
+CREATE TABLE temporal_t3 (
+ id int4range,
+ valid_at daterange,
+ CONSTRAINT temporal_t3_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS));
+--should fail.
+alter table temporal_t3 drop constraint valid_at_not_empty;
+
+begin;
+ALTER TABLE temporal_t3
+ ALTER COLUMN valid_at TYPE daterange USING daterange(lower(valid_at)::date, upper(valid_at)::date);
+--should be fine with data type change.
+select * from validate_period_empty_dependency('temporal_t3'::regclass);
+alter table temporal_t3 drop constraint temporal_t3_rng_pk;
+rollback;
+
+alter table temporal_t3 rename valid_at to valid_at1;
+select * from validate_period_empty_dependency('temporal_t3'::regclass);
+
+select pg_get_constraintdef(oid) from pg_constraint
+where conrelid = 'temporal_t3'::regclass
+and contype = 'c'
+and conperiod;
+
+alter table temporal_t3 drop constraint temporal_t3_rng_pk;
+select * from validate_period_empty_dependency('temporal_t3'::regclass);
+
+DROP TABLE temporal_t3;
+
+
+-- Add range column and the PK at the same time
+CREATE TABLE temporal_t4 (id int4range);
+ALTER TABLE temporal_t4
+ ADD COLUMN valid_at daterange,
+ ADD CONSTRAINT temporal_t4_pk
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+\d temporal_t4
+
+select * from validate_period_empty_dependency('temporal_t4'::regclass);
+DROP TABLE temporal_t4;
+
+
+-- temporal partition table with check constraint
+CREATE TABLE temporal_p (
+ id int4range,
+ valid_at daterange,
+ name text,
+ CONSTRAINT temporal_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+) PARTITION BY LIST (id);
+CREATE TABLE temporal_p1 PARTITION OF temporal_p FOR VALUES IN ('[1,2)', '[2,3)');
+CREATE TABLE temporal_p2 PARTITION OF temporal_p FOR VALUES IN ('[3,4)', '[4,5)');
+\d+ temporal_p
+\d temporal_p1
+alter table temporal_p1 drop constraint valid_at_not_empty;
+\d temporal_p2
+alter table temporal_p2 drop constraint valid_at_not_empty;
+alter table temporal_p drop constraint temporal_pk;
+\d+ temporal_p
+DROP TABLE temporal_p;
+
+DROP FUNCTION validate_period_empty_dependency;
-- 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 (
--
2.34.1
