Comment gérer les lignes manquantes dans les UPSERT PostgreSQL avec des transactions simultanées ?

Gestion des lignes de résultats manquantes dans l'opération PostgreSQL UPSERT

Dans PostgreSQL 9.5, l'utilisation conjointe de RETURNING et ON CONFLICT entraînait parfois des lignes manquantes lorsque des transactions simultanées mettaient à jour des cibles conflictuelles spécifiées.

Inconvénients de la réponse actuellement acceptée

La réponse actuellement acceptée est sujette à des problèmes lorsque des transactions simultanées mettent à jour plusieurs lignes dans la table cible. Bien qu'il évite les lignes manquantes, il introduit d'autres effets secondaires et pénalités de performances associés aux mises à jour vides.

Alternatives

Aucune charge d'écriture simultanée :

WITH input_rows(usr, contact, name) AS (
   VALUES
      (text 'foo1', text 'bar1', text 'bob1')  -- 第一行中的类型转换
    , ('foo2', 'bar2', 'bob2')
    -- 更多？
   )
, ins AS (
   INSERT INTO chats (usr, contact, name) 
   SELECT * FROM input_rows
   ON CONFLICT (usr, contact) DO NOTHING
   RETURNING id  --, usr, contact              -- 返回更多列？
   )
SELECT 'i' AS source                           -- 'i' 代表'插入'
     , id  --, usr, contact                    -- 返回更多列？
FROM   ins
UNION  ALL
SELECT 's' AS source                           -- 's' 代表'选择'
     , c.id  --, usr, contact                  -- 返回更多列？
FROM   input_rows
JOIN   chats c USING (usr, contact);           -- 唯一索引的列

Avoir une charge d'écriture simultanée

Pour résoudre les conditions de concurrence pour plusieurs transactions simultanées :

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 NOTHING
   RETURNING id, usr, contact                   -- 我们需要唯一的列来进行后续连接
   )
, sel AS (
   SELECT 'i'::"char" AS source                 -- 'i' 代表'插入'
        , id, usr, contact
   FROM   ins
   UNION  ALL
   SELECT 's'::"char" AS source                 -- 's' 代表'选择'
        , c.id, usr, contact
   FROM   input_rows
   JOIN   chats c USING (usr, contact)
   )
, ups AS (                                      -- 罕见的极端情况
   INSERT INTO chats AS c (usr, contact, name)  -- 另一个UPSERT，不仅仅是UPDATE
   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'::"char" AS source              -- 'u' 代表更新
           , id  --, usr, contact               -- 返回更多列？
   )
SELECT source, id FROM sel
UNION  ALL
TABLE  ups;

Points clés :

• sel CTE renvoie les lignes insérées et les lignes sélectionnées, fournissant l'ensemble complet des résultats de sortie.
• ups CTE gère les lignes manquantes et les met à jour avec les valeurs existantes.
• Le jeu de résultats de sortie exclut toutes les lignes mises à jour par des transactions simultanées à moins qu'elles ne soient ups traitées.

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!