7 ms·
UPDATE phonebook SET phonenumber = 704-555-1212' WHERE name = 'Alice'; INSERT INTO phonebook(name, phonenumber) VALUES ('Alice','704-555-1212') WHERE NOT EXIST
by cobythedog 8y ago
UPDATE phonebook
SET phonenumber = 704-555-1212'
WHERE name = 'Alice';
INSERT INTO phonebook(name, phonenumber)
VALUES ('Alice','704-555-1212')
WHERE NOT EXISTS (SELECT 1 FROM phonebook WHERE name = 'Alice');
- jedberg 8y agoYes, but did you just do an update and an insert every time, or did you have some code that checked the return values and not run the extra unnecessary command?
- cobythedog 8y agoThey are run together every time. You could run a check but it would probably cost as much as the update, which does nothing when a record is not found. In PostgreSQL (for example) you could use a CTE and the RETURNING clause to prevent two statements from having to run. I don't think sqlite supports that. Current version of PostgreSQL makes that unnecessary as it has "upsert" as well.
- jedberg 8y agoYou are right. You've chosen to trade off network round trips for extra work in the database and slightly longer row locks while both commands run, which seems like a fair tradeoff.
- kodablah 8y ago> You could run a check but it would probably cost as much as the update, which does nothing when a record is not found. True if the generation of the insert doesn't have some heavier burdens on the caller side. Otherwise, start a txn, do the first, check if 0 updated, then do the second.
- merb 8y agowell even if you have an if after your update it's not what he basically meant. What he meant is that some people would've written the if/else with SQL and not with the language they written ?
- forgotmypw 8y agoThank you for posting this.
- deleted 8y ago[deleted]
- deleted 8y ago[deleted]
- anarazel 8y agoExcept that that's not concurrency safe in a transaction, because you might not yet see the row inside the NOT EXISTS() subquery if it hasn't yet committed.
- jessaustin 8y agoThe update isn't inserting anything. Either the row exists already, or it doesn't. Maybe I'm missing something?
- anarazel 8y ago> The update isn't inserting anything. Either the row exists already, or it doesn't. Maybe I'm missing something? 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> Edit: formatting
- eclark 8y agoI 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. https://www.postgresql.org/docs/9.1/static/transaction-iso.html 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.
- 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.
- drawkbox 8y agoYou could also COUNT on a query/filter to see if it exists by row count, then INSERT or UPDATE based on if there is an existing item for that filter/query. That is still two calls and can be safer, it can also be done in a transaction. Lots of app level code in ORMs that use ADD/EDIT or CREATE/UPDATE actions can encapsulate this by just a SET, which counts the rows and then if zero INSERTS, if > 0 UPDATE.
- schrodinger 8y agoI believe you need to repeat that update one more time, in case the row wasn’t present during the first update, but was created by another session immediately before the insert. Unless you’re in a transaction—then you’d be fine as posted above.