Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1usJ3U-003656-W2 for pgsql-general@arkaria.postgresql.org; Sat, 30 Aug 2025 10:43:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1usJ3S-00F4mO-Qb for pgsql-general@arkaria.postgresql.org; Sat, 30 Aug 2025 10:43:31 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1usJ3S-00F4mF-BH for pgsql-general@lists.postgresql.org; Sat, 30 Aug 2025 10:43:30 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by makus.postgresql.org with smtp (Exim 4.96) (envelope-from ) id 1usJ3P-002W14-0E for pgsql-general@postgresql.org; Sat, 30 Aug 2025 10:43:29 +0000 Content-Type: multipart/alternative; boundary="------------RSfkXyjWsiYPaT0TLGUJcaF4" Message-ID: <771eb8cf-42c7-468b-98ce-d0e8f86443d3@cloud.gatewaynet.com> Date: Sat, 30 Aug 2025 13:43:23 +0300 MIME-Version: 1.0 Subject: Re: Saw some strange behavior when using `INSERT ON CONFLICT` inside a transaction. To: Sagar Tiwari , pgsql-general@postgresql.org References: Content-Language: en-US From: Achilleas Mantzios In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------RSfkXyjWsiYPaT0TLGUJcaF4 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: quoted-printable On 8/30/25 10:43, Sagar Tiwari wrote: > Repro steps: > * I created the table: > ``` > create table t (col TEXT primary key); > ``` > > * Open two database consoles and use the following queries in them: > > ``` > 1 begin; > 2 select txid_current(); > 3 insert into t (col) values ('test') on conflict (col) do nothing; > 4 commit; > ``` > > I ran 1 and 2 in both. It worked for both. > And then I ran query 3 in the first console. It worked. > After that I ran query 3 in the second console. It got stuck! > When I commit the tx in first console, the stuck query in second=20 > console finishes > The transaction isolation level is 'read committed'. This is absolutely as it should be. When you issue the statement 3 in the second transaction it cannot know=20 if the first transaction will be committed, rollbacked or aborted. If=20 the first transaction commits, then there will be a conflict on the=20 insertion in the=C2=A0 second xaction and it should do nothing, so this=20 statement will not insert anything and return successfully, then the=20 commit should work as well. But if the first transaction aborts or=20 rollbacks, then the insert on the 2nd xaction should be attempted and=20 most probably succeed. That's why the second xaction blocks. You can=20 view the locks as you experiment via the pg_locks view. > > --=20 > Best Regards > Sagar Tiwari --------------RSfkXyjWsiYPaT0TLGUJcaF4 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable


On 8/30/25 10:43, Sagar Tiwari wrote:<= br>
Repro steps:
*=C2=A0I created the table:
```=
create table=C2=A0t (col TEXT primary key);
```

*=C2=A0Open two database consoles and use the following queries in them:

```
1 begin;
2 select txid_current();
3 insert into t (col) values ('test')=C2=A0on conflict (col) do nothing;
4 commit;
```

I ran 1 and 2 in both. It worked for both.
And then I ran query 3 in the first console. It worked.
After that I ran query 3 in the second console. It got stuck!
When I commit the tx in first console, the stuck query in second console finishes
The transaction isolation level is 'read committed'.=C2=A0

This is absolutely as it should be.=C2=A0

When you issue the statement 3 in the second transaction it cannot know if the first transaction will be committed, rollbacked or aborted. If the first transaction commits, then there will be a conflict on the insertion in the=C2=A0 second xaction and it should= do nothing, so this statement will not insert anything and return successfully, then the commit should work as well. But if the first transaction aborts or rollbacks, then the insert on the 2nd xaction should be attempted and most probably succeed. That's why the second xaction blocks. You can view the locks as you experiment via the pg_locks view.


--
Best Regards
Sagar Tiwari
--------------RSfkXyjWsiYPaT0TLGUJcaF4--