Analyse des erreurs typiques lors du debug et de l’optimisation de PL/pgSQL

Aujourd’hui, pour finir ce voyage épique à travers PL/pgSQL, on va décortiquer les erreurs les plus fréquentes qui peuvent te tomber dessus pendant le debug et l’optimisation de tes fonctions et procédures. Connaître ces pièges va t’aider non seulement à éviter des galères plus tard, mais aussi à mieux t’en sortir quand tu tombes sur un bug.

Erreurs typiques lors du debug et de l’optimisation

1. Mauvaise utilisation des variables

Une des erreurs les plus fréquentes quand tu codes et débugges des fonctions en PL/pgSQL — c’est de mal déclarer ou utiliser les variables. Par exemple, si tu oublies de préciser explicitement le type d’une variable ou si tu te mélanges dans les valeurs passées en paramètres. Mate un peu à quoi ça peut ressembler en vrai :

CREATE OR REPLACE FUNCTION calculate_discount(order_total NUMERIC)
RETURNS NUMERIC AS $$
DECLARE
    discount_rate NUMERIC;
BEGIN
    -- Oups ! J’ai oublié d’initialiser la variable discount_rate
    RETURN order_total * discount_rate;
END;
$$ LANGUAGE plpgsql;

Quand tu appelles cette fonction, tu vas te prendre une erreur liée à l’utilisation de NULL dans les calculs, parce que la variable discount_rate n’est pas initialisée au départ.

Comment éviter ça :

1. Pense toujours à donner une valeur par défaut à tes variables quand tu les déclares :

   DECLARE
       discount_rate NUMERIC := 0.1; -- Valeur par défaut

2. Vérifie tes variables avec RAISE NOTICE pour être sûr qu’elles ont bien la valeur attendue :

RAISE NOTICE 'Valeur de discount_rate : %', discount_rate;

2. Pas de logging des erreurs

Un autre souci courant — c’est l’absence de mécanisme de logging. Si un truc part en vrille et que tu ne logues pas ce qui se passe dans ta fonction, c’est comme chercher un chat noir dans une pièce sombre, surtout si tu sais même pas s’il y a un chat.

Voilà un exemple de fonction sans logging :

CREATE OR REPLACE FUNCTION process_order(order_id INT)
RETURNS VOID AS $$
BEGIN
    -- Une logique de traitement de commande bien velue
    UPDATE orders SET status = 'processed' WHERE id = order_id;
END;
$$ LANGUAGE plpgsql;

Et si order_id est foireux ? Et si la ligne dans la table orders n’existe pas ?

Comment éviter ça : Ajoute des RAISE NOTICE ou RAISE EXCEPTION pour logger les étapes critiques :

CREATE OR REPLACE FUNCTION process_order(order_id INT)
RETURNS VOID AS $$
BEGIN
    -- On log les données d’entrée
    RAISE NOTICE 'Traitement de la commande avec ID %', order_id;

    -- Logique de traitement velue
    UPDATE orders SET status = 'processed' WHERE id = order_id;

    -- On log le résultat
    RAISE NOTICE 'Statut de la commande mis à jour pour ID %', order_id;
END;
$$ LANGUAGE plpgsql;

Maintenant tu pourras facilement voir où ça coince grâce aux messages affichés.

3. Ignorer la performance des requêtes

C’est un des pires ennemis de tout dev SQL. Par exemple, tu écris une fonction qui a l’air clean, mais elle rame à mort. Souvent, c’est parce qu’il n’y a pas d’index ou que les plans d’exécution sont pourris.

Exemple de requête lente :

CREATE OR REPLACE FUNCTION get_large_orders()
RETURNS TABLE(order_id INT, total NUMERIC) AS $$
BEGIN
    RETURN QUERY
    SELECT id, total FROM orders WHERE total > 1000;
END;
$$ LANGUAGE plpgsql;

Si la colonne total dans la table orders n’est pas indexée, la requête va scanner toute la table, et ça craint niveau perf.

Comment éviter ça :

1. Utilise EXPLAIN ANALYZE pour checker l’efficacité de tes requêtes :

EXPLAIN ANALYZE SELECT id, total FROM orders WHERE total > 1000;

2. Crée des index sur les colonnes que tu utilises souvent :

CREATE INDEX idx_orders_total ON orders(total);

4. Utiliser le mauvais niveau d’isolation des transactions

Quand tu fais des procédures complexes, il peut y avoir des bugs à cause d’une mauvaise compréhension des niveaux d’isolation des transactions. Par exemple, si deux transactions essaient de mettre à jour la même ligne en même temps, tu peux te prendre un deadlock.

Exemple de deadlock potentiel :

BEGIN;
UPDATE orders SET status = 'processed' WHERE id = 1;

-- Attente du lock d’une autre transaction
UPDATE inventory SET stock = stock - 1 WHERE product_id = 100;
COMMIT;

Si une autre transaction fait ces opérations dans l’autre sens, tu te retrouves avec un blocage mutuel.

Comment éviter ça :

1. Réfléchis à l’ordre des opérations et garde-le partout.
2. Utilise le niveau d’isolation SERIALIZABLE si besoin.

5. Pas de gestion des erreurs

Gérer les erreurs, c’est pas juste une bonne pratique, c’est essentiel pour que ton code tienne la route. Par exemple, dans le code suivant, il n’y a aucune gestion des erreurs possibles :

CREATE OR REPLACE FUNCTION add_order(order_id INT)
RETURNS VOID AS $$
BEGIN
    INSERT INTO orders (id, status) VALUES (order_id, 'new');
END;
$$ LANGUAGE plpgsql;

Si jamais order_id existe déjà, tu vas te prendre une erreur duplicate key value violates unique constraint.

Comment éviter ça : Utilise des blocs de gestion d’exceptions :

CREATE OR REPLACE FUNCTION add_order(order_id INT)
RETURNS VOID AS $$
BEGIN
    INSERT INTO orders (id, status) VALUES (order_id, 'new');
EXCEPTION WHEN unique_violation THEN
    RAISE NOTICE 'Commande avec ID % existe déjà !', order_id;
END;
$$ LANGUAGE plpgsql;

Exemples d’erreurs et comment les corriger

Erreur 1 : Les requêtes sont lentes à cause du manque d’index

Situation : Tu as une requête qui filtre une table sur une colonne, mais il n’y a pas d’index sur cette colonne.

Correction : Crée un index sur la colonne concernée.

Erreur 2 : La logique de la fonction est trop complexe et difficile à débugger

Situation : La fonction contient trop de logique et n’est pas découpée en sous-fonctions.

Correction : Découpe ta grosse fonction en petites sous-fonctions. Ça rend le code plus lisible et le debug plus simple.

Erreur 3 : Mauvaise utilisation de RAISE EXCEPTION

Situation : RAISE EXCEPTION est utilisé pour toutes les erreurs, même les petites.

Correction : Utilise RAISE NOTICE pour les messages d’info et RAISE EXCEPTION seulement pour les cas critiques.

RAISE NOTICE 'Tout roule — étape actuelle de la fonction terminée.';
RAISE EXCEPTION 'Un truc a cassé ! Check les paramètres d’entrée.';

Conseils pour éviter les erreurs

1. Ajoute du logging : sur les étapes critiques de ta fonction, utilise RAISE NOTICE pour suivre l’exécution.
2. Teste tes fonctions : utilise régulièrement des données de test pour vérifier tes fonctions et procédures.
3. Garde ton code lisible : découpe les fonctions complexes en petites sous-fonctions et procédures.
4. Analyse la performance : utilise EXPLAIN ANALYZE pour t’assurer que tes requêtes tournent bien.
5. Prépare-toi aux imprévus : ajoute toujours des blocs de gestion d’exceptions pour gérer les erreurs.

EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Une erreur imprévue est survenue : %', SQLERRM;

Ça va te permettre de mieux gérer les bugs et d’éviter qu’ils reviennent plus tard.