Hi, On 2019-04-30 17:15:55 -0700, Melanie Plageman wrote: > Today, while poking around the table_complete_speculative code which Ashwin > mentioned in [1], we were trying to understand when exactly we would > complete a > speculative insert by aborting.
(FWIW, it's on my todo queue to look at this) > We added a logging message to heapam_tuple_complete_speculative before it > calls > heap_abort_speculative and ran the regression and isolation tests to see > what > test failed so we knew how to exercise this codepath. > No tests failed, so we spent some time trying to understand when 'succeeded' > would be true coming into heap_tuple_complete_speculative. > > Eventually, we figured out that if one transaction speculatively inserts a > tuple into a table with a unique index and then pauses before inserting the > value into the index, and while it is paused, another transaction > successfully > inserts a value which would conflict with that value, it would result in an > aborted speculative insertion. > > t1(id,val) > unique index t1(id) > > s1: insert into t1 values(1, 'someval') on conflict(id) do update set val = > 'someotherval'; > s1: pause in ExecInsert before calling ExecInsertIndexTuples > s2: insert into t1 values(1, 'someval'); > s2: continue > > We don't know of a way to add this scenario to the current isolation > framework. > Can anyone think of a good way to put this codepath under test? 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? Alternatively, as a fallback, there's a short pgbench test, I wonder if we could just adapt that to use ON CONFLICT UPDATE? Greetings, Andres Freund