Date: Sat, 30 Aug 2025 11:58:59 -0500
From: Nico Williams <nico@cryptonector.com>
To: Sagar Tiwari <iaansagar@gmail.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Saw some strange behavior when using `INSERT ON CONFLICT` inside
 a transaction.
Message-ID: <aLMt00w2HukeP5k0@ubby>
References: <CAJ8WsYMYH0ZbxA4krA0JNnF1fPdvtdANxV-WDbwZCcC++QUODw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <CAJ8WsYMYH0ZbxA4krA0JNnF1fPdvtdANxV-WDbwZCcC++QUODw@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/aLMt00w2HukeP5k0%40ubby>
Precedence: bulk

On Sat, Aug 30, 2025 at 01:13:35PM +0530, 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'.

The transaction that loses the race to do the insert (step 3) has to
block somewhere waiting for the winning transaction to commit or
rollback.  The losing transaction could have continued speculatively and
block in the commit (step 4) instead of in the insert, but it still had
to block.

Does it matter that it blocks in the insert instead of the commit?