public inbox for [email protected]
help / color / mirror / Atom feedRe: Create unique GiST indexes
2+ messages / 1 participants
[nested] [flat]
* Re: Create unique GiST indexes
@ 2026-01-02 18:39 Paul A Jungwirth <[email protected]>
2026-03-13 20:29 ` Re: Create unique GiST indexes Paul A Jungwirth <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Paul A Jungwirth @ 2026-01-02 18:39 UTC (permalink / raw)
To: Kirill Reshke <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Matthias van de Meent <[email protected]>; Tom Lane <[email protected]>; Laurenz Albe <[email protected]>; [email protected]; Peter Eisentraut <[email protected]>
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]
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Create unique GiST indexes
2026-01-02 18:39 Re: Create unique GiST indexes Paul A Jungwirth <[email protected]>
@ 2026-03-13 20:29 ` Paul A Jungwirth <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Paul A Jungwirth @ 2026-03-13 20:29 UTC (permalink / raw)
To: Kirill Reshke <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Matthias van de Meent <[email protected]>; Tom Lane <[email protected]>; Laurenz Albe <[email protected]>; [email protected]; Peter Eisentraut <[email protected]>; Warda Bibi <[email protected]>; Prafulla P. Ranadive <[email protected]>
On Fri, Jan 2, 2026 at 10:39 AM Paul A Jungwirth
<[email protected]> 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 ~{:-)
[email protected]
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-03-13 20:29 UTC | newest]
Thread overview: 2+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-02 18:39 Re: Create unique GiST indexes Paul A Jungwirth <[email protected]>
2026-03-13 20:29 ` Paul A Jungwirth <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox