Re: INSERT...ON DUPLICATE KEY IGNORE

On 2013-09-04 15:01:57 -0700, Peter Geoghegan wrote:
> On Wed, Sep 4, 2013 at 1:26 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> > 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.
> 
> Then I have some bad news for you - that's already quite possible.
> _bt_insertonpg() is called with the very same buffer exclusive locked,
> and is where we do btree page splits. The first thing that _bt_split
> does is this:
> 
> /* Acquire a new page to split into */
> rbuf = _bt_getbuf(rel, P_NEW, BT_WRITE);
> 
> (Obviously this may ultimately result in the storage manager extending
> the index relation).

I don't think that's an argument for much TBH. Those operations are way
much less heavyweight than the ones you're proposing to hold the pages
locked over and there actually is a forward guarantee. And it's very
hard to avoid locking a page exlusively once you've decided that you
need to split the page. You cannot just release the lock while you look
for a victim buffer.

> I think that Andres and I ought to re-frame this discussion a little
> bit. Right now, the presumption we seem to be making, perhaps without
> even realizing it, is this is about providing functionality equivalent
> to MySQL's INSERT IGNORE; insert tuples proposed for insertion where
> possible, otherwise do not. However, Andres and I, not to mention
> almost every Postgres user, are actually much more interested in
> something like INSERT...ON DUPLICATE KEY LOCK FOR UPDATE.

Yes, the promises approach gets more advantageous if you think about
UPSERT because most of the work will be paid of when the UPDATE occurs.

> Maybe this will enable Andres to refute my position that the buffer
> locking approach to speculative insertion/value locking may actually
> be acceptable.

Sorry to be harsh here, but I don't think I need to do that. I've
explained most of the reasons I see that that approach won't work out
and so far I don't see those refuted. And to me those issues seem to be
fatal for the approach. If you find a solution to the problems noted
uppon - great. So far it seems neither Robert nor me see how that is
possible, but that obviously doesn't mean it's impossible that you find
a way.
But why should I argue further until you proof me wrong (newer patch or
explaining changed algorithms)? If you don't think my arguments are
valid, well, I've brought those up I see as relevant and that's
it. Can't do much further.

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services