Re: Create unique GiST indexes

On Thu, Jan 1, 2026 at 11:17 PM Kirill Reshke <reshkekirill(at)gmail(dot)com> 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 ~{:-)
pj(at)illuminatedcomputing(dot)com