Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 7/23/14 3:40 PM, Tom Lane wrote:
John R Pierce <pierce@xxxxxxxxxxxx> 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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux