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

Peter,

* Peter Geoghegan (pg(at)heroku(dot)com) wrote:
> Forgive me if I'm making a leap here, but it seems like what you're
> saying is that the semantics of upsert that one might naturally expect
> are *arguably* fundamentally impossible, because they entail
> potentially locking a row that isn't current to your snapshot, and you
> cannot throw a serialization failure at read committed. I respectfully
> suggest that that exact definition of upsert isn't a useful one,

I'm not sure I follow this completely- you're saying that a definition
of 'upsert' which includes having to lock rows which aren't in your
current snapshot (for reasons stated) isn't a useful one. Is the
implication that a useful definition of 'upsert' is that it *doesn't*
have to lock rows which aren't in your current snapshot, and if so, then
what would the semantics of that upsert look like?

> because other snapshot isolation/MVCC systems operating within the
> same constraints must have the same issues, and yet they manage to
> implement something that could be called upsert that people seem happy
> with.

This I am generally in agreement with, to the extent that 'upsert' is
something we really want and we should figure out a way to get there
from here, but it wouldn't be the first time that we worked out a
better solution than existing implementations. So, another '+1' from me
wrt your working this issue and please don't get too discouraged that
there's a lot of pressure to find a magic bullet- I think part of it is
exactly because everyone wants this and wants it to be better than
what's out there today.

Thanks,

Stephen