A Case for Upserts

  > 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.

I hate this pattern.

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;

"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?

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.

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)

Isn't upsert a bad design smell though?

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?

This article is missing the solution brought by MVCC.

Namely, invalidating any query that comes after the update and asking to redo the transaction. This is done without lock.

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

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?

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.

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.

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'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/

Is that not an 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.

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.