Erreurs typiques lors de l'utilisation de PostgreSQL. 2e partie

Nous continuons à publier des vidéos et des transcriptions des meilleurs reportages de la conférence PGConf.Russia 2019 . Dans la première partie de l’intervention d’ Ivan Frolkov , il s’agissait de dénominations incohérentes, de contraintes, d’où il valait mieux concentrer la logique - dans la base de données ou dans l’application. Dans cette partie, vous trouverez la gestion des erreurs d'analyse, l'accès simultané, les opérations non annulables, CTE et JSON.



Je vais raconter une telle histoire. Notre client déclare: «La base de données fonctionne lentement et notre application est au service de la population. Nous avons peur qu'ils nous élèvent ici pour des fourchettes. » Il s'est avéré qu'ils avaient beaucoup de processus inactifs dans l' état de transaction . L'application a démarré la transaction, ne fait rien, mais la transaction n'est pas terminée. Si vous interagissez avec certains services externes, alors, en principe, c'est une situation normale. Une autre chose est que si votre état de transaction inactif dure longtemps (c'est déjà suspect depuis plus d'une minute), alors c'est mauvais parce que PostgreSQL n'aime vraiment pas les transactions longues: VACUUM ne pourra pas effacer toutes les lignes qu'il pourrait voir, et se bloquer pendant longtemps transaction bloque efficacement VACUUM. Les tableaux commencent à gonfler, les index deviennent de moins en moins efficaces.



Dans ce cas, les gens n'ont pas rédigé correctement les demandes et ont reçu des produits cartésiens - ces demandes ont été traitées pendant plusieurs jours. Eh bien, l'utilisateur, il va appuyer sur le bouton, attendre le résultat et, s'il n'y a pas de résultat, appuyer à nouveau sur le bouton.

Mais cela n'explique pas pourquoi ils ont autant de processus inactifs dans les transactions . Et ils sont apparus dans la situation suivante: l'application rampe dans la base de données, démarre la transaction, rampe sur un service externe, y obtient une erreur, puis tout s'écroule, nous imprimons dans le journal de suivi de la pile et nous nous calmons. La connexion reste abandonnée, suspendue et interférant.

Que faire à ce sujet? Tout d'abord, vous devez toujours gérer les erreurs. Si une erreur vous arrive, veuillez ne pas l'ignorer. C’est bien si PostgreSQL perd la connexion: cela annule la transaction, nous survivons. Je m'arrête là-dessus. Eh bien, s'il y a un code qui n'a pas le temps de le modifier du tout, alors nous avons encore le max inactif dans la transaction - vous pouvez le mettre, et il supprimera simplement les transactions inactives.



Un cas typique de gestion des erreurs est le suivant: EXCEPTION WHEN OTHERS THAN NULL. Une fois, nous discutions avec un collègue de la terminologie. J'ai dit que cela se traduisait par "tout brûler avec une flamme bleue", et il veut dire "tout est perdu." Si quelque chose de mauvais s'est passé avec nous, alors, même si tout a grondé le journal, c'est toujours mieux qu'un silence complet - comme ici.



Si vous ne savez pas quoi faire de l'erreur, ne l'interceptez pas. Une pratique très courante: ils ont détecté une erreur, l'ont enregistrée et ont continué comme si de rien n'était. Si, encore une fois, vous effectuez des transactions monétaires et que vous avez une erreur que vous avez ignorée, les résultats peuvent être imprévisibles. Dans les années 90, ils pouvaient par exemple être emmenés dans la forêt dans le tronc. Maintenant, les temps sont devenus plus doux, mais aussi pas très agréables.



Si nous effectuons l'opération sur le client, nous renvoyons généralement la valeur: tout s'est déroulé avec succès ou sans succès. Et nous traitons chaque erreur. J'ai vu comment les gens ont spécialement écrit le code plpgsql , où ils ont détecté une erreur, ont écrit au journal que, disent-ils, oui, il y avait une erreur et assez grossier, ils ont inséré leur texte de message. Mais SQLSTATE n'est pas revenu. C'est toujours fait, donc s'ils ont oublié de vérifier quelque chose, alors ils ont commencé à avoir des problèmes.

Tout le monde, pour une raison quelconque, a peur des exceptions - à la fois dans plpgsql et dans d'autres langues. Et si vous n'inventez pas quelque chose de vous-même, mais utilisez les fonctionnalités standard du langage, tout fonctionne généralement bien. En particulier, ce problème se produit souvent lorsque la connexion tombe. Il est tombé, le processus est inactif dans la transaction , la base de données se remplit, les performances baissent. Soit dit en passant, une telle transaction peut toujours laisser des verrous, mais pour une raison quelconque, ce n'est pas si courant. Par conséquent, ajoutez enfin des erreurs au code de traitement et là, nettoyez la connexion et rendez-la au serveur.



De plus, si vous avez des contraintes bien définies, vous pouvez lever une exception non pas depuis la base de données, mais depuis l'application lors du traitement de l'erreur. Au printemps, il y a une traduction d'exception , en php , respectivement, set_exception_handler . Faites attention aux outils que votre framework vous fournit, ils y sont apparus pour une raison.

Donc: n'attrapez pas l'erreur avec laquelle vous ne savez pas quoi faire; nommer les erreurs avec soin et précision; classer les erreurs.



Personnellement, je classe selon ces critères: l'opération peut être répétée (par exemple, nous avons eu une impasse); l'opération ne peut pas être répétée, elle est déjà terminée; l'opération ne peut pas être effectuée en principe.

Paradoxalement, du point de vue de l'application, les situations où un blocage se produit, lorsque la connexion est perdue et lorsque nous n'avons plus d'argent à payer sont les mêmes situations: le gestionnaire d'erreurs tentera de refaire l'opération après un certain temps.



En revanche, ce qu'ils écrivent dans la candidature, en général, n'est pas mon affaire: je suis engagé dans la base. Je vous invite simplement à gérer les erreurs avec soin, sinon: inactif dans la transaction, lignes verrouillées, bases de données enflées, etc.

La plupart des développeurs pensent qu'ils travaillent uniquement avec la base de données et que leur application effectue des opérations strictement séquentiellement. Et c'est un plus pour tous les SGBD relationnels car, curieusement, tout fonctionne, en règle générale, très bien, même avec le niveau d'isolement standard READ COMMITTED, et non SERIALIZABLE. Dans le même temps, des situations se produisent lorsque des mises à jour sont perdues: l'une charge le formulaire, l'autre charge le même formulaire, l'une a écrit et enregistré, l'autre a sauvegardé l'ancien - les modifications ont été effacées. Le premier est venu jurer: "comment ça, j'ai tellement écrit, et tout est perdu."



D'après mon expérience: une fois par semaine le vendredi, deux gestionnaires ont effectué des paiements. Ils devraient
étaient en train de changer à chaque fois, mais, néanmoins, une fois grimpé en même temps et effectué deux paiements par personne. Si vous avez au moins une chance d'erreur d'accès concurrentiel, cela se produira tôt ou tard. La question est quand.

De plus, j'attire votre attention sur les limites. J'ai vu à plusieurs reprises comment ils ont essayé de donner un caractère unique aux déclencheurs. Vous ne fournirez pas l'unicité dans le tableau avec des déclencheurs. Vous devrez soit bloquer toute la table, soit effectuer d'autres gestes complexes. Vous tomberez dessus tôt ou tard.



Quelques fois, je suis tombé sur une chose complètement cauchemardesque: un service Web externe est appelé à partir de la base de données. Certaines opérations ont modifié les entités externes. Cela est mauvais car une transaction peut être annulée dans la base de données, mais les opérations sur le service distant ne seront pas rejetées.

Un point encore plus subtil est l'impasse. Imaginons: nous traitons une transaction, appelons un service Web externe, modifions quelque chose, après quoi nous obtenons un blocage, et nous annulons, puis nous essayons de refaire l'opération, appelons à nouveau, dans de bonnes circonstances, un blocage se produit à nouveau, à nouveau revenir en arrière - il peut
arriver plusieurs fois (je suis tombé sur quelques centaines de répétitions). Et maintenant, vous traitez ces blocages plus ou moins correctement, répétez les opérations et constatez soudainement que vous avez payé un double montant à quelqu'un dans les deux mois.



J'ai rencontré des services de paiement qui avaient une mauvaise API: «payer tel ou tel montant à tel ou tel utilisateur»; la fonction renvoie le résultat - payé / non payé. Premièrement, il y a un problème dans le cas d'une répétition, et deuxièmement, on ne sait pas quoi faire si la connexion est interrompue. Pour une raison quelconque, très peu de gens se soucient de ce sujet non plus.



Un exemple est sur la diapositive: une telle opération devrait être effectuée en deux étapes: comme si un avertissement - «nous ferons quelque chose maintenant»; l'opération elle-même.



Si nous interrompons soudainement - vous ne savez jamais, coupez l'alimentation - nous pouvons recommencer l'opération. Si nous mourons dans la deuxième étape, alors, dans le monde entier, la deuxième fois, nous ne le ferons pas, et cela peut être démonté manuellement. En fait, la grande majorité de ces opérations fonctionnent normalement pour la première fois, mais ces mesures ne sont pas des fabrications théoriques. Tout peut fonctionner normalement pendant des mois, et soudain, l'administrateur commence à devenir plus sage avec le réseau, le service commence à clignoter activement - et les problèmes ont commencé.


Il existe 4 types d'opérations non annulables sur la diapositive. Ce dernier est des opérations non idempotentes. C'est un cas très triste. Au début, je parlais d'un camarade qui faisait tout sur les déclencheurs précisément pour assurer l'idempotence de ses opérations.


Lors de la conférence, les gens parleront des expressions de la table commune, de leur qualité. Malheureusement, les CTE PostgreSQL ne sont pas gratuits: ils ont besoin de work_mem pour eux-mêmes. Si vous avez un petit échantillon, alors, en général, ça va. Et si vous l'avez soudainement gros, alors vos problèmes commencent. Les gens utilisent très souvent CTE comme une sorte de mini-vues - afin que vous puissiez en quelque sorte structurer l'application. CTE est très demandé.





Vous pouvez créer des vues temporaires, mais, malheureusement, chacune prend une ligne dans pg_class, et si cela est très activement utilisé, il peut y avoir des problèmes avec le gonflement du répertoire.
Dans ce cas, vous pouvez conseiller de faire une vue paramétrée, ou de former dynamiquement une requête, mais, malheureusement, dans PostgreSQL de l'intérieur, ce n'est pas très cool.



JSON est généralement parlé dans d'excellentes tonalités, mais il y a une tendance dans l'application JSON à pousser quoi que ce soit. En principe, tout fonctionne bien. D'un autre côté, les données sont récupérées à partir de JSON, bien que rapidement, mais pas aussi rapidement qu'à partir des colonnes. Pire encore, si vous avez un gros JSON et qu'il est émis dans TOAST. Pour obtenir JSON à partir de là, vous devez le récupérer sur TOAST.

Si toutes les colonnes sont en JSON, un index fonctionnel est même construit dessus, alors vous devez toujours le sortir de là. Cela devient encore pire avec un grand volume, lorsque la base de données est volumineuse, lorsque vous avez une analyse d'index bitmap . Ensuite, nous avons des liens non pas vers des chaînes, mais vers la page entière, et afin de comprendre ce qu'il faut retirer de la page, PostgreSQL fera Recheck , c'est-à-dire qu'il lève une ligne de TOAST et vérifie si cette valeur est là ou non, et donc déjà saute ou ne saute pas. Si avec de petites colonnes cela fonctionne bien, alors avec JSON c'est un gros problème. Il n'est pas nécessaire de se laisser trop emporter par les JSON.



- Comment vérifier quand plusieurs utilisateurs travaillent avec une chaîne? Quelles sont les options?

- Tout d'abord, vous pouvez soustraire les valeurs de toutes les colonnes et vous assurer qu'elles n'ont pas changé avant d'afficher la ligne dans le formulaire. La deuxième option, plus pratique: calculer le hachage du tout
colonnes, d'autant plus que les colonnes peuvent être grandes et épaisses. Et le hachage n'est pas si gros.

- Vous dites que les contraintes doivent être appelées de bons noms afin que l'utilisateur puisse comprendre ce qui se passe. Mais il y a une limite de 60 caractères par nom de contrainte. Ce n'est souvent pas suffisant. Comment y faire face?

- Je pense que de lutter par retenue. Dans PostgreSQL, il s'agit d'un type spécial de longueur 64. En principe, vous pouvez recompiler sur une longueur plus longue, mais ce n'est pas très bon.

- Dans le rapport, vous nous avez intrigués par le fait que nous devons faire quelque chose avec les archives. Quel mécanisme est considéré comme le plus correct pour un archivage obsolète?

- Comme je l'ai dit au tout début, avec diligence, tout fonctionne. Quelle méthode vous convient le mieux, alors utilisez-la.


Calendrier: la partie 2 du rapport commence à 25 h 16.

- Il existe une certaine procédure que plusieurs utilisateurs appellent en parallèle. Comment limiter l'exécution parallèle de cette procédure, c'est-à-dire construire tous
utilisateurs dans la file d'attente de sorte que jusqu'à ce que l'un termine la procédure, le suivant ne peut pas commencer à l'utiliser?

- Précisément la procédure? Ou est-ce une transaction suffisante?

- C'est la procédure qui est appelée dans une transaction.

- Vous pouvez mettre un verrou sur l'objet. Ce serait difficile si vous aviez une condition, disons, pas plus de 3 en même temps. Mais c'est réalisable. J'utilise généralement des verrous transactionnels, mais des verrous non transactionnels sont également possibles.

- Je voudrais encore revenir aux données d'archives. Vous avez parlé
archiver les options de stockage afin que les données de l'application soient également disponibles. Il m'est venu à l'esprit de créer simplement une base de données d'archives distincte. Quelles sont les autres options?

- Oui, vous pouvez créer une base de données d'archives. Vous pouvez écrire une fonction et l'encapsuler dans une vue. Dans une fonction, vous pouvez faire tout ce qu'il faut: vous pouvez aller à la base de données d'archives, vous pouvez récupérer des fichiers sur le disque, vous pouvez aller à un service Web externe, vous pouvez combiner tout cela, vous pouvez générer vous-même des données aléatoires - choix limité que par l'imagination.

- À la question sur les données d'archives: vous pouvez utiliser des partitions - de nouvelles puces de la 11e version, lorsque nous faisons partitionner la table entière, puis détaillons la partition et la laissons comme archive. Il est également accessible.

"Bien sûr, pourquoi pas." Je cède la place à l'orateur suivant.