Re: "Too far out of the mainstream" - Mailing list pgsql-general

On Wed, Sep 5, 2012 at 7:56 PM, Edson Richter <edsonrichter@hotmail.com> wrote:

Em 05/09/2012 23:49, Chris Travers escreveu:

Regarding MySQL vs PostgreSQL:

MySQL is what you get when app developers build a database server.
PostgreSQL is what you get when db developers build a development platform.

There really isn't anything more to say about it.

This kind of claim is just to feed flame wars. Don't think I need to state that a "db developer" becomes a "app developer" as soon as he start to develop any database server code, right?

I don't mean it that way.

Ok, understood. My point here was just to return focus to the main question, and avoid feed the trolls :-)

The basic thing is that MySQL's view of data integrity is extremely application centric.  Even today, applications get to tell the server whether to throw an error when you try to insert 0000-00-00 into a date field (this is via the sql_mode setting and admins can't restrict what an app can do there).  MySQL makes perfect sense when you are an application developer looking at the database as a place to store information for your own private use.     In essence, MySQL makes perfect sense when you realize that "my" = "private" in OO terms.

Yes, I agree. Nothing professional can run this way, but for personal purposes, you can even call "access", "dbf" or "Isis txt format" a database.

This isn't necessarily a bad thing if that's what you are using it for, and because of ways the db market has developed there are a huge number of developers who are very happy with a lowest common denominator RDBMS where you can assume one app writing to the db (or at least any given relation), and possibly other apps reading.  In short if you want an easy db to port SQL code that was intended to be portable to, MySQL is the RDBMS for you.  For people who want to avoid putting business logic in the db, and want to put all the API's for interoperability and integration in their app logic, it's a good RDBMS.  In fact, I can't actually think of better.  This is *especially true* if you want to make it dangerous for other apps to write to the db, perhaps in order to say this is not supported and ask people to purchase more client access licenses....

Actually, for web based applications, developers are forced to add validation at several levels. But is still database responsibility to accept or reject the data that will persist for a lifetime (sometimes less).

MySQL behavior that seems "incorrect" is not necessarily "incorrect" in that context.  It's a data store for one app to write to and optionally other apps to read from.  The app can be trusted to not do crazy things with sql_mode settings or the like, and if it does, whatever the app tells the db is correct behavior, the db is supposed to do.

It is incorrect in a way to by the MySQL behavior, data will get corrupt in a very short of time, I know because I tried with application that run perfectly well in PostgreSQL and get corrupt in a very short of time when using MySQL. The same statement is true for Access and DBF in any multi user scenario.

PostgreSQL on the other hand has been engineered from the beginning (as I understand it) with the idea that you have multiple applications writing to the same relations.  So a lot of the things like sql_mode settings, which are great for porting applications to MySQL, would be dangerous in a PostgreSQL context.  The relations are a public API, while in MySQL they are at least semi-private.  Additionally from the beginning you have had a very strong emphasis on being able to do advanced data modelling in PostgreSQL perhaps to an extent even today unparalleled elsewhere.  If you are going to do db-level programming in PostgreSQL, you shouldn't IMO think like an application developer but rather like a database developer.

What I am getting at is that if you are an app developer looking at databases, MySQL looks fine, and the warts more or less match how you would tend to think a db should act anyway.  If you are a db developer, PostgreSQL tries hard where we all agree on correct db behavior to do the right thing without respect to what the app might have intended.  On the other hand, this is mostly a platform for data modelling, and if you are an app developer a lot of things will seem weird in that context until you get used to it.

Like it or not, the perspectives are very different.  If all you want is an information store for your app with reporting capabilities, then you end up with a different solution then if you want to manage data in a centralized way.

Of course. But remember that writing wrong applications is wrong by design, and its cause are the wrong decisions. The main problem is the people behind and its behavior, not the role they play. Remember that there is not software without bugs, there is software that has been poorly tested. Since we have bugs, we need to have a great community, open for discussions (like this one), and humility to accept that we can always improve. PostgreSQL has all of this, MySQL (and some other opensource and commercial databases) not.


Regards,

Edson.

Best Wishes,

Chris Travers