public inbox for [email protected]  

help / color / mirror / Atom feed

From: Gurjeet Singh <[email protected]>
To: Sasa Vilic <[email protected]>
Cc: [email protected]
Subject: Re: Concurrent delete and insert on same key, potentially a bug
Date: Fri, 28 Mar 2025 13:52:18 -0700
Message-ID: <CABwTF4UA=Jfsy1=-ZrWGvOSJi1xLhFBU4sMy9bffaEUSkcnw=Q@mail.gmail.com> (raw)
In-Reply-To: <CAOJhpYd_K6EdTs_3LAa0RMna59cC256cZerXAE9nfBKVDTFGjQ@mail.gmail.com>
References: <CAOJhpYd_K6EdTs_3LAa0RMna59cC256cZerXAE9nfBKVDTFGjQ@mail.gmail.com>

On Fri, Mar 28, 2025 at 12:39 PM Sasa Vilic <[email protected]> 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

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]
  Subject: Re: Concurrent delete and insert on same key, potentially a bug
  In-Reply-To: <CABwTF4UA=Jfsy1=-ZrWGvOSJi1xLhFBU4sMy9bffaEUSkcnw=Q@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