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

On Mon, Sep 30, 2013 at 9:11 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> On Mon, Sep 30, 2013 at 3:45 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
>> If you think it's a bit odd that we lock every value while the user
>> essentially has one constraint in mind when writing their DML,
>> consider:
>
> I should add to that list:
>
> 4) Locking all the values at once is necessary for the behavior of the
> locking to be well-defined -- I feel we need to know that some exact
> tuple is to blame (according to our well defined ordering for checking
> unique indexes for conflicts) for at least one instant in time.
>
> Given that we need to be the first to change the row without anything
> being altered to it, this ought to be sufficient. If you think it's
> bad that some other session can come in and insert a tuple that would
> have caused us to decide differently (before *our* transaction commits
> but *after* we've inserted), now you're into blaming the *wrong* tuple
> in the future, and I can't get excited about that - we always prefer a
> tuple normally visible to our snapshot, but if forced to (if there is
> none) we just throw a serialization failure (where appropriate). So
> for read committed you can have no *principled* beef with this, but
> for serializable you're going to naturally prefer the
> currently-visible tuple generally (that's the only correct behavior
> there that won't error - there *better* be something visible).
>
> Besides, the way the user tacitly has to use the feature with one
> particular constraint in mind kind of implies that this cannot
> happen...

This patch is still marked as "Needs Review" in the CommitFest
application. There's no reviewer, but in fact Andres and I both spent
quite a lot of time providing design feedback (probably more than I
spent on any other CommitFest patch). I think it's clear that the
patch as submitted is not committable, so as far as the CommitFest
goes I'm going to mark it Returned with Feedback.

I think there are still some design considerations to work out here,
but honestly I'm not totally sure what the remaining points of
disagreement are. It would be nice to here the opinions of a few more
people on the concurrency issues, but beyond that I think that a lot
of this is going to boil down to whether the details of the value
locking can be made to seem palatable enough and sufficiently
low-overhead in the common case. I don't believe we can comment on
that in the abstract.

There's still some question in my mind as to what the semantics ought
to be. I do understand Peter's point that having to specify a
particular index would be grotty, but I'm not sure it invalidates my
point that having to work across multiple indexes could lead to
surprising results in some scenarios. I'm not going to stand here and
hold my breath, though: if that's the only thing that makes me nervous
about the final patch, I'll not object to it on that basis.

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