On Thu, Dec 4, 2025 at 2:39 AM Paul A Jungwirth <[email protected]> wrote: > > I think there are these cases to consider: > > - WITHOUT OVERLAPS on a rangetype whose subtype has a domain > - WITHOUT OVERLAPS on a rangetype with a domain on itself > - WITHOUT OVERLAPS on a multirangetype whose subtype has a domain > - WITHOUT OVERLAPS on a multirangetype whose rangetype has a domain > - WITHOUT OVERLAPS on a multirangetype with a domain on itself > hi.
please check the attached polished test. (no C code change). I think the test I am missing is > - WITHOUT OVERLAPS on a multirangetype whose rangetype has a domain because it expects CREATE DOMAIN also to create a multirange type for rangetype. but currently we didn't do it. -- jian https://www.enterprisedb.com
From eda1820f0aa597d5c28c89e870aa1384556f6224 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Thu, 11 Dec 2025 12:20:38 +0800 Subject: [PATCH v2 1/1] domain for WITHOUT OVERLAPS polished tests for > - WITHOUT OVERLAPS on a rangetype whose subtype has a domain > - WITHOUT OVERLAPS on a rangetype with a domain on itself > - WITHOUT OVERLAPS on a multirangetype whose subtype has a domain > - WITHOUT OVERLAPS on a multirangetype whose rangetype has a domain > - WITHOUT OVERLAPS on a multirangetype with a domain on itself no tests for > - WITHOUT OVERLAPS on a multirangetype whose rangetype has a domain now. because the expected behavior assumes that CREATE DOMAIN also generates a corresponding multirange type for the domain range type, which is not true. --example, we expect create domain also create a multirange type for xxint4 CREATE DOMAIN xxint4 AS int4range CHECK (VALUE <> '[10,11)'); SELECT typname FROM pg_type WHERE typname ~* 'xxint4'; commitfest: https://commitfest.postgresql.org/patch/6281 discussion: https://postgr.es/m/CACJufxGoAmN_0iJ=hjtg0vgposoyy-vyyfe+-q0awxrq2_p...@mail.gmail.com --- src/backend/executor/execIndexing.c | 10 +- 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 | 135 +++++++++++++++++- src/test/regress/sql/without_overlaps.sql | 104 +++++++++++++- 6 files changed, 250 insertions(+), 8 deletions(-) diff --git a/src/backend/executor/execIndexing.c b/src/backend/executor/execIndexing.c index dd323c9b9fd..6a46ef21113 100644 --- a/src/backend/executor/execIndexing.c +++ b/src/backend/executor/execIndexing.c @@ -752,13 +752,19 @@ 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..1ee88c4230f 100644 --- a/src/test/regress/expected/without_overlaps.out +++ b/src/test/regress/expected/without_overlaps.out @@ -162,7 +162,118 @@ CREATE TABLE temporal_rng3 ( ); ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_pk; DROP TABLE temporal_rng3; -DROP TYPE textrange2; +-- +-- tests for range over domain, multirange over a domain, custom range type over +-- domain. +-- +CREATE DOMAIN int4_d as integer check (value <> 10); +CREATE TYPE int4_d_range as range (subtype = int4_d); +CREATE DOMAIN int4multirange_d as int4multirange check (value <> '{[10,11)}'); +CREATE DOMAIN d_int4range1 AS int4range CHECK (VALUE <> '[10,11)'); +CREATE DOMAIN d_textrange2 AS textrange2 CHECK (VALUE <> '[c,d)'); +CREATE DOMAIN d_textrange2c AS d_textrange2; +CREATE DOMAIN textmultirange2_d AS textmultirange2 CHECK (VALUE <> '{[c,d)}'); +CREATE TABLE temporal_rng4mrng ( + id d_int4range1, + valid_at int4multirange_d, + CONSTRAINT temporal_rng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO temporal_rng4mrng VALUES ('[1,2)', '{[10,11)}'); --error +ERROR: value for domain int4multirange_d violates check constraint "int4multirange_d_check" +INSERT INTO temporal_rng4mrng VALUES ('[1,2)', '{[10,13)}'), ('[1,2)', '{[2,13)}'); --error +ERROR: conflicting key value violates exclusion constraint "temporal_rng4_pk" +DETAIL: Key (id, valid_at)=([1,2), {[2,13)}) conflicts with existing key (id, valid_at)=([1,2), {[10,13)}). +DROP TABLE temporal_rng4mrng; +CREATE TABLE temporal_rng4 ( + id d_int4range1, + id1 int4_d_range, + valid_at d_textrange2c, + CONSTRAINT temporal_rng4_pk PRIMARY KEY (id, id1, valid_at WITHOUT OVERLAPS) +); +INSERT INTO temporal_rng4 VALUES ('[1,2)', '[1,2)', '[c,d)'); --error +ERROR: value for domain d_textrange2c violates check constraint "d_textrange2_check" +INSERT INTO temporal_rng4 VALUES ('[1,2)', '[10,12)', '[a,g)'); --error +ERROR: value for domain int4_d violates check constraint "int4_d_check" +LINE 1: INSERT INTO temporal_rng4 VALUES ('[1,2)', '[10,12)', '[a,g)... + ^ +INSERT INTO temporal_rng4 + VALUES ('[2,3)', '[9,11)', '[B,C)'), ('[2,3)', '[9,11)', '[A,C)'); --error +ERROR: conflicting key value violates exclusion constraint "temporal_rng4_pk" +DETAIL: Key (id, id1, valid_at)=([2,3), [9,11), [A,C)) conflicts with existing key (id, id1, valid_at)=([2,3), [9,11), [B,C)). +INSERT INTO temporal_rng4 VALUES ('[1,2)', '[9,11)', '[a,g)'); +INSERT INTO temporal_rng4 VALUES ('[1,2)', '[9,11)', '[b,c)'); --error +ERROR: conflicting key value violates exclusion constraint "temporal_rng4_pk" +DETAIL: Key (id, id1, valid_at)=([1,2), [9,11), [b,c)) conflicts with existing key (id, id1, valid_at)=([1,2), [9,11), [a,g)). +CREATE TABLE temporal_rngfk ( + parent_id d_int4range1, + id int4range, + id1 int4_d_range, + valid_at d_textrange2); +ALTER TABLE temporal_rngfk + ADD CONSTRAINT temporal_rngfk_fk + FOREIGN KEY (parent_id, id1, PERIOD valid_at) + REFERENCES temporal_rng4; +INSERT INTO temporal_rngfk VALUES ('[1,2)', '[2,3)', '[9,11)', '[d,e)'); +TABLE temporal_rng4; + id | id1 | valid_at +-------+--------+---------- + [1,2) | [9,11) | [a,g) +(1 row) + +TABLE temporal_rngfk; + parent_id | id | id1 | valid_at +-----------+-------+--------+---------- + [1,2) | [2,3) | [9,11) | [d,e) +(1 row) + +UPDATE temporal_rng4 SET valid_at = '[c,d)'; --error +ERROR: value for domain d_textrange2c violates check constraint "d_textrange2_check" +UPDATE temporal_rng4 SET valid_at = '[a,h)'; +UPDATE temporal_rng4 SET valid_at = '[f,g)'; --error +ERROR: update or delete on table "temporal_rng4" violates foreign key constraint "temporal_rngfk_fk" on table "temporal_rngfk" +DETAIL: Key (id, id1, valid_at)=([1,2), [9,11), [a,h)) is still referenced from table "temporal_rngfk". +DROP TABLE temporal_rng4, temporal_rngfk; +-- domain on a multirange +CREATE TABLE temporal_mltrng4 ( + id d_int4range1, + valid_at textmultirange2_d, + CONSTRAINT temporal_rng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[c,d)}'); --error +ERROR: value for domain textmultirange2_d violates check constraint "textmultirange2_d_check" +INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[a,g)}'); +INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[b,c)}'); --error +ERROR: conflicting key value violates exclusion constraint "temporal_rng4_pk" +DETAIL: Key (id, valid_at)=([1,2), {[b,c)}) conflicts with existing key (id, valid_at)=([1,2), {[a,g)}). +INSERT INTO temporal_mltrng4 VALUES ('[2,3)', '{[B,C)}'), ('[2,3)', '{[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_mltrngfk (parent_id d_int4range1, id int4range, +valid_at textmultirange2_d); +ALTER TABLE temporal_mltrngfk + ADD CONSTRAINT temporal_mltrngfk_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng4; +INSERT INTO temporal_mltrngfk VALUES ('[1,2)', '[2,3)', '{[d,e)}'); +TABLE temporal_mltrng4; + id | valid_at +-------+---------- + [1,2) | {[a,g)} +(1 row) + +TABLE temporal_mltrngfk; + parent_id | id | valid_at +-----------+-------+---------- + [1,2) | [2,3) | {[d,e)} +(1 row) + +UPDATE temporal_mltrng4 SET valid_at = '{[c,d)}'; --error +ERROR: value for domain textmultirange2_d violates check constraint "textmultirange2_d_check" +UPDATE temporal_mltrng4 SET valid_at = '{[a,h)}'; +UPDATE temporal_mltrng4 SET valid_at = '{[f,g)}'; --error +ERROR: update or delete on table "temporal_mltrng4" violates foreign key constraint "temporal_mltrngfk_fk" on table "temporal_mltrngfk" +DETAIL: Key (id, valid_at)=([1,2), {[a,h)}) is still referenced from table "temporal_mltrngfk". +DROP TABLE temporal_mltrng4, temporal_mltrngfk; -- PK with one column plus a multirange: CREATE TABLE temporal_mltrng ( id int4range, @@ -301,7 +412,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 +419,27 @@ CREATE TABLE temporal_rng3 ( ); ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_uq; DROP TABLE temporal_rng3; +CREATE TABLE temporal_rng4 ( + id d_int4range1, + valid_at d_textrange2c, + CONSTRAINT temporal_rng4_pk UNIQUE (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO temporal_rng4 VALUES ('[1,2)', NULL), (NULL, '[1,2)'); +INSERT INTO temporal_rng4 VALUES ('[1,2)', '[c,d)'); --error +ERROR: value for domain d_textrange2c violates check constraint "d_textrange2_check" +INSERT INTO temporal_rng4 VALUES ('[1,2)', '[a,d)'); +INSERT INTO temporal_rng4 VALUES ('[1,2)', '[b,c)'); --error +ERROR: conflicting key value violates exclusion constraint "temporal_rng4_pk" +DETAIL: Key (id, valid_at)=([1,2), [b,c)) conflicts with existing key (id, valid_at)=([1,2), [a,d)). +INSERT INTO temporal_rng4 VALUES ('[10,11)', NULL); --error +ERROR: value for domain d_int4range1 violates check constraint "d_int4range1_check" +INSERT INTO temporal_rng4 VALUES ('[2,3)', '[B,C)'), ('[2,3)', '[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 TYPE int4_d_range; +DROP DOMAIN d_int4range1, d_textrange2c, d_textrange2, + textmultirange2_d, int4multirange_d, int4_d; 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..4e0d2df7775 100644 --- a/src/test/regress/sql/without_overlaps.sql +++ b/src/test/regress/sql/without_overlaps.sql @@ -100,7 +100,91 @@ CREATE TABLE temporal_rng3 ( ); ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_pk; DROP TABLE temporal_rng3; -DROP TYPE textrange2; + +-- +-- tests for range over domain, multirange over a domain, custom range type over +-- domain. +-- +CREATE DOMAIN int4_d as integer check (value <> 10); +CREATE TYPE int4_d_range as range (subtype = int4_d); +CREATE DOMAIN int4multirange_d as int4multirange check (value <> '{[10,11)}'); +CREATE DOMAIN d_int4range1 AS int4range CHECK (VALUE <> '[10,11)'); +CREATE DOMAIN d_textrange2 AS textrange2 CHECK (VALUE <> '[c,d)'); +CREATE DOMAIN d_textrange2c AS d_textrange2; +CREATE DOMAIN textmultirange2_d AS textmultirange2 CHECK (VALUE <> '{[c,d)}'); + +CREATE TABLE temporal_rng4mrng ( + id d_int4range1, + valid_at int4multirange_d, + CONSTRAINT temporal_rng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO temporal_rng4mrng VALUES ('[1,2)', '{[10,11)}'); --error +INSERT INTO temporal_rng4mrng VALUES ('[1,2)', '{[10,13)}'), ('[1,2)', '{[2,13)}'); --error +DROP TABLE temporal_rng4mrng; + +CREATE TABLE temporal_rng4 ( + id d_int4range1, + id1 int4_d_range, + valid_at d_textrange2c, + CONSTRAINT temporal_rng4_pk PRIMARY KEY (id, id1, valid_at WITHOUT OVERLAPS) +); + +INSERT INTO temporal_rng4 VALUES ('[1,2)', '[1,2)', '[c,d)'); --error +INSERT INTO temporal_rng4 VALUES ('[1,2)', '[10,12)', '[a,g)'); --error +INSERT INTO temporal_rng4 + VALUES ('[2,3)', '[9,11)', '[B,C)'), ('[2,3)', '[9,11)', '[A,C)'); --error +INSERT INTO temporal_rng4 VALUES ('[1,2)', '[9,11)', '[a,g)'); +INSERT INTO temporal_rng4 VALUES ('[1,2)', '[9,11)', '[b,c)'); --error + +CREATE TABLE temporal_rngfk ( + parent_id d_int4range1, + id int4range, + id1 int4_d_range, + valid_at d_textrange2); +ALTER TABLE temporal_rngfk + ADD CONSTRAINT temporal_rngfk_fk + FOREIGN KEY (parent_id, id1, PERIOD valid_at) + REFERENCES temporal_rng4; + +INSERT INTO temporal_rngfk VALUES ('[1,2)', '[2,3)', '[9,11)', '[d,e)'); +TABLE temporal_rng4; +TABLE temporal_rngfk; + +UPDATE temporal_rng4 SET valid_at = '[c,d)'; --error +UPDATE temporal_rng4 SET valid_at = '[a,h)'; +UPDATE temporal_rng4 SET valid_at = '[f,g)'; --error + +DROP TABLE temporal_rng4, temporal_rngfk; + +-- domain on a multirange +CREATE TABLE temporal_mltrng4 ( + id d_int4range1, + valid_at textmultirange2_d, + CONSTRAINT temporal_rng4_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); + +INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[c,d)}'); --error +INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[a,g)}'); +INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[b,c)}'); --error +INSERT INTO temporal_mltrng4 VALUES ('[2,3)', '{[B,C)}'), ('[2,3)', '{[A,C)}'); --error + +CREATE TABLE temporal_mltrngfk (parent_id d_int4range1, id int4range, +valid_at textmultirange2_d); +ALTER TABLE temporal_mltrngfk + ADD CONSTRAINT temporal_mltrngfk_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng4; + +INSERT INTO temporal_mltrngfk VALUES ('[1,2)', '[2,3)', '{[d,e)}'); +TABLE temporal_mltrng4; +TABLE temporal_mltrngfk; + +UPDATE temporal_mltrng4 SET valid_at = '{[c,d)}'; --error +UPDATE temporal_mltrng4 SET valid_at = '{[a,h)}'; +UPDATE temporal_mltrng4 SET valid_at = '{[f,g)}'; --error + +DROP TABLE temporal_mltrng4, temporal_mltrngfk; + -- PK with one column plus a multirange: CREATE TABLE temporal_mltrng ( @@ -170,7 +254,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 +261,23 @@ CREATE TABLE temporal_rng3 ( ); ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_uq; DROP TABLE temporal_rng3; + +CREATE TABLE temporal_rng4 ( + id d_int4range1, + valid_at d_textrange2c, + CONSTRAINT temporal_rng4_pk UNIQUE (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO temporal_rng4 VALUES ('[1,2)', NULL), (NULL, '[1,2)'); +INSERT INTO temporal_rng4 VALUES ('[1,2)', '[c,d)'); --error +INSERT INTO temporal_rng4 VALUES ('[1,2)', '[a,d)'); +INSERT INTO temporal_rng4 VALUES ('[1,2)', '[b,c)'); --error +INSERT INTO temporal_rng4 VALUES ('[10,11)', NULL); --error +INSERT INTO temporal_rng4 VALUES ('[2,3)', '[B,C)'), ('[2,3)', '[A,C)'); --error + +DROP TABLE temporal_rng4; +DROP TYPE int4_d_range; +DROP DOMAIN d_int4range1, d_textrange2c, d_textrange2, + textmultirange2_d, int4multirange_d, int4_d; DROP TYPE textrange2; -- -- 2.34.1
