Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Postgres gets support for upsert

git.postgresql.org

Post reply on HN
Print view View original

71–80 of 114 posts

Re: Postgres gets support for upsert

#71

Post by CWuestefeld »

daigoba66 wrote: post #64
viraptor wrote: post #19

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

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 nee…

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

Re: Postgres gets support for upsert

#72

Post by NoMoreNicksLeft »

anilshanbhag wrote: post #3

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.

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.

Re: Postgres gets support for upsert

#73

Post by buremba »

anarazel wrote: post #24
viraptor wrote: post #19

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

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.

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

Re: Postgres gets support for upsert

#74

Post by cstejerean »

anarazel wrote: post #24
viraptor wrote: post #19

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

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.

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.

Re: Postgres gets support for upsert

#75

Post by anarazel »

NoMoreNicksLeft wrote: post #72
anilshanbhag wrote: post #3

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.

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.

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

Re: Postgres gets support for upsert

#76

Post by Jweb_Guru »

CWuestefeld wrote: post #71
daigoba66 wrote: post #64

Earlier quoted context omitted.

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 nee…

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

The T-SQL design also has numerous bugs, and allows for many nontrivial race conditions (which appears not to be a well-known fact).

http://www.mssqltips.com/sqlservertip/3074/use-caution-with-...

Postgres tries to avoid "gotchas" like this. If SQL Server fixes all the issues listed above by the time Postgres releases its first version of MERGE, but Postgres's version actually works the first time because they spent enough time up front addressing concurrency issues, then IMO Postgres will have made the right call.

(And yes, if Microsoft is unable to ever fix all these issues and Postgres never releases the feature, I would still stand by this statement. I want to be able to rely on every feature in my database).

Re: Postgres gets support for upsert

#77

Post by anarazel »

ivoras wrote: post #61

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?

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?

Re: Postgres gets support for upsert

#78

Post by anarazel »

buremba wrote: post #73
anarazel wrote: post #24

Earlier quoted context omitted.

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.

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

I'm pretty sure it's not (going to be) considered insignificant.

Unfortunately that does not equate to resources (i.e. time by somebody sufficiently crazy^Wdetermined) for implementing it being available. A large part of postgres development is driven by individuals. Some of it on company time, but usually not most of it.

Re: Postgres gets support for upsert

#79

Post by kermatt »

rimantas wrote: post #52
pilif wrote: post #27

Earlier quoted context omitted.

> late version 7 or early version 8, back when MySQL was the more featureful and performant of the two MySQL was never more featureful. The reason I started migrating back in the 7.1 time frame (when TOAST tables were added and you could finally store more than 8K of text in a TEXT column) was the lack of sub-selects in MySQL. Even aside of that, Postgres was far ahead when considering basic SQL support: stored proce…

> MySQL was never more featureful Replication? Replication? Replication? UPSERTS? Also, MySQL supports multiple engines, so when talking about MySQL it makes sense which engine you have in mind.

Multiple engines in the MySQL case are an anti-feature, from the MyISAM v InnoDB perspective. Far too often the wrong or arbitrary choices are made, mixing within the same database, causing unnecessary confusion and work. "That table is on this storage engine which does not support the operation we need."

I can see the case where a different storage engine supports a different data format or use case (columnar storage for example), but in an RDBMS case I would argue consistency outweighs convenience.

Re: Postgres gets support for upsert

#80

Post by Mister_Snuggles »

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

    READU REC FROM FILE,KEY ELSE
        REC = 'stuff'
        * Set other fields as appropriate for a brand new record - this is the 'insert'
    END
    REC = '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.
Post reply on HN