public inbox for [email protected]
help / color / mirror / Atom feedFrom: Kirill Reshke <[email protected]>
To: Paul A Jungwirth <[email protected]>
Cc: jian he <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: domain for WITHOUT OVERLAPS
Date: Tue, 10 Mar 2026 14:53:41 +0500
Message-ID: <CALdSSPgcp90V5nnA0NKy=TG9JgdG8kbg2LQD6paSOS7Q-11wAQ@mail.gmail.com> (raw)
In-Reply-To: <CA+renyX_5VweE4OJp+wYy2ON-TNr8rGuxt25GkacuWt0ZPP37Q@mail.gmail.com>
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>
On Wed, 11 Feb 2026 at 23:51, Paul A Jungwirth
<[email protected]> wrote:
>
> On Sun, Dec 28, 2025 at 11:18 PM jian he <[email protected]> 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 protected]
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
view thread (12+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: domain for WITHOUT OVERLAPS
In-Reply-To: <CALdSSPgcp90V5nnA0NKy=TG9JgdG8kbg2LQD6paSOS7Q-11wAQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox