public inbox for [email protected]
help / color / mirror / Atom feedFrom: Matthias van de Meent <[email protected]>
To: Paul Jungwirth <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: jian he <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: SQL:2011 application time
Date: Wed, 12 Jun 2024 17:48:59 +0200
Message-ID: <CAEze2WjL=bPxU1PVGYVOStqNLi750+dX+RegFV1isusmJMGz6Q@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com>
<[email protected]>
<CACJufxHsYDfHK62FTHdLsgDenvJd_bEuxCiyH62Lacj7NvzX4w@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CA+TgmoaH-2fAttd24pA3p45TUHbM9hRU6ud=brhKsp9ENHXWDg@mail.gmail.com>
<[email protected]>
<CA+TgmobfDJuXJATNUco+g6AiUb3WS2Pi9FrJe9hKAHGw37RX-g@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CAEze2WiD+U1BuJDLGL=FXxa8hDxNALVE6Jij0cNXjp10Q=nZHw@mail.gmail.com>
<[email protected]>
<CAEze2Wh21V66udM8cbvBBsAgyQ_5x9nfR0d3sWzbmZk++ey7xw@mail.gmail.com>
<[email protected]>
On Wed, 5 Jun 2024 at 22:57, Paul Jungwirth <[email protected]> wrote:
>
> On Thu, May 9, 2024 at 5:44 PM Matthias van de Meent <[email protected]> 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, since I agree it is an
> important workflow to support. Here are some thoughts about what would need 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 (reverted for v17), we could
> change amcanunique to true for the GiST AM handler. If the index's opclasses had that sproc and it
> gave non-zero for RTEqualStrategyNumber, we would have a reliable "definition 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 something more general. You can
> already give opclasses for each indexed column. How about allowing operators as well? For instance
> `CREATE UNIQUE INDEX idx ON t (id WITH =, 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 OVERLAPS? I guess if the
> underlying index has (foo WITH = [, bar WITH =], 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 plans 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 supported. Maybe whatever caused
> problems for REINDEX isn't an issue for just INDEX, but it would take more 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)
view thread (10+ 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], [email protected], [email protected]
Subject: Re: SQL:2011 application time
In-Reply-To: <CAEze2WjL=bPxU1PVGYVOStqNLi750+dX+RegFV1isusmJMGz6Q@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