URL: https://linuxfr.org/users/n_e/journaux/upsert-dans-postgresql-ca-dechire Title: UPSERT dans PostgreSQL ça déchire Authors: n_e Date: 2018年09月07日T21:59:55+02:00 Tags: postgresql Score: 30 **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 1. insérer les enregistrements absents 1. 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](https://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.

AltStyle によって変換されたページ (->オリジナル) /