Le tabelle di transizione, introdotte in PostgreSQL 10, forniscono questa capacità attraverso la clausola REFERENCING nei trigger a livello di istruzione. Questo meccanismo espone l'intero risultato di una modifica come strutture di tabelle temporanee—NEW TABLE per operazioni di INSERT/UPDATE e OLD TABLE per operazioni di DELETE/UPDATE—consentendo alla funzione del trigger di eseguire elaborazioni basate su insiemi. Invece di iterare attraverso le righe singolarmente, è possibile eseguire un'unica istruzione SQL che opera su tutte le righe interessate simultaneamente.
CREATE TRIGGER bulk_audit_trigger AFTER UPDATE ON inventory REFERENCING NEW TABLE AS updated_items OLD TABLE AS previous_items FOR EACH STATEMENT EXECUTE FUNCTION log_inventory_changes();
All'interno di log_inventory_changes(), interrogare updated_items restituisce tutte le righe toccate dall'UPDATE scatenante, consentendo operazioni di massa efficienti come INSERT INTO audit_log SELECT * FROM updated_items;.
Una piattaforma di e-commerce ha subito una grave degradazione delle prestazioni durante gli aggiustamenti notturni dei prezzi in massa che interessavano 100.000 record di prodotto. L'architettura esistente utilizzava un trigger di tipo AFTER a livello di riga per scrivere voci di audit, causando 100.000 esecuzioni sequenziali di INSERT nella tabella price_history e esaurendo le risorse del pool di connessioni.
Soluzione 1: Elaborazione batch lato applicazione Il team ha preso in considerazione l'idea di rimuovere il trigger e gestire le voci di audit all'interno dell'applicazione Java utilizzando inserimenti batch JDBC. Sebbene questo avrebbe ridotto il carico sulla CPU del database, ha introdotto rischi critici di coerenza: se l'applicazione si fosse bloccata durante il batch, gli aggiornamenti di prezzo già impegnati avrebbero permanentemente mancato le relative registrazioni di audit, violando la conformità SOX. Inoltre, questo approccio richiedeva una complessa gestione delle transazioni distribuite tra il server dell'app e PostgreSQL.
Soluzione 2: Coda di messaggi asincrona Un'altra proposta prevedeva di scrivere gli identificatori di riga in uno stream Redis durante l'aggiornamento, quindi elaborare le audit tramite un worker in background. Questo staccava i percorsi di scrittura ma sacrificava la coerenza transazionale immediata. Il worker asincrono potrebbe avere ritardi durante carichi elevati, creando gap temporanei nella traccia di audit che sarebbero stati contrassegnati dagli auditor regolatori. Inoltre, garantire semantiche esattamente una volta tra PostgreSQL e Redis ha aggiunto una notevole complessità infrastrutturale.
Soluzione 3: Trigger a livello di istruzione con tabelle di transizione
L'approccio selezionato ha sostituito il trigger a riga con un trigger a livello di istruzione utilizzando REFERENCING NEW TABLE AS new_prices. La funzione del trigger ha effettuato un'unica operazione basata su insiemi: INSERT INTO price_history SELECT product_id, old_price, new_price, NOW() FROM new_prices;. Questo ha mantenuto la rigorosa conformità ACID all'interno della stessa transazione, elaborando tutte le modifiche in un unico passaggio.
Risultato: Il tempo di completamento del batch notturno è sceso da 45 secondi a 300 millisecondi. La generazione di WAL (Write-Ahead Log) è diminuita del 90% e il sistema ha eliminato i picchi di contesa dei blocchi precedentemente causati da una massiccia ricorsione del trigger.
Come interagiscono le tabelle di transizione con i trigger BEFORE e i trigger INSTEAD OF sulle viste?
Le tabelle di transizione sono esclusivamente disponibili nei trigger AFTER su tabelle ordinarie. I trigger BEFORE operano su righe singole prima che l'insieme finale dei risultati dell'istruzione si materializzi, quindi l'intera raccolta di righe modificate non esiste ancora. I trigger INSTEAD OF sulle viste non possono utilizzare tabelle di transizione perché definiscono percorsi di esecuzione alternativi piuttosto che osservare il risultato effettivo delle operazioni DML sulle tabelle di base sottostanti.
La funzione del trigger può modificare i dati all'interno della NEW TABLE o OLD TABLE per alterare il risultato finale?
No, le tabelle di transizione sono snapshot di sola lettura accessibili solo durante l'esecuzione del trigger. Rappresentano viste immutabili delle righe interessate dall'istruzione e non possono essere modificate utilizzando operazioni di UPDATE, DELETE o INSERT. Per cambiare i valori prima che vengano impegnati, è necessario utilizzare trigger BEFORE a livello di riga che manipolino direttamente la variabile record NEW, o effettuare operazioni DML separate contro tabelle permanenti.
Perché le operazioni TRUNCATE non popolano la tabella di transizione OLD TABLE?
Sebbene PostgreSQL supporti i trigger su TRUNCATE a livello di istruzione, questo comando DDL rimuove tutte le righe senza generare stati di transizione di riga individuali attraverso il sistema MVCC. TRUNCATE bypassa il meccanismo di versioning delle tuple utilizzato per costruire le relazioni di transizione, quindi non può popolare OLD TABLE con le righe eliminate. L'audit delle operazioni TRUNCATE richiede meccanismi alternativi come i trigger di evento o gli stream di decodifica logica.