Esempi pratici di lavoro con transazioni annidate

Oggi il nostro obiettivo è sviluppare una funzione che:

1. Controlla il saldo del cliente. Prima di scalare l'importo, bisogna verificare che ci siano abbastanza fondi.
2. Scala i fondi dal saldo. Se il saldo è sufficiente, si effettua la detrazione.
3. Logga le operazioni riuscite e non riuscite. Tutte le azioni vengono registrate nella tabella dei log per analisi future.

Non è solo una noiosa funzione di sottrazione. Qui useremo transazioni annidate per fare rollback delle modifiche se qualcosa va storto (tipo fondi insufficienti o errore nel log). Scopriremo l'utilità dei savepoint (SAVEPOINT) e impareremo a rendere le procedure resistenti agli errori.

Creiamo le tabelle iniziali

Prima di creare la funzione, prepariamo il database. Ci servono tre tabelle:

1. clients — per salvare i dati dei clienti e i loro saldi.
2. payments — per registrare le transazioni riuscite.
3. logs — per salvare info su tutti i tentativi di pagamento (riusciti e falliti).

-- Tabella dei clienti
CREATE TABLE clients (
    client_id SERIAL PRIMARY KEY,
    full_name TEXT NOT NULL,
    balance NUMERIC(10, 2) NOT NULL DEFAULT 0
);

-- Tabella dei pagamenti riusciti
CREATE TABLE payments (
    payment_id SERIAL PRIMARY KEY,
    client_id INT NOT NULL REFERENCES clients(client_id),
    amount NUMERIC(10, 2) NOT NULL,
    payment_date TIMESTAMP DEFAULT NOW()
);

-- Tabella dei log
CREATE TABLE logs (
    log_id SERIAL PRIMARY KEY,
    client_id INT NOT NULL REFERENCES clients(client_id),
    message TEXT NOT NULL,
    log_date TIMESTAMP DEFAULT NOW()
);

Popoliamo la tabella clients con dati di test

INSERT INTO clients (full_name, balance)
VALUES 
    ('Otto Song', 100.00),
    ('Maria Chi', 50.00),
    ('Anna Vel', 0.00);

Ora abbiamo tre clienti: Otto ha 100 sul conto, Maria — 50, e Anna — 0.

Implementazione della business logic: PROCEDURE vs FUNZIONE

In breve:

• Per operazioni business "tutto o niente" basta una funzione.
• Per gestire le transazioni a step, commit parziali, rollback, log degli errori — usa una procedura (CREATE PROCEDURE).

Perché non una funzione? In PostgreSQL 17 dentro una funzione NON puoi usare né COMMIT, né SAVEPOINT, né ROLLBACK. Tutte le modifiche sono atomiche nella transazione esterna.

Solo la procedura (CREATE PROCEDURE ... LANGUAGE plpgsql) permette di usare SAVEPOINT, COMMIT, ROLLBACK — ma con limiti importanti:

• Dentro una procedura sono permessi SAVEPOINT, COMMIT, RELEASE SAVEPOINT.
• ROLLBACK TO SAVEPOINT è vietato in PL/pgSQL (dà errore), invece si usano i blocchi BEGIN ... EXCEPTION ... END, che fanno un "savepoint virtuale".

Tecnica base per rollback parziale:

BEGIN
    -- il tuo codice
EXCEPTION
    WHEN OTHERS THEN
        -- Questo blocco, in caso di errore, fa rollback di TUTTE le modifiche dentro il blocco!
        -- Puoi lasciare info nel log:
        INSERT INTO logs (...) VALUES (...);
END;

Creiamo una procedura di pagamento con rollback parziale e logging

CREATE OR REPLACE PROCEDURE process_payment(
    in_client_id INT,
    in_payment_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    current_balance NUMERIC;
BEGIN
    -- Otteniamo il saldo del cliente
    SELECT balance INTO current_balance
    FROM clients
    WHERE client_id = in_client_id;

    IF NOT FOUND THEN
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, 'Cliente non trovato, operazione rifiutata');
        RAISE EXCEPTION 'Cliente con ID % non trovato', in_client_id;
    END IF;

    -- Controlliamo se ci sono abbastanza fondi
    IF current_balance < in_payment_amount THEN
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, 'Fondi insufficienti per scalare ' || in_payment_amount || ' euro.');
        -- Terminiamo la procedura
        RETURN;
    END IF;

    -- Blocco per modifiche atomiche; in caso di errore — rollback (savepoint virtuale)
    BEGIN
        -- Scalare il saldo
        UPDATE clients
        SET balance = balance - in_payment_amount
        WHERE client_id = in_client_id;

        -- Aggiungiamo la registrazione del pagamento riuscito
        INSERT INTO payments (client_id, amount)
        VALUES (in_client_id, in_payment_amount);

        -- Logghiamo il successo
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, 'Scalamento riuscito di ' || in_payment_amount || ' euro.');

    EXCEPTION
        WHEN OTHERS THEN
            -- Tutte le modifiche in questo blocco vengono annullate
            INSERT INTO logs (client_id, message)
            VALUES (in_client_id, 'Errore durante il pagamento: ' || SQLERRM);
            -- (non serve ROLLBACK TO SAVEPOINT esplicito — è vietato e non necessario)
    END;
END;
$$;

In breve cosa succede:

• Se i fondi sono pochi/cliente non esiste — logghiamo e usciamo.
• Tutto il codice critico è dentro il blocco BEGIN ... EXCEPTION ... END.
• Se c'è qualsiasi errore dentro questo blocco — tutte le modifiche vengono annullate; scriviamo l'errore nei log.
• Nessun uso diretto di SAVEPOINT e ROLLBACK TO SAVEPOINT — è giusto così, in PL/pgSQL funziona solo tramite blocchi EXCEPTION.

Chiamare la procedura

Importante: la procedura si chiama con CALL ..., e la connessione al database deve essere in modalità autocommit o fuori da una transazione grande esplicita!

CALL process_payment(1, 30.00);   -- Pagamento riuscito
CALL process_payment(2, 100.00);  -- Fondi insufficienti
CALL process_payment(999, 50.00); -- Cliente non esiste

Verifica dei risultati

• Il saldo del cliente cambia solo se il pagamento va a buon fine.
• Tabella payments — registrazione solo se la detrazione è riuscita.
• logs — storia di tutti i tentativi (e degli errori).

SELECT * FROM clients;
SELECT * FROM payments;
SELECT * FROM logs;

Applicazione reale

Le procedure per gestire le transazioni sono una delle parti centrali nei sistemi fintech, e-commerce e anche nelle piattaforme di gaming. Immagina un e-shop che deve gestire il saldo dei buoni regalo e scalarli quando si fanno acquisti, oppure un sistema bancario con migliaia di operazioni al secondo.

Queste conoscenze ti saranno utili nella pratica, ti aiuteranno a proteggere i dati dei tuoi clienti e ad evitare errori catastrofici nella gestione dei pagamenti.