Re: SQL:2011 application time

On Wed, 5 Jun 2024 at 22:57, Paul Jungwirth <pj(at)illuminatedcomputing(dot)com> wrote:
>
> On Thu, May 9, 2024 at 5:44 PM Matthias van de Meent <boekewurm+postgres(at)gmail(dot)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, 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)