Re: INSERT...ON DUPLICATE KEY IGNORE

On Sat, Aug 31, 2013 at 2:34 PM, Andres Freund <andres@2ndquadrant.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?