Re: INSERT...ON DUPLICATE KEY IGNORE

On 05/09/13 08:26, Robert Haas wrote:
> On Sat, Aug 31, 2013 at 2:34 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>> After some thinking I don't think any solution primarily based on
>> holding page level locks across other index operations is going to scale
>> ok.
> I'd like to chime in with a large +1 for this sentiment and pretty
> much everything else Andres said further downthread. The operations
> across which you're proposing to hold buffer locks seem at least an
> order of magnitude too complex to get away with something like that.
> Concurrent readers will block in a non-interruptible wait if they try
> to access a buffer, and that's a situation that will be intolerable
> if, for example, it can persist across a disk I/O. And I don't see
> any way to avoid that.
>
> One possible alternative to inserting promises into the index pages
> themselves might be to use some kind of heavyweight lock. The way
> that SIREAD locks work is not entirely dissimilar to what's needed
> here, I think. Of course, the performance implications of checking
> for lots of extra locks during inserts could be pretty bad, so you'd
> probably need some way of avoiding that in common cases, which I don't
> know exactly how to do, but maybe there's a way.
>
How about an 'Expensive bit' (of course, renamed to sound more
professional and to better indicate what it does!) - if the bit is set,
then do the expensive processing. This should have minimal impact for
the common case, so extensive checking would only be required when lots
of locks need to be checked.

I strongly suspect that the situation, is way more complicated, than I
imply above - but possibly, a more sophisticated version of the above
might help?