Thread

Re: BUG #19098: Can't create unique gist index, where pg_indexes says that WITHOUT OVERLAPS does exacly that

Laurenz Albe <laurenz.albe@cybertec.at> — 2025-10-31T08:51:55Z

On Thu, 2025-10-30 at 14:31 -0400, Tom Lane wrote:
> Paul A Jungwirth <pj@illuminatedcomputing.com> writes:
> > So here is a proposed sequence of work:
> > 
> > - Add an opclass_parameter so you can say without_overlaps = true.
> > Only the last column of the index allows that (at least for now), and
> > there must be an overlaps operator.
> > - If an index has that property, enforce the exclusion constraint
> > rules and forbid empty ranges/multiranges.
> > - Update pg_get_indexdef to output the right syntax to create an
> > independent temporal index.
> 
> > Doing the first 3 items here would fix pg_get_indexdef. The hard one
> > is the second, but I will work on a patch for it. Is that something
> > we'd want to release as a patch to v18?
> 
> I think that's too much risk and churn for v18 at this point.
> Even if we risked putting in such a patch, we'd have a situation
> where pg_get_indexdef in later v18 minor releases would output
> syntax that's rejected by earlier minor releases, which would
> be a mess.
> 
> My feeling is that pg_get_indexdef is broken for these indexes, but
> there's nothing we can do now to improve that in v18.  The important
> thing is to make sure it gets fixed for 19.

One thing we could do is have pg_get_indexdef() throw an error rather
than producing wrong output.

Yours,
Laurenz Albe