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

On Sat, Feb 21, 2015 at 11:15 AM, Heikki Linnakangas
<hlinnakangas(at)vmware(dot)com> wrote:
> Ah, ok, I can see the confusion now.

Cool.

>> Do we not wait on anything, and just declare that we're done? Then I
>> think that breaks exclusion constraint enforcement, because we need to
>> rescan the index to do that (i.e., "goto retry"). Do we wait on their
>> token, as my most recent revision does, but *without* a pre-check, for
>> regular inserters? Then I think that our old tuple could keep multiple
>> other sessions spinning indefinitely.
>
> 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?

Are we on the same page now?
--
Peter Geoghegan