5 ms·
> I haven't tested but I don't think that will happen on the default settings for postgres. I know for a fact that it won't on higher isolation levels. Unfortu
by anarazel 8y ago
> I haven't tested but I don't think that will happen on the default settings for postgres. I know for a fact that it won't on higher isolation levels.
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
(the number in brackets in the prompt is the backend pid, allowing to differentiate the two sessions).
> > 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.
- chacham15 8y agoI know very little about Postgres, but doesnt this appear to violate the isolation property of ACID?
- anarazel 8y agoNo. ACID doesn't guarantee that you can't have constraint violations triggered by a concurrent session. That'd make any sort of efficient constraint pretty much impossible. It guarantees that that error is caught despite the concurrency however.
- chacham15 8y agoAm I missing something? Wikipedia[1] says: "The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed sequentially." Oh, could the exception here be caused by the raw insert (without the where clause)? If you retried your example where both inserts were: INSERT INTO data SELECT 'alice' WHERE NOT EXISTS(SELECT * FROM data WHERE key = 'alice'); would that still cause the constraint error? If so, isnt that not equivalent to running the two queries sequentially hence violating the isolation property? Have I misunderstood something? [1]https://en.wikipedia.org/wiki/ACID https://en.wikipedia.org/wiki/ACID
- deleted 8y ago[deleted]
- imtringued 8y agoThink of it like git branching. 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.
- chacham15 8y agoAwesome! Thanks for the explanation! Sorry for the additional questions, but if we "rerun" the branch, shouldnt everything work as expected? (And in parallel, the answer to my above question is: yes, the raw insert is being rerun (after the insert where) which is causing the constraint violation; is that right?
- anarazel 8y ago
- eclark 8y agoUnique constraints have nothing to do with transactions. There's no promise that all columns that you run a transaction on will have a 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.