Hi, On 2019-04-30 18:34:42 -0700, Melanie Plageman wrote: > On Tue, Apr 30, 2019 at 5:22 PM Andres Freund <and...@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