SQLite Query Language: upsert | Hacker News Reader

SQLite Query Language: upsert

sqlite.org

248 pointscoleifer70 comments

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).

> 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.

I 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?

I 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.

> 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.

I 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?

I 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.

Before 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.

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');

Yes, 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?

They 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.

You 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.

> 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.

well 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 ?

Thank you for posting this.

Except 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.

The update isn't inserting anything. Either the row exists already, or it doesn't. Maybe I'm missing something?

> 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

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.

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.

> 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.

I know very little about Postgres, but doesnt this appear to violate the isolation property of ACID?

No. 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.

Am 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

Think 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.

Awesome! 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?

What you're not supposed to allow is letting both succeed, because then the clients would assume everything went fine. If one errored before the commit, both transactions didn't execute successfully and you're fine. Pretty much every approach to isolation has a good chunk of errors that aren't strictly necessary (deadlocks, spurious serialization errors, ...).

Unique 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.

Usually for the serializable isolation level you need to manually rerun the transaction.

There are some DBs that do that for you like CockroachDB.

I believe the issue is a concurrent transaction (i.e. a separate one from the one you're currently in) may perform the INSERT and COMMIT that insert after your transaction's SELECT subquery returns nothing (thus allowing INSERT to proceed, by the WHERE clause), but before the actual INSERT itself is committed.

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.

In that case the other effort (I'm reluctant to call it a transaction) wins. Maybe it was later and should win, maybe it was earlier and shouldn't. It takes a lot more care than seen here to preserve a meaning of "earlier" and "later" that isn't vulnerable to this issue. The way the schema is designed, there can be only one "Alice". I'm trying and failing to imagine a way in which both this effort and the other effort can fail.

I think the particular point made by Andres (and elsewhere in this thread) is really not about what the schema for a phone book is like, and more that "Accurately replicating what UPSERT does without it is exceedingly hard and you will probably get it wrong or suboptimal in a number of scenarios you can't possibly imagine until they happen".

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".

Exactly this. It‘s so hard that it took PostgreSQL almost two decades to finally implement that feature and they had to support a lot of internal features first, like speculative insertion. If you want to do UPSERT 100% correctly and ACID, the list of edge cases just grows and grows.

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...

Im sorry, maybe im just inexperienced here, but doesnt what you're describing violate the isolation part of ACID? https://en.wikipedia.org/wiki/Isolation_(database_systems)

I'm not 100% familiar with the system, but depending on the transaction levels available you could do wrap it in like... a serializable transaction, then select the rows (just to lock them), then do the update, then do the insert, then commit the transaction

Maybe... I'm used to systems with upsert/merge.

But we are talking about sqlite, right? By default the database is locked once a write occurs, so there could not be concurrent transactions writing to the database.

You 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.

I 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.

Fyi, Expensify uses sqlite as it's primary database in a HA clustered way, see bedrockdb.com for more details.

It's been a while since I've used SQLite (over a decade ago), but didn't they already have this sort of functionality in there with an 'INSERT OR UPDATE' modification to the standard SQL INSERT command?

In fact, it always made me wonder why other systems (at the time) didn't support this critical and useful feature? I was getting tired of writing a SELECT, comparing the returned row against the one I was about to update, then branching to an UPDATE or INSERT command depending. Doing it all in one command was a breeze.

If I understand, the difference is that this allows insertion of only some columns, including the primary key. If there is an existing row, the union of all columns will be the result. On the other hand, `INSERT OR REPLACE` completely replaces the existing row, if one exists.

Thanks for the quick and informative explanation. I'll admit that I didn't have time to read the entire article right now, but I will go back and look at it in more detail.

Just a note: this feature is scheduled to be included in the next official release of SQLite (3.24.0). Additionally, for some time SQLite has supported INSERT OR REPLACE which offers a simpler, all-or-nothing upsert query.

The diagram used on this page [0], and the ones on the JSON site [1] are pretty awesome diagrams. What are they called? Is there a good programmatic tool to generate them?

[0] - https://www.sqlite.org/draft/images/syntax/upsert-clause.gif

[1] - https://www.json.org/

Often called "railroad diagram" [1] [2]

[1] https://en.wikipedia.org/wiki/Syntax_diagram

[2] https://stackoverflow.com/a/7143467/489590

I thought these are simple EBNF diagrams. As taught in British schools to 16 year olds

From the FAQ [1],

> (25) How are the syntax diagrams (a.k.a. "railroad" diagrams) for SQLite generated?

> The process is explained at http://wiki.tcl-lang.org/21708.

I looks like a TCL script is used [2].

[1]: https://www.sqlite.org/faq.html#q25

[2]: https://www.sqlite.org/docsrc/doc/tip/art/syntax/bubble-gene...

I don't know if they used it but mermaid is pretty great:

https://mermaidjs.github.io/

At an old job we had a mammoth awful database running on MSSQLSERVER and we at one point ran into concurrency using the MERGE statement in a high-traffic area of the database. It turns out MERGE isn’t transactional so in between the check to see if a row exists and the insert, a concurrent call would sometimes create the row and we would get a constraint violation (it was a unique index basically). Tracking down the source of the error was only the first of many horrors on the way to fixing the problem for good.

Fun times.

Did you put the merge in a transaction to resolve the issue?

I hope they build this in a way that returns the correct changed row count. Currently upserts in sqlite are a bit complicated because there is no feedback

Agreed that it should return the updated count automatically, but it's possible to get that amount by pairing it with a query for the rowid of that new record:

https://www.sqlite.org/c3ref/last_insert_rowid.html

And if it's a table with autoincrement primary key, try:

https://stackoverflow.com/a/2217015/223327

From a quick test SQLite seems to be doing the right thing. I tested inserting single rows, multiple rows, and restricting the conflict-resolution with a WHERE clause -- the "row count" always came out correct.

Cool. Just as a side note - I was first introduced to the upsert concept in kdb. Similar behaviour to the SQLite one..

I'm torn on whether always forcing an explicit SET for ON CONFLICT UPDATE is good practice. It would make for much cleaner code to just update every column with the supplied insert values (as an option). Or maybe I'm just lazy (heck that's why I like UPSERTs in the first place!)

INSERT OR REPLACE does this. http://www.sqlitetutorial.net/sqlite-replace-statement/

I actually started using Sqlite as my go to SQL db. Logic being, the products I make will never have more than a thousand people that pay me $1/day. For that reason, Sqlite suits me well and will serve my users too reliably.

At what level would you say SQLite doesn't scale?

I'd say it depends more on what you're doing -- if you're not doing a whole lot of writes, you could get really far. See the "websites" section of the sqlite When to Use article: https://www.sqlite.org/whentouse.html

@levelsio website hood maps was able to handle hundred thousands users on sqlite. For stuff you're charging for scale is not issue, as in you won't face that problem.

Excellent news that they are finally including this. I was using SQLite3 heavily a few years ago, and one of the only complaints I had was the lack of this feature.

For those who may be interested: I was curious about the difference between MERGE [1] and UPSERT, and why database engines seem to support a custom (nonstandard) UPSERT instead of the MERGE introduced in standard SQL in 2003, so I did some web research and came across a reasonable-sounding answer in a comment on the following article.

https://www.depesz.com/2012/06/10/why-is-upsert-so-complicat...

> As noted, MERGE is complicated and slow. If two different database servers implement it, then it is going to be implemented with different schematics [sic]. Oracle and MySQL already have different implementation’s, and to nobody’s surprise, MySQL’s implementation allows for risky behavior that does not protect the data. PostgreSQL users expect that any data put into the database will reliably come out of the database. So MERGE is slow and inconsistently implemented. The correct thing to do is make the application aware of if a record is new or existing. Any application making use of MERGE should open a bug to replace it with more predictable and faster logic using INSERT and UPDATE.

> If an application does use MERGE, it has to account for implementation specific behavior and not will not be portable, in a safe way, to other database servers. So it is not suitable for ORM’s or database independent applications.

> So what is a legitimate use case for MERGE where not knowing if a record is new or existing is not possible prior to the transaction? They only thing that I can think of is one-off scripts that do not have any concurrency. But if you design for that, then some misguided ORM for web applications is going to use your MERGE function and users are going to wonder about what happened to their data when concurrency concerns were ignored or not understood.

(I believe this comment is by Michael McLaughlin, the author of books on Oracle PL/SQL programming.)

Does anyone happen to know if this is still accurate today, or whether there's been any attempt to incorporate UPSERT support into standard SQL? I could imagine the SQL standard maintainers not wanting to offer UPSERT if MERGE is already a superset of its functionality; but a case could be made that, since MERGE's complexity has apparently discouraged adoption by implementations and users, it may be sensible to support UPSERT nonetheless. Is this a reasonable way to think about it?

I don't mean to imply that standardizing UPSERT into SQL is an especially important concern for anyone; this is merely a trail of thought that originated from wondering about why the UPSERT statement is nonstandard.

[1] https://en.wikipedia.org/wiki/Merge_(SQL)

> So what is a legitimate use case for MERGE where not knowing if a record is new or existing is not possible prior to the transaction?

I think he concentrates on "possible" where the interesting use case is "convenient". The are many cases where you want to go from at-least-once execution of idempotent tasks to a consistent view of data. In those cases it's irrelevant if the data existed before or not - you're likely doing an upsert with exactly the same data under exactly the same key and care only the resulting log of events.

MySQL named this action as "REPLACE" instead of UPSERT which isn't an english word.

MySQL has REPLACE INTO. SQLite has REPLACE INTO too.

MySQL has INSERT INTO ... ON DUPLICATE KEY UPDATE. This is the functionality that SQLite now has.

The difference between REPLACE and ON DUPLICATE KEY UPDATE is subtle but useful.

I'd love it the SQLite page wrapped content in a div of a reasonable max-width. Text running edge-to-edge is hard to read.

Anyone know if the site is hosted on GitHub?

Since sqlite uses fossil for its scm, I thought that fossil drove the web site. It does for fossil.

https://www.fossil-scm.org/index.html/doc/trunk/www/index.wi...

I've never understood this position - if you want sites to be no wider than a particular width, can't you just make your browser that width?

I usually have things set up such that my browser only takes up half of one of my monitors and the amount of websites that don't handle this properly is ridiculous, even Hacker News has a scroll bar at the bottom because the bar at the top is too wide.

That was somewhat tangential, sorry, but it's been really bugging me recently and I wanted to vent.

I do a lot of front end work and this is a pet peeve of mine. And I _still_ screw it up sometimes. For whatever reason, layout is still really hard to get bug-free.

CSS Grid will help a lot as it gains adoption, but if FlexBox's adoption is any indication, that won't be for some time.

I agree, although I also dislike text that's wrapped in a too narrow div with tiny text :) There are a couple of user styles here that look like what you're after if you've got the Stylish/Stylus extension installed:

https://userstyles.org/styles/browse?search_terms=sqlite

Reply on news.ycombinator.com