Feedback-ID: i76984098:Fastmail
Message-ID: <0902ed04-9752-4c1d-97cd-81ca448d490a@aklaver.com>
Date: Fri, 28 Mar 2025 13:57:36 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Concurrent delete and insert on same key, potentially a bug
To: Sasa Vilic <sasavilic@gmail.com>, pgsql-general@postgresql.org
References: <CAOJhpYd_K6EdTs_3LAa0RMna59cC256cZerXAE9nfBKVDTFGjQ@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAOJhpYd_K6EdTs_3LAa0RMna59cC256cZerXAE9nfBKVDTFGjQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/0902ed04-9752-4c1d-97cd-81ca448d490a%40aklaver.com>
Precedence: bulk

On 3/28/25 12:38, Sasa Vilic 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.

 From here:

https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-COMMITTED

13.2.1. Read Committed Isolation Level


"UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave 
the same as SELECT in terms of searching for target rows: they will only 
find target rows that were committed as of the command start time. 
However, such a target row might have already been updated (or deleted 
or locked) by another concurrent transaction by the time it is found. In 
this case, the would-be updater will wait for the first updating 
transaction to commit or roll back (if it is still in progress). If the 
first updater rolls back, then its effects are negated and the second 
updater can proceed with updating the originally found row. If the first 
updater commits, the second updater will ignore the row if the first 
updater deleted it, otherwise it will attempt to apply its operation to 
the updated version of the row. The search condition of the command (the 
WHERE clause) is re-evaluated to see if the updated version of the row 
still matches the search condition. If so, the second updater proceeds 
with its operation using the updated version of the row. In the case of 
SELECT FOR UPDATE and SELECT FOR SHARE, this means it is the updated 
version of the row that is locked and returned to the client."

The:

delete from tst1 where id = 1;

started before you did the COMMIT in terminal 1 so the DELETE query ends 
up doing:

"[...]  If the first updater commits, the second updater will ignore the 
row if the first updater deleted it, [...]"

Hence the DELETE 0 and the PK violation because the INSERT in terminal 1 
already created a row with id=1.


> 
> Thanks in advance!
> 
> Kind Regards
> Sasa Vilic
> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com