Problemi con gli XID in PostgreSQL

administrator

Qualche giorno fa uno Zabbix server che seguo ha iniziato a segnalare dei problemi con il DB. Nei log c’erano messaggi come questo:

"pg_query previous segment is only 5538 blocks"

Questo messaggio indica un potenziale problema con la gestione dei Large Object (LOB) in PostgreSQL. In sostanza, stai dicendo che quando PostgreSQL ha cercato di accedere a un segmento precedente di un Large Object, ha trovato solo 5538 blocchi, il che potrebbe essere inferiore a quanto si aspettava o a quanto necessario per l’operazione.

La documentazione suggerisce, come possibili cause:

  • Corruzione del Large Object: Il LOB potrebbe essere stato danneggiato a causa di un errore di scrittura, un problema hardware o un bug software.
  • Truncamento Inatteso: Il LOB potrebbe essere stato troncato inavvertitamente da un’operazione precedente.
  • Bug in PostgreSQL: Sebbene raro, potrebbe esserci un bug nella gestione dei Large Object nella versione di PostgreSQL che stai utilizzando.
  • Problemi di Concorrenza: Operazioni concorrenti sullo stesso LOB potrebbero aver portato a uno stato inconsistente.
  • Errori di Programmazione: L’applicazione che sta manipolando il LOB potrebbe contenere errori logici che portano a un accesso o una manipolazione errata.
  • Problemi con il File System Sottostante: Rari, ma problemi con il file system su cui risiede il database potrebbero causare corruzione dei dati.

L’operazione più indicata in questo caso è una verifica dell’integrità del DB, usando il comando VACUUM FULL ANALYZE. Questo può aiutare a identificare eventuali altre corruzioni. L’operazione va fatta con lo Zabbix Server spento e programmando una fineestra di manutenzione, dato che VACUUM FULL blocca la tabella e potrebbe richiedere molto tempo su database di grandi dimensioni.

SQL

VACUUM FULL ANALYZE;

Dato che la fortuna è una dea bendata, ma la sfiga ci vede benissimo (e, a volte prende anche la mira) il processo di consolidamento si bloccava con il messaggio di log in PostgreSQL “uncommitted xmin [NUMERO] from before xid cutoff [ALTRO NUMERO] needs to be frozen”

Per capire questo messaggio, dobbiamo spiegare alcuni concetti chiave di PostgreSQL:

  • MVCC (Multi-Version Concurrency Control): PostgreSQL utilizza MVCC. Quando una riga viene modificata o cancellata, la vecchia versione della riga non viene immediatamente eliminata. Invece, viene marcata come “morta”, e una nuova versione (o nessuna versione in caso di DELETE) viene creata. Queste tuple morte rimangono finché non vengono pulite da VACUUM.
  • ID Transazione (XID): Ogni transazione in PostgreSQL riceve un ID numerico univoco (XID). Le tuple (righe) sono associate a XID per tenere traccia di quando sono state create (xmin) e quando sono state rese “morte” (xmax).
  • Wraparound Problem: Gli XID sono numeri interi a 32 bit. Questo significa che, dopo circa 2 miliardi di transazioni, gli XID “fanno il giro” (wraparound) e ricominciano da zero. Se PostgreSQL raggiunge un XID molto alto e ha ancora tuple vive associate a XID molto bassi (che sembrerebbero “nel futuro” dopo il wraparound), il database potrebbe non sapere quali righe sono visibili e quali no. Questo può portare alla corruzione dei dati o alla perdita di dati.
  • Freezing: Per prevenire il wraparound, PostgreSQL ha un processo di “freezing”. Le tuple (righe) che sono rimaste invariate per un lungo periodo (cioè, il loro xmin è molto vecchio) vengono “congelate”. Questo significa che il loro xmin viene impostato a una speciale transazione “virtuale” (normalmente 2), che indica che la tupla è visibile a tutte le transazioni future, indipendentemente dal wraparound degli XID reali.
  • xid cutoff: È una soglia (un XID specifico) che PostgreSQL calcola. Se una tupla ha un xmin (ID della transazione di creazione) che è precedente a questo xid cutoff, e quella tupla non è ancora stata congelata, significa che è diventata “troppo vecchia” e deve essere congelata per prevenire problemi di wraparound.

Il messaggio “uncommitted xmin [NUMERO] from before xid cutoff [ALTRO NUMERO] needs to be frozen” indica che ci sono tuple vive (o comunque tuple che non sono state marcate come morte e pulite) che sono state create da transazioni con un XID molto basso (quindi molto vecchie), e che queste tuple non sono ancora state soggette al freezing automatico. Questo è un avviso che il database si sta avvicinando al limite in cui il wraparound XID potrebbe causare problemi, e che queste tuple devono essere congelate per tempo.

Ma perché accade questo?

  • autovacuum non efficiente: Il processo autovacuum non riesce a tenere il passo con la generazione di tuple morte, o è configurato in modo troppo conservativo, o le sue impostazioni non gli permettono di raggiungere le tabelle che necessitano di freezing.
  • Transazioni lunghe: Una transazione molto lunga può bloccare il autovacuum dal raggiungere l’XID più basso che deve essere congelato.
  • Tabelle con poche modifiche: Tabelle che vengono raramente aggiornate o eliminate potrebbero non raggiungere le soglie di autovacuum che attiverebbero un VACUUM di tipo freezing.

In questo casi la cosa migliore da fare è forzare un freeze della tabella (se la si conosce) oppure dell’intero DB.

Per una tabella specifica:

SQL

VACUUM FREEZE NOME_TABELLA;

Questo blocca solo la tabella specificata durante l’operazione.

Per l’intero database:

SQL

VACUUM FREEZE;

Questo è molto impattante e blocca l’intero database per un periodo di tempo variabile.

Se il problema persiste, controlla se ci sono transazioni molto lunghe che impediscono a autovacuum di procedere. Questa è una query di esempio per cercarle:

SQL

SELECT
pid, datname, usename, client_addr, application_name, backend_start, state, state_change, query_start, xact_start, query
FROM pg_stat_activity
WHERE state = 'active' AND xact_start IS NOT NULL ORDER BY xact_start;

Questa query permette di identificare le transazioni con xact_start molto vecchi, che si possono terminare (con pg_terminate_backend(pid)).

Si può monitorare  l’età delle transazioni nel tuo database utilizzando la vista pg_database e la colonna datfrozenxid:

SQL

SELECT datname, age(datfrozenxid) FROM pg_database;

Valori di age che si avvicinano a autovacuum_freeze_max_age (tipicamente 200 milioni), indicano possibili problemi.

Ma nel nostro caso i problemi non erano finiti. Infatti il FREEZE rigenerava lo stesso problema (uncommitted xmin [NUMERO] from before xid cutoff [ALTRO NUMERO] needs to be frozen), seguito da un altro errore (while scanning block [NUMERO] offset [ALTRO NUMERO] of relation)

Questa concatenazione di errori significa che il problema principale non è necessariamente una corruzione fisica del blocco (come un danneggiamento del disco o un header invalido), ma piuttosto una conseguenza diretta del problema di wraparound XID/freezing.

Sono due problemi legati:

  • Problema di Freezing/Wraparound XID (la causa radice): Il database ha transazioni (xmin) talmente vecchie che sono al di fuori della finestra di visibilità gestibile dal sistema di gestione degli XID di PostgreSQL. Il processo di VACUUM (o autovacuum) non è riuscito a “congelare” queste tuple in tempo. Questo è l’avviso iniziale “uncommitted xmin from before xid cutoff needs to be frozen”.
  • Impossibilità di Scansionare il Blocco (la conseguenza): Quando PostgreSQL cerca di scansionare un blocco (while scanning block offset of relation) e trova delle tuple con XID così vecchi e non congelati, si trova in una situazione ambigua. Non riesce più a determinare in modo affidabile se quelle tuple sono visibili o meno (perché il loro XID è potenzialmente “prima” del wraparound, ma potrebbe essere interpretato come “dopo” se il wraparound è già avvenuto e non sono state congelate). Questa ambiguità può portare PostgreSQL a non riuscire a processare correttamente quel blocco, generando un errore a quel punto.

In pratica, il database sta dicendo:

“Ho delle vecchie transazioni/righe che non sono state pulite/congelate. A causa di questo, quando ho cercato di leggere questo blocco, non sono riuscito a capire la visibilità delle righe contenute, quindi non posso processarlo correttamente.”

La vista pg_stat_activity ci può aiutare a fare un analisi del problema.

pg_stat_activity è una vista di sistema che fornisce informazioni in tempo reale sulle attività correnti di ogni processo backend (sessione) del server PostgreSQL. Ti mostra cosa sta facendo ogni connessione al database.

Si può interrogare pg_stat_activity come una qualsiasi altra tabella o vista in PostgreSQL. Il modo più comune è usare il client a riga di comando psql.

Le Colonne più Utili di pg_stat_activity:

Ecco alcune delle colonne chiave e cosa significano:

  • pid: L’ID del processo del backend. Questo è cruciale se hai bisogno di terminare una sessione problematica con pg_terminate_backend(pid).
    datname: Il nome del database a cui il backend è connesso.
  • usename: Il nome dell’utente che ha stabilito la connessione.
  • client_addr: L’indirizzo IP del client che ha stabilito la connessione.
  • application_name: Il nome dell’applicazione che si è connessa (utile per capire da dove provengono le connessioni, ad esempio ‘psql’, ‘DBeaver’, ‘phpPgAdmin’, ‘Spring Boot App’, ecc.). Molte
  • applicazioni lo impostano automaticamente.
  • backend_start: Il timestamp in cui il processo backend è stato avviato (quando la connessione è stata stabilita).
  • query_start: Il timestamp in cui la query attualmente in esecuzione (o l’ultima query se state è ‘idle’) è stata avviata.
  • xact_start: Il timestamp in cui la transazione corrente è stata avviata. Questa è la colonna cruciale per identificare transazioni di lunga durata. Se è NULL, non c’è una transazione attiva.
  • state: Lo stato attuale del backend:
  • active: Il backend sta eseguendo una query.
  • idle: Il backend è in attesa di un comando dal client.
  • idle in transaction: Il backend è all’interno di una transazione, ma non sta eseguendo una query (ha inviato un BEGIN ma non un COMMIT o ROLLBACK e sta aspettando il prossimo comando).
  • Questo è un stato che può causare problemi di blocco o wraparound se persistente.
  • idle in transaction (aborted): La transazione è stata annullata a causa di un errore, ma il client non ha ancora terminato la transazione.
  • waiting: Il backend è in attesa di un lock.
  • query: Il testo della query attualmente in esecuzione (o l’ultima query completata se state è ‘idle’).

Esempi di Query Utili con pg_stat_activity:

Visualizzare tutte le attività correnti:

SQL

SELECT pid, datname, usename, client_addr, application_name, state, query FROM pg_stat_activity;

Trovare query attive di lunga durata:

SQL

SELECT
pid, datname, usename, client_addr, application_name, query,now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

Questa query mostra quali query sono in esecuzione da più tempo.

Identificazione di transazioni “idle in transaction” (molto importanti per i problemi di wraparound/freezing e blocchi):

SQL

SELECT
pid,
datname,
usename,
client_addr,
application_name,
state, now() - xact_start AS transaction_duration,
query
FROM   pg_stat_activity
WHERE state LIKE 'idle in transaction%'
ORDER BY transaction_duration DESC;

Queste sono le sessioni che hanno iniziato una transazione ma non l’hanno ancora conclusa, potenzialmente bloccando autovacuum o altri processi.

Query che aspettano un lock:

SQL

SELECT
a.pid,
a.datname,
a.usename,
a.client_addr,
a.application_name,
a.query,
a.state,
l.mode,
l.locktype,
l.relation::regclass,
l.granted
FROM
pg_stat_activity a
JOIN
pg_locks l ON a.pid = l.pid
WHERE
l.granted = false;

Questa query aiuta a capire quali sessioni sono bloccate e su quale risorsa stanno aspettando.

Identificare le transazioni più vecchie (XID): Questa è la query più direttamente correlata al problema “uncommitted xmin from before xid cutoff needs to be frozen”:

SQL

SELECT
pid,
datname,
usename,
client_addr,
application_name,
backend_start,
query_start,
xact_start,
state,
query
FROM
pg_stat_activity
WHERE
xact_start IS NOT NULL -- Solo transazioni attive
ORDER BY
xact_start ASC; -- Ordina dalla più vecchia alla più recente

Sessioni idle in transaction con un xact_start molto lontano nel passato, quelle sono le principali candidate a causare problemi di wraparound.

Considerazioni Importanti:

Terminare le sessioni problematiche: Le sessioni che stanno causando problemi (es. idle in transaction per troppo tempo), si possono terminare usando pg_terminate_backend(pid);Attenzione: questo comando terminerà la sessione e farà un ROLLBACK di tutte le modifiche non commesse.
Frequenza di interrogazione: un’interrogazione troppo frequente di pg_stat_activity potrebbe causare dei rallentamenti del sistema.

A questo punto l’unico modo è identificare le righe collegate alle transazioni con XID vecchi che bloccano il freeze. Quest query permette di identificarle:

SQL

SELECT ctid, xmin, *
FROM [NOME TABELLA]
WHERE xmin = '[XID]'::xid;

Attenzione, ovviamente a cosa si cancella. Potrebbero essere righe con informazioni che tutto sommato si possono perdere oppure no. Un tool grafico come DBeaver o simili può essere molto utile per fre le query, visualizzare i risultati e procedere alla cancellazione

© 2025 Catfish Blog | Tema: Storto di CrestaProject WordPress Themes.