On Thu, 1 Jan 2026 at 17:12, Paul A Jungwirth <[email protected]> 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 ~{:-) > [email protected]
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
