Re: Why is unique constraint needed for upsert? (treat atomicity as optional)

On 7/23/14 3:40 PM, Tom Lane wrote:
> John R Pierce <pierce(at)hogranch(dot)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