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

I don't mean it that way.

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.

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....

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.

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.

Best Wishes,

Chris Travers