MIME-Version: 1.0
References: <CA+renyUKo9GC+btg07E2Dt8998kWxSvM-4z16jUA7SOxDcJ9OQ@mail.gmail.com>
 <CALdSSPjv0P__-Y+xySXx=CuFEA0knwn9y2kvHDOT_e4708bX5w@mail.gmail.com>
In-Reply-To: <CALdSSPjv0P__-Y+xySXx=CuFEA0knwn9y2kvHDOT_e4708bX5w@mail.gmail.com>
From: Paul A Jungwirth <pj@illuminatedcomputing.com>
Date: Fri, 2 Jan 2026 10:39:52 -0800
Message-ID: <CA+renyVX7iV_VQxGDCFWh1Q1-R9t+zuvP+UjgHyX9ExUcKHKpg@mail.gmail.com>
Subject: Re: Create unique GiST indexes
To: Kirill Reshke <reshkekirill@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, 
	Matthias van de Meent <boekewurm+postgres@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, 
	Laurenz Albe <laurenz.albe@cybertec.at>, n.bartek3762@gmail.com, 
	Peter Eisentraut <peter@eisentraut.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CA%2BrenyVX7iV_VQxGDCFWh1Q1-R9t%2BzuvP%2BUjgHyX9ExUcKHKpg%40mail.gmail.com>
Precedence: bulk

On Thu, Jan 1, 2026 at 11:17=E2=80=AFPM Kirill Reshke <reshkekirill@gmail.c=
om> wrote:
>
> Hi!
> I was also wondering about UNIQUE gist index desirability. Given
> nearby WITHOUT OVERLAPS thread, looks like this is actually desirable.
>
> I will try to help move this thread forward.

Thanks for taking a look! I noticed these emails were now going to
*both* pgsql-hackers and pgsql-bugs (my fault), so I've removed
pgsql-bugs from the CC list. Sorry about that!

> The first issue I encounter, is following:
> We have different behaviour for BTREE vs GIST for concurrent deletion. Li=
ke:
>
> ```
> reshke=3D# create table t(i int);
> CREATE TABLE
> reshke=3D# create extension btree_gist ;
> CREATE EXTENSION
> reshke=3D# create unique index on t using gist(i);
> CREATE INDEX
> reshke=3D# insert into t values(1);
> INSERT 0 1
> reshke=3D# insert into t values(2);
> INSERT 0 1
> ```
>
> now, do a concurrent delete and insert:
>
>
> s1: BEGIN; DELETE FROM t;
>
>
> s2:
> reshke=3D# begin;
> BEGIN
> reshke=3D*# insert into t values(1);
> ERROR:  duplicate key value violates unique constraint "t_i_idx"
> DETAIL:  Key (i)=3D(1) already exists.
>
> With the UNIQUE GIST index we receive errors immediately, we are not
> waiting for s1 to complete. If create BTREE index, s2 will wait until
> s1 completes, and then commits/errors depending on s1
> commit/rollbacks.

This is the missing MVCC functionality I mentioned when I posted the
patch. It's the next thing on my list to work on. As I said the patch
is not really done. But it took longer than I expected to send a reply
to Matthias, and I wanted to post something before the commitfest
deadline. And I thought I at least had enough to get feedback on the
overall approach.

I think your example here would make a great isolation test though.
I'll incorporate that into future work, or please feel free to write
it yourself and share if you like.

Yours,

--=20
Paul              ~{:-)
pj@illuminatedcomputing.com