Journal UPSERT dans PostgreSQL ça déchire

Posté par .
Tags : aucun
29
7
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 :

  1. vérifier si les enregistrements existent
  2. insérer les enregistrements absents
  3. 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 NOTHING
On 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 . É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 . É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 . É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 . É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 . É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 . É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 . É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 (page perso) . Évalué à 6. Dernière modification le 09/09/18 à 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 . É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)

  • # mongo

    Posté par . É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 à ceux qui les ont postés. Nous n'en sommes pas responsables.