Re: INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

On Sun, Sep 22, 2013 at 1:39 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> I still fail to see how that's relevant. For every index there's two
> things that can happen:
> a) there's a conflicting tuple. In that case we can fail at that
> point/convert to an update. No Bloat.

Well, yes - if the conflict is in the first unique index you look at.

> b) there's no conflicting tuple. In that case we will insert a promise
> tuple.

Yeah, if there is no conflict relating to any of the tuples, the cost
is limited to updating the promise tuples in-place. Not exactly a
trivial additional cost even then though, because you have to
exclusive lock and WAL-log twice per index tuple.

> If there's no conflict in further indexes (i.e. we INSERT), the
> promise will converted to a plain tuple.

Sure.

> If there *is* a further
> conflict, you *still* need the new index tuple because by definition
> (the index changed) it cannot be an HOT update.

By definition? What do you mean? This isn't MySQL's REPLACE. This
feature is almost certainly going to tacitly require the user to write
the upsert SQL with a particular unique index in mind (to figure that
out for ourselves, we'd need to somehow ask/infer, which is ugly/very
hard to impossible). The UPDATE, as typically written, probably
*won't* actually update any of the other, incidentally
unique-constrained/value locked columns that we have to check in case
that's what the user really meant, and very probably not the
"interesting" column appearing in the UPDATE qual itself, so it
probably *will* be a HOT update.

> So you convert it as
> well. No Bloat.

Even if this is a practical possibility, which I doubt, the book
keeping sounds very messy and invasive indeed.

> Yes, I plan to reply to those, I just didn't have time to do so this
> weekend.

Great, thanks. I cannot strongly emphasize enough how I think that's
the way to frame all of this. So much so that I almost managed to
resist answering the above points. :-)

> There's other stuff than PG every now and then ;)

Hope you enjoyed the hike.

--
Peter Geoghegan