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.
Then when the s1 is committed, we won't execute the speculative insert code at all and will go into ExecOnConflictUpdate instead.
Maybe I just need a different kind of advisory lock to allow ExecCheckIndexConstraints to be able to check the index here. I figured it is a read operation, so a shared advisory lock should be okay, but it seems like it is not okay
Without knowing any of the context, on an initial pass of debugging, I did notice that, in the initial check of the index by s2, XactLockTableWait is called with reason_wait as XLTW_InsertIndex (even though we are just trying to check it, so maybe it knows our intentions:))
Is there something I can do in the test to allow my check to go through but the insert to have to wait?
