Create unique GiST indexes

On Thu, 1 Jan 2026 at 17:12, Paul A Jungwirth
<pj@illuminatedcomputing.com> wrote:

Here is a patch to create unique GiST indexes. It is not really
finished yet, but I thought I would share. It is intended to address
the bug discussed at [1], where calling pg_get_indexdef on a WITHOUT
OVERLAPS constraint generates SQL that Postgres refuses to run. I
realized after sending that email that the thread is on pgsql-bugs,
but I think it belongs on pgsql-hackers now (for one reason, so I can
create a commitfest entry). Please see my notes at that link, and
apologies for the extra noise.

[1] /messages/by-id/CA+renyU4jKCxrtASJpssZmfrkWhi-+Q_PF__jxt8E23T755SPQ@mail.gmail.com

Yours,

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

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. I did have a look at your
patch. 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.

I have not yet looked at the exact reasons for this yet.

--
Best regards,
Kirill Reshke

On Thu, Jan 1, 2026 at 11:17 PM Kirill Reshke <reshkekirill@gmail.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@illuminatedcomputing.com

On Fri, Jan 2, 2026 at 10:39 AM Paul A Jungwirth
<pj@illuminatedcomputing.com> wrote:

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.

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,

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