Quel mécanisme permet aux triggers **PostgreSQL** d'accéder à l'ensemble des lignes modifiées par une seule instruction **DML** sans se déclencher une fois par ligne ?

Réponse à la question

Les tables de transition, introduites dans PostgreSQL 10, fournissent cette capacité grâce à la clause REFERENCING dans les triggers au niveau de l'instruction. Ce mécanisme expose l'ensemble du jeu de résultats d'une modification sous forme de structures de table temporaires—NEW TABLE pour les opérations INSERT/UPDATE et OLD TABLE pour les opérations DELETE/UPDATE—permettant à la fonction trigger d'effectuer un traitement par ensembles. Au lieu d'itérer à travers les lignes individuellement, vous pouvez exécuter une seule instruction SQL qui opère sur toutes les lignes affectées simultanément.

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();

À l'intérieur de log_inventory_changes(), interroger updated_items retourne toutes les lignes touchées par la mise à jour déclenchante, permettant des opérations en masse efficaces comme INSERT INTO audit_log SELECT * FROM updated_items;.

Situation de la vie réelle

Une plateforme de commerce électronique a subi une forte dégradation des performances lors d'ajustements de prix en masse nocturnes affectant 100 000 enregistrements de produits. L'architecture existante utilisait un trigger AFTER au niveau des lignes pour écrire des entrées d'audit, ce qui entraînait 100 000 exécutions INSERT séquentielles dans la table price_history et épuisait les ressources du pool de connexions.

Solution 1 : Traitement par lots côté application L'équipe a envisagé de supprimer le trigger et de gérer les audits dans l'application Java en utilisant des inserts par lots JDBC. Bien que cela réduise la charge du processeur de la base de données, cela introduisait des risques de cohérence critiques : si l'application plantait au milieu d'un lot, les mises à jour de prix validées manqueraient définitivement d'enregistrements d'audit correspondants, violant la conformité SOX. De plus, cette approche nécessitait une gestion complexe des transactions distribuées entre le serveur d'application et PostgreSQL.

Solution 2 : File d'attente de messages asynchrone Une autre proposition impliquait d'écrire des identifiants de lignes dans un flux Redis pendant la mise à jour, puis de traiter les audits via un travailleur en arrière-plan. Cela découpait les chemins d'écriture mais sacrifiait la cohérence transactionnelle immédiate. Le travailleur asynchrone pouvait avoir du retard pendant les périodes de forte charge, créant des lacunes temporaires dans la traçabilité d'audit que les auditeurs réglementaires signaleraient. De plus, garantir des sémantiques exactement une fois entre PostgreSQL et Redis ajoutait une complexité d'infrastructure significative.

Solution 3 : Trigger au niveau de l'instruction avec des tables de transition L'approche sélectionnée a remplacé le trigger au niveau des lignes par un trigger au niveau de l'instruction utilisant REFERENCING NEW TABLE AS new_prices. La fonction trigger a effectué une seule opération par lots : INSERT INTO price_history SELECT product_id, old_price, new_price, NOW() FROM new_prices;. Cela a maintenu une conformité stricte ACID dans la même transaction tout en traitant toutes les modifications en une seule fois.

Résultat : Le temps de réalisation des lots nocturnes est passé de 45 secondes à 300 millisecondes. La génération de WAL (Write-Ahead Log) a chuté de 90 %, et le système a éliminé les pics de contention de verrouillage précédemment causés par une récursivité massive du trigger.

Ce que les candidats manquent souvent

Comment les tables de transition interagissent-elles avec les triggers BEFORE et INSTEAD OF sur les vues ?

Les tables de transition sont exclusivement disponibles dans les triggers AFTER sur les tables ordinaires. Les triggers BEFORE fonctionnent sur des lignes individuelles avant que le jeu de résultats final de l'instruction ne se matérialise, de sorte que l'ensemble des lignes modifiées n'existe pas encore. Les triggers INSTEAD OF sur les vues ne peuvent pas utiliser les tables de transition car ils définissent des chemins d'exécution alternatifs plutôt que d'observer le résultat réel des opérations DML sur les tables de base sous-jacentes.

La fonction du trigger peut-elle modifier des données dans la NEW TABLE ou la OLD TABLE pour altérer le résultat final ?

Non, les tables de transition sont des instantanés en lecture seule accessibles uniquement durant l'exécution du trigger. Elles représentent des vues immuables des lignes affectées par l'instruction et ne peuvent pas être modifiées à l'aide d'opérations de UPDATE, DELETE, ou INSERT. Pour changer des valeurs avant qu'elles ne soient validées, vous devez utiliser des triggers BEFORE au niveau des lignes qui manipulent directement la variable d'enregistrement NEW, ou effectuer des opérations DML séparées contre des tables permanentes.

Pourquoi les opérations TRUNCATE ne peuplent-elles pas la table de transition OLD TABLE ?

Bien que PostgreSQL prenne en charge les triggers sur TRUNCATE au niveau de l'instruction, cette commande DDL supprime toutes les lignes sans générer d'états de transition de lignes individuelles via le système MVCC. TRUNCATE contourne le mécanisme de versionnement de tuples utilisé pour construire des relations de transition, elle ne peut donc pas peupler OLD TABLE avec les lignes supprimées. L'audit des opérations TRUNCATE nécessite des mécanismes alternatifs tels que des event triggers ou des flux de décodage logique.