MIME-Version: 1.0
From: Sasa Vilic <sasavilic@gmail.com>
Date: Fri, 28 Mar 2025 20:38:50 +0100
Message-ID: <CAOJhpYd_K6EdTs_3LAa0RMna59cC256cZerXAE9nfBKVDTFGjQ@mail.gmail.com>
Subject: Concurrent delete and insert on same key, potentially a bug
To: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="00000000000060fb2a06316c3abc"
Archived-At: <https://www.postgresql.org/message-id/CAOJhpYd_K6EdTs_3LAa0RMna59cC256cZerXAE9nfBKVDTFGjQ%40mail.gmail.com>
Precedence: bulk

--00000000000060fb2a06316c3abc
Content-Type: text/plain; charset="UTF-8"

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.

Thanks in advance!

Kind Regards
Sasa Vilic

--00000000000060fb2a06316c3abc
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hello, <br></div><div><br></div><div>Please excuse my=
 ignorance, because I might be missing something obvious, but how is this n=
ot a bug? <br></div><div><br></div><div>I simply open 2 terminals and conne=
ct to the same database. <br></div><div><br></div><div>**TERMINAL 1:**</div=
><div><br></div>sasa=3D# create table tst1(id int primary key);<br>CREATE T=
ABLE<br>sasa=3D# insert into tst1(id) values(1);<br>INSERT 0 1<br>sasa=3D# =
begin;<br><div>BEGIN</div><div>sasa=3D# select * from tst1 where id =3D 1;<=
br>=C2=A0id <br>----<br>=C2=A0 1<br>(1 row)<br></div><div><br></div><div>**=
TERMINAL 2:**</div><div><br></div><div>sasa=3D# begin;<br>BEGIN<br>sasa=3D#=
 select * from tst1 where id =3D 1;<br>=C2=A0id <br>----<br>=C2=A0 1<br>(1 =
row)<br><br></div><div>**TERMINAL 1:**</div><div><br></div>sasa=3D# delete =
from tst1 where id =3D 1;<br>DELETE 1<br>sasa=3D# insert into tst1(id) valu=
es(1);<br><div>INSERT 0 1</div><div><br></div><div>**TERMINAL 2:**</div><br=
><div>sasa=3D# delete from tst1 where id =3D 1;</div><div>// client is bloc=
ked due to row lock/index lock</div><div><br></div><div>**TERMINAL 1:**</di=
v><div><br></div><div>sasa=3D# commit;<br>COMMIT</div><div><br></div><div>*=
*TERMINAL 2:**</div><div>// resumes because terminal 1 committed transactio=
n</div><div>DELETE 0<br>sasa=3D# insert into tst1(id) values(1);<br>ERROR: =
=C2=A0duplicate key value violates unique constraint &quot;tst1_pkey&quot;<=
br>DETAIL: =C2=A0Key (id)=3D(1) already exists.<br></div><div><br></div><di=
v>To my understanding, the second client (terminal 2) should already see ch=
anges from the first client, because the transaction isolation level is REA=
D COMMITTED and the first client did commit its transaction.</div><div><br>=
</div><div>Thanks in advance!</div><div><br></div><div>Kind Regards</div><d=
iv>Sasa Vilic</div><div><br></div><div><br></div><br></div>

--00000000000060fb2a06316c3abc--