Analisi delle query e delle transazioni attuali

Partiamo dalle basi. PostgreSQL ti mette a disposizione una serie di strumenti pazzeschi per analizzare le query SQL e le transazioni. Per esempio, le funzioni integrate current_query() e txid_current() ti permettono di:

• Ottenere la query SQL attualmente in esecuzione.
• Scoprire in quale transazione viene eseguita la query.
• Loggare le operazioni SQL per analizzarle dopo.
• Tracciare problemi con le transazioni, tipo quando il tuo codice si aspetta una cosa ma ne succede un’altra.

Tutto questo ti salva la vita quando il debug standard non basta o quando vuoi analizzare il comportamento delle query “a posteriori”.

Panoramica delle funzioni integrate

Funzione current_query()

current_query() restituisce il testo della query SQL attualmente in esecuzione su quella connessione. “Come fa a saperlo?” — ti starai chiedendo. PostgreSQL tiene traccia dello stato di ogni connessione, e questa funzione ti fa sbirciare dietro le quinte.

Sintassi:

SELECT current_query();

Esempio di esecuzione:

-- Eseguiamo una query dentro una funzione
DO $$
BEGIN
    RAISE NOTICE 'Query attuale: %', current_query();
END;
$$;

-- Risultato:
-- NOTICE: Query attuale: DO $$ BEGIN RAISE NOTICE 'Query attuale: %', current_query(); END; $$;

Come vedi dall’esempio, current_query() ci mostra il testo della query in esecuzione. Questa info è super utile per analizzare procedure complesse: sai esattamente cosa sta girando in quel momento!

Funzione txid_current()

Quando si parla di transazioni, la funzione txid_current() è una bomba. Ti restituisce l’identificatore unico della transazione attuale. È utilissima se vuoi tracciare la sequenza delle operazioni dentro una singola transazione.

Sintassi:

SELECT txid_current();

Esempio di esecuzione:

BEGIN;

-- Ottieni l’ID della transazione attuale
SELECT txid_current();

-- Output:
-- 564 (per esempio, l’identificatore)

-- Chiudi la transazione
COMMIT;

Questi ID delle transazioni possono essere usati per incrociare i log, analizzare la sequenza delle azioni e persino per il debug di sistemi multiutente.

Esempi di utilizzo in casi reali

1. Loggare la query attuale durante l’esecuzione.

A volte una procedura o funzione contiene un sacco di query SQL. Per capire dove qualcosa è andato storto, puoi loggare la query SQL attuale. Tipo così:

DO $$
DECLARE
    current_txn_id BIGINT;
BEGIN
    current_txn_id := txid_current();
    RAISE NOTICE 'ID della transazione attuale: %', current_txn_id;

    RAISE NOTICE 'Query attuale: %', current_query();

    -- Qui potresti avere altre operazioni tue
END;
$$;

Questo codice stampa in console l’ID della transazione e il testo della query attuale. Così sai esattamente cosa sta succedendo in quel momento.

2. Analisi delle transazioni per trovare problemi.

Immagina uno scenario in cui gli utenti si lamentano della perdita di dati durante un aggiornamento massivo. Hai diverse procedure, ognuna lanciata dentro una transazione. Come fai a capire chi è il colpevole? Ecco un esempio:

BEGIN;

-- Logghiamo la transazione
DO $$
BEGIN
    RAISE NOTICE 'ID della transazione attuale: %', txid_current();
END;
$$;

-- Esegui la query “problematica”
UPDATE orders
SET status = 'processed'
WHERE id IN (SELECT order_id FROM pending_orders);

COMMIT;

Se gli aggiornamenti non vanno a buon fine, vedi subito l’ID della transazione a cui si riferiscono i tuoi cambiamenti. Questo non solo rende più facile trovare l’errore, ma ti aiuta anche a capire se ci sono stati conflitti tra transazioni.

3. Log delle query per analisi storica.

A volte non devi solo risolvere il problema attuale, ma anche ricordarti quali query SQL sono state eseguite. Per esempio, puoi creare una tabella per il log:

CREATE TABLE query_log (
    log_time TIMESTAMP DEFAULT NOW(),
    query_text TEXT,
    txn_id BIGINT
);

Ecco come puoi salvare le query usando current_query() e txid_current():

DO $$
BEGIN
    INSERT INTO query_log (query_text, txn_id)
    VALUES (current_query(), txid_current());
END;
$$;

Ora nella tabella query_log hai info su ogni query eseguita e sulla transazione in cui è stata fatta. È uno strumento preziosissimo per analizzare il lavoro del database.

Casi pratici di utilizzo

Esempio 1: audit delle transazioni

Immagina di dover analizzare le operazioni in un sistema multiutente. Loggare l’ID della transazione (txid_current) ti permette di raggruppare le azioni per ogni transazione.

DO $$
DECLARE
    txn_id BIGINT;
BEGIN
    txn_id := txid_current();
    RAISE NOTICE 'Transazione iniziata con ID: %', txn_id;

    -- Qualche operazione
    UPDATE users SET last_login = NOW() WHERE id = 123;

    RAISE NOTICE 'Query attuale: %', current_query();
END;
$$;

Esempio 2: debug più semplice delle procedure

Hai chiamato una procedura complicata e qualcosa è andato storto. Puoi inserire il log di current_query() in vari punti della funzione per vedere quale query è stata eseguita:

CREATE OR REPLACE FUNCTION debugged_function() RETURNS VOID AS $$
BEGIN
    RAISE NOTICE 'Query attuale prima dell’update: %', current_query();
    UPDATE data_table SET field = 'debugging';
    RAISE NOTICE 'Query attuale dopo l’update: %', current_query();
END;
$$ LANGUAGE plpgsql;

Quando la funzione finisce, ricevi due notifiche con le query SQL corrispondenti.

Consigli pratici

1. Usa current_query() per loggare le query dentro sistemi multiutente, così capisci quali azioni vengono fatte.
2. txid_current() è perfetta per analizzare l’origine dei cambiamenti: in quale punto della tua transazione sono stati aggiunti o modificati i dati.
3. Ricordati di togliere il log quando non ti serve più. Le notifiche continue con RAISE NOTICE possono rallentare l’esecuzione delle tue funzioni.

Queste funzioni integrate sono il tuo “microscopio” per esplorare i dettagli più piccoli di come lavora il database. Ti aiutano a beccare errori, migliorare le performance e capire cosa succede nei sistemi complessi. Da qualche parte, dentro PostgreSQL, il tuo database è già pronto a svelarti i suoi segreti — devi solo imparare a leggerli.