MIME-Version: 1.0
References: 
 <CACJufxGoAmN_0iJ=hjTG0vGpOSOyy-vYyfE+-q0AWxrq2_p5XQ@mail.gmail.com>
 <CA+renyU=ukksgiMQioaUixG+DJY1w1cwGdssKy9_hj_uaYDkBA@mail.gmail.com>
 <CACJufxEABtUZf=M9h3XpvjiWKFNDxObPpe6Ly9_dd-k3_xJJ+w@mail.gmail.com>
 <CA+renyW+ia6Uewa5+9f+cgTinndj8wt7yiG=3yZ_FG-hmiHn3w@mail.gmail.com>
 <CACJufxFkUPuh+wXGgVFfGeU+HGuCnDJxWmg-8QwVtFj=EnxTGw@mail.gmail.com>
 <CA+renyX_5VweE4OJp+wYy2ON-TNr8rGuxt25GkacuWt0ZPP37Q@mail.gmail.com>
In-Reply-To: 
 <CA+renyX_5VweE4OJp+wYy2ON-TNr8rGuxt25GkacuWt0ZPP37Q@mail.gmail.com>
From: Kirill Reshke <reshkekirill@gmail.com>
Date: Tue, 10 Mar 2026 14:53:41 +0500
Message-ID: 
 <CALdSSPgcp90V5nnA0NKy=TG9JgdG8kbg2LQD6paSOS7Q-11wAQ@mail.gmail.com>
Subject: Re: domain for WITHOUT OVERLAPS
To: Paul A Jungwirth <pj@illuminatedcomputing.com>
Cc: jian he <jian.universality@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CALdSSPgcp90V5nnA0NKy%3DTG9JgdG8kbg2LQD6paSOS7Q-11wAQ%40mail.gmail.com>
Precedence: bulk

On Wed, 11 Feb 2026 at 23:51, Paul A Jungwirth
<pj@illuminatedcomputing.com> wrote:
>
> On Sun, Dec 28, 2025 at 11:18=E2=80=AFPM jian he <jian.universality@gmail=
.com> 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=E2=80=99s typtype. I do not see a viable alternative appr=
oach.
> >
> > We can cache the base type's typtype in TypeCacheEntry->domainBaseTypty=
pe.
>
> Okay.
>
> > >
> > > Also testing a range over a domain (in WITHOUT OVERLAPS position) wou=
ld be good.
> > >
> > I do not think we need extensive foreign key=E2=80=93related tests, bec=
ause
> > check_exclusion_or_unique_constraint is only invoked for data changes o=
n the
> > primary key side. So, I kept only a single foreign key=E2=80=93related =
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 =3D int4_d);
> +CREATE DOMAIN int4multirange_d as int4multirange check (value <> '{[10,1=
1)}');
> +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=
=E2=80=99s
> +-- 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              ~{:-)
> pj@illuminatedcomputing.com


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.

--=20
Best regards,
Kirill Reshke