public inbox for [email protected]  

help / color / mirror / Atom feed

From: Paul A Jungwirth <[email protected]>
To: Kirill Reshke <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Matthias van de Meent <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Laurenz Albe <[email protected]>
Cc: [email protected]
Cc: Peter Eisentraut <[email protected]>
Subject: Re: Create unique GiST indexes
Date: Fri, 2 Jan 2026 10:39:52 -0800
Message-ID: <CA+renyVX7iV_VQxGDCFWh1Q1-R9t+zuvP+UjgHyX9ExUcKHKpg@mail.gmail.com> (raw)
In-Reply-To: <CALdSSPjv0P__-Y+xySXx=CuFEA0knwn9y2kvHDOT_e4708bX5w@mail.gmail.com>
References: <CA+renyUKo9GC+btg07E2Dt8998kWxSvM-4z16jUA7SOxDcJ9OQ@mail.gmail.com>
	<CALdSSPjv0P__-Y+xySXx=CuFEA0knwn9y2kvHDOT_e4708bX5w@mail.gmail.com>

On Thu, Jan 1, 2026 at 11:17 PM Kirill Reshke <[email protected]> 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 protected]

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Create unique GiST indexes
  In-Reply-To: <CA+renyVX7iV_VQxGDCFWh1Q1-R9t+zuvP+UjgHyX9ExUcKHKpg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox