PostgresQL vs MySQL

Mon application a été construite sur PostgresQL, système de base de données auquel on accède par JDBC. Dès le départ il a été envisagé d'utiliser d'autres SGDBs. Nous avons toutefois attendu une demande formelle avant de passer à l'acte.
C'est ce qui s'est passé récemment avec un utilisateur confessant que notre Saada serait encore plus génial s'il pouvait utiliser MySQL en lieu et place de Postgres et que autrement il ne lui serait d'aucun intérêt.
Vous trouverez plus loin un bestiaire des incompatibilités SQL entre les deux systèmes. Il ne s'agit pas ici de comparer mais simplement de pointer les différences.

Les transactions

L'ouverture de transaction permet de rendre atomique l'exécution d'un ensemble de requêtes. Si l'une plante, la base retombe dans son état initial (rollback) quelque soit les modifications déjà effectuées avant le plantage.

	Postgres	MySQL
Ouvrir la transaction	BEGIN TRANSACTION	START TRANSACTION
Terminer la transaction	COMMIT	COMMIT
Annuler la transaction	ABORT	ROLLBACK

Une différence plus importante concerne le verrouillage des tables. Sous Postgres, le verrouillages des tables durant une transaction est implicite. Il n'y a pas à s'en occuper.
Sous MySQL, il faut verrouiller explicitement toutes les tables auxquelles on accède durant la transaction.

• Si au cours de la transaction une requête modifie la table TABLE_W il faut la faire précéder par LOCK TABLE TABLE_W WRITE


• Si au cours de la transaction une requête lit la table TABLE_R il faut la faire précéder par LOCK TABLE TABLE_R READ


• Si au cours de la transaction une requête lit les tables TABLE_R1 et TABLE_R2 et qu'elle modifie la table TABLE_W, il faut la faire précéder par LOCK TABLE TABLE_R1, TABLE_R2 READ, TABLE_W WRITE

Les tables temporaires

Un petit bonnet d'âne à MySQL: La partie Web des bases Saada utilise un rôle (compte) d'accès à la base avec des droits réduits (le reader) de manière a éviter à des requêtes malicieuses d'altérer les données. Seulement voila, certaines requêtes compliquées utilisent des tables temporaires. Cela nous permet d'éviter des jointure compliquées dont on ne sait jamais comment l'optimiseur du moteur de requête se sortira.
Sous Postgres, il n'y a rien à faire de particulier, le reader peut créer ses tables temporaires.
Sous MySQL, la musique est bien différente. Il faut donner explicitement au reader le droit de créer des tables temporaires:GRANT CREATE TEMPORARY TABLES ON database TO reader

Le chargement de données à partir de fichiers

Lors du chargement de donnée dans MySQL à partir d'un fichiers ASCII (LOAD DATA INFILE...) les contraintes de clés primaires sont vérifiées ligne par ligne, et ça rame vraiment beaucoup. La commande SQL suivante permer de dévalider temporairement les contraintes sur la clé primaire.
ALTER TABLE tbl_name DISABLE KEYS