Re: Adding a test for speculative insert abort case

On Tue, Apr 30, 2019 at 5:22 PM Andres Freund <andres(at)anarazel(dot)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?

--
Melanie Plageman