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(at)iki(dot)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