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

21–30 of 114 posts

Re: Postgres gets support for upsert

#21

Post by smegel »

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!

Re: Postgres gets support for upsert

#22

Post by colanderman »

dan15 wrote: post #12
DigitalSea wrote: post #6

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.

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

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.

Re: Postgres gets support for upsert

#23

Post by veddan »

taspeotis wrote: post #20

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)

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

Re: Postgres gets support for upsert

#24

Post by anarazel »

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.

Re: Postgres gets support for upsert

#25

Post by colanderman »

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

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.

Re: Postgres gets support for upsert

#27

Post by pilif »

colanderman wrote: post #22
dan15 wrote: post #12

Earlier quoted context omitted.

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

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…

> 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 procedures, views, subselects, check constraints, triggers, actually enforcing foreign key constraints and so on.

It was significantly slower than MySQL, but it also scaled much better under load. Back then, when you had low load, MySQL would be about twice as fast as PostgreSQL but then as the load increases, MySQL's performance would drop sharply and Postgres would stay consistent.

By now, MySQL has mostly caught up feature-wise, but there's still stuff left that Postgres just does better. Also, even plain ideological reasons (community project vs. oracle open-core project) would want me to stay with postgres.

I also have anecdotal evidence that MySQL still has serious issues in the robustness department which I've yet to see with postgres.

Re: Postgres gets support for upsert

#28

Post by atombender »

colanderman wrote: post #11
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.

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

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.

Re: Postgres gets support for upsert

#29

Post by saurik »

colanderman wrote: post #11
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.

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

...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 :/).
Post reply on HN