[support] Drupal: MySQL vs. PostgreSQL

[Pierre Rineau]

Le mercredi 21 juillet 2010 à 16:56 +0200, Ivan Sergio Borgonovo a
écrit :
> 
> It depends on what you call SQL. Depending on definition of SQL many
> DB including postgresql aren't SQL compliant. [1]
> 
> Most of drupal modules fail to work on DB other than MySQL simply
> because they liberally and for no reason use MySQL quirks at the SQL
> language "layer", let alone any consideration for ACID stuff. Drupal
> itself just recently gained basic support for transactions.
> 
> I still have to discover if in D7 eg. modules that add details to
> users can be wrapped in a transaction so that users don't get
> created mangled, same for nodes etc...
> 
> Then if there is a mature support for transactions in core all this
> will have to percolate into modules otherwise it won't be that
> useful. This will be much trickier than seeing module developers
> write nice SQL.
> 
> Support for transactions will make Postgresql much more popular and
> maybe a better choice for more sites.
> 
> [1] we could digress about when DB implementations divert from
> standards for a reason (implementing features or avoiding SQL
> standard dark areas) or when they do it for ignorance, laziness,
> boldness... the same approach reflects in DB users...

Don't worry, it was just a flame :) SQL standard has never been fully
supported by any DBMS I know (and, by the way, SQL is only a language
after all).

D7 database layer supports foreign key definition, but like the old
MyISAM engine, just ignore it (it's only semantics for the schema API).
And for transactions, I don't know, I hope they did the twice magical
functions start_transaction() and stop_transaction() (I really hope they
did it).

<flame warning="If you are expecting good info, stop reading here!">

I worked on business applications strongly database oriented, and did a
lot of triggers and procedures with MySQL, this is HELL!

There is some really annoying stuff with MySQL, each time you do a
complex query ("complex" is relative), you have to test it disabling the
MySQL internal query optimizer, because this big guy often does crap.

Also, when playing with databases that have some years behind them, you
sometime find MyISAM and InnoDB tables playing together, just try to
debug why arbitrary simple queries fail with, as the only error message,
a number (yes, foreign keys on InnoDB tables pointing to MyISAM tables,
it just does not work, even worst, it crashes some queries arbitrary,
when playing with MyISAM only you can still define them but they are
just ignored and all works perfectly, except for data consistency).

> SELECT name FROM dbms WHERE working_with IS LIKE '%FUCKING%HELL%';
MySQL.
1 row(s).

</flame>

Pierre.