Which version of Postgres will this land in?
9.5, that is, upcoming!
Which will hopefully drop in September.
Postgres gets support for upsert
51–60 of 114 posts
Re: Postgres gets support for upsert
#52Earlier quoted context omitted.
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 proce…
> MySQL was never more featureful
Re: Postgres gets support for upsert
#53Earlier quoted context omitted.
...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 implementati…
Is my conclusion correct that this implies sequence increments are run outside of the transaction?
Yes indeed.
Re: Postgres gets support for upsert
#54Earlier quoted context omitted.
...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 implementati…
Is my conclusion correct that this implies sequence increments are run outside of the transaction?
Yes: http://www.postgresql.org/docs/9.4/static/functions-sequence...
> 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.
Re: Postgres gets support for upsert
#55Earlier 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…
MariaDB, WebScaleSQL, Percona are just three MySQL forks that are completely open source in every single way. Likewise MySQL-Server is GPL so it definitely should be considered as open source. 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 th…
I think mysql-tests aren't open-source anymore (gift from oracle)
Re: Postgres gets support for upsert
#56Earlier quoted context omitted.
...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 implementati…
Is my conclusion correct that this implies sequence increments are run outside of the transaction?
Sequences in Postgres are updated even if a transaction is rolled back. It's pretty easy to see why that's important, especially when dealing with concurrent transactions.
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.
Re: Postgres gets support for upsert
#57Earlier 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.
The context of my comment and the comment it responded to was the ~7.1 time-frame which was in 2000. My "never more featureful" comment was related to what was released in 2000.
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.
Re: Postgres gets support for upsert
#58Earlier quoted context omitted.
MariaDB, WebScaleSQL, Percona are just three MySQL forks that are completely open source in every single way. Likewise MySQL-Server is GPL so it definitely should be considered as open source. 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 th…
> 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. 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…
I never said popularity was an argument for quality. You did.
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.
Re: Postgres gets support for upsert
#59I 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 not very familiar with those specific features in other dbms, but in mysql's case "on duplicate" and "replace" are two very different thing. The first is an upsert, while the other drops the old row if it exists and always make a new one.
Both have different use cases.
Re: Postgres gets support for upsert
#60Earlier quoted context omitted.
MariaDB, WebScaleSQL, Percona are just three MySQL forks that are completely open source in every single way. Likewise MySQL-Server is GPL so it definitely should be considered as open source. 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 th…
> 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. 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…
As an enthusiastic wearer of Crocs I have to interject to point out that there are many different aspects to quality - being robust is just one of them. Being able to easily recruit experts, ease of use, availability and quality of tools in the ecosystem are other measures.