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

On 7/23/14 6:50 PM, David G Johnston wrote:
> seamusabshere wrote
>> On 7/23/14 6:03 PM, John R Pierce wrote:
>>> On 7/23/2014 1:45 PM, Seamus Abshere wrote:
>>>> What if we treat atomicity as optional?
>>> atomicity is not and never will be optional in PostgreSQL.
>> I'm wondering what a minimal definition of upsert could be - possibly
>> separating concurrency handling out as a (rigorously defined) option for
>> those who need it.
> Given we do not have native UPSERT I'm not sure where your question is
> coming from anyway. I'm not sure what the plans are for UPSERT at the
> moment but nothing prevents us from performing the UPSERT comparison on a
> non-uniqe set of columns.

hi David,

My argument lives and dies on the assumption that UPSERT would be useful
even if it was (when given with no options) just a macro for

> UPDATE db SET b = data WHERE a = key;
> IF NOT found THEN
> INSERT INTO db(a,b) VALUES (key, data);
> END IF;

Adding things like unique indexes would work like you would expect with
individual INSERTs or UPDATEs - your statement might raise an exception.
Then, going beyond, UPSERT would optionally support atomic "a = a+1"
stuff, special actions to take on duplicate keys, all the concurrency
stuff that people have been talking about.

IMO having such a complicated definition of what an upsert "must" be
makes it a unicorn when it could just be a sibling to INSERT and UPDATE.

Best,
Seamus

--
Seamus Abshere, SCEA
https://github.com/seamusabshere