> If Postgres would implement a shitty and
> inefficient version of an upsert statement at
> the very worst it could be as bad as the current
> implementation that people write on their own
Wow. please don't do that.
Many people do not understand why an upsert is a hard problem and would blindly use any solution presented without understanding it's limitations. At least with the current situation if a developer finds themselves in need of an upsert they are forced to seek out a solution and in the process understand why it's tricky.
As someone who does a lot with hand-coded upsert implementations, I would say that the problems are genuinely hard. I am somewhat on the sidelines. Not only are the problems hard, but the expected behavior is sometimes even difficult to define.
The real problem is that if the desired behavior is not nailed down entirely in the first implementation then what will happen when the operation is improved is that it will break stuff. That's I think the strongest argument for waiting.
OTOH, the concurrency issues are genuinely hard and there are a large number of applications for whom they don't really apply in the real world. It might seem to be nice to have a canonical syntax.
I guess on balance I agree with you but for very different reasons. Once you have an API, you have a contract.
Or blindly copy and paste the first solution they find on Google. Even after searching and trying to understand, it took me a while to really grasp the problems. In the end, I still walked away not knowing if the solution I used was actually alright (ie fast, no race conditions, etc).
I'd still argue that at least you are aware there may be an issue with your code. So at least if something comes up (duplicates, exceptions during high load upserts) you'll know where to look.
Will you remember though, six months later? What about when someone inherits the codebase from you, or you inherit a codebase from someone else containing a solution someone else implemented? You'll be stuck trying to diagnose and fix issues with very little guidance, because only a handful of other people may have implemented the same solution as you.
Having a single, canonical solution, even if it it's imperfect, is usually better than having a myriad of equally imperfect options. At least then documentation and community guidance can coalesce around it. Its caveats can be properly explored and explained, and when you do run into trouble and go looking for help, you're far more likely to find people who have had the same problems in the past.
Saying "feature x is hard, so we'll leave it to developers to figure out" is a disastrous mindset for any piece of software. The whole point of frameworks, runtime libraries, DBs, etc. is to provide developers with standardised, battle-tested infrastructure to do difficult things that require specialist knowledge outside of their area of expertise to get right. The argument that the hardest stuff should be left out, because it encourages developers to try and figure it out themselves is crazy: if the PostgreSQL developers can't work out how to implement upsert perfectly, then non-DB specialists are likely to do even worse.
I certainly wasn't saying the feature should not exist because it's hard.... merely, do it right or not at all.
The culture on the Postgres development side has always taken a very conservative approach, which carries with it much trust that the features that exist work very well.
Introducing a half-baked feature would break this trust. And so I would rather wait.
When you have a huge investment in your data, a conservative approach to database servers is full of merit. Once I began to understand concurrent design my years of MySQL experience were suddenly curdled. I had to switch to a server/community that care about integrity, and do not play fast and loose with new features. It's not a popularity contest.
And at least the version he's using will likely retain the subtleties of its behaviour over future postgres versions.
Depending on a "shitty & broken" implementation that goes into postgres which then subtly changes its behaviour after a few years could cause no end of problems.
> implementation that people write on their own
> and then at least, there is an established syntax
> and a way to improve it further.
That was the point.
Being aware of the problem doesn't mean you'll actually find a good solution. At least this way, any lingering problems will eventually be fixed instead of draining more of your own resources. Chances are, the Postgres team will be able to figure out the subtleties much better than you.
The more domain knowledge your system requires, the worse off everybody is. Here's a story.
At my previous company, I implemented something close to 'upsert'. After hours of researching, implementing, and testing in as many environment I could think of, I wondered why I wasted so many hours trying to get it right. Sure, I now understand the subtleties involved. (Well, I think so anyway. I still don't know what I don't know.) But am I better off now? Is the company? Not really. What we have is a tangled hack.
And worse, I no longer work with this company. So the next guy will have to figure out what's going on when there's an issue. To be safe, I spent another day re-organizing the code for clarity and documenting the complicated workaround. A scrappy syntax shipped today and fixed tomorrow would have been ideal. That way, the new guy could search for the symptom and see that that the current implementation of UPSERT is the cause. They can then use a recommended workaround, or simply upgrade Postgres to fix it entirely.
The problem though is that the corner cases of concurrency etc. with upserts need to be hammered out and agreed on at the beginning. Otherwise you will have "well, we improved concurrency handling in upserts and sorry, that broke your application."
Once you have a public API you have a contract, and that contract really shouldn't be broken without good reason. If PostgreSQL offered an upsert syntax with the same limitations as writeable CTE's, and promised to solve the concurrency issues in future releases, I wouldn't use it. I would rather have a hand-written implementation which is guaranteed to work in the future with the same caveats than an implementation I have no control over that may pose backwards-compatibility handling issues if I am supporting multiple Pg versions with my app.
Stability in API's is important. The idea that we will just throw something together now, and then fix it later will result in PostgreSQL getting a bad reputation.
It would also bloat the PostgreSQL codebase which crappy syntaxes which they may have to support for many years in the future.
The PostgreSQL team already merges features which are not yet perfect (see materialized views in 9.3, JSON in 9.2, and replication in 9.0 for examples), but they will not do that if they think they will have to break the API in a near future.
Not to mention that any expensive ("shitty and inefficient") database operation that has a user-facing endpoint increases surface area for DoS attacks.
agreed - I really want an upsert, but putting out a subpar implementation will just give upsert a bad name - then even if they fix it to work right you have months or years of google results telling you not to use it.
People hear, "PostgreSQL is like MySQL except that it is done right." They then go to PostgreSQL and say, "PostgreSQL does not implement my favorite MySQL feature in my favorite MySQL way."
Part of what makes PostgreSQL what it is is that they care so much about getting features right, and making them standards compliant. (Because if you do things differently than the standard, there is a chance that you will find yourself forever maintaining both the way that you did it and the way the standard said to do it.)
I like upserts. I understand the value and the functionality. But please don't add it until you can do it right. And please, please, please make it work like the standard says to. The standard is explicit, flexible, and a standard. Plus if you haven't impressed on the MySQL solution, it really isn't very hard to figure out how to do what you want to do. (I certainly didn't find it hard when I had to do it.)
2226 words and not a single mention of CREATE RULE. Postgres has had something far more general than "upserts" for over a decade.
CREATE RULE Pages_Upsert AS ON INSERT TO Pages
WHERE EXISTS (SELECT 1 from Pages P where NEW.Url = P.Url)
DO INSTEAD
UPDATE Pages SET LastCrawled = NOW(), Html = NEW.Html WHERE Url = NEW.Url;
1. Note that there are no plans for postgres to implement a specialised UPSERT. The plan was/is to implement MERGE, which is a fair bit more generic (especially with the 2008 and 2011 extensions). In fact, TFA very specifically disagrees with this route in his conclusion.
2. IIRC the rule method does not work correctly for multiple-rows inserts including duplicates
> 1. Note that there are no plans for postgres to implement a specialised UPSERT. The plan was/is to implement MERGE, which is a fair bit more generic (especially with the 2008 and 2011 extensions). In fact, TFA very specifically disagrees with this route in his conclusion.
Does the SQL standard actually guarantee that a MERGE is atomic? After all the MERGE statement involves a subselect which seems to be about as concurrency safe as a SQLite replace insert which a join.
If PostgreSQL goes with an implementation of MERGE like that then it would not be a replacement for UPSERT. With UPSERT you can make sure to never get any constraint violations.
There is a patch for specialized UPSERT which was being actively developed very recently. I would not be surprised if it makes it into PostgreSQL 9.5. On the other hand there is currently no working being done on MERGE.
"The reason for this is that transactions just serialize the execution, they don't guarantee any atomicity of independent row updates. After the delete happens the second transaction gets a chance to run and the update will fail because it no longer sees a row"
Umm - I thought everything in a transaction can be treated as atomic wrt to other transactions.. ie they don't see "in between" states?
"…it is possible for an updating command to see an inconsistent snapshot: it can see the effects of concurrent updating commands on the same rows it is trying to update, but it does not see effects of those commands on other rows in the database."
That only applies for the read committed isolation level. It seems to me that at least serializable should not show inconsistent state in this situation?
What does that have to do with serializable transactions?
"The Serializable isolation level provides the strictest transaction isolation. This level emulates serial transaction execution for all committed transactions; as if transactions had been executed one after another, serially, rather than concurrently."
If you don't get the right behavior with serializable transactions, as you seem to be claiming, it seems to me that serializable transactions should be considered buggy. In this case they do not provide the guarantees they are claimed to provide.
Yes, it is true for all databases which do not take table level locks. In PostgreSQL you can increase the isolation level to serializable and get an error when this happens instead of incorrect query results, not sure about MySQL.
It's actually that isolation that causes the problem. The issue is you have updates which dont see the existing inserted (and uncommitted) row and so the resulting inserts collide.
I understand that MERGE or something like that would be a nice convenience.
But if I personally had this situation what I would do is (in pseudo code, but think this would be in e. g. Python, or in a stored procedure):
begin transaction
does row with unique key x exist?
if exists: update all relevant fields
else: insert new row
commit
How does this have a different concurrency behavior than e. g. an equivalent Oracle MERGE statement or MySQL ON DUPLICATE KEY?
If another process tries to write to this row before the change is committed, it would have to wait (in any case).
If another process tries to read from this row before commit then what happens depends on the transaction isolation level and maybe even on implementation details. But I don't see how this is changed from having a built-in MERGE-like utility.
What happens is this: Concurrent transactions marked A and B:
A: Does row exist? Answer no
A: Ok, so insert row.
A: ....
B: Does row exist? Answer no (A has not committed yet)
B: Insert row (locks, waits to see if A commits or rolls back)
A: Commits
B: Unique constraint violation error.
Now here's where it gets really tricky: What is the desired behavior? Do we:
1. Just raise a constraint violation error? That's what we do with LedgerSMB because, frankly, unique constraints are far more likely to be violated by human error than concurrency issues and we can tackle other related errors elsewhere. This is specific to our app. Other apps may differ.
2. Should the upsert lock, wait, and retry if the the upsert criteria is violated by a pending transaction? That's the cleanest option. However, this requires being aware of which unique indexes are affected by the upsert criteria.
A quick and dirty upsert like yours is good enough for most applications based on human input. It is not good enough, IMO, for applications with lots of direct automation coming in from various sources. So those are very different use cases and they have very different criteria.
Thanks for this explanation. I would have expected #1 to happen in the case that you outline.
You're right, since a built in merge utility has more information about the semantical intent of the operation it can essentially repeat the complete operation whereas my naive implementation would not.
The UPSERT implementations in this article are simply wrong (he should call them "DELSERT" perhaps).
A simple Postgres function like the following works for me:
CREATE OR REPLACE FUNCTION upsert_my_table(key text, value text) RETURNS void
LANGUAGE plpgsql AS $$
BEGIN
UPDATE my_table SET value=$2 WHERE key=$1;
IF NOT FOUND THEN
INSERT INTO my_table (key,value) VALUES ($1,$2);
END IF;
-- add PERFORM pg_sleep(10) for concurrency testing
END
$$;
(according to my tests, it has no concurrent UPDATE problems ... duplicate keys are another matter and best handled with restarting transactions / savepoints)
> according to my tests, it has no concurrent UPDATE problems
You have concurrency updates with deletes as well as inserts. That's literally the worst way to implement upserts currently. Even CTE's are superior to that.
If you have high concurrent updates, you should be dealing with append-only database design and collapsing into a view to query, like Datomic. All the locking required for atomic upserts would wreak havoc on performance anyway no?
It depends. We use upserts for certain kinds of data in LedgerSMB despite the fact that a lot of data is append-only.
> If you have high concurrent updates, you should be dealing with append-only database design and collapsing into a view to query, like Datomic. All the locking required for atomic upserts would wreak havoc on performance anyway no?
There are many cases beyond high concurrent updates where you want an append-only design. We use a lot of append-only in LedgerSMB but that's for transparency/audit control reasons.
The problem is handling corner cases with upserts. The fact is that it is a useful tool in many areas. However you wouldn't use a chasing hammer to drive nails, would you?
> you should be dealing with append-only database design and collapsing into a view to query, like Datomic
True, but postgres is very bad at that. So you need to work with the tools that postgres gives you. Yes postgres currently does not give you an upsert but an upsert is much closer to postgres' design than an append only database that denormalizes on inserts.
Highly concurrent updates? By that I understand a workload where you have multiple clients updating the same rows.
In my experience it's either rare or the result of doing something inefficiently (e.g., doing analytics as a "view_count" column). Everytime I've faced that I've remodeled the database, both for performance (you avoid invalidating caches for the entire table) and auditing reasons (if you have a bunch of people replacing each other's changes, you might want to retain the history).
"Asking to redo the transaction" pushes complexity into the client. Armin did mention that: "Unfortunately this problem does not go away if you tell the client to solve it."
What you are suggesting is a loop where you try update/insert until it succeeds. Because there is no lock involved there is no guarantee that it will ever finish. That's exactly what's being mentioned in the blog as a workaround until an upsert exists.
It is guaranteed to return after either inserting or updating. Most implementors terminate the loop after some arbitrary number because an error elsewhere could prevent both insertion and deletion, and it is nice to get an exception rather than a hang in from your upsert.
> It is guaranteed to return after either inserting or updating. Most implementors terminate the loop after some arbitrary number because an error elsewhere could prevent both insertion and deletion, and it is nice to get an exception rather than a hang in from your upsert.
Which is why I said multiple times that this needs database support at which point a lock. That would guarantee that the contention at the lock is fair. With a loop around that section the winner is chosen by network latency and retry speed if high contention happens. Also there is not even a guarantee that it will ever finish.
> That would guarantee that the contention at the lock is fair.
It's not fair, it's a lock. Locking all the table for one row, is not fair. I don't know what you mean by fair.
> With a loop around that section the winner is chosen by network latency and retry speed if high contention happens.
Yes and? Anyway, this must be in the database, the database knows the transaction can pass, it doesn't need to send it back to the user, except if there is heavy writes on this row, which would mean the application is under attack or not correctly programmed.
> Also there is not even a guarantee that it will ever finish.
There is a MAX_RETRY, it is guaranteed to return.
---
Also:
- You don't state your problem upfront, just say you need this COMMAND. Which is forbidden by netiquette.
- You never state that it's a Write/Write transaction problem
- What about Read/Write?
- There is a solution, use a migration or MYSQL
He is incorrect about isolation levels, at least in PostgreSQL. Serializable with DELETE + INSERT will make a concurrent serialiable update fail with serialization error. I just tested this.
1=# CREATE TABLE my_table (key text primary key, value text not null);
1=# INSERT INTO my_table VALUES ('key', 'foo');
1=# BEGIN ISOLATION LEVEL SERIALIZABLE;
1=# DELETE FROM my_table WHERE key = 'key';
2=# BEGIN ISOLATION LEVEL SERIALIZABLE;
2=# UPDATE my_table SET value = 'bar';
1=# INSERT INTO my_table (key, value) VALUES ('key', 'value');
1=# COMMIT;
2 ERROR: could not serialize access due to concurrent update
Other isolation levels will make the concurrent update find zero rows. Serializable gives an error instead which you reliable can retry the transaction on.
They fail in different ways (for serializable a in this case more useful way) which should have been pointed out by the author. It does not seem like he actually tested what isolation levels does or he tested it in a database which does not provide serializable isolation.
I'm confused. Are we designing accounting software or high concurrency? Upsert is not magic, there are always trade offs.
If this is accounting software then use transactions.
If you're doing a research paper on accounting software then have fun, there are lots of trade offs to play with.
If you are designing for concurrency then drop your constraints and transactions and just make everything an insert. Then when you want to select just look for the first record from the bottom of the table.
No feature, be it upsert or whatever, can ever beat proper design. The best it can ever aspire to do is equal it.
The DBA in me is having a difficult time understanding PostgreSQL's behavior with regard to the delete/insert within a transaction?
In MySQL, I get the exact behavior that I expect with anything above RC isolation level (which is the default for PostgreSQL):
session 1> create table foo ( k varchar(10) not null, v varchar(10) not null ) ;
session 1> set transaction isolation level read committed;
session 2> set transaction isolation level read committed;
session 1> insert into foo (k,v) values ('a','b');
session 1> begin;
session 2> begin;
session 1> delete from foo where k = 'a';
session 2> delete from foo where k = 'a';
-- transaction hangs here as expected since session 1 has a lock on this record
session 1> insert into foo (k,v) values ('a','c');
session 1> commit;
-- session 2 executes the delete
session 2> insert into foo (k,v) values ('a','e');
session 2> commit;
session 2> select * from foo;
+---+---+
| k | v |
+---+---+
| a | e |
+---+---+
When I do this in PostgreSQL, I end up with 2 rows in the table, which seems fundamentally incorrect and to me implies that PostgreSQL's implementation of read committed is flawed and should be fixed. The transaction in session 2, at RC isolation, should see the most recently committed result, which is the single record from session 1. I'm not CJ Date, though so am very likely missing some subtlety.
PostgreSQL after the sequence above:
test=# select * from foo;
k | v
---+---
a | c
a | e
(2 rows)
What am I missing? Why is this considered correct behavior?
I'm not an expert, but as I understand it, both deletes see row ('a','b'). Session 2 sees that that row is locked so it waits, and when the lock is released it sees that it's already deleted. However, it doesn't redo the query so it never sees ('a','c'). As far as PostgresSQL is concerned, ('a','c') is a completely different row from ('a','b') and it was created too late to have any effect on session 2.
These transactions aren't serializable but you didn't ask for that.
To get intuitive behavior, a delete followed by an insert with the same primary key in the same transaction would have to be treated as a row update, holding onto a single row lock, rather than as two independent rows with different row locks.
This violates the promise of read committed. In RC, all transactions are supposed to see the most recently committed version of a record. The fact that it was a delete, then an insert is an implementation detail and "not my problem" as a DBA. At the time the transaction in session 1 commits, the most recently committed version of k='a' is v='c'. PostgreSQL, in the situation, seems to be operating in read uncommitted isolation level...session 2 is treating the uncommitted delete in session 1 as the most recent version.
I'd like to give PostgreSQL the benefit of the doubt here, but this behavior seems fundamentally incorrect to me.
That's the subtle part: they aren't two versions of the same record. They're two entirely different records, so all bets are off. (In your example, they don't even have the same primary key because no primary key is declared. PostgresSQL doesn't know that k is supposed to be a key; it's just another column.)
That is an implementation problem. The behavior is still incorrect. MySQL manages to do this correctly, so it's clearly not impossible. I can't come up with any other conclusion except that transaction isolation in pg is fundamentally broken when I see this type of case show up.
Before i know about
"on duplicate key update"
i used to first run an update check the results on how many rows where matched then insert if there was none.
Another way i remember doing was having a unique key and inserting if an exception was thrown the update statement would run update.
Both where slow as hell. Du to the back and forwards stuff required.
In presence of foreign key constraints for example, DELETE+INSERT might break/fail when UPDATE+INSERT will not. Also, the performance characteristics might be different.
I agree. DELETING when you want to do an UPDATE or INSERT just seems wrong on all levels. DELETING is dangerous and I tend to prefer slightly slower performance of UPSERT with UPDATE/INSERT than involving a delete in the operation.
Why doesn't taking a row lock instead of a table lock fix this? Every transaction that wants to read/write to that row id blocks on that lock but you're not blocking the whole table.
You mean that with current semantics if you do a "delete from table where id=X" and X doesn't exist you don't get a lock on that id? So why not just change that or add a "lock my_table in id=X" syntax. Isn't what's needed here fine grained locking by row id?
You are correct, and the solutions I have read are based around locking the index entry when doing an upsert. The question is how it is best done. Simplified explanations: one solution is to insert a row and lock the new row, another solution is to lock the index entry itself.
Thanks for the answer. I'm sure there's plenty of complexity I'm missing here. It just seems like having an ID-indexed set of mutexes shouldn't be too hard. Does anyone have a good link discussing the design tradeoffs for a performant upsert?
I'm a bit confused - can anyone clarify what is the problem in TFA example
begin;
delete from my_table where key='key';
insert into my_table (key, value) values ('key', 'value');
commit;
? As far as I understand any outside transaction would either see the old value (assuming one existed) or the new value, not the middle result where the row doesn't exist - am I mistaken and this doesn't work that way in Postgresql?
I think his point is quite exactly that a concurrent write transaction (starts after the first transaction starts but before it commits) that relies on the existence of that key/value pair will fail because of how Postgres works.
OK, I tried this on a random PG server I had and replicated the issue with a concurrent update; and running two such transactions in 'overlapping' way inserted two duplicate entries for the same key.
That is interesting - is the current behavior really considered WAD? I.e., shouldn't then the 'correct' solution be in fixing this behavior instead of extending syntax for 'upserts'?
I'm sorry, I only just started learning SQL a few days ago... But I was building a simple web application and I wanted to add a user if the email didn't exist else update that user's fields. And I came across this article: http://www.the-art-of-web.com/sql/upsert/
im not a postgres guy so I don't know how possible this is - couldn't the pythonic "forgiveness not permission" pattern work here?
That is, a proc that just attempts the insert, handles the unique or primary key constraint violation error, and then proceeds to the update as a fallback? Sure you push the concurrency risk to deletion instead of creation, but deletions are generally more rare than creations so a table-lock on deletion is more palatable.
This is a common trouble. It is, however, a structural artifact of data size. You cannot solve this problem with a "code" solution because it is an actual computational complexity issue, not a implementation weakness.
This is why data design is with performance and security as an attribute that you must bake in. You cannot bolt on data design after the fact.
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.
Doesn'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.
If 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.
The combination of postgres's range types[1] and exclusion constraints[2] allow you to do this safely. My 'primary key' constraints look something like:
ALTER TABLE foo ADD CONSTRAINT temporal_pk EXCLUDE USING GIST (
identity_id WITH =,
valid_range WITH &&
);
It'd be nice if temporal foreign keys were supported too, where the existence of contiguous child rows through the valid_range of the parent was enforced, but I've had to use a trigger for that.
I 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.
Sure it does. You need to ensure that you only have one non-invalidated record. If you don't have partial unique index, you can't guarantee that two concurrent transactions won't invalidate the same row and add two different rows. With a partial unique index, you can make that guarantee.
The main take-away from my original comment should be "never destroy data". You may write new data, but you may never delete or overwrite data. How you design the database after that is up to you. The particular example I used is of course not going to be good for enforcing, say, uniqueness of email addresses across users.
It has tended to make me really consider the notion of uniqueness within a database. What does the unique constraint mean? I think that people think of uniqueness to mean "at this point in time", but if you enforce it in the database, it means "for all time." In relational algebra, the unique constraint is really about enforcing a candidate key, a field that could satisfactorily be used as a key, but we've so happened to have chosen a different field as the actual primary key. In that context, given that we would want someone to be able to change their email address, I don't think "unique" email addresses make sense.
So many things change over time and so many seemingly "unique" fields turn out to not be unique in the real world that, for the most part, I've given up on the unique constraint. Full names are neither unique, nor are they final (people get married and change their name). Not even Social Security Numbers are necessarily unique, for a variety of reasons (http://www.computerworld.com/s/article/300161/Not_So_Unique).
That's what partial unique indexes allow you to put in the database. take this example which would work in PostgreSQL:
CREATE UNIQUE INDEX profile_username_valid_idx_u
ON profile(username)
WHERE invalidated_at IS NULL;
The problem with enforcing this in app-space is it doesn't get around the isolation problem. In both cases, you are assuming that the same profile won't get concurrently updated twice by two different transactions. Since only the database can referee this issue, you must enforce the uniqueness there. I.e. this check must happen on transaction commit and your application has no way of knowing if it did.
Yes, exactly. It is basically a time machine. A close reading of some federal compliance laws basically requires either this sort of history tracking or caching of every report ever ran, as the auditing requirements often stipulate needing to be able to "see how the report appeared at the time it was generated".
Yes! It was one of the most satisfying moments of my career. The person had been falsifying documents to say they were distributing samples of drugs to doctors, but we're actually selling them on the black market. It taught me a valuable lesson about how seemingly innocuous software can have an impact in the world and that every project deserves competent engineers.
Yeah I was just referencing the standard pattern for doing this, I didn't intend it to be complete code. Using a table that stores a new copy of the item for each "update" and having a convenience view that shows the "current" one is pretty standard, and I don't get how it doesn't accomplish his goal.
In the user profile example, you'd only get one profile entry for one user that way.
And I don't see what is so complex about it. It's meaning is explicit and clear. The difference from UPSERT is in the requirements. Any notion of an UPSERT would fail to meet my requirements, requirements I have far more often than I don't.
Many people do not understand why an upsert is a hard problem and would blindly use any solution presented without understanding it's limitations. At least with the current situation if a developer finds themselves in need of an upsert they are forced to seek out a solution and in the process understand why it's tricky.