On Tue, Dec 2, 2025 at 11:39 PM jian he <[email protected]> wrote:
>
> 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.
Thanks for the bug report and fix! Have you created a commitfest entry
for this? I didn't find one.
+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;
The first domain should be called d_int4range or d_int4range1, right?
Also let's name them like int4range_d so that we can use int4_d_range
for a range over a domain.
Please use closed/open notation to match the rest of the ranges in the
file: '[10,11)' and '[c,d)'.
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
For instance we could set things up like so:
-- range+multirange over a domain:
create domain int4_d as integer check (value <> 10);
create type int4_d_range as range (subtype = int4_d);
-- domain on a range:
create domain int4range_d as int4range check (value <> '[10,11)');
-- domain on a multirange:
create domain int4multirange_d as int4multirange check (value <> '{[10,11)}');
Then we have this:
```
[v19devel:5432][426675] postgres=# select oid, typname, typtype,
typbasetype, typtypmod from pg_type where oid >= 20000 order by oid ;
oid | typname | typtype | typbasetype | typtypmod
-------+--------------------+---------+-------------+-----------
24595 | _int4_d | b | 0 | -1
24596 | int4_d | d | 23 | -1
24598 | _int4_d_range | b | 0 | -1
24599 | int4_d_multirange | m | 0 | -1
24600 | _int4_d_multirange | b | 0 | -1
24601 | int4_d_range | r | 0 | -1
24608 | _int4range_d | b | 0 | -1
24609 | int4range_d | d | 3904 | -1
24611 | _int4multirange_d | b | 0 | -1
24612 | int4multirange_d | d | 4451 | -1
```
Note that creating a domain on a range does not create a corresponding
multirange. Maybe that is a bug. It means we can't test the 4th case
above.
It looks like domains on the subtype work, but not directly on a range
or multirange:
```
[v19devel:5432][426675] postgres=# create table t1 (id int4range,
valid_at int4_d_range, primary key (id, valid_at without overlaps));
CREATE TABLE
[v19devel:5432][426675] postgres=# create table t2 (id int4range,
valid_at int4range_d, primary key (id, valid_at without overlaps));
ERROR: column "valid_at" in WITHOUT OVERLAPS is not a range or multirange type
LINE 1: ...ate table t2 (id int4range, valid_at int4range_d, primary ke...
^
[v19devel:5432][426675] postgres=# create table t3 (id int4range,
valid_at int4_d_multirange, primary key (id, valid_at without
overlaps));
CREATE TABLE
[v19devel:5432][426675] postgres=# create table t4 (id int4range,
valid_at int4_multirange_d, primary key (id, valid_at without
overlaps));
ERROR: type "int4_multirange_d" does not exist
LINE 1: create table t4 (id int4range, valid_at int4_multirange_d, p...
```
It would be good to have tests for all of those. For instance:
```
-- domain on a multirange:
CREATE DOMAIN textmultirange2_d AS textmultirange2 CHECK (VALUE <> '{[c,d)}');
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)}'); --domain
constraint violation
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,2]',
'{[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;
```
When I try that, it looks like your patch fixes multiranges too.
Yours,
--
Paul ~{:-)
[email protected]
