Journal UPSERT dans PostgreSQL ça déchire

Posté par n_e le 07 septembre 2018 à 21:59.

Étiquettes :

sept.

2018

UPSERT, c'est quoi ?

Comme son nom l'indique, UPSERT est un mélange de la mise à jour (UPDATE) et de l'insertion (INSERT). Ça consiste à insérer un enregistrement, et, s'il existe, à le mettre à jour, en une seule commande.

L'intérêt "de base" de cette commande est lié au parallélisme, mais on verra juste après qu'elle est aussi très pratique.

Le parallélisme
Avant UPSERT, il fallait utiliser plusieurs commandes, par exemple :

vérifier si les enregistrements existent
insérer les enregistrements absents
mettre à jour ceux qui existent

Sauf que si un enregistrement absent est créé par un autre utilisateur entre les étapes 1. et 2., ou un enregistrement supprimé entre 1. et 3. ça va échouer, et en plus la mise à jour sera faite à moitié.

Vous me direz : "mais tu es bête, il faut utiliser une transaction, c'est fait pour ça". Sauf que pour être sûr de ne pas avoir de problème, il faut utiliser une isolation de niveau SERIALIZABLE, et dans ce cas les transactions peuvent échouer.

Mais revenons à nos moutons, je veux surtout vous parler du coté pratique d'UPSERT.

UPSERT dans PostgreSQL, comment ça marche ?

C'est tout simple, on ajoute à une expression INSERT la clause ON CONFLICT, qui dit ce qu'il faut faire en cas de conflit.

Il y a deux options possibles :
ON CONFLIT DO NOTHINGOn ne fait rien en cas de conflit : les enregistrements qui produiraient des conflits ne sont pas insérés mais les autres oui.
ON CONFLICT DO UPDATE SET column_name = expression...En cas de conflit, les enregistrements qui produiraient des conflits sont mis à jour selon les règles qu'on indique, par exemple :

INSERT INTO produits(id,prix) VALUES(1,199)
ON CONFLICT DO UPDATE SET produits.prix = 199

A noter qu'il existe une table "virtuelle" s'appelant excluded, qui contient les valeurs qu'on voudrait insérer, ce qui permet de ne pas saisir 199 deux fois :

INSERT INTO produits(id,prix) VALUES(1,199)
ON CONFLICT DO UPDATE SET produits.prix = excluded.prix

Maintenant que vous voyez comment ça marche, passons à un exemple intéressant

Un exemple intéressant (ou pas)

Prenons l'exemple, que vous connaitrez sans doute, de totoz.eu.

Les totoz sont affublés de tags. Ces tags ont certaines propriétés :

à un totoz sont associés 0 à n tags
chaque tag est unique pour chaque totoz (i.e. les totoz [:uxam] et [:wat] peuvent tous les deux être tagués chat mais on ne va pas avoir plusieurs tags chat pour [:uxam])
n'importe qui peut créer un tag sur n'importe quel totoz
seuls les créateurs d'un tag ou du totoz associé peuvent le supprimer.

Le schéma de la table tags est le suivant :

CREATE TABLE tags (
    name varchar(100) not null,
    totoz_name varchar(512) not null references totoz(name),
    user_name varchar(100) references users(name),
    primary key (name,totoz_name)
);

Maintenant attaquons le cas pratique.

Permettre à l'utilisateur d'ajouter des tags à un totoz

L'utilisateur a un champ où il saisit les tags à ajouter, séparés par des espaces ou des virgules. Coté serveur, on les vérifie et on les met dans un tableau :

const tags = ('' + req.body.tags).split(/[ ,]/)
        .map(t => t.replace(/[^A-Za-z0-9-_]/g, ''))
        .filter(t => t.length > 0)

Maintenant essayons d'insérer les tags sans upsert.

Commençons par la requête que ferait un débutant en SQL :

INSERT INTO tags(name, totoz_name, user_name) VALUES ($1,$2,$3)

avec $1=tags[0], $2='uxam', $3='n_e'

C'est très bof : il faut faire autant de requêtes que de tags à insérer, ou alors utiliser plusieurs valeurs mais dans ce cas on ne peut plus utiliser de paramètres.

Heureusement, PostgreSQL gère les tableaux :

insert into tags(name,totoz_name,user_name)
    select
        unnest,$2,$3
    from  unnest($1)

avec $1=tags, $2='uxam', $3='n_e'

On peut tout insérer en une requête.

Rq. : unnest transforme un tableau en ensemble d'enregistrements, ce qui permet de faire un select dessus.

Sauf que maintenant on a un problème : si un tag existe déjà, la contrainte sur l'unicité du couple (name, totoz_name) est violée, et aucun des tags n'est inséré.

On veut que seuls les tags qui n'ont pas été insérés le soient.

Sans UPSERT, on pourrait :

vérifier quels tags existent et les enlever tu tableau tags avant de faire l'insertion
insérer un tag à la fois et ignorer l'exception si le tag existe

Bref c'est bien moche.

Avec UPSERT il suffit d'ajouter une petite ligne :

insert into tags(name,totoz_name,user_name)
    select
        unnest,$2,$3
    from  unnest($1)
on conflict do nothing

avec $1=tags, $2='uxam', $3='n_e'

Mettre à jour les tags des totoz à partir d'un site externe

Certains totoz de totoz.eu sont importés de hardware.fr. Les tags de ces totoz sont à la fois ceux de hardware.fr et ceux ajoutés par les utilisateurs de totoz.eu. Les tags de hardware.fr sont mis à jour périodiquement sur totoz.eu.

Lors de la mise à jour, on veut que :

les tags de hardware.fr inexistants soient ajoutés
les tags de hardware.fr existants sur totoz.eu mais attribués à personne soient attribués à l'utilisateur 'hfr'
les tags de hardware.fr déjà créés par un utilisateur de totoz.eu ne soient pas touchés.

On peut modifier la requête plus haut ainsi :

insert into tags(name,totoz_name,user_name)
    select
        unnest,$2,'hfr'
    from  unnest($1)
on conflict do update
    set user_name='hfr' where tags.user_name is null

avec $1=tags, $2='uxam'

Pour finir

J'espère que ces exemples vous ont convaincu de l'intérêt d'upsert et plus généralement de la puissance de SQL plutôt que de manipuler excessivement les données dans l'application.

# not exists

Posté par julo4lfr le 08 septembre 2018 à 09:18. Évalué à 4.

Je suis pas expert postgresql mais une jointure de unnest sur la table tags (left avec condition null ou un not exist) ne permet pas d’avoir un résultat équivalent sans passer par un upsert ?
- [^] # Re: not exists
  
  Posté par dyno partouzeur du centre le 08 septembre 2018 à 18:24. Évalué à 1.
  En fait on doit pouvoir faire:
```
insert into tags(name,totoz_name,user_name)
    select
        unnest,$2,$3
    from unnest($1)
    where not exists(
        select 1 
        from tags t 
        where 
            t.name = tags.name
            and t.totoz_name = tags.totoz_name
    )
```
  C'est un peu plus lourd mais au moins c'est standard et portable
  - [^] # Re: not exists
    
    Posté par dverite le 08 septembre 2018 à 19:04. Évalué à 3.
    
    Cette requête INSERT ne peut pas mettre à jour des lignes existantes, seulement insérer de nouvelles lignes, contrairement à un UPSERT qui oriente chaque nouvelle ligne vers un UPDATE ou un INSERT en fonction de l'existant.
    
    Par ailleurs cette clause NOT EXISTS(…) ne peut pas voir les changement d'une transaction concurrente qui n'aurait pas encore fait de COMMIT, alors que l'UPSERT via INSERT…ON CONFLICT peut le faire.
    - [^] # Re: not exists
      
      Posté par dyno partouzeur du centre le 08 septembre 2018 à 19:41. Évalué à 1.
      
      1/ Tu peux faire l'update ensuite. OK y a peut-être des cas tordus où tu n'as pas la souplesse de l'upsert.
      
      2/ Ca doit dépendre de ton niveau d'isolation j'espère. Et avoir des phantom reads c'est pas vraiment un avantage : Qui te dit que la transaction concurrente ne sera pas rollbackée ?
      - [^] # Re: not exists
        
        Posté par n_e le 08 septembre 2018 à 20:24. Évalué à 2.
        
        C'est quand même mieux de faire une requête simple (ta requête deux posts plus haut est déjà plus longue que celle avec ON CONFLICT) que plusieurs, non ?
        
        Pour le point 2), pour éviter les phantom reads il faut utiliser le niveau d'isolation serializable, niveau où les requêtes peuvent échouer. Ça complique encore plus la chose (alors que l'upsert est atomique).
      - [^] # Re: not exists
        
        Posté par dverite le 11 septembre 2018 à 18:14. Évalué à 2.
        
        Justement les problèmes avec une transaction concurrente sont gérés par INSERT.. ON CONFLICT y compris dans le niveau d'isolation le plus faible, READ COMMITTED (qui est celui par défaut, donc forcément beaucoup de gens l'utilisent), c'est ce qui contribue beaucoup à son intérêt, comme le dit le billet.
        
        Il n'y a jamais de "dirty read" dans PostgreSQL, mais ça n'empêche pas le moteur lui-même de savoir, en présence d'une contrainte d'unicité, qu'une transaction veut insérer la même valeur qu'une autre qui n'a pas encore committé mais dont l'INSERT est déjà passé avec succès. Dans ce cas le deuxième INSERT est mis en attente du résultat de l'autre transaction. Si la première transaction fait un COMMIT, la deuxième partira une erreur de violation de clef unique. Si au contraire la 1ere transaction fait un ROLLBACK, la 2eme pourra continuer.
        
        C'est ce que je voulais dire par "l'UPSERT via INSERT…ON CONFLICT peut voir les changements d'une transaction concurrente". Ce n'est pas un dirty read (ni un phantom read qui est encore autre chose, cf https://en.wikipedia.org/wiki/Isolation_(database_systems)#Phantom_reads) parce que cette visibilité est interne à l'exécution de UPSERT.
        
        Il y a plein de détails à ce sujet sur le wiki PostgreSQL écrits au moment du développement de cette fonctionnalité, y compris des comparaisons avec les solutions d'autres SGBDs:
        
        https://wiki.postgresql.org/wiki/UPSERT
# MySQL : INSERT IGNORE ou REPLACE

Posté par Yth (Mastodon) le 09 septembre 2018 à 00:10. Évalué à 8.

Dans MySQL, il y a deux instructions pour faire plus ou moins la même chose selon les cas :
INSERT IGNORE va faire exactement INSERT ON CONFLIT DO NOTHING, c'est à dire que tu fais un INSERT, et si ça rate, tant pis.
Particulièrement utile lors d'INSERT multiples, ça ajoute tout ce que ça peut ajouter sans planter.

Et aussi le REPLACE, qui fait un DELETE si l'INSERT est impossible, puis qui fait l'INSERT.

Le cas non géré est l'intermédiaire, où va mettre à jour si la ligne existe et la créer sinon.
Utile pour des cas particuliers où l'INSERT se fait avec des colonnes non définies, donc à la valeur par défaut, mais si la ligne existe et que ces valeurs ont été modifiées, on ne les change pas, on met juste à jour certaines colonnes. Je n'ai pas spécialement d'exemple à proposer…

Dans l'exemple donné avec les totoz, on doit pouvoir s'en sortir avec de l'INSERT IGNORE et du REPLACE.

Maintenant, je ne sais pas trop où se situe la norme SQL entre les IGNORE, les ON CONFLICT etc..

Yth.
- [^] # Re: MySQL : INSERT IGNORE ou REPLACE
  
  Posté par claudex le 09 septembre 2018 à 08:19. Évalué à 6. Dernière modification le 09 septembre 2018 à 08:19.
  
  Utile pour des cas particuliers où l'INSERT se fait avec des colonnes non définies, donc à la valeur par défaut, mais si la ligne existe et que ces valeurs ont été modifiées, on ne les change pas, on met juste à jour certaines colonnes. Je n'ai pas spécialement d'exemple à proposer…
  
  Par exemple, si tu as un "created_time = NOW()".
  
  « Rappelez-vous toujours que si la Gestapo avait les moyens de vous faire parler, les politiciens ont, eux, les moyens de vous faire taire. » Coluche
- [^] # Re: MySQL : INSERT IGNORE ou REPLACE
  
  Posté par windu.2b le 09 septembre 2018 à 10:47. Évalué à 8.
  
  Le cas non géré est l'intermédiaire, où va mettre à jour si la ligne existe et la créer sinon.
  
  Bien sûr que si ! Avec un INSERT... ON DUPLICATE KEY UPDATE
  (cf. la documentation officielle de MySQL)
  - [^] # Re: MySQL : INSERT IGNORE ou REPLACE
    
    Posté par 42nodid le 14 septembre 2018 à 12:37. Évalué à 0.
    
    Exact. Ou alors il y a une subtilité que je n'ai pas compris, mais cette fonction existe dans tous les "grands" SGBD : https://sql.sh/cours/insert-into/on-duplicate-key
    La seule chose, c'est qu'elle n'est pas standardisée, et ce n'est pas cette nouvelle fonction spécifique a Postgre qui risque de faire évoluer la situation …
# mongo

Posté par barmic le 09 septembre 2018 à 09:12. Évalué à 2.

Je découvre un peu mongodb en ce moment. Il a une option upsert pour prévoir la même chose. Avec la possibilité dans une requête d'update d'indiquer que tel champ doit être écrit qu'à l'insertion via $setOnInsert.

Mais en vrai c'est pas fou. C'est pas une opération atomique. Donc un upsert, peu t'envoyer une erreur Duplicate key error. Ça surprend la première fois.

https://jira.mongodb.org/browse/SERVER-14322

Suivre le flux des commentaires

Note : les commentaires appartiennent à celles et ceux qui les ont postés. Nous n’en sommes pas responsables.