Re: Concurrent delete and insert on same key, potentially a bug

On Fri, Mar 28, 2025 at 12:39 PM Sasa Vilic <sasavilic(at)gmail(dot)com> wrote:
>
> Hello,
>
> Please excuse my ignorance, because I might be missing something obvious, but how is this not a bug?
>
> I simply open 2 terminals and connect to the same database.
>
> **TERMINAL 1:**
>
> sasa=# create table tst1(id int primary key);
> CREATE TABLE
> sasa=# insert into tst1(id) values(1);
> INSERT 0 1
> sasa=# begin;
> BEGIN
> sasa=# select * from tst1 where id = 1;
> id
> ----
> 1
> (1 row)
>
> **TERMINAL 2:**
>
> sasa=# begin;
> BEGIN
> sasa=# select * from tst1 where id = 1;
> id
> ----
> 1
> (1 row)
>
> **TERMINAL 1:**
>
> sasa=# delete from tst1 where id = 1;
> DELETE 1
> sasa=# insert into tst1(id) values(1);
> INSERT 0 1
>
> **TERMINAL 2:**
>
> sasa=# delete from tst1 where id = 1;
> // client is blocked due to row lock/index lock
>
> **TERMINAL 1:**
>
> sasa=# commit;
> COMMIT
>
> **TERMINAL 2:**
> // resumes because terminal 1 committed transaction
> DELETE 0
> sasa=# insert into tst1(id) values(1);
> ERROR: duplicate key value violates unique constraint "tst1_pkey"
> DETAIL: Key (id)=(1) already exists.
>
> To my understanding, the second client (terminal 2) should already see changes from the first client, because the transaction isolation level is READ COMMITTED and the first client did commit its transaction.
>

The session/transaction interactions you show are exactly how I'd
expect the to interact. I don't see any buggy behaviour here.

It would help others if you pointed at specific lines in the text
above and share what you would expect instead to happen at that step.
I think your concern is about the point in time where the second
session prints DELETE 0, but can't say for sure.

Best regards,
Gurjeet
http://Gurje.et