PL/PgSQL : introduction

Qu’est-ce que PL/pgSQL ?

Voici mot pour mot la définition de PL/pgSQL d’après la documentation de PostgreSQL :

PL/pgSQL est un langage de procédures chargeable pour le système de bases de données PostgreSQL™. Les objectifs de la conception de PL/pgSQL ont été de créer un langage de procédures chargeable qui

  • est utilisé pour créer des fonctions standards et triggers,

  • ajoute des structures de contrôle au langage SQL,

  • permet d’effectuer des traitements complexes,

  • hérite de tous les types, fonctions et opérateurs définis par les utilisateurs,

  • est défini comme digne de confiance par le serveur,

  • est facile à utiliser.

Maintenant, d’une manière simplifiée, PL/pgSQL est un langage qui permet d’ajouter au sql des fonctions intéressantes, notamment des boucles et des instructions conditionnelles. Il est l’équivalent du PL/Sql pour oracle.

Pourquoi utiliser le PL/pgSQL ?

1 – Pour sa rapidité d’exécution

Partons du postulat suivant : soit une table test, contenant un seul champ (test_cod) qui est une clé primaire. Je veux boucler 1000 fois pour insérer une valeur dans cette clé. Entre chaque test, nous allons vider la table test, et faire un vacuum analyze full afin de ne pas fausser les résultats.

Méthode 1 : php (pdo) sans transactions :

Voici le code suivant :

myChromePhp::timer('log');
$req = "insert into test (test_cod) values (?)";
$stmt = $db->prepare($req);
for($i=0;$i<=1000;$i++) 	
   $stmt = $db->execute(array($i),$stmt);
myChromePhp::timer('log');

D’après le timer myChromePhp, le temps d’exécution est de 18557 ms.

Méthode 2 : php (pdo) avec transactions.

Le code précédent n’est pas du tout optimisé. En effet, chaque insert provoque une transaction unitaire, et la page effectue dont 1000 transactions. Afin d’améliorer un peu tout ça, nous allons ajouter la gestion des transactions :

myChromePhp::timer('log');
$db->Begin();
$req = "insert into test (test_cod) values (?)";
$stmt = $db->prepare($req);
for($i=0;$i<=1000;$i++)    
   $stmt = $db->execute(array($i),$stmt);
$db->Commit();
myChromePhp::timer('log');

Dans ce cas, nous obtenons une diminution drastique du temps d’éxécution, qui passe à 440 ms.

Méthode 3 : utilisation d’une fonction PL/pgSQL

Nous allons écrire une fonction PL/pgSQL qui effectue exactement les mêmes actions :

CREATE OR REPLACE FUNCTION test()
  RETURNS text AS
$BODY$declare
 compteur integer;
begin
compteur := 0;
LOOP
    insert into test (test_cod) values (compteur);
    compteur := compteur + 1;
    EXIT WHEN compteur >= 1000;
END LOOP;
return 'ok';
end;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Nous modifions maintenant le code php :

myChromePhp::timer('log');
$req = "select test()";
$stmt = $db->execute($req);
myChromePhp::timer('log');

Et nous atteignons maintenant un temps record de 78 ms !

2 – Pour son portabilité et son indépendance

Une fonction PL/pgSQL est liée à la base de données PostgreSQL. Elle ne dépend donc plus d’un langage procédural externe. Vous pouvez donc l’appeler depuis n’importe quelle langage qui permet de faire une requête à votre base (php, asp, shell, …). Si vous devez un jour déménager votre projet, vous n’aurez pas à réécrire la totalité des programmes dans le nouveau langage.

Si le langage appelant est surchargé, vous risquez d’alourdir chacune de vos boucles, mais ce ne sera pas le cas avec une fonction qui ne fait appel qu’à la base de données.

3 – Pour la gestion des transactions

Chaque fonction est transactionnelle. Un commit est effectué si la fonction s’est bien déroulée, sinon, il y a un rollback automatique. Il n’y a donc plus de transactions à gérer en php (ou autres).

Les inconvénients du PL/pgSQL

Il n’existe pas de méthode facile pour faire du debug en PL/pgSQL. Il y a bien une gestion des exceptions, mais elle devient très rapidement lourde. De plus, il est quasiment impossible de mettre des points d’arrêts efficace.

De plus, le explain plan ne fonctionne pas avec une fonction, il ne donnera qu’une ligne de résultats, sans détailler les éventuels points de ralentissements. Si une de vos fonctions est trop longue, il faut la découper, et tester requête par requête votre explain plan.

Il existe une méthode pour pallier ces deux inconvénients : la fonction raise qui permet d’afficher certaines informations.

Dernier argument, et non des moindres : PL/pgSQL est un langage liée à postgreSQL. Si votre projet comprend des fonctions PL/pgSQL, il sera difficile à porter vers d’autres bases de données (Oracle, Mysql…). Il est donc important de ne choisir cette voie avec précaution, quand vous êtes certain de ne pas avoir à migrer vers d’autres horizons.

Laisser un commentaire

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.