Postgres gets support for upsert
git.postgresql.org
git.postgresql.org
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.
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).
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.
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.
> Important: To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used, even if the transaction that did the nextval later aborts. This means that aborted transactions might leave unused "holes" in the sequence of assigned values.
You can't give out the same number to multiple transactions without causing a bunch of unique constraint violations. And you can't retroactively decrease a sequence number without affecting all the other transactions currently executing. You could in theory go back and plug in the gaps in a sequence, but it's more important to guarantee that a single thread of execution will always see increasing numbers than it is to guarantee that all numbers will eventually be handed out to a successful transaction.
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.
pgsql -> on conflict
mysql -> replace / on duplicate
oracle -> merge
mssql -> merge
sqlite -> insert or replace
firebird -> merge / update or insert
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.
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.
Both have different use cases.
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.
And on the flip side, it's a lot more complicated to write, and difficult even to remember the syntax (at least so far).
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).
As you can see from the commit, too, this was not a trivial feature. So yes, I'm overall pleased.
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...
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.
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.
And if MySQL wasn't robust then YouTube, Facebook, Twitter, Alibaba, LinkedIn etc wouldn't be using it for core parts of their infrastructure. It's definitely robust.
No doubt that PostgreSQL is better at MySQL in many areas though and probably could do with a self contained, single download PostgreSQL Cluster edition.
Either that or they have staff and infrastructure in place to deal with the lack of robustness. Yes, you need to be prepared to deal with corruption anyways, but the more robust your solution, the more time is left to deal with other things.
Over the years I have seen multiple instances of MySQL table corruption, index corruption and mysqldump exiting with a zero exit code after aborting mid-dump due to table corruption.
Of course I was prepared for this and I always had backups ready, but it was still time-consuming and annoying.
With Postgres I've yet to see any kind of data corruption even though my postgres usage is much heavier than my MySQL usage.
But this is why I said "anecdotal evidence": For me personally, Postgres has proven to be way more robust than MySQL. Is this my inability to properly administer MySQL? Is it me being unlucky with hardware (though Postgres is fine on the same hardware)? Is it me just being unlucky? I really don't know.
Popularity is not an argument for quality. See: crocs, Justin Bieber, PHP.
Those companies you mentioned, like many others, probably use it because they're locked in that technology, not because it's a superior one. Just like banks still use COBOL.
If you're interested on knowing why using MySQL isn't a good idea, there's this (highly opinionated, obviously) post about it: http://grimoire.ca/mysql/choose-something-else
What I am saying is that if MySQL wasn't robust then those companies simply wouldn't be using it. Since at their scale any bug or weakness will manifest at a level far greater than say at a startup. And they have the skills, time and money to choose any technology they wan't so I don't buy your argument that they are "locked in". Some like Facebook and LinkedIn have even created their own databases.
And I never said MySQL was a good idea. You did. I am saying that to claim it is not robust flies in the face of available evidence.
I'd tend to think your statement:
"And if MySQL wasn't robust then YouTube, Facebook, Twitter, Alibaba, LinkedIn etc wouldn't be using it for core parts of their infrastructure. It's definitely robust."
..points out that many (popular) sites use it, and a prerequisite is robustness, which thesaurus-wise, sounds a lot like quality.
I get that
a) you didn't actually say it, and
b) you could mean something much more specific, such as "companies with many highly-starred, complex open source projects which have also re-written major parts of their tech stack, but chose to leave mysql in place".
I know thats a bit more verbose, but it just seems to obviously close to the other author's interpretation with the ambiguity of the statement.
threeseed's argument is not
this handful of popular companies use it, therefore it is good
this handful of companies, each of which is considered by some/many to be 'excellent' or 'top', use it, therefore it is goodThat conclusion does not follow from the premises: that some well known large companies use a technology in no way implies that it is robust.
You are assuming that large, well known companies always intrinsically select for high robustness in their tools, which is not necessarily the case. In fact, there are many well known public cases of the opposite.
It could be (for example) that large companies simply have the spare organizational capacity to deal with a lack of robustness. Perhaps they are locked in and find the ongoing cost of dealing with lack of robustness to be lower than the costs of switching to something more robust. Or it could be that they don't notice, for whatever reason, the lack of robustness. There could be many other possible explanations.
I am glad you mention the word "evidence", since you have provided none so far. An assorted selection of popular, buzz-worthy sites does not qualify as evidence (or lack thereof) of the validity of the technologies they use and endorse.
For contrast, I linked to a really nice post that explains thoroughly why MySQL is not a good idea in the slightest. I would encourage you to read it.
> MySQL was never more featureful
Back then, there was just MyISAM (and maybe even still ISAM), no transactions, no replication, no upserts. Back then there was nothing that MySQL could do that Postgres couldn't.
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.
Edit: munro answered that. PostgreSQL does it but it's expensive because it has to rewrite the table.
id
created_at
updated_at
status
fkey1_id
fkey2_id
fkey3_id
date_field1
date_field2
column1
column2
column3
http://www.postgresql.org/message-id/flat/20141209174146.GP1...
This was the most requested feature in PostgreSQL: https://postgresql.uservoice.com/forums/21853-general
[1] https://wiki.postgresql.org/wiki/UPSERT#SQL_MERGE_syntax
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?
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!
Way to go Postgres team!
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);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.
C.f. http://www.postgresql.org/docs/9.4/static/plpgsql-control-st...
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.
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
I was so surprised when I ran into the lack of this feature as I'd had it on both MSSQL and MySQL.
I was really missing SQL Server's powerful MERGE statement, so this is welcome news.
1. INSERT .. ON CONFLICT is an implementation UPSERT technique
2. MERGE is something meant to be different from UPSERT but has UPSERT feature?
I'll get my coat.
EDIT: rather the whole "Value Locking" page - https://wiki.postgresql.org/wiki/Value_locking
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
Get off my lawn, etc.