Postgres gets support for upsert

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=168d5805e4c08bed7b95d351bf097cff7c07dd65

by anarazel • 11 years ago

1003 109 11 years ago

git.postgresql.org

ozgune• 11 years ago

So excited about upserts in Postgres!

This was the most requested feature in PostgreSQL: https://postgresql.uservoice.com/forums/21853-general

anilshanbhag• 11 years ago

This is actually huge. A common problem that arises when you write applications is you want to INSERT if key does not exist else UPDATE. The right way of doing this without an upsert is using a transaction. However this will make life easier as you can do it directly in one SQL statement.

ryan0x00• 11 years ago

i've seen a system in mysql do "insert on duplicate key update" for ages, so this is that same pattern?

girvo2• 11 years ago

Yeah, it is the same as INSERT ... ON DUPLICATE KEY UPDATE, and similar to REPLACE in MySQL. Exciting to see it in Postgres!

colanderman3• 11 years ago

Just "using a transaction" is insufficient. You must be prepared to handle the case that neither the INSERT nor the UPDATE succeeds (with READ COMMITTED isolation), or that the transaction fails (with REPEATABLE READ isolation or better), by repeating the transaction. And if latency is at all a concern to you, you must wrap this all in a stored procedure to avoid the necessary round-trip-time between the commands.

Hence this is more than just saving typing a couple lines -- this saves writing entire stupid loops to do what is conceptually a simple (and very common) operation.

Postgres gets better and better.

atombender2• 11 years ago

It's fairly trivial to do with a savepoint, but it's definitely not performant, and requires some pretty stupid client-side error-handling.

It will also cause your Postgres logs to be littered with benign but misleading error messages (possibly these could be silenced, I never bothered to find out).

So, yes, this is great news.

saurik• 11 years ago

...and then you additionally have to verify that the specific reason both the update and the insert failed was due to concurrency, or you can make the mistake I did a couple years ago where I had transactions spinning against my database for weeks on end due to an unrelated constraint failure I had failed to notice with some transactions that I kept retrying in a loop as part of my hacked together upsert implementation (which is why my user id numbers jumped from ~8m to ~460m... it is a meaningless mistake, but one I am reminded of every day due to that "46" staring at me :/).

NoMoreNicksLeft• 11 years ago

Why do you need to use a transaction for that?

Do the update statement, followed by insert-where-not-exists. If the update doesn't match, 0 rows updated, and the insert works. If the update matches, then 0 rows inserted.

anarazel7• 11 years ago

That's unfortunately not correct. The WHERE NOT EXISTS(...) will not "see" rows inserted by concurrently running transactions that have not committed yet.

andrewstuart2• 11 years ago

Seriously, IIRC the wiki page for this feature was like 5 years old. I'll have some statements I'll be very glad to update as soon as this is released.

I was so surprised when I ran into the lack of this feature as I'd had it on both MSSQL and MySQL.

DigitalSea• 11 years ago

Yet another reason why I absolutely love Postgres. It might have taken four years, but we finally got what many of us have been asking for. Nothing ruined my day more than having to write insert-update loops, I am beyond ecstatic for this.

dan15• 11 years ago

You love Postgres because it takes four years to get requested features?

jsmthrowaway• 11 years ago

Postgres turned a corner around the 9.0 era and has been churning out huge features for years. Indexes on jsonb completely changed the game for me.

As you can see from the commit, too, this was not a trivial feature. So yes, I'm overall pleased.

pilif4• 11 years ago

If given the choice between do it half-assed but quickly and do it right but using how ever much time is required to arrive there, the PostgreSQL team always choses the latter approach.

This conservatism works well for me considering we're talking about a database here.

One of the contributors has written an interesting article explaining why upsert is difficult to get right (if by "right" you want it to complete reasonably quickly and without any chance of corrupting your data):

http://www.depesz.com/2012/06/10/why-is-upsert-so-complicate...

colanderman3• 11 years ago

Because the devs pay attention, and take the time to do things right. See https://wiki.postgresql.org/wiki/UPSERT for a glimpse into the design that needed to go behind this.

I feel a lot of Postgres fans (myself included) put their "money" on Postgres circa late version 7 or early version 8, back when MySQL was the more featureful and performant of the two, while Postgres had the reputation for being more, shall we say, robust. (Remember, those were the days before InnoDB was the default in MySQL.)

The payout for investment in Postgres the past few years has been substantial -- native replication, true serializable transactions, foreign data wrappers, index-only scans, native JSON support, updatable views, and materialized views are among the features added to Postgres in the last 4 years. It's matured from being an "entry-level" RDBMS with few features that all work reliably, to a much more enterprise-friendly RDBMS with many features that still work reliably.

karmakaze• 11 years ago

Yes! it's about bloody time.

pbnjay• 11 years ago

I think I squealed with delight when I read this title. This is really a great addition, and will simplify so much of my code once I'm able to upgrade.

I try not to think too deeply about all the DB internals (some people are much smarter than me about this stuff), but clearly there are a lot of things that need maintained internally to make this correct, so kudos to all the people who contributed to this.

craigkerstiens• 11 years ago

A big +1 to this. Peter Geoghegan is the primary author and has been championing it for over 2 Postgres releases now. I know he's especially excited to see it land.

Also it couldn't have been possible without the support of Heikki and Andres (who actually submitted this to HN as his first ever submission).

anarazel7• 11 years ago

Hah, where from do you know that I don't have my sock puppet accounts ;)

olalonde• 11 years ago

Which version of Postgres will this land in?

amitlan2• 11 years ago

9.5, that is, upcoming!

elchief2• 11 years ago

Which will hopefully drop in September.

whistlerbrk• 11 years ago

For anyone who has ever had to write scripts to import incremental batch data, CSV, etc this seems huge.

nsxwolf• 11 years ago

I come from a SQL Server background and recently jumped over to a Postgres shop. It seems like I came in at a really exciting time.

I was really missing SQL Server's powerful MERGE statement, so this is welcome news.

silon3• 11 years ago

Does it handle locking (aka no deadlocks) correctly?

amitlan2• 11 years ago

There is a section in UPSERT wiki about this - https://wiki.postgresql.org/wiki/Value_locking#.22Unprincipl...

EDIT: rather the whole "Value Locking" page - https://wiki.postgresql.org/wiki/Value_locking

viraptor• 11 years ago

I love that it's there now. I've been waiting for it for a long time, but one thing I don't get is... why does every single implementation have to have their own slightly different syntax?

pgsql -> on conflict

mysql -> replace / on duplicate

oracle -> merge

mssql -> merge

sqlite -> insert or replace

firebird -> merge / update or insert

anarazel7• 11 years ago

MERGE is actually a separate feature. We're still considering implementing it. But it's syntax is too cumbersome for many usages. There's also some unrelated complexity - so implementing merge support does get easier by the infrastructure in here, but a fair amount of work remains.

ryanjshaw• 11 years ago

This seems MUCH better than MERGE for the common case.

I tried implementing some MERGE logic on MSSQL recently and at first it seemed great until I realised:

(a) maintaining/debugging a MERGE that uses most of its syntactical features is an absolute nightmare involving psychically debugging 20+ lines of opaque code - I ended up copying & pasting pieces of the statement into temporary tables/variables and confirming results that way, making me question the value of MERGE as the performance was similar to the original code, but less flexible (MERGE doesn't make available some of the values you need in some cases, so you need to do pre-mapping of data in those cases anyway, resulting in lots of similar code)

(b) it doesn't save you from needing to know and use the correct locks, something many people don't seem to realise!

I then discovered this page, listing all kinds of issues with the MERGE implementation (even on MS SQL 2014, years after MERGE had been introduced): http://www.mssqltips.com/sqlservertip/3074/use-caution-with-...

I realised that even if I followed my debugging approach in (a), one day I was going to run into a problem with MERGE that couldn't be replicated in decomposed statements because MERGE was doing 'something' else. And course, if you look at some of those issues, many of them suffer from the typical Microsoft "Won't fix and won't say why" attitude. In the end I decided to just keep the original/decomposed code, which was clearer and easier to work with.

MERGE is probably okay if you're staging data; I wouldn't use it for transactional processing. It is far too complicated. I really like that PG has focused on efficiently implementing the common use case and avoided the kitchen sink that is MERGE.

buremba• 11 years ago

It's great that you guys implemented UPSERT but I hope MERGE support won't be considered as insignificant because of this feature.

cstejerean• 11 years ago

Please add MERGE support. I use this in Oracle fairly regularly, mostly for merging two datasets rather than single row upsert. It would be really helpful to have this in Postgres as well.

colanderman3• 11 years ago

There's a great discussion here: https://wiki.postgresql.org/wiki/UPSERT#Syntax_discussion It basically boils down to that the different syntaxes mean slightly different things. The PG devs (IMO rightly) don't want to adopt an existing syntax that does something slightly different than what they are willing/able to provide, thus confusing users.

nolok• 11 years ago

I'm not very familiar with those specific features in other dbms, but in mysql's case "on duplicate" and "replace" are two very different thing. The first is an upsert, while the other drops the old row if it exists and always make a new one.

Both have different use cases.

daigoba66• 11 years ago

I'm a fan of MERGE in T-SQL. An "upsert" is just one use-case enabled by the statement. Granted for the relatively simple use-case of an "upsert", especially a single row, MERGE has incredibly cumbersome syntax.

But MERGE also, in my opinion, aligns better with set-based logic. I.e., I have two _sets_ of data that I want to merge together. In some cases I want need to INSERT rows into the target, in other cases I need to UPDATE, and in some cases I might DELETE. The <ouput_clause> in T-SQL is also quite useful.

CWuestefeld• 11 years ago

The T-SQL design is far more flexible and expressive. However, I don't think I've ever used it beyond the simple case.

And on the flip side, it's a lot more complicated to write, and difficult even to remember the syntax (at least so far).

taspeotis• 11 years ago

Who decided the syntax? Why not use MERGE ... INTO ... USING ... ON ... WHEN like Oracle and MSSQL and apparently ANSI SQL [1]? I'm not aware of ON CONFLICT being standard.

[1] http://en.wikipedia.org/wiki/Merge_(SQL)

veddan• 11 years ago

There's some extended discussion of the issue on the wiki[1]. A future full MERGE implementation might have slightly different semantics or performance characteristics than ON CONFLICT (which might also differ from other DBMS).

[1] https://wiki.postgresql.org/wiki/UPSERT#SQL_MERGE_syntax

smegel• 11 years ago

I remember about 10 years ago using UPSERT in a Teradata database to do delta-diff updating of tables.

Basically, I was updating a table from an external file, and I wanted to add what was new, leave what was the same, and delete any records no longer in the external table. Using UPSERT to insert new rows, update a timestamp on existing rows, and then afterwards delete any rows who's timestamp was before the UPSERT operation. Because of the nature of the file, doing an external patch/diff approach was difficult, but the UPSERT operation worked like a breeze!

petergeoghegan• 11 years ago

The documentation covering the new feature has now built: http://www.postgresql.org/docs/devel/static/sql-insert.html

electrotype• 11 years ago

Postgres's documentation is one of the best I've seen. Thanks to everybody working on it!

abannin• 11 years ago

I want to give a giant digital hug to all PG committers who made this happen.

donw• 11 years ago

Damnit, I just wrote a sweet gem that handles this... ah, okay, but it requires an index constraint, whereas I needed to merge on arbitrary conditions. So I didn't totally waste my time.

Way to go Postgres team!

loopdoend• 11 years ago

Is the gem open source? I checked your github and didn't see it, I think people would still find it useful!

kbendyk3• 11 years ago

I know it doesn't matter to some developers out there, but for me, the only thing left is the ability to add a column after another one. Even if it's only logical order, it's easier for me to read a table structure when fields are ordered nicely.

narsil• 11 years ago

Unsure what you mean. A subset of columns can be selected in any order, across tables. Were you referring to the select order used by "*"?

munro• 11 years ago

https://wiki.postgresql.org/wiki/Alter_column_position

pmontra• 11 years ago

Yes, I think it's about changing the default presentation order. That could be a feature of the client, psql, pgcli, pgadmin3, but it won't be portable across them unless it's some metadata stored inside the db. Is there any db with that feature?

Edit: munro answered that. PostgreSQL does it but it's expensive because it has to rewrite the table.

kbendyk3• 11 years ago

I know, but when I look at a table structure in PgAdmin, all newly added columns appear at the end, of course this can handled properly in select col2, col1... , or by creating a view, but by any other way as this wiki [1] page says. But during development, ability to create logical column order is somehow important to me.

[1]https://wiki.postgresql.org/wiki/Alter_column_position

leftnode• 11 years ago

Same thing here, I would love this feature. I like to make my tables have a common structure:

    id
    created_at
    updated_at
    status
    fkey1_id
    fkey2_id
    fkey3_id
    date_field1
    date_field2
    column1
    column2
    column3

Being able to add a new foreign key column with the "rest" of them would be awesome.

corford4• 11 years ago

I'd love this too. I wanted materialised views and upsert, so here's hoping :)

jeltz2• 11 years ago

Work on this started recently, but since it is an open source project there is no way to know if this will make it to 9.6 or not (the work was start too late to get into 9.5).

http://www.postgresql.org/message-id/flat/20141209174146.GP1...

ianamartin• 11 years ago

In my crazy-ass opinion, if you need upserts, there are problems with your application logic and problems with your database.

Get off my lawn, etc.

Nullabillity• 11 years ago

Syncing data from a third-party API?

spacemanmatt2• 11 years ago

In a concurrent world, it's a reality you eventually have to deal with.

taco_emoji• 11 years ago

Why bother posting if you're not going to explain yourself? It's 2015, nobody's going to be impressed by mere contrarianism.

DotHide• 11 years ago

Great! I'm just like it!

ivoras• 11 years ago

Does the description seems like it's a two-pass operation to anyone else? Can anyone with more detailed knowledge tell why can't this be a single-pass "INSERT in a unique index, UPDATE if it fails" operation?

anarazel7• 11 years ago

To understand the problem one first has to know that for better or worse postgres does not use index organized tables. Additionally this ought to work not only on a primary key but also on additional unique constraints. Including partial unique indexes, potentially over expressions (say lower(username) or something).

That makes some approaches harder.

The bigger problem is that 'update if fails' is easier than it sounds. When you find a conflicting row in the index, that row's creating transaction might not yet have committed. So you need to first wait for it to commit, and then retry. But that implies that the transaction could actually roll back or delete the row again. And suddenly there's not actually a row to UPDATE. Thus you need to retry, right?

There's also some issues around avoiding deadlocks. If you "naively" just acquire a 'value lock' on the to-be-inserted row, you can easily get into deadlock territory if more than one row is inserted in one transaction. Such value locks have to be released after an attempt to update. Which then necessitates a looping attempt...

Does that start to explain the problems a bit?

agotterer• 11 years ago

Does anyone know when this is expected to be released?

elchief2• 11 years ago

New versions of Postgres usually ship in September. Except 9.4, which slipped to December.

endymi0n• 11 years ago

For the backstory on why this has taken so long: http://www.depesz.com/2012/06/10/why-is-upsert-so-complicate...

Mister_Snuggles• 11 years ago

This reminds me of a common idiom in the Pick-style databases that I've worked on...

    READU REC FROM FILE,KEY ELSE
        REC<1> = 'stuff'
        * Set other fields as appropriate for a brand new record - this is the 'insert'
    END
    REC<1> = 'blah'
    * Set other fields as appropriate for an update
    WRITE BLAH TO FILE,KEY ;* Write record and release locks

This can, of course, be wrapped in a transaction.

morekozhambu3• 11 years ago

So to simplify, what I understand is

1. INSERT .. ON CONFLICT is an implementation UPSERT technique

2. MERGE is something meant to be different from UPSERT but has UPSERT feature?

jeltz2• 11 years ago

Basically true except for many implementations of MERGE not being concurrency safe, which means you would have to loop anyway on unique violation making it no better than the CTE based techniques that already exist in 9.4.

asnyder• 11 years ago

As someone that's been using PostgreSQL way before it was cool (since 2005), I'm super happy to see this. Previously I had to do EXISTS, IF FOUND UPDATE ELSE INSERT. Now I can just do upsert, so nice.

Very thankful to all the hard working Postgres devs. PostgreSQL was really good when I first started using it, and just keeps getting better. Glad to see the general community finally getting on the PostgreSQL bandwagon.

Now let's see if I can get more people to use internal PostgreSQL functions instead of constantly writing statements directly in their code.

morekozhambu3• 11 years ago

Slightly OT, why do we see these errors with Oracle's MERGE statements?

ORA-01779: cannot modify a column which maps to a non key-preserved table

ORA-30926: unable to get a stable set of rows in the source tables

njharman• 11 years ago

Asking a support question on a feature announcement post for a completely unrelated product is EXTREMELY off topic.

morekozhambu3• 11 years ago

It was not meant as a support question. It was in the context of MERGE/UPSERT was I asking. I wanted to know if similar thing happens in PG.

corford4• 11 years ago

I've been looking forward to this for ages! The solution we use at the moment for upsert is a create or replace rule on the table but it isn't great as you need to remember to update the rule whenever you change the table schema...

  CREATE OR REPLACE RULE "replace_row" AS
  ON INSERT TO my_table
  WHERE EXISTS(SELECT 1 FROM my_table WHERE id=NEW.id)
  DO INSTEAD
  (UPDATE my_table SET
  col1=NEW.col1,
  col2=NEW.col2,
  col3=NEW.col3,
  WHERE id=NEW.id);

anarazel7• 11 years ago

That unfortunately is not safe on several fronts.

For one, the WHERE EXISTS() will not see concurrent insertions by transactions that are still in progress, which means you'll still get constraint violation errors.

For another, rules generally have very surprising behaviour. E.g. with this RULE you'll get into trouble if "id" isn't passed in as a explicit value, but uses a non-deterministic DEFAULT or directly passed in expression. Every reference to NEW.id will not be replaced by the result of that expression, but rather with the expression itself. For the common serial id column (aka autoincrement) you'll have a separate nextval() call in each reference. Which can make this explode in pretty damn confusing ways.

corford4• 11 years ago

Unfortunately, there's no good all round solution for upsert in current postgres versions. For our use case (one logger daemon feeding results off a message queue in to a table in an idempotent way) this approach was the simplest and worked the best.

piker• 11 years ago

I am extremely happy to hear about this. We currently insert, catch the exception and update if necessary. That can lead us to race conditions in concurrent transactions. Thank you very much for your excellent work on Postgres. It continues to be an amazing product to work with.

jedi_master• 11 years ago

its about F*G time

rabee• 11 years ago

Oh happy day. PostgreSQL is the best.

ExpiredLink• 11 years ago

Say goodbye to CRUD. Because 'create' and 'update' are syntactically and semantically the same, right?

DiabloD3• 11 years ago

When is Postgres going to get built in fault tolerant multi-master support in any form?

ape4• 11 years ago

I think an UPSERT keyword would be more clear.

pcl• 11 years ago

Upserts are generally useful tools for applications that run in active-active multi-datacenter configurations. Have you guys explored the implications of this feature for such deployments on top of Postgres yet?

qubyte• 11 years ago

I found engineering around the lack of this feature very upserting.

I'll get my coat.

Postgres gets support for upsert

Related Stories