Appel de procédures et fonctions à l'intérieur des transactions

Dans les systèmes de bases de données modernes, la logique métier est souvent réalisée côté serveur — avec des procédures et des fonctions. Quand tu bosses avec PostgreSQL, c'est super important de piger la différence entre fonctions et procédures (surtout depuis que les procédures sont arrivées avec la version 11+) et comment elles interagissent avec les transactions.

En dessous, je t'explique les trucs essentiels sur la mécanique des transactions, les appels imbriqués et le rollback partiel des changements dans les procédures/fonctions PostgreSQL 17, d'après la doc officielle et les limitations actuelles.

Concepts clés : fonctions vs procédures

Fonction (CREATE FUNCTION) — s'exécute toujours dans une seule transaction externe ; à l'intérieur des fonctions, tu peux pas utiliser les commandes transactionnelles explicites (BEGIN, COMMIT, ROLLBACK, SAVEPOINT).

• Tous les changements sont validés ou annulés uniquement au niveau de la transaction externe.
• Pour un « rollback partiel » dans une fonction, on utilise BEGIN ... EXCEPTION ... END, mais tu peux pas faire de commit à l'intérieur d'une fonction.

Procédure (CREATE PROCEDURE) — est apparue pour gérer les transactions direct sur le serveur (genre faire des commits partiels, rollback d'étapes, etc.).

• Dans les procédures (PL/pgSQL), tu peux utiliser COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT.
• IMPORTANT : tu peux pas utiliser ROLLBACK TO SAVEPOINT dans une procédure PL/pgSQL (ça va te sortir une erreur de syntaxe).
• Les procédures ne peuvent être appelées qu'avec la commande SQL CALL ..., pas via SELECT ni à l'intérieur d'autres fonctions.

Comment appeler une procédure/fonction depuis une autre ?

Les fonctions appellent « en toute transparence » d'autres fonctions juste en utilisant leur nom :

-- Exemple : fonction pour calculer une remise
CREATE OR REPLACE FUNCTION calculate_discount(order_total NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    IF order_total >= 100 THEN
        RETURN order_total * 0.1;
    ELSE
        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Fonction de traitement de commande qui appelle une autre fonction
CREATE OR REPLACE FUNCTION process_order(order_id INT, order_total NUMERIC)
RETURNS VOID AS $$
DECLARE
    discount NUMERIC;
BEGIN
    discount := calculate_discount(order_total);
    RAISE NOTICE 'Remise : %', discount;
    INSERT INTO orders_log (order_id, order_total, discount)
    VALUES (order_id, order_total, discount);
END;
$$ LANGUAGE plpgsql;

Tout s'exécute dans une seule transaction externe ! Une erreur dans n'importe quelle fonction va annuler tous les changements.

Appel de procédures et transactions imbriquées

Les procédures peuvent être appelées à l'intérieur d'autres procédures avec la commande CALL ... (dans PostgreSQL 17, tu peux faire une pile d'appels CALL proc1() -> CALL proc2()), mais les règles des transactions restent :

• Les commandes transactionnelles (COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT) sont dispo seulement au niveau supérieur des procédures.
• Si une procédure qui gère les transactions est appelée dans une transaction explicite déjà active (genre via un client sans autocommit), essayer de faire COMMIT/SAVEPOINT va planter.

Exemple de procédure avec gestion des transactions

-- Procédure avec commit étape par étape (marche seulement en mode autocommit de la connexion)
CREATE PROCEDURE process_batch_orders()
LANGUAGE plpgsql
AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT order_id, order_total FROM incoming_orders LOOP
        BEGIN
            -- On sauvegarde chaque lot de données séparément
            INSERT INTO orders (order_id, total) VALUES (rec.order_id, rec.order_total);
        EXCEPTION WHEN OTHERS THEN
            INSERT INTO order_errors(order_id, err_text) VALUES (rec.order_id, SQLERRM);
        END;
        COMMIT;
    END LOOP;
END;
$$;

-- Appel de la procédure
CALL process_batch_orders();

Après chaque COMMIT, une nouvelle transaction démarre automatiquement.

Rollback partiel (comportement type savepoint) en PL/pgSQL

PL/pgSQL (et dans les fonctions comme dans les procédures) ne supporte pas la commande ROLLBACK TO SAVEPOINT.

Pour annuler les changements d'une partie du code, on utilise juste le bloc BEGIN ... EXCEPTION ... END :

BEGIN
    -- quelques actions
    BEGIN
        -- opération potentiellement foireuse
    EXCEPTION WHEN OTHERS THEN
        -- tous les changements de ce bloc seront annulés
        RAISE NOTICE 'Rollback à l'intérieur du bloc !';
    END;
END;

Dans les procédures tu peux aussi utiliser SAVEPOINT et RELEASE SAVEPOINT, mais pas ROLLBACK TO SAVEPOINT. Leur but c'est de séparer les étapes, mais tu peux les gérer seulement via la gestion des exceptions.

Limitations et bonnes pratiques

1. Fonctions : opérations atomiques uniquement : tout ou rien. Si un truc foire — tout est annulé.
2. Procédures : seulement via CALL : et seulement avec une commande SQL séparée, pas depuis SELECT/fonctions. La gestion imbriquée des transactions est possible, mais faut bien respecter les limitations de PL/pgSQL.
3. Rollback partiel : seulement via EXCEPTION : c'est la méthode officielle recommandée et supportée pour le rollback partiel (genre SAVEPOINT).
4. Les procédures imbriquées peuvent gérer les transactions seulement si appelées via CALL : sinon, ça va planter.

Questions sur l'interaction logique et transactions

Est-ce que je peux faire une « transaction imbriquée » dans une fonction ?

Non. Tout s'exécute dans une seule transaction. Pour le rollback partiel — seulement les blocs EXCEPTION.

Est-ce que je peux faire COMMIT/ROLLBACK dans une fonction ou un bloc anonyme ?

Non, c'est une erreur de syntaxe. Utilise des procédures.

Est-ce que je peux appeler une procédure depuis une fonction ?

Non, seulement avec la commande CALL. Depuis une fonction/SELECT — c'est pas possible.

Est-ce que je peux faire ROLLBACK TO SAVEPOINT dans une procédure ?

Non ! En PL/pgSQL c'est interdit. Utilise les blocs EXCEPTION.