Thread

Re: domain for WITHOUT OVERLAPS

Paul A Jungwirth <pj@illuminatedcomputing.com> — 2025-12-03T18:39:40Z

On Tue, Dec 2, 2025 at 11:39 PM jian he <jian.universality@gmail.com> 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              ~{:-)
pj@illuminatedcomputing.com