Subject: Re: Create unique GiST indexes

From: Paul A Jungwirth <[email-masked]>
Date: Fri, 2 Jan 2026 10:39:52 -0800

On Thu, Jan 1, 2026 at 11:17 PM Kirill Reshke <[email-masked]> 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. Like:
>
> ```
> reshke=# create table t(i int);
> CREATE TABLE
> reshke=# create extension btree_gist ;
> CREATE EXTENSION
> reshke=# create unique index on t using gist(i);
> CREATE INDEX
> reshke=# insert into t values(1);
> INSERT 0 1
> reshke=# insert into t values(2);
> INSERT 0 1
> ```
>
> now, do a concurrent delete and insert:
>
>
> s1: BEGIN; DELETE FROM t;
>
>
> s2:
> reshke=# begin;
> BEGIN
> reshke=*# insert into t values(1);
> ERROR:  duplicate key value violates unique constraint "t_i_idx"
> DETAIL:  Key (i)=(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,

-- 
Paul              ~{:-)
[email-masked]

---

From: Paul A Jungwirth <[email-masked]>
Date: Fri, 13 Mar 2026 13:29:38 -0700

> it yourself and share if you like.

I realized my approach to this patch is just wrong, because I'm only
searching one index leaf page for conflicts (whichever one the insert
lands on), and they might be in different pages, due to changes in
penalty, splits, and maybe other reasons. I've been working on a
different approach, but I don't have something ready to send in yet.
I've updated the commitfest status to Waiting on Author.

Yours,