Rows can exists without you being able to see them. Consider:
S1: BEGIN;
S2: BEGIN;
S1: INSERT name = 'Alice'
S2: UPDATE WHERE name = 'Alice' -> no row matches
S2: INSERT name = 'Alice' WHERE NOT EXISTS (name = 'Alice')
S2: <blocks, due to potential uniqueness violation>
S1: COMMIT;
S2: <resumes, raises uniqueness violation>
https://www.postgresql.org/docs/9.1/static/transaction-iso.h...
> If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row.
My understanding is that the update from S2 would be rerun applied.
Specifically postgres calls out:
> The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition.
Unfortunately you're wrong. I've now tested it, but I was also pretty confident before - I'm a postgres developer, and I worked on/comitted the PG upsert implementation ;)
postgres[10287][1]=# CREATE TABLE data(key text unique);
CREATE TABLE
postgres[10287][1]=# BEGIN ISOLATION LEVEL SERIALIZABLE ;
BEGIN
postgres[10284][1]=# BEGIN ISOLATION LEVEL SERIALIZABLE ;
BEGIN
postgres[10287][1]*=# INSERT INTO data VALUES('alice');
INSERT 0 1
postgres[10284][1]*=# INSERT INTO data SELECT 'alice' WHERE NOT EXISTS(SELECT * FROM data WHERE key = 'alice');
postgres[10287][1]*=# COMMIT;
COMMIT
postgres[10284][1]*=#
ERROR: 23505: duplicate key value violates unique constraint "data_key_key"
DETAIL: Key (key)=(alice) already exists.
SCHEMA NAME: public
TABLE NAME: data
CONSTRAINT NAME: data_key_key
LOCATION: _bt_check_unique, nbtinsert.c:535
> > If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row.
> My understanding is that the update from S2 would be rerun applied.
> Specifically postgres calls out:
> > The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition.
Those comments are about row-level locks - they're not the problem here. What you get is a constraint violation due to the unique constraint.
Try the same thing with no constraint. You should see the transaction fail but not because of a unique constraint. Otherwise there's something incorrect.
In the master branch there is no file "Alice". Two branches get created. One of them runs sed via find on all files with the name "Alice" and then it creates a file called "Alice" if find doesn't find any files called Alice.
The second branch creates a file called "Alice".
Both want to merge now but unfortunately there is a merge conflict. In this case automatic conflict resolution is impossible so one of the branches has to be discarded (transaction rollback). You can now "rerun" the branch and everything should work on the next merge.
Conclusion: "update + insert where" is not equivalent to "upsert". The former can cause a transaction to fail and must be retried on failure. With "upsert" the database engine has enough information to handle the unique constraint.
There are some DBs that do that for you like CockroachDB.
This means there is a window between the sub-SELECT and the INSERT itself where a secondary transaction can insert something, causing both your original update to do nothing, and the insert to fail. Thus you've lost the write entirely.
Isolation levels will for example have impact on how transactions proceed and what results you see (REPEATABLE READ vs READ COMMITTED); the error handling (did your transaction actually fail from a concurrent INSERT succeeding, or did the update fail, or did something else happen entirely? UPSERT is often not a standalone operation in a transaction) must be carefully decided for each use-case or else you're likely to redo needless transactions or throw away good ones, and you have to work around incidental problems like latency using stored procedures to keep everything server-side, etc etc.
It's just not the kind of thing you want to replicate a number of places at each use site in a number of tools. It's something only the database knows how to manage properly by having "global knowledge".
Here‘s a great article describing these problems in detail if you‘re interested: https://www.depesz.com/2012/06/10/why-is-upsert-so-complicat...