[development] Do not let postgresql hold back great patches

[Ivan Sergio Borgonovo]

On Wed, 28 Nov 2007 11:48:23 +0100
"Chris Johnson" <cxjohnson at gmail.com> wrote:

> It's easier to find work-arounds and translations from standard SQL
> to MySQL syntax, and do the same thing to translate to another
> reasonable database (Posgres, Oracle, MSSQL), than it is to reverse
> translate MySQL (or other database) specific features to other
> databases.

Agree.

> Standard SQL is the sort of the greatest common factor (in the
> mathematical sense).  Even more significantly, hundreds of MySQL
> people have spent significant effort in coming up with standard SQL
> equivalents in MySQL, so most of that work is already done for us.

> Maybe there is an open-source tool which can validate standard SQL
> which could be used.  Then our DB abstraction could require a
> verifiable standard SQL syntax which would then be translated (where
> necessary) into the MySQL, Oracle, Postgres, etc. equivalents.
> This might only be for CRUD operations (data manipulation
> statements) and not for DDL, for which we already have a reasonable
> API and which are much less standardized.

On the long run this doesn't give space for optimisations.
People should write SQL with standards in mind and not MySQL in mind
but the DB abstraction layer should leave a door open for
optimisation.
There should be a default path that doesn't impact on performances
(including both mysql and pgsql code) that let module writers write
custom code for a particular DB *if* they are willing to do so.
There was a thread that share some aspect with this problem about
"numbers of loaded module and performance" and guidelines for
module developers.

> I myself always use MySQL for my Drupal installs -- because it will
> work.  Thus, I never test them with Postgres, even though I run
> Postgres on several systems, am very comfortable with using it, etc.
> I just don't have the time to hassle with discovering some obscure
> SQL bug resulting from database incompatabilitilies when I'm just
> trying to make a site work.

I use pgsql for drupal and till now I installed stable versions of
drupal where most stuff was working out of the box in core (there was
a minor issue in length of watchdog messages where MySQL silently trim
stuff while pg complains) and fixes for the modules I use were
trivial.

I've to learn how to use the drupal "patch/issues" infrastructure to
give more chances the patch I made get into modules/core smoother.
I still have to read carefully all the coding standards.

> So unless we actually push the issue of supporting multiple
> databases as a community, the vast majority of developers will
> never make the effort.  And with each passing release spent in that
> mode, the harder it will become to reverse it.  We will end up
> supporting only one database, MySQL, de facto.

Absolutely agree. But it is a problem of chicken and egg. dev don't
care cos there isn't enough abstraction in DB layer and the code is
too mysqlish, the code continue to be that way because most dev are
surmounted/ignore how to really deal with multiple DB.

I sympathise with Chris Johnson. I understand the frustration of core
dev when they have to deal with the latitancy/scarcity of pg dev
trying to improve/give suggestions etc... about performance on pg.
But again this is a problem of chicken and egg. Now everything that
is going to change/add work to the MySQL side is seen as an annoyance
because it is actually complicated to improve stuff for pg without
degrading performances for MySQL.

In the light of the fact that MySQL is going to have the same
features pg had for years, I would think it would be a good bet to
improve the abstraction layer for BOTH so to be able to use the most
advanced features that BOTH will have. Anyone that didn't deal with
MyISAM only know there are places where some of these features can
improve performances and code writing *a lot*.

I think most people here know there is no silver bullet when you've
to deal with flexibility and performance and DB abstraction layers
are not an exception.

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it