4 ms·
I tend to think that UPSERT is the right solution to the wrong problem. Take the example of a user profile. The problem for me is not typically "the first time
by moron4hire 13y ago
I tend to think that UPSERT is the right solution to the wrong problem.
Take the example of a user profile. The problem for me is not typically "the first time the user shows up, I need to create a record, and all other times, I need to overwrite it." In the systems I build, there is almost always a use case for having full access to the history of data. So the better solution is, "I must ALWAYS update whatever record exists AND insert a new record".
That means my UserProfile table also has two extra columns, CreatedOn and InvalidatedOn. Then, creating/updating a user's email address is always the same, two statement operation:
-- If UserName does not yet exist, this does nothing
UPDATE UserProfile
SET InvalidatedOn = NOW()
WHERE UserName = ?
AND NOW() BETWEEN CreatedOn AND InvalidatedOn;
INSERT INTO UserProfile(UserName, Email, <etc.>, CreatedOn, InvalidatedOn)
VALUES(?, ?, <etc.>, NOW(), MAX_DATE());
In practice I would use default value constraints for the CreatedOn and InvalidatedOn columns. Writing it explicitly is just for this example.
This becomes really important once you've lived with your database for several months/years, and especially when dealing with customers. For example, say you send an important email to a user, then they change their email address, then you send more important emails referring to the first one, which they now claim they never received in the first place. With an UPSERT, that user's email address for all of time looks like the most current version, and you can't figure out why they didn't receive the first one.
It's better to capture more data and realize down the road that you don't need it than it is to not capture enough and realize you need more. Databases are temporal objects. They grow over time and they change over time.
Another example from my past: a client would send us a dump of their data, every morning. We were performing analysis of their data and were supposed to wipe out our database and re-import it every morning. This meant on day N+1 we were re-importing the first N days again. I implemented this as a temporal database, which caught me some hell when the database grew in size. But, after 6 months, we discovered someone had attempted to alter the historical data. If we had blindly taken the data, they would have gotten away with their crime (and it literally was a crime, we had several government regulations we had to fulfill, the project being used in both a health-care and a financial capacity).
I've never regretted implementing my tables temporally. I've sometimes changed the implementation to a data warehouse setup, after learning that the historical data wasn't necessarily important to day-to-day operation, but I've never regretted having the historical data available, and I have almost always learned to regret NOT having historical data.
- sirclueless 13y agoDoesn't this have the same problems as the DELETE+INSERT from OP? Namely, a concurrently executing transaction will see a brief moment in time where no valid (e.g. CreatedOn < NOW() < InvalidatedOn) UserProfile exists. There's also a bunch of weird corner cases you need to expect. For example, you can have multiple valid UserProfile objects at once if two transactions insert concurrently. If you LIMIT 1 and sort by CreatedOn that may not be a huge issue, but it will look weird if you try to look at historical data.
- moron4hire 13y agoIf you execute this in a transaction (as you should whenever dealing with any two queries that alter state and logically follow each other), then neither case creates a state in which some other transaction sees the first half of this sort of update complete without the second half. And if you don't, then it's not quite the same as DELETE+INSERT, because you haven't destroyed any data. Also, even if one considered this "no better" than DELETE+INSERT, it has significant other benefits for other purposes. The history of data is worth it alone. But you've made me consider the issue more, and I think perhaps a better way to run this would be: INSERT ... -- same as before DECLARE @ID int; SELECT @ID = SCOPE_IDENTITY(); UPDATE the_table SET InvalidatedOn = CASE WHEN the_table.ID = @ID THEN MAX_DATE() ELSE NOW() END WHERE surrogate_key = @KEY AND InvalidatedOn > NOW(); then, even if you intentionally created a race condition, you should always end up with one and only one "definitive" record.
- einhverfr 13y agoI think you'd need partial unique indexes to make that remotely concurrency safe.
- moron4hire 13y agoI don't think partial unique index would have an impact on concurrency safety. An appropriate one would be useful for making the query for the most recent value faster, but that doesn't mean it would be concurrency-safe without a transaction.