MERGE just landed for #postgresql 15.
I had this on my list of "I really hope it makes it, but it's a tough one" list from my talk at
I am very glad it made it! Thanks for keeping up to the finish line, and team!
Conversation
So what does MERGE do that you couldn’t do with INSERT … ON CONFLICT UPDATE?
3
You’re unable to view this Tweet because this account owner limits who can view their Tweets. Learn more
Right, but the downside is that MERGE's MVCC snapshot might not be able to see a concurrently inserted tuple, go on to insert (instead of say updating the other session's row), then get a unique violation. AFAICT you can't have generality of MERGE without accepting that downside.
3
3
Maybe we can solve that problem by allowing the INSERT in MERGE be an INSERT ON CONFLICT UPDATE
1
4
I briefly tried to picture MERGE with INSERT ON CONFLICT in my head .. I need to lie down now.
2
8
Right, that was my point: the restrictions in ON CONFLICT's syntax are not incidental to the concurrency issues it addresses. That was all very deliberate. Conditioning upsert behavior on "would-be duplicate violation" is weird, but seems necessary to get "upsert atomicity".