Re: INSERT...ON DUPLICATE KEY IGNORE

On Tue, Sep 3, 2013 at 12:52 AM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
> That'd certainly be the ideal, but as you say is far from simple, and
> IIRC prior discussions here have suggested the SSI / predicate locking
> stuff won't help.

I think that by far the strongest argument for what Andres suggested
(that has, incidentally, been suggested by a number of others), which
is to insert what he termed promise index tuples before a heap tuple,
is that there is no sensible way to lock the tuples while holding an
exclusive buffer lock on an index.

It could take basically any amount of time to acquire those locks (see
GetTupleForTrigger() to get a rough idea of how that ought to work),
and it would be totally unacceptable if that meant that lots of people
blocked on the page lock that an upserter held while it tried to
acquire locks on tuples (we could only unlock the buffer lock/locked
value when the rows were locked). So while I think what I've done here
is probably workable for the ON DUPLICATE KEY IGNORE case, perhaps I'm
being short-sighted in focusing on that. I'm surprised Andres didn't
call me out on this himself, actually.

--
Peter Geoghegan