Subject: Re: domain for WITHOUT OVERLAPS

From: Paul A Jungwirth <[email-masked]>
Date: Wed, 11 Feb 2026 10:50:49 -0800

On Sun, Dec 28, 2025 at 11:18 PM jian he <[email-masked]> wrote:
>
> > 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.
> >
> >
> hi.
>
> To make WITHOUT OVERLAPS work with domains, ExecWithoutOverlapsNotEmpty requires
> the base type’s typtype. I do not see a viable alternative approach.
>
> We can cache the base type's typtype in TypeCacheEntry->domainBaseTyptype.

Okay.

> >
> > Also testing a range over a domain (in WITHOUT OVERLAPS position) would be good.
> >
> I do not think we need extensive foreign key–related tests, because
> check_exclusion_or_unique_constraint is only invoked for data changes on the
> primary key side. So, I kept only a single foreign key–related test.

The foreign key code also uses the column type, so there are still
interesting feature combinations here that should be tested rather
than just assuming they work.

> The attached patch should address all of your comments.
> I think you will like attached regress tests.

A lot of the issues I mentioned before don't look addressed to me.
What do you think of the v4 patch attached here? It includes a few
more FK tests. Some other details I changed:

+--
+-- tests for range over domain, multirange over a domain, custom
range type over
+-- domain, custom multi 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 int4range_d AS int4range CHECK (VALUE <> '[10,11)');
+CREATE DOMAIN textrange2_d AS textrange2 CHECK (VALUE <> '[c,d)');
+CREATE DOMAIN textrange2_dd AS textrange2_d;
+CREATE DOMAIN textmultirange2_d AS textmultirange2 CHECK (VALUE <> '{[c,d)}');

This comment still doesn't match the types created below it. In my v4
patch I've added a comment before each CREATE TYPE/DOMAIN line.

Also let's define these in the same order that we test them below.

+-- It is a range type rather than a domain type; however, the range type’s
+-- subtype is a domain type.
+CREATE TABLE temporal_rng4 (
+  id int4range_d,
+  valid_at int4_d_range,
+  CONSTRAINT temporal_rng4_pk PRIMARY KEY(id, valid_at WITHOUT OVERLAPS)
+);
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[10,11)'); --error
+INSERT INTO temporal_rng4 VALUES ('[10,11)', '[1,2)'); --error
+INSERT INTO temporal_rng4 VALUES ('[1,11)', '[9,10)'); --error
+INSERT INTO temporal_rng4 VALUES ('[1,2)', '[1,13)'), ('[1,2)',
'[2,5)'); --error
+DROP TABLE temporal_rng4;

We need to test a case that succeeds, not just cases that fail. Also
the last case doesn't violate the domain; it just conflicts with a
prior row. That's a good test too, but let's clarify.

The point is to test WITHOUT OVERLAPS, but several of these lines are
testing the id column, not the valid_at column. I made the id columns
be just regular int4range.

Likewise with the scenarios below.

Yours,

-- 
Paul              ~{:-)
[email-masked]

---

From: jian he <[email-masked]>
Date: Tue, 10 Mar 2026 15:41:49 +0800

Hi.

V4 looks good to me.

One minor question regarding comments in transformIndexConstraint:
/*
 * The WITHOUT OVERLAPS part (if any) must be a range or
 * multirange type.
 */

Should we change it to:

/*
 * The WITHOUT OVERLAPS part (if any) base type must be a range or
 * multirange type.
 */

--
jian
https://www.enterprisedb.com/

---

From: Kirill Reshke <[email-masked]>
Date: Tue, 10 Mar 2026 14:53:41 +0500

> Likewise with the scenarios below.

Hi!
I v4, test comments says we check for unique violation, while test
output says its exclusion constraint:

+INSERT INTO temporal_mltrng4 VALUES ('[1,2)', '{[1,13)}'), ('[1,2)',
'{[2,13)}'); -- not unique
+ERROR:  conflicting key value violates exclusion constraint
"temporal_mltrng4_pk"

Maybe we can update wording to be more precise? Like `-- overlaps with
other tuple`.

Other that this nit, patch looks good.

-- 
Best regards,
Kirill Reshke

---

From: Paul A Jungwirth <[email-masked]>
Date: Tue, 10 Mar 2026 09:31:58 -0700

> V4 looks good to me.

Thanks for taking a look!

>  * The WITHOUT OVERLAPS part (if any) base type must be a range or

Sure. Here is an updated patch. It has some comment adjustments based
on Kirill's feedback too.

Yours,

---

From: Paul A Jungwirth <[email-masked]>
Date: Tue, 10 Mar 2026 09:33:18 -0700

> Other that this nit, patch looks good.

Thanks for taking a look! I changed places we say `-- not unique` to
now say `-- overlaps`. v5 is attached to my reply to jian he.

Yours,

---

From: Tom Lane <[email-masked]>
Date: Tue, 07 Apr 2026 14:57:40 -0400

> now say `-- overlaps`. v5 is attached to my reply to jian he.

I pushed this patch, but not without significant revisions:

* I didn't think the case had been made for adding this field to
typcache.  There's only one user, it's in a very minority-interest
code path, and it'd add both space in TypeCacheEntry and a
noticeable number of cycles to TYPECACHE_DOMAIN_BASE_INFO requests.
(Admittedly the extra-cycles complaint is weak, since we expect
TypeCacheEntries to live a long time once populated.)  The other
problem was that the patch was buggy anyway: there is more than
one place in typcache.c that'd have to change.  I just moved the
get_typtype(typcache->domainBaseType) call to runtime instead.
We can revisit that if more potential uses appear.

* I thought the number of new regression test cases was quite
excessive compared to the value.  On my machine, the runtime for
without_overlaps.sql went from ~420ms to ~500ms, and it's already
the slowest in its parallel group by a good margin.  I cut it back to
just testing the base domain-over-range and range-over-domain cases,
which added only ~5ms.  (To be clear, I think it was worth making
those tests just to verify there are no other holes.  But carrying
them forward forevermore is another matter.)

* I happened to notice that the loop just above the changes in
transformIndexConstraint was buggy: it had "break" where it
should have had "continue".  That bug can be demonstrated with

CREATE TABLE temporal_rng4 (
  id int4range,
  bogus int,
  valid_at int4
);

ALTER TABLE temporal_rng4 DROP COLUMN bogus;

ALTER TABLE temporal_rng4 ADD
  CONSTRAINT temporal_rng4_pk UNIQUE (id, valid_at WITHOUT OVERLAPS);

which gave different errors with and without the DROP COLUMN
step.

			regards, tom lane