News for hackers. Stuff that matters.
Re:Upsert Lands in PostgreSQL 9.5 – A First Look by jeffdavis on Wednesday May 13, 2015 @01:21PM (#9540483)
"DO UPDATE SET description=description;"

I think that should be:

"DO UPDATE SET description=excluded.description;"

And doesn't it also need an inference clause or constraint specification for DO UPDATE?

Please update example code to be self-contained, so that readers can copy/paste.

The example itself -- upserting product descriptions from another data source -- is a great one though.

Re:Upsert Lands in PostgreSQL 9.5 – A First Look by craigkerstiens on Wednesday May 13, 2015 @02:09PM (#9540841)
Hi, Jeff

Will update as soon as I'm at a machine, but yes of course you're correct.

Re:Upsert Lands in PostgreSQL 9.5 – A First Look by robbles on Wednesday May 13, 2015 @12:00PM (#9539925)
I've read several times now that the tradeoff for upsert taking so long was that the implementation is "safe". Is MySQL's / other DB's version of this "unsafe" somehow? Or was there a particular architecture issue specific to Postgres that made this so difficult to implement without bugs?
Re:Upsert Lands in PostgreSQL 9.5 – A First Look by jandrewrogers on Wednesday May 13, 2015 @12:16PM (#9540029)
The difficulty is supporting high concurrency AND correctness AND high performance with this feature. Most databases that have supported this in the past have sacrificed one of these; PostgreSQL requires an implementation with no impact to existing users and without weakening the guarantees people are used to.

In most good databases you can compose an upsert transactionally from a set of independent operations. The problem is that this is slow because you are plumbing the database engine multiple times for an operation that could be executed in one shot in principle. A native upsert implementation provides a fast, single operation. In the case of PostgreSQL though, they had to support this in the context of high-performance, high-concurrency, and correctness which makes it tricky because it is relatively complex to implement under those constraints.

Re:Upsert Lands in PostgreSQL 9.5 – A First Look by ryanjshaw on Wednesday May 13, 2015 @12:50PM (#9540229)
This implementation of upsert is especially nice because it is atomic [1]:

    <literal>ON CONFLICT DO UPDATE</literal> guarantees an atomic
    <command>INSERT</command> or <command>UPDATE</command> outcome - provided
    there is no independent error, one of those two outcomes is guaranteed,
    even under high concurrency.
I don't know about Oracle or MySQL, but the SQL Server implementation of MERGE requires you to carefully consider which locks to use and it's very easy to shoot yourself in the foot with.

[1] http://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdif...

Re:Upsert Lands in PostgreSQL 9.5 – A First Look by zimpenfish on Wednesday May 13, 2015 @12:08PM (#9539973)
In the previous discussion, someone mentioned MSSQL (and T-SQL)'s issues with MERGE:

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

And also this:

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

Re:Upsert Lands in PostgreSQL 9.5 – A First Look by luckycharms810 on Wednesday May 13, 2015 @12:05PM (#9539959)
I think one of the quirks you can run in to with MySQL's implementation is that even the DB decides to do an update, you end up burning an auto incremented key. Curious to see what PG ends up doing here.
Re:Upsert Lands in PostgreSQL 9.5 – A First Look by anarazel on Wednesday May 13, 2015 @08:46PM (#9542886)
You pretty much have to do so. The conflict could be on the column with the default value after all. And obviously you can't just rollback the sequence/autoincrement value after deciding to update because that'd either require locking the sequence for the duration (horrible for concurrency) or would pose problems with other sessions already having used further values.
Re:Upsert Lands in PostgreSQL 9.5 – A First Look by pilif on Wednesday May 13, 2015 @12:15PM (#9540018)
> you end up burning an auto incremented key. Curious to see what PG ends up doing here

I haven't tried, but Postgres always uses values in sequences in similar cases. The good news is that there are so many values in a 64 bit sequence that this really shouldn't matter.

I much prefer some values in sequences to be not used over even the tiniest possibility of a value being used twice accidentally.

Re:Upsert Lands in PostgreSQL 9.5 – A First Look by spacemanmatt on Wednesday May 13, 2015 @12:03PM (#9539942)
Not especially more difficult than other architectures, but their quality bar is typically much higher. YMMV.
Re:Upsert Lands in PostgreSQL 9.5 – A First Look by pilif on Wednesday May 13, 2015 @12:11PM (#9539997)
Now if only there was some syntax shorthand for "just take everything from this now conflicting INSERT and discard what's already there".

Like SQLite's "INSERT OR REPLACE".

Aside of the transactional benefits that upsert has, it's also convenient in that you don't have to update and then insert, saving a lot of typing in cases where you're not relying on an ORM and still have to work with bulk data where you really don't care about local changes (because there were none).

The syntax for this is a bit inconvenient right now.

Re:Upsert Lands in PostgreSQL 9.5 – A First Look by mbreese on Wednesday May 13, 2015 @12:19PM (#9540049)
The syntax looks inconvenient, but I like it. If you have a conflict, you might not want to just update everything, but only a subset of the fields. With this syntax (verbose as it is), you have the option of explicitly stating what you want to happen in the event of a conflict with no ambiguity. Perhaps there is a place for some shorthand like

    ON CONFLICT SET *=*.
But aside from that, for a RDBMS like Postgres, I'd opt for explicit over implicit.
Re:Upsert Lands in PostgreSQL 9.5 – A First Look by tracker1 on Wednesday May 13, 2015 @03:18PM (#9541310)
One would think you would just need a list of fields...

   ON CONFLICT SET colA,colB,colC
As to the gp, you don't want to update the "CREATED" field on an upsert.
Re:Upsert Lands in PostgreSQL 9.5 – A First Look by ccleve on Wednesday May 13, 2015 @03:27PM (#9541362)
Is there a reason for yet another syntax for upserts?

There's an ANSI SQL syntax: http://en.wikipedia.org/wiki/Merge_%28SQL%29

And MySQL has its own syntax: http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.h...

This is a pain for those of us who are trying to maintain cross-database libraries. (https://github.com/dieselpoint/norm)

Re:Upsert Lands in PostgreSQL 9.5 – A First Look by kidmenot on Thursday May 14, 2015 @05:13AM (#9544090)
Norm looks very nice, thanks for posting. I haven't had a chance to look at the internals, but do you think it could be ported to C#?
Re:Upsert Lands in PostgreSQL 9.5 – A First Look by ccleve on Wednesday May 20, 2015 @10:58PM (#9580716)
Probably, but I don't use C#. Feel free to give it a shot.
Re:Upsert Lands in PostgreSQL 9.5 – A First Look by Keats on Wednesday May 13, 2015 @11:45AM (#9539800)
Getting Sorry, I cannot find /2015/05/08/upsert-lands-in-postgres-9.5/
Re:Upsert Lands in PostgreSQL 9.5 – A First Look by postila on Wednesday May 13, 2015 @11:25AM (#9539610)
Yep, this is awesome. Go Postgres!
Re:Upsert Lands in PostgreSQL 9.5 – A First Look by sksixk on Wednesday May 13, 2015 @11:45AM (#9539795)
good news: upsert works, bad news: select doesn't work
Re:Upsert Lands in PostgreSQL 9.5 – A First Look by Tenhundfeld on Wednesday May 13, 2015 @12:16PM (#9540028)
IMHO, this qualifies as trolling without further explanation.
Re:Upsert Lands in PostgreSQL 9.5 – A First Look by bsg75 on Wednesday May 13, 2015 @03:00PM (#9541204)
I will bite: SELECT does not work on what? As compared to?