Gestion des erreurs dans PostgreSQL

Introduction

Dans ce laboratoire, vous explorerez les techniques de gestion des erreurs dans PostgreSQL en utilisant son langage procédural, PL/pgSQL. Vous apprendrez à créer des fonctions capables de gérer gracieusement les erreurs potentielles au lieu de planter. Le laboratoire couvre la génération de messages informatifs, la capture d'exceptions spécifiques comme la "division par zéro" et l'enregistrement des détails d'erreur dans une table de base de données pour une consultation ultérieure. À la fin, vous comprendrez comment construire des fonctions de base de données plus robustes et fiables.

Émettre des notifications et des avertissements

Dans cette étape, vous apprendrez à utiliser l'instruction RAISE dans une fonction PL/pgSQL. L'instruction RAISE est utile pour fournir des messages informatifs, des avertissements ou des sorties de débogage sans interrompre l'exécution de la fonction.

Connectez-vous d'abord au terminal interactif de PostgreSQL, psql, en tant qu'utilisateur postgres. Toutes les commandes SQL suivantes dans ce laboratoire seront exécutées dans ce terminal.

sudo -u postgres psql

Vous verrez maintenant l'invite psql, qui ressemble à postgres=#.

Créons une fonction nommée greet qui prend un nom en entrée et génère une NOTICE. Les notices sont des messages de faible priorité.

Exécutez la commande SQL suivante dans le shell psql :

CREATE OR REPLACE FUNCTION greet(name TEXT)
RETURNS TEXT AS $$
BEGIN
  RAISE NOTICE 'Greeting function called with name: %', name;
  RETURN 'Hello, ' || name;
END;
$$ LANGUAGE plpgsql;

Cette fonction prend un argument name. La ligne RAISE NOTICE '...', name; génère un message de notice. Le % est un espace réservé qui est remplacé par la valeur de la variable name.

Appelez maintenant la fonction pour la voir en action :

SELECT greet('LabEx');

La sortie affiche à la fois le message de notice et la valeur de retour de la fonction :

NOTICE:  Greeting function called with name: LabEx
   greet
-----------
 Hello, LabEx
(1 row)

Vous pouvez également générer des messages avec un niveau de sévérité plus élevé, comme WARNING. Modifions la fonction pour générer un avertissement si le nom d'entrée est vide.

CREATE OR REPLACE FUNCTION greet(name TEXT)
RETURNS TEXT AS $$
BEGIN
  IF name IS NULL OR name = '' THEN
    RAISE WARNING 'Input name is empty or NULL.';
    RETURN 'Hello, stranger';
  ELSE
    RAISE NOTICE 'Greeting function called with name: %', name;
    RETURN 'Hello, ' || name;
  END IF;
END;
$$ LANGUAGE plpgsql;

Appelez maintenant la fonction avec une chaîne vide pour déclencher l'avertissement :

SELECT greet('');

Cette fois, vous verrez un message WARNING au lieu d'un NOTICE :

WARNING:  Input name is empty or NULL.
      greet
------------------
 Hello, stranger
(1 row)

Vous avez utilisé avec succès RAISE pour générer différents types de messages à partir d'une fonction.

Déclencher et intercepter une exception

Dans cette étape, vous apprendrez à gérer les erreurs d'exécution, connues sous le nom d'exceptions. Une exception non gérée terminera immédiatement votre fonction. Vous allez d'abord créer une fonction qui échoue, puis la modifier pour capturer l'exception gracieusement.

Créons une fonction simple_divide qui effectue une division entière. Cette fonction présente un défaut potentiel : elle plantera si vous essayez de diviser par zéro.

CREATE OR REPLACE FUNCTION simple_divide(numerator INTEGER, denominator INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN numerator / denominator;
END;
$$ LANGUAGE plpgsql;

Appelez d'abord la fonction avec des entrées valides pour confirmer qu'elle fonctionne correctement :

SELECT simple_divide(10, 2);

La sortie sera le résultat de la division :

 simple_divide
---------------
             5
(1 row)

Appelez maintenant la fonction avec un dénominateur nul pour déclencher une exception :

SELECT simple_divide(10, 0);

Cette commande échouera et retournera un message d'erreur. L'exécution de la fonction est interrompue.

ERROR:  division by zero
CONTEXT:  PL/pgSQL function simple_divide(integer,integer) line 3 at RETURN

Ceci est une exception non gérée. Pour éviter que la fonction ne plante, vous pouvez utiliser un bloc EXCEPTION. Créons une nouvelle fonction safe_divide qui inclut la gestion des erreurs.

CREATE OR REPLACE FUNCTION safe_divide(numerator INTEGER, denominator INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN numerator / denominator;
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'Error: Cannot divide by zero.';
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Voici comment fonctionne le bloc EXCEPTION :

• BEGIN...END; : Ceci définit un bloc de code.
• EXCEPTION : Ce mot-clé démarre la section de gestion des exceptions.
• WHEN division_by_zero THEN : Ceci spécifie que le code suivant ne doit s'exécuter que lorsqu'une erreur division_by_zero se produit.
• Le code à l'intérieur du gestionnaire génère une notice et retourne NULL au lieu de planter.

Appelez maintenant la nouvelle fonction safe_divide avec un dénominateur nul :

SELECT safe_divide(10, 0);

Cette fois, la fonction ne plante pas. Elle capture l'exception, affiche votre notice personnalisée et retourne NULL.

NOTICE:  Error: Cannot divide by zero.
 safe_divide
-------------

(1 row)

Vous avez réussi à capturer une exception spécifique et à contrôler la sortie de la fonction.

Journalisation des erreurs dans une table

La capture des exceptions est une bonne pratique, mais pour l'audit et le débogage, il est souvent préférable de journaliser les erreurs dans un emplacement persistant, comme une table. Dans cette étape, vous allez modifier la fonction safe_divide pour écrire les détails de l'erreur dans la table error_log qui a été créée lors de la configuration du laboratoire.

Examinons d'abord la structure de la table error_log à l'aide de la commande \d dans psql.

\d error_log

Vous verrez les colonnes de la table, qui comprennent un id auto-incrémenté, un timestamp, le message d'erreur et le function_name.

                                              Table "public.error_log"
    Column     |            Type             | Collation | Nullable |                     Default
---------------+-----------------------------+-----------+----------+----------------------------------------------------
 id            | integer                     |           | not null | nextval('error_log_id_seq'::regclass)
 timestamp     | timestamp without time zone |           |          | (now() AT TIME ZONE 'utc'::text)
 message       | text                        |           |          |
 function_name | text                        |           |          |
Indexes:
    "error_log_pkey" PRIMARY KEY, btree (id)

Modifions maintenant la fonction safe_divide. Dans le bloc EXCEPTION, vous ajouterez une instruction INSERT pour journaliser l'erreur avant de retourner.

CREATE OR REPLACE FUNCTION safe_divide(numerator INTEGER, denominator INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN numerator / denominator;
EXCEPTION
  WHEN division_by_zero THEN
    INSERT INTO error_log (message, function_name)
    VALUES ('Division by zero occurred!', 'safe_divide');

    RAISE NOTICE 'Error: Cannot divide by zero. Details logged.';
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Appelez maintenant à nouveau la fonction pour déclencher l'erreur et la nouvelle logique de journalisation :

SELECT safe_divide(10, 0);

La fonction retournera NULL comme précédemment, mais elle aura également effectué une écriture dans la base de données. Pour vérifier cela, interrogez la table error_log pour voir la nouvelle entrée.

SELECT function_name, message FROM error_log;

La sortie devrait afficher l'erreur journalisée :

 function_name |          message
---------------+----------------------------
 safe_divide   | Division by zero occurred!
(1 row)

Cela confirme que votre logique de gestion des erreurs est désormais capable de conserver les informations d'erreur pour une analyse ultérieure.

Gestion des exceptions générales avec OTHERS

Jusqu'à présent, vous avez géré une erreur très spécifique : division_by_zero. Mais qu'en est-il des autres erreurs inattendues ? PL/pgSQL fournit un gestionnaire d'exceptions général OTHERS pour intercepter toute erreur qui n'a pas été interceptée par une clause WHEN plus spécifique.

Dans cette étape, vous allez améliorer la fonction safe_divide pour gérer toute exception possible. Vous utiliserez également deux variables spéciales, SQLSTATE (le code d'erreur SQL) et SQLERRM (le message d'erreur SQL), pour journaliser des informations plus détaillées.

Modifions la fonction safe_divide une dernière fois.

CREATE OR REPLACE FUNCTION safe_divide(numerator INTEGER, denominator INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN numerator / denominator;
EXCEPTION
  WHEN division_by_zero THEN
    INSERT INTO error_log (message, function_name)
    VALUES ('Division by zero occurred!', 'safe_divide');

    RAISE NOTICE 'Error: Cannot divide by zero. Details logged.';
    RETURN NULL;

  WHEN OTHERS THEN
    INSERT INTO error_log (message, function_name)
    VALUES ('An unexpected error occurred: ' || SQLSTATE || ' - ' || SQLERRM, 'safe_divide');

    RAISE NOTICE 'An unexpected error occurred. Details logged.';
    RETURN -1;
END;
$$ LANGUAGE plpgsql;

Le nouveau bloc WHEN OTHERS interceptera toute autre erreur. Il construit un message détaillé en utilisant les variables SQLSTATE et SQLERRM et l'insère dans le journal. Il retourne ensuite -1 pour signaler qu'une erreur générique s'est produite.

Bien que notre fonction actuelle ne puisse déclencher qu'une erreur division_by_zero, cette structure la rend beaucoup plus robuste face aux modifications futures ou aux entrées inattendues. Par exemple, si les types d'entrée étaient modifiés pour un type susceptible de provoquer un dépassement, le bloc OTHERS intercepterait l'erreur numeric_value_out_of_range.

Testons à nouveau le chemin division_by_zero pour nous assurer qu'il fonctionne toujours comme prévu. Tout d'abord, videz la table de journalisation pour un test propre.

TRUNCATE error_log;

Appelez maintenant la fonction :

SELECT safe_divide(10, 0);

Enfin, vérifiez le journal. Il devrait contenir le message spécifique "Division by zero", confirmant que le premier bloc WHEN a été correctement choisi.

SELECT message FROM error_log;

          message
----------------------------
 Division by zero occurred!
(1 row)

Vous avez maintenant créé une fonction résiliente qui gère à la fois les erreurs spécifiques et anticipées, ainsi que les erreurs générales et inattendues.

Résumé

Dans ce laboratoire, vous avez appris les bases de la gestion des erreurs en PL/pgSQL de PostgreSQL. Vous avez commencé par utiliser l'instruction RAISE pour émettre des messages NOTICE et WARNING à des fins de débogage et d'information. Vous êtes ensuite passé à la gestion des exceptions d'exécution en créant une fonction susceptible d'échouer, puis en implémentant un bloc BEGIN...EXCEPTION...END pour intercepter une erreur spécifique division_by_zero. En vous basant sur cela, vous avez amélioré la fonction pour enregistrer les détails des erreurs dans une table de base de données, fournissant ainsi un enregistrement persistant pour l'audit. Enfin, vous avez rendu la fonction plus robuste en ajoutant un gestionnaire général WHEN OTHERS pour intercepter les erreurs inattendues, garantissant ainsi que votre fonction peut échouer gracieusement dans diverses conditions.