Message-ID: <7c4f344adfc2848e3cfe68b148b3e6c352bae554.camel@cybertec.at>
Subject: Re: BUG #19098: Can't create unique gist index, where pg_indexes
 says that WITHOUT OVERLAPS does exacly that
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Tom Lane <tgl@sss.pgh.pa.us>, Paul A Jungwirth
 <pj@illuminatedcomputing.com>
Cc: n.bartek3762@gmail.com, pgsql-bugs@lists.postgresql.org, Peter
 Eisentraut	 <peter@eisentraut.org>, Matthias van de Meent
 <boekewurm+postgres@gmail.com>
Date: Fri, 31 Oct 2025 09:51:55 +0100
In-Reply-To: <3329404.1761849077@sss.pgh.pa.us>
References: <19098-7f8deb5093c13da2@postgresql.org>
	 <11e73b0d2d89476fb04e6055a80638f776bb6f18.camel@cybertec.at>
	 <2748212.1761750444@sss.pgh.pa.us> <2750966.1761751813@sss.pgh.pa.us>
	 <CA+renyV3dxf8TSEG7C0vpwwFDr67KF=FGCTZ8ZrXWxOK-T1vfw@mail.gmail.com>
	 <0e8907d8a0cb19b64d732ec7b44f8eb669b54075.camel@cybertec.at>
	 <CA+renyVFvUeCFeNYqkoQ4Sx+J9Wn+M3wqhtxuWVEpExvJYjsHQ@mail.gmail.com>
	 <3329404.1761849077@sss.pgh.pa.us>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/7c4f344adfc2848e3cfe68b148b3e6c352bae554.camel%40cybertec.at>
Precedence: bulk

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:
> >=20
> > - Add an opclass_parameter so you can say without_overlaps =3D 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.
>=20
> > 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?
>=20
> 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.
>=20
> 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.=C2=A0 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