38.10. Les dessous de PL/pgSQL

Quand PL/pgSQL prépare l'exécution d'une instruction SQL ou d'une expression, tout nom de variable PL/pgSQL apparaissant dans l'instruction ou l'expression est remplacée par un symbole de paramètre, $n. La valeur actuelle de la variable est alors fournie en tant que valeur du paramètre quand l'instruction ou l'expression est exécutée. Comme exemple, considérez la fonction suivante :

CREATE FUNCTION logfunc(logtxt text) RETURNS void AS $$
    DECLARE
        curtime timestamp := now();
    BEGIN
        INSERT INTO logtable VALUES (logtxt, curtime);
    END;
$$ LANGUAGE plpgsql;

L'instruction INSERT sera en fait traitée comme :

PREPARE nom_instruction(text, timestamp) AS
  INSERT INTO logtable VALUES ($1, $2);

suivie par chaque exécution (EXECUTE) avec les valeurs réelles des deux variables. (Note : ici nous parlons de la commande EXECUTE du moteur SQL, pas du EXECUTE de PL/pgSQL.)

Le mécanisme de substitution remplacera tout jeton qui correspond au nom d'une variable connue. Ceci est un risque pour le débutant. Par exemple, c'est une mauvaise idée d'utiliser un nom de variable qui est identique à un nom de table ou de colonne que vous comptez référencer dans les requêtes d'une fonction car ce que vous pensez être un nom de table ou de colonne sera toujours remplacé. Dans l'exemple ci-dessus, supposez que logtable a les colonnes logtxt et logtime, et que nous essayons d'écrire un INSERT ainsi :

        INSERT INTO logtable (logtxt, logtime) VALUES (logtxt, curtime);

Ceci sera renvoyé à l'analyseur SQL de cette façon :

        INSERT INTO logtable ($1, logtime) VALUES ($1, $2);

résultant en l'erreur de syntaxe suivante :

ERROR:  syntax error at or near "$1"
LINE 1: INSERT INTO logtable ( $1 , logtime) VALUES ( $1 ,  $2 )
                               ^
QUERY:  INSERT INTO logtable ( $1 , logtime) VALUES ( $1 ,  $2 )
CONTEXT:  SQL statement in PL/PgSQL function "logfunc2" near line 5

Cet exemple est assez simple à diagnostiquer car il ramène une erreur de syntaxe évidente. Il existe des cas plus complexes où la substitution est syntaxiquement autorisée car le seul symptome est un comportement erroné de la fonction. Dans un cas, un utilisateur avait écrit ceci :

    DECLARE
        val text;
        search_key integer;
    BEGIN
        ...
        FOR val IN SELECT val FROM table WHERE key = search_key LOOP ...

et se demandait pourquoi toutes ses entrées de table semblaient valoir NULL. Bien sûr, ce qui était survenu était la transformation suivante de la requête :

        SELECT $1 FROM table WHERE key = $2

et, du coup, c'était juste une façon complexe d'affecter la valeur courante de val à elle-même pour chaque ligne.

Une règle de codage généralement acceptée pour éviter ce type de problème est d'utiliser une convention de nommage différente pour les variables PL/pgSQL et pour celles de vos tables et colonnes. Par exemple, si toutes vos variables sont nommées v_quelquechose alors qu'aucune de vos tables ou colonnes ne commence par v_, le risque est bien moindre.

Un autre contournement est d'utiliser des noms qualifiés pour les entités SQL. Par exemple, l'exemple suivant peut être ré-écrit de la façon suivante :

        FOR val IN SELECT table.val FROM table WHERE key = search_key LOOP ...

parce que PL/pgSQL ne substitutera pas une variable pour le composant de fin d'un nom qualifié. Néanmoins, cette solution ne fonctionne pas dans tous les cas -- vous ne pouvez pas qualifier un nom dans la liste des colonnes d'une commande INSERT par exemple. Un autre point est que les noms de variables record ou ligne correspondront aux premiers composants des noms qualifiés, donc un nom SQL qualifié est toujours vulnérable dans certains cas. En fait, dans certains cas, choisir un nom de variable sans conflit est le seul moyen.

Une autre technique que vous pouvez utiliser est d'attacher un label au bloc dans lequel sont déclarées vos variables, puis de qualifier les noms des variables dans vos commandes SQL (voir la Section 38.2, « Structure de PL/pgSQL »). Par exemple :

    <<pl>>
    DECLARE
        val text;
    BEGIN
        ...
        UPDATE table SET col = pl.val WHERE ...

Ce n'est pas en soi une solution au problème des conflits car un nom non qualifié dans une commande SQL courera toujours le risque d'être interprétée de la « mauvaise » façon. Par contre, c'est utile pour clarifier le but d'un code potentiellement ambigü.

La substitution de variable n'arrive pas dans la chaîne de commande donnée à EXECUTE ou une de ces variantes. Si vous avez besoin d'insérer une valeur dans une telle commande, faites-le lors de la construction d'une valeur de chaîne, illustrée dans la Section 38.5.4, « Exécuter des commandes dynamiques ».

La substitution de variable fonctionne seulement dans les commandes SELECT, INSERT, UPDATE et DELETE parce que le moteur SQL principal autorise les symboles de paramètres seulement dans ces commandes. Pour utiliser un nom variable ou une valeur dans les autres types d'instructions (généralement appelées des instructions utilitaires), vous devez construire l'instruction en question comme une chaîne et l'exécuter via EXECUTE.

L'interpréteur PL/pgSQL analyse le source d'une fonction et produit un arbre binaire interne d'instructions la première fois que la fonction est appelée (à l'intérieur de chaque session). L'arbre des instructions se traduit complètement par la structure d'instructions PL/pgSQL mais les expressions et les commandes SQL individuelles utilisées dans la fonction ne sont pas traduites immédiatement.

Au moment où chaque expression et commande SQL est exécutée en premier lieu dans la fonction, l'interpréteur PL/pgSQL crée un plan d'exécution préparée (en utilisant les fonctions SPI_prepare et SPI_saveplan du gestionnaire SPI ). Les appels suivants à cette expression ou commande réutilisent le plan préparé. Du coup, une fonction avec du code conditionnel contenant de nombreuses instructions pour lesquels les plans d'exécutions pourraient être requis vont seulement préparer et sauvegarder ces plans qui sont utilisés tout au long de la durée de vie de la connexion à la base de données. Ceci peut réduire substantiellement le temps total requis pour analyser et générer les plans d'exécution pour les instructions d'une fonction PL/pgSQL. Un inconvénient est que les erreurs dans une expression ou commande spécifique ne peuvent pas être détectées avant que la fonction a atteint son exécution. (Les erreurs de syntaxe triviales seront détectées à la première passe d'analyse mais quelque chose de plus complexe ne sera pas détecté avant son exécution.)

Une fois que PL/pgSQL a réalisé un plan d'exécution pour une commande particulière d'une fonction, il ré-utilisera ce plan pour toute la durée de vie de la connexion à la base. Ceci est un gain majeur pour les performances mais cela posera des problèmes si vous modifiez dynamiquement votre schéma de bases de données. Par exemple :

CREATE FUNCTION peupler() RETURNS integer AS $$
DECLARE
    -- declarations
BEGIN
    PERFORM ma_fonction();
END;
$$ LANGUAGE plpgsql;

Si vous exécutez la fonction ci-dessus, elle référencera l'OID de la fonction ma_fonction() dans le plan d'exécution produit par l'instruction PERFORM. Plus tard, si vous supprimez puis re-créez la fonction ma_fonction(), alors peupler() ne sera plus capable de trouver ma_fonction(). Vous devrez alors commencer une nouvelle session à la base de données pour que peupler() soit compilée de nouveau et qu'elle soit de nouveau utilisable. Vous pouvez éviter ce problème en utilisant CREATE OR REPLACE FUNCTION lors de la mise à jour de la définition de ma_fonction car, quand une fonction est « remplacée », son OID n'est pas modifié.

Comme PL/pgSQL sauvegarde les plans d'exécution de cette façon, les commandes SQL qui apparaissent directement dans une fonction PL/pgSQL doivent faire référence aux même tables et aux mêmes colonnes à chaque exécution ; c'est-à-dire que vous ne pouvez pas utiliser un paramètre comme le nom d'une table ou d'une colonne dans une commande SQL. Pour contourner cette restriction, vous pouvez construire des commandes dynamiques en utilisant l'instruction EXECUTE de PL/pgSQL -- au prix de la construction d'un nouveau plan d'exécution à chaque exécution.

Un autre point important est que les plans préparés utilisent des paramètres pour permettre le changement des valeurs des variables PL/pgSQL entre chaque exécution, comme indiqué en détail ci-dessus. Quelque fois, cela signifie qu'un plan est moins efficace que s'il avait été généré avec une valeur spécifique. Comme exemple, regardez :

SELECT * INTO mon_enregistrement FROM dictionnaire WHERE mot LIKE terme_recherche;

où terme_recherche est une variable PL/pgSQL. Le plan caché pour cette requête n'utilisera jamais un index sur une variable. Le plan caché pour cette requête n'utilisera jamais un index sur un mot car le planificateur ne peut pas savoir si le modèle LIKE comprendra une ancre à gauche à l'exécution. Pour utiliser un index, la requête doit être planifiée avec un modèle spécifique pour LIKE. C'est un autre cas où EXECUTE peut être utilisé pour forcer la génération d'un nouveau plan à chaque exécution.

La nature muable des variables de type record présente un autre problème dans cette connexion. Quand les champs d'une variable record sont utilisés dans les expressions ou instructions, les types de données des champs ne doivent pas modifier d'un appel de la fonction à un autre car chaque expression sera planifiée en utilisant le type de données qui est présent quand l'expression est atteinte en premier. EXECUTE peut être utilisé pour contourner ce problème si nécessaire.

Si la même fonction est utilisée comme trigger pour plus d'une table, PL/pgSQL prépare et met en cache les plans indépendament pour chacune de ses tables -- c'est-à-dire qu'il y a un cache pour chaque combinaison fonction trigger/table, pas uniquement pour chaque fonction. Ceci diminue certains des problèmes avec les types de données variables ; par exemple, une fonction trigger pourra fonctionner correctement avec une colonne nommée cle même si cette colonne a différents types dans différentes tables.

De la même façon, les fonctions ayant des types polymorphiques pour les arguments ont un cache séparé des plans pour chaque combinaison des types d'argument réél avec lesquels elles ont été appelées, donc les différences de type de données ne causent pas d'échecs inattendus.

La mise en cache du plan peut parfois avoir des effets surprenants sur l'interprétation des valeurs sensibles à l'heure. Par exemple, il y a une différence entre ce que font ces deux fonctions :

CREATE FUNCTION logfunc1(logtxt text) RETURNS void AS $$
    BEGIN
        INSERT INTO logtable VALUES (logtxt, 'now');
    END;
$$ LANGUAGE plpgsql;

et :

CREATE FUNCTION logfunc2(logtxt text) RETURNS void AS $$
    DECLARE
        curtime timestamp;
    BEGIN
        curtime := 'now';
        INSERT INTO logtable VALUES (logtxt, curtime);
    END;
$$ LANGUAGE plpgsql;

Dans le cas de logfunc1, l'analyseur principal de PostgreSQL™ sait que, au moment de la préparation du plan pour INSERT, la chaîne 'now' devra être interprétée comme une valeur de type timestamp car la colonne cible de logtable est de ce type. Du coup, 'now' sera converti en une constante quand INSERT est planifié, puis utilisé dans tous les appels à logfunc1 lors de la durée de vie de la session. Il n'est pas nécessaire de préciser que ce n'est pas le souhait du développeur.

Dans le cas de logfunc2, l'analyseur principal de PostgreSQL™ ne connaît pas le type que deviendra 'now' et, du coup, il renvoie une valeur de type text contenant la chaîne now. Lors de l'affectation à la variable curtime locale, l'interpréteur PL/pgSQL convertie cette chaîne dans le type timestamp en appelant les fonctions text_out et timestamp_in pour la conversion. Du coup, l'heure calculée est mise à jour à chaque exécution comme le suppose le développeur.