Hi,
On 2019-04-30 18:34:42 -0700, Melanie Plageman wrote:
> On Tue, Apr 30, 2019 at 5:22 PM Andres Freund <andres@anarazel.de> wrote:
>
> >
> > Not easily so - that's why the ON CONFLICT patch didn't add code
> > coverage for it :(. I wonder if you could whip something up by having
> > another non-unique expression index, where the expression acquires a
> > advisory lock? If that advisory lock where previously acquired by
> > another session, that should allow to write a reliable isolation test?
> >
> >
> So, I took a look at one of the existing tests that does something like what
> you mentioned and tried the following:
> ----------
> create table t1(key int, val text);
> create unique index t1_uniq_idx on t1(key);
> create or replace function t1_lock_func(int) returns int immutable language
> sql AS
> 'select pg_advisory_xact_lock_shared(1); select $1';
> create index t1_lock_idx ON t1(t1_lock_func(key));
> ----------
> s1:
> begin isolation level read committed;
> insert into t1 values(1, 'someval');
> s2:
> set default_transaction_isolation = 'read committed';
> insert into t1 values(1, 'anyval') on conflict(key) do update set val =
> 'updatedval';
> ----------
>
> So, the above doesn't work because s2 waits to acquire the lock in the first
> phase of the speculative insert -- when it is just checking the index,
> before
> inserting to the table and before inserting to the index.
Couldn't that be addressed by having t1_lock_func() acquire two locks?
One for blocking during the initial index probe, and one for the
speculative insertion?
I'm imagining something like
if (pg_try_advisory_xact_lock(1))
pg_advisory_xact_lock(2);
else
pg_advisory_xact_lock(1);
in t1_lock_func. If you then make the session something roughly like
s1: pg_advisory_xact_lock(1);
s1: pg_advisory_xact_lock(2);
s2: upsert t1 <blocking for 1>
s1: pg_advisory_xact_unlock(1);
s2: <continuing>
s2: <blocking for 2>
s1: insert into t1 values(1, 'someval');
s1: pg_advisory_xact_unlock(2);
s2: <continuing>
s2: spec-conflict
Greetings,
Andres Freund
