6 ms·
This is great! Given that SQLite is generally used for small projects and not in any sort of cluster or HA environment, pushing this kind of work (the if/then/
by jedberg 9y ago
This is great! Given that SQLite is generally used for small projects and not in any sort of cluster or HA environment, pushing this kind of work (the if/then/else block that you don't have to write in your code anymore) makes a ton of sense.
I actually like this feature more in SQLite than in Postgres. In Postgres I worry about pushing that kind of work into the database in clustered situations, as there are places where it can go wrong and it would be more likely that the code wouldn't be able to handle that failure properly (as opposed to having to write the if/then/else yourself, which if you're a good coder would also involve some try/catch and proper fallback behavior in case of an error).
- anarazel 9y ago> which if you're a good coder would also involve some try/catch and proper fallback behavior in case of an error It's actually really hard to do so correctly in the face of concurrency. Do not attempt to do so unless you've a really good reason.
- jedberg 9y agoI would say in this day of high scalability and microservices, a good engineer should be skilled at dealing with these types of errors, as they would be one of the most common types. Edit: I find it interesting that this comment has fluctuated between -2 and +2 points. Apparently saying that engineers should be able to handle concurrency errors is controversial?
- anarazel 9y agoI bet that at least 9/10 experienced engineers in the space will come up with a solution that has at least one of a) incorrect behaviour under concurrency b) unnecessary deadlocks c) extreme slowness d) break w/ triggers, row level security etc.
- anarazel 9y ago> Apparently saying that engineers should be able to handle concurrency errors is controversial? I didn't vote either way. But perhaps it's less the fact that they should be able to handle concurrency, and more that replicating the necessary non-trivial logic in several applications isn't a good plan.
- Normal_gaussian 9y agoI greatly enjoyed the addition of upsert to Postgres, but not in any high availability situation. Could elaborate on the way the failure modes of UPSERT aren't sufficient for HA?
- ams6110 9y agoI would trust postgres to manage concurrency better than I could write an if/then/else that did it properly in all cases. If you're writing if/then/else when dealing with a database, in my mind you're likely doing it wrong.
- jedberg 9y agoBefore upsert, how would you handle the examples in the article? Like, how would you "insert Alice's phone number unless it's already there, in which case update it". Edit: Downthread it was pointed out that you could just run an update and insert on every request and let one fail, which seems like a fair tradeoff for the extra network round trips.
- cobythedog 9y agoUPDATE 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 9y 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 9y 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 9y ago
- quinthar 9y agoFyi, Expensify uses sqlite as it's primary database in a HA clustered way, see bedrockdb.com for more details.