hi.
While working on domain IS JSON, I found out that
WITHOUT OVERLAPS does not support for domain too.
but it does support user-defined range types (via CREATE TYPE).
after looking around:
check_exclusion_or_unique_constraint->ExecWithoutOverlapsNotEmpty
ExecWithoutOverlapsNotEmpty typtype should be domain's basetype's typtype
otherwise it will fallback to:
elog(ERROR, "WITHOUT OVERLAPS column \"%s\" is not a range
or multirange",
NameStr(attname));
That means we need to cheaply get the domain basetype's
pg_type.typtype in lookup_type_cache.
so I added a new char field: TypeCacheEntry.domainBaseTyptype.
it should be working:
For primary key, unique constraint: pg_index.indclass is
the same for domain and basetype.
no need to worry too much about pg_index.indcollation because range type is not
collateable.
For foreign key: pg_constraint (conpfeqop, conppeqop, conffeqop) are the same
for domain and basetype.
--
jian
https://www.enterprisedb.com/
From 37852b4a14ae29e845e748be9a0e0499990e4d03 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Wed, 3 Dec 2025 15:31:03 +0800
Subject: [PATCH v1 1/1] domain for WITHOUT OVERLAPS
Domains are now supported for WITHOUT OVERLAPS primary key, unique, and foreign
key constraints.
discussion: https://postgr.es/m/
---
src/backend/executor/execIndexing.c | 11 +++-
src/backend/parser/parse_utilcmd.c | 2 +
src/backend/utils/cache/typcache.c | 2 +
src/include/utils/typcache.h | 5 +-
.../regress/expected/without_overlaps.out | 63 ++++++++++++++++++-
src/test/regress/sql/without_overlaps.sql | 47 +++++++++++++-
6 files changed, 122 insertions(+), 8 deletions(-)
diff --git a/src/backend/executor/execIndexing.c b/src/backend/executor/execIndexing.c
index dd323c9b9fd..fcf30c6cd2f 100644
--- a/src/backend/executor/execIndexing.c
+++ b/src/backend/executor/execIndexing.c
@@ -752,13 +752,20 @@ check_exclusion_or_unique_constraint(Relation heap, Relation index,
if (!isnull[indnkeyatts - 1])
{
+ char typtype;
TupleDesc tupdesc = RelationGetDescr(heap);
Form_pg_attribute att = TupleDescAttr(tupdesc, attno - 1);
- TypeCacheEntry *typcache = lookup_type_cache(att->atttypid, 0);
+ TypeCacheEntry *typcache = lookup_type_cache(att->atttypid, TYPECACHE_DOMAIN_BASE_INFO);
+
+ if (OidIsValid(typcache->domainBaseType))
+ typtype = typcache->domainBaseTyptype;
+ else
+ typtype = typcache->typtype;
+
ExecWithoutOverlapsNotEmpty(heap, att->attname,
values[indnkeyatts - 1],
- typcache->typtype, att->atttypid);
+ typtype, att->atttypid);
}
}
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index e96b38a59d5..67ec1aaad03 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -2792,6 +2792,8 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
if (!OidIsValid(typid) && column)
typid = typenameTypeId(NULL, column->typeName);
+ typid = getBaseType(typid);
+
if (!OidIsValid(typid) || !(type_is_range(typid) || type_is_multirange(typid)))
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
diff --git a/src/backend/utils/cache/typcache.c b/src/backend/utils/cache/typcache.c
index 6a347698edf..e27ca1466f2 100644
--- a/src/backend/utils/cache/typcache.c
+++ b/src/backend/utils/cache/typcache.c
@@ -944,6 +944,8 @@ lookup_type_cache(Oid type_id, int flags)
typentry->domainBaseTypmod = -1;
typentry->domainBaseType =
getBaseTypeAndTypmod(type_id, &typentry->domainBaseTypmod);
+ typentry->domainBaseTyptype =
+ get_typtype(typentry->domainBaseType);
}
if ((flags & TYPECACHE_DOMAIN_CONSTR_INFO) &&
(typentry->flags & TCFLAGS_CHECKED_DOMAIN_CONSTRAINTS) == 0 &&
diff --git a/src/include/utils/typcache.h b/src/include/utils/typcache.h
index 1cb30f1818c..2ad7528c195 100644
--- a/src/include/utils/typcache.h
+++ b/src/include/utils/typcache.h
@@ -109,11 +109,12 @@ typedef struct TypeCacheEntry
struct TypeCacheEntry *rngtype; /* multirange's range underlying type */
/*
- * Domain's base type and typmod if it's a domain type. Zeroes if not
- * domain, or if information hasn't been requested.
+ * Domain's base type, typmod, and typtype if it's a domain type.
+ * Zeroes if not domain, or if information hasn't been requested.
*/
Oid domainBaseType;
int32 domainBaseTypmod;
+ char domainBaseTyptype;
/*
* Domain constraint data if it's a domain type. NULL if not domain, or
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index f3144bdc39c..e3e5b8a4e8d 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -162,7 +162,48 @@ CREATE TABLE temporal_rng3 (
);
ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_pk;
DROP TABLE temporal_rng3;
-DROP TYPE textrange2;
+CREATE DOMAIN d_textrange1 AS int4range CHECK (VALUE <> '[10,10]');
+CREATE DOMAIN d_textrange2 AS textrange2 CHECK (VALUE <> '[c,c]');
+CREATE DOMAIN d_textrange2c AS d_textrange2;
+CREATE TABLE temporal_rng4 (
+ id d_textrange1,
+ valid_at d_textrange2c,
+ CONSTRAINT temporal_rng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_rng4 VALUES ('[1,1]', '[c,c]'); --domain constraint violation
+ERROR: value for domain d_textrange2c violates check constraint "d_textrange2_check"
+INSERT INTO temporal_rng4 VALUES ('[1,1]', '[a,f]');
+INSERT INTO temporal_rng4 VALUES ('[1,1]', '[b,b]'); --error
+ERROR: conflicting key value violates exclusion constraint "temporal_rng4_pk"
+DETAIL: Key (id, valid_at)=([1,2), [b,b]) conflicts with existing key (id, valid_at)=([1,2), [a,f]).
+INSERT INTO temporal_rng4 VALUES ('[2,2]', '[B,C)'), ('[2,2]', '[A,C)'); --error
+ERROR: conflicting key value violates exclusion constraint "temporal_rng4_pk"
+DETAIL: Key (id, valid_at)=([2,3), [A,C)) conflicts with existing key (id, valid_at)=([2,3), [B,C)).
+CREATE TABLE temporal_rngfk (parent_id d_textrange1, id int4range, valid_at d_textrange2);
+ALTER TABLE temporal_rngfk
+ ADD CONSTRAINT temporal_rngfk_fk
+ FOREIGN KEY (parent_id, PERIOD valid_at)
+ REFERENCES temporal_rng4;
+INSERT INTO temporal_rngfk VALUES ('[1,1]', '[2,2]', '[d,d]');
+TABLE temporal_rng4;
+ id | valid_at
+-------+----------
+ [1,2) | [a,f]
+(1 row)
+
+TABLE temporal_rngfk;
+ parent_id | id | valid_at
+-----------+-------+----------
+ [1,2) | [2,3) | [d,d]
+(1 row)
+
+UPDATE temporal_rng4 SET valid_at = '[c,c]'; --error
+ERROR: value for domain d_textrange2c violates check constraint "d_textrange2_check"
+UPDATE temporal_rng4 SET valid_at = '[a,g]';
+UPDATE temporal_rng4 SET valid_at = '[f,f]'; --error
+ERROR: update or delete on table "temporal_rng4" violates foreign key constraint "temporal_rngfk_fk" on table "temporal_rngfk"
+DETAIL: Key (id, valid_at)=([1,2), [a,g]) is still referenced from table "temporal_rngfk".
+DROP TABLE temporal_rng4, temporal_rngfk;
-- PK with one column plus a multirange:
CREATE TABLE temporal_mltrng (
id int4range,
@@ -301,7 +342,6 @@ SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'te
DROP TABLE temporal_rng3;
-- UNIQUE with a custom range type:
-CREATE TYPE textrange2 AS range (subtype=text, collation="C");
CREATE TABLE temporal_rng3 (
id int4range,
valid_at textrange2,
@@ -309,6 +349,25 @@ CREATE TABLE temporal_rng3 (
);
ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_uq;
DROP TABLE temporal_rng3;
+CREATE TABLE temporal_rng4 (
+ id d_textrange1,
+ valid_at d_textrange2c,
+ CONSTRAINT temporal_rng4_pk UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_rng4 VALUES ('[1,1]', NULL), (NULL, '[1,1]');
+INSERT INTO temporal_rng4 VALUES ('[1,1]', '[c,c]'); --domain constraint violation
+ERROR: value for domain d_textrange2c violates check constraint "d_textrange2_check"
+INSERT INTO temporal_rng4 VALUES ('[1,1]', '[a,c]');
+INSERT INTO temporal_rng4 VALUES ('[1,1]', '[b,b]'); --error
+ERROR: conflicting key value violates exclusion constraint "temporal_rng4_pk"
+DETAIL: Key (id, valid_at)=([1,2), [b,b]) conflicts with existing key (id, valid_at)=([1,2), [a,c]).
+INSERT INTO temporal_rng4 VALUES ('[10,10]', NULL); --error
+ERROR: value for domain d_textrange1 violates check constraint "d_textrange1_check"
+INSERT INTO temporal_rng4 VALUES ('[2,2]', '[B,C)'), ('[2,2]', '[A,C)'); --error
+ERROR: conflicting key value violates exclusion constraint "temporal_rng4_pk"
+DETAIL: Key (id, valid_at)=([2,3), [A,C)) conflicts with existing key (id, valid_at)=([2,3), [B,C)).
+DROP TABLE temporal_rng4;
+DROP DOMAIN d_textrange1, d_textrange2c, d_textrange2;
DROP TYPE textrange2;
--
-- test ALTER TABLE ADD CONSTRAINT
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index 4aaca242bbe..a984dd0f372 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -100,7 +100,36 @@ CREATE TABLE temporal_rng3 (
);
ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_pk;
DROP TABLE temporal_rng3;
-DROP TYPE textrange2;
+
+CREATE DOMAIN d_textrange1 AS int4range CHECK (VALUE <> '[10,10]');
+CREATE DOMAIN d_textrange2 AS textrange2 CHECK (VALUE <> '[c,c]');
+CREATE DOMAIN d_textrange2c AS d_textrange2;
+CREATE TABLE temporal_rng4 (
+ id d_textrange1,
+ valid_at d_textrange2c,
+ CONSTRAINT temporal_rng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+
+INSERT INTO temporal_rng4 VALUES ('[1,1]', '[c,c]'); --domain constraint violation
+INSERT INTO temporal_rng4 VALUES ('[1,1]', '[a,f]');
+INSERT INTO temporal_rng4 VALUES ('[1,1]', '[b,b]'); --error
+INSERT INTO temporal_rng4 VALUES ('[2,2]', '[B,C)'), ('[2,2]', '[A,C)'); --error
+
+CREATE TABLE temporal_rngfk (parent_id d_textrange1, id int4range, valid_at d_textrange2);
+ALTER TABLE temporal_rngfk
+ ADD CONSTRAINT temporal_rngfk_fk
+ FOREIGN KEY (parent_id, PERIOD valid_at)
+ REFERENCES temporal_rng4;
+
+INSERT INTO temporal_rngfk VALUES ('[1,1]', '[2,2]', '[d,d]');
+TABLE temporal_rng4;
+TABLE temporal_rngfk;
+
+UPDATE temporal_rng4 SET valid_at = '[c,c]'; --error
+UPDATE temporal_rng4 SET valid_at = '[a,g]';
+UPDATE temporal_rng4 SET valid_at = '[f,f]'; --error
+
+DROP TABLE temporal_rng4, temporal_rngfk;
-- PK with one column plus a multirange:
CREATE TABLE temporal_mltrng (
@@ -170,7 +199,6 @@ SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'te
DROP TABLE temporal_rng3;
-- UNIQUE with a custom range type:
-CREATE TYPE textrange2 AS range (subtype=text, collation="C");
CREATE TABLE temporal_rng3 (
id int4range,
valid_at textrange2,
@@ -178,6 +206,21 @@ CREATE TABLE temporal_rng3 (
);
ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_uq;
DROP TABLE temporal_rng3;
+
+CREATE TABLE temporal_rng4 (
+ id d_textrange1,
+ valid_at d_textrange2c,
+ CONSTRAINT temporal_rng4_pk UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_rng4 VALUES ('[1,1]', NULL), (NULL, '[1,1]');
+INSERT INTO temporal_rng4 VALUES ('[1,1]', '[c,c]'); --domain constraint violation
+INSERT INTO temporal_rng4 VALUES ('[1,1]', '[a,c]');
+INSERT INTO temporal_rng4 VALUES ('[1,1]', '[b,b]'); --error
+INSERT INTO temporal_rng4 VALUES ('[10,10]', NULL); --error
+INSERT INTO temporal_rng4 VALUES ('[2,2]', '[B,C)'), ('[2,2]', '[A,C)'); --error
+
+DROP TABLE temporal_rng4;
+DROP DOMAIN d_textrange1, d_textrange2c, d_textrange2;
DROP TYPE textrange2;
--
--
2.34.1
