Re: Create unique GiST indexes

On Thu, 1 Jan 2026 at 17:12, Paul A Jungwirth
<pj(at)illuminatedcomputing(dot)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] https://www.postgresql.org/message-id/CA%2BrenyU4jKCxrtASJpssZmfrkWhi-%2BQ_PF__jxt8E23T755SPQ%40mail.gmail.com
>
> Yours,
>
> --
> Paul ~{:-)
> pj(at)illuminatedcomputing(dot)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