Re: INSERT ... ON CONFLICT {UPDATE | IGNORE} 2.0

On 02/21/2015 10:41 PM, Peter Geoghegan wrote:
> On Sat, Feb 21, 2015 at 11:15 AM, Heikki Linnakangas
> <hlinnakangas(at)vmware(dot)com> wrote:
>> What I had in mind is that the "winning" inserter waits on the other
>> inserter's token, without super-deleting. Like all inserts do today. So the
>> above scenario becomes:
>>
>> * Session 1 physically inserts, and then checks for a conflict.
>>
>> * Session 2 physically inserts, and then checks for a conflict.
>>
>> * Session 1 sees session 2's conflicting TID. Session 1's XID is older, so
>> it "wins". It waits for session 2's token, without super-deleting.
>>
>> * Session 2 sees session 1's conflicting TID. It super deletes,
>> releases token, and sleeps on session 1's token.
>>
>> * Session 1 wakes up. It looks at session 2's tuple again and sees that it
>> was super-deleted. There are no further conflicts, so the insertion is
>> complete, and it releases the token.
>>
>> * Session 2 wakes up. It looks at session 1's tuple again and sees that it's
>> still there. It goes back to sleep, this time on session 2's XID.
>>
>> * Session 1 commits. Session 2 wakes up, sees that the tuple is still there,
>> and throws a "contraint violation" error.
>
> I think we're actually 100% in agreement, then. I just prefer to have
> the second last step (the check without a promise tuple visible to
> anyone made by the "loser") occur as part of the pre-check that
> happens anyway with ON CONFLICT IGNORE. Otherwise, you'll end up with
> some much more complicated control flow that has to care about not
> doing that twice for ON CONFLICT IGNORE...and for the benefit of what?
> For regular inserters, that we don't actually care about fixing
> unprincipled deadlocks for?

Right. That will allow me to review and test the locking part of the
patch separately.

- Heikki