pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedSaw some strange behavior when using `INSERT ON CONFLICT` inside a transaction.
3+ messages / 3 participants
[nested] [flat]
* Saw some strange behavior when using `INSERT ON CONFLICT` inside a transaction.
@ 2025-08-30 07:43 Sagar Tiwari <iaansagar@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Sagar Tiwari @ 2025-08-30 07:43 UTC (permalink / raw)
To: pgsql-general
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'.
--
Best Regards
Sagar Tiwari
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Saw some strange behavior when using `INSERT ON CONFLICT` inside a transaction.
@ 2025-08-30 10:43 Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
parent: Sagar Tiwari <iaansagar@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Achilleas Mantzios @ 2025-08-30 10:43 UTC (permalink / raw)
To: Sagar Tiwari <iaansagar@gmail.com>; pgsql-general
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Saw some strange behavior when using `INSERT ON CONFLICT` inside a transaction.
@ 2025-08-30 16:58 Nico Williams <nico@cryptonector.com>
parent: Sagar Tiwari <iaansagar@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Nico Williams @ 2025-08-30 16:58 UTC (permalink / raw)
To: Sagar Tiwari <iaansagar@gmail.com>; +Cc: pgsql-general
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?
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-08-30 16:58 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-08-30 07:43 Saw some strange behavior when using `INSERT ON CONFLICT` inside a transaction. Sagar Tiwari <iaansagar@gmail.com>
2025-08-30 10:43 ` Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
2025-08-30 16:58 ` Nico Williams <nico@cryptonector.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox