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

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.

>>> -- no guarantees, no index required
>>> UPSERT age = 5 INTO dogs WHERE name = 'Jerry';
>
> and if there's several rows with name='Jerry', you'd want to update them
> ALL ?  if name isn't indexed, this will, as Tom suggests, require a FULL
> table scan, and it still will have issues with concurrency

Ah, I was just saying, in terms of correctness, it seems to me that
upsert shouldn't NEED a index to work, just like you don't need an index
on "name" when you say WHERE name = 'Jerry' in SELECTs or INSERTS or
UPDATES.

Appreciate the defense of data integrity in any case!!

Best,
Seamus

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