3 ms·

I don't understand why INSERT ... ON CONFLICT ... DO is even a thing. Yesterday there was a item on a piece by rachelbythebay about checking for and dealing w

I don't understand why INSERT ... ON CONFLICT ... DO is even a thing. Yesterday there was a item on a piece by rachelbythebay about checking for and dealing with unexpected conditions in your code as a practice that separated professionals from ... er, idiots. This would appear to be a prime example of that antipattern -- in general, not just in SQL Server. If a tuple snuck into your relation while you weren't looking, don't you need to rollback and find out the current state of things? edit: Rachel's piece: https://rachelbythebay.com/w/2020/09/02/check/ https://rachelbythebay.com/w/2020/09/02/check/

manigandham 6y ago
What do you mean by "you weren't looking"? The SQL statement which is doing what you tell it to do, when there's a conflict.
- tuatoru 6y ago
  I'm asking, why is there a conflict? Why don't you know whether or not that tuple is in the relation before you start? In OLTP, this practice (UPSERT) would cause all sorts of mayhem.
  - Ancapistani 6y ago
    > Why don't you know whether or not that tuple is in the relation before you start? ... because that requires a redundant query, its associate lookup cost, and in order to be sure that tuple wasn't added or removed between the time your check and the insert/update executes, either a write lock on the table or on the row itself (if it exists). "Blind" inserts are the right way to go IMO.
  - icedchai 6y ago
    In practice, these conflicts can happen quite often during data synchronization. Say someone uploads a list of inventory, keyed by ITEM_ID. You want all your items to be either added (new) or modified (previously existing.) You could loop through them one-by-one, or you can just let the database handle it with a bulk upsert.
  - masklinn 6y ago
    > Why don't you know whether or not that tuple is in the relation before you start? Because you've got a unique constraint on some sort of natural key and the service this data is fed from doesn't have any guarantee it'll only deliver the information once. Or the feed can be replayed in the normal course of operation (e.g. system imports / synchronisations). Handrolling your upsert creates extra failure modes aplenty and is very tricky to get right, which is why built-in support was ultimately introduced. > In OLTP, this practice (UPSERT) would cause all sorts of mayhem. So?
  - manigandham 6y ago
    Does every application (and process) using your database automatically know what's in every table at every moment? That's highly unlikely, so you still have to do queries to find out. And then what would you do if the tuple was or wasn't in there already? Whatever your answer is, UPSERT just combines that into a single more efficient statement. There's no new logic, just a better expression of it. If you want examples: 1) analytics with `key(id,timestamp)` to avoid manually creating new rows as time goes on. 2) OLTP updates where we only want the latest data. 3) new data from multiple sources concurrently and we want the last write. 4) duplicate data and we want a single copy for each key.
NegativeLatency 6y ago
You can sometimes get better performance or be easier on the db itself if you have some records that you want to update in bulk. `ON CONFLICT` gives you a way to tell the db how to handle conflicts (duplicates/collisions/errors) without having to write potentially slower/buggier application code to handle the situation.
samatman 6y ago
A better way to look at it, imho: you can check for the unexpected condition in the client, or in the database. With ON CONFLICT .. DO, it's in the database, which has advantages sometimes: it's typical to have multiple clients, and one database. Even better, you can treat conflict as an expected condition, and specify what to DO when it arises. An example in SQLite which I happened to be working on this week: I have a table with a (foreign key, ordinal) tuple as the primary key of the table. Bad things happen to the client code if there are duplicate ordinals, but the row itself is mutable, so: on conflict, update. There's nothing left to check, this is simply a compact way for me to specify the correct behavior. The client can simply write new rows, without having to delete the old ones.
magicalhippo 6y ago
Our application has an article catalog. There's an article number (primary key), along with description, classification codes, weights and other things, in addition to specific stuff for our application. Our users have integrations which can update the catalogs "common" data. Descriptions can change, classification codes can change etc. A few users have several million articles, and they simply hand us a dump of them each night. I tried initially handling this client side, load the articles, check if each article already existed, if so update else insert. If no changes don't do anything. I quickly discovered it used 4-5 hours each night when one of these big catalog changes started rolling in. Then I rewrote it to issuing "insert ... on conflict" and just overwrote everything. Now it uses 15-20 minutes tops.

manigandham 6y ago

What do you mean by "you weren't looking"? The SQL statement which is doing what you tell it to do, when there's a conflict.

tuatoru 6y ago
I'm asking, why is there a conflict? Why don't you know whether or not that tuple is in the relation before you start? In OLTP, this practice (UPSERT) would cause all sorts of mayhem.
- Ancapistani 6y ago
  > Why don't you know whether or not that tuple is in the relation before you start? ... because that requires a redundant query, its associate lookup cost, and in order to be sure that tuple wasn't added or removed between the time your check and the insert/update executes, either a write lock on the table or on the row itself (if it exists). "Blind" inserts are the right way to go IMO.
- icedchai 6y ago
  In practice, these conflicts can happen quite often during data synchronization. Say someone uploads a list of inventory, keyed by ITEM_ID. You want all your items to be either added (new) or modified (previously existing.) You could loop through them one-by-one, or you can just let the database handle it with a bulk upsert.
- masklinn 6y ago
  > Why don't you know whether or not that tuple is in the relation before you start? Because you've got a unique constraint on some sort of natural key and the service this data is fed from doesn't have any guarantee it'll only deliver the information once. Or the feed can be replayed in the normal course of operation (e.g. system imports / synchronisations). Handrolling your upsert creates extra failure modes aplenty and is very tricky to get right, which is why built-in support was ultimately introduced. > In OLTP, this practice (UPSERT) would cause all sorts of mayhem. So?
- manigandham 6y ago
  Does every application (and process) using your database automatically know what's in every table at every moment? That's highly unlikely, so you still have to do queries to find out. And then what would you do if the tuple was or wasn't in there already? Whatever your answer is, UPSERT just combines that into a single more efficient statement. There's no new logic, just a better expression of it. If you want examples: 1) analytics with `key(id,timestamp)` to avoid manually creating new rows as time goes on. 2) OLTP updates where we only want the latest data. 3) new data from multiple sources concurrently and we want the last write. 4) duplicate data and we want a single copy for each key.

NegativeLatency 6y ago

You can sometimes get better performance or be easier on the db itself if you have some records that you want to update in bulk. `ON CONFLICT` gives you a way to tell the db how to handle conflicts (duplicates/collisions/errors) without having to write potentially slower/buggier application code to handle the situation.

samatman 6y ago

A better way to look at it, imho: you can check for the unexpected condition in the client, or in the database. With ON CONFLICT .. DO, it's in the database, which has advantages sometimes: it's typical to have multiple clients, and one database. Even better, you can treat conflict as an expected condition, and specify what to DO when it arises. An example in SQLite which I happened to be working on this week: I have a table with a (foreign key, ordinal) tuple as the primary key of the table. Bad things happen to the client code if there are duplicate ordinals, but the row itself is mutable, so: on conflict, update. There's nothing left to check, this is simply a compact way for me to specify the correct behavior. The client can simply write new rows, without having to delete the old ones.

magicalhippo 6y ago

Our application has an article catalog. There's an article number (primary key), along with description, classification codes, weights and other things, in addition to specific stuff for our application. Our users have integrations which can update the catalogs "common" data. Descriptions can change, classification codes can change etc. A few users have several million articles, and they simply hand us a dump of them each night. I tried initially handling this client side, load the articles, check if each article already existed, if so update else insert. If no changes don't do anything. I quickly discovered it used 4-5 hours each night when one of these big catalog changes started rolling in. Then I rewrote it to issuing "insert ... on conflict" and just overwrote everything. Now it uses 15-20 minutes tops.