public inbox for [email protected]  

help / color / mirror / Atom feed

From: Achilleas Mantzios <[email protected]>
To: Sagar Tiwari <[email protected]>
To: [email protected]
Subject: Re: Saw some strange behavior when using `INSERT ON CONFLICT` inside a transaction.
Date: Sat, 30 Aug 2025 13:43:23 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAJ8WsYMYH0ZbxA4krA0JNnF1fPdvtdANxV-WDbwZCcC++QUODw@mail.gmail.com>
References: <CAJ8WsYMYH0ZbxA4krA0JNnF1fPdvtdANxV-WDbwZCcC++QUODw@mail.gmail.com>


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 
> 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 
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  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

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: Saw some strange behavior when using `INSERT ON CONFLICT` inside a transaction.
  In-Reply-To: <[email protected]>

* 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