Comment garantir des résultats cohérents des opérations PostgreSQL UPSERT avec RETURNING ?

Opérations PostgreSQL UPSERT et clause RETURNING : gestion des conflits

PostgreSQL INSERT ... ON CONFLICT fournit une fonctionnalité d'upsert, combinant l'insertion et les mises à jour. Cependant, l'utilisation de DO NOTHING avec la clause RETURNING peut conduire à des résultats incomplets dans des scénarios simultanés.

Problème de concurrence 1 : lignes renvoyées manquantes

Si une autre transaction modifie la ligne cible avant la fin de votre INSERT ... ON CONFLICT, l'upsert peut ne pas détecter le conflit, ce qui entraînera des lignes manquantes dans la sortie RETURNING.

Solutions :

Plusieurs approches atténuent cela :

• Vérification du nombre de lignes : Comparez le nombre de lignes d'entrée avec le nombre RETURNING. Les divergences indiquent des lignes manquantes, ce qui entraîne une réexécution de l'instruction.
• Écrasement forcé : Un CTE (Common Table Expression) distinct peut insérer toutes les lignes manquantes. Bien qu'efficace, cette méthode risque des blocages avec plusieurs transactions qui se chevauchent.

Problème de concurrence 2 : verrouillage des lignes

Pour les transactions nécessitant des verrous de ligne, utilisez ON CONFLICT DO UPDATE avec WHERE FALSE. Cela verrouille les lignes sans les modifier. Combinez cela avec SELECT ... FOR UPDATE pour un verrouillage supplémentaire.

Gestion et conversion des types de données pour plus de robustesse

Les solutions existantes sont insuffisantes pour tous les scénarios simultanés. Une approche plus globale implique :

Faible simultanéité :

• Utilisez un CTE avec INSERT et SELECT UNION pour différencier les lignes insérées et sélectionnées.
• Utilisez un CTE pour définir les types de données d'entrée, en les alignant sur le schéma de la table cible.

Concurrence élevée :

• Utilisez l'approche à faible concurrence.
• Incluez un CTE pour insérer de force les lignes manquantes si nécessaire.
• Verrouillez les lignes pertinentes dans le INSERT ... ON CONFLICT CTE pour gérer la concurrence.

Évitement des impasses :

Un ordre d'insertion cohérent minimise les occurrences de blocage.

Gestion des types de données :

• Exploitez une relation existante pour déterminer automatiquement les types de données d'entrée.
• Omettez les noms de colonnes pour le mappage automatique des colonnes.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!