Re: IO related waits

You may be able to solve this with advisory locks. In particular,
transaction-level advisory locks with the "try-pass/fail" variant. Here,
"123" is a unique number used by your app, related to this particular
table. You also need to force read committed mode, as the advisory locks go
away after the transaction ends, so at that point, we switch from advisory
locks to the ON CONFLICT clause, which can only work smoothly if other
processes can see the new row right away.

drop table if exists t1;
create table t1(id int primary key);

-- Session 1:
begin transaction isolation level read committed;

-- inserts one row:
insert into t1 select 1 where pg_try_advisory_xact_lock(123,1)
on conflict(id) do nothing;

-- Session 2:
begin transaction isolation level read committed;

-- inserts one row:
insert into t1 select 2 where pg_try_advisory_xact_lock(123,2)
on conflict(id) do nothing;

-- Session 1:

-- silently 'fails' because no lock is granted, so inserts zero rows:
insert into t1 select 2 where pg_try_advisory_xact_lock(123,2)
on conflict(id) do nothing;

-- Session 2:

-- silently 'fails' because no lock is granted, so inserts zero rows:
insert into t1 select 1 where pg_try_advisory_xact_lock(123,1)
on conflict(id) do nothing;

-- inserts one row:
insert into t1 select 3 where pg_try_advisory_xact_lock(123,3)
on conflict(id) do nothing;

commit; -- lock on 2 and 3 goes away

-- Session 1:

-- silently fails because of the on conflict clause
insert into t1 select 3 where pg_try_advisory_xact_lock(123,3)
on conflict(id) do nothing;

Cheers,
Greg