Thread

Re: domain for WITHOUT OVERLAPS

Paul A Jungwirth <pj@illuminatedcomputing.com> — 2025-12-23T03:25:46Z

On Wed, Dec 10, 2025 at 8:23 PM jian he <jian.universality@gmail.com> wrote:
> 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';

I'm not sure whether creating a domain on a range should create a
multirange or not. I asked the list on another thread. Since it does
not create a new range constructor either (or an entry in pg_range),
maybe not creating a new multirange is correct. Anyway I think we can
fix the WITHOUT OVERLAPS issue without dealing with that.


> 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 &&

Is there any performance concern about adding this lookup?
>From what I can tell we don't use TYPECACHE_DOMAIN_BASE_INFO very
often, so it is probably okay.
We can get here in the executor from ExecEvalWholeRowVar, but that
seems acceptable to me.


> +--
> +-- 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)}');

The comment doesn't seem to match. It mentions three scenarios, but I'm seeing:

- int4_d_range is a range over a domain.
- int4multirange_d is a domain over a multirange.
- d_int4range1 is a domain over a range.
- d_textrange2 is a domain over a custom range.
- d_textrange2c is a domain over a domain, with no extra constraint.
What is this one for?
- textmultirange2_d is a domain over a custom multirange.

I think the naming is confusing. Sometimes you prepend "d_" and
sometimes you append "_d".
Consistency helps here because I can infer what the name means without
having to look it up.
What do you think of always appending "_d"?

Why "d_int4range1"? I don't see a "d_int4range2".


> +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;

Okay, so we test a domain over a multirange here.

I don't understand the table name temporal_rng4mrng. It might be a
typo for temporal_mltrng4?


> +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)...
> +                                                   ^

Here we test a domain over a custom rangetype (d_textrange2c).
Does using domains for the non-WITHOUT OVERLAPS parts add anything to this test?
Or should we have separate tests where those are in the WITHOUT
OVERLAPS position?

We get a line number for the second failure above but not the first.
Is that something we can fix?


> +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;

Here we test foreign keys with a domain over a custom rangetype.
Again I'm not sure it adds anything to add int4_d_range.

What about a domain referencing a non-domain and a non-domain
referencing a domain (in the WITHOUT OVERLAPS position)?
We allow that for regular FK parts, so we should allow it for the
WITHOUT OVERLAPS part as well.


> +-- 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;

This is similar to the test above using table temporal_rng4mrng.
The only difference is that the multirange uses a custom range type.
That's great, but maybe the comment can express that, and the two test
groups could be adjacent?


> @@ -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

Here we test a domain over a custom rangetype. Great! But again a
comment explaining the intent would be good.

The order of the new test groups seems odd to me. I would have
expected to go from simple to complex, e.g. domain over range, domain
over custom range, domain over multirange, domain over custom
multirange.

Also testing a range over a domain (in WITHOUT OVERLAPS position) would be good.

Yours,

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com