La pseudo-tabella EXCLUDED in PostgreSQL rappresenta la riga proposta per l'inserimento durante un'operazione ON CONFLICT. Storicamente, gli sviluppatori che migrano da ambienti MySQL o Oracle spesso presumono che i confronti di uguaglianza diretta (=) siano sufficienti per rilevare le modifiche di valore all'interno dei modelli di upsert. Tuttavia, la logica a tre valori standard di SQL stabilisce che NULL rappresenta uno stato sconosciuto, il che significa che NULL = NULL viene valutato come NULL (sconosciuto), non TRUE.
Questo crea un problema critico quando la clausola di risoluzione dei conflitti tenta di ottimizzare gli aggiornamenti aggiungendo una clausola WHERE come WHERE EXCLUDED.phone != users.phone. Se sia la riga esistente che la riga proposta contengono NULL per la colonna del telefono, il confronto restituisce NULL, il che fa fallire il predicato WHERE. Di conseguenza, il database salta l'aggiornamento anche se i valori potrebbero essere effettivamente diversi nel contesto della logica aziendale, o non può distinguere tra un NULL nei nuovi dati e un NULL nei vecchi dati.
La soluzione implica l'utilizzo dell'operatore IS DISTINCT FROM, che tratta NULL come valore comparabile. Strutturando la clausola di aggiornamento con WHERE EXCLUDED.column IS DISTINCT FROM table.column, il confronto restituisce FALSE quando entrambi i valori sono NULL (indicando nessuna modifica) e TRUE quando uno è NULL e l'altro non lo è. Questo garantisce un comportamento deterministico evitando scritture non necessarie.
INSERT INTO patient_records (clinic_id, external_id, phone, updated_at) VALUES (101, 'P-2024-001', NULL, NOW()) ON CONFLICT (clinic_id, external_id) DO UPDATE SET phone = COALESCE(EXCLUDED.phone, patient_records.phone), updated_at = EXCLUDED.updated_at WHERE EXCLUDED.phone IS DISTINCT FROM patient_records.phone;
Una rete ospedaliera aveva bisogno di sincronizzare quotidianamente i dati dei pazienti provenienti da 50 cliniche esterne in un magazzino dati centrale PostgreSQL. Ogni clinica esportava file CSV dove i numeri di telefono dei pazienti mancanti apparivano come stringhe vuote, che il comando COPY convertiva in NULL durante l'ingestione. Lo script ETL Python esistente utilizzava SQLAlchemy per eseguire bulk upserts con ON CONFLICT (clinic_id, external_id) DO UPDATE SET phone = EXCLUDED.phone.
Il problema è emerso quando il personale della clinica ha segnalato che i numeri di telefono validi inseriti direttamente nel sistema centrale scomparivano misteriosamente dopo la sincronizzazione notturna. Un'indagine ha rivelato che quando il feed esterno inviava NULL (indicando un telefono sconosciuto), sovrascriveva i numeri validi esistenti perché la clausola SET veniva eseguita incondizionatamente. Aggiungere un filtro ingenuo WHERE EXCLUDED.phone != patient_records.phone è fallito perché quando entrambi erano NULL, il confronto restituiva NULL (sconosciuto), causando la corretta omissione dell'aggiornamento, e quando il nuovo valore era NULL e il vecchio non lo era, la logica si comportava in modo incoerente tra le diverse versioni minori di PostgreSQL.
Tre soluzioni sono state valutate.
Il primo approccio utilizzava esclusivamente COALESCE nella clausola SET: SET phone = COALESCE(EXCLUDED.phone, patient_records.phone). Questo impediva di sovrascrivere con NULL ma forzava un aggiornamento a ogni conflitto, attivando costosi ricostruzioni dell'indice B-Tree sulla colonna phone e attivando i trigger di audit che registravano modifiche "no-op" come modifiche legittime. Ciò aumentava il traffico WAL (Write-Ahead Log) del 300%, minacciando il ritardo nella replicazione e saturando l'I/O del disco.
La seconda soluzione tentava di gestire esplicitamente la logica booleana per trattare NULL: WHERE (EXCLUDED.phone != patient_records.phone) OR (EXCLUDED.phone IS NULL AND patient_records.phone IS NOT NULL). Anche se logicamente corretta, questo modello verboso richiedeva una manutenzione attenta su 15 colonne nullable e confondeva l'ottimizzatore di query. Il pianificatore abbandonava le scansioni degli indici a favore delle scansioni sequenziali su una tabella di 20 milioni di righe, causando il superamento della finestra di manutenzione di sei ore dell'attività ETL.
La terza soluzione implementava IS DISTINCT FROM per tutte le colonne nullable nella clausola WHERE. Questo forniva un predicato conciso e sargable che identificava correttamente le reali modifiche dei dati inclusi i passaggi NULL. Consentiva aggiornamenti solo quando necessario, eliminando le esecuzioni di trigger superflue e la generazione di WAL mantenendo piani di query coerenti.
Il team ha scelto la terza soluzione per i campi di contatto critici e la prima soluzione per i metadati non critici dove la protezione contro la sovrascrittura contava di più delle prestazioni. Il risultato è stato drammatico: la durata del lavoro di sincronizzazione è passata da 45 minuti a 12 minuti, il ritardo nella replicazione si è stabilizzato sotto i cinque secondi e gli incidenti di "numero di telefono scomparso" sono cessati completamente entro la prima settimana di distribuzione.
Perché WHERE EXCLUDED.column != table.column salta righe quando entrambi i valori sono NULL, e come interagisce questo con il meccanismo di aggiornamento di PostgreSQL?
Molti candidati presumono che se due NULL non sono uguali, il confronto dovrebbe restituire TRUE e consentire l'aggiornamento. Tuttavia, SQL utilizza una logica a tre valori: NULL rappresenta un valore sconosciuto. Qualsiasi confronto con NULL (inclusi NULL = NULL o NULL != NULL) restituisce NULL (sconosciuto), non un booleano TRUE o FALSE. Nella clausola WHERE di PostgreSQL, solo le righe che valutano TRUE procedono; NULL è trattato come FALSE. Pertanto, quando si confrontano due numeri di telefono NULL, il risultato è NULL, l'aggiornamento viene saltato e il sistema presume erroneamente che non sia necessaria alcuna modifica. IS DISTINCT FROM restituisce FALSE per NULL vs NULL, indicando correttamente che sono identici e saltando l'aggiornamento solo quando appropriato, mentre restituisce TRUE quando un valore è NULL e l'altro non lo è.
Qual è la differenza tra l'utilizzo di COALESCE(EXCLUDED.column, table.column) nella clausola SET rispetto all'utilizzo di WHERE EXCLUDED.column IS DISTINCT FROM table.column, in particolare riguardo all'esecuzione di trigger e alla versioning delle righe?
Utilizzare COALESCE nella clausola SET scrive incondizionatamente un valore nella riga (sia i nuovi dati che i dati vecchi preservati). Questa operazione genera una nuova versione di riga (CTID), scrive nel WAL e attiva tutti i trigger BEFORE e AFTER associati alla tabella, anche se il valore finale rimane identico allo stato precedente. Questo crea "rumore" nelle tabelle di audit e aumenta il carico di replicazione. Al contrario, la clausola WHERE con IS DISTINCT FROM impedisce del tutto la modifica della riga se non è avvenuta alcuna modifica effettiva. Non viene creata una nuova versione di tupla, i trigger non vengono attivati e la generazione di WAL viene evitata. Questa distinzione è critica per sistemi ad alto throughput con registrazione di audit o cascata di chiavi esterne, dove gli aggiornamenti "no-op" creano un sovraccarico significativo.