Chiamata di procedure e funzioni all'interno delle transazioni

Nelle moderne basi di dati la business logic spesso viene implementata lato server — usando procedure e funzioni. Lavorando con PostgreSQL è importante capire la differenza tra funzioni e procedure (soprattutto da quando sono arrivate le procedure dalla versione 11+) e come interagiscono con le transazioni.

Qui sotto ti racconto i fatti principali sulla meccanica delle transazioni, le chiamate annidate e il rollback parziale delle modifiche in procedure/funzioni PostgreSQL 17, secondo la documentazione ufficiale e i limiti attuali.

Concetti chiave: funzioni vs procedure

Funzione (CREATE FUNCTION) — viene sempre eseguita all'interno di una singola transazione esterna; dentro le funzioni non puoi usare comandi transazionali espliciti (BEGIN, COMMIT, ROLLBACK, SAVEPOINT).

• Tutte le modifiche vengono confermate o annullate solo a livello della transazione esterna.
• Per il "rollback parziale" dentro le funzioni si usa BEGIN ... EXCEPTION ... END, ma non puoi fare commit dentro la funzione.

Procedura (CREATE PROCEDURE) — è stata introdotta per gestire le transazioni direttamente sul server (tipo fare commit parziali, rollback di step, ecc).

• Nelle procedure (PL/pgSQL) puoi usare COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT.
• IMPORTANTE: non puoi usare ROLLBACK TO SAVEPOINT in una procedura PL/pgSQL (ti darà errore di sintassi).
• Le procedure si possono chiamare solo con il comando SQL CALL ..., non tramite SELECT o dentro altre funzioni.

Come chiamare una procedura/funzione da un'altra?

Le funzioni chiamano "in trasparenza" altre funzioni semplicemente usando il loro nome:

-- Esempio: funzione per calcolare lo sconto
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;

-- Funzione di gestione ordine che chiama un'altra funzione
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 'Sconto: %', discount;
    INSERT INTO orders_log (order_id, order_total, discount)
    VALUES (order_id, order_total, discount);
END;
$$ LANGUAGE plpgsql;

Tutto viene eseguito all'interno di una singola transazione esterna! Un errore in qualsiasi funzione causa il rollback di tutte le modifiche.

Chiamata di procedure e transazioni annidate

Le procedure si possono chiamare dentro altre procedure con il comando CALL ... (in PostgreSQL 17 è permesso uno stack di chiamate tipo CALL proc1() -> CALL proc2()), ma le regole delle transazioni restano le stesse:

• I comandi transazionali (COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT) sono disponibili solo al livello più alto delle procedure.
• Se una procedura con gestione delle transazioni viene chiamata dentro una transazione esplicita già attiva (tipo da un client senza autocommit), provare a fare COMMIT/SAVEPOINT causerà errore.

Esempio di procedura con gestione delle transazioni

-- Procedura con commit step-by-step (funziona solo in modalità autocommit della connessione)
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
            -- Salviamo ogni batch di dati separatamente
            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;
$$;

-- Chiamata della procedura
CALL process_batch_orders();

Dopo ogni COMMIT parte automaticamente una nuova transazione.

Rollback parziale (comportamento tipo savepoint) in PL/pgSQL

PL/pgSQL (sia nelle funzioni che nelle procedure) non supporta il comando ROLLBACK TO SAVEPOINT.

Per annullare le modifiche di una parte di codice si usa solo il blocco BEGIN ... EXCEPTION ... END:

BEGIN
    -- qualche azione
    BEGIN
        -- operazione potenzialmente con errore
    EXCEPTION WHEN OTHERS THEN
        -- tutte le modifiche di questo blocco verranno annullate
        RAISE NOTICE 'Rollback dentro il blocco!';
    END;
END;

Nelle procedure puoi anche usare SAVEPOINT e RELEASE SAVEPOINT, ma non ROLLBACK TO SAVEPOINT. Servono solo a separare gli step, ma puoi gestirli solo tramite la gestione delle eccezioni.

Limitazioni e best practice

1. Funzioni — solo operazioni atomiche: tutto o niente. Se qualcosa va storto — tutte le modifiche vengono annullate.
2. Procedure — solo tramite CALL: e solo con comando SQL separato, non da SELECT/funzioni. La gestione annidata delle transazioni è possibile, ma solo rispettando i limiti di PL/pgSQL.
3. Rollback parziale — solo tramite EXCEPTION: è il modo ufficialmente raccomandato e supportato per il rollback parziale (tipo SAVEPOINT).
4. Le procedure annidate possono gestire le transazioni solo se chiamate tramite CALL: altrimenti avrai un errore.

Domande su logica e transazioni

Posso fare una "transazione annidata" dentro una funzione?

No. Tutto viene eseguito in una sola transazione. Per rollback parziale — solo blocchi EXCEPTION.

Posso fare COMMIT/ROLLBACK dentro una funzione o un blocco anonimo?

No, è un errore di sintassi. Usa le procedure.

Posso chiamare una procedura da una funzione?

No, solo con il comando CALL. Da funzione/SELECT — non si può.

Posso fare ROLLBACK TO SAVEPOINT in una procedura?

No! In PL/pgSQL è vietato. Usa i blocchi EXCEPTION.