Debugging completo e ottimizzazione di una procedura multi-step

Le procedure multi-step sono i "coltellini svizzeri" dei database. Di solito includono la validazione degli input, l’esecuzione di modifiche (tipo update dei record, insert dei log), e a volte anche un po’ di analytics. Ma c’è un problema: più la procedura è complessa, più è facile che qualcosa vada storto. Un errore logico, una query lenta, un dettaglio mancato — e tutto può andare a rotoli.

Il debugging completo include questi aspetti:

1. Analisi degli input: i parametri sono settati giusti? I dati passati sono corretti?
2. Controllo dell’esecuzione dei passaggi chiave: tutti gli step della procedura vanno come dovrebbero?
3. Logging dei risultati intermedi: così sai cosa è successo prima che qualcosa si "rompa".
4. Ottimizzazione dei colli di bottiglia: miglioriamo i punti deboli che "rallentano" le query.

Definizione del problema: esempio di procedura multi-step

Per il nostro esempio pratico, immaginiamo di lavorare con il database di un e-commerce. Dobbiamo creare una procedura per gestire un ordine. Farà questi step:

1. Controllare la disponibilità del prodotto in magazzino.
2. Riservare il prodotto.
3. Aggiornare lo stato dell’ordine.
4. Scrivere gli eventi (tipo prenotazione riuscita o errore) nella tabella dei log.

Script della struttura del database:

-- Tabella prodotti
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    stock_quantity INTEGER NOT NULL
);

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

-- Tabella log degli ordini
CREATE TABLE order_logs (
    log_id SERIAL PRIMARY KEY,
    order_id INTEGER,
    log_message TEXT,
    log_time TIMESTAMP DEFAULT NOW()
);

Step 1: Creazione della procedura multi-step

Dai, creiamo la procedura base process_order. Prende l’id dell’ordine e fa tutti gli step di gestione.

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. Prendiamo l’id del prodotto e lo stato dell’ordine
    SELECT product_id INTO v_product_id
    FROM orders
    WHERE order_id = p_order_id;

    IF v_product_id IS NULL THEN
        RAISE EXCEPTION 'Ordine % non esiste o manca product_id', p_order_id;
    END IF;

    -- 2. Controlliamo la disponibilità in magazzino
    SELECT stock_quantity INTO v_stock_quantity
    FROM products
    WHERE product_id = v_product_id;

    IF v_stock_quantity <= 0 THEN
        RAISE EXCEPTION 'Prodotto % non disponibile in magazzino', v_product_id;
    END IF;

    -- 3. Aggiorniamo la quantità in magazzino
    UPDATE products
    SET stock_quantity = stock_quantity - 1
    WHERE product_id = v_product_id;

    -- 4. Aggiorniamo lo stato dell’ordine
    UPDATE orders
    SET order_status = 'Processato'
    WHERE order_id = p_order_id;

    -- 5. Scriviamo evento di successo nel log
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, 'Ordine processato con successo.');
END;
$$;

Step 2: Logging degli errori con RAISE NOTICE e RAISE EXCEPTION

Qui inizia la magia. Aggiungiamo logging degli step intermedi, così becchiamo subito gli errori e capiamo cosa succede a ogni passo.

Codice aggiornato con 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 'Processo ordine %...', p_order_id;

    -- 1. Prendiamo l’id del prodotto
    SELECT product_id INTO v_product_id
    FROM orders
    WHERE order_id = p_order_id;

    IF v_product_id IS NULL THEN
        RAISE EXCEPTION 'Ordine % non esiste o manca product_id', p_order_id;
    END IF;
    RAISE NOTICE 'Product ID per ordine %: %', p_order_id, v_product_id;

    -- 2. Controlliamo la disponibilità in magazzino
    SELECT stock_quantity INTO v_stock_quantity
    FROM products
    WHERE product_id = v_product_id;

    IF v_stock_quantity <= 0 THEN
        RAISE EXCEPTION 'Prodotto % non disponibile in magazzino', v_product_id;
    END IF;
    RAISE NOTICE 'Quantità in magazzino per prodotto %: %', v_product_id, v_stock_quantity;

    -- 3. Aggiorniamo la quantità in magazzino
    UPDATE products
    SET stock_quantity = stock_quantity - 1
    WHERE product_id = v_product_id;

    -- 4. Aggiorniamo lo stato dell’ordine
    UPDATE orders
    SET order_status = 'Processato'
    WHERE order_id = p_order_id;

    -- 5. Logghiamo il successo
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, 'Ordine processato con successo.');
    RAISE NOTICE 'Ordine % processato con successo.', p_order_id;

EXCEPTION WHEN OTHERS THEN
    -- Logghiamo l’errore
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, 'Errore: ' || SQLERRM);
    RAISE;
END;
$$;

Step 3: Ottimizzazione con gli indici

Se il database ha tanti prodotti o ordini, trovare le righe giuste può diventare un collo di bottiglia. Aggiungiamo indici per velocizzare le query durante la gestione:

-- Indice per velocizzare la ricerca nella tabella orders
CREATE INDEX idx_orders_product_id ON orders(product_id);

-- Indice per velocizzare la ricerca nella tabella products
CREATE INDEX idx_products_stock_quantity ON products(stock_quantity);

Step 4: Analisi delle performance con EXPLAIN ANALYZE

Ora vediamo quanto è veloce la nostra funzione. La chiamiamo con l’analisi delle performance:

EXPLAIN ANALYZE
SELECT process_order(1);

Il risultato ti mostra quanto tempo prende ogni step. Così puoi capire quale parte è più lenta — e ottimizzare ancora la procedura.

Step 5: Miglioramento usando le transazioni

Per essere più affidabili, puoi mettere tutta la procedura in una transazione. Così, se qualcosa va storto, tutto viene rollbackato.

BEGIN;

-- Chiamata della funzione
SELECT process_order(1);

-- Commit della transazione
COMMIT;

Nel codice della funzione puoi usare SAVEPOINT e ROLLBACK TO SAVEPOINT per gestire errori parziali.

Esercizio pratico: gestione di ordini in massa

Chiudiamo la lezione con un esempio di gestione di più ordini insieme. Creiamo una funzione che processa tutti gli ordini con stato In attesa:

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 = 'In attesa'
    LOOP
        BEGIN
            PERFORM process_order(v_order_id);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'Impossibile processare ordine %: %', v_order_id, SQLERRM;
        END;
    END LOOP;
END;
$$;

Quando chiami questa funzione, tutti gli ordini con stato In attesa verranno processati, e ogni errore sarà solo loggato.

Così abbiamo visto come fare debugging e ottimizzare procedure complesse, migliorando affidabilità, performance e leggibilità. Queste skill ti serviranno nei progetti veri, dove la qualità delle procedure fa la differenza per il successo dell’applicazione.