3 ms·

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.

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.