Re: INSERT ... ON CONFLICT UPDATE/IGNORE 4.0

On 04/27/2015 11:02 PM, Peter Geoghegan wrote:
> On Mon, Apr 27, 2015 at 8:31 PM, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
>> I thought we had an ironclad scheme to prevent deadlocks like this, so I'd
>> like to understand why that happens.
>
>
> Okay. I think I know how it happens (I was always skeptical of the
> idea that this would be 100% reliable), but I'll be able to show you
> exactly how tomorrow. I'll have pg_xlogdump output then.

I was able to reproduce this, using two sessions, so that on session 
does a regular INSERT, and another does INSERT ON CONFLICT, after adding 
a sleep(5) to a strategic place. So this was indeed a live bug, 
reproducible even without the hack you had to allow ON CONFLICT UPDATE 
with exclusion constraints. Fortunately this is easy to fix.

Here's how to reproduce:

1. Insert "sleep(5)" into ExecInsertIndexTuples, just after the 
index_insert() call.

2. Create the test table and index:

create extension btree_gist;
create table foo (id int4, constraint foo_x exclude using gist (id with 
=) );

3. Launch two psql sessions, A and B. Do the following:

A: set deadlock_timeout='10s';
B: set deadlock_timeout='20s';
A: begin; select txid_current();
B: begin; select txid_current();

A: insert into foo values (1) on conflict do nothing;
(the insert will hit the sleep(5) - quickly perform the second insert 
quickly: )
B: insert into foo values (1);

At this point, both transactions have already inserted the tuple to the 
heap. A has done so speculatively, but B has done a regular insert. B 
will find A's tuple and wait until A's speculative insertion completes. 
A will find B's tuple, and wait until B completes, and you get the 
deadlock. Thanks to the way the deadlock_timeout's are set, A will 
detect the deadlock first and abort. That's not cool with ON CONFLICT 
IGNORE.

To fix that, we need to fix the "livelock insurance" check so that A 
does not wait for B here. Because B is not a speculative insertion, A 
should cancel its speculative insertion and retry instead. (I pushed the 
one-line fix for that to your github repository)

- Heikki