Bataille de MERGE. Chronique avec conclusions et morale

Quelques semaines avant l'important festival de commit - le dernier avant la version feature freeze de PostgreSQL 11 - les newsletters des hackers , compressant le chipset dans le package de gauche, ont regardé le thriller MERGE . Le directeur du thriller et PDG de 2ndQuadrant, Simon Riggs , a essayé de pousser un patch qui implémente la syntaxe de la commande MERGE avec une persévérance et une ingéniosité impressionnantes. Riggs est comédien depuis 2009, et avec le statut de comédien, vous pouvez approuver les correctifs vous-même. Il a été combattu par des comités et des vétérans de PostgreSQL non moins respectés. Les passions bouillonnaient clairement et implicitement, il ne s'agissait même pas d'insultes directes - un fait surprenant pour les habitués de nombreux forums nationaux. Cependant, une certaine tension est restée jusqu'à présent lorsque la question a été réglée, et il n'y a rien à discuter.

Mais les passions sont des passions (elles seront discutées plus loin), et je voudrais trier sans passion l'essence de ce problème complètement farfelu.

MERGE dehors

Si cela simplifie complètement, alors la chose est la suivante: nous avons 2 tables avec les mêmes champs et des données différentes. Supposons le nom et l'âge. Nous devons les combiner en un seul. Mais il serait nécessaire de décider quoi faire avec ces personnalités qui sont dans les deux tableaux. Très probablement, nous voudrons tout dans la table finale et mettre à jour les informations pour les individus correspondants. Il est clair que même dans ce contexte, c'est une tâche très courante. Il peut être résolu sans MERGE , ce qui rend une demande complexe, vous pouvez utiliser des déclencheurs et ainsi de suite. Mais c'est gênant. Cependant, la version non canonique de MERGE, appelée UPSERT (UPdate + inSERT), résout ce problème.

L'opérateur MERGE est dans la norme SQL-2003 et déjà dans toute sa splendeur dans SQL-2008. Il est implémenté dans Oracle, DB2 et MS SQL, ce qui signifie que le manque de MERGE dérangera ceux qui envisagent de passer de ces SGBD à PostgreSQL. Le désir ardent de Simon Riggs le plus rapidement possible, déjà dans PostgreSQL 11, a été alimenté par les souhaits des clients 2ndQuadrant, et non par l'ambition ou la querelle.

En fait, MERGE a des capacités riches, les données ne doivent pas être extraites de tables, en particulier de structures similaires.

La syntaxe de la commande est la suivante:

  MERGE INTO tablename USING table_reference ON (condition) WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...] WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...]); 

Vous pouvez cependant comme ceci:

 MERGE [hint] INTO [schema .] {table | view} [table_alias] USING { subquery | [schema .] { table | view}} [table_alias] ON ( condition ) [ merge_update_clause ] [ merge_insert_clause ] [ error_logging_clause ] ; 

Cette syntaxe est implémentée dans Oracle. En d'autres termes, MERGE effectue des actions qui modifient les enregistrements dans la table cible target_table_name à l'aide de data_source dans une seule commande SQL, qui peut, selon les conditions, faire INSERT, UPDATE ou DELETE par rapport aux enregistrements dans target_table_name. Dans ce cas, target_table_name peut être une vue et data_source peut être un ensemble de tables ou de vues, le résultat d'une sous-requête .

Premièrement, l' MERGE effectue une left outer join sur la data_source de data_source avec target_table_name , suggérant 0 ou plusieurs enregistrements de changement de candidat; WHEN clauses sont calculées dans l'ordre spécifié; dès que la condition est remplie, l'action correspondante est exécutée. Mots clés WHEN [NOT] MATCH THEN n'est pas très courant en SQL , nous vous rappelons donc qu'il s'agit d'une construction de contrôle comme if-else dans d'autres langages. MERGE agit de la même manière que UPDATE, INSERT ou DELETE qui concerne target_table_name , seule la syntaxe de la commande entière est différente.

Une clause avec ON doit établir une connexion sur toutes les colonnes de la clé primaire ou, si d'autres colonnes sont spécifiées, alors un index unique doit être utilisé pour que les conditions [NOT] MATCHED déterminent immédiatement les actions pour l'enregistrement candidat afin d'exclure l'interaction avec d'autres transactions.

Commande déterministe MERGE : vous ne pouvez pas mettre à jour plusieurs fois le même enregistrement dans la même commande MERGE.
Un exemple:

 MERGE CustomerAccount CA USING RecentTransactions T ON T.CustomerId = CA.CustomerId WHEN MATCHED THEN UPDATE SET Balance = Balance + TransactionValue WHEN NOT MATCHED THEN INSERT (CustomerId, Balance) VALUES (T.CustomerId, T.TransactionValue); 

ou avec une sous-requête:

 MERGE INTO bonuses D USING (SELECT employee_id, salary, department_id FROM employees WHERE department_id = 80) S ON (D.employee_id = S.employee_id) WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01 DELETE WHERE (S.salary > 8000) WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus) VALUES (S.employee_id, S.salary*.01) WHERE (S.salary <= 8000); 

Dans IBM DB2, la syntaxe fonctionnera également. Comme on dit , "sous le capot", cela se fera de la même manière que la construction UPDATE FROM .
Depuis 2008, MS SQL possède également MERGE .

Mais même derrière une seule syntaxe standard, le problème du choix parmi un nombre considérable de mécanismes et de méthodes de mise en œuvre commence. L'équipe doit travailler à différents niveaux d'isolement des transactions, avec différents algorithmes de verrouillage, en se concentrant sur un mode de fonctionnement hautement compétitif ou pas si bon. Et, comme vous pouvez le deviner, pour implémenter cette logique compliquée, vous devez toucher à de nombreux composants SGBD.

UPSERT, pseudo-MERGE

Il est clair que les développeurs de SGBD recherchaient des solutions de compromis, refusant de reproduire littéralement la syntaxe standard. Le plus de cette approche est la liberté. Vous pouvez utiliser des mécanismes organiques pour un SGBD particulier, vous pouvez optimiser la mise en œuvre pour les tâches que vous jugez les plus pertinentes pour vos utilisateurs.

Par exemple, dans MySQL, il existe une commande REPLACE qui fonctionne comme INSERT , mais si les nouvelles et anciennes lignes ont les mêmes valeurs dans l'index PRIMARY KEY ou UNIQUE , alors l'ancienne ligne est supprimée avant l'insertion de la nouvelle. Mais il y a aussi INSERT ... ON DUPLICATE KEY UPDATE où INSERT et UPDATE se produisent (au lieu de DELETE dans REPLACE ). Voici UPSERT . Et il y a INSERT IGNORE , qui n'effectue simplement pas l'insertion, sans lancer d'erreur (mais d'avertissement) sous certaines restrictions sur la table cible.

Chroniques de PG MERGE

Dans la communauté PostgreSQL, parler de MERGE a commencé en 2005 lorsque Jaime Casanova a demandé si quelqu'un dans la communauté avait commencé à développer MERGE . Peter Eisentraut a suggéré de discuter si PostgreSQL devrait développer l'une des options MERGE: similaire à l'implémentation MySQL, ou mieux pour diriger l'effort vers une version fonctionnellement légère du type MERGE d'Oracle. Mais vaut-il la peine de faire des efforts dans ce sens?

Au milieu d'une courte discussion, le protagoniste de ce récit Simon Riggs apparaît avec les mots:
MERGE est utile à la fois pour les systèmes OLTP et pour DW (Data Warehouse - les entrepôts de données, c'est-à-dire les applications analytiques où les requêtes complexes, mais les environnements et les données pas trop compétitifs sont rarement mis à jour, et s'ils sont mis à jour, généralement en gros morceaux. <...> Nous pouvons implémenter MERGE comme une variante de COPY FROM, ce sera très cool.

Tout le monde est d'accord: oui, cool. Plus précisément, presque tout: Stephen Frost : Je pense que je ne suis pas le seul à dire que j'ai besoin d'une norme MERGE à part entière et conforme.

Bruce Momjian a une proposition différente et plus pragmatique: il me semble que nous devons implémenter dans MERGE quelques options que nous pouvons implémenter, et dans le reste nous donnerons une erreur (et dans les cas où il sera nécessaire de bloquer la table entière). Et après avoir reçu les commentaires des utilisateurs et nous réfléchirons à la suite.

Mais jusqu'à présent, rien ne se passe.

La glace s'est brisée

En 2008, Simon Riggs a de nouveau exhorté à traiter avec MERGE - laquelle des façons de choisir (d'ici là, une nouvelle version de MERGE dans la norme SQL-2008, qui est encore en ébauche, est déjà en train d'apparaître). Il peint en détail à ce moment l'implémentation d'Oracle, IBM et MS SQL et la syntaxe alternative de MySQL et Teradata. Et un peu plus tard, il mentionne déjà le début des travaux dans 2ndQuadrant dans ce sens.

Peter Eisentraut écrit sur son blog : Bien sûr, Riggs est l'un des spécialistes les plus qualifiés, il peut diriger les travaux sur la mise en œuvre de MERGE.

Mais voici le premier virage inattendu: un étudiant est impliqué dans le problème - un participant au développement du programme GSoC , c'est-à-dire Google Summer of Code. Son nom est Boxuan Bxzhai - je ne prétends pas transcrire le nom de famille. Bientôt, il écrit que le travail est presque terminé.

Mais presque ne compte pas. Greg Smith de 2ndQuadrant (c'est-à-dire l'allié de Simon Riggs) écrit:
Nous avons donc un correctif dans le code dont une demi-douzaine de problèmes graves non résolus. Je me tais sur les petits. Les problèmes sont trop profonds pour finaliser le code du commitfest. Pendant ce temps, rien n'a été entendu de Boxuan depuis longtemps. Nous pourrions l'aider, mais où est-il? Qui est au courant?

Une discussion sur les chemins d'implémentation refait surface en 2014 , mais là encore rien ne se passe: il n'y a pas de code.

Enfin, dès 2017, Simon Riggs écrit:
Je travaille sur du code pour valider MERGE dans PostgreSQL version 11 . Nous utilisons les mêmes mécanismes qui sous-tendent le INSERT ON CONFLICT , qui fonctionne déjà, afin qu'aucune modification d'infrastructure ne soit nécessaire, en implémentant simplement la syntaxe en plus de ce qui est disponible. Mais j'écris mon code à partir de zéro, je n'utilise pas les développements précédents.

Nous parlons de Peter Geoghegan ( VMware ) implémenté à cette époque déjà dans la syntaxe alternative INSERT .. ON CONFLICT UPDATE 9.5 INSERT .. ON CONFLICT UPDATE , différent du standard SQL, mais toujours lié à MERGE et REPLACE dans MySQL.

Au début, le travail de Simon a rencontré des exclamations du travail de Nice! Cependant, Robert Haas , bien que favorable, met en garde contre d'éventuelles anomalies de sérialisation. Par exemple, pour gérer INSERT .. ON CONFLICT UPDATE , sans MERGE à sa base, c'est en quelque sorte plus calme.

UPSERT auteur de PostgreSQL UPSERT lui-même:
Je ne mélangerais pas le MERGE ON CONFLICT DO UPDATE et MERGE . <...> Pour charger de gros morceaux de données ( bulk load ), j'utiliserais, par exemple, l'algorithme de merge join . <...> En général, les avantages de MERGE seraient liés au fait que les connexions normales y fonctionneraient de la manière habituelle: nested loop, hash, merge . Et dans INSERT … ON CONFLICT il n'y a aucune jointure.

Haas: Comme Peter, je pense que si cela est fait de cette façon, alors un tel verrou fort lors de l'exécution d'une demande DML semble moyen. Il est peu probable que quiconque soit satisfait qu'une seule personne puisse travailler avec MERGE à la fois.

Pour ceux qui sont curieux: Geigan analyse les subtilités et les différences UPSERT entre MERGE et MERGE ici et ici (nous stockons la correspondance archivée de PostgreSQL sur notre site Web).

Simon résiste. Il fait appel à l'histoire récente. Par exemple, à propos de la section, ils ont également dit "une nouvelle syntaxe, rien de plus". Mais cela s'est avéré être une chose très utile. Mais je ne propose pas de réaliser tout de suite tout ce qu'il y a dans MERGE. Nous ferons la même chose qu'avec le partitionnement - nous divisons le développement en phases.

Et un autre argument, à mon avis, est très convaincant: bien. Mais choisissons. Je propose une option pratique. 10 ans viendront bientôt de la première tentative sérieuse de développer MERGE . N'est-il pas temps de commencer à faire quelque chose, d'obtenir une solution utile, au lieu d'attendre encore 10 ans de la solution parfaite? En supposant que cela existe.

Enfin, le patch arrive dans la communauté. Quelle date? Imaginez s'il vous plaît. Non, ils n'ont pas deviné: Simon l'envoie le 30 décembre 2017. Et stipule qu'il s'agit d'un correctif WIP, c'est-à-dire Work in Progress - un correctif en cours.

Simon, janvier:
Le patch est terminé sans aucun bogue spécial. 1200 lignes de code plus tests et documentation. Je vais le confier à ce commitfest, et nous compléterons le RLS (Row Level Security - protection au niveau de l'enregistrement) et le support de partitionnement plus tard.

Caste des commissions

Ici, nous devons prendre un pas de côté et expliquer le rôle du commissaire dans la communauté. Les fonctions du commissaire, c'est-à-dire celui qui est autorisé à accepter le patch dans la prochaine version, ont historiquement changé. Il était une fois, quand il y avait peu de développeurs, le droit de s'engager était généreusement distribué. Par exemple, le célèbre (dans un domaine complètement différent) Julian Assange a reçu le titre de commandant, étant l'auteur de seulement six patchs. Maintenant, ce n'est pas facile de devenir commissaire, il n'y a pas de parvenus sur la liste de quelques dizaines de personnes. Boyus Momdjan ( EnterpriseDB ) a 13 363 commits, Tom Lane (Tom Lane, Crunchy Data ) 13127, Robert Haas ( EnterpriseDB ) - 2074. Soit dit en passant, le seul committer de Russie est Fedor Sigaev ( Postgres Professional ) avec ses 383 commits . Simon Riggs en a lui-même 449. Je le répète: en tant que commissaire, il a suffisamment d'autorité pour prendre et commettre des correctifs - lui et ses employés. Une autre chose est que cela ne vaut guère la peine de le faire, négligeant franchement les opinions des autres principaux comités de sommités. Ils peuvent également priver le statut de commissaire, mais au moins ils revert patch.

Fracture au combat

Bien sûr, dans le patch "désespéré", fait, en général, à la hâte, ils trouvent de nouvelles erreurs. Les nouvelles versions roulent en réponse.

Fin janvier, un nouveau personnage apparaît: le développeur de 2ndQuadrant Pavan (son nom est tout le monde par son nom; complètement Pavan Deolasee). Maintenant, la communauté a affaire à un tandem: Pavan envoie de nouvelles versions et merci pour les critiques, et Simon les brise avec une pression marketing remarquable.

Haas: Je ne pense pas que cela vaut la peine de prendre des décisions unilatérales sur l'exclusion des fonctionnalités qui fonctionnent partout. Si nous convenons que certaines fonctionnalités ne seront pas incluses dans ce patch - c'est une chose. Et c'est complètement différent que dans les commentaires à cette occasion, tout le monde ait exprimé son désaccord. Et nous n'avons en fait pas entendu les raisons pour lesquelles ces fonctionnalités devraient être exclues.

La logique a été présentée comme suit:

• a priori, il y a de sérieux problèmes car ils ne peuvent qu'être dans les évolutions du style «attaque de cavalerie».
• La prise en charge de fonctionnalités même importantes telles que le nouveau partitionnement dans les versions 10-11, CTE (Common Table Expressions = WITH queries) ou RLS (Row Level Security) peut être terminée même après l'acceptation du correctif dans la version actuelle, mais uniquement si l'architecture proposée convient pour la construction par le haut sa fonctionnalité souhaitée.

Le deuxième Peter Geigan formule ceci:
Habituellement, je fais attention au support de diverses fonctionnalités, car si c'est le cas, cela renforce la croyance générale que le design est fait comme il se doit . Et si de tels problèmes sont causés par la prise en charge des expressions WITH [c'est-à-dire CTE ], j'ai alors l'idée que l'architecture sous-jacente est telle qu'elle causera des problèmes ici et là.

Pendant ce temps, l'heure X (le dernier comité) approche et les nuages ​​au-dessus de MERGE se rassemblent. Ce n'est pas que les pères fondateurs aient spécifiquement recherché de sérieux problèmes dans l'architecture des patchs réalisés par Simon puis Pavan. Je n'ai pas eu à chercher de problèmes, ils se sont volontairement ouverts.

Le dénouement approche

L'intrigue s'accélère. Malgré l'attitude cool des autres comités envers son entreprise, le 2 avril, Simon décide de valider la commande SQL SQL 2016 , ajoute les fichiers, Depesz (Hubert Lubachevsky) parvient à l' annoncer sur son blog, mais le même jour, Simon annule tout parce que des erreurs.

Le lendemain, engagez-vous à nouveau en ajoutant WITH support.

En réponse, les allégations sont vraiment graves. Andres Freund ( EnterpriseDB ) écrit:
L'architecture de MERGE dans l'analyseur et l'exécuteur testamentaire ne m'a pas impressionné de manière fiable. La création de jointures cachées lors de l'analyse syntaxique est une très mauvaise idée. Cette structure de l'exécuteur doit être complètement modifiée.

Tom Lane:
La conception de l'arbre d'analyse est faible.



Vous surchargez la fonction InsertStmt , poursuit-il, elle ne fait pas du tout INSERT , mais elle a aléatoirement les mêmes champs que celui d'origine. Et pas tous, mais certains. C'est mauvais, cela mène à la confusion.

Ajoutons des observations de Fedor Sigayev :
Dans l'analyseur, des nœuds INSERT liés à MERGE sont apparus, accrochés à un tas de champs supplémentaires. Si vous regardez le plan d'exécution dans ANALIZE , vous ne comprendrez pas immédiatement si vous avez affaire à un INSERT régulier ou à MERGE : pour comprendre, vous devez regarder des champs supplémentaires.


Simon, calmement: OK, nous allons changer cela et envoyer un nouveau fichier demain .
Haas: Je suis d'accord avec Peter. Le choix de l'architecture est infructueux.

Simon n'abandonne pas. Le 6 avril, en réponse aux critiques de Tom Lane, commet un nouveau patch tel que modifié dans l'analyseur.

Négociation et remise

Bruce Momjan 6 avril :
Je veux noter que les gens ne vous ont pas demandé de travailler dur pour réparer quelque chose de toute urgence. Ils vous ont demandé de retirer le patch. Vous pouvez bien sûr travailler dur, en espérant qu'ils changeront d'avis, mais - encore une fois - ils ne vous ont pas posé de questions à ce sujet.

Simon: Si Tom [Lane] et Andres [Freund] pour les quelques jours restants estiment toujours que leurs craintes n'ont pas été dissipées, je serai heureux de faire reculer le patch sans plus tarder.

Tom Lane: Je vote toujours pour que le patch soit annulé. Même s'il était parfait maintenant, les gens n'ont plus le temps d'en être convaincus - à la gorge d'autres questions urgentes.

C’est tout.

Simon a dit OK, et la bataille de MERGE terminée. Tous les correctifs sont pompés, le sujet a été déplacé vers le prochain commitfest avec le statut "En attente de la fin de l'auteur". Les participants au spectacle ont fait la paix.


Cependant, à en juger par la correspondance des dernières semaines, une certaine tension semble persister.

Moralité promise

• Heureusement, la communauté PostgreSQL dispose de mécanismes naturels et formels pour le filtrage (presque) sans conflit des tentatives de solutions immatures. Même s'ils sont poinçonnés par des développeurs respectés au rang de chef d'entreprise, dont la contribution au développement de PostgreSQL est énorme. Et les clients qui manquent de fonctionnalités poussent à investir.
• Malheureusement, la communauté cale souvent. Elle est inertielle dans l'adoption d'évolutions pertinentes, même sans ambiguïté. Parfois, le perfectionnisme irrationnel est inclus. L'expérience de Postgres Professional, où je travaille, le confirme. Nous avons perforé un patch important et important d' indices INCLUDE pendant 3 ans. Une série de correctifs utiles pour travailler avec JSON / JSONB attend toujours. L'expression "donnez votre développement à la communauté" ne signifie pas vraiment donner, mais donner des coups de poing : l'invité est accueilli à bras ouverts et escorté en quarantaine.

PS: Avis de non-responsabilité de l'auteur : nous voulions simplement montrer un morceau de vie communautaire. Toutes les correspondances de noms sont aléatoires :)
PPS: Samurai Natalia Levshina .