Débogage complet et optimisation d'une procédure multi-étapes

Les procédures multi-étapes, c'est un peu le "couteau suisse" des bases de données. Souvent, elles incluent la validation des données d'entrée, l'exécution de changements (genre mise à jour de lignes, insertion de logs), et parfois même un peu d'analytics. Mais voilà le souci : plus la procédure est complexe, plus t'as de chances de te planter. Une erreur logique, une requête lente, un détail oublié — et tout peut partir en vrille.

Le débogage complet, ça inclut ces aspects-là :

1. Analyse des données d'entrée : les paramètres sont-ils bien configurés ? Les données transmises sont-elles correctes ?
2. Vérification de l'exécution des étapes clés : est-ce que chaque étape de la procédure se passe bien ?
3. Logging des résultats intermédiaires : histoire de savoir ce qui s'est passé avant que tout "casse".
4. Optimisation des goulots d'étranglement de performance : on améliore les points faibles qui "ralentissent" les requêtes.

Énoncé du problème : exemple de procédure multi-étapes

Pour notre exemple pratique, imagine qu'on bosse avec la base de données d'une boutique en ligne. On doit créer une procédure pour traiter une commande. Elle va faire les étapes suivantes :

1. Vérifier la disponibilité du produit en stock.
2. Réserver le produit.
3. Mettre à jour le statut de la commande.
4. Enregistrer les événements (genre réservation réussie ou erreur) dans la table des logs.

Script de structure de la base de données :

-- Table des produits
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    stock_quantity INTEGER NOT NULL
);

-- Table des commandes
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(product_id),
    order_status TEXT NOT NULL
);

-- Table des logs
CREATE TABLE order_logs (
    log_id SERIAL PRIMARY KEY,
    order_id INTEGER,
    log_message TEXT,
    log_time TIMESTAMP DEFAULT NOW()
);

Étape 1 : Création de la procédure multi-étapes

Allez, on va créer une procédure de base process_order. Elle va prendre l'identifiant de la commande et gérer toutes les étapes du traitement.

CREATE OR REPLACE FUNCTION process_order(p_order_id INTEGER)
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    v_product_id INTEGER;
    v_stock_quantity INTEGER;
BEGIN
    -- 1. On récupère l'identifiant du produit et le statut de la commande
    SELECT product_id INTO v_product_id
    FROM orders
    WHERE order_id = p_order_id;

    IF v_product_id IS NULL THEN
        RAISE EXCEPTION 'Commande % n''existe pas ou product_id manquant', p_order_id;
    END IF;

    -- 2. On vérifie la disponibilité du produit en stock
    SELECT stock_quantity INTO v_stock_quantity
    FROM products
    WHERE product_id = v_product_id;

    IF v_stock_quantity <= 0 THEN
        RAISE EXCEPTION 'Produit % en rupture de stock', v_product_id;
    END IF;

    -- 3. On met à jour la quantité en stock
    UPDATE products
    SET stock_quantity = stock_quantity - 1
    WHERE product_id = v_product_id;

    -- 4. On met à jour le statut de la commande
    UPDATE orders
    SET order_status = 'Traité'
    WHERE order_id = p_order_id;

    -- 5. On enregistre l'événement réussi dans les logs
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, 'Commande traitée avec succès.');
END;
$$;

Étape 2 : Logging des erreurs avec RAISE NOTICE et RAISE EXCEPTION

C'est là que la magie commence. On va ajouter du logging à chaque étape pour choper les erreurs et piger ce qui se passe à chaque moment.

Code mis à jour avec logging :

CREATE OR REPLACE FUNCTION process_order(p_order_id INTEGER)
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    v_product_id INTEGER;
    v_stock_quantity INTEGER;
BEGIN
    RAISE NOTICE 'Traitement de la commande %...', p_order_id;

    -- 1. On récupère l'identifiant du produit
    SELECT product_id INTO v_product_id
    FROM orders
    WHERE order_id = p_order_id;

    IF v_product_id IS NULL THEN
        RAISE EXCEPTION 'Commande % n''existe pas ou product_id manquant', p_order_id;
    END IF;
    RAISE NOTICE 'Product ID pour la commande % : %', p_order_id, v_product_id;

    -- 2. On vérifie la disponibilité du produit en stock
    SELECT stock_quantity INTO v_stock_quantity
    FROM products
    WHERE product_id = v_product_id;

    IF v_stock_quantity <= 0 THEN
        RAISE EXCEPTION 'Produit % en rupture de stock', v_product_id;
    END IF;
    RAISE NOTICE 'Quantité en stock pour le produit % : %', v_product_id, v_stock_quantity;

    -- 3. On met à jour la quantité en stock
    UPDATE products
    SET stock_quantity = stock_quantity - 1
    WHERE product_id = v_product_id;

    -- 4. On met à jour le statut de la commande
    UPDATE orders
    SET order_status = 'Traité'
    WHERE order_id = p_order_id;

    -- 5. Logging de la réussite
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, 'Commande traitée avec succès.');
    RAISE NOTICE 'Commande % traitée avec succès.', p_order_id;

EXCEPTION WHEN OTHERS THEN
    -- Logging de l'erreur
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, 'Erreur : ' || SQLERRM);
    RAISE;
END;
$$;

Étape 3 : Optimisation avec des index

Si t'as plein de produits ou de commandes dans la base, trouver la bonne ligne peut devenir un vrai goulot d'étranglement. On va ajouter des index pour accélérer la sélection pendant le traitement :

-- Index pour accélérer la recherche dans la table orders
CREATE INDEX idx_orders_product_id ON orders(product_id);

-- Index pour accélérer la recherche dans la table products
CREATE INDEX idx_products_stock_quantity ON products(stock_quantity);

Étape 4 : Analyse des performances avec EXPLAIN ANALYZE

Maintenant, on va checker à quelle vitesse notre fonction s'exécute. Pour ça, on la lance avec une analyse de perf :

EXPLAIN ANALYZE
SELECT process_order(1);

Le résultat va te montrer combien de temps prend chaque étape. Tu pourras voir laquelle est la plus lente — ça t'aidera à optimiser encore plus la procédure.

Étape 5 : Amélioration avec les transactions

Pour fiabiliser le tout, tu peux emballer toute la procédure dans une transaction. Comme ça, si un truc foire, tout est rollback.

BEGIN;

-- Appel de la fonction
SELECT process_order(1);

-- Commit de la transaction
COMMIT;

Dans le code de la fonction, tu peux utiliser SAVEPOINT et ROLLBACK TO SAVEPOINT pour gérer les erreurs partielles.

Exercice pratique : traitement de commandes en masse

On finit la leçon avec un exemple où on traite plusieurs commandes d'un coup. On va créer une fonction qui va traiter toutes les commandes avec le statut En attente :

CREATE OR REPLACE FUNCTION process_all_orders()
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    v_order_id INTEGER;
BEGIN
    FOR v_order_id IN
        SELECT order_id
        FROM orders
        WHERE order_status = 'En attente'
    LOOP
        BEGIN
            PERFORM process_order(v_order_id);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'Échec du traitement de la commande % : %', v_order_id, SQLERRM;
        END;
    END LOOP;
END;
$$;

Quand tu appelles cette fonction, toutes les commandes avec le statut En attente seront traitées, et toutes les erreurs seront juste loguées.

Voilà, on t'a montré comment déboguer et optimiser des procédures complexes, pour les rendre plus fiables, plus performantes et plus lisibles. Ces skills te serviront dans des vrais projets, où la qualité des procédures fait la différence pour le succès de l'appli.