Comment utiliser RETURNING avec ON CONFLICT dans PostgreSQL?

J’ai eu exactement le même problème, et je l’ai résolu en utilisant «do update» au lieu de «ne rien faire», même si je n’avais rien à mettre à jour. Dans votre cas, ce serait quelque chose comme ceci:

 INSERT INTO chats ("user", "contact", "name") VALUES ($1, $2, $3), ($2, $1, NULL) ON CONFLICT("user", "contact") DO UPDATE SET name=EXCLUDED.name RETURNING id; 

Cette requête renverra toutes les lignes, indépendamment de leur insertion ou de leur existence antérieure.

La réponse actuellement acceptée semble convenir pour quelques conflits, petits tuples et aucun déclencheur. Et cela évite le problème de concurrence 1 avec la force brute (voir ci-dessous). La solution simple a son attrait, les effets secondaires peuvent être moins importants.

Pour tous les autres cas, ne mettez pas à jour des lignes identiques sans besoin. Même si vous ne voyez aucune différence en surface, il y a divers effets secondaires :

• Il pourrait déclencher des déclencheurs qui ne devraient pas être déclenchés.

• Il verrouille en écriture les lignes “innocentes”, entraînant éventuellement des coûts pour les transactions simultanées.

• Cela peut donner l’impression que la ligne est nouvelle, même si elle est ancienne (horodatage de transaction).

• Plus important encore , avec le modèle MVCC de PostgreSQL, une nouvelle version de ligne est écrite de toute façon, que les données de la ligne soient identiques ou non. Cela entraîne une pénalité de performance pour l’UPSERT lui-même, le gonflement de la table, le gonflement de l’index, la pénalité de performance pour toutes les opérations ultérieures sur la table, le coût de VACUUM . Un effet mineur pour quelques doublons, mais massif pour la plupart des dupes.

Vous pouvez atteindre (presque) le même sans les mises à jour vides et les effets secondaires.

Sans charge d’écriture concurrente

 WITH input_rows(usr, contact, name) AS ( VALUES (text 'foo1', text 'bar1', text 'bob1') -- type casts in first row , ('foo2', 'bar2', 'bob2') -- more? ) , ins AS ( INSERT INTO chats (usr, contact, name) SELECT * FROM input_rows ON CONFLICT (usr, contact) DO NOTHING RETURNING id --, usr, contact -- return more columns? ) SELECT 'i' AS source -- 'i' for 'inserted' , id --, usr, contact -- return more columns? FROM ins UNION ALL SELECT 's' AS source -- 's' for 'selected' , c.id --, usr, contact -- return more columns? FROM input_rows JOIN chats c USING (usr, contact); -- columns of unique index 

La colonne source est un ajout facultatif pour montrer comment cela fonctionne. Vous en avez peut-être besoin pour faire la différence entre les deux cas (un autre avantage par rapport aux écritures vides).

Les dernières JOIN chats fonctionnent car les nouvelles lignes insérées à partir d’un CTE de modification de données attaché ne sont pas encore visibles dans la table sous-jacente. (Toutes les parties de la même instruction SQL voient les mêmes instantanés des tables sous-jacentes.)

Puisque l’expression VALUES est indépendante (non directement attachée à une INSERT ), Postgres ne peut pas dériver des types de données des colonnes cibles et vous devrez peut-être append des transtypages de type explicites. Le manuel:

Lorsque VALUES est utilisé dans INSERT , les valeurs sont toutes automatiquement forcées dans le type de données de la colonne de destination correspondante. Lorsqu’il est utilisé dans d’autres contextes, il peut être nécessaire de spécifier le type de données correct. Si les entrées sont toutes des constantes littérales, la première contrainte suffit pour déterminer le type supposé pour tous.

La requête elle-même peut être un peu plus chère pour quelques dupes, en raison de la surcharge du CTE et du SELECT supplémentaire (qui devrait être bon marché car l’index parfait est là par définition – une contrainte unique est implémentée avec un index).

Peut être (beaucoup) plus rapide pour de nombreux doublons. Le coût effectif des écritures supplémentaires dépend de nombreux facteurs.

Mais il y a de moins en moins d’effets secondaires et de coûts cachés . C’est probablement moins cher en général.

(Les séquences jointes sont toujours avancées, car les valeurs par défaut sont remplies avant de tester les conflits.)

À propos des CTE:

• Les requêtes de type SELECT sont-elles le seul type pouvant être nested?
• Dédupliquer les instructions SELECT dans la division relationnelle

Avec un chargement d’écriture simultané

En supposant l’isolement de transaction READ COMMITTED par défaut.

Réponse connexe sur dba.SE avec explication détaillée:

• Les transactions simultanées entraînent une condition de concurrence avec une contrainte unique sur l’insertion

La meilleure stratégie pour se défendre contre les conditions de concurrence dépend des exigences exactes, du nombre et de la taille des lignes du tableau et des UPSERT, du nombre de transactions simultanées, de la probabilité de conflits, des ressources disponibles et d’autres facteurs.

Problème de concurrence 1

Si une transaction concurrente a été écrite sur une ligne que votre transaction essaie maintenant d’UPSERT, votre transaction doit attendre que l’autre termine.

Si l’autre transaction se termine par ROLLBACK (ou toute erreur, par exemple ROLLBACK automatique), votre transaction peut continuer normalement. Effet secondaire mineur: lacunes dans les nombres séquentiels. Mais pas de lignes manquantes.

Si l’autre transaction se termine normalement ( COMMIT implicite ou explicite), votre INSERT détectera un conflit (l’index / contrainte UNIQUE est absolu) et DO NOTHING , donc ne retournera pas non plus la ligne. (Il est également impossible de verrouiller la ligne comme indiqué dans le problème de concurrence 2 ci-dessous, car elle n’est pas visible .) SELECT voit le même instantané depuis le début de la requête et ne peut pas non plus renvoyer la ligne encore invisible.

De telles lignes sont absentes du jeu de résultats (même si elles existent dans la table sous-jacente)!

Cela peut être correct tel quel . Surtout si vous ne renvoyez pas de lignes comme dans l’exemple et que vous êtes satisfait de savoir que la ligne est là. Si ce n’est pas suffisant, il y a plusieurs façons de le contourner.

Vous pouvez vérifier le nombre de lignes de la sortie et répéter l’instruction si elle ne correspond pas au nombre de lignes de l’entrée. Peut-être suffisant pour le cas rare. Le but est de démarrer une nouvelle requête, qui verra alors les nouvelles lignes validées.

Ou vérifiez les lignes de résultat manquantes dans la même requête et écrasez celles avec le tour de force brutal démontré dans la réponse d’Alextoni .

 WITH input_rows(usr, contact, name) AS ( ... ) -- see above , ins AS ( INSERT INTO chats AS c (usr, contact, name) SELECT * FROM input_rows ON CONFLICT (usr, contact) DO NOTHING RETURNING id, usr, contact -- we need unique columns for later join ) , sel AS ( SELECT 'i'::"char" AS source -- 'i' for 'inserted' , id, usr, contact FROM ins UNION ALL SELECT 's'::"char" AS source -- 's' for 'selected' , c.id, usr, contact FROM input_rows JOIN chats c USING (usr, contact) ) , ups AS ( -- RARE corner case INSERT INTO chats AS c (usr, contact, name) -- another UPSERT, not just UPDATE SELECT i.* FROM input_rows i LEFT JOIN sel s USING (usr, contact) -- columns of unique index WHERE s.usr IS NULL -- missing! ON CONFLICT (usr, contact) DO UPDATE -- we've asked nicely the 1st time ... SET name = c.name -- ... this time we overwrite with old value -- SET name = EXCLUDED.name -- alternatively overwrite with *new* value RETURNING 'u'::"char" AS source -- 'u' for updated , id --, usr, contact -- return more columns? ) SELECT source, id FROM sel UNION ALL TABLE ups; 

C’est comme la requête ci-dessus, mais nous ajoutons une étape supplémentaire avec les améliorations de CTE, avant que nous retournions le jeu de résultats complet . Ce dernier CTE ne fera rien la plupart du temps. Seulement si les lignes sont manquantes dans le résultat retourné, nous utilisons la force brute.

Encore plus de frais généraux. Plus il y a de conflits avec les lignes préexistantes, plus il y a de chances que cela devance l’approche simple.

Un effet secondaire: le 2ème UPSERT écrit les lignes dans le désordre, donc il réintroduit la possibilité de blocages (voir ci-dessous) si trois transactions ou plus écrivant sur les mêmes lignes se chevauchent. Si c’est un problème, vous avez besoin d’une solution différente.

Problème de concurrence 2

Si les transactions simultanées peuvent écrire dans les colonnes concernées des lignes affectées et que vous devez vous assurer que les lignes trouvées se trouvent toujours à un stade ultérieur de la même transaction, vous pouvez verrouiller les lignes à moindre coût avec:

 ... ON CONFLICT (usr, contact) DO UPDATE SET name = name WHERE FALSE -- never executed, but still locks the row ... 

Et append une clause de locking au SELECT , comme FOR UPDATE .

Cela permet aux opérations d’écriture concurrentes d’attendre la fin de la transaction, lorsque tous les verrous sont libérés. Alors soyez bref.

Plus de détails et explications:

• Comment inclure des lignes exclues dans RETOURNER à partir de INSERT … ON CONFLICT
• Est-ce que SELECT ou INSERT est une fonction sujette à des conditions de course?

Les impasses?

Défendez les blocages en insérant des lignes dans un ordre cohérent . Voir:

• Interblocage avec des INSERT à plusieurs lignes malgré le fait que ON NE SAIT PAS DE CONFLIT

Types de données et conversions

Table existante en tant que modèle pour les types de données …

La conversion de type explicite pour la première ligne de données dans l’expression VALUES autonome peut être gênante. Il y a des façons de contourner cela. Vous pouvez utiliser n’importe quelle relation existante (table, vue, …) en tant que modèle de ligne. La table cible est le choix évident pour le cas d’utilisation. Les données d’entrée sont forcées automatiquement aux types appropriés, comme dans une clause VALUES d’un INSERT :

 WITH input_rows AS ( (SELECT usr, contact, name FROM chats LIMIT 0) -- only copies column names and types UNION ALL VALUES ('foo1', 'bar1', 'bob1') -- no type casts needed , ('foo2', 'bar2', 'bob2') ) ... 

Cela ne fonctionne pas pour certains types de données (explication dans la réponse liée en bas). L’astuce suivante fonctionne pour tous les types de données:

… et les noms

Si vous insérez des lignes entières (toutes les colonnes de la table – ou au moins un ensemble de colonnes de début), vous pouvez également omettre les noms de colonne. En supposant que les chats table dans l’exemple ne comportent que les 3 colonnes utilisées:

 WITH input_rows AS ( SELECT * FROM ( VALUES ((NULL::chats).*) -- copies whole row definition ('foo1', 'bar1', 'bob1') -- no type casts needed , ('foo2', 'bar2', 'bob2') ) sub OFFSET 1 ) ... 

Explication détaillée et autres alternatives:

• Conversion de type NULL lors de la mise à jour de plusieurs lignes

---

À part: n’utilisez pas de mots réservés comme "user" comme identifiant. C’est un footgun chargé. Utilisez des identifiants légaux, minuscules et non cotés. Je l’ai remplacé par usr .

Upsert, étant une extension de la requête INSERT peut être défini avec deux comportements différents en cas de conflit de contraintes: DO NOTHING ou DO UPDATE .

 INSERT INTO upsert_table VALUES (2, 6, 'upserted') ON CONFLICT DO NOTHING RETURNING *; id | sub_id | status ----+--------+-------- (0 rows) 

Notez également que RETURNING ne renvoie rien car aucun tuples n’a été inséré . Maintenant, avec DO UPDATE , il est possible d’effectuer des opérations sur le tuple avec lequel il y a un conflit. Notez tout d’abord qu’il est important de définir une contrainte qui sera utilisée pour définir qu’il existe un conflit.

 INSERT INTO upsert_table VALUES (2, 2, 'inserted') ON CONFLICT ON CONSTRAINT upsert_table_sub_id_key DO UPDATE SET status = 'upserted' RETURNING *; id | sub_id | status ----+--------+---------- 2 | 2 | upserted (1 row)