Upsert in SQL
antonz.org
antonz.org
This is incorrect. To quote the Postgres MERGE docs:
"When MERGE is run concurrently with other commands that modify the target table, the usual transaction isolation rules apply; see Section 13.2 for an explanation on the behavior at each isolation level. You may also wish to consider using INSERT ... ON CONFLICT as an alternative statement which offers the ability to run an UPDATE if a concurrent INSERT occurs. There are a variety of differences and restrictions between the two statement types and they are not interchangeable."
MERGE can give duplicate violation errors when concurrent inserts take place (at the default READ COMMITTED isolation level). MERGE is no more capable of avoiding these sorts of race conditions than totally naively application code. In short, upserting is precisely what you're not supposed to use MERGE for -- ON CONFLICT is expressly designed for that use case.
Generally, stay away from MERGE. At least in SQL Server.
https://en.wikipedia.org/wiki/Merge_(SQL)
No question, everyone should use standardized, cross-platform syntax if at all possible.
Maybe someday ANSI will standardize some kind of upsert syntax; until that time use upsert only if you are explicitly tying your SQL to your particular database.
There are big questionmarks around this statement. SQL isn't an interchange format. Standard table design goes a long way because data has lots of different consumers and needs frequently to be moved around between systems, standard SQL is nothing useful to aim for. Everyone already uses DB-specific drivers and syntax because it is better than the alternative.
- The standard is not generally available. Most of us will never learn what is in it.
- SQL standardises terrible syntax. The SQL standards committee has a unique view on programming where "function_name(a, b, c);" is some sort of weird alternative syntax. They haven't gotten as far as working out variables or function composition either [0].
- Tying SQL to your specific database is the best option for performance. Writing database-independent SQL is somethign of a fools errand, it is either trivial performance-insensitive enough that you should be using a code generator or complex enough to deserve DB-specific turning.
[0] I assume. Not going to pay for a copy of the standard to check.
It also not aimed at users, but at implementors. Funny enough, they don't read the standard either ;) But more seriously: Some implementations are old and generally vendors prefer not changing the behavior of their product. When the standard comes later, the train has already departed. The most critical incompatibilities are in the elder features. The newer ones have a tendency the be more aligned with standard behavior (e.g. window functions are typically implemented just fine).
> They haven't gotten as far as working out variables or function composition either [0].
Part 2 SQL[0] is declarative and intentionally doesn't have variables. Part 4 SQL (aka "pl" SQL) does have variables. I personally consider Part 4 obsolete on systems that have sufficient modern Part 2 support.
> Tying SQL to your specific database is the best option for performance. Writing database-independent SQL is somethign of a fools errand, it is either trivial performance-insensitive enough that you should be using a code generator or complex enough to deserve DB-specific turning.
While this is certainly true for some cases there are also plenty of examples where the standard SQL is more concise and less error prone than product-specific alternatives. E.g. there COALESCE is the way to go rather than isnull, ifnull, nvl, or the like (typically limited to two arguments, sometimes strange treatment of different types).
There is a lot of *unnecessary* vendor-lock in in the field.
The issue I have with that is that making a bad decision for a reason doesn't change the fundamental correctness of the decision.
SQL semantics clearly support variables, because they are available as WITH ... clauses and subqueries. So either these elements are a mistake and should be discouraged, or the language has variables and they should be made to read like variables in almost all other languages for consistency and readability.
This is literally a cosmetic issue but a lot of thought on syntax has happened since the 1970s and there seems to be an overwhelming on-the-ground consensus that SQL's syntax is not the way to lay code out. The semantics? Great. But there is nearly nothing from SQL's syntax choices that is present in any surviving programming language since. Many of the more popular languages explicitly reject SQL syntax decisions like Python (significant whitespace), "function(arg,arg,arg)" instead of constructs like "VERB arg,arg WITH TEXT,arg TEXT arg" (everything I've met bar lisp) for functions and not replicating broken English (everything).
Variables are a different thing because the declarative nature of SQL is a useful property. But standard SQL overall does such an appalling job of handling syntax I feel confident that they have gotten this part of the syntax wrong too.
Gotten a bit off topic, just happens to be a thing I feel strongly about.
That's not the kind of thing that the standard takes a position on, for a variety of reasons. In general, it says very little about concurrency control/isolation. And what little is does say is all very abstract.
For whatever reason, a lot of people believe that MERGE promises something over and above what using multiple INSERT, UPDATE, and DELETE statements will promise. But that simply isn't true. The standard doesn't say anything that even vaguely suggests otherwise. Same is true of both the SQL Server MERGE docs, and the Oracle MERGE docs.
Anyone using MSSQL should read it.
There I also look at limitations of some implementations and problems such as not reporting ambiguous column names — just guessing what you mean ;)
https://michaeljswart.com/2021/08/what-to-avoid-if-you-want-...
That's how I got an improved query performance for syncing data.
writer A: begin xact, update, see 0 rows updated
writer B: begin xact, insert same thing, end xact
writer A: insert, end xact
Im those cases, one needs “delete-then-upsert” or “soft-delete-then-upsert” in your upset/merge implementation. I was a tad disappointed to see this only peripherally alluded to.
With some flavor of merge implementations you can do this with a pair of merge calls but in my opinion, ideally it should be one merge operation and db vendors should wise up to this.
Per https://modern-sql.com/caniuse/merge only SQL Server and BigQuery currently support “when not matched by source”. But Snowflake lets you have multiple “when not matched” clauses with “and” expressions so perhaps you can do it on that. Redshift docs show examples of using two merge statements.
I didn't understand what you meant by this - you seem to be referencing a larger workflow (e.g. keeping 2 tables in sync), and I'm not understanding how is relevant to the "upsert" primitive. Can you give an example?
The analytic database is expected to maintain a list of all customers and their transactions going back years. The transactional system stores a more limited history for operational performance reasons.
You daily pull the list of customers from source transactional system and upsert/merge it into your analytic database. Over time the source transaction team removes old unused or mistakenly created customers.
If you use a pure upsert without awareness/handling of source-side deletes, your analytic list of customers grows without any way to indicate to reporting users which customers are still in your transaction system (or when they got deleted from the transactional system.) But various analytics and/or APIs from your analytic system need to exclude such no-longer-present customers…
“Why am I seeing these customers in analytics that aren’t in the source system?” “Can I just get the current customer list from the analytic system?” “Can your analytics component X quit transmitting to me these old/bogus customers I don’t see in the transactional system?” people will ask you…
I admit I have no visibility into the true history of Oracle UPSERT vs ANSI merge (2003) and their initial target audience or the audience for early implementations. A web search on my part turned up no clarity. But I have three reasons I don’t perceive upsert as as OLTP-not-OLAP feature:
1) I vaguely recall seeing documentation from Microsoft around 2008 when they first implemented merge (I was excited at the time, forgive me) that it was for analytic use cases.
2) Anecdotally I did telecom+web “OLTP” programming for many years without hearing about the upset concept, but heard about upserts in my first few weeks working in the analytic (ETL) space and it was clearly a fundamental operation there.
I suppose the upsert concept was also fundamental for web crud operations and ensuring idempotency but nobody called it upsert back when I was doing that — we just wrapped an insert and update in a transaction. I acknowledge this is an anecdotal claim but it is my Bayesian prior.
3) Merge is a bit notorious for wonky transactional behavior in corner cases… so I’m not confident OLTP was/is the target audience.
Additionally I am not sure why you suggest batch usage implies you wouldn’t upsert. That’s a bit bizarre to me. The operation for moving data from staging tables (for dimensional/attribute data) to main target tables often uses upsert logic of some form.
Given that MERGE has this "wonky transactional behaviour", I don't really see the point of using it at all. I guess it gives a more convenient syntax in some cases, but it seems like it will always be possible to replicate the functionality by something like separate INSERT/UPDATE operatiosn (optionally wrapped in a transaction). And OLAP use cases are unlikely to have lots of concurrent transactions that are liable to issues with this approach.
So basically, while you might want to UPSERT in an OLAP use case, you probably don't need watertight atomic UPSERTs, which are the feature that requires special database support.
Sequences, at least in the database products I'm familiar with, do not exhibit transactional behavior; I'm not sure if this is actually part of the standard, but if not, I at least expect this to be pretty common. Part of the reason for this is to avoid introducing a point of serialization in concurrent transaction processing. If we were to do what you're asking for, you can get quite a performance hit. Consider a long running transaction involving an insert into a table using an auto-incrementing key. Any other transaction that wanted to get an ID would have to wait for that long running transaction to finish to get a number from the sequence... the waiting transaction wouldn't know if the long running transaction was going to commit or rollback and so the sequence number would be undefined until the waiting transaction could get its own lock.
If we set forth the expectation that a sequence will only guarantee you that you'll get a unique number from within the range, but that any other desire like gapless numbering is out of scope for the sequence you can avoid that whole waiting game. Sure, you might have gaps as a result; if the long running transaction finally rolls back, you'd get a gap for the number it consumed.... but no one waited to find out if that was the case and all the records got unique IDs.
If you need to ascribe meaning beyond merely "unique identity" to such a record ID, it might be that an auto-sequencing is simply the wrong tool. I think the compromise and expectation that sequences, and extended functionality depending on sequence mechanics, are not transactional is a good one given the performance costs of ensuring gapless numbering whether you really need it or not.
postgres requests an id from the sequence for each row of the incoming data ahead of time since it doesn't know which rows are updates and which are inserts (although presumably this could be changed?). the sequence doesn't reset down for the unused so this can eat through it unexpectedly quickly if you have a table with a relatively large volume of updates.
also as a tip if you hit the max integer for the sequence and need space to implement a fundamental fix you can quickly change the sequence to start at -1 and go down. there's no issue with negative ids since they're also integers.
Not in any sort of general way - the sequence can be part of the unique key that you're conflicting on.
I somehow feel it's wasteful and feel a bit conflicted. I also realize that while it feels natural to somehow get a feeling for the row numbers and it can help with debugging because you somewhat know some IDs / ranges after a while it's probably more something I just got used to and maybe shouldn't rely on.
I'm not sure how to go about making an official feature/change request though.
Personally, I work with MSSQL which has skipped adoption of UPSERT syntax and supported MERGE statements for probably 15 years now. While there's a certain degree of complexity in a MERGE statement, there's also a strong level of explicitness that is helpful in making sure certain conditions don't fall through the cracks or don't perform an operation that isn't expected.
The ability to handle different conditions in match statements is incredibly powerful and allows potentially complex ETL statements to be handled within a single merge statement. I'm not saying that's always the best practice, but if things are structured in the right way, it may be.
I believe the transaction isolation of a MERGE statement (in MSSQL at least) means that the lock on the rows is not released between the read and the write, which means you'll have fewer issues with reading data, attempting an update, then having it fail bc another process made a change in between the two calls.
I regularly use output tables to debug the results of a MERGE statement, and in this case the $action keyword is incredibly helpful for knowing what was done.
I wonder how often one has the ideal schemas for such. The advantage of an ETL is that it's probably easier to debug, log, and trouble-shoot problems. One-Giant-Do-All-Command is often a recipe for headaches. Being able to "X-Ray" the intermediate pipes really helps troubleshooting.
The X-ray issue is one reason I have trouble debugging functional programming. Maybe there's a way to X-ray better, but I haven't found it yet.
Based on https://dba.stackexchange.com/questions/187776/merge-deadloc... it seems that's still an issue as of 5 years ago, and not the recommended way of doing an upsert on MSSQL.
The "WHEN NOT MATCHED THEN DO NOTHING" is very appealing, and we currently implement this with a transaction and a DELETE after the upsert because we want to do updates only, not unintentionally insert new rows.
Huh??? Why not just do an UPDATE then? After all, you seem to be saying "we want to do an upsert but without the insert part".
Edit: I'm also trying to understand the value of "WHEN NOT MATCHED THEN DO NOTHING". How is that any different that just leaving off the "WHEN NOT MATCHED" clause altogether?
(Agree that the “WHEN NOT MATCHED DO NOTHING” is effectively the default behavior.)
Use case is the Outbound Prober processes for https://heiioncall.com/ which can do thousands of HTTP requests per second, then aggregate all those row updates into a single query (or single transaction, which in practice is a single query).
We can do these batched updates very, very fast using INSERT … ON CONFLICT. No need to re-parse a differently shaped query, only a single index rebuild. Smooth :)
I highly recommend the author’s book
I did not realize that PostgreSQL now supported MERGE
You have ton of interesting posts in your personal website!