# PL/pgSQL avancé
> Transcription française complète du chapitre « PL/pgSQL avancé » du manuel DEV42, révision 26.09, pages PDF 257 à 324.
---
Index LLMS : [llms.txt](/fr/llms.txt)
---
# PL/pgSQL avancé {#dev42-ch4}
## Préambule {#dev42-ch4-1}
### Au menu {#dev42-ch4-1-1}
- Routines « variadic » et polymorphes
- Fonctions trigger
- Curseurs
- Récupérer les erreurs
- Messages d’erreur dans les logs
- Sécurité
- Optimisation
- Problèmes fréquents
### Objectifs {#dev42-ch4-1-2}
- Connaître la majorité des possibilités de PL/pgSQL
- Les utiliser pour étendre les fonctionnalités de la base
- Écrire du code robuste
- Éviter les pièges de sécurité
- Savoir optimiser une routine
## Routines variadic {#dev42-ch4-2}
### Routines variadic : introduction {#dev42-ch4-2-1}
- Permet de créer des routines avec un nombre d’arguments variables
- … mais du même type
L’utilisation du mot clé `VARIADIC` dans la déclaration des routines permet d’utiliser un nombre variable d’arguments dans la mesure où tous les arguments optionnels sont du même type de données. Ces arguments sont passés à la fonction sous forme de tableau d’arguments du même type.
``` sql
VARIADIC tableau text[]
```
Il n’est pas possible d’utiliser d’autres arguments en entrée à la suite d’un paramètre `VARIADIC`.
### Routines variadic : exemple {#dev42-ch4-2-2}
Récupérer le minimum d’une liste :
``` sql
CREATE FUNCTION pluspetit(VARIADIC numeric[])
RETURNS numeric AS $$
SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;
SELECT pluspetit(10, -1, 5, 4.4);
pluspetit
-----------
-1
(1 row)
```
Quelques explications sur cette fonction :
- SQL est un langage de routines stockées
- une routine SQL ne contient que des ordres SQL exécutés séquentiellements
- le résultat de la fonction est le résultat du dernier ordre
- `generate_subscript()` prend un tableau en premier paramètre et la dimension de ce tableau (un tableau peut avoir plusieurs dimensions), et elle retourne une série d’entiers allant du premier au dernier indice du tableau dans cette dimension
- g(i) est un alias : `generate_subscripts` est une SRF (`set-returning function`, retourne un `SETOF`), g est donc le nom de l’alias de table, et i le nom de l’alias de colonne.
### Routines variadic : exemple PL/pgSQL {#dev42-ch4-2-3}
- En PL/pgSQL, cette fois-ci
- Démonstration de `FOREACH xxx IN ARRAY aaa LOOP`
- Précédemment, obligé de convertir le tableau en relation pour boucler (`unnest`)
En PL/pgSQL, il est possible d’utiliser une boucle `FOREACH` pour parcourir directement le tableau des arguments optionnels.
``` sql
CREATE OR REPLACE FUNCTION pluspetit(VARIADIC liste numeric[])
RETURNS numeric
LANGUAGE plpgsql
AS $function$
DECLARE
courant numeric;
plus_petit numeric;
BEGIN
FOREACH courant IN ARRAY liste LOOP
IF plus_petit IS NULL OR courant < plus_petit THEN
plus_petit := courant;
END IF;
END LOOP;
RETURN plus_petit;
END
$function$;
```
Auparavant, il fallait développer le tableau avec la fonction `unnest()` pour réaliser la même opération.
``` sql
CREATE OR REPLACE FUNCTION pluspetit(VARIADIC liste numeric[])
RETURNS numeric
LANGUAGE plpgsql
AS $function$
DECLARE
courant numeric;
plus_petit numeric;
BEGIN
FOR courant IN SELECT unnest(liste) LOOP
IF plus_petit IS NULL OR courant < plus_petit THEN
plus_petit := courant;
END IF;
END LOOP;
RETURN plus_petit;
END
$function$;
```
## Routines polymorphes {#dev42-ch4-3}
### Routines polymorphes : introduction {#dev42-ch4-3-1}
- Typer les variables oblige à dupliquer les routines communes à plusieurs types
- PostgreSQL propose des types polymorphes
- Le typage se fait à l’exécution
Pour pouvoir utiliser la même fonction en utilisant des types différents, il est nécessaire de la redéfinir avec les différents types autorisés en entrée. Par exemple, pour autoriser l’utilisation de données de type `integer` ou `float` en entrée et retournés par une même fonction, il faut la dupliquer.
``` sql
CREATE OR REPLACE FUNCTION
addition(var1 integer, var2 integer)
RETURNS integer
AS $$
DECLARE
somme integer;
BEGIN
somme := var1 + var2;
RETURN somme;
END;
$$ LANGUAGE plpgsql;
```
``` sql
CREATE OR REPLACE FUNCTION
addition(var1 float, var2 float)
RETURNS float
AS $$
DECLARE
somme float;
BEGIN
somme := var1 + var2;
RETURN somme;
END;
$$ LANGUAGE plpgsql;
```
L’utilisation de types polymorphes permet d’éviter ce genre de duplications fastidieuses.
### Routines polymorphes : anyelement {#dev42-ch4-3-2}
- Remplace tout type de données simple ou composite
- pour les paramètres en entrée comme pour les paramètres en sortie
- Tous les paramètres et type de retour de type `anyelement` se voient attribués le même type
- Donc un seul type pour tous les `anyelement` autorisés
- Paramètre spécial `$0` : du type attribué aux éléments `anyelement`
### Routines polymorphes : anyarray {#dev42-ch4-3-3}
- `anyarray` remplace tout tableau de type de données simple ou composite
- pour les paramètres en entrée comme pour les paramètres en sortie
- Le typage se fait à l’exécution
- Tous les paramètres de type `anyarray` se voient attribués le même type
### Routines polymorphes : exemple {#dev42-ch4-3-4}
L’addition est un exemple fréquent :
``` sql
CREATE OR REPLACE FUNCTION
addition(var1 anyelement, var2 anyelement)
RETURNS anyelement
AS $$
DECLARE
somme ALIAS FOR $0;
BEGIN
somme := var1 + var2;
RETURN somme;
END;
$$ LANGUAGE plpgsql;
```
### Routines polymorphes : tests {#dev42-ch4-3-5}
``` sql
# SELECT addition(1, 3);
addition
----------
4
(1 row)
# SELECT addition(1.3, 3.5);
addition
----------
4.8
(1 row)
```
L’opérateur `+` étant défini pour les entiers comme pour les `numeric`, la fonction ne pose aucun problème pour ces deux types de données, et retourne une donnée du même type que les données d’entrée.
### Routines polymorphes : problème {#dev42-ch4-3-6}
- Attention lors de l’utilisation de type polymorphe…
``` sql
# SELECT addition('un'::text, 'mot'::text);
ERREUR: L'opérateur n'existe pas : text + text
LIGNE 1 : SELECT $1 + $2
^
ASTUCE : Aucun opérateur correspond au nom donné et aux types d'arguments.
Vous devez ajouter des conversions explicites de type.
REQUÊTE : SELECT $1 + $2
CONTEXTE : PL/pgSQL function "addition" line 4 at assignment
```
Le typage n’étant connu qu’à l’exécution, c’est aussi à ce moment que se déclenchent les erreurs.
De même, l’affectation du type unique pour tous les éléments se fait sur la base du premier élément, ainsi :
```text
# SELECT addition(1, 3.5);
ERROR: function addition(integer, numeric) does not exist
LIGNE 1 : SELECT addition(1, 3.5);
^
ASTUCE : No function matches the given name and argument types.
You might need to add explicit type casts.
```
génère une erreur car du premier argument est déduit le type `integer`, ce qui n’est évidement pas le cas du deuxième. Il peut donc être nécessaire d’utiliser une conversion explicite pour résoudre ce genre de problématique.
``` text
# SELECT addition(1::numeric, 3.5);
addition
----------
4.5
```
## Fonctions trigger {#dev42-ch4-4}
### Fonctions trigger : introduction {#dev42-ch4-4-1}
- Fonction stockée
- Action déclenchée par `INSERT` (incluant `COPY`), `UPDATE`, `DELETE`, `TRUNCATE`
- Mode **par ligne** ou **par instruction**
- Exécution d’une fonction
Un trigger est une spécification précisant que la base de données doit exécuter une fonction particulière quand un certain type d’opération est traité. Les fonctions trigger peuvent être définies pour s’exécuter avant ou après une commande `INSERT`, `UPDATE`, `DELETE` ou `TRUNCATE`.
La fonction trigger doit être définie avant que le trigger lui-même puisse être créé. La fonction trigger doit être déclarée comme une fonction ne prenant aucun argument et retournant un type `trigger`.
Une fois qu’une fonction trigger est créée, le trigger est créé avec `CREATE TRIGGER`. La même fonction trigger est utilisable par plusieurs triggers.
Un trigger `TRUNCATE` ne peut utiliser que le mode par instruction, contrairement aux autres triggers pour lesquels vous avez le choix entre « par ligne » et « par instruction ».
Enfin, l’instruction `COPY` est traitée comme s’il s’agissait d’une commande `INSERT`.
À noter que les problématiques de visibilité et de volatilité depuis un trigger sont assez complexes dès lors que l’on lit ou modifie les données. Voir [la documentation](https://docs.postgresql.fr/current/trigger-datachanges.html) pour plus de détails à ce sujet.
### Fonctions trigger : variables (1/5) {#dev42-ch4-4-2}
- `OLD` :
- type de données `RECORD` correspondant à la ligne avant modification
- valable pour un `DELETE` et un `UPDATE`
- `NEW` :
- type de données `RECORD` correspondant à la ligne après modification
- valable pour un `INSERT` et un `UPDATE`
### Fonctions trigger : variables (2/5) {#dev42-ch4-4-3}
- Ces deux variables sont valables uniquement pour les triggers en mode ligne
- pour les triggers en mode instruction, utiliser les tables de transition
- Accès aux champs par la notation pointée
- `NEW.champ1` pour accéder à la nouvelle valeur de `champ1`
### Fonctions trigger : variables (3/5) {#dev42-ch4-4-4}
- `TG_NAME`
- nom du trigger qui a déclenché l’appel de la fonction
- `TG_WHEN`
- chaîne valant `BEFORE`, `AFTER` ou `INSTEAD OF` suivant le type du trigger
- `TG_LEVEL`
- chaîne valant `ROW` ou `STATEMENT` suivant le mode du trigger
- `TG_OP`
- chaîne valant `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE` suivant l’opération qui a déclenché le trigger
### Fonctions trigger : variables (4/5) {#dev42-ch4-4-5}
- `TG_RELID`
- `OID` de la table qui a déclenché le trigger
- `TG_TABLE_NAME`
- nom de la table qui a déclenché le trigger
- `TG_TABLE_SCHEMA`
- nom du schéma contenant la table qui a déclenché le trigger
Vous pourriez aussi rencontrer dans du code la variable `TG_RELNAME`. C’est aussi le nom de la table qui a déclenché le trigger. Attention, cette variable est obsolète, il est préférable d’utiliser maintenant `TG_TABLE_NAME`.
### Fonctions trigger : variables (5/5) {#dev42-ch4-4-6}
- `TG_NARGS`
- nombre d’arguments donnés à la fonction trigger
- `TG_ARGV`
- les arguments donnés à la fonction trigger (le tableau commence à 0)
La fonction trigger est déclarée sans arguments mais il est possible de lui en passer dans la déclaration du trigger. Dans ce cas, il faut utiliser les deux variables ci-dessus pour y accéder. Attention, tous les arguments sont convertis en texte. Il faut donc se cantonner à des informations simples, sous peine de compliquer le code.
``` sql
CREATE OR REPLACE FUNCTION verifier_somme()
RETURNS trigger AS $$
DECLARE
fact_limit integer;
arg_color varchar;
BEGIN
fact_limit := TG_ARGV[0];
IF NEW.somme > fact_limit THEN
RAISE NOTICE 'La facture % necessite une verification. '
'La somme % depasse la limite autorisee de %.',
NEW.idfact, NEW.somme, fact_limit;
END IF;
NEW.datecreate := current_timestamp;
return NEW;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER trig_verifier_debit
BEFORE INSERT OR UPDATE ON test
FOR EACH ROW
EXECUTE PROCEDURE verifier_somme(400);
CREATE TRIGGER trig_verifier_credit
BEFORE INSERT OR UPDATE ON test
FOR EACH ROW
EXECUTE PROCEDURE verifier_somme(800);
```
### Fonctions trigger : retour {#dev42-ch4-4-7}
- Une fonction trigger a un type de retour spécial : `trigger`
- Trigger `BEFORE FOR EACH ROW` :
- si retour `NULL` : annulation de l’opération, sans erreur, la transaction continue
- modification possible de `NEW`
- `BEFORE DELETE` : `NEW` est `NULL`, retourner `OLD`
- `BEFORE INSERT` : `OLD` est `NULL`, retourner `NEW`
- Trigger `AFTER` :
- valeur de retour ignorée (même `NULL`)
- Trigger `FOR EACH STATEMENT` :
- valeur de retour ignorée, renvoyer `NULL`
- `RAISE EXCEPTION` toujours possible, interrompt la transaction
Une fonction trigger retourne le type spécial `trigger`. Pour cette raison, ces fonctions ne peuvent être utilisées que dans le contexte d’un ou plusieurs triggers.
Pour pouvoir être utilisée comme valeur de retour dans la fonction (avec `RETURN`), une variable doit être de structure identique à celle de la table sur laquelle le trigger a été déclenché. Les variables spéciales `OLD` (ancienne valeur avant application de l’action à l’origine du déclenchement) et `NEW` (nouvelle valeur après application de l’action) sont également disponibles, utilisables et même modifiables.
**Trigger BEFORE … FOR EACH ROW** :
La valeur de retour d’un trigger de type ligne déclenché avant l’opération peut changer complètement l’effet de la commande.
Il est possible de changer les valeurs de la nouvelle ligne créée par une action `INSERT` ou `UPDATE` en modifiant `NEW` directement, puis en faisant `RETURN NEW`.
Il est également possible d’annuler complètement l’action sur la ligne, sans erreur en retournant `NULL`. Cette annulation ne vaut que pour la ligne, et la transaction continue normalement. Les triggers ne sont plus déclenchés pour cette ligne.
Attention ! Dans le cas d’une fonction trigger `BEFORE DELETE`, il faut prendre en compte que `NEW` contient `NULL`, et en conséquence `RETURN NEW` provoque l’annulation du `DELETE` ! Dans ce cas, la convention est de faire un `RETURN OLD` pour poursuivre, ou `RETURN NULL` pour une annulation explicite.
**Trigger AFTER … FOR EACH ROW** et **FOR EACH STATEMENT** :
En revanche, la valeur de retour utilisée n’a pas d’effet dans les cas des triggers `ROW` et `AFTER`, et des triggers `FOR EACH STATEMENT` (par exemple les triggers sur `TRUNCATE` ou les triggers utilisant les tables de transition). La documentation conseille alors de toujours retourner `NULL`.
Dans ces cas, il reste possible d’annuler l’action en remontant une erreur à l’exécution de la fonction (`RAISE EXCEPTION`). Mais cela va annuler toute la transaction.
### Fonctions trigger : exemple - 1 {#dev42-ch4-4-8}
- Horodater une opération sur une ligne
``` sql
CREATE TABLE ma_table (
id serial,
-- un certain nombre de champs informatifs
date_ajout timestamp,
date_modif timestamp);
```
### Fonctions trigger : exemple - 2 {#dev42-ch4-4-9}
``` sql
CREATE OR REPLACE FUNCTION horodatage() RETURNS trigger
AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
NEW.date_ajout := now();
ELSEIF TG_OP = 'UPDATE' THEN
NEW.date_modif := now();
END IF;
RETURN NEW;
END; $$ LANGUAGE plpgsql;
CREATE TRIGGER trig_horodatage
BEFORE INSERT OR UPDATE ON ma_table
FOR EACH ROW
EXECUTE PROCEDURE horodatage();
```
### Options de CREATE TRIGGER {#dev42-ch4-4-10}
`CREATE TRIGGER` permet quelques variantes :
- `CREATE TRIGGER name WHEN ( condition )`
- `CREATE TRIGGER name BEFORE UPDATE OF colx ON my_table`
- `CREATE CONSTRAINT TRIGGER` : exécuté qu’au moment de la validation de la transaction
- `CREATE TRIGGER view_insert INSTEAD OF INSERT ON my_view`
- On peut ne déclencher un trigger que si une condition est vérifiée. Cela simplifie souvent le code du trigger, et gagne en performances : plus besoin pour le moteur d’aller exécuter la fonction.
- On peut ne déclencher un trigger que si une colonne spécifique a été modifiée. Il ne s’agit donc que de triggers sur `UPDATE`. Encore un moyen de simplifier le code et de gagner en performances en évitant les déclenchements inutiles.
- On peut créer un trigger en le déclarant comme étant un trigger de contrainte. Il peut alors être *deferrable*, *deferred*, comme tout autre contrainte, c’est-à-dire n’être exécuté qu’au moment de la validation de la transaction, ce qui permet de ne vérifier les contraintes implémentées par le trigger qu’au moment de la validation finale.
- On peut créer un trigger sur une vue. C’est un trigger `INSTEAD OF`, qui permet de programmer de façon efficace les `INSERT`/`UPDATE`/`DELETE`/`TRUNCATE` sur les vues. Auparavant, il fallait passer par le système de règles (`RULES`), complexe et sujet à erreurs.
### Tables de transition {#dev42-ch4-4-11}
- Pour les triggers de type `AFTER` et de niveau statement
- Possibilité de stocker les lignes avant et/ou après modification
- `REFERENCING OLD TABLE`
- `REFERENCING NEW TABLE`
- Par exemple :
``` sql
CREATE TRIGGER tr1
AFTER DELETE ON t1
REFERENCING OLD TABLE AS oldtable
FOR EACH STATEMENT
EXECUTE PROCEDURE log_delete();
```
Dans le cas d’un trigger en mode instruction, il n’est pas possible d’utiliser les variables `OLD` et `NEW` car elles ciblent une seule ligne. Pour cela, le standard SQL parle de tables de transition.
**Exemple** :
Nous allons créer une table `t1` qui portera le trigger et une table `archives` qui aura pour but de récupérer les enregistrements supprimés de la table `t1`.
``` sql
CREATE TABLE t1 (c1 integer, c2 text);
CREATE TABLE archives (id integer GENERATED ALWAYS AS IDENTITY,
dlog timestamp DEFAULT now(),
t1_c1 integer, t1_c2 text);
```
Maintenant, il faut créer le code de la procédure stockée :
``` sql
CREATE OR REPLACE FUNCTION log_delete() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO archives (t1_c1, t1_c2) SELECT c1, c2 FROM oldtable;
RETURN null;
END
$$;
```
Et ajouter le trigger sur la table `t1` :
``` sql
CREATE TRIGGER tr1
AFTER DELETE ON t1
REFERENCING OLD TABLE AS oldtable
FOR EACH STATEMENT
EXECUTE PROCEDURE log_delete();
```
Maintenant, insérons un million de ligne dans `t1` puis supprimons-les :
``` sql
INSERT INTO t1 SELECT i, 'Ligne '||i FROM generate_series(1, 1000000) i;
DELETE FROM t1;
Time: 2141.871 ms (00:02.142)
```
La suppression avec le trigger prend 2 secondes. Il est possible de connaître le temps à supprimer les lignes et le temps à exécuter le trigger en utilisant l’ordre `EXPLAIN ANALYZE` :
``` sql
TRUNCATE archives;
INSERT INTO t1 SELECT i, 'Ligne '||i FROM generate_series(1, 1000000) i;
EXPLAIN (ANALYZE) DELETE FROM t1;
QUERY PLAN
--------------------------------------------------------------------------
Delete on t1 (cost=0.00..14241.98 rows=796798 width=6)
(actual time=781.612..781.612 rows=0 loops=1)
-> Seq Scan on t1 (cost=0.00..14241.98 rows=796798 width=6)
(actual time=0.113..104.328 rows=1000000 loops=1)
Planning time: 0.079 ms
Trigger tr1: time=1501.688 calls=1
Execution time: 2287.907 ms
(5 rows)
```
Donc la suppression des lignes met 0,7 seconde alors que l’exécution du trigger met 1,5 seconde.
Pour comparer, voici l’ancienne façon de faire (configuration d’un trigger en mode ligne) :
``` sql
CREATE OR REPLACE FUNCTION log_delete() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO archives (t1_c1, t1_c2) VALUES (old.c1, old.c2);
RETURN null;
END
$$;
DROP TRIGGER tr1 ON t1;
CREATE TRIGGER tr1
AFTER DELETE ON t1
FOR EACH ROW
EXECUTE PROCEDURE log_delete();
TRUNCATE archives;
TRUNCATE t1;
INSERT INTO t1 SELECT i, 'Ligne '||i FROM generate_series(1, 1000000) i;
DELETE FROM t1;
Time: 8445.697 ms (00:08.446)
TRUNCATE archives;
INSERT INTO t1 SELECT i, 'Ligne '||i FROM generate_series(1, 1000000) i;
EXPLAIN (ANALYZE) DELETE FROM t1;
QUERY PLAN
--------------------------------------------------------------------------
Delete on t1 (cost=0.00..14241.98 rows=796798 width=6)
(actual time=1049.420..1049.420 rows=0 loops=1)
-> Seq Scan on t1 (cost=0.00..14241.98 rows=796798 width=6)
(actual time=0.061..121.701 rows=1000000 loops=1)
Planning time: 0.096 ms
Trigger tr1: time=7709.725 calls=1000000
Execution time: 8825.958 ms
(5 rows)
```
Donc avec un trigger en mode ligne, la suppression du million de lignes met presque 9 secondes à s’exécuter, dont 7,7 pour l’exécution du trigger. Sur le trigger en mode instruction, il faut compter 2,2 secondes, dont 1,5 sur le trigger. Les tables de transition nous permettent de gagner en performance.
Le gros intérêt des tables de transition est le gain en performance que cela apporte.
## Curseurs {#dev42-ch4-5}
### Curseurs : introduction {#dev42-ch4-5-1}
- Exécuter une requête en une fois peut ramener beaucoup de résultats
- Tout ce résultat est en mémoire
- risque de dépassement mémoire
- La solution : les curseurs
- Un curseur permet d’exécuter la requête sur le serveur mais de ne récupérer les résultats que petit bout par petit bout
- Dans une transaction ou une routine
À noter que la notion de curseur existe aussi en SQL pur, sans passer par une routine PL/pgSQL. On les crée en utilisant la commande `DECLARE`, et les règles de manipulation sont légèrement différentes (on peut par exemple créer un curseur `WITH HOLD`, qui persistera après la fin de la transaction). Voir la documentation pour plus d’informations à ce sujet : https://docs.postgresql.fr/current/sql-declare.html
### Curseurs : déclaration d’un curseur {#dev42-ch4-5-2}
- Avec le type refcursor
- Avec la pseudo-instruction `CURSOR FOR`
- Avec une requête paramétrée
- Exemples :
``` sql
curseur1 refcursor;
curseur2 CURSOR FOR SELECT * FROM ma_table;
curseur3 CURSOR (param integer) IS
SELECT * FROM ma_table WHERE un_champ=param;
```
La première forme permet la création d’un curseur non lié à une requête.
### Curseurs : ouverture d’un curseur {#dev42-ch4-5-3}
- Lier une requête à un curseur :
``` sql
OPEN curseur FOR requete
```
- Plan de la requête mis en cache
- Lier une requête dynamique à un curseur
``` sql
OPEN curseur FOR EXECUTE chaine_requete
```
Voici un exemple de lien entre une requête et un curseur :
``` sql
OPEN curseur FOR SELECT * FROM ma_table;
```
Et voici un exemple d’utilisation d’une requête dynamique :
``` sql
OPEN curseur FOR EXECUTE 'SELECT * FROM ' || quote_ident(TG_TABLE_NAME);
```
### Curseurs : ouverture d’un curseur lié {#dev42-ch4-5-4}
- Instruction SQL : `OPEN curseur(arguments)`
- Permet d’ouvrir un curseur déjà lié à une requête
- Impossible d’ouvrir deux fois le même curseur
- Plan de la requête mise en cache
- Exemple
``` sql
curseur CURSOR FOR SELECT * FROM ma_table;
...
OPEN curseur;
```
### Curseurs : récupération des données {#dev42-ch4-5-5}
- Instruction SQL :
``` sql
FETCH [ direction { FROM | IN } ] curseur INTO cible
```
- Récupère la prochaine ligne
- FOUND indique si cette nouvelle ligne a été récupérée
- Cible est
- une variable RECORD
- une variable ROW
- un ensemble de variables séparées par des virgules
### Curseurs : récupération des données {#dev42-ch4-5-6}
- `direction` du `FETCH` :
- `NEXT`, `PRIOR`
- `FIRST`, `LAST`
- `ABSOLUTE nombre`, `RELATIVE nombre`
- nombre
- `ALL`
- `FORWARD`, `FORWARD nombre`, `FORWARD ALL`
- `BACKWARD`, `BACKWARD nombre`, `BACKWARD ALL`
### Curseurs : modification des données {#dev42-ch4-5-7}
- Mise à jour d’une ligne d’un curseur :
``` sql
UPDATE une_table SET ...
WHERE CURRENT OF curseur;
```
- Suppression d’une ligne d’un curseur :
``` sql
DELETE FROM une_table
WHERE CURRENT OF curseur;
```
Attention, ces différentes syntaxes ne modifient pas les données dans le curseur en mémoire, mais font réellement la modification dans la table. L’emplacement actuel du curseur est utilisé ici pour identifier la ligne correspondante à mettre à jour.
### Curseurs : fermeture d’un curseur {#dev42-ch4-5-8}
- Instruction SQL : `CLOSE curseur`
- Ferme le curseur
- Permet de récupérer de la mémoire
- Permet aussi de réouvrir le curseur
### Curseurs : renvoi d’un curseur {#dev42-ch4-5-9}
- Fonction renvoyant une valeur de type `refcursor`
- Permet donc de renvoyer plusieurs valeurs
Voici un exemple d’utilisation d’une référence de curseur retournée par une fonction :
``` sql
CREATE FUNCTION consult_all_stock(refcursor) RETURNS refcursor AS $$
BEGIN
OPEN $1 FOR SELECT * FROM stock;
RETURN $1;
END;
$$ LANGUAGE plpgsql;
-- doit être dans une transaction pour utiliser les curseurs.
BEGIN;
SELECT * FROM consult_all_stock('cursor_a');
FETCH ALL FROM cursor_a;
COMMIT;
```
## Gestion des erreurs {#dev42-ch4-6}
### Gestion des erreurs : introduction {#dev42-ch4-6-1}
- Sans exceptions :
- toute erreur provoque un arrêt de la fonction
- toute modification suite à une instruction SQL (`INSERT`, `UPDATE`, `DELETE`) est annulée
- d’où l’ajout d’une gestion personnalisée des erreurs avec le concept des exceptions
### Gestion des erreurs : une exception {#dev42-ch4-6-2}
- La fonction comporte un bloc supplémentaire, `EXCEPTION` :
``` sql
DECLARE
-- déclaration des variables locales
BEGIN
-- instructions de la fonction
EXCEPTION
WHEN condition THEN
-- instructions traitant cette erreur
WHEN condition THEN
-- autres instructions traitant cette autre erreur
-- etc.
END
```
### Gestion des erreurs : flot dans une fonction {#dev42-ch4-6-3}
- L’exécution de la fonction commence après le `BEGIN`
- Si aucune erreur ne survient, le bloc `EXCEPTION` est ignoré
- Si une erreur se produit
- tout ce qui a été modifié dans la base dans le bloc est annulé
- les variables gardent par contre leur état
- l’exécution passe directement dans le bloc de gestion de l’exception
### Gestion des erreurs : flot dans une exception {#dev42-ch4-6-4}
- Recherche d’une condition satisfaisante
- Si cette condition est trouvée
- exécution des instructions correspondantes
- Si aucune condition n’est compatible
- sortie du bloc `BEGIN`/`END` comme si le bloc d’exception n’existait pas
- passage de l’exception au bloc `BEGIN`/`END` contenant (après annulation de ce que ce bloc a modifié en base)
- Dans un bloc d’exception, les instructions `INSERT`, `UPDATE`, `DELETE` de la fonction ont été annulées
- Dans un bloc d’exception, les variables locales de la fonction ont gardé leur ancienne valeur
### Gestion des erreurs : codes d’erreurs {#dev42-ch4-6-5}
- `SQLSTATE` : code d’erreur
- `SQLERRM` : message d’erreur
- Par exemple :
- `Data Exception` : division par zéro, overflow, argument invalide pour certaines fonctions, etc.
- `Integrity Constraint Violation` : unicité, CHECK, clé étrangère, etc.
- `Syntax Error`
- `PL/pgSQL Error` : `RAISE EXCEPTION`, pas de données, trop de lignes, etc.
- Les erreurs sont contenues dans des classes d’erreurs plus génériques, qui peuvent aussi être utilisées
Toutes les erreurs sont référencées [dans la documentation](https://docs.postgresql.fr/current/errcodes-appendix.html)
Attention, des codes d’erreurs nouveaux apparaissent à chaque version.
La classe data_exception contient de nombreuses erreurs, comme datetime_field_overflow, invalid_escape_character, invalid_binary_representation… On peut donc, dans la déclaration de l’exception, intercepter toutes les erreurs de type data_exception d’un coup, ou une par une.
L’instruction `GET STACKED DIAGNOSTICS` permet d’avoir une vision plus précise de l’erreur récupéré par le bloc de traitement des exceptions. La liste de toutes les informations que l’on peut collecter est disponible [dans la documentation](https://docs.postgresql.fr/current/plpgsql-control-structures.html#PLPGSQL-EXCEPTION-DIAGNOSTICS-VALUES) .
La démonstration ci-dessous montre comment elle peut être utilisée.
``` sql
# CREATE TABLE t5(c1 integer PRIMARY KEY);
CREATE TABLE
# INSERT INTO t5 VALUES (1);
INSERT 0 1
# CREATE OR REPLACE FUNCTION test(INT4) RETURNS void AS $$
DECLARE
v_state TEXT;
v_msg TEXT;
v_detail TEXT;
v_hint TEXT;
v_context TEXT;
BEGIN
BEGIN
INSERT INTO t5 (c1) VALUES ($1);
EXCEPTION WHEN others THEN
GET STACKED DIAGNOSTICS
v_state = RETURNED_SQLSTATE,
v_msg = MESSAGE_TEXT,
v_detail = PG_EXCEPTION_DETAIL,
v_hint = PG_EXCEPTION_HINT,
v_context = PG_EXCEPTION_CONTEXT;
raise notice E'Et une exception :
state : %
message: %
detail : %
hint : %
context: %', v_state, v_msg, v_detail, v_hint, v_context;
END;
RETURN;
END;
$$ LANGUAGE plpgsql;
# SELECT test(2);
test
------
(1 row)
# SELECT test(2);
NOTICE: Et une exception :
state : 23505
message: duplicate key value violates unique constraint "t5_pkey"
detail : Key (c1)=(2) already exists.
hint :
context: SQL statement "INSERT INTO t5 (c1) VALUES ($1)"
PL/pgSQL function test(integer) line 10 at SQL statement
test
------
(1 row)
```
### Messages d’erreurs : RAISE - 1 {#dev42-ch4-6-6}
- Envoyer une trace dans les journaux applicatifs et/ou vers le client
- `RAISE niveau message`
- Niveau correspond au niveau d’importance du message
- `DEBUG`, `LOG`, `INFO`, `NOTICE`, `WARNING`, `EXCEPTION`
- Message est la trace à enregistrer
- Message dynamique… tout signe `%` est remplacé par la valeur indiquée après le message
- Champs `DETAIL` et `HINT` disponibles
Il convient de noter qu’un message envoyé de cette manière ne fera pas partie de l’éventuel résultat d’une fonction, et ne sera donc pas exploitable en SQL. Pour cela, il faut utiliser l’instruction `RETURN` avec un type de retour approprié.
Le traitement des messages de ce type et leur destination d’envoi sont contrôlés par le serveur à l’aide des paramètres `log_min_messages` et `client_min_messages`.
### Messages d’erreurs : RAISE - 2 {#dev42-ch4-6-7}
Exemples :
``` sql
RAISE WARNING 'valeur % interdite', valeur;
```
``` sql
RAISE WARNING 'valeur % ambigue',
valeur
USING HINT = 'Controlez la valeur saisie en amont';
```
Les autres niveaux pour `RAISE` ne sont que des messages, sans déclenchement d’exception.
### Messages d’erreurs : configuration des logs {#dev42-ch4-6-8}
- Deux paramètres importants pour les traces
- `log_min_messages`
- niveau minimum pour que la trace soit enregistrée dans les journaux
- `client_min_messages`
- niveau minimum pour que la trace soit envoyée au client
- Dans le cas d’un `RAISE NOTICE message`, il faut avoir soit `log_min_messages`, soit `client_min_messages`, soit les deux à la valeur `NOTICE` au minimum.
### Messages d’erreurs : RAISE EXCEPTION - 1 {#dev42-ch4-6-9}
- Annule le bloc en cours d’exécution
- `RAISE EXCEPTION message`
- Sauf en cas de présence d’un bloc `EXCEPTION` gérant la condition `RAISE_EXCEPTION`
- message est la trace à enregistrer, et est dynamique… tout signe % est remplacé par la valeur indiquée après le message
### Messages d’erreurs : RAISE EXCEPTION - 2 {#dev42-ch4-6-10}
Exemple :
``` sql
RAISE EXCEPTION 'erreur interne';
-- La chose à ne pas faire !
```
Le rôle d’une exception est d’intercepter une erreur pour exécuter un traitement permettant soit de corriger l’erreur, soit de remonter une erreur pertinente. Intercepter un problème pour retourner « erreur interne » n’est pas une bonne idée.
### Flux des erreurs dans du code PL {#dev42-ch4-6-11}
- Les exceptions non traitées «remontent»
- de bloc `BEGIN`/`END` imbriqués vers les blocs parents (fonctions appelantes comprises)
- jusqu’à ce que personne ne puisse les traiter
- voir note pour démonstration
Démonstration en plusieurs étapes :
``` sql
# CREATE TABLE ma_table (
id integer unique
);
CREATE TABLE
# CREATE OR REPLACE FUNCTION public.demo_exception()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
BEGIN
INSERT INTO ma_table VALUES (1);
-- Va déclencher une erreur de violation de contrainte d'unicité
INSERT INTO ma_table VALUES (1);
END
$function$;
CREATE FUNCTION
# SELECT demo_exception();
ERROR: duplicate key value violates unique constraint "ma_table_id_key"
DETAIL: Key (id)=(1) already exists.
CONTEXT: SQL statement "INSERT INTO ma_table VALUES (1)"
PL/pgSQL function demo_exception() line 6 at SQL statement
```
Une exception a été remontée avec un message explicite.
``` sql
# SELECT * FROM ma_table ;
a
---
(0 row)
```
La fonction a bien été annulée.
### Flux des erreurs dans du code PL - 2 {#dev42-ch4-6-12}
- Les erreurs remontent
- Cette fois-ci, on rajoute un bloc PL pour intercepter l’erreur
``` sql
# CREATE OR REPLACE FUNCTION public.demo_exception()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
BEGIN
INSERT INTO ma_table VALUES (1);
-- Va déclencher une erreur de violation de contrainte d'unicité
INSERT INTO ma_table VALUES (1);
EXCEPTION WHEN unique_violation THEN
RAISE NOTICE 'violation d''unicite, mais celle-ci n''est pas grave';
RAISE NOTICE 'erreur: %',sqlerrm;
END
$function$;
CREATE FUNCTION
# SELECT demo_exception();
NOTICE: violation d'unicite, mais celle-ci n'est pas grave
NOTICE: erreur: duplicate key value violates unique constraint "ma_table_id_key"
demo_exception
----------------
(1 row)
```
L’erreur est bien devenue un message de niveau `NOTICE`.
``` sql
# SELECT * FROM ma_table ;
a
---
(0 row)
```
La table n’en reste pas moins vide pour autant puisque le bloc a été annulé.
### Flux des erreurs dans du code PL - 3 {#dev42-ch4-6-13}
- Cette fois-ci, on rajoute un bloc PL indépendant pour gérer le second `INSERT`
Voici une nouvelle version de la fonction :
``` sql
# CREATE OR REPLACE FUNCTION public.demo_exception()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
BEGIN
INSERT INTO ma_table VALUES (1);
-- L'operation suivante pourrait échouer.
-- Il ne faut pas perdre le travail effectué jusqu'à ici
BEGIN
-- Va déclencher une erreur de violation de contrainte d'unicité
INSERT INTO ma_table VALUES (1);
EXCEPTION WHEN unique_violation THEN
-- Cette exception est bien celle du bloc imbriqué
RAISE NOTICE 'violation d''unicite, mais celle-ci n''est pas grave';
RAISE NOTICE 'erreur: %',sqlerrm;
END; -- Fin du bloc imbriqué
END
$function$;
CREATE FUNCTION
# SELECT demo_exception();
NOTICE: violation d'unicite, mais celle-ci n'est pas grave
NOTICE: erreur: duplicate key value violates unique constraint "ma_table_id_key"
demo_exception
----------------
(1 row)
```
En apparence, le résultat est identique.
``` sql
# SELECT * FROM ma_table ;
a
---
1
(1 row)
```
Mais cette fois-ci, le bloc `BEGIN` parent n’a pas eu d’exception, il s’est donc bien terminé.
### Flux des erreurs dans du code PL - 4 {#dev42-ch4-6-14}
- Illustrons maintenant la remontée d’erreurs
- Nous avons deux blocs imbriqués
- Une erreur non prévue va se produire dans le bloc intérieur
On commence par ajouter une contrainte sur la colonne pour empêcher les valeurs supérieures ou égales à 10 :
``` sql
# ALTER TABLE ma_table ADD CHECK (id < 10 ) ;
ALTER TABLE
```
Puis, on recrée la fonction de façon à ce qu’elle déclenche cette erreur dans le bloc le plus bas, et la gère uniquement dans le bloc parent :
``` sql
CREATE OR REPLACE FUNCTION public.demo_exception()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
BEGIN
INSERT INTO ma_table VALUES (1);
-- L'operation suivante pourrait échouer.
-- Il ne faut pas perdre le travail effectué jusqu'à ici
BEGIN
-- Va déclencher une erreur de violation de check (col < 10)
INSERT INTO ma_table VALUES (100);
EXCEPTION WHEN unique_violation THEN
-- Cette exception est bien celle du bloc imbriqué
RAISE NOTICE 'violation d''unicite, mais celle-ci n''est pas grave';
RAISE NOTICE 'erreur: %',sqlerrm;
END; -- Fin du bloc imbriqué
EXCEPTION WHEN check_violation THEN
RAISE NOTICE 'violation de contrainte check';
RAISE EXCEPTION 'mais on va remonter une exception à l''appelant, '
'juste pour le montrer';
END
$function$;
```
Exécutons la fonction :
``` sql
# SELECT demo_exception();
ERROR: duplicate key value violates unique constraint "ma_table_id_key"
DETAIL: Key (id)=(1) already exists.
CONTEXT: SQL statement "INSERT INTO ma_table VALUES (1)"
PL/pgSQL function demo_exception() line 4 at SQL statement
```
C’est normal, nous avons toujours l’enregistrement à 1 du test précédent. L’exception se déclenche donc dans le bloc parent, sans espoir d’interception: nous n’avons pas d’exception pour lui.
Nettoyons donc la table, pour reprendre le test :
``` sql
# TRUNCATE ma_table ;
TRUNCATE TABLE
# SELECT demo_exception();
NOTICE: violation de contrainte check
ERREUR: mais on va remonter une exception à l'appelant, juste pour le montrer
CONTEXT: PL/pgSQL function demo_exception() line 17 at RAISE
```
Le gestionnaire d’exception qui intercepte l’erreur est bien ici celui de l’appelant. Par ailleurs, comme nous retournons nous-même une exception, la requête ne retourne pas de résultat, mais une erreur : il n’y a plus personne pour récupérer l’exception, c’est donc PostgreSQL lui-même qui s’en charge.
### Abus des exceptions {#dev42-ch4-6-15}
- Ne pas abuser des exceptions
- les sous-transactions ne sont pas gratuites
- Si saturation, paramétre SLRU dédié (v17)
La levée d’une exception revient à poser un `SAVEPOINT` pendant la transaction (une forme de sous-transaction).
En cas de trop nombreuses levées d’exception, un des caches dédiés en mémoire partagée (SLRU) de PostgreSQL peut saturer et poser des soucis de performance (le *wait event* `SubtransSLRU` apparaît dans `pg_stat_activity.wait_event`). Depuis PostgreSQL 17 le DBA peut monter ce cache, avec le paramètre `subtransaction_buffers`.
## Sécurité {#dev42-ch4-7}
### Sécurité : droits {#dev42-ch4-7-1}
- L’exécution de la routine dépend du droit `EXECUTE`
- Par défaut, ce droit est donné à la création de la routine
- au propriétaire de la routine
- au groupe spécial PUBLIC
### Sécurité : ajout {#dev42-ch4-7-2}
- Ce droit peut être donné avec l’instruction SQL `GRANT` :
``` sql
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name
[ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ... ]
| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
```
### Sécurité : suppression {#dev42-ch4-7-3}
- Un droit peut être révoqué avec l’instruction SQL `REVOKE`
``` sql
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON { { FUNCTION | PROCEDURE | ROUTINE } function_name
[ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ... ]
| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
```
### Sécurité : SECURITY INVOKER/DEFINER {#dev42-ch4-7-4}
- `SECURITY INVOKER`
- la routine s’exécute avec les droits de l’utilisateur qui l’exécute
- `SECURITY DEFINER`
- la routine s’exécute avec les droits du propriétaire
- équivalent du `sudo` Unix
- Impérativement sécuriser les variables d’environnement
- et surtout `search_path`
Rappelons qu’une fonction s’exécute par défaut avec les droits d’accès de l’utilisateur qui l’exécute ; et que pour donner accès à ses données, un utilisateur peut mettre à disposition des autres des fonctions en mode `SECURITY DEFINER` qui profiteront de ses droits. Il est impératif de réinitialiser l’environnement et en particulier le `search_path` pour éviter des attaques de la part d’utilisateurs malveillants, et de restreindre au maximum l’accès à ces fonctions.
### Sécurité : LEAKPROOF {#dev42-ch4-7-5}
- `LEAKPROOF`
- indique au planificateur que la routine ne peut pas faire fuiter d’information de contexte
- réservé aux superutilisateurs
- si on la déclare telle, s’assurer que la routine est véritablement sûre !
- Option utile lorsque l’on utilise des vues avec l’option `security_barrier`
Certains utilisateurs créent des vues pour filtrer des lignes, afin de restreindre la visibilité sur certaines données. Or, cela peut se révéler dangereux si un utilisateur malintentionné a la possibilité de créer une fonction car il peut facilement contourner cette sécurité si cette option n’est pas utilisée, notamment en jouant sur des paramètres de fonction comme `COST`, qui permet d’indiquer au planificateur un coût estimé pour la fonction.
En indiquant un coût extrêmement faible, le planificateur aura tendance à réécrire la requête, et à déplacer l’exécution de la fonction dans le code même de la vue, avant l’application des filtres restreignant l’accès aux données : la fonction a donc accès a tout le contenu de la table, et peut faire fuiter des données normalement inaccessibles, par exemple à travers l’utilisation de la commande `RAISE`.
L’option `security_barrier` des vues dans PostgreSQL bloque ce comportement du planificateur, mais en conséquence empêche le choix de plans d’exécutions potentiellement plus performants. Déclarer une fonction avec l’option `LEAKPROOF` permet d’indiquer à PostgreSQL que celle-ci ne peut pas occasionner de fuite d’informations. Ainsi, le planificateur de PostgreSQL sait qu’il peut en optimiser l’exécution. Cette option n’est accessible qu’aux superutilisateurs.
### Sécurité : visibilité des sources - 1 {#dev42-ch4-7-6}
- Le code d’une fonction est visible par tout le monde
- y compris ceux qui n’ont pas le droit d’exécuter la fonction
- Vous devez donc écrire un code robuste
- pas espérer que, comme personne n’en a le code, personne ne trouvera de faille
- **Surtout pour les fonctions SECURITY DEFINER**
### Sécurité : visibilité des sources - 2 {#dev42-ch4-7-7}
``` sql
# SELECT proargnames, prosrc
FROM pg_proc WHERE proname='addition';
-[ RECORD 1 ]--------------------------
proargnames | {var1,var2}
prosrc |
: DECLARE
: somme ALIAS FOR $0;
: BEGIN
: somme := var1 + var2;
: RETURN somme;
: END;
:
```
La méta-commande psql `\df+ public.addition` permet également d’obtenir cette information.
### Sécurité : Injections SQL {#dev42-ch4-7-8}
- Les paramètres d’une routine doivent être considérés comme hostiles :
- ils contiennent des données non validées (qui appelle la routine ?)
- ils peuvent, si l’utilisateur est imaginatif, être utilisés pour exécuter du code
- Utiliser `quote_ident`, `quote_literal` et `quote_nullable`
- Utiliser aussi `format`
Voici un exemple simple :
``` sql
CREATE TABLE ma_table_secrete1 (b integer, a integer);
INSERT INTO ma_table_secrete1 SELECT i,i from generate_series(1,20) i;
CREATE OR REPLACE FUNCTION demo_injection ( param1 text, value1 text )
RETURNS SETOF ma_table_secrete1
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
-- Cette fonction prend un nom de colonne variable
-- et l'utilise dans une clause WHERE
-- Il faut donc une requête dynamique
-- Par contre, mon utilisateur 'normal' qui appelle
-- n'a droit qu'aux enregistrements où a<10
DECLARE
ma_requete text;
ma_ligne record;
BEGIN
ma_requete := 'SELECT * FROM ma_table_secrete1 WHERE ' || param1 || ' = ' ||
value1 || ' AND a < 10';
RETURN QUERY EXECUTE ma_requete;
END
$function$;
```
``` sql
# SELECT * from demo_injection ('b','2');
a | b
---+---
2 | 2
(1 row)
# SELECT * from demo_injection ('a','20');
a | b
---+---
(0 row)
```
Tout va bien, elle effectue ce qui est demandé.
Par contre, elle effectue aussi ce qui n’est pas prévu :
``` sql
# SELECT * from demo_injection ('1=1 --','');
a | b
-----+-----
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
6 | 6
7 | 7
8 | 8
9 | 9
10 | 10
11 | 11
12 | 12
13 | 13
14 | 14
15 | 15
16 | 16
17 | 17
18 | 18
19 | 19
20 | 20
(20 lignes)
```
Cet exemple est évidemment simplifié.
Une règle demeure : ne jamais faire confiance aux paramètres d’une fonction. Au minimum, un `quote_ident` pour param1 et un `quote_literal` pour val1 étaient obligatoires, pour se protéger de ce genre de problèmes.
## Optimisation {#dev42-ch4-8}
### Fonctions immutables, stables ou volatiles - 1 {#dev42-ch4-8-1}
- Par défaut, PostgreSQL considère que les fonctions sont `VOLATILE`
- `volatile` : fonction dont l’exécution ne peut ni ne doit être évitée
Les fonctions de ce type sont susceptibles de renvoyer un résultat différent à chaque appel, comme par exemple `random()` ou `setval()`.
Toute fonction ayant des effets de bords doit être qualifiée `volatile` dans le but d’éviter que PostgreSQL utilise un résultat intermédiaire déjà calculé et évite ainsi d’exécuter le code de la fonction.
À noter qu’il est possible de « forcer » le pré-calcul du résultat d’une fonction volatile dans une requête SQL en utilisant une sous-requête. Par exemple, dans l’exemple suivant, `random()` est exécutée pour chaque ligne de la table `ma_table`, et renverra donc une valeur différente par ligne :
``` sql
SELECT random() FROM ma_table;
```
Par contre, en utilisant une sous-requête, l’optimiseur va pré-calculer le résultat de `random()`… l’exécution sera donc plus rapide, mais le résultat différent, puisque la même valeur sera affichée pour toutes les lignes !
``` sql
SELECT ( SELECT random() ) FROM ma_table;
```
### Fonctions immutables, stables ou volatiles - 2 {#dev42-ch4-8-2}
- `immutable` : fonctions déterministes, dont le résultat peut être précalculé avant de planifier la requête.
Certaines fonctions que l’on écrit sont déterministes. C’est-à-dire qu’à paramètre(s) identique(s), le résultat est identique.
Le résultat de telles fonctions est alors remplaçable par son résultat avant même de commencer à planifier la requête.
Voici un exemple qui utilise cette particularité :
``` sql
create function factorielle (a integer) returns bigint as
$$
declare
result bigint;
begin
if a=1 then
return 1;
else
return a*(factorielle(a-1));
end if;
end;
$$
language plpgsql immutable;
```
``` sql
# CREATE TABLE test (a bigint UNIQUE);
CREATE TABLE
# INSERT INTO test SELECT generate_series(1,1000000);
INSERT 0 1000000
# ANALYZE test;
# EXPLAIN ANALYZE SELECT * FROM test WHERE a < factorielle(12);
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on test (cost=0.00..16925.00 rows=1000000 width=8)
(actual time=0.032..130.921 rows=1000000 loops=1)
Filter: (a < '479001600'::bigint)
Planning time: 896.039 ms
Execution time: 169.954 ms
(4 rows)
```
La fonction est exécutée une fois, remplacée par sa constante, et la requête est ensuite planifiée.
Si on déclare la fonction comme stable :
``` sql
# EXPLAIN ANALYZE SELECT * FROM test WHERE a < factorielle(12);
QUERY PLAN
----------------------------------------------------------
Index Only Scan using test_a_key on test
(cost=0.68..28480.67 rows=1000000 width=8)
(actual time=0.137..115.592 rows=1000000 loops=1)
Index Cond: (a < factorielle(12))
Heap Fetches: 0
Planning time: 4.682 ms
Execution time: 153.762 ms
(5 rows)
```
La requête est planifiée sans connaître `factorielle(12)`, donc avec une hypothèse très approximative sur la cardinalité. `factorielle(12)` est calculé, et la requête est exécutée. Grâce au `Index Only Scan`, le requête s’effectue rapidement.
Si on déclare la fonction comme volatile :
``` sql
# EXPLAIN ANALYZE SELECT * FROM test WHERE a < factorielle(12);
QUERY PLAN
-----------------------------------------------------------------------
Seq Scan on test (cost=0.00..266925.00 rows=333333 width=8)
(actual time=1.005..57519.702 rows=1000000 loops=1)
Filter: (a < factorielle(12))
Planning time: 0.388 ms
Execution time: 57573.508 ms
(4 rows)
```
La requête est planifiée, et factorielle(12) est calculé pour chaque enregistrement de la table, car on ne sait pas si elle retourne toujours le même résultat.
### Fonctions immutables, stables ou volatiles - 3 {#dev42-ch4-8-3}
- `stable` : fonction ayant un comportement **stable** au sein d’un même ordre SQL.
Ces fonctions retournent la même valeur pour la même requête SQL, mais peuvent retourner une valeur différente dans la prochaine instruction.
Il s’agit typiquement de fonctions dont le traitement dépend d’autres valeurs dans la base de données, ou bien de réglages de configuration. Les fonctions comme `to_char()`, `to_date()` sont `STABLE` et non `IMMUTABLE` car des paramètres de configuration (*locale* utilisée pour `to_char()`, *timezone* pour les fonctions temporelles, etc.) pourraient influer sur le résultat.
À noter au passage que les fonctions de la famille de `current_timestamp` (et donc le fréquemment utilisé `now()`) renvoient de plus une valeur constante au sein d’une même transaction.
PostgreSQL refusera de déclarer comme `STABLE` toute fonction modifiant des données : elle ne peut pas être stable si elle modifie la base.
### Optimisation : rigueur {#dev42-ch4-8-4}
- Fonction `STRICT`
- La fonction renvoie `NULL` si au moins un des arguments est `NULL`
Les fonctions définies comme `STRICT` ou `RETURNS NULL ON NULL INPUT` annule l’exécution de la requête si l’un des paramètres passés est `NULL`. Dans ce cas, la fonction est considérée comme ayant renvoyé `NULL`.
Si l’on reprend l’exemple de la fonction `factorielle()` :
``` sql
create or replace function factorielle (a integer) returns bigint as
$$
declare
result bigint;
begin
if a=1 then
return 1;
else
return a*(factorielle(a-1));
end if;
end;
$$
language plpgsql immutable STRICT;
```
on obtient le résultat suivant si elle est exécutée avec la valeur `NULL` passée en paramètre :
``` sql
# EXPLAIN ANALYZE SELECT * FROM test WHERE a < factorielle(NULL);
QUERY PLAN
---------------------------------------------------
Result (cost=0.00..0.00 rows=0 width=8)
(actual time=0.002..0.002 rows=0 loops=1)
One-Time Filter: false
Planning time: 0.100 ms
Execution time: 0.039 ms
(4 rows)
```
### Optimisation : EXCEPTION {#dev42-ch4-8-5}
- Un bloc contenant une clause `EXCEPTION` est plus coûteuse en entrée/sortie qu’un bloc sans
- un `SAVEPOINT` est créé à chaque fois pour pouvoir annuler le bloc uniquement.
- À utiliser avec parcimonie
- Un bloc `BEGIN` imbriqué a un coût aussi
- un `SAVEPOINT` est créé à chaque fois.
### Requête statique ou dynamique ? {#dev42-ch4-8-6}
- Les requêtes statiques :
- sont écrites « en dur » dans le code PL/pgSQL
- donc pas d’`EXECUTE` ou `PERFORM`
- sont préparées une fois par session, à leur première exécution
- peuvent avoir un plan générique lorsque c’est jugé utile par le planificateur
Avant la version 9.2, un plan générique (indépendant des paramètres de l’ordre SQL) était systématiquement généré et utilisé. Ce système permet de gagner du temps d’exécution si la requête est réutilisée plusieurs fois, et qu’elle est coûteuse à planifier.
Toutefois, un plan générique n’est pas forcément idéal dans toutes les situations, et peut conduire à des mauvaises performances.
Par exemple :
``` sql
SELECT * FROM ma_table WHERE col_pk = param_function ;
```
est un excellent candidat à être écrit statiquement : le plan sera toujours le même : on attaque l’index de la clé primaire pour trouver l’enregistrement.
``` sql
SELECT * FROM ma_table WHERE col_timestamp > param_function ;
```
est un moins bon candidat : le plan, idéalement, dépend de param_function : on ne parcourt pas la même fraction de la table suivant la valeur de param_function.
Par défaut, un plan générique ne sera utilisé dès la première exécution d’une requête statique que si celle-ci ne dépend d’aucun paramètre. Dans le cas contraire, cela ne se produira qu’au bout de plusieurs exécutions de la requête, et seulement si le planificateur détermine que les plans spécifiques utilisés n’apportent pas d’avantage par rapport au plan générique.
### Requête statique ou dynamique ? - 2 {#dev42-ch4-8-7}
- Les requêtes dynamiques :
- sont écrites avec un `EXECUTE`, `PERFORM`…
- sont préparées à chaque exécution
- ont un plan optimisé
- sont donc plus coûteuses en planification
- mais potentiellement plus rapides à l’exécution
L’écriture d’une requête dynamique est par contre un peu plus pénible, puisqu’il faut fabriquer un ordre SQL, puis le passer en paramètre à `EXECUTE`, avec tous les quote\_\* que cela implique pour en protéger les paramètres.
Pour se faciliter la vie, on peut utiliser `EXECUTE query USING param1, param2 …,` qui est même quelquefois plus lisible que la syntaxe en dur : les paramètres de la requête sont clairement identifiés dans cette syntaxe.
Par contre, la syntaxe `USING` n’est utilisable que si le nombre de paramètres est fixe.
### Requête statique ou dynamique ? -3 {#dev42-ch4-8-8}
- Alors, statique ou dynamique ?
- Si la requête est simple : statique
- peu de `WHERE`
- peu ou pas de jointure
- Sinon dynamique
La limite est difficile à placer, il s’agit de faire un compromis entre le temps de planification d’une requête (quelques dizaines de microsecondes pour une requête basique à potentiellement plusieurs secondes si on dépasse la dizaine de jointures) et le temps d’exécution.
Dans le doute, réalisez un test de performance de la fonction sur un jeu de données représentatif.
## Outils {#dev42-ch4-9}
- Deux outils disponibles
- un debugger
- un pseudo-profiler
Tous les outils d’administration PostgreSQL permettent d’écrire des routines stockées en PL/pgSQL, la plupart avec les fonctionnalités habituelles (comme le surlignage des mots clés, l’indentation automatique, etc.).
Par contre, pour aller plus loin, l’offre est restreinte. Il existe tout de même un debugger qui fonctionne avec pgAdmin 4, sous la forme d’une extension.
### pldebugger {#dev42-ch4-9-1}
- License Artistic 2.0
- Développé par EDB et intégrable dans pgAdmin
- Installé par défaut avec le one-click installer
- mais non activé
- Compilation nécessaire pour les autres systèmes
pldebugger est un outil initialement créé par Dave Page et Korry Douglas au sein d’EnterpriseDB, repris par la communauté. Il est proposé sous license libre (Artistic 2.0).
Il fonctionne grâce à des hooks implémentés dans la version 8.2 de PostgreSQL.
Il est assez peu connu, ce qui explique que peu l’utilisent. Seul l’outil d’installation « one-click installer » l’installe par défaut. Pour tous les autres systèmes, cela réclame une compilation supplémentaire. Cette compilation est d’ailleurs peu aisée étant donné qu’il n’utilise pas le système pgxs.
### pldebugger - Compilation {#dev42-ch4-9-2}
- Récupérer le source avec git
- Copier le répertoire dans le répertoire contrib des sources de PostgreSQL
- Et les suivre étapes standards
- make
- make install
Voici les étapes à réaliser pour compiler pldebugger en prenant pour hypothèse que les sources de PostgreSQL sont disponibles dans le répertoire `/usr/src/postgresql-10` et qu’ils ont été préconfigurés avec la commande `./configure` :
- Se placer dans le répertoire contrib des sources de PostgreSQL :
```text
$ cd /usr/src/postgresql-10/contrib
```
- Cloner le dépôt git :
```text
$ git clone git://git.postgresql.org/git/pldebugger.git
Cloning into 'pldebugger'...
remote: Counting objects: 441, done.
remote: Compressing objects: 100% (337/337), done.
remote: Total 441 (delta 282), reused 171 (delta 104)
Receiving objects: 100% (441/441), 170.24 KiB, done.
Resolving deltas: 100% (282/282), done.
```
- Se placer dans le nouveau répertoire `pldebugger` :
```text
$ cd pldebugger
```
- Compiler pldebugger :
```text
$ make
```
- Installer pldebugger :
```text
# make install
```
L’installation copie le fichier `plugin_debugger.so` dans le répertoire des bibliothèques partagées de PostgreSQL. L’installation copie ensuite les fichiers SQL et de contrôle de l’extension `pldbgapi` dans le répertoire `extension` du répertoire `share` de PostgreSQL.
### pldebugger - Activation {#dev42-ch4-9-3}
- Configurer `shared_preload_libraries`
- `shared_preload_libraries = 'plugin_debugger'`
- Redémarrer PostgreSQL
- Installer l’extension `pldbgapi` :
``` sql
CREATE EXTENSION pldbgapi;
```
La configuration du paramètre `shared_preload_libraries` permet au démarrage de PostgreSQL de laisser la bibliothèque `plugin_debugger` s’accrocher aux hooks de l’interpréteur PL/pgSQL. Du coup, pour que la modification de ce paramètre soit prise en compte, il faut redémarrer PostgreSQL.
L’interaction avec pldebugger se fait par l’intermédiaire de procédures stockées. Il faut donc au préalable créer ces procédures stockées dans la base contenant les procédures PL/pgSQL à débugguer. Cela se fait en créant l’extension :
``` sql
$ psql
psql (13.0)
Type "help" for help.
postgres# create extension pldbgapi;
CREATE EXTENSION
```
### auto_explain {#dev42-ch4-9-4}
- Mise en place globale (traces) :
- `shared_preload_libraries='auto_explain'` si global
- `ALTER DATABASE erp SET auto_explain.log_min_duration = '3s'`
- Ou par session :
- `LOAD 'auto_explain'`
- `SET auto_explain.log_analyze TO true;`
- `SET auto_explain.log_nested_statements TO true;`
`auto_explain` est une « contrib » officielle de PostgreSQL (et non une extension). Il permet de tracer le plan d’une requête. En général, on ne trace ainsi que les requêtes dont la durée d’exécution dépasse la durée configurée avec le paramètre’`auto_explain.log_min_duration`. Par défaut, ce paramètre est à -1 pour ne tracer aucun plan.
Comme dans un `EXPLAIN` classique, on peut activer toutes les options (par exemple `ANALYZE` ou `TIMING` avec, respectivement `SET auto_explain.log_analyze TO true;` et `SET auto_explain.log_timing TO true;`) mais l’impact en performance peut être important même pour les requêtes qui ne seront pas tracées.
D’autres options existent, qui reprennent les paramètres habituels d’`EXPLAIN`, notamment `auto_explain.log_buffers` et `auto_explain.log_settings` (voir la [documentation](https://docs.postgresql.fr/current/auto-explain.html)).
L’exemple suivant utilise deux fonctions imbriquées mais cela marche pour une simple requête :
``` sql
CREATE OR REPLACE FUNCTION table_nb_indexes (tabname IN text, nbi OUT int)
RETURNS int
LANGUAGE plpgsql
AS $$
BEGIN
SELECT COUNT(*) INTO nbi
FROM pg_index i INNER JOIN pg_class c ON (c.oid=indrelid)
WHERE relname LIKE tabname ;
RETURN ;
END ;
$$
;
CREATE OR REPLACE FUNCTION table_nb_col_indexes
(tabname IN text, nb_cols OUT int, nb_indexes OUT int)
RETURNS record
LANGUAGE plpgsql
AS $$
BEGIN
SELECT COUNT(*) INTO nb_cols
FROM pg_attribute
WHERE attname LIKE tabname ;
SELECT nbi INTO nb_indexes FROM table_nb_indexes (tabname) ;
RETURN ;
END ;
$$
;
```
Chargement dans la session d’`auto_explain` (si pas déjà présent dans `shared_preload_libraries`) :
``` sql
LOAD 'auto_explain' ;
```
Activation pour toutes les requêtes, avec les options `ANALYZE` et `BUFFERS`, puis affichage dans la console (si la sortie dans les traces ne suffit pas) :
``` sql
SET auto_explain.log_min_duration TO 0 ;
SET auto_explain.log_analyze TO on ;
SET auto_explain.log_buffers TO on ;
SET client_min_messages TO log ;
```
Test de la première fonction : le plan s’affiche, mais les compteurs (ici juste *shared hit*), ne concernent que la fonction dans son ensemble.
```text
postgres=# SELECT * FROM table_nb_col_indexes ('pg_class') ;
LOG: duration: 2.208 ms plan:
Query Text: SELECT * FROM table_nb_col_indexes ('pg_class') ;
Function Scan on table_nb_col_indexes (cost=0.25..0.26 rows=1 width=8)
(actual time=2.203..2.203 rows=1 loops=1)
Buffers: shared hit=294
nb_cols | nb_indexes
---------+------------
0 | 3
```
En activant `auto_explain.log_nested_statements`, on voit clairement les plans de chaque requête exécutée :
``` sql
SET auto_explain.log_nested_statements TO on ;
```
```text
postgres=# SELECT * FROM table_nb_col_indexes ('pg_class') ;
LOG: duration: 0.235 ms plan:
Query Text: SELECT COUNT(*) FROM pg_attribute
WHERE attname LIKE tabname
Aggregate (cost=65.95..65.96 rows=1 width=8)
(actual time=0.234..0.234 rows=1 loops=1)
Buffers: shared hit=24
-> Index Only Scan using pg_attribute_relid_attnam_index on pg_attribute
(cost=0.28..65.94 rows=1 width=0)
(actual time=0.233..0.233 rows=0 loops=1)
Index Cond: ((attname >= 'pg'::text) AND (attname < 'ph'::text))
Filter: (attname ~~ 'pg_class'::text)
Heap Fetches: 0
Buffers: shared hit=24
LOG: duration: 0.102 ms plan:
Query Text: SELECT COUNT(*) FROM pg_index i
INNER JOIN pg_class c ON (c.oid=indrelid)
WHERE relname LIKE tabname
Aggregate (cost=24.48..24.49 rows=1 width=8)
(actual time=0.100..0.100 rows=1 loops=1)
Buffers: shared hit=18
-> Nested Loop (cost=0.14..24.47 rows=1 width=0)
(actual time=0.096..0.099 rows=3 loops=1)
Buffers: shared hit=18
-> Seq Scan on pg_class c (cost=0.00..23.30 rows=1 width=4)
(actual time=0.091..0.093 rows=1 loops=1)
Filter: (relname ~~ 'pg_class'::text)
Rows Removed by Filter: 580
Buffers: shared hit=16
-> Index Only Scan using pg_index_indrelid_index on pg_index i
(cost=0.14..1.16 rows=1 width=4)
(actual time=0.003..0.004 rows=3 loops=1)
Index Cond: (indrelid = c.oid)
Heap Fetches: 0
Buffers: shared hit=2
LOG: duration: 0.703 ms plan:
Query Text: SELECT nbi FROM table_nb_indexes (tabname)
Function Scan on table_nb_indexes (cost=0.25..0.26 rows=1 width=4)
(actual time=0.702..0.702 rows=1 loops=1)
Buffers: shared hit=26
LOG: duration: 1.524 ms plan:
Query Text: SELECT * FROM table_nb_col_indexes ('pg_class') ;
Function Scan on table_nb_col_indexes (cost=0.25..0.26 rows=1 width=8)
(actual time=1.520..1.520 rows=1 loops=1)
Buffers: shared hit=59
nb_cols | nb_indexes
---------+------------
0 | 3
```
Cet exemple permet de mettre le doigt sur un petit problème de performance dans la fonction : le `_` est interprété comme critère de recherche. En modifiant le paramètre on peut constater le changement de plan au niveau des index :
```text
postgres=# SELECT * FROM table_nb_col_indexes ('pg\_class') ;
LOG: duration: 0.141 ms plan:
Query Text: SELECT COUNT(*) FROM pg_attribute
WHERE attname LIKE tabname
Aggregate (cost=56.28..56.29 rows=1 width=8)
(actual time=0.140..0.140 rows=1 loops=1)
Buffers: shared hit=24
-> Index Only Scan using pg_attribute_relid_attnam_index on pg_attribute
(cost=0.28..56.28 rows=1 width=0)
(actual time=0.138..0.138 rows=0 loops=1)
Index Cond: (attname = 'pg_class'::text)
Filter: (attname ~~ 'pg\_class'::text)
Heap Fetches: 0
Buffers: shared hit=24
LOG: duration: 0.026 ms plan:
Query Text: SELECT COUNT(*) FROM pg_index i
INNER JOIN pg_class c ON (c.oid=indrelid)
WHERE relname LIKE tabname
Aggregate (cost=3.47..3.48 rows=1 width=8) (actual time=0.024..0.024 rows=1 loops=1)
Buffers: shared hit=8
-> Nested Loop (cost=0.42..3.47 rows=1 width=0) (…)
Buffers: shared hit=8
-> Index Scan using pg_class_relname_nsp_index on pg_class c
(cost=0.28..2.29 rows=1 width=4)
(actual time=0.017..0.018 rows=1 loops=1)
Index Cond: (relname = 'pg_class'::text)
Filter: (relname ~~ 'pg\_class'::text)
Buffers: shared hit=6
-> Index Only Scan using pg_index_indrelid_index on pg_index i (…)
Index Cond: (indrelid = c.oid)
Heap Fetches: 0
Buffers: shared hit=2
LOG: duration: 0.414 ms plan:
Query Text: SELECT nbi FROM table_nb_indexes (tabname)
Function Scan on table_nb_indexes (cost=0.25..0.26 rows=1 width=4)
(actual time=0.412..0.412 rows=1 loops=1)
Buffers: shared hit=16
LOG: duration: 1.046 ms plan:
Query Text: SELECT * FROM table_nb_col_indexes ('pg\_class') ;
Function Scan on table_nb_col_indexes (cost=0.25..0.26 rows=1 width=8)
(actual time=1.042..1.043 rows=1 loops=1)
Buffers: shared hit=56
nb_cols | nb_indexes
---------+------------
0 | 3
```
Pour les procédures, il est possible de mettre en place cette trace avec `ALTER PROCEDURE … SET auto_explain.log_min_duration = 0`. Cela ne fonctionne pas pour les fonctions.
pgBadger est capable de lire les plans tracés par `auto_explain`, de les intégrer à son rapport et d’inclure un lien vers [depesz.com](https://explain.depesz.com/) pour une version plus lisible.
### pldebugger - Utilisation {#dev42-ch4-9-5}
- Via pgAdmin
Le menu contextuel pour accéder au débuggage d’une fonction :
Accéder au débuggage d’une fonction
La fenêtre du débugger :
Fenêtre du debugger
### log_functions {#dev42-ch4-9-6}
- Créé par Dalibo
- License BSD
- Compilation nécessaire
`log_functions` est un outil créé par Guillaume Lelarge au sein de Dalibo. Il est proposé sous license libre (BSD).
### log_functions - Compilation {#dev42-ch4-9-7}
- Récupérer l’archive sur PGXN.org
- Décompresser l’archive puis : `make USE_PGXS=1 && make USE_PGXS=1 install`
Voici les étapes à réaliser pour compiler log_functions en prenant pour hypothèse que les sources de PostgreSQL sont disponibles dans le répertoire `/home/guillaume/postgresql-9.1.4` et qu’ils ont été préconfigurés avec la commande `./configure` :
- Se placer dans le répertoire contrib des sources de PostgreSQL :
``` bash
$ cd /home/guillaume/postgresql-9.1.4/contrib
```
- Récupérer le dépôt git de log_functions :
``` bash
$ git://github.com/gleu/log_functions.git
Cloning into 'log_functions'...
remote: Counting objects: 24, done.
remote: Compressing objects: 100% (15/15), done.
remote: Total 24 (delta 8), reused 24 (delta 8)
Receiving objects: 100% (24/24), 11.71 KiB, done.
Resolving deltas: 100% (8/8), done.
```
- Se placer dans le nouveau répertoire `log_functions` :
``` bash
$ cd log_functions
```
- Compiler log_functions :
``` bash
$ make
```
- Installer log_functions :
``` bash
$ make install
```
L’installation copie le fichier `log_functions.o` dans le répertoire des bibliothèques partagées de PostgreSQL.
Si la version de PostgreSQL est supérieure ou égale à la 9.2, alors l’installation est plus simple et les sources de PostgreSQL ne sont plus nécessaires.
Téléchargement de log_functions :
``` bash
wget http://api.pgxn.org/dist/log_functions/1.0.0/log_functions-1.0.0.zip
```
puis décompression et installation de l’extension :
``` bash
unzip log_functions-1.0.0.zip
cd log_functions-1.0.0/
make USE_PGXS=1 && make USE_PGXS=1 install
```
L’installation copie aussi le fichier `log_functions.so` dans le répertoire des bibliothèques partagées de PostgreSQL.
### log_functions - Activation {#dev42-ch4-9-8}
- Permanente
- `shared_preload_libraries = 'log_functions'`
- Redémarrage de PostgreSQL
- Au cas par cas
- `LOAD 'log_functions'`
Le module log_functions est activable de deux façons.
La première consiste à demander à PostgreSQL de le charger au démarrage. Pour cela, il faut configurer la variable `shared_preload_libraries`, puis redémarrer PostgreSQL pour que le changement soit pris en compte.
La deuxième manière de l’activer est de l’activer seulement au moment où son utilisation s’avère nécessaire. Il faut utiliser pour cela la commande `LOAD` en précisant le module à charger.
La première méthode a un coût en terme de performances car le module s’exécute à chaque exécution d’une procédure stockée écrite en PL/pgSQL. La deuxième méthode rend l’utilisation du profiler un peu plus complexe. Le choix est donc laissé à l’administrateur.
### log_functions - Configuration {#dev42-ch4-9-9}
- 5 paramètres en tout
- À configurer
- dans Postgresql.conf
- ou avec `SET`
Les informations de profilage récupérées par log_functions sont envoyées dans les traces de PostgreSQL. Comme cela va générer plus d’écriture, et donc plus de lenteurs, il est possible de configurer chaque trace.
La configuration se fait soit dans le fichier `postgresql.conf` soit avec l’instruction `SET`.
Voici la liste des paramètres et leur utilité :
- `log_functions.log_declare`, à mettre à true pour tracer le moment où PL/pgSQL exécute la partie `DECLARE` d’une procédure stockée ;
- `log_functions.log_function_begin`, à mettre à true pour tracer le moment où PL/pgSQL exécute la partie `BEGIN` d’une procédure stockée ;
- `log_functions.log_function_end`, à mettre à true pour tracer le moment où PL/pgSQL exécute la partie `END` d’une procédure stockée ;
- `log_functions.log_statement_begin`, à mettre à true pour tracer le moment où PL/pgSQL commence l’exécution d’une instruction dans une procédure stockée ;
- `log_functions.log_statement_end`, à mettre à true pour tracer le moment où PL/pgSQL termine l’exécution d’une instruction dans une procédure stockée.
Par défaut, seuls `log_statement_begin` et `log_statement_end` sont à false pour éviter la génération de traces trop importantes.
### log_functions - Utilisation {#dev42-ch4-9-10}
- Exécuter des procédures stockées en PL/pgSQL
- Lire les journaux applicatifs
- `grep` très utile
Voici un exemple d’utilisation de cet outil :
``` sql
b2# SELECT incremente(4);
incremente
------------
5
(1 row)
b2# LOAD 'log_functions';
LOAD
b2# SET client_min_messages TO log;
LOG: duration: 0.136 ms statement: set client_min_messages to log;
SET
b2# SELECT incremente(4);
LOG: log_functions, DECLARE, incremente
LOG: log_functions, BEGIN, incremente
CONTEXT: PL/pgSQL function "incremente" during function entry
LOG: valeur de b : 5
LOG: log_functions, END, incremente
CONTEXT: PL/pgSQL function "incremente" during function exit
LOG: duration: 118.332 ms statement: select incremente(4);
incremente
------------
5
(1 row)
```
## Conclusion {#dev42-ch4-10}
- PL/pgSQL est un langage puissant
- Seul inconvénient
- sa lenteur par rapport à d’autres PL comme PL/perl ou C
- PL/perl est très efficace pour les traitements de chaîne notamment
- Permet néanmoins de traiter la plupart des cas, de façon simple et efficace
### Pour aller plus loin {#dev42-ch4-10-1}
- Documentation officielle
- « Chapitre 40. PL/pgSQL - Langage de procédures SQL »
Quelques liens utiles dans la documentation de PostgreSQL :
- [Chapitre 40. PL/pgSQL - Langage de procédures SQL](https://docs.postgresql.fr/current/plpgsql.html)
- [Annexe A. Codes d’erreurs de PostgreSQL](https://docs.postgresql.fr/current/errcodes-appendix.html)
### Questions {#dev42-ch4-10-2}
N’hésitez pas, c’est le moment !
## Travaux pratiques {#dev42-ch4-11}
La version en ligne des solutions de ces TP est disponible sur .
### Exceptions {#dev42-ch4-11-1}
> Ré-écrire la fonction suivante pour intercepter une division par zéro (utiliser l’exception DIVISION_BY_ZERO), afficher un message et renvoyer `Nan`.
``` sql
CREATE OR REPLACE FUNCTION division (arg1 integer, arg2 integer)
RETURNS float
AS $BODY$
BEGIN
RETURN arg1::float / arg2::float;
END
$BODY$ LANGUAGE plpgsql;
```
### Triggers {#dev42-ch4-11-2}
> Créer la table suivante :
``` sql
CREATE TABLE stock (
article_id integer PRIMARY KEY,
dateheure timestamptz DEFAULT now(),
stock integer NOT NULL DEFAULT 0 );
```
> Créer une table `log_stock` comme ci-desssous pour tracer toutes les modifications du champ `nombre` dans `stock`, avec l’ancienne et la nouvelle valeur, l’article, l’année, le moment, et qui a fait la modification (variable `session_user`). Créer un trigger `AFTER` par ligne, et sa fonction associée pour tracer cela. Insérer quelques lignes, les mettre à jour, en supprimer une.
``` sql
CREATE TABLE log_stock (
id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
utilisateur text,
dateheure timestamptz,
article_id integer,
ancien_stock integer,
nouveau_stock integer);
```
> Dans un autre trigger `BEFORE DELETE` par ligne, interdire la suppression des lignes dans `stock`.\
> Afficher un message d’erreur dans les logs dans ce cas.
> Tenter d’interdire l’exécution d’un `TRUNCATE` avec un trigger `BEFORE TRUNCATE` (Il faudra un `RAISE EXCEPTION`).
> Quelle alternative y a-t-il pour interdire `DELETE` ou `TRUNCATE` sur cette table ? Tester avec un nouvel utilisateur nommé **pierre**.
> Supprimer le trigger `BEFORE DELETE` pour le remplacer par un autre plus complet qui :
>
> - interdit les `DELETE` comme précédemment ;
> - affiche un message `NOTICE` quand le stock est ou devient inférieur ou égal à 5 (à moins qu’il ne remonte) ;
> - affiche un message `WARNING` quand il est ou tombe à 0 ;
> - remplace une valeur négative ou NULL par un 0.\
> Tester et vérifier dans `log_stock`.
> Insérer des lignes et comparer les performances de la requête suivante avant et après la suppression de tous les triggers :
``` sql
INSERT INTO stock (article_id, stock)
SELECT i, 10 FROM generate_series (5000, 15000) i ;
```
> Réécrire le trigger de trace en utilisant des tables de transition (il va falloir trois triggers et une fonction qui distingue les trois cas `UPSERT`/`INSERT`/`DELETE`).\
> En profiter pour ajouter des `RAISE NOTICE` qui affichent les valeurs des fonctions `current_user`, `session_user`, `system_user` (sur PostgreSQL 15 ou plus).
> Créer un rôle **adminlog**.\
> Lui donner la propriété et les droits exclusifs sur `log_stock`.\
> Modifier les droits de la fonction de trace de `stock` pour qu’elle fonctionne encore.
### Curseur {#dev42-ch4-11-3}
> Écrire une fonction `checkadmin` qui utilise un curseur pour parcourir la table `pg_stat_user_tables`, et affiche un `RAISE LOG` dans les traces si `last_autovacuum` et `last_vacuum` sont tous deux vides.\
> Afficher le numéro de la ligne dans le curseur (tri par nom de table).\
> Sortir après le troisième avertissement, sinon retourner le nombre de lignes lues.
## Travaux pratiques (solutions) {#dev42-ch4-12}
### Exceptions {#dev42-ch4-12-1}
> Ré-écrire la fonction suivante pour intercepter une division par zéro (utiliser l’exception DIVISION_BY_ZERO), afficher un message et renvoyer `Nan`.
``` sql
CREATE OR REPLACE FUNCTION division (arg1 integer, arg2 integer)
RETURNS float
AS $BODY$
BEGIN
RETURN arg1::float / arg2::float;
END
$BODY$ LANGUAGE plpgsql;
```
Effectivement :
``` sql
SELECT division (1,5);
```
``` default
division
----------
0.2
```
``` sql
SELECT division (5,0);
```
``` default
ERROR: division by zero
CONTEXTE : PL/pgSQL function division(integer,integer) line 3 at RETURN
```
La division par zéro peut s’intercepter ainsi :
``` sql
CREATE OR REPLACE FUNCTION division (arg1 integer, arg2 integer)
RETURNS float AS
$BODY$
BEGIN
RETURN arg1::float/arg2::float;
EXCEPTION WHEN DIVISION_BY_ZERO THEN
RAISE LOG '[%] %', SQLSTATE, SQLERRM;
RETURN 'NaN';
END
$BODY$ LANGUAGE plpgsql ;
```
Requêtage :
``` sql
SELECT division (5,0);
```
``` default
division
----------
NaN
```
Mais le message d’erreur ne s’affiche que dans les traces (`postgresql.log`), à moins de changer ce paramètre dans la session :
``` sql
SET client_min_messages TO log; -- le défaut est 'notice'
SELECT division(1,5), division (5,0);
```
``` default
LOG: [22012] division by zero
division | division
----------+----------
0.2 | NaN
```
Une autre possibilité est d’utiliser `RAISE NOTICE` au lieu de `RAISE LOG`. Auquel cas le message apparaîtra dans la session et non dans les traces du serveur.
### Triggers {#dev42-ch4-12-2}
> Créer la table suivante :
``` sql
CREATE TABLE stock (
article_id integer PRIMARY KEY,
dateheure timestamptz DEFAULT now(),
stock integer NOT NULL DEFAULT 0 );
```
> Créer une table `log_stock` comme ci-desssous pour tracer toutes les modifications du champ `nombre` dans `stock`, avec l’ancienne et la nouvelle valeur, l’article, l’année, le moment, et qui a fait la modification (variable `session_user`). Créer un trigger `AFTER` par ligne, et sa fonction associée pour tracer cela. Insérer quelques lignes, les mettre à jour, en supprimer une.
``` sql
CREATE TABLE log_stock (
id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
utilisateur text,
dateheure timestamptz,
article_id integer,
ancien_stock integer,
nouveau_stock integer);
```
La colonne `id` n’est pas totalement nécessaire, mais il est préférable de poser des clés primaires sur toutes les tables.
La fonction trigger :
``` sql
CREATE OR REPLACE FUNCTION stock_trace()
RETURNS TRIGGER AS
$BODY$
DECLARE
v_articleid integer;
v_ancien_stock integer;
v_nouveau_stock integer;
v_atracer boolean := false;
BEGIN
--
-- Ce code est volontairement verbeux pour pointer
-- des différents cas où des variables peuvent être NULL.
-- On pourrait économiser la plupart des variables.
--
IF TG_OP = 'INSERT' THEN
-- cas de l'insertion
v_atracer := true;
v_articleid := NEW.article_id;
v_ancien_stock := NULL;
v_nouveau_stock := NEW.stock;
ELSEIF TG_OP = 'UPDATE' THEN
-- Ce test pour vérifier que le contenu de stock a bien changé
v_atracer := OLD.stock != NEW.stock;
v_articleid := NEW.article_id;
v_ancien_stock := OLD.stock;
v_nouveau_stock := NEW.stock;
ELSEIF TG_OP = 'DELETE' THEN
-- cas de la suppression
v_atracer := true;
v_articleid := OLD.article_id;
v_ancien_stock := OLD.stock;
v_nouveau_stock := NULL;
END IF;
-- pour débogage éventuel
-- RAISE NOTICE '% : % %->% %',
-- TG_OP, v_articleid, v_ancien_stock, v_nouveau_stock, v_atracer ;
IF v_atracer THEN
INSERT INTO log_stock
(utilisateur, dateheure, article_id,
ancien_stock, nouveau_stock)
VALUES
(session_user, now(), v_articleid,
v_ancien_stock, v_nouveau_stock);
END IF;
RETURN NEW ;
END $BODY$
LANGUAGE plpgsql ;
```
Le trigger :
``` sql
CREATE TRIGGER stock_trace
AFTER INSERT OR UPDATE OR DELETE
ON stock FOR EACH ROW
EXECUTE PROCEDURE stock_trace();
```
Test :
``` sql
\d stock
```
``` default
Table « public.stock »
Colonne | Type | Collationnement | NULL-able | Par défaut
------------+--------------------------+-----------------+-----------+------------
article_id | integer | | not null |
dateheure | timestamp with time zone | | | now()
stock | integer | | not null | 0
Index :
"stock_pkey" PRIMARY KEY, btree (article_id)
Triggers :
stock_trace AFTER INSERT OR DELETE OR UPDATE ON stock FOR EACH ROW EXECUTE FUNCTION stock_trace()
```
``` sql
-- Alimentation et vérification du contenu
INSERT INTO stock (article_id, stock)
VALUES (33, 10), (42, 6), (99, 20) ;
UPDATE stock SET stock = 9
WHERE article_id = 33 ;
UPDATE stock SET stock = 7
WHERE article_id = 42 ;
UPDATE stock SET stock = 7 -- répétition
WHERE article_id = 42 ;
DELETE FROM stock
WHERE article_id = 99 ;
TABLE stock ;
```
``` default
article_id | dateheure | stock
------------+------------------------------+-------
33 | 2025-02-07 18:19:31.57928+01 | 9
42 | 2025-02-07 18:19:31.57928+01 | 7
```
La table de trace contient alors ce qui suit. Noter que l’`UPDATE` inutile n’est pas tracé (on pourrait en décider autrement).
``` sql
SELECT
utilisateur, dateheure::date, article_id, ancien_stock, nouveau_stock
FROM log_stock ;
```
``` default
utilisateur | dateheure | article_id | ancien_stock | nouveau_stock
-------------+------------+------------+--------------+---------------
postgres | 2025-02-07 | 33 | | 10
postgres | 2025-02-07 | 42 | | 6
postgres | 2025-02-07 | 99 | | 20
postgres | 2025-02-07 | 33 | 10 | 9
postgres | 2025-02-07 | 42 | 6 | 7
postgres | 2025-02-07 | 99 | 20 |
```
> Dans un autre trigger `BEFORE DELETE` par ligne, interdire la suppression des lignes dans `stock`.\
> Afficher un message d’erreur dans les logs dans ce cas.
On pourrait continuer à utiliser le trigger `AFTER`, mais, sur le principe, il vaut mieux interdire une modification plutôt que de l’annuler.
La fonction trigger :
``` sql
CREATE OR REPLACE FUNCTION interdit_suppression ()
RETURNS TRIGGER AS $BODY$
BEGIN
RAISE WARNING '% : article % - Suppression de ligne de stock interdite',
TG_OP, OLD.article_id ;
RETURN NULL ; -- annulation de l'ordre mais pas de la transaction
END $BODY$ LANGUAGE plpgsql ;
```
Le trigger :
``` sql
CREATE TRIGGER stock_interdit_delete
BEFORE DELETE
ON stock FOR EACH ROW
EXECUTE PROCEDURE interdit_suppression();
```
Dans la transaction suivante, le `DELETE` est annulé mais la transaction se poursuit et l’`INSERT` a bien lieu :
``` sql
BEGIN ;
DELETE FROM stock
WHERE article_id = 33 ;
INSERT INTO stock (article_id, stock)
VALUES (100, 1000) ;
COMMIT ;
TABLE stock ;
```
``` default
…
WARNING: DELETE : article 33 - Suppression de ligne de stock interdite
…
article_id | dateheure | stock
------------+-------------------------------+-------
33 | 2025-02-07 18:19:31.57928+01 | 9
42 | 2025-02-07 18:19:31.57928+01 | 7
100 | 2025-02-07 18:19:31.584649+01 | 1000
```
> Si l’on préfère une erreur franche avec annulation de la transaction en cours, il faut un `RAISE EXCEPTION` (et non `RAISE WARNING`), qui donnera lieu à un message `ERROR` (et non `WARNING`). La transaction sera interrompue.
> Tenter d’interdire l’exécution d’un `TRUNCATE` avec un trigger `BEFORE TRUNCATE` (Il faudra un `RAISE EXCEPTION`).
Effectivement, le trigger `BEFORE DELETE` ne protège pas d’un `TRUNCATE` :
``` sql
BEGIN ;
TRUNCATE TABLE stock ;
TABLE stock;
ROLLBACK ;
```
``` default
article_id | dateheure | stock
------------+-----------+-------
(0 ligne)
```
Cependant, un trigger sur `TRUNCATE` est un trigger par ordre (`FOR EACH STATEMENT`). On ne peut annuler son fonctionnement avec un simple `RETURN NULL`. Il faut un `RAISE EXCEPTION`, et malheureusement, cela implique d’interrompre la transaction.
``` sql
CREATE OR REPLACE FUNCTION interdit_truncate()
RETURNS TRIGGER AS $$
BEGIN
RAISE EXCEPTION '% : Suppression de ligne de stock interdite', TG_OP ;
RETURN NULL ;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER stock_interdit_truncate
BEFORE TRUNCATE
ON stock FOR EACH STATEMENT
EXECUTE PROCEDURE interdit_truncate();
```
``` sql
TRUNCATE stock ;
TABLE stock ;
```
``` default
ERROR: TRUNCATE : Suppression de ligne de stock interdite
CONTEXTE : PL/pgSQL function interdit_truncate() line 3 at RAISE
```
``` sql
TABLE stock ;
```
``` default
article_id | dateheure | stock
------------+-------------------------------+-------
33 | 2025-02-07 18:19:31.57928+01 | 9
42 | 2025-02-07 18:19:31.57928+01 | 7
100 | 2025-02-07 18:19:31.584649+01 | 1000
```
Avec tous ces triggers, la définition de la table devient lourde :
``` sql
\d stock
```
``` default
Table « public.stock »
Colonne | Type | Collationnement | NULL-able | Par défaut
------------+--------------------------+-----------------+-----------+------------
article_id | integer | | not null |
dateheure | timestamp with time zone | | | now()
stock | integer | | not null | 0
Index :
"stock_pkey" PRIMARY KEY, btree (article_id)
Triggers :
stock_trace AFTER INSERT OR DELETE OR UPDATE ON stock FOR EACH ROW EXECUTE FUNCTION stock_trace()
stock_interdit_delete BEFORE DELETE ON stock FOR EACH ROW EXECUTE FUNCTION interdit_suppression()
stock_interdit_truncate BEFORE TRUNCATE ON stock FOR EACH STATEMENT EXECUTE FUNCTION interdit_truncate()
```
> Quelle alternative y a-t-il pour interdire `DELETE` ou `TRUNCATE` sur cette table ? Tester avec un nouvel utilisateur nommé **pierre**.
- Il ne faut pas oublier que les droits peuvent interdire aux utilisateurs (sauf le superutilisateur) d’effectuer ces ordres. Cela évite d’avoir à gérer des triggers, et la gestion des droits peut être plus fine :
``` sql
-- Exécuter en tant que superutilisateur
REVOKE TRUNCATE, DELETE ON TABLE stock FROM public;
CREATE ROLE pierre LOGIN ;
GRANT ALL ON TABLE stock TO pierre ;
SET ROLE pierre ;
UPDATE stock SET stock=998
WHERE article_id = 100 ;
DELETE FROM stock WHERE article_id = 100;
TRUNCATE TABLE stock ;
```
``` default
NOTICE: UPDATE : 100 1000->1000 t
ERROR: permission denied for table log_stock
```
- Une vue masquant la table est une autre alternative pour interdire `TRUNCATE` (un `TRUNCATE` sur une vue ne fonctionne pas).
``` sql
SET ROLE postgres ;
CREATE VIEW v_stock AS SELECT * FROM stock ;
GRANT ALL ON v_stock TO pierre ;
SET ROLE pierre ;
TRUNCATE v_stock ;
```
``` default
ERROR: "v_stock" is not a table
```
> Supprimer le trigger `BEFORE DELETE` pour le remplacer par un autre plus complet qui :
>
> - interdit les `DELETE` comme précédemment ;
> - affiche un message `NOTICE` quand le stock est ou devient inférieur ou égal à 5 (à moins qu’il ne remonte) ;
> - affiche un message `WARNING` quand il est ou tombe à 0 ;
> - remplace une valeur négative ou NULL par un 0.\
> Tester et vérifier dans `log_stock`.
À la suppression du trigger `BEFORE DELETE`, ne pas oublier de supprimer la fonction associée :
``` sql
-- en tant que superutilisateur
DROP TRIGGER stock_interdit_delete ON stock ;
DROP FUNCTION interdit_suppression ;
```
``` sql
CREATE OR REPLACE FUNCTION verifications_stock ()
RETURNS TRIGGER AS $BODY$
BEGIN
IF TG_OP = 'DELETE' THEN
RAISE WARNING
'% : article % - Suppression de ligne de stock interdite',
TG_OP, OLD.article_id ;
RETURN NULL ;
ELSEIF TG_OP IN ('UPDATE','INSERT') THEN
IF NEW.stock BETWEEN 1 AND 5 AND NEW.stock < OLD.stock THEN
RAISE NOTICE 'article % - Valeur % faible !',
OLD.article_id, NEW.stock ;
ELSEIF coalesce(NEW.stock,0) <= 0 THEN
RAISE WARNING 'article % - Plus de stock !', OLD.article_id ;
NEW.stock = 0;
END IF ;
RETURN NEW ;
END IF ;
END $BODY$ LANGUAGE plpgsql ;
```
``` sql
CREATE TRIGGER stock_verifications
BEFORE DELETE OR INSERT OR UPDATE
ON stock FOR EACH ROW
EXECUTE PROCEDURE verifications_stock();
```
Test :
``` sql
INSERT INTO stock (article_id, stock)
VALUES (1789, 3) ;
UPDATE stock SET stock = stock - 2 -- stock : 1
WHERE article_id = 1789 ;
UPDATE stock SET stock = stock - 2 -- stock : 0
WHERE article_id = 1789 ;
UPDATE stock SET stock = stock - 2 -- idem
WHERE article_id = 1789 ;
UPDATE stock SET stock = stock + 20 -- stock 20
WHERE article_id = 1789 ;
UPDATE stock SET stock = NULL
WHERE article_id = 1789 ;
```
``` default
INSERT INTO stock (article_id, stock)
VALUES (1789, 3) ;
INSERT 0 1
UPDATE stock SET stock = stock - 2 -- stock : 1
WHERE article_id = 1789 ;
NOTICE: article 1789 - Valeur 1 faible !
UPDATE 1
UPDATE stock SET stock = stock - 2 -- stock : 0
WHERE article_id = 1789 ;
WARNING: article 1789 - Plus de stock !
UPDATE 1
UPDATE stock SET stock = stock - 2 -- idem
WHERE article_id = 1789 ;
WARNING: article 1789 - Plus de stock !
UPDATE 1
UPDATE stock SET stock = stock + 20 -- stock 20
WHERE article_id = 1789 ;
UPDATE 1
UPDATE stock SET stock = NULL
WHERE article_id = 1789 ;
WARNING: article 1789 - Plus de stock !
UPDATE 1
```
Noter que le `SET stock = NULL` ne provoque pas d’erreur sur la contrainte `NOT NULL` de la colonne `stock`. La vérification se fait après l’ordre SQL et le trigger `BEFORE`.
La trace reflète bien les valeurs modifiées :
``` sql
SELECT
utilisateur, dateheure::date, article_id, ancien_stock, nouveau_stock
FROM log_stock WHERE article_id = 1789 ;
```
``` default
utilisateur | dateheure | article_id | ancien_stock | nouveau_stock
-------------+------------+------------+--------------+---------------
postgres | 2025-02-07 | 1789 | | 3
postgres | 2025-02-07 | 1789 | 3 | 1
postgres | 2025-02-07 | 1789 | 1 | 0
postgres | 2025-02-07 | 1789 | 0 | 20
postgres | 2025-02-07 | 1789 | 20 | 0
```
> Insérer des lignes et comparer les performances de la requête suivante avant et après la suppression de tous les triggers :
``` sql
INSERT INTO stock (article_id, stock)
SELECT i, 10 FROM generate_series (5000, 15000) i ;
```
``` sql
BEGIN ;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, VERBOSE)
UPDATE stock SET stock = 9
WHERE article_id BETWEEN 5000 and 15000 ;
ROLLBACK ;
```
``` sql
DROP TRIGGER stock_verifications ON stock ;
DROP TRIGGER stock_interdit_truncate ON stock ;
DROP TRIGGER stock_trace ON stock ;
```
``` sql
BEGIN ;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, VERBOSE)
UPDATE stock SET stock = 9
WHERE article_id BETWEEN 5000 and 15000 ;
ROLLBACK ;
```
Ce test est un peu rapide, mais il doit montrer un rapport de temps d’exécution de 1 à 3 ou 1 à 4, bien sûr en défaveur des triggers. La trace est l’opération la plus lourde (il faut `EXPLAIN (VERBOSE)` pour l’afficher) :
``` default
…
Trigger stock_trace: time=45.730 calls=10001
Trigger stock_verifications: time=6.725 calls=10001
Execution Time: 79.630 ms
```
Sans les triggers, sur la même machine :
``` default
…
Execution Time: 25.583 ms
```
> Réécrire le trigger de trace en utilisant des tables de transition (il va falloir trois triggers et une fonction qui distingue les trois cas `UPSERT`/`INSERT`/`DELETE`).\
> En profiter pour ajouter des `RAISE NOTICE` qui affichent les valeurs des fonctions `current_user`, `session_user`, `system_user` (sur PostgreSQL 15 ou plus).
La première difficulté est que les tables correspondant à `NEW` et `OLD` ne sont pas définies dans tous les cas. La seconde est qu’il est délicat (et lourd) de rapprocher les valeurs des deux tableaux `NEW` et `OLD` si les deux sont remplis, on se contente donc ici d’ajouter deux lignes séparées pour l’`UPDATE`.
``` sql
CREATE OR REPLACE FUNCTION stock_trace2()
RETURNS TRIGGER AS $BODY$
DECLARE
nb int DEFAULT 0 ;
BEGIN
IF TG_OP IN ('UPDATE','DELETE') THEN
INSERT INTO log_stock
(utilisateur, dateheure, article_id,
ancien_stock, nouveau_stock)
SELECT
session_user, now(), oldstock.article_id,
oldstock.stock, NULL
FROM oldstock ;
-- pour débogage
RAISE NOTICE 'Trigger de trace pour % (utilisateur %/%/%)',
TG_OP, current_user, session_user, system_user ;
END IF ;
IF TG_OP IN ('UPDATE','INSERT') THEN
INSERT INTO log_stock
(utilisateur, dateheure, article_id,
ancien_stock, nouveau_stock)
SELECT
session_user, now(), newstock.article_id,
NULL, newstock.stock
FROM newstock ;
-- pour débogage
RAISE NOTICE 'Trigger de trace pour % (utilisateur %/%/%)',
TG_OP, current_user, session_user, system_user ;
END IF ;
RETURN NEW ;
END $BODY$ LANGUAGE plpgsql ;
```
Les triggers :
``` sql
CREATE TRIGGER stock_trace_ins
AFTER INSERT ON stock
REFERENCING NEW TABLE AS newstock
FOR EACH STATEMENT
EXECUTE PROCEDURE stock_trace2();
```
``` sql
CREATE TRIGGER stock_trace_del
AFTER DELETE ON stock
REFERENCING OLD TABLE AS oldstock
FOR EACH STATEMENT
EXECUTE PROCEDURE stock_trace2();
```
``` sql
-- seul trigger à gérer REFERENCING OLD et NEW
CREATE TRIGGER stock_trace_upd
AFTER UPDATE ON stock
REFERENCING
OLD TABLE AS oldstock
NEW TABLE AS newstock
FOR EACH STATEMENT
EXECUTE PROCEDURE stock_trace2();
```
Test basique :
``` sql
TRUNCATE TABLE log_stock ;
INSERT INTO stock (article_id, stock)
VALUES (1000, 3) ;
UPDATE stock SET stock = 5
WHERE article_id = 1000 ;
DELETE FROM stock
WHERE article_id = 1000 ;
SELECT dateheure::time, article_id, ancien_stock, nouveau_stock
FROM log_stock
WHERE article_id = 1000 ;
```
``` default
dateheure | article_id | ancien_stock | nouveau_stock
-----------------+------------+--------------+---------------
23:23:33.325255 | 1000 | | 3
23:23:33.326288 | 1000 | 3 |
23:23:33.326288 | 1000 | | 5
23:23:33.327044 | 1000 | 5 |
```
``` sql
BEGIN ;
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, VERBOSE)
UPDATE stock SET stock = 9
WHERE article_id BETWEEN 5000 and 15000 ;
COMMIT ;
```
Le trigger n’est appelé qu’une seule fois par opération, le gain est donc sensible. Cette simple requête reste tout de même deux fois plus lente avec le trigger.
``` default
Trigger stock_trace_upd: time=24.596 calls=1
Execution Time: 53.306 ms
```
``` sql
-- Test avec un article
SELECT dateheure::time, article_id, ancien_stock, nouveau_stock
FROM log_stock
WHERE article_id = 9999 ;
```
``` default
dateheure | article_id | ancien_stock | nouveau_stock
-----------------+------------+--------------+---------------
23:26:41.040846 | 9999 | 10 |
23:26:41.040846 | 9999 | | 9
```
> Créer un rôle **adminlog**.\
> Lui donner la propriété et les droits exclusifs sur `log_stock`.\
> Modifier les droits de la fonction de trace de `stock` pour qu’elle fonctionne encore.
Pour des raisons de sécurité, il est logique que l’utilisateur applicatif ne puisse pas modifier la table de trace `log_stock`.
``` sql
-- En tant que superutilisateur
CREATE ROLE adminlog ;
ALTER TABLE log_stock OWNER TO adminlog ;
REVOKE ALL ON TABLE log_stock FROM public ;
SET ROLE pierre ;
```
``` sql
\d
```
``` default
Schéma | Nom | Type | Propriétaire
--------+------------------+----------+--------------
public | log_stock | table | adminlog
public | log_stock_id_seq | séquence | adminlog
public | stock | table | postgres
…
```
``` sql
\d
```
``` default
Schéma | Nom | Type | Propriétaire
--------+------------------+----------+--------------
public | log_stock | table | adminlog
public | log_stock_id_seq | séquence | adminlog
public | stock | table | postgres
…
```
Suite au changement de droits, une modification de la table échoue :
``` sql
INSERT INTO stock (article_id, stock)
VALUES (20000, 10) ;
```
``` default
ERROR: permission denied for table log_stock
CONTEXTE : SQL statement "INSERT INTO log_stock
…
PL/pgSQL function stock_trace2() line 17 at SQL statement
```
La fonction `stock_trace2` est `SECURITY INVOKER` (elle utilise les droits de l’utilisateur), c’est le défaut. On peut lui permettre de tourner avec les droits plus étendus de son propriétaire (`SECURITY DEFINER`). Il faut donc la donner à **adminlog**, puis donner les droits d’exécution aux utilisateurs.
``` sql
SET ROLE postgres ;
ALTER FUNCTION stock_trace2() OWNER TO adminlog ;
ALTER FUNCTION stock_trace2() SECURITY DEFINER ;
```
``` sql
SET ROLE pierre ;
INSERT INTO stock (article_id, stock)
VALUES (20000, 10) ;
```
``` default
NOTICE: Trigger de trace pour INSERT (utilisateur adminlog/postgres/peer:dalibo)
```
Noter que le trigger insère la ligne en tant que **adminlog** (`current_user`) et non **pierre** ou **postgres** (l’appelant, `session_user`). C’est à savoir si l’on trace l’utilisateur.
### Curseur {#dev42-ch4-12-3}
> Écrire une fonction `checkadmin` qui utilise un curseur pour parcourir la table `pg_stat_user_tables`, et affiche un `RAISE LOG` dans les traces si `last_autovacuum` et `last_vacuum` sont tous deux vides.\
> Afficher le numéro de la ligne dans le curseur (tri par nom de table).\
> Sortir après le troisième avertissement, sinon retourner le nombre de lignes lues.
``` sql
-- Tables d'exemples
CREATE TABLE demo1 AS SELECT 1 AS t ;
CREATE TABLE demo2 AS SELECT 1 AS t ;
CREATE TABLE demo3 AS SELECT 1 AS t ;
CREATE TABLE demo4 AS SELECT 1 AS t ;
```
- Première implémentation possible :
``` sql
CREATE OR REPLACE FUNCTION checkadmin()
RETURNS int AS
$BODY$
DECLARE
c_curseur refcursor;
r_resultat pg_stat_user_tables%ROWTYPE;
v_index integer := 0 ;
v_nb_avertissements integer := 0 ;
BEGIN
OPEN c_curseur FOR
SELECT * FROM pg_stat_user_tables
ORDER BY relname ;
LOOP
FETCH c_curseur INTO r_resultat;
IF NOT FOUND THEN
EXIT;
END IF;
-- debogage
RAISE NOTICE 'Table % (%)',
r_resultat.relname, v_index ;
v_index := v_index + 1;
IF r_resultat.last_autovacuum IS NULL
AND r_resultat.last_vacuum IS NULL
THEN
v_nb_avertissements := v_nb_avertissements + 1 ;
RAISE LOG 'Autovacuum jamais passé sur table % (ligne %)',
r_resultat.relname, v_index ;
IF v_nb_avertissements >= 3 THEN
EXIT ;
END IF ;
END IF ;
END LOOP;
CLOSE c_curseur ;
RETURN v_index;
END $BODY$ LANGUAGE plpgsql ;
```
``` sql
-- Affichage dans la session du contenu des traces
SET client_min_messages TO log;
-- Test
SELECT checkadmin() ;
```
``` default
NOTICE: Table demo1 (0)
LOG: Autovacuum jamais passé sur table demo1 (ligne 1)
NOTICE: Table demo2 (1)
LOG: Autovacuum jamais passé sur table demo2 (ligne 2)
NOTICE: Table demo3 (2)
LOG: Autovacuum jamais passé sur table demo3 (ligne 3)
checkadmin
------------
3
```
- Seconde implémentation possible, un peu plus courte :
``` sql
CREATE OR REPLACE FUNCTION checkadmin()
RETURNS int AS
$BODY$
DECLARE
r_resultat record ;
v_index integer := 0 ;
v_nb_avertissements integer := 0 ;
BEGIN
FOR r_resultat IN (
SELECT relname,last_autovacuum, last_vacuum
FROM pg_stat_user_tables ORDER BY relname )
LOOP
-- debogage
RAISE NOTICE 'Table % (%)',
r_resultat.relname, v_index ;
v_index := v_index + 1;
IF coalesce(r_resultat.last_autovacuum,
r_resultat.last_vacuum) IS NULL
THEN
v_nb_avertissements := v_nb_avertissements + 1 ;
RAISE LOG 'Autovacuum jamais passé sur table % (ligne %)',
r_resultat.relname, v_index ;
EXIT WHEN v_nb_avertissements >= 3 ;
END IF ;
END LOOP ;
RETURN v_index ;
END $BODY$ LANGUAGE plpgsql ;
```
``` sql
SELECT checkadmin() ;
VACUUM ;
SELECT checkadmin() ;
```
``` default
NOTICE: Table demo1 (0)
NOTICE: Table demo2 (1)
NOTICE: Table demo3 (2)
NOTICE: Table demo4 (3)
NOTICE: Table log_stock (4)
NOTICE: Table stock (5)
checkadmin
------------
6
```
## Navigation
Pour revenir au chapitre précédent, voir [le chapitre précédent](/fr/dev42/03-plpgsql-les-bases/#dev42-ch3).
Pour poursuivre la lecture, voir [le chapitre suivant](/fr/dev42/05-extensions-postgresql-utilisateur/#dev42-ch5).
## Préambule {#dev42-ch4-1}
### Au menu {#dev42-ch4-1-1}
- Routines « variadic » et polymorphes
- Fonctions trigger
- Curseurs
- Récupérer les erreurs
- Messages d’erreur dans les logs
- Sécurité
- Optimisation
- Problèmes fréquents
### Objectifs {#dev42-ch4-1-2}
- Connaître la majorité des possibilités de PL/pgSQL
- Les utiliser pour étendre les fonctionnalités de la base
- Écrire du code robuste
- Éviter les pièges de sécurité
- Savoir optimiser une routine
## Routines variadic {#dev42-ch4-2}
### Routines variadic : introduction {#dev42-ch4-2-1}
- Permet de créer des routines avec un nombre d’arguments variables
- … mais du même type
L’utilisation du mot clé `VARIADIC` dans la déclaration des routines permet d’utiliser un nombre variable d’arguments dans la mesure où tous les arguments optionnels sont du même type de données. Ces arguments sont passés à la fonction sous forme de tableau d’arguments du même type.
``` sql
VARIADIC tableau text[]
```
Il n’est pas possible d’utiliser d’autres arguments en entrée à la suite d’un paramètre `VARIADIC`.
### Routines variadic : exemple {#dev42-ch4-2-2}
Récupérer le minimum d’une liste :
``` sql
CREATE FUNCTION pluspetit(VARIADIC numeric[])
RETURNS numeric AS $$
SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;
SELECT pluspetit(10, -1, 5, 4.4);
pluspetit
-----------
-1
(1 row)
```
Quelques explications sur cette fonction :
- SQL est un langage de routines stockées
- une routine SQL ne contient que des ordres SQL exécutés séquentiellements
- le résultat de la fonction est le résultat du dernier ordre
- `generate_subscript()` prend un tableau en premier paramètre et la dimension de ce tableau (un tableau peut avoir plusieurs dimensions), et elle retourne une série d’entiers allant du premier au dernier indice du tableau dans cette dimension
- g(i) est un alias : `generate_subscripts` est une SRF (`set-returning function`, retourne un `SETOF`), g est donc le nom de l’alias de table, et i le nom de l’alias de colonne.
### Routines variadic : exemple PL/pgSQL {#dev42-ch4-2-3}
- En PL/pgSQL, cette fois-ci
- Démonstration de `FOREACH xxx IN ARRAY aaa LOOP`
- Précédemment, obligé de convertir le tableau en relation pour boucler (`unnest`)
En PL/pgSQL, il est possible d’utiliser une boucle `FOREACH` pour parcourir directement le tableau des arguments optionnels.
``` sql
CREATE OR REPLACE FUNCTION pluspetit(VARIADIC liste numeric[])
RETURNS numeric
LANGUAGE plpgsql
AS $function$
DECLARE
courant numeric;
plus_petit numeric;
BEGIN
FOREACH courant IN ARRAY liste LOOP
IF plus_petit IS NULL OR courant < plus_petit THEN
plus_petit := courant;
END IF;
END LOOP;
RETURN plus_petit;
END
$function$;
```
Auparavant, il fallait développer le tableau avec la fonction `unnest()` pour réaliser la même opération.
``` sql
CREATE OR REPLACE FUNCTION pluspetit(VARIADIC liste numeric[])
RETURNS numeric
LANGUAGE plpgsql
AS $function$
DECLARE
courant numeric;
plus_petit numeric;
BEGIN
FOR courant IN SELECT unnest(liste) LOOP
IF plus_petit IS NULL OR courant < plus_petit THEN
plus_petit := courant;
END IF;
END LOOP;
RETURN plus_petit;
END
$function$;
```
## Routines polymorphes {#dev42-ch4-3}
### Routines polymorphes : introduction {#dev42-ch4-3-1}
- Typer les variables oblige à dupliquer les routines communes à plusieurs types
- PostgreSQL propose des types polymorphes
- Le typage se fait à l’exécution
Pour pouvoir utiliser la même fonction en utilisant des types différents, il est nécessaire de la redéfinir avec les différents types autorisés en entrée. Par exemple, pour autoriser l’utilisation de données de type `integer` ou `float` en entrée et retournés par une même fonction, il faut la dupliquer.
``` sql
CREATE OR REPLACE FUNCTION
addition(var1 integer, var2 integer)
RETURNS integer
AS $$
DECLARE
somme integer;
BEGIN
somme := var1 + var2;
RETURN somme;
END;
$$ LANGUAGE plpgsql;
```
``` sql
CREATE OR REPLACE FUNCTION
addition(var1 float, var2 float)
RETURNS float
AS $$
DECLARE
somme float;
BEGIN
somme := var1 + var2;
RETURN somme;
END;
$$ LANGUAGE plpgsql;
```
L’utilisation de types polymorphes permet d’éviter ce genre de duplications fastidieuses.
### Routines polymorphes : anyelement {#dev42-ch4-3-2}
- Remplace tout type de données simple ou composite
- pour les paramètres en entrée comme pour les paramètres en sortie
- Tous les paramètres et type de retour de type `anyelement` se voient attribués le même type
- Donc un seul type pour tous les `anyelement` autorisés
- Paramètre spécial `$0` : du type attribué aux éléments `anyelement`
### Routines polymorphes : anyarray {#dev42-ch4-3-3}
- `anyarray` remplace tout tableau de type de données simple ou composite
- pour les paramètres en entrée comme pour les paramètres en sortie
- Le typage se fait à l’exécution
- Tous les paramètres de type `anyarray` se voient attribués le même type
### Routines polymorphes : exemple {#dev42-ch4-3-4}
L’addition est un exemple fréquent :
``` sql
CREATE OR REPLACE FUNCTION
addition(var1 anyelement, var2 anyelement)
RETURNS anyelement
AS $$
DECLARE
somme ALIAS FOR $0;
BEGIN
somme := var1 + var2;
RETURN somme;
END;
$$ LANGUAGE plpgsql;
```
### Routines polymorphes : tests {#dev42-ch4-3-5}
``` sql
# SELECT addition(1, 3);
addition
----------
4
(1 row)
# SELECT addition(1.3, 3.5);
addition
----------
4.8
(1 row)
```
L’opérateur `+` étant défini pour les entiers comme pour les `numeric`, la fonction ne pose aucun problème pour ces deux types de données, et retourne une donnée du même type que les données d’entrée.
### Routines polymorphes : problème {#dev42-ch4-3-6}
- Attention lors de l’utilisation de type polymorphe…
``` sql
# SELECT addition('un'::text, 'mot'::text);
ERREUR: L'opérateur n'existe pas : text + text
LIGNE 1 : SELECT $1 + $2
^
ASTUCE : Aucun opérateur correspond au nom donné et aux types d'arguments.
Vous devez ajouter des conversions explicites de type.
REQUÊTE : SELECT $1 + $2
CONTEXTE : PL/pgSQL function "addition" line 4 at assignment
```
Le typage n’étant connu qu’à l’exécution, c’est aussi à ce moment que se déclenchent les erreurs.
De même, l’affectation du type unique pour tous les éléments se fait sur la base du premier élément, ainsi :
```text
# SELECT addition(1, 3.5);
ERROR: function addition(integer, numeric) does not exist
LIGNE 1 : SELECT addition(1, 3.5);
^
ASTUCE : No function matches the given name and argument types.
You might need to add explicit type casts.
```
génère une erreur car du premier argument est déduit le type `integer`, ce qui n’est évidement pas le cas du deuxième. Il peut donc être nécessaire d’utiliser une conversion explicite pour résoudre ce genre de problématique.
``` text
# SELECT addition(1::numeric, 3.5);
addition
----------
4.5
```
## Fonctions trigger {#dev42-ch4-4}
### Fonctions trigger : introduction {#dev42-ch4-4-1}
- Fonction stockée
- Action déclenchée par `INSERT` (incluant `COPY`), `UPDATE`, `DELETE`, `TRUNCATE`
- Mode **par ligne** ou **par instruction**
- Exécution d’une fonction
Un trigger est une spécification précisant que la base de données doit exécuter une fonction particulière quand un certain type d’opération est traité. Les fonctions trigger peuvent être définies pour s’exécuter avant ou après une commande `INSERT`, `UPDATE`, `DELETE` ou `TRUNCATE`.
La fonction trigger doit être définie avant que le trigger lui-même puisse être créé. La fonction trigger doit être déclarée comme une fonction ne prenant aucun argument et retournant un type `trigger`.
Une fois qu’une fonction trigger est créée, le trigger est créé avec `CREATE TRIGGER`. La même fonction trigger est utilisable par plusieurs triggers.
Un trigger `TRUNCATE` ne peut utiliser que le mode par instruction, contrairement aux autres triggers pour lesquels vous avez le choix entre « par ligne » et « par instruction ».
Enfin, l’instruction `COPY` est traitée comme s’il s’agissait d’une commande `INSERT`.
À noter que les problématiques de visibilité et de volatilité depuis un trigger sont assez complexes dès lors que l’on lit ou modifie les données. Voir [la documentation](https://docs.postgresql.fr/current/trigger-datachanges.html) pour plus de détails à ce sujet.
### Fonctions trigger : variables (1/5) {#dev42-ch4-4-2}
- `OLD` :
- type de données `RECORD` correspondant à la ligne avant modification
- valable pour un `DELETE` et un `UPDATE`
- `NEW` :
- type de données `RECORD` correspondant à la ligne après modification
- valable pour un `INSERT` et un `UPDATE`
### Fonctions trigger : variables (2/5) {#dev42-ch4-4-3}
- Ces deux variables sont valables uniquement pour les triggers en mode ligne
- pour les triggers en mode instruction, utiliser les tables de transition
- Accès aux champs par la notation pointée
- `NEW.champ1` pour accéder à la nouvelle valeur de `champ1`
### Fonctions trigger : variables (3/5) {#dev42-ch4-4-4}
- `TG_NAME`
- nom du trigger qui a déclenché l’appel de la fonction
- `TG_WHEN`
- chaîne valant `BEFORE`, `AFTER` ou `INSTEAD OF` suivant le type du trigger
- `TG_LEVEL`
- chaîne valant `ROW` ou `STATEMENT` suivant le mode du trigger
- `TG_OP`
- chaîne valant `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE` suivant l’opération qui a déclenché le trigger
### Fonctions trigger : variables (4/5) {#dev42-ch4-4-5}
- `TG_RELID`
- `OID` de la table qui a déclenché le trigger
- `TG_TABLE_NAME`
- nom de la table qui a déclenché le trigger
- `TG_TABLE_SCHEMA`
- nom du schéma contenant la table qui a déclenché le trigger
Vous pourriez aussi rencontrer dans du code la variable `TG_RELNAME`. C’est aussi le nom de la table qui a déclenché le trigger. Attention, cette variable est obsolète, il est préférable d’utiliser maintenant `TG_TABLE_NAME`.
### Fonctions trigger : variables (5/5) {#dev42-ch4-4-6}
- `TG_NARGS`
- nombre d’arguments donnés à la fonction trigger
- `TG_ARGV`
- les arguments donnés à la fonction trigger (le tableau commence à 0)
La fonction trigger est déclarée sans arguments mais il est possible de lui en passer dans la déclaration du trigger. Dans ce cas, il faut utiliser les deux variables ci-dessus pour y accéder. Attention, tous les arguments sont convertis en texte. Il faut donc se cantonner à des informations simples, sous peine de compliquer le code.
``` sql
CREATE OR REPLACE FUNCTION verifier_somme()
RETURNS trigger AS $$
DECLARE
fact_limit integer;
arg_color varchar;
BEGIN
fact_limit := TG_ARGV[0];
IF NEW.somme > fact_limit THEN
RAISE NOTICE 'La facture % necessite une verification. '
'La somme % depasse la limite autorisee de %.',
NEW.idfact, NEW.somme, fact_limit;
END IF;
NEW.datecreate := current_timestamp;
return NEW;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER trig_verifier_debit
BEFORE INSERT OR UPDATE ON test
FOR EACH ROW
EXECUTE PROCEDURE verifier_somme(400);
CREATE TRIGGER trig_verifier_credit
BEFORE INSERT OR UPDATE ON test
FOR EACH ROW
EXECUTE PROCEDURE verifier_somme(800);
```
### Fonctions trigger : retour {#dev42-ch4-4-7}
- Une fonction trigger a un type de retour spécial : `trigger`
- Trigger `BEFORE FOR EACH ROW` :
- si retour `NULL` : annulation de l’opération, sans erreur, la transaction continue
- modification possible de `NEW`
- `BEFORE DELETE` : `NEW` est `NULL`, retourner `OLD`
- `BEFORE INSERT` : `OLD` est `NULL`, retourner `NEW`
- Trigger `AFTER` :
- valeur de retour ignorée (même `NULL`)
- Trigger `FOR EACH STATEMENT` :
- valeur de retour ignorée, renvoyer `NULL`
- `RAISE EXCEPTION` toujours possible, interrompt la transaction
Une fonction trigger retourne le type spécial `trigger`. Pour cette raison, ces fonctions ne peuvent être utilisées que dans le contexte d’un ou plusieurs triggers.
Pour pouvoir être utilisée comme valeur de retour dans la fonction (avec `RETURN`), une variable doit être de structure identique à celle de la table sur laquelle le trigger a été déclenché. Les variables spéciales `OLD` (ancienne valeur avant application de l’action à l’origine du déclenchement) et `NEW` (nouvelle valeur après application de l’action) sont également disponibles, utilisables et même modifiables.
**Trigger BEFORE … FOR EACH ROW** :
La valeur de retour d’un trigger de type ligne déclenché avant l’opération peut changer complètement l’effet de la commande.
Il est possible de changer les valeurs de la nouvelle ligne créée par une action `INSERT` ou `UPDATE` en modifiant `NEW` directement, puis en faisant `RETURN NEW`.
Il est également possible d’annuler complètement l’action sur la ligne, sans erreur en retournant `NULL`. Cette annulation ne vaut que pour la ligne, et la transaction continue normalement. Les triggers ne sont plus déclenchés pour cette ligne.
Attention ! Dans le cas d’une fonction trigger `BEFORE DELETE`, il faut prendre en compte que `NEW` contient `NULL`, et en conséquence `RETURN NEW` provoque l’annulation du `DELETE` ! Dans ce cas, la convention est de faire un `RETURN OLD` pour poursuivre, ou `RETURN NULL` pour une annulation explicite.
**Trigger AFTER … FOR EACH ROW** et **FOR EACH STATEMENT** :
En revanche, la valeur de retour utilisée n’a pas d’effet dans les cas des triggers `ROW` et `AFTER`, et des triggers `FOR EACH STATEMENT` (par exemple les triggers sur `TRUNCATE` ou les triggers utilisant les tables de transition). La documentation conseille alors de toujours retourner `NULL`.
Dans ces cas, il reste possible d’annuler l’action en remontant une erreur à l’exécution de la fonction (`RAISE EXCEPTION`). Mais cela va annuler toute la transaction.
### Fonctions trigger : exemple - 1 {#dev42-ch4-4-8}
- Horodater une opération sur une ligne
``` sql
CREATE TABLE ma_table (
id serial,
-- un certain nombre de champs informatifs
date_ajout timestamp,
date_modif timestamp);
```
### Fonctions trigger : exemple - 2 {#dev42-ch4-4-9}
``` sql
CREATE OR REPLACE FUNCTION horodatage() RETURNS trigger
AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
NEW.date_ajout := now();
ELSEIF TG_OP = 'UPDATE' THEN
NEW.date_modif := now();
END IF;
RETURN NEW;
END; $$ LANGUAGE plpgsql;
CREATE TRIGGER trig_horodatage
BEFORE INSERT OR UPDATE ON ma_table
FOR EACH ROW
EXECUTE PROCEDURE horodatage();
```
### Options de CREATE TRIGGER {#dev42-ch4-4-10}
`CREATE TRIGGER` permet quelques variantes :
- `CREATE TRIGGER name WHEN ( condition )`
- `CREATE TRIGGER name BEFORE UPDATE OF colx ON my_table`
- `CREATE CONSTRAINT TRIGGER` : exécuté qu’au moment de la validation de la transaction
- `CREATE TRIGGER view_insert INSTEAD OF INSERT ON my_view`
- On peut ne déclencher un trigger que si une condition est vérifiée. Cela simplifie souvent le code du trigger, et gagne en performances : plus besoin pour le moteur d’aller exécuter la fonction.
- On peut ne déclencher un trigger que si une colonne spécifique a été modifiée. Il ne s’agit donc que de triggers sur `UPDATE`. Encore un moyen de simplifier le code et de gagner en performances en évitant les déclenchements inutiles.
- On peut créer un trigger en le déclarant comme étant un trigger de contrainte. Il peut alors être *deferrable*, *deferred*, comme tout autre contrainte, c’est-à-dire n’être exécuté qu’au moment de la validation de la transaction, ce qui permet de ne vérifier les contraintes implémentées par le trigger qu’au moment de la validation finale.
- On peut créer un trigger sur une vue. C’est un trigger `INSTEAD OF`, qui permet de programmer de façon efficace les `INSERT`/`UPDATE`/`DELETE`/`TRUNCATE` sur les vues. Auparavant, il fallait passer par le système de règles (`RULES`), complexe et sujet à erreurs.
### Tables de transition {#dev42-ch4-4-11}
- Pour les triggers de type `AFTER` et de niveau statement
- Possibilité de stocker les lignes avant et/ou après modification
- `REFERENCING OLD TABLE`
- `REFERENCING NEW TABLE`
- Par exemple :
``` sql
CREATE TRIGGER tr1
AFTER DELETE ON t1
REFERENCING OLD TABLE AS oldtable
FOR EACH STATEMENT
EXECUTE PROCEDURE log_delete();
```
Dans le cas d’un trigger en mode instruction, il n’est pas possible d’utiliser les variables `OLD` et `NEW` car elles ciblent une seule ligne. Pour cela, le standard SQL parle de tables de transition.
**Exemple** :
Nous allons créer une table `t1` qui portera le trigger et une table `archives` qui aura pour but de récupérer les enregistrements supprimés de la table `t1`.
``` sql
CREATE TABLE t1 (c1 integer, c2 text);
CREATE TABLE archives (id integer GENERATED ALWAYS AS IDENTITY,
dlog timestamp DEFAULT now(),
t1_c1 integer, t1_c2 text);
```
Maintenant, il faut créer le code de la procédure stockée :
``` sql
CREATE OR REPLACE FUNCTION log_delete() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO archives (t1_c1, t1_c2) SELECT c1, c2 FROM oldtable;
RETURN null;
END
$$;
```
Et ajouter le trigger sur la table `t1` :
``` sql
CREATE TRIGGER tr1
AFTER DELETE ON t1
REFERENCING OLD TABLE AS oldtable
FOR EACH STATEMENT
EXECUTE PROCEDURE log_delete();
```
Maintenant, insérons un million de ligne dans `t1` puis supprimons-les :
``` sql
INSERT INTO t1 SELECT i, 'Ligne '||i FROM generate_series(1, 1000000) i;
DELETE FROM t1;
Time: 2141.871 ms (00:02.142)
```
La suppression avec le trigger prend 2 secondes. Il est possible de connaître le temps à supprimer les lignes et le temps à exécuter le trigger en utilisant l’ordre `EXPLAIN ANALYZE` :
``` sql
TRUNCATE archives;
INSERT INTO t1 SELECT i, 'Ligne '||i FROM generate_series(1, 1000000) i;
EXPLAIN (ANALYZE) DELETE FROM t1;
QUERY PLAN
--------------------------------------------------------------------------
Delete on t1 (cost=0.00..14241.98 rows=796798 width=6)
(actual time=781.612..781.612 rows=0 loops=1)
-> Seq Scan on t1 (cost=0.00..14241.98 rows=796798 width=6)
(actual time=0.113..104.328 rows=1000000 loops=1)
Planning time: 0.079 ms
Trigger tr1: time=1501.688 calls=1
Execution time: 2287.907 ms
(5 rows)
```
Donc la suppression des lignes met 0,7 seconde alors que l’exécution du trigger met 1,5 seconde.
Pour comparer, voici l’ancienne façon de faire (configuration d’un trigger en mode ligne) :
``` sql
CREATE OR REPLACE FUNCTION log_delete() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO archives (t1_c1, t1_c2) VALUES (old.c1, old.c2);
RETURN null;
END
$$;
DROP TRIGGER tr1 ON t1;
CREATE TRIGGER tr1
AFTER DELETE ON t1
FOR EACH ROW
EXECUTE PROCEDURE log_delete();
TRUNCATE archives;
TRUNCATE t1;
INSERT INTO t1 SELECT i, 'Ligne '||i FROM generate_series(1, 1000000) i;
DELETE FROM t1;
Time: 8445.697 ms (00:08.446)
TRUNCATE archives;
INSERT INTO t1 SELECT i, 'Ligne '||i FROM generate_series(1, 1000000) i;
EXPLAIN (ANALYZE) DELETE FROM t1;
QUERY PLAN
--------------------------------------------------------------------------
Delete on t1 (cost=0.00..14241.98 rows=796798 width=6)
(actual time=1049.420..1049.420 rows=0 loops=1)
-> Seq Scan on t1 (cost=0.00..14241.98 rows=796798 width=6)
(actual time=0.061..121.701 rows=1000000 loops=1)
Planning time: 0.096 ms
Trigger tr1: time=7709.725 calls=1000000
Execution time: 8825.958 ms
(5 rows)
```
Donc avec un trigger en mode ligne, la suppression du million de lignes met presque 9 secondes à s’exécuter, dont 7,7 pour l’exécution du trigger. Sur le trigger en mode instruction, il faut compter 2,2 secondes, dont 1,5 sur le trigger. Les tables de transition nous permettent de gagner en performance.
Le gros intérêt des tables de transition est le gain en performance que cela apporte.
## Curseurs {#dev42-ch4-5}
### Curseurs : introduction {#dev42-ch4-5-1}
- Exécuter une requête en une fois peut ramener beaucoup de résultats
- Tout ce résultat est en mémoire
- risque de dépassement mémoire
- La solution : les curseurs
- Un curseur permet d’exécuter la requête sur le serveur mais de ne récupérer les résultats que petit bout par petit bout
- Dans une transaction ou une routine
À noter que la notion de curseur existe aussi en SQL pur, sans passer par une routine PL/pgSQL. On les crée en utilisant la commande `DECLARE`, et les règles de manipulation sont légèrement différentes (on peut par exemple créer un curseur `WITH HOLD`, qui persistera après la fin de la transaction). Voir la documentation pour plus d’informations à ce sujet : https://docs.postgresql.fr/current/sql-declare.html
### Curseurs : déclaration d’un curseur {#dev42-ch4-5-2}
- Avec le type refcursor
- Avec la pseudo-instruction `CURSOR FOR`
- Avec une requête paramétrée
- Exemples :
``` sql
curseur1 refcursor;
curseur2 CURSOR FOR SELECT * FROM ma_table;
curseur3 CURSOR (param integer) IS
SELECT * FROM ma_table WHERE un_champ=param;
```
La première forme permet la création d’un curseur non lié à une requête.
### Curseurs : ouverture d’un curseur {#dev42-ch4-5-3}
- Lier une requête à un curseur :
``` sql
OPEN curseur FOR requete
```
- Plan de la requête mis en cache
- Lier une requête dynamique à un curseur
``` sql
OPEN curseur FOR EXECUTE chaine_requete
```
Voici un exemple de lien entre une requête et un curseur :
``` sql
OPEN curseur FOR SELECT * FROM ma_table;
```
Et voici un exemple d’utilisation d’une requête dynamique :
``` sql
OPEN curseur FOR EXECUTE 'SELECT * FROM ' || quote_ident(TG_TABLE_NAME);
```
### Curseurs : ouverture d’un curseur lié {#dev42-ch4-5-4}
- Instruction SQL : `OPEN curseur(arguments)`
- Permet d’ouvrir un curseur déjà lié à une requête
- Impossible d’ouvrir deux fois le même curseur
- Plan de la requête mise en cache
- Exemple
``` sql
curseur CURSOR FOR SELECT * FROM ma_table;
...
OPEN curseur;
```
### Curseurs : récupération des données {#dev42-ch4-5-5}
- Instruction SQL :
``` sql
FETCH [ direction { FROM | IN } ] curseur INTO cible
```
- Récupère la prochaine ligne
- FOUND indique si cette nouvelle ligne a été récupérée
- Cible est
- une variable RECORD
- une variable ROW
- un ensemble de variables séparées par des virgules
### Curseurs : récupération des données {#dev42-ch4-5-6}
- `direction` du `FETCH` :
- `NEXT`, `PRIOR`
- `FIRST`, `LAST`
- `ABSOLUTE nombre`, `RELATIVE nombre`
- nombre
- `ALL`
- `FORWARD`, `FORWARD nombre`, `FORWARD ALL`
- `BACKWARD`, `BACKWARD nombre`, `BACKWARD ALL`
### Curseurs : modification des données {#dev42-ch4-5-7}
- Mise à jour d’une ligne d’un curseur :
``` sql
UPDATE une_table SET ...
WHERE CURRENT OF curseur;
```
- Suppression d’une ligne d’un curseur :
``` sql
DELETE FROM une_table
WHERE CURRENT OF curseur;
```
Attention, ces différentes syntaxes ne modifient pas les données dans le curseur en mémoire, mais font réellement la modification dans la table. L’emplacement actuel du curseur est utilisé ici pour identifier la ligne correspondante à mettre à jour.
### Curseurs : fermeture d’un curseur {#dev42-ch4-5-8}
- Instruction SQL : `CLOSE curseur`
- Ferme le curseur
- Permet de récupérer de la mémoire
- Permet aussi de réouvrir le curseur
### Curseurs : renvoi d’un curseur {#dev42-ch4-5-9}
- Fonction renvoyant une valeur de type `refcursor`
- Permet donc de renvoyer plusieurs valeurs
Voici un exemple d’utilisation d’une référence de curseur retournée par une fonction :
``` sql
CREATE FUNCTION consult_all_stock(refcursor) RETURNS refcursor AS $$
BEGIN
OPEN $1 FOR SELECT * FROM stock;
RETURN $1;
END;
$$ LANGUAGE plpgsql;
-- doit être dans une transaction pour utiliser les curseurs.
BEGIN;
SELECT * FROM consult_all_stock('cursor_a');
FETCH ALL FROM cursor_a;
COMMIT;
```
## Gestion des erreurs {#dev42-ch4-6}
### Gestion des erreurs : introduction {#dev42-ch4-6-1}
- Sans exceptions :
- toute erreur provoque un arrêt de la fonction
- toute modification suite à une instruction SQL (`INSERT`, `UPDATE`, `DELETE`) est annulée
- d’où l’ajout d’une gestion personnalisée des erreurs avec le concept des exceptions
### Gestion des erreurs : une exception {#dev42-ch4-6-2}
- La fonction comporte un bloc supplémentaire, `EXCEPTION` :
``` sql
DECLARE
-- déclaration des variables locales
BEGIN
-- instructions de la fonction
EXCEPTION
WHEN condition THEN
-- instructions traitant cette erreur
WHEN condition THEN
-- autres instructions traitant cette autre erreur
-- etc.
END
```
### Gestion des erreurs : flot dans une fonction {#dev42-ch4-6-3}
- L’exécution de la fonction commence après le `BEGIN`
- Si aucune erreur ne survient, le bloc `EXCEPTION` est ignoré
- Si une erreur se produit
- tout ce qui a été modifié dans la base dans le bloc est annulé
- les variables gardent par contre leur état
- l’exécution passe directement dans le bloc de gestion de l’exception
### Gestion des erreurs : flot dans une exception {#dev42-ch4-6-4}
- Recherche d’une condition satisfaisante
- Si cette condition est trouvée
- exécution des instructions correspondantes
- Si aucune condition n’est compatible
- sortie du bloc `BEGIN`/`END` comme si le bloc d’exception n’existait pas
- passage de l’exception au bloc `BEGIN`/`END` contenant (après annulation de ce que ce bloc a modifié en base)
- Dans un bloc d’exception, les instructions `INSERT`, `UPDATE`, `DELETE` de la fonction ont été annulées
- Dans un bloc d’exception, les variables locales de la fonction ont gardé leur ancienne valeur
### Gestion des erreurs : codes d’erreurs {#dev42-ch4-6-5}
- `SQLSTATE` : code d’erreur
- `SQLERRM` : message d’erreur
- Par exemple :
- `Data Exception` : division par zéro, overflow, argument invalide pour certaines fonctions, etc.
- `Integrity Constraint Violation` : unicité, CHECK, clé étrangère, etc.
- `Syntax Error`
- `PL/pgSQL Error` : `RAISE EXCEPTION`, pas de données, trop de lignes, etc.
- Les erreurs sont contenues dans des classes d’erreurs plus génériques, qui peuvent aussi être utilisées
Toutes les erreurs sont référencées [dans la documentation](https://docs.postgresql.fr/current/errcodes-appendix.html)
Attention, des codes d’erreurs nouveaux apparaissent à chaque version.
La classe data_exception contient de nombreuses erreurs, comme datetime_field_overflow, invalid_escape_character, invalid_binary_representation… On peut donc, dans la déclaration de l’exception, intercepter toutes les erreurs de type data_exception d’un coup, ou une par une.
L’instruction `GET STACKED DIAGNOSTICS` permet d’avoir une vision plus précise de l’erreur récupéré par le bloc de traitement des exceptions. La liste de toutes les informations que l’on peut collecter est disponible [dans la documentation](https://docs.postgresql.fr/current/plpgsql-control-structures.html#PLPGSQL-EXCEPTION-DIAGNOSTICS-VALUES) .
La démonstration ci-dessous montre comment elle peut être utilisée.
``` sql
# CREATE TABLE t5(c1 integer PRIMARY KEY);
CREATE TABLE
# INSERT INTO t5 VALUES (1);
INSERT 0 1
# CREATE OR REPLACE FUNCTION test(INT4) RETURNS void AS $$
DECLARE
v_state TEXT;
v_msg TEXT;
v_detail TEXT;
v_hint TEXT;
v_context TEXT;
BEGIN
BEGIN
INSERT INTO t5 (c1) VALUES ($1);
EXCEPTION WHEN others THEN
GET STACKED DIAGNOSTICS
v_state = RETURNED_SQLSTATE,
v_msg = MESSAGE_TEXT,
v_detail = PG_EXCEPTION_DETAIL,
v_hint = PG_EXCEPTION_HINT,
v_context = PG_EXCEPTION_CONTEXT;
raise notice E'Et une exception :
state : %
message: %
detail : %
hint : %
context: %', v_state, v_msg, v_detail, v_hint, v_context;
END;
RETURN;
END;
$$ LANGUAGE plpgsql;
# SELECT test(2);
test
------
(1 row)
# SELECT test(2);
NOTICE: Et une exception :
state : 23505
message: duplicate key value violates unique constraint "t5_pkey"
detail : Key (c1)=(2) already exists.
hint :
context: SQL statement "INSERT INTO t5 (c1) VALUES ($1)"
PL/pgSQL function test(integer) line 10 at SQL statement
test
------
(1 row)
```
### Messages d’erreurs : RAISE - 1 {#dev42-ch4-6-6}
- Envoyer une trace dans les journaux applicatifs et/ou vers le client
- `RAISE niveau message`
- Niveau correspond au niveau d’importance du message
- `DEBUG`, `LOG`, `INFO`, `NOTICE`, `WARNING`, `EXCEPTION`
- Message est la trace à enregistrer
- Message dynamique… tout signe `%` est remplacé par la valeur indiquée après le message
- Champs `DETAIL` et `HINT` disponibles
Il convient de noter qu’un message envoyé de cette manière ne fera pas partie de l’éventuel résultat d’une fonction, et ne sera donc pas exploitable en SQL. Pour cela, il faut utiliser l’instruction `RETURN` avec un type de retour approprié.
Le traitement des messages de ce type et leur destination d’envoi sont contrôlés par le serveur à l’aide des paramètres `log_min_messages` et `client_min_messages`.
### Messages d’erreurs : RAISE - 2 {#dev42-ch4-6-7}
Exemples :
``` sql
RAISE WARNING 'valeur % interdite', valeur;
```
``` sql
RAISE WARNING 'valeur % ambigue',
valeur
USING HINT = 'Controlez la valeur saisie en amont';
```
Les autres niveaux pour `RAISE` ne sont que des messages, sans déclenchement d’exception.
### Messages d’erreurs : configuration des logs {#dev42-ch4-6-8}
- Deux paramètres importants pour les traces
- `log_min_messages`
- niveau minimum pour que la trace soit enregistrée dans les journaux
- `client_min_messages`
- niveau minimum pour que la trace soit envoyée au client
- Dans le cas d’un `RAISE NOTICE message`, il faut avoir soit `log_min_messages`, soit `client_min_messages`, soit les deux à la valeur `NOTICE` au minimum.
### Messages d’erreurs : RAISE EXCEPTION - 1 {#dev42-ch4-6-9}
- Annule le bloc en cours d’exécution
- `RAISE EXCEPTION message`
- Sauf en cas de présence d’un bloc `EXCEPTION` gérant la condition `RAISE_EXCEPTION`
- message est la trace à enregistrer, et est dynamique… tout signe % est remplacé par la valeur indiquée après le message
### Messages d’erreurs : RAISE EXCEPTION - 2 {#dev42-ch4-6-10}
Exemple :
``` sql
RAISE EXCEPTION 'erreur interne';
-- La chose à ne pas faire !
```
Le rôle d’une exception est d’intercepter une erreur pour exécuter un traitement permettant soit de corriger l’erreur, soit de remonter une erreur pertinente. Intercepter un problème pour retourner « erreur interne » n’est pas une bonne idée.
### Flux des erreurs dans du code PL {#dev42-ch4-6-11}
- Les exceptions non traitées «remontent»
- de bloc `BEGIN`/`END` imbriqués vers les blocs parents (fonctions appelantes comprises)
- jusqu’à ce que personne ne puisse les traiter
- voir note pour démonstration
Démonstration en plusieurs étapes :
``` sql
# CREATE TABLE ma_table (
id integer unique
);
CREATE TABLE
# CREATE OR REPLACE FUNCTION public.demo_exception()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
BEGIN
INSERT INTO ma_table VALUES (1);
-- Va déclencher une erreur de violation de contrainte d'unicité
INSERT INTO ma_table VALUES (1);
END
$function$;
CREATE FUNCTION
# SELECT demo_exception();
ERROR: duplicate key value violates unique constraint "ma_table_id_key"
DETAIL: Key (id)=(1) already exists.
CONTEXT: SQL statement "INSERT INTO ma_table VALUES (1)"
PL/pgSQL function demo_exception() line 6 at SQL statement
```
Une exception a été remontée avec un message explicite.
``` sql
# SELECT * FROM ma_table ;
a
---
(0 row)
```
La fonction a bien été annulée.
### Flux des erreurs dans du code PL - 2 {#dev42-ch4-6-12}
- Les erreurs remontent
- Cette fois-ci, on rajoute un bloc PL pour intercepter l’erreur
``` sql
# CREATE OR REPLACE FUNCTION public.demo_exception()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
BEGIN
INSERT INTO ma_table VALUES (1);
-- Va déclencher une erreur de violation de contrainte d'unicité
INSERT INTO ma_table VALUES (1);
EXCEPTION WHEN unique_violation THEN
RAISE NOTICE 'violation d''unicite, mais celle-ci n''est pas grave';
RAISE NOTICE 'erreur: %',sqlerrm;
END
$function$;
CREATE FUNCTION
# SELECT demo_exception();
NOTICE: violation d'unicite, mais celle-ci n'est pas grave
NOTICE: erreur: duplicate key value violates unique constraint "ma_table_id_key"
demo_exception
----------------
(1 row)
```
L’erreur est bien devenue un message de niveau `NOTICE`.
``` sql
# SELECT * FROM ma_table ;
a
---
(0 row)
```
La table n’en reste pas moins vide pour autant puisque le bloc a été annulé.
### Flux des erreurs dans du code PL - 3 {#dev42-ch4-6-13}
- Cette fois-ci, on rajoute un bloc PL indépendant pour gérer le second `INSERT`
Voici une nouvelle version de la fonction :
``` sql
# CREATE OR REPLACE FUNCTION public.demo_exception()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
BEGIN
INSERT INTO ma_table VALUES (1);
-- L'operation suivante pourrait échouer.
-- Il ne faut pas perdre le travail effectué jusqu'à ici
BEGIN
-- Va déclencher une erreur de violation de contrainte d'unicité
INSERT INTO ma_table VALUES (1);
EXCEPTION WHEN unique_violation THEN
-- Cette exception est bien celle du bloc imbriqué
RAISE NOTICE 'violation d''unicite, mais celle-ci n''est pas grave';
RAISE NOTICE 'erreur: %',sqlerrm;
END; -- Fin du bloc imbriqué
END
$function$;
CREATE FUNCTION
# SELECT demo_exception();
NOTICE: violation d'unicite, mais celle-ci n'est pas grave
NOTICE: erreur: duplicate key value violates unique constraint "ma_table_id_key"
demo_exception
----------------
(1 row)
```
En apparence, le résultat est identique.
``` sql
# SELECT * FROM ma_table ;
a
---
1
(1 row)
```
Mais cette fois-ci, le bloc `BEGIN` parent n’a pas eu d’exception, il s’est donc bien terminé.
### Flux des erreurs dans du code PL - 4 {#dev42-ch4-6-14}
- Illustrons maintenant la remontée d’erreurs
- Nous avons deux blocs imbriqués
- Une erreur non prévue va se produire dans le bloc intérieur
On commence par ajouter une contrainte sur la colonne pour empêcher les valeurs supérieures ou égales à 10 :
``` sql
# ALTER TABLE ma_table ADD CHECK (id < 10 ) ;
ALTER TABLE
```
Puis, on recrée la fonction de façon à ce qu’elle déclenche cette erreur dans le bloc le plus bas, et la gère uniquement dans le bloc parent :
``` sql
CREATE OR REPLACE FUNCTION public.demo_exception()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
BEGIN
INSERT INTO ma_table VALUES (1);
-- L'operation suivante pourrait échouer.
-- Il ne faut pas perdre le travail effectué jusqu'à ici
BEGIN
-- Va déclencher une erreur de violation de check (col < 10)
INSERT INTO ma_table VALUES (100);
EXCEPTION WHEN unique_violation THEN
-- Cette exception est bien celle du bloc imbriqué
RAISE NOTICE 'violation d''unicite, mais celle-ci n''est pas grave';
RAISE NOTICE 'erreur: %',sqlerrm;
END; -- Fin du bloc imbriqué
EXCEPTION WHEN check_violation THEN
RAISE NOTICE 'violation de contrainte check';
RAISE EXCEPTION 'mais on va remonter une exception à l''appelant, '
'juste pour le montrer';
END
$function$;
```
Exécutons la fonction :
``` sql
# SELECT demo_exception();
ERROR: duplicate key value violates unique constraint "ma_table_id_key"
DETAIL: Key (id)=(1) already exists.
CONTEXT: SQL statement "INSERT INTO ma_table VALUES (1)"
PL/pgSQL function demo_exception() line 4 at SQL statement
```
C’est normal, nous avons toujours l’enregistrement à 1 du test précédent. L’exception se déclenche donc dans le bloc parent, sans espoir d’interception: nous n’avons pas d’exception pour lui.
Nettoyons donc la table, pour reprendre le test :
``` sql
# TRUNCATE ma_table ;
TRUNCATE TABLE
# SELECT demo_exception();
NOTICE: violation de contrainte check
ERREUR: mais on va remonter une exception à l'appelant, juste pour le montrer
CONTEXT: PL/pgSQL function demo_exception() line 17 at RAISE
```
Le gestionnaire d’exception qui intercepte l’erreur est bien ici celui de l’appelant. Par ailleurs, comme nous retournons nous-même une exception, la requête ne retourne pas de résultat, mais une erreur : il n’y a plus personne pour récupérer l’exception, c’est donc PostgreSQL lui-même qui s’en charge.
### Abus des exceptions {#dev42-ch4-6-15}
- Ne pas abuser des exceptions
- les sous-transactions ne sont pas gratuites
- Si saturation, paramétre SLRU dédié (v17)
La levée d’une exception revient à poser un `SAVEPOINT` pendant la transaction (une forme de sous-transaction).
En cas de trop nombreuses levées d’exception, un des caches dédiés en mémoire partagée (SLRU) de PostgreSQL peut saturer et poser des soucis de performance (le *wait event* `SubtransSLRU` apparaît dans `pg_stat_activity.wait_event`). Depuis PostgreSQL 17 le DBA peut monter ce cache, avec le paramètre `subtransaction_buffers`.
## Sécurité {#dev42-ch4-7}
### Sécurité : droits {#dev42-ch4-7-1}
- L’exécution de la routine dépend du droit `EXECUTE`
- Par défaut, ce droit est donné à la création de la routine
- au propriétaire de la routine
- au groupe spécial PUBLIC
### Sécurité : ajout {#dev42-ch4-7-2}
- Ce droit peut être donné avec l’instruction SQL `GRANT` :
``` sql
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name
[ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ... ]
| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
```
### Sécurité : suppression {#dev42-ch4-7-3}
- Un droit peut être révoqué avec l’instruction SQL `REVOKE`
``` sql
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON { { FUNCTION | PROCEDURE | ROUTINE } function_name
[ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ... ]
| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
```
### Sécurité : SECURITY INVOKER/DEFINER {#dev42-ch4-7-4}
- `SECURITY INVOKER`
- la routine s’exécute avec les droits de l’utilisateur qui l’exécute
- `SECURITY DEFINER`
- la routine s’exécute avec les droits du propriétaire
- équivalent du `sudo` Unix
- Impérativement sécuriser les variables d’environnement
- et surtout `search_path`
Rappelons qu’une fonction s’exécute par défaut avec les droits d’accès de l’utilisateur qui l’exécute ; et que pour donner accès à ses données, un utilisateur peut mettre à disposition des autres des fonctions en mode `SECURITY DEFINER` qui profiteront de ses droits. Il est impératif de réinitialiser l’environnement et en particulier le `search_path` pour éviter des attaques de la part d’utilisateurs malveillants, et de restreindre au maximum l’accès à ces fonctions.
### Sécurité : LEAKPROOF {#dev42-ch4-7-5}
- `LEAKPROOF`
- indique au planificateur que la routine ne peut pas faire fuiter d’information de contexte
- réservé aux superutilisateurs
- si on la déclare telle, s’assurer que la routine est véritablement sûre !
- Option utile lorsque l’on utilise des vues avec l’option `security_barrier`
Certains utilisateurs créent des vues pour filtrer des lignes, afin de restreindre la visibilité sur certaines données. Or, cela peut se révéler dangereux si un utilisateur malintentionné a la possibilité de créer une fonction car il peut facilement contourner cette sécurité si cette option n’est pas utilisée, notamment en jouant sur des paramètres de fonction comme `COST`, qui permet d’indiquer au planificateur un coût estimé pour la fonction.
En indiquant un coût extrêmement faible, le planificateur aura tendance à réécrire la requête, et à déplacer l’exécution de la fonction dans le code même de la vue, avant l’application des filtres restreignant l’accès aux données : la fonction a donc accès a tout le contenu de la table, et peut faire fuiter des données normalement inaccessibles, par exemple à travers l’utilisation de la commande `RAISE`.
L’option `security_barrier` des vues dans PostgreSQL bloque ce comportement du planificateur, mais en conséquence empêche le choix de plans d’exécutions potentiellement plus performants. Déclarer une fonction avec l’option `LEAKPROOF` permet d’indiquer à PostgreSQL que celle-ci ne peut pas occasionner de fuite d’informations. Ainsi, le planificateur de PostgreSQL sait qu’il peut en optimiser l’exécution. Cette option n’est accessible qu’aux superutilisateurs.
### Sécurité : visibilité des sources - 1 {#dev42-ch4-7-6}
- Le code d’une fonction est visible par tout le monde
- y compris ceux qui n’ont pas le droit d’exécuter la fonction
- Vous devez donc écrire un code robuste
- pas espérer que, comme personne n’en a le code, personne ne trouvera de faille
- **Surtout pour les fonctions SECURITY DEFINER**
### Sécurité : visibilité des sources - 2 {#dev42-ch4-7-7}
``` sql
# SELECT proargnames, prosrc
FROM pg_proc WHERE proname='addition';
-[ RECORD 1 ]--------------------------
proargnames | {var1,var2}
prosrc |
: DECLARE
: somme ALIAS FOR $0;
: BEGIN
: somme := var1 + var2;
: RETURN somme;
: END;
:
```
La méta-commande psql `\df+ public.addition` permet également d’obtenir cette information.
### Sécurité : Injections SQL {#dev42-ch4-7-8}
- Les paramètres d’une routine doivent être considérés comme hostiles :
- ils contiennent des données non validées (qui appelle la routine ?)
- ils peuvent, si l’utilisateur est imaginatif, être utilisés pour exécuter du code
- Utiliser `quote_ident`, `quote_literal` et `quote_nullable`
- Utiliser aussi `format`
Voici un exemple simple :
``` sql
CREATE TABLE ma_table_secrete1 (b integer, a integer);
INSERT INTO ma_table_secrete1 SELECT i,i from generate_series(1,20) i;
CREATE OR REPLACE FUNCTION demo_injection ( param1 text, value1 text )
RETURNS SETOF ma_table_secrete1
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
-- Cette fonction prend un nom de colonne variable
-- et l'utilise dans une clause WHERE
-- Il faut donc une requête dynamique
-- Par contre, mon utilisateur 'normal' qui appelle
-- n'a droit qu'aux enregistrements où a<10
DECLARE
ma_requete text;
ma_ligne record;
BEGIN
ma_requete := 'SELECT * FROM ma_table_secrete1 WHERE ' || param1 || ' = ' ||
value1 || ' AND a < 10';
RETURN QUERY EXECUTE ma_requete;
END
$function$;
```
``` sql
# SELECT * from demo_injection ('b','2');
a | b
---+---
2 | 2
(1 row)
# SELECT * from demo_injection ('a','20');
a | b
---+---
(0 row)
```
Tout va bien, elle effectue ce qui est demandé.
Par contre, elle effectue aussi ce qui n’est pas prévu :
``` sql
# SELECT * from demo_injection ('1=1 --','');
a | b
-----+-----
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
6 | 6
7 | 7
8 | 8
9 | 9
10 | 10
11 | 11
12 | 12
13 | 13
14 | 14
15 | 15
16 | 16
17 | 17
18 | 18
19 | 19
20 | 20
(20 lignes)
```
Cet exemple est évidemment simplifié.
Une règle demeure : ne jamais faire confiance aux paramètres d’une fonction. Au minimum, un `quote_ident` pour param1 et un `quote_literal` pour val1 étaient obligatoires, pour se protéger de ce genre de problèmes.
## Optimisation {#dev42-ch4-8}
### Fonctions immutables, stables ou volatiles - 1 {#dev42-ch4-8-1}
- Par défaut, PostgreSQL considère que les fonctions sont `VOLATILE`
- `volatile` : fonction dont l’exécution ne peut ni ne doit être évitée
Les fonctions de ce type sont susceptibles de renvoyer un résultat différent à chaque appel, comme par exemple `random()` ou `setval()`.
Toute fonction ayant des effets de bords doit être qualifiée `volatile` dans le but d’éviter que PostgreSQL utilise un résultat intermédiaire déjà calculé et évite ainsi d’exécuter le code de la fonction.
À noter qu’il est possible de « forcer » le pré-calcul du résultat d’une fonction volatile dans une requête SQL en utilisant une sous-requête. Par exemple, dans l’exemple suivant, `random()` est exécutée pour chaque ligne de la table `ma_table`, et renverra donc une valeur différente par ligne :
``` sql
SELECT random() FROM ma_table;
```
Par contre, en utilisant une sous-requête, l’optimiseur va pré-calculer le résultat de `random()`… l’exécution sera donc plus rapide, mais le résultat différent, puisque la même valeur sera affichée pour toutes les lignes !
``` sql
SELECT ( SELECT random() ) FROM ma_table;
```
### Fonctions immutables, stables ou volatiles - 2 {#dev42-ch4-8-2}
- `immutable` : fonctions déterministes, dont le résultat peut être précalculé avant de planifier la requête.
Certaines fonctions que l’on écrit sont déterministes. C’est-à-dire qu’à paramètre(s) identique(s), le résultat est identique.
Le résultat de telles fonctions est alors remplaçable par son résultat avant même de commencer à planifier la requête.
Voici un exemple qui utilise cette particularité :
``` sql
create function factorielle (a integer) returns bigint as
$$
declare
result bigint;
begin
if a=1 then
return 1;
else
return a*(factorielle(a-1));
end if;
end;
$$
language plpgsql immutable;
```
``` sql
# CREATE TABLE test (a bigint UNIQUE);
CREATE TABLE
# INSERT INTO test SELECT generate_series(1,1000000);
INSERT 0 1000000
# ANALYZE test;
# EXPLAIN ANALYZE SELECT * FROM test WHERE a < factorielle(12);
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on test (cost=0.00..16925.00 rows=1000000 width=8)
(actual time=0.032..130.921 rows=1000000 loops=1)
Filter: (a < '479001600'::bigint)
Planning time: 896.039 ms
Execution time: 169.954 ms
(4 rows)
```
La fonction est exécutée une fois, remplacée par sa constante, et la requête est ensuite planifiée.
Si on déclare la fonction comme stable :
``` sql
# EXPLAIN ANALYZE SELECT * FROM test WHERE a < factorielle(12);
QUERY PLAN
----------------------------------------------------------
Index Only Scan using test_a_key on test
(cost=0.68..28480.67 rows=1000000 width=8)
(actual time=0.137..115.592 rows=1000000 loops=1)
Index Cond: (a < factorielle(12))
Heap Fetches: 0
Planning time: 4.682 ms
Execution time: 153.762 ms
(5 rows)
```
La requête est planifiée sans connaître `factorielle(12)`, donc avec une hypothèse très approximative sur la cardinalité. `factorielle(12)` est calculé, et la requête est exécutée. Grâce au `Index Only Scan`, le requête s’effectue rapidement.
Si on déclare la fonction comme volatile :
``` sql
# EXPLAIN ANALYZE SELECT * FROM test WHERE a < factorielle(12);
QUERY PLAN
-----------------------------------------------------------------------
Seq Scan on test (cost=0.00..266925.00 rows=333333 width=8)
(actual time=1.005..57519.702 rows=1000000 loops=1)
Filter: (a < factorielle(12))
Planning time: 0.388 ms
Execution time: 57573.508 ms
(4 rows)
```
La requête est planifiée, et factorielle(12) est calculé pour chaque enregistrement de la table, car on ne sait pas si elle retourne toujours le même résultat.
### Fonctions immutables, stables ou volatiles - 3 {#dev42-ch4-8-3}
- `stable` : fonction ayant un comportement **stable** au sein d’un même ordre SQL.
Ces fonctions retournent la même valeur pour la même requête SQL, mais peuvent retourner une valeur différente dans la prochaine instruction.
Il s’agit typiquement de fonctions dont le traitement dépend d’autres valeurs dans la base de données, ou bien de réglages de configuration. Les fonctions comme `to_char()`, `to_date()` sont `STABLE` et non `IMMUTABLE` car des paramètres de configuration (*locale* utilisée pour `to_char()`, *timezone* pour les fonctions temporelles, etc.) pourraient influer sur le résultat.
À noter au passage que les fonctions de la famille de `current_timestamp` (et donc le fréquemment utilisé `now()`) renvoient de plus une valeur constante au sein d’une même transaction.
PostgreSQL refusera de déclarer comme `STABLE` toute fonction modifiant des données : elle ne peut pas être stable si elle modifie la base.
### Optimisation : rigueur {#dev42-ch4-8-4}
- Fonction `STRICT`
- La fonction renvoie `NULL` si au moins un des arguments est `NULL`
Les fonctions définies comme `STRICT` ou `RETURNS NULL ON NULL INPUT` annule l’exécution de la requête si l’un des paramètres passés est `NULL`. Dans ce cas, la fonction est considérée comme ayant renvoyé `NULL`.
Si l’on reprend l’exemple de la fonction `factorielle()` :
``` sql
create or replace function factorielle (a integer) returns bigint as
$$
declare
result bigint;
begin
if a=1 then
return 1;
else
return a*(factorielle(a-1));
end if;
end;
$$
language plpgsql immutable STRICT;
```
on obtient le résultat suivant si elle est exécutée avec la valeur `NULL` passée en paramètre :
``` sql
# EXPLAIN ANALYZE SELECT * FROM test WHERE a < factorielle(NULL);
QUERY PLAN
---------------------------------------------------
Result (cost=0.00..0.00 rows=0 width=8)
(actual time=0.002..0.002 rows=0 loops=1)
One-Time Filter: false
Planning time: 0.100 ms
Execution time: 0.039 ms
(4 rows)
```
### Optimisation : EXCEPTION {#dev42-ch4-8-5}
- Un bloc contenant une clause `EXCEPTION` est plus coûteuse en entrée/sortie qu’un bloc sans
- un `SAVEPOINT` est créé à chaque fois pour pouvoir annuler le bloc uniquement.
- À utiliser avec parcimonie
- Un bloc `BEGIN` imbriqué a un coût aussi
- un `SAVEPOINT` est créé à chaque fois.
### Requête statique ou dynamique ? {#dev42-ch4-8-6}
- Les requêtes statiques :
- sont écrites « en dur » dans le code PL/pgSQL
- donc pas d’`EXECUTE` ou `PERFORM`
- sont préparées une fois par session, à leur première exécution
- peuvent avoir un plan générique lorsque c’est jugé utile par le planificateur
Avant la version 9.2, un plan générique (indépendant des paramètres de l’ordre SQL) était systématiquement généré et utilisé. Ce système permet de gagner du temps d’exécution si la requête est réutilisée plusieurs fois, et qu’elle est coûteuse à planifier.
Toutefois, un plan générique n’est pas forcément idéal dans toutes les situations, et peut conduire à des mauvaises performances.
Par exemple :
``` sql
SELECT * FROM ma_table WHERE col_pk = param_function ;
```
est un excellent candidat à être écrit statiquement : le plan sera toujours le même : on attaque l’index de la clé primaire pour trouver l’enregistrement.
``` sql
SELECT * FROM ma_table WHERE col_timestamp > param_function ;
```
est un moins bon candidat : le plan, idéalement, dépend de param_function : on ne parcourt pas la même fraction de la table suivant la valeur de param_function.
Par défaut, un plan générique ne sera utilisé dès la première exécution d’une requête statique que si celle-ci ne dépend d’aucun paramètre. Dans le cas contraire, cela ne se produira qu’au bout de plusieurs exécutions de la requête, et seulement si le planificateur détermine que les plans spécifiques utilisés n’apportent pas d’avantage par rapport au plan générique.
### Requête statique ou dynamique ? - 2 {#dev42-ch4-8-7}
- Les requêtes dynamiques :
- sont écrites avec un `EXECUTE`, `PERFORM`…
- sont préparées à chaque exécution
- ont un plan optimisé
- sont donc plus coûteuses en planification
- mais potentiellement plus rapides à l’exécution
L’écriture d’une requête dynamique est par contre un peu plus pénible, puisqu’il faut fabriquer un ordre SQL, puis le passer en paramètre à `EXECUTE`, avec tous les quote\_\* que cela implique pour en protéger les paramètres.
Pour se faciliter la vie, on peut utiliser `EXECUTE query USING param1, param2 …,` qui est même quelquefois plus lisible que la syntaxe en dur : les paramètres de la requête sont clairement identifiés dans cette syntaxe.
Par contre, la syntaxe `USING` n’est utilisable que si le nombre de paramètres est fixe.
### Requête statique ou dynamique ? -3 {#dev42-ch4-8-8}
- Alors, statique ou dynamique ?
- Si la requête est simple : statique
- peu de `WHERE`
- peu ou pas de jointure
- Sinon dynamique
La limite est difficile à placer, il s’agit de faire un compromis entre le temps de planification d’une requête (quelques dizaines de microsecondes pour une requête basique à potentiellement plusieurs secondes si on dépasse la dizaine de jointures) et le temps d’exécution.
Dans le doute, réalisez un test de performance de la fonction sur un jeu de données représentatif.
## Outils {#dev42-ch4-9}
- Deux outils disponibles
- un debugger
- un pseudo-profiler
Tous les outils d’administration PostgreSQL permettent d’écrire des routines stockées en PL/pgSQL, la plupart avec les fonctionnalités habituelles (comme le surlignage des mots clés, l’indentation automatique, etc.).
Par contre, pour aller plus loin, l’offre est restreinte. Il existe tout de même un debugger qui fonctionne avec pgAdmin 4, sous la forme d’une extension.
### pldebugger {#dev42-ch4-9-1}
- License Artistic 2.0
- Développé par EDB et intégrable dans pgAdmin
- Installé par défaut avec le one-click installer
- mais non activé
- Compilation nécessaire pour les autres systèmes
pldebugger est un outil initialement créé par Dave Page et Korry Douglas au sein d’EnterpriseDB, repris par la communauté. Il est proposé sous license libre (Artistic 2.0).
Il fonctionne grâce à des hooks implémentés dans la version 8.2 de PostgreSQL.
Il est assez peu connu, ce qui explique que peu l’utilisent. Seul l’outil d’installation « one-click installer » l’installe par défaut. Pour tous les autres systèmes, cela réclame une compilation supplémentaire. Cette compilation est d’ailleurs peu aisée étant donné qu’il n’utilise pas le système pgxs.
### pldebugger - Compilation {#dev42-ch4-9-2}
- Récupérer le source avec git
- Copier le répertoire dans le répertoire contrib des sources de PostgreSQL
- Et les suivre étapes standards
- make
- make install
Voici les étapes à réaliser pour compiler pldebugger en prenant pour hypothèse que les sources de PostgreSQL sont disponibles dans le répertoire `/usr/src/postgresql-10` et qu’ils ont été préconfigurés avec la commande `./configure` :
- Se placer dans le répertoire contrib des sources de PostgreSQL :
```text
$ cd /usr/src/postgresql-10/contrib
```
- Cloner le dépôt git :
```text
$ git clone git://git.postgresql.org/git/pldebugger.git
Cloning into 'pldebugger'...
remote: Counting objects: 441, done.
remote: Compressing objects: 100% (337/337), done.
remote: Total 441 (delta 282), reused 171 (delta 104)
Receiving objects: 100% (441/441), 170.24 KiB, done.
Resolving deltas: 100% (282/282), done.
```
- Se placer dans le nouveau répertoire `pldebugger` :
```text
$ cd pldebugger
```
- Compiler pldebugger :
```text
$ make
```
- Installer pldebugger :
```text
# make install
```
L’installation copie le fichier `plugin_debugger.so` dans le répertoire des bibliothèques partagées de PostgreSQL. L’installation copie ensuite les fichiers SQL et de contrôle de l’extension `pldbgapi` dans le répertoire `extension` du répertoire `share` de PostgreSQL.
### pldebugger - Activation {#dev42-ch4-9-3}
- Configurer `shared_preload_libraries`
- `shared_preload_libraries = 'plugin_debugger'`
- Redémarrer PostgreSQL
- Installer l’extension `pldbgapi` :
``` sql
CREATE EXTENSION pldbgapi;
```
La configuration du paramètre `shared_preload_libraries` permet au démarrage de PostgreSQL de laisser la bibliothèque `plugin_debugger` s’accrocher aux hooks de l’interpréteur PL/pgSQL. Du coup, pour que la modification de ce paramètre soit prise en compte, il faut redémarrer PostgreSQL.
L’interaction avec pldebugger se fait par l’intermédiaire de procédures stockées. Il faut donc au préalable créer ces procédures stockées dans la base contenant les procédures PL/pgSQL à débugguer. Cela se fait en créant l’extension :
``` sql
$ psql
psql (13.0)
Type "help" for help.
postgres# create extension pldbgapi;
CREATE EXTENSION
```
### auto_explain {#dev42-ch4-9-4}
- Mise en place globale (traces) :
- `shared_preload_libraries='auto_explain'` si global
- `ALTER DATABASE erp SET auto_explain.log_min_duration = '3s'`
- Ou par session :
- `LOAD 'auto_explain'`
- `SET auto_explain.log_analyze TO true;`
- `SET auto_explain.log_nested_statements TO true;`
`auto_explain` est une « contrib » officielle de PostgreSQL (et non une extension). Il permet de tracer le plan d’une requête. En général, on ne trace ainsi que les requêtes dont la durée d’exécution dépasse la durée configurée avec le paramètre’`auto_explain.log_min_duration`. Par défaut, ce paramètre est à -1 pour ne tracer aucun plan.
Comme dans un `EXPLAIN` classique, on peut activer toutes les options (par exemple `ANALYZE` ou `TIMING` avec, respectivement `SET auto_explain.log_analyze TO true;` et `SET auto_explain.log_timing TO true;`) mais l’impact en performance peut être important même pour les requêtes qui ne seront pas tracées.
D’autres options existent, qui reprennent les paramètres habituels d’`EXPLAIN`, notamment `auto_explain.log_buffers` et `auto_explain.log_settings` (voir la [documentation](https://docs.postgresql.fr/current/auto-explain.html)).
L’exemple suivant utilise deux fonctions imbriquées mais cela marche pour une simple requête :
``` sql
CREATE OR REPLACE FUNCTION table_nb_indexes (tabname IN text, nbi OUT int)
RETURNS int
LANGUAGE plpgsql
AS $$
BEGIN
SELECT COUNT(*) INTO nbi
FROM pg_index i INNER JOIN pg_class c ON (c.oid=indrelid)
WHERE relname LIKE tabname ;
RETURN ;
END ;
$$
;
CREATE OR REPLACE FUNCTION table_nb_col_indexes
(tabname IN text, nb_cols OUT int, nb_indexes OUT int)
RETURNS record
LANGUAGE plpgsql
AS $$
BEGIN
SELECT COUNT(*) INTO nb_cols
FROM pg_attribute
WHERE attname LIKE tabname ;
SELECT nbi INTO nb_indexes FROM table_nb_indexes (tabname) ;
RETURN ;
END ;
$$
;
```
Chargement dans la session d’`auto_explain` (si pas déjà présent dans `shared_preload_libraries`) :
``` sql
LOAD 'auto_explain' ;
```
Activation pour toutes les requêtes, avec les options `ANALYZE` et `BUFFERS`, puis affichage dans la console (si la sortie dans les traces ne suffit pas) :
``` sql
SET auto_explain.log_min_duration TO 0 ;
SET auto_explain.log_analyze TO on ;
SET auto_explain.log_buffers TO on ;
SET client_min_messages TO log ;
```
Test de la première fonction : le plan s’affiche, mais les compteurs (ici juste *shared hit*), ne concernent que la fonction dans son ensemble.
```text
postgres=# SELECT * FROM table_nb_col_indexes ('pg_class') ;
LOG: duration: 2.208 ms plan:
Query Text: SELECT * FROM table_nb_col_indexes ('pg_class') ;
Function Scan on table_nb_col_indexes (cost=0.25..0.26 rows=1 width=8)
(actual time=2.203..2.203 rows=1 loops=1)
Buffers: shared hit=294
nb_cols | nb_indexes
---------+------------
0 | 3
```
En activant `auto_explain.log_nested_statements`, on voit clairement les plans de chaque requête exécutée :
``` sql
SET auto_explain.log_nested_statements TO on ;
```
```text
postgres=# SELECT * FROM table_nb_col_indexes ('pg_class') ;
LOG: duration: 0.235 ms plan:
Query Text: SELECT COUNT(*) FROM pg_attribute
WHERE attname LIKE tabname
Aggregate (cost=65.95..65.96 rows=1 width=8)
(actual time=0.234..0.234 rows=1 loops=1)
Buffers: shared hit=24
-> Index Only Scan using pg_attribute_relid_attnam_index on pg_attribute
(cost=0.28..65.94 rows=1 width=0)
(actual time=0.233..0.233 rows=0 loops=1)
Index Cond: ((attname >= 'pg'::text) AND (attname < 'ph'::text))
Filter: (attname ~~ 'pg_class'::text)
Heap Fetches: 0
Buffers: shared hit=24
LOG: duration: 0.102 ms plan:
Query Text: SELECT COUNT(*) FROM pg_index i
INNER JOIN pg_class c ON (c.oid=indrelid)
WHERE relname LIKE tabname
Aggregate (cost=24.48..24.49 rows=1 width=8)
(actual time=0.100..0.100 rows=1 loops=1)
Buffers: shared hit=18
-> Nested Loop (cost=0.14..24.47 rows=1 width=0)
(actual time=0.096..0.099 rows=3 loops=1)
Buffers: shared hit=18
-> Seq Scan on pg_class c (cost=0.00..23.30 rows=1 width=4)
(actual time=0.091..0.093 rows=1 loops=1)
Filter: (relname ~~ 'pg_class'::text)
Rows Removed by Filter: 580
Buffers: shared hit=16
-> Index Only Scan using pg_index_indrelid_index on pg_index i
(cost=0.14..1.16 rows=1 width=4)
(actual time=0.003..0.004 rows=3 loops=1)
Index Cond: (indrelid = c.oid)
Heap Fetches: 0
Buffers: shared hit=2
LOG: duration: 0.703 ms plan:
Query Text: SELECT nbi FROM table_nb_indexes (tabname)
Function Scan on table_nb_indexes (cost=0.25..0.26 rows=1 width=4)
(actual time=0.702..0.702 rows=1 loops=1)
Buffers: shared hit=26
LOG: duration: 1.524 ms plan:
Query Text: SELECT * FROM table_nb_col_indexes ('pg_class') ;
Function Scan on table_nb_col_indexes (cost=0.25..0.26 rows=1 width=8)
(actual time=1.520..1.520 rows=1 loops=1)
Buffers: shared hit=59
nb_cols | nb_indexes
---------+------------
0 | 3
```
Cet exemple permet de mettre le doigt sur un petit problème de performance dans la fonction : le `_` est interprété comme critère de recherche. En modifiant le paramètre on peut constater le changement de plan au niveau des index :
```text
postgres=# SELECT * FROM table_nb_col_indexes ('pg\_class') ;
LOG: duration: 0.141 ms plan:
Query Text: SELECT COUNT(*) FROM pg_attribute
WHERE attname LIKE tabname
Aggregate (cost=56.28..56.29 rows=1 width=8)
(actual time=0.140..0.140 rows=1 loops=1)
Buffers: shared hit=24
-> Index Only Scan using pg_attribute_relid_attnam_index on pg_attribute
(cost=0.28..56.28 rows=1 width=0)
(actual time=0.138..0.138 rows=0 loops=1)
Index Cond: (attname = 'pg_class'::text)
Filter: (attname ~~ 'pg\_class'::text)
Heap Fetches: 0
Buffers: shared hit=24
LOG: duration: 0.026 ms plan:
Query Text: SELECT COUNT(*) FROM pg_index i
INNER JOIN pg_class c ON (c.oid=indrelid)
WHERE relname LIKE tabname
Aggregate (cost=3.47..3.48 rows=1 width=8) (actual time=0.024..0.024 rows=1 loops=1)
Buffers: shared hit=8
-> Nested Loop (cost=0.42..3.47 rows=1 width=0) (…)
Buffers: shared hit=8
-> Index Scan using pg_class_relname_nsp_index on pg_class c
(cost=0.28..2.29 rows=1 width=4)
(actual time=0.017..0.018 rows=1 loops=1)
Index Cond: (relname = 'pg_class'::text)
Filter: (relname ~~ 'pg\_class'::text)
Buffers: shared hit=6
-> Index Only Scan using pg_index_indrelid_index on pg_index i (…)
Index Cond: (indrelid = c.oid)
Heap Fetches: 0
Buffers: shared hit=2
LOG: duration: 0.414 ms plan:
Query Text: SELECT nbi FROM table_nb_indexes (tabname)
Function Scan on table_nb_indexes (cost=0.25..0.26 rows=1 width=4)
(actual time=0.412..0.412 rows=1 loops=1)
Buffers: shared hit=16
LOG: duration: 1.046 ms plan:
Query Text: SELECT * FROM table_nb_col_indexes ('pg\_class') ;
Function Scan on table_nb_col_indexes (cost=0.25..0.26 rows=1 width=8)
(actual time=1.042..1.043 rows=1 loops=1)
Buffers: shared hit=56
nb_cols | nb_indexes
---------+------------
0 | 3
```
Pour les procédures, il est possible de mettre en place cette trace avec `ALTER PROCEDURE … SET auto_explain.log_min_duration = 0`. Cela ne fonctionne pas pour les fonctions.
pgBadger est capable de lire les plans tracés par `auto_explain`, de les intégrer à son rapport et d’inclure un lien vers [depesz.com](https://explain.depesz.com/) pour une version plus lisible.
### pldebugger - Utilisation {#dev42-ch4-9-5}
- Via pgAdmin
Le menu contextuel pour accéder au débuggage d’une fonction :
