MIME-Version: 1.0
References: <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com>
 <49c07eac-18ec-4aff-929c-e81b87df1092@eisentraut.org> <CACJufxHsYDfHK62FTHdLsgDenvJd_bEuxCiyH62Lacj7NvzX4w@mail.gmail.com>
 <d579a67c-e8d7-4eb7-ae14-f6fca01489cf@illuminatedcomputing.com>
 <1edb3b79-a4bc-43b1-80aa-2e090472fe32@eisentraut.org> <47550967-260b-4180-9791-b224859fe63e@illuminatedcomputing.com>
 <CA+TgmoaH-2fAttd24pA3p45TUHbM9hRU6ud=brhKsp9ENHXWDg@mail.gmail.com>
 <1b2ab118-82c1-4e79-a094-04dab382e2c4@illuminatedcomputing.com>
 <CA+TgmobfDJuXJATNUco+g6AiUb3WS2Pi9FrJe9hKAHGw37RX-g@mail.gmail.com>
 <3775839b-3f0f-4c8a-ac03-a253222e6a4b@illuminatedcomputing.com>
 <731ef81e-d4c3-41c6-ba8f-f6131f4f6407@eisentraut.org> <1426589a-83cb-4a89-bf40-713970c07e63@illuminatedcomputing.com>
 <CAEze2WiD+U1BuJDLGL=FXxa8hDxNALVE6Jij0cNXjp10Q=nZHw@mail.gmail.com>
 <5aefa87e-ba20-4a34-88aa-233255643c1b@illuminatedcomputing.com>
 <CAEze2Wh21V66udM8cbvBBsAgyQ_5x9nfR0d3sWzbmZk++ey7xw@mail.gmail.com> <3ad31f4c-8caf-4b9e-9f1f-b949c6330005@illuminatedcomputing.com>
In-Reply-To: <3ad31f4c-8caf-4b9e-9f1f-b949c6330005@illuminatedcomputing.com>
From: Matthias van de Meent <boekewurm+postgres@gmail.com>
Date: Wed, 12 Jun 2024 17:48:59 +0200
Message-ID: <CAEze2WjL=bPxU1PVGYVOStqNLi750+dX+RegFV1isusmJMGz6Q@mail.gmail.com>
Subject: Re: SQL:2011 application time
To: Paul Jungwirth <pj@illuminatedcomputing.com>
Cc: Peter Eisentraut <peter@eisentraut.org>, Robert Haas <robertmhaas@gmail.com>, 
	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/CAEze2WjL%3DbPxU1PVGYVOStqNLi750%2BdX%2BRegFV1isusmJMGz6Q%40mail.gmail.com>
Precedence: bulk

On Wed, 5 Jun 2024 at 22:57, Paul Jungwirth <pj@illuminatedcomputing.com> w=
rote:
>
> On Thu, May 9, 2024 at 5:44=E2=80=AFPM Matthias van de Meent <boekewurm+p=
ostgres@gmail.com> wrote:
>  > Additionally, because I can't create my own non-constraint-backing
>  > unique GIST indexes, I can't pre-create my unique constraints
>  > CONCURRENTLY as one could do for the non-temporal case
>
> We talked about this a bit at pgconf.dev. I would like to implement it, s=
ince I agree it is an
> important workflow to support. Here are some thoughts about what would ne=
ed to be done.
>
> First we could take a small step: allow non-temporal UNIQUE GiST indexes.=
 This is possible according
> to [1], but in the past we had no way of knowing which strategy number an=
 opclass was using for
> equality. With the stratnum support proc introduced by 6db4598fcb (revert=
ed for v17), we could
> change amcanunique to true for the GiST AM handler. If the index's opclas=
ses had that sproc and it
> gave non-zero for RTEqualStrategyNumber, we would have a reliable "defini=
tion of uniqueness". UNIQUE
> GiST indexes would raise an error if they detected a duplicate record.

Cool.

> But that is just regular non-temporal indexes. To avoid a long table lock=
 you'd need a way to build
> the index that is not just unique, but also does exclusion based on &&.  =
We could borrow syntax from
> SQL:2011 and allow `CREATE INDEX idx ON t (id, valid_at WITHOUT OVERLAPS)=
`. But since CREATE INDEX
> is a lower-level concept than a constraint, it'd be better to do somethin=
g more general. You can
> already give opclasses for each indexed column. How about allowing operat=
ors as well? For instance
> `CREATE UNIQUE INDEX idx ON t (id WITH =3D, valid_at WITH &&)`? Then the =
index would know to enforce
> those rules.

I think this looks fine. I'd like it even better if we could default
to the equality operator that's used by the type's default btree
opclass in this syntax; that'd make CREATE UNIQUE INDEX much less
awkward for e.g. hash indexes.

> This is the same data we store today in pg_constraint.conexclops. So that=
 would get
> moved/copied to pg_index (probably moved).

I'd keep the pg_constraint.conexclops around: People are inevitably
going to want to keep the current exclusion constraints' handling of
duplicate empty ranges, which is different from expectations we see
for UNIQUE INDEX's handling.

> Then when you add the constraint, what is the syntax? Today when you say =
PRIMARY KEY/UNIQUE USING
> INDEX, you don't give the column names. So how do we know it's WITHOUT OV=
ERLAPS? I guess if the
> underlying index has (foo WITH =3D [, bar WITH =3D], baz WITH &&) we just=
 assume the user wants WITHOUT
> OVERLAPS, and otherwise they want a regular PK/UQ constraint?

Presumably you would know this based on the pg_index.indisunique flag?

> In addition this workflow only works if you can CREATE INDEX CONCURRENTLY=
. I'm not sure yet if we'll
> have problems there. I noticed that for REINDEX at least, there were plan=
s in 2012 to support
> exclusion-constraint indexes,[2] but when the patch was committed in 2019=
 they had been dropped,
> with plans to add support eventually.[3] Today they are still not support=
ed. Maybe whatever caused
> problems for REINDEX isn't an issue for just INDEX, but it would take mor=
e research to find out.

I don't quite see where exclusion constraints get into the picture?
Isn't this about unique indexes, not exclusion constraints? I
understand exclusion constraints are backed by indexes, but that
doesn't have to make it a unique index, right? I mean, currently, you
can write an exclusion constraint that makes sure that all rows with a
certain prefix have the same suffix columns (given a btree-esque index
type with <> -operator support), which seems exactly opposite of what
unique indexes should do.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech)