Re: INSERT...ON DUPLICATE KEY IGNORE

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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company