Quote Tweet
Replying to @adriandozsa and @Yugabyte
What about calling it INSERT ... IN CONFLICT?
asciinema.org/a/qnShTnDjmVO7
1
1
3
Conversation
Not really IMO, since ON CONFLICT offers strong guarantees about insert-or-update outcome (relevant in READ COMMITTED). The downside is that the syntax is constrained in order to do that job well. MERGE is a way of "combining statements" - offers no special concurrency guarantee.
Interresting. I never thought about this. Do you mean that they don't read the same snapshot?
1
1
Pretty much. *Any* unique violation could trivially violate snapshot consistency (at least in RC). In general you could easily get an error due to insert from an xact that wasn't visible to your own RC insert statement's snapshot, of course. "True upsert" is intrinsically messy.
2
It's no coincidence that statement is structured as INSERT ... ON CONFLICT DO UPDATE, rather than UPDATE ... WHEN MISSING DO INSERT. Needs an "arbiter constraint" (or unique index) to referee who conflicts with whom very precisely.
2