On Wed, 5 Jun 2024 at 22:57, Paul Jungwirth <p...@illuminatedcomputing.com> wrote: > > On Thu, May 9, 2024 at 5:44 PM Matthias van de Meent > <boekewurm+postg...@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, 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)
