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

On 7/23/2014 1:45 PM, Seamus Abshere wrote:
>
> What if we treat atomicity as optional? You could have extremely
> readable syntax like:

atomicity is not and never will be optional in PostgreSQL.

>> -- 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 (connection
scans table, finds nothing, starts to insert, user 2 scans table, finds
nothing, starts to insert, poof, now we have two records?!?).   If name
*is* indexed and unique, this collision will cause a error at commit for
at least one of those connections.

--
john r pierce                                      37N 122W
somewhere on the middle of the left coast