Comment utiliser de manière fiable la clause RETURNING de PostgreSQL avec ON CONFLICT DO NOTHING ?

Clause RETURNING de PostgreSQL et ON CONFLICT DO NOTHING

La clause RETURNING de PostgreSQL est inestimable pour récupérer des données à partir de lignes nouvellement insérées après une opération INSERT. Ceci est particulièrement utile avec les UPSERT (insertion ou mise à jour) utilisant ON CONFLICT. Un défi courant se pose lors de l'utilisation de ON CONFLICT DO NOTHING : si aucun conflit n'existe, RETURNING ne donne aucun résultat.

Bien que tenter de mettre à jour les lignes correspondantes pour déclencher RETURNING puisse sembler être une solution, cela est fortement déconseillé. Les inconvénients potentiels incluent :

• Activations de déclencheurs inutiles
• Verrouillage involontaire en écriture des lignes
• Représenter à tort les lignes existantes comme étant nouvelles
• Dégradation des performances due à une gestion excessive des versions de lignes

Des alternatives plus efficaces et robustes existent.

Gestion des scénarios sans écritures simultanées :

Pour les environnements sans activité d'écriture simultanée significative, un CTE (Common Table Expression) combiné à un JOIN final fournit une solution élégante. Cela garantit que les lignes insérées et existantes sont incluses dans la sortie :

<code class="language-sql">WITH input_rows(usr, contact, name) AS (
   VALUES
      ('foo1', 'bar1', 'bob1')
    , ('foo2', 'bar2', 'bob2')
    -- more?
   )
, ins AS (
   INSERT INTO chats (usr, contact, name) 
   SELECT * FROM input_rows
   ON CONFLICT (usr, contact) DO NOTHING
   RETURNING id
   )
SELECT 'i' AS source, id
FROM   ins
UNION  ALL
SELECT 's' AS source, c.id
FROM   input_rows
JOIN   chats c USING (usr, contact);</code>

Résolution des charges d'écriture simultanées :

Les opérations d'écriture simultanées introduisent de la complexité. Si une autre transaction modifie une ligne avant la fin de votre INSERT, des incohérences peuvent survenir.

Pour atténuer ce problème, envisagez de verrouiller les lignes existantes plus tôt. Une deuxième étape UPSERT peut gérer les lignes manquantes :

<code class="language-sql">WITH input_rows(usr, contact, name) AS ( ... )
, ins AS (
   INSERT INTO chats AS c (usr, contact, name) 
   SELECT * FROM input_rows
   ON     CONFLICT (usr, contact) DO UPDATE
   SET    name = name WHERE FALSE  -- Locks the row without updating
   RETURNING id, usr, contact
   )
, sel AS (
   SELECT 'i' AS source, id, usr, contact
   FROM   ins
   UNION  ALL
   SELECT 's' AS source, c.id, usr, contact
   FROM   input_rows
   JOIN   chats c USING (usr, contact)
   )
, ups AS (
   INSERT INTO chats AS c (usr, contact, name)
   SELECT i.*
   FROM   input_rows i
   LEFT   JOIN sel   s USING (usr, contact)
   WHERE  s.usr IS NULL
   ON     CONFLICT (usr, contact) DO UPDATE
   SET    name = c.name
   RETURNING 'u' AS source, id
   )
SELECT source, id FROM sel
UNION  ALL
TABLE  ups;</code>

Ces approches renvoient de manière fiable des données pour les lignes insérées et existantes, même sous une pression d'écriture simultanée.

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!