PostgreSQL 9.5 Does UPSERT Right (thenewstack.io)

joabj writes: For years, PostgreSQL users would ask when their favorite open source database system would get the UPSERT operator, which can either insert an entry or update it if a previous version already existed. Other RDMS have long offered this feature. Bruce Momjian, one of the chief contributors to PostgreSQL, admits to being embarrassed that it wasn't supported. Well, PostgreSQL 9.5, now generally available, finally offers a version of UPSERT and users may be glad the dev team took their time with it. Implementations of UPSERT on other database systems were "handled very badly," sometimes leading to unexpected error messages Momjian said. Turns out it is very difficult to implement on multi-user systems. "What is nice about our implementation is that it never generates an unexpected error. You can have multiple people doing this, and there is very little performance impact," Momjian said. Because it can work on multiple tables at once, it can even be used to merge one table into another.

Comments

Too late

[Anonymous Coward]

Already check, then insert. Not rewriting code, and will use what I've got.

Re: Too late

[Anonymous Coward]

Oh. How disappointing. I'll let the team know to remove the code that does this then seeing as you already have a solution.

Re:Too late

[TheRaven64]

I hope that you're doing the check then insert inside a transaction.

Re:Too late

[Anonymous Coward]

What if two (or more) transactions, need to update, delete, insert the same?

I love race conditions.

Best solution I have is to return failure, and rollback, and ask the user (or calling system), to try again. Trying to solve the race condition often leads to worse situations than just returning a failure.

Re:Too late

[behrooz0az]

this.
try to solve a complex one in code without user interaction and having a deadlock is only the good part.

Re:Too late

[MagicMerlin]

That's how to do it. It has to be managed in a loop transaction side or in the application. This loop is remarkably difficult to get right by most programmers. For example, if a trigger is added to the inserted table after the fact causing an error in some other table, most loop based upsert code I've seen will fail into an infinite loop.

This JUST In!

[Anonymous Coward]

Roy Rodgers Is Riding To-Nite!

Fucking finally.

[Anonymous Coward]

Finally. That's all.

Re: Fucking finally.

[Anonymous Coward]

PostgeSQL is one of those rare projects that delays a bit but makes sure that they plan their codebase ahead, and Get It Right The First Time.

Nobody should ever whine about delays in projects that value correctness over first to market, especially in an open source project.

Re: Fucking finally.

[Anonymous Coward]

And this is why PostgreSQL rocks!

Re: Fucking finally.

[Rei]

Postgres is leading edge in some things, like (ridiculously useful) table inheritance. Their implementation could have been slightly better (the most common complaint being that indices don't inherit, you need to re-add them on all descendants), but I'm very glad that they were early adopters on this one. They even pulled off multiple inheritance well.

Re: Fucking finally.

[Anonymous Coward]

I only wish child tables didn't have to have all the fields defined or in the same order (null'em out, etc.).

Re:Fucking finally.

[U2xhc2hkb3QgU3Vja3M]

Are you upsert that they took so long?

Re: Fucking finally.

[Rei]

I'm not sure what you mean. Could you clarify with an example of how it is vs. what you want?

Re: Fucking finally.

[mark-t]

He's probably referring to schemaless data records.

Re: Fucking finally.

[Rei]

But python has them. Three different types (xml, hstore, and json). And I don't see how that would be something special to inheritance.

Re: Fucking finally.

[Rei]

úff... that should read "postgres has them" :P Been using python too much lately...

Re: Fucking finally.

[Qzukk]

He's talking about table inheritance: CREATE TABLE foo (...) INHERITS (parenttable);.

If parenttable has "id integer, name varchar" then foo will automatically have "id integer, name varchar" just like inheritance in programming languages. Unlike most programming languages, you cannot redefine these fields at all (for instance, change id to a UUID datatype or remove it). Doing that would break postgresql's version of polymorphism, where SELECT * FROM parenttable; will also return the id and name columns (only columns that exist in parenttable) of all of the rows of foo.

Re: Fucking finally.

[Rei]

If parenttable has "id integer, name varchar" then foo will automatically have "id integer, name varchar"

... which is the whole point if inheritance. Am I to understand that he wants inheritance, except without any inheritance?

just like inheritance in programming languages. Unlike most programming languages, you cannot redefine these fields at all (for instance, change id to a UUID datatype or remove it).

In what programming languages can you do that? That doesn't even make sense, you couldn't convert an object back to the parent if you did that - wherein the child wouldn't be a child at all. You're describing template programming, not inheritance.

Doing that would break postgresql's version of polymorphism, where SELECT * FROM parenttable; will also return the id and name columns (only columns that exist in parenttable) of all of the rows of foo.

Of course it would, and it damn well should. If an A is a type of B, then a query of all Bs should include all As.

And on top of all this I still fail to see any problem. So they don't want to use the parent columns? Nobody is holding a gun to their head - unless they purposely specified the parent columns as NOT NULL they'll default to null.

Nice.

[Anonymous Coward]

Nice.

Re:Nice.

[davester666]

Dup

Re:Nice.

[Anonymous Coward]

Dup.

Re:Nice.

[Anonymous Coward]

Yup

Re:Nice.

[Anonymous Coward]

'Sup?

Re:Nice.

[Anonymous Coward]

right on

Nice.

[Anonymous Coward]

Nice.

Nice.

[Anonymous Coward]

Nice.

Nice.

[Anonymous Coward]

Nice.

Nice.

[Anonymous Coward]

Nice.

Cool

[Anonymous Coward]

It has been out for a week, kinda early to pat yourself on the back

Re: Cool

[Anonymous Coward]

With that said, it's still good to see they finally have upsert

Re:Cool

[93 Escort Wagon]

Well, it's postgres - do you expect them to just say "sorry we were behind on implementing this feature"?

Re:Cool

[phantomfive]

but that doesn't mean I want his bundt cake recipe

Does he have a good one?

Re:Cool

[Zero__Kelvin]

"Well, it's postgres - do you expect them to just say "sorry we were behind on implementing this feature"?"

Of course not. That would be a ludicrous thing for anyone to say. You don't seem to understand FOSS. Unless they announced that said feature would be implemented by a certain date and missed their dealine, then they aren't "behind", they are on schedule.

I'm somewhat

[vikingpower]

upserted, this morning.

Re:I'm somewhat

[haruchai]

Love the .sig, Oliver Reed's last role.
R.I.P you crazy drunken Brit tough guy.

Re:I'm somewhat

[KGIII]

Heh, you're a good one to ask. I am not, and should not be confused for, a DB admin - in fact, I hate it. Oh, I've had to fight with them before and I suck at it. I've gone on about the "wizard" who did the job for us. I am forever grateful for his skills, to the point where is peculiarities did not bother.

At any rate... Am I reading this summary properly? Is this summary saying that, prior to now, you could not update data that was in the database?

For years, PostgreSQL users would ask when their favorite open source database system would get the UPSERT operator, which can either insert an entry or update it if a previous version already existed. Other RDMS have long offered this feature. Bruce Momjian, one of the chief contributors to PostgreSQL, admits to being embarrassed that it wasn't supported.

Err... You couldn't update an entry if a previous version already existed? So, umm... What if the user changed their password? Did it create a whole new field to store the (hopefully) hash? Did it depreciate the old field somehow?

I'm thinking that it is either poorly written (and that I'm not too bright in the ways of a database) or that all the hype that I've read about Postgre has not been very honest. :/

Re:I'm somewhat

[Lord Crc]

Is this summary saying that, prior to now, you could not update data that was in the database?

No. In some cases you want to use a table like a key-value storage. If the key does not exist you need to insert a row with that key. Otherwise you'll want to keep only one row with that key, and just update the value.

One can always do a "if key exists then update else insert", but the problem is that this is not atomic, because the "key exists" check is a separate statement from the insert or update statement. This can lead to issues if you have multiple connections accessing the same keys at the same time.

The UPSERT allows you to do this as one atomic operation.

Re:I'm somewhat

[vikingpower]

Lord Crc answered correctly. UPSERT = (INSERT iff not exists... else UPDATE ). The article is correct in the sense that other databases, especially open source one, do not always handle this one correctly. It was not part of "traditional" SQL and is rather new. Object-oriented and graph databases do not have any problems with such operators, as they're explicitly written to deal with this use case. For relational databases like PostgreSQL this is a harder one to get right. Whether PostgreSQL now really got it right, can only be proven by protracted use "out in the wild".

Re:I'm somewhat

[Hognoxious]

The difference was that you had to know in advance whether it existed and use a different command in each case.

I have to say that I've rarely found that to be a problem, but it's always nice to have options.

Re:I'm somewhat

[vikingpower]

Agree. It's something seen in the "natural" evolution of (computer) languages all the time. When the language ages gracefully, such "finer" options become part of it.

Re:I'm somewhat

[KGIII]

Ah ha! Thanks. I get it now. Well, I think... Basically, this allows more an if/or? If it is X (and should be Y) then change it to Y and if it is Y already then leave it as Y. But in one more command without actually having to use a longer statement to get the same results?

That makes sense. I can join, add, merge, and stuff like that. I can even (sort of) do it in C, PHP, and probably bang it out in Perl. However, I hate it. I know it may sound odd but, for whatever reason - and I hold a PhD in Applied Mathematics, I just can't wrap my head around it well. I don't know what it is, I don't know why. I don't know how to change it - and I've tried. For whatever reasons, I just can't quite grasp databases that well. Well enough to use them, well enough to learn some things by rote, but not enough to truly conceptualize.

Erf... To put it into a bit of perspective (if I can) my maths skills were rather poor until I had an instructor who enabled me, sort of triggered it, to actually visualize what the things I'd been doing by rote actually meant. From there on out? I was golden. I can do fractions, percentages, long division, and more in my head. Now that I get it, I understand logarithms and can even "see" what an algorithm does. For whatever reason, I can't seem to get a database. Oh, I know what they are and what they do and how to do some of it. I understand columns, rows, tables, joins, inserts, mergers, and stuff... I just can't grab it.

Heh, the other day - after all these years, it finally clicked as to what a hash was actually doing in a database. Yes, really. I'd never bothered asking and had somehow concluded they must be being decrypted/dehashed at some point. Yup... Oh, it's worse. Not long before that - I finally went "duh." I finally understood (after having it beat into my head) that a OTP actually does and why it can't be just brute forced. Yeah, I felt really dumb. I'm okay with that, however. It means that I'm still willing to learn and I'd been smart enough to not assert that I was correct but rather presented my thoughts as a question and asked for correction. Fortunately, someone was kind enough to be patient and beat me with a clue stick until I finally understood it. It's for things like that, times like that, that I appreciate Slashdot. Sometimes I get to wield the clue stick and sometimes it's wielded on me.

At any rate - I think I have it now. I also think something might have just clicked. Strange as that may sound - sometimes it's the little things that end up making me finally able to understand. No, I have no idea why my brain works the way it does. It's like I can do things by rote forever and not understand at all and then, finally, something - even something small, means I start to understand. I have no idea if that's normal or not. Consider, if you will, that I worked - quite extensively - with very large data sets. :/ Yeah... However, it was mostly rote and trial and error until I got things to work.

Re:I'm somewhat

[KGIII]

That makes perfect sense! For whatever reason, those words worked. Maybe you should look into teaching? I dunno. But that actually worked and now I understand - I think. Thanks. Seriously, thanks. I can even envision why this might be handy - hell, I can think of places where I'd have used this in the past.

Re:I'm somewhat

[KGIII]

I see, now. :D That makes a lot more sense to me. Between Hog and Orc, I got it fairly well, I think. I dunno, my brain works "funny." At least I assume it's not normal, I can't say that I know - I'm used to it, so it seems normal to me. There are things that I end up just having to learn by rote - and it's hard. And then sometimes, and it might be the smallest of things, it just "clicks" and I get it and can visualize the entirety instead of just a piece. Until I'm able to conceptualize the entirety, I'm just going by rote - I might not even understand what I'm doing or why what I'm doing is working. Then, and it might seem a trivial remark, makes it sort of open up and I understand the whole.

If you give me a practice exam based on actual randomized questions that are on the tests and let me finish and score those practice tests for three days? I can pass any exam out there, so long as I take that exam not long after. I won't actually know a damned thing but I can answer the questions - I can even answer other questions based on correlations with previous answers/questions. I still don't understand it. I'm just good at taking tests. Then, sometimes, it just clicks and I get it. I used to be really, really bad at mathematics until I finally had an instructor who made it so that things "clicked." (I mentioned this in another reply.)

At that point, I just understood. I could understand the whole. I could actually see why the math was what it was, what it meant, how it applied, and even do a good amount of it in my head - or even just get really accurate guesstimates. An example might be that I went shopping with the missus a while back and she wondered how much the cart full of items was going to cost. I told her the amount and the total with sales tax. I was fifty three cents off from a total of some 30+ items with a $300+ value - I hadn't actually really been trying to keep track, I just remembered what the prices were 'cause I like numbers and I know what the sales tax is. (I was probably only off because it turns out that some foodstuffs are taxed. I did not know that.)

Dunno if that makes sense? Basically, once it clicks then I get it. Until then, I'm flailing around in the dark and probably going to poke an eye out. I can do the "rote" thing and that would look like understanding to a casual observer but it's not actual comprehension. I doubt it's unique, by any means, but I still suspect it's not entirely normal. I've bumped into a few people who express similar thoughts about their learning process. In this case, for whatever reason, it might actually have caused some internal clicking but I won't know until I'm faced again with something that I don't know and can see if I can reach the correct answer. Erf... I'm not sure that's even English but, it's what I'm going with. I don't blame you if it doesn't make sense.

Re:I'm somewhat

[Hognoxious]

It's more like "If it's X, change it to Y. If it doesn't exist at all, create it with a Y."

Without this, trying to modify a record that doesn't already exist will cause an error. //to do: dig at MySQL goes here.

Re:I'm somewhat

[Anonymous Coward]

One can always do a "if key exists then update else insert", but the problem is that this is not atomic, because the "key exists" check is a separate statement from the insert or update statement. This can lead to issues if you have multiple connections accessing the same keys at the same time.

Most databases allow you to do transactions (BEGIN TRAN/COMMIT TRAN) that force it to be atomic.

Does postgres not support that?

Admittedly it's easier to code with an UPSERT operation, but this is hardly revolutionary stuff.

Re:I'm somewhat

[Lord Crc]

Most databases allow you to do transactions (BEGIN TRAN/COMMIT TRAN) that force it to be atomic.

Sure, but in that case you might get errors when you try to commit if another connection has changed the value in the meantime, and you'll have to retry the whole thing. That's what UPSERT avoids.

Re:I'm somewhat

[KGIII]

Cool. Thanks again. I should find some sort of database system to play with and see how it goes. Maybe redo an SMF install onto PostgreSQL and then see what I can break/tweak/learn. 'Snot like I'll be breaking the whole internet, just a small piece and it'll be wiped clean in a day or two, after I'm done playing. I'd not want to leave my mess open for others to exploit and then abuse. I guess I could do it locally. All of my hardware down here, at this place, was out of date - so I ordered a few new boxes, a new router, and I should have all that arriving this week.

If anyone's local and wants a few boxes (all about three or four years old)? I should check with the Slashdotters who came by for NYE though I'm not sure if they'll make the trip for a three desktops that are already a few years old. Well, four of them, probably. The fourth one's a wee bit older and not really a desktop. :/ They're nothing special, just white boxes with the last one being a server for file sharing and hosting a few VMs. If they don't want 'em then I think I saw a Goodwill. I don't suppose you're near Panama City Beach, FL and want a few boxes... I've ordered all new peripherals so they've all got LCDs (21" I guess) and whatnot. I think they've all got 8 GB of RAM, spinning platters for data - I know, I know, and the likes. I think the server's got 16 gigs of RAM. Err... Also *hangs head* they've got Windows on 'em. They all work though.

I should see if there's a LUG or something, maybe some makers? They're a bit 'too new' to just toss into Goodwill but not new enough to keep. Oh, Google tells me there's a Goodwill Industries - they do the training and whatnot. They might want 'em for office work. They run fine with Win 7 and Server '08 on them but I'm sure they'll run even better with Lubuntu or Mint on 'em. The licenses on 'em are originally MSDN licenses, so I'm pretty sure I'm not supposed to transfer those. I started to switch them over to Lubuntu but I never got around to finishing it. :/ Hmm... Anyone local that I haven't already met? I'll send an email to the ones that I have met and offer them the boxes first but, if they don't want 'em and someone else does then they might as well go to a good home.

Re:I'm somewhat

[Zero__Kelvin]

I sincerely want to congratulate you for learning about a subject rather than speaking from a position of false authority. It seems like just the other day you were rambling on passing yourself on as an expert on a subject you didn't understand. This is a new KGill I'm seeing! Kudos to you!

Re:I'm somewhat

[ADRA]

Not to forget the abhorrent performance loss of adding at least a round trip per row. If you're running a typical poorly performing CRUD app, that gets multiplied for every item in a batch of insert/update's that you'd like to process. Assuming the data was guaranteed to be identical, it was faster to delete all / insert all vs. the alternative which would be manually verifying each row's existence sequentially. This certainly speeds up a lot of natural key table interactions.

Re:I'm somewhat

[Anonymous Coward]

Internally some databases did it "atomically" in a sense but really just issues a delete statement then an insert.

Re:I'm somewhat

[Ed Avis]

Many RDBMSes allow more than one statement per batch - so you can execute two or more statements in a single round trip. For example you could do 'if exists (blah) ...', assuming your dialect of SQL supports it, in just one round trip, or even separate 'insert where not exists... update...' or whatever technique you want to use. (I am not saying that these techniques are a foolproof alternative to merge or upsert, they are not, but that is for another discussion.) If you have multiple rows, you can still prepare a statement handle and handle them in the same number of round trips it would require for a single insert, or at most one extra round trip for the whole batch. Essentially - the checking for an existing row, even without a merge / update builtin, can usually be done in SQL rather than clunkily by the client app.

Re:I'm somewhat

[Anonymous Coward]

> One can always do a "if key exists then update else insert", but the problem is that this is not atomic, because the "key exists" check is a separate >statement from the insert or update statement. This can lead to issues if you have multiple connections accessing the same keys at the same time.
Ever heard of SELECT .. FOR UPDATE?

Re:I'm somewhat

[Lord Crc]

Ever heard of SELECT .. FOR UPDATE?

How do you prevent two connections from inserting the same key at the same time using that?

Re:I'm somewhat

[Anonymous Coward]

It was not part of "traditional" SQL and is rather new.

That's because UPSERT is a poor substitute for standard SQL MERGE INTO. While I'm no fan of Larry Ellison, Oracle gets MERGE INTO right (except for fine-grained access control). So do DB2 and MSSQL, though both offer non-standard extensions. I'm actually disappointed with this addition to PostgreSQL, because it likely means that support for MERGE INTO will be delayed even longer than it already has, perhaps indefinitely, or discarded entirely.

Since 9.1, I would achieve the effects of MERGE INTO using a writable CTE, though the documentation still does not explicitly state whether the entire query, including the WITH clause, is guaranteed to be atomic and consistent. I have always assumed there must be some gotchas, otherwise the PostgreSQL team could have implemented MERGE INTO simply by means of a syntactic rewrite into a corresponding writable CTE.

- T

RTFS

[Anonymous Coward]

âoeOther RDMS have long offered this feature. Bruce Momjian, one of the chief contributors to PostgreSQL, admits to being embarrassed that it wasn't supported.â

It was even in the summary, let alone the article. No pleasing some people I guess.

PC cops

[Tablizer]

Sounds too much like "Up Skirt". Somebody's gonna sue.

Re:PC cops

[NoNonAlphaCharsHere]

Nah. If you want to get upskirted by a database, you're gonna have to go with Squeel Server.

Thanks a lot PostgreSQL devels

[hrumph]

This is great. I've been using PosgreSQL for a while now. It's one of those pieces of software that just does what it's told and doesn't let you down. While I'm saying this there are credible rumours to the effect that the Oracle merge operation is broken. Read the comments to the most upvoted answer at this stack exchange question. The final comment is:
Not reliably. I ended up with retry loops in the client code. :( – Randy Magruder Aug 27 '15 at 16:05
This makes me think that Bruce Momijan may have been thinking about Oracle's implementation of merge when he said that other implementations were handled very badly.

Re:Thanks a lot PostgreSQL devels

[Anonymous Coward]

PostgreSQL is maintained by full-time employees. None of the "devs" are doing favors for anyone, they're doing their job, while the owners try to keep the project relevant.

Re:Thanks a lot PostgreSQL devels

[Anonymous Coward]

As are most enterprise level open source projects, doesn't make the development any less worthy of compliment.

Re: Thanks a lot PostgreSQL devels

[BigZee]

Merge is not really the same as upsert. Merge (using the dual psuedo table) is a potential solution to the problem as are a number of options using pl/sql. I think the real truth here is that oracle haven't seen the need to implement an upsert command. Its not difficult to code and would be easy to build a solution without the need of a special command.

Re: Thanks a lot PostgreSQL devels

[Anonymous Coward]

It is not that trivial, if you have to take into account race conditions and roll-back without using a global lock which would kill the performance. There are actually quite a few research papers just about this problem.

Re:Thanks a lot PostgreSQL devels

[Anonymous Coward]

I think you're confused. Momjian runs a company based on Postgresql called EnterpriseDB. Not all postgresql devs work at that company. You would have to dif deeply to figure out a percentage of how many are being paid to do what they do-- no one bothers to ask, of course, they just look at the code.

Re: Thanks a lot PostgreSQL devels

[Anonymous Coward]

On Oracle and SQL Server the merge statement adds a delete to the statement. Oracle merge is not as easy as SQL. There is also a difference between the SQL and PL SQL merge. For Oracle I recommend not using dual and using the where to avoid logging.

Personally, I have never encountered any issues with Oracle merge since we implemented 10g in 2006.

David Bowie

[Anonymous Coward]

Tell you what I'm really surprised David Bowie's death isn't headline news on Slashdot.. seeing as they now report anything for some attention whoring..

Re:David Bowie

[Anonymous Coward]

Holy shit was hoping you were trolling. RIP.

fuck

Re:David Bowie

[Zero__Kelvin]

Actually David Bowie was known for leveraging technology in his musical career. Here is the first article that pops up on Google.

I'm curious

[Tablizer]

What would be the equivalent long-hand in "traditional" SQL?

Re:I'm curious

[Anonymous Coward]

Something like this :

IF EXISTS (SELECT id FROM [table] WHERE id = :id)
THEN
      UPDATE TABLE [table] .......... WHERE id = :id
ELSE
      INSERT INTO [table] (.......) VALUES (...........)
END IF;

Re:I'm curious

[tijgertje]

Oops forgot to log in ^^

Re:I'm curious

[KiloByte]

In postgres, this can be wrapped in a RULE so it's transparent when inserting.

Re:I'm curious

[Anonymous Coward]

The MERGE command. Although PostgreSQL claimed SQL compliance, this command was not understood. Mind you, the MERGE command in standard SQL is so overly complicated that all legibility is lost. This is probably why a lot of other databases have chosen other syntaxes that work better.

Re:I'm curious

[hrumph]

Your answer is naive and wrong. See this Stack Overflow page. The solutions on this page work but none of them have the elegance of the newly available INSERT ... ON DUPLICATE UPDATE method.

Re:I'm curious

[halivar]

The GGP asked for "traditional" SQL, for which the GP offered the correct answer. You offered another vendor-specific solution, not standard ANSI. The GP was in no way wrong. If he was doing retry loops, sure; that would be wrong.

Re:I'm curious

[Anonymous Coward]

Depends on what you're doing and how you're doing it. Most databases should never erase anything, in other words, no updates (as those lose information, and information *is* money).

Traditionally if you want a value in the database, insert it. When seek out the old record and mark it as deleted. (or engineer such that you just pickup the `latest' value upon subsequent selects).

That way you get to keep a perfect log of what happened and when, and can later sell the dataset to some data mining marketing outfit.

Re:I'm curious

[halivar]

For that matter, I give him a gold star with bonus internets because he tested EXISTS instead of COUNT > 0.

Re:I'm curious

[mark-t]

INSERT ... ON DUPLICATE UPDATE will only trigger the update on what is otherwise an attempt to insert a record that will violate unique index or primary key constraints, while the above poster's solution will update any and all records that match the query, which is what UPSERT does when things match the query.

Re:I'm curious

[mrchaotica]

In terms of "traditional" SQL, the GGP's answer is wrong because it fails to be atomic. The whole thing needs to be wrapped in a transaction.

Re:I'm curious

[mark-t]

The biggest problem that I could foresee with the approach that you've suggested, particularly for very large tables where updates are being done very frequently, is that you could rapidly run out of storage.

Re:I'm curious

[tijgertje]

Thanks, although it is she, not he :)

Re:I'm curious

[Anonymous Coward]

"Traditional" ANSI SQL:2003 has had MERGE for more than a decade, which is a more verbose but significantly more powerful version of UPSERT since you can describe multiple potential actions based on whether the target record is found or based on comparisons between the source and target data. MERGE can combine multiple INSERT, UPDATE and DELETE operations into a single atomic operation.

It's sad that not only is PostgreSQL over a decade behind but they implemented the sloppy non-ANSI standard version.

Re:I'm curious

[halivar]

If someone has to be told to put a mult-statement SQL write operation in a transaction...

I kind of think that transactions go without saying, even for UPSERT (according to the Wiki page, UPSERT will "guarantee insert-or-update 'atomicity' for the simple cases", but leaves me questioning what a "simple case" is).

Re:I'm curious

[halivar]

MERGE can combine multiple INSERT, UPDATE and DELETE operations into a single atomic operation.

In PostgresSQL, Oracle, and SQL Server, MERGE is not atomic. Also, it is not UPSERT. You can use MERGE to accomplish the same end goal, but they are not synonymous and they do not work the same. Also, the syntax is idiosyncratic, and most extant implementations are problematic enough that MERGE is best avoided.

Re:I'm curious

[Zero__Kelvin]

"What would be the equivalent long-hand in "traditional" SQL?"

It doesn't exist. That's the point. This isn't implemented to save typing. It is true atomic insert or update.

Re:I'm curious

[Zero__Kelvin]

Bullshit. This is the correct answer. Until you understand that there is no equivalent to UPSERT, you don't understand UPSERT.

Re:I'm curious

[Zero__Kelvin]

You still aren't getting it. Transactions like you describe are not equivalent and have Race Condition issues that need to be handled by the client code (and that usually isn't handled cleanly) whereas UPSERT makes the transaction truly atomic, not just on a per transaction basis, but even when multiple threads attempt to handle the same kind of transaction simultaneously.

Re:I'm curious

[hrumph]

A transaction commit happens after the logic of the transaction is processed, and two or more parallell threads can start off with the same DB snapshot when processing their transactions. Supposing that one thread commits first, then the logic that the other one used in its processing will be invalid upon its turn to commit. I don't see how an UPSERT implementation would fix this. The solution arrived at by PostgreSQL is basically perfect.

Re:I'm curious

[mark-t]

It will know that the processing was invalid because the second one tried to modify records would discover that another process had locked them. The update would fail, and it would be up to the user to either reissue or modify the query.

Re:I'm curious

[Anonymous Coward]

MERGE is absolutely atomic. It either entirely fails or entirely succeeds. It isn't UPSERT because UPSERT is limited non-standard garbage.

Re:I'm curious

[Ed Avis]

There is no exact equivalent in traditional SQL. As others have pointed out, you can check exists and then insert, but that introduces a race condition; wrapping it in an explicit transaction might help depending on the locking model, but might still introduce failures that have to be introduced by client code.

That said, if you can make some assumptions about what else is writing to the table then you can get fairly close. One technique I often use is like this:

-- Insert the row if none with the same PK exists.
-- As a single SQL statement, this executes atomically
-- in all but the most braindead DBMSes.
--
insert into mytable (keycol, datacol)
select 1, 'a'
where not exists (
select 0
from mytable
where keycol = 1
)

-- OK, now there is definitely a row there with keycol=1.
-- Update the row which exists.
-- This is redundant work if we just inserted it above, but do it anyway.
--
update mytable
set datacol = 'a'
where keycol = 1

This is clearly not safe in general. If there are others accessing the table and doing general delete, or test-and-set, it can lose data. However, if you know that code like the above is the only code accessing the table, or if you restrict it to the above plus simple inserts, then you have a safe way to insert-or-update a row that doesn't involve holding locks for a period of time and doesn't require an explicit transaction.

Re:I'm curious

[hrumph]

This doesn't sound any better than the already available retry loop.....

Re:I'm curious

[mark-t]

Any automated loop would be a Bad Thing(tm). The correct thing to on any failure is to report the failure, not to simply try again... because there is no way for the computer to know that trying again is even what the person would want to do in the event of such a condition.

Re:I'm curious

[halivar]

MERGE is absolutely atomic. It either entirely fails or entirely succeeds.

That's not what atomicity means.

Re:I'm curious

[Tablizer]

So far it looks like there is no full equivalent. By not being based on sequential steps, the "UPSERT" has advantages. But the jury is still out...

Re:I'm curious

[Dunavant]

MERGE is absolutely atomic. It either entirely fails or entirely succeeds.

That's not what atomicity means.

Actually it's exactly what atomicity means in this context of a database transaction. In an atomic transaction, a series of database operations either all occur, or nothing occurs. You may be confusing it with an 'atomic operation' in programming. https://en.wikipedia.org/wiki/...

Re:I'm curious

[Anonymous Coward]

Although PostgreSQL claimed SQL compliance

Liar

Re:I'm curious

[Anonymous Coward]

It's sad that not only is PostgreSQL over a decade behind but they implemented the sloppy non-ANSI standard version.

You mean as opposed to the sloppy ANSI standard version that makes no guarantees about concurrency behaviour, something that Postgres's new feature goes to considerable lengths to handle correctly and intuitively?

Re:I'm curious

[Anonymous Coward]

MERGE is absolutely atomic. It either entirely fails or entirely succeeds.

And if used in a concurrent environment, it's quite likely to entirely fail. INSERT ... ON CONFLICT is specifically designed to avoid that.

It isn't UPSERT because UPSERT is limited non-standard garbage.

INSERT ... ON CONFLICT is limited on purpose, specifically because MERGE is so general that it's not possible to offer strong guarantees about concurrent behaviour.

Re:I'm curious

[Anonymous Coward]

Emphasis mine:

In PostgresSQL, Oracle, and SQL Server, MERGE is not atomic.

PostgreSQL still doesn't provide MERGE . Perhaps you meant to write "DB2" instead?

- T

hilarious

[Anonymous Coward]

That sounds like the "save with replace" bug on the Commodore 1541 disk drive 35 years ago. Plus ca change, eh?

Progress!

[Anonymous Coward]

Great, now all they have to do is fix the transaction isolation model and we can finally use the thing!

Re:Progress!

[Qzukk]

What's broken with the transaction isolation model? The only thing I see is that they don't do the non-transaction "read uncommitted" transaction that lets you see records that other transactions have not committed.

Oracle was there first

[Ora*DBA]

Oracle has included the 'merge' command for several iterations now, which does the same thing. It is a rich command - those interested can check the documentation at https://docs.oracle.com/database/121/SQLRF/statements_9016.htm#SQLRF01606

Re:Oracle was there first

[Anonymous Coward]

Oracle has included the 'merge' command for several iterations now, which does the same thing.

Wrong. It does not do the same thing - it's subject to race conditions, which you have to account for yourself if you want to use it in the situations INSERT ... ON CONFLICT was designed for.