Historique de la question
Cette ambiguïté est apparue avec l'introduction de la fonctionnalité native UPSERT dans PostgreSQL 9.5 via la clause ON CONFLICT. Avant cette version, les développeurs mettaient en œuvre des insertions idempotentes en utilisant des boucles complexes en PL/pgSQL ou une logique côté application sujette aux erreurs. La clause RETURNING a longtemps été essentielle pour récupérer les UUID ou les ID sériels, mais son interaction avec le modèle d'exécution à double voie de UPSERT—où l'instruction peut donner lieu à une INSERT ou une UPDATE—a créé un écart sémantique subtil qui confuse même les ingénieurs expérimentés sur la version de ligne qui est réellement retournée.
Le problème
Lorsque l'instruction INSERT ... ON CONFLICT ... DO UPDATE rencontre une violation d'unicité, elle se pivote vers la mise à jour de la ligne existante. La clause RETURNING référence ensuite l'état persistant final de cette ligne. Cependant, si votre logique d'application dépend des valeurs générées pour l'insertion tentée—comme les horodatages created_at, les expressions par défaut, ou les valeurs calculées par l'application—l'instruction retourne au lieu de cela les données obsolètes de la ligne existante. Cette substitution silencieuse provoque une désynchronisation du cache, une corruption de la piste d'audit, et des conditions de course subtiles où les systèmes en aval reçoivent des métadonnées temporellement incohérentes.
La solution
La pseudo-table EXCLUDED offre une fenêtre sur les valeurs d'insertion proposées qui ont déclenché le conflit. En référence explicite à EXCLUDED.column_name dans votre clause RETURNING ou la liste de définition de UPDATE, vous garantissez l'accès aux nouvelles données prévues quels que soient le chemin d'exécution emprunté.
INSERT INTO user_sessions (user_id, login_count, last_seen, session_token) VALUES (1001, 1, NOW(), gen_random_uuid()) ON CONFLICT (user_id) DO UPDATE SET login_count = user_sessions.login_count + 1, last_seen = EXCLUDED.last_seen, session_token = EXCLUDED.session_token RETURNING session_id, user_id, CASE WHEN xmax = 0 THEN 'inserté' ELSE 'mis à jour' END AS operation_type, session_token, EXCLUDED.last_seen AS intended_timestamp;
Dans ce modèle, EXCLUDED.last_seen et EXCLUDED.session_token garantissent que l'application reçoit les nouvelles valeurs de l'insertion tentée, même lorsque la base de données effectue une mise à jour à la place.
Accrual de points de fidélité concurrent
Une plateforme fintech traitant des microtransactions à haute fréquence a rencontré des calculs de récompense fantomatiques. Lorsque deux demandes parallèles tentaient d'attribuer des points au même compte utilisateur simultanément, la base de données PostgreSQL a correctement maintenu l'atomicité, mais le niveau de cache Redis a reçu des horodatages updated_at obsolètes de la clause RETURNING. Cela a conduit le cache à rejeter des incréments de points légitimes comme étant périmés, entraînant une fuite de revenus et des plaintes de clients concernant des récompenses manquantes.
Solution A : Verrouillage distribué avec Redis
L'équipe d'ingénierie a initialement proposé d'acquérir des verrous distribués dans Redis avant d'exécuter la transaction de base de données. Cette approche permettrait de sérialiser les opérations conflictuelles et de garantir la cohérence séquentielle. Cependant, cela a introduit un point de défaillance unique, ajouté 12-18 ms de latence réseau par demande, et créé des scénarios complexes de blocage lorsqu'une transaction était avortée après l'acquisition de verrous. La surcharge opérationnelle de la gestion des verrous et le risque de défaillances en cascade ont rendu cette architecture insoutenable à grande échelle.
Solution B : Lecture-modification-écriture côté application
Une autre suggestion a consisté à interroger d'abord l'existence de l'enregistrement avec un SELECT, puis à décider entre INSERT ou UPDATE dans le code de l'application. Bien que conceptuellement simple, ce modèle échoue catastrophiquement sous une charge concurrente en raison de l'isolation READ COMMITTED permettant des lectures non répétables entre la vérification et l'écriture. La mise en œuvre d'une isolation SERIALIZABLE pour prévenir les conditions de course aurait causé des échecs de sérialisation excessifs et des tempêtes de réessai, tandis que des verrous de table explicites auraient limité le débit à des niveaux inacceptables.
Solution C : Utilisation appropriée d'EXCLUDED
L'approche sélectionnée a refondu la requête pour tirer parti d'EXCLUDED pour toutes les valeurs mutables dans la clause RETURNING. En référence à EXCLUDED.points et EXCLUDED.calculated_at, l'application a constamment reçu les métadonnées prévues de la tentative d'insertion, qu'elle ait abouti à une nouvelle ligne ou à une mise à jour.
Solution choisie et résultat
L'équipe a mis en œuvre la Solution C dans le microservice des récompenses. Cela a éliminé les problèmes d'incohérence du cache sans ajouter de sauts réseau ni compromettre les niveaux d'isolation. La précision de l'accumulation de points a été améliorée à 99,99 %, l'utilisation du CPU de la base de données a diminué de 35 % grâce à la réduction des allers-retours de requêtes, et le système a réussi à gérer les pics de trafic du Black Friday sans intervention manuelle.
Comment PostgreSQL détermine-t-il quel index unique utiliser pour la détection de conflit lorsque plusieurs index existent sur une table ?
PostgreSQL nécessite une spécification explicite de l'arbitre dans la clause ON CONFLICT. Lorsque vous écrivez ON CONFLICT (column_list), le planificateur sélectionne l'index unique dont les colonnes indexées correspondent exactement à la liste fournie dans l'ordre. Si plusieurs index existent sur des colonnes identiques, il choisit celui créé en premier. Pour les index uniques partiels (ceux avec des clauses WHERE) ou les index d'expression, vous devez utiliser la syntaxe ON CONFLICT ON CONSTRAINT constraint_name ; sinon, le moteur renvoie une erreur indiquant qu'il ne peut pas inférer l'index arbitre. Les candidats supposent souvent à tort que la base de données sélectionne automatiquement l'index "le plus sélectif" ou négligent que les index fonctionnels nécessitent un nom de contrainte explicite.
Pourquoi une instruction UPSERT pourrait-elle silencieusement perdre des mises à jour lorsque plusieurs transactions sont en conflit sur la même clé sous l'isolation READ COMMITTED ?
Cela se produit en raison du comportement de réévaluation de la clause UPDATE. Lorsque la Transaction A insère une ligne et s'engage, la Transaction B—attendant sur le verrou de ligne—réexécute son prédicat de UPDATE contre la nouvelle ligne visible. Si la logique de UPDATE utilise une affectation absolue (par exemple, SET balance = 100) plutôt que des références arithmétiques relatives à EXCLUDED (par exemple, SET balance = account.balance + EXCLUDED.amount), la Transaction B écrase complètement les modifications de la Transaction A. De nombreux candidats supposent à tort que UPSERT implique une fusion ou une accumulation automatique, ne réalisant pas que la clause DO UPDATE nécessite une gestion explicite des valeurs EXCLUDED pour obtenir des sémantiques d'accumulation idempotente.
Quelle est la différence précise entre vérifier xmax = 0 et xmax IS NULL pour déterminer si un UPSERT a effectué une insertion, et pourquoi cette distinction est-elle importante pour les mises à jour HOT ?
Dans PostgreSQL, xmax stocke l'ID de transaction de la transaction de suppression ou de mise à jour. Pour les lignes nouvellement insérées, xmax est initialisé à 0, jamais NULL. Les candidats vérifient souvent à tort xmax IS NULL pour détecter les insertions, ce qui renvoie toujours faux. La vérification xmax = 0 identifie de manière fiable les insertions par rapport aux mises à jour. Cette distinction devient critique avec les mises à jour HOT (Heap Only Tuple), où PostgreSQL optimise les performances en mettant à jour les lignes sur place dans la même page sans modifier les index. Tandis que xmax indique correctement que la ligne a été touchée, comprendre que 0 signifie "pas de précédent mise à jour" alors qu'un non-zéro indique la versionnement évite des erreurs logiques lors du calcul des numéros de génération des lignes ou de la mise en œuvre de la logique de capture de changement de données personnalisée qui doit distinguer entre les naissances et les mutations.