La pseudo-table EXCLUDED dans PostgreSQL représente la ligne proposée pour insertion lors d'une opération ON CONFLICT. Historiquement, les développeurs migrés depuis des environnements MySQL ou Oracle supposent souvent que les comparaisons d'égalité directe (=) suffisent pour détecter les changements de valeur dans les modèles upsert. Cependant, la logique à trois valeurs de la norme SQL dicte que NULL représente un état inconnu, ce qui signifie que NULL = NULL s'évalue à NULL (inconnu), et non à TRUE.
Cela crée un problème critique lorsque la clause de résolution de conflit tente d'optimiser les mises à jour en ajoutant une clause WHERE telle que WHERE EXCLUDED.phone != users.phone. Si à la fois la ligne existante et la ligne proposée contiennent NULL pour la colonne téléphone, la comparaison retourne NULL, ce qui échoue le prédicat WHERE. Par conséquent, la base de données ignore la mise à jour même si les valeurs pourraient être véritablement différentes dans le contexte de la logique métier, ou elle ne peut pas distinguer entre un NULL dans les nouvelles données et un NULL dans les anciennes données.
La solution consiste à utiliser l'opérateur IS DISTINCT FROM, qui considère NULL comme une valeur comparable. En structurant la clause de mise à jour avec WHERE EXCLUDED.column IS DISTINCT FROM table.column, la comparaison retourne FALSE lorsque les deux valeurs sont NULL (indiquant aucun changement) et TRUE lorsque l'une est NULL et l'autre ne l'est pas. Cela garantit un comportement déterministe tout en empêchant des écritures inutiles.
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;
Un réseau hospitalier avait besoin de synchroniser quotidiennement les données d'admission de patients de 50 cliniques externes dans un entrepôt de données central PostgreSQL. Chaque clinique exportait des fichiers CSV où les numéros de téléphone des patients manquants apparaissaient comme des chaînes vides, que la commande COPY convertissait en NULL lors de l'ingestion. Le script ETL Python existant utilisait SQLAlchemy pour exécuter des upserts en masse avec ON CONFLICT (clinic_id, external_id) DO UPDATE SET phone = EXCLUDED.phone.
Le problème est survenu lorsque le personnel de la clinique a signalé que des numéros de téléphone valides saisis directement dans le système central disparaissaient mystérieusement après la synchronisation nocturne. Une enquête a révélé que lorsque le flux externe envoyait NULL (indiquant un téléphone inconnu), cela remplaçait les numéros valides existants car la clause SET s'exécutait sans condition. Ajouter un filtre naïf WHERE EXCLUDED.phone != patient_records.phone a échoué car lorsque les deux étaient NULL, la comparaison retournait NULL (inconnu), ce qui a conduit à ce que la mise à jour soit ignorée de manière incorrecte, et lorsque la nouvelle valeur était NULL et l'ancienne ne l'était pas, la logique se comportait de manière incohérente à travers différentes versions mineures de PostgreSQL.
Trois solutions ont été évaluées.
La première approche a utilisé COALESCE exclusivement dans la clause SET : SET phone = COALESCE(EXCLUDED.phone, patient_records.phone). Cela empêchait l'écrasement avec NULL mais forçait une mise à jour à chaque conflit, déclenchant des reconstructions coûteuses d'index B-Tree sur la colonne phone et lançant des déclencheurs d'audit qui enregistraient les modifications « no-op » comme des modifications légitimes. Cela a augmenté le trafic WAL (Write-Ahead Log) de 300 %, menaçant le retard de réplication et saturant le disque I/O.
La deuxième solution a tenté une logique booléenne explicite pour gérer NULL : WHERE (EXCLUDED.phone != patient_records.phone) OR (EXCLUDED.phone IS NULL AND patient_records.phone IS NOT NULL). Bien que logiquement correcte, ce schéma verbeux nécessitait une maintenance minutieuse à travers 15 colonnes nullables et confuse l'optimiseur de requêtes. Le planificateur a abandonné les scans d'index en faveur de scans séquentiels sur la table de 20 millions de lignes, ce qui a entraîné un dépassement de la fenêtre de maintenance de six heures pour le travail ETL.
La troisième solution a mis en œuvre IS DISTINCT FROM pour toutes les colonnes nullables dans la clause WHERE. Cela a fourni un prédicat concis et sargable qui a correctement identifié les véritables changements de données y compris les transitions NULL. Il a permis des mises à jour seulement lorsque nécessaire, éliminant les exécutions de déclencheurs superflues et la génération de WAL tout en maintenant des plans de requêtes cohérents.
L'équipe a choisi la troisième solution pour les champs de contact critiques et la première solution pour les métadonnées non critiques où la protection contre l'écrasement importait plus que la performance. Le résultat a été spectaculaire : la durée du travail de synchronisation est passée de 45 minutes à 12 minutes, le retard de réplication s'est stabilisé en dessous de cinq secondes et les incidents de « disparition de numéro de téléphone » ont complètement cessé au cours de la première semaine de déploiement.
Pourquoi WHERE EXCLUDED.column != table.column ignore-t-il les lignes lorsque les deux valeurs sont NULL, et comment cela interagit-il avec le mécanisme de mise à jour de PostgreSQL ?
Beaucoup de candidats supposent que si deux NULL ne sont pas égaux, la comparaison devrait retourner TRUE et permettre la mise à jour. Cependant, SQL utilise une logique à trois valeurs : NULL représente une valeur inconnue. Toute comparaison avec NULL (y compris NULL = NULL ou NULL != NULL) produit NULL (inconnu), et non un booléen TRUE ou FALSE. Dans la clause WHERE de PostgreSQL, seules les lignes évaluées à TRUE peuvent avancer ; NULL est traité comme FALSE. Ainsi, lorsqu'on compare deux numéros de téléphone NULL, le résultat est NULL, la mise à jour est sautée, et le système suppose incorrectement qu'aucun changement n'est nécessaire. IS DISTINCT FROM retourne FALSE pour NULL contre NULL, indiquant correctement qu'ils sont identiques et sautant la mise à jour uniquement lorsque cela est approprié, tout en retournant TRUE lorsque l'une des valeurs est NULL et l'autre ne l'est pas.
Comment l'ordre des colonnes dans une contrainte unique multicolonne affecte-t-il la performance de la résolution ON CONFLICT, et que se passe-t-il si l'objectif de conflit ne correspond pas exactement à la définition de l'index ?
Les candidats négligent souvent que PostgreSQL exige que l'objectif de conflit (les colonnes listées dans ON CONFLICT (...)) corresponde précisément à la définition d'un index unique, y compris l'ordre des colonnes et toutes les expressions fonctionnelles. Si un index unique existe sur (clinic_id, external_id) mais que la requête spécifie ON CONFLICT (external_id, clinic_id), le planificateur peut échouer à inférer l'index, générant une erreur "il n'y a pas de contrainte unique ou d'exclusion correspondant à la spécification ON CONFLICT". Même s'il réussit, un ordre de colonnes mal assorti peut empêcher l'optimiseur d'utiliser un scan uniquement d'index pour localiser le tuple en conflit, ce qui force un accès au tas et augmente les coûts d'I/O de manière significative.
Quelle est la différence entre l'utilisation de COALESCE(EXCLUDED.column, table.column) dans la clause SET par rapport à l'utilisation de WHERE EXCLUDED.column IS DISTINCT FROM table.column, en particulier en ce qui concerne l'exécution des déclencheurs et le versionnage des lignes ?
L'utilisation de COALESCE dans la clause SET écrit sans condition une valeur dans la ligne (soit les nouvelles données, soit les anciennes données préservées). Cette opération génère une nouvelle version de ligne (CTID), écrit dans le WAL, et déclenche tous les déclencheurs BEFORE et AFTER associés à la table, même si la valeur finale reste identique à l'état précédent. Cela crée du « bruit » dans les tables d'audit et augmente la charge de réplication. En revanche, la clause WHERE avec IS DISTINCT FROM empêche entièrement la modification de la ligne si aucun changement réel ne s'est produit. Aucune nouvelle version de tuple n'est créée, les déclencheurs ne s'activeront pas, et la génération de WAL est évitée. Cette distinction est cruciale pour les systèmes à fort débit avec journalisation d'audit ou cascades de clés étrangères, où les mises à jour « no-op » entraînent une surcharge importante.