On 7/23/14 3:40 PM, Tom Lane wrote:
> John R Pierce <pierce@hogranch.com> writes:
>> On 7/23/2014 10:21 AM, Seamus Abshere wrote:
>>> Upsert is usually defined [1] in reference to a violating a unique key:
>>> Is this theoretically preferable to just looking for a row that
>>> matches certain criteria, updating it if found or inserting otherwise?
>
>> what happens when two connections do this more or less concurrently, in
>> transactions?
>
> For the OP's benefit --- the subtext John left unstated is that the
> unique-key mechanism has already solved the problem of preventing
> concurrent updates from creating duplicate keys. If we build a version of
> UPSERT that doesn't rely on a unique index then it'll need some entirely
> new mechanism to prevent concurrent key insertion. (And if you don't care
> about concurrent cases, you don't really need UPSERT ...)
hi all,
What if we treat atomicity as optional? You could have extremely
readable syntax like:
> -- no guarantees, no index required
> UPSERT age = 5 INTO dogs WHERE name = 'Jerry';
> -- optionally tell us how you want to deal with collision
> UPSERT age = 3 INTO dogs WHERE name = 'Jerry' ON DUPLICATE KEY KEEP NEWEST;
> UPSERT age = 5 INTO dogs WHERE name = 'Jerry' ON DUPLICATE KEY KEEP NEWEST;
> -- only **require** (by throwing an error) a unique index or a locked table for queries like
> UPSERT age = age+1 INTO dogs WHERE name = 'Jerry';
Obviously this flies in the face of what most people say the
"fundamental Upsert property" is [1]
> At READ COMMITTED isolation level, you should always get an atomic insert or update [1]
I just think there are a lot of non-concurrent bulk loading and
processing workflows that could benefit from the performance advantages
of upsert (one trip to database).
Best, thanks,
Seamus
[1] http://www.pgcon.org/2014/schedule/events/661.en.html
--
Seamus Abshere, SCEA
https://github.com/seamusabshere
