On peut depuis bien longtemps exporter des résultats de requête
en CSV, soit avec COPY (SELECT ...) TO STDOUT CSV qui est une
commande SQL, soit via la méta-commande \copy de psql qui appelle
en sous-main COPY en gérant le flux de données côté client.
Mais il reste quelques situations non couvertes par cette
fonctionnalité, et c’est pourquoi dans PostgreSQL 12, le CSV a été
ajouté aux formats de sortie en général, c’est-à-dire qu’il est disponible
pour toutes les commandes produisant des résultats tabulaires.
On peut opter pour ce format avec la commande \pset format csv, ou en ligne de
commande avec l’option --csv.
Concrètement, quels sont les cas où on peut en avoir
besoin préférentiellement à \copy?
quand on récupère les données via une méthode non supportée par COPY,
par exemple un curseur:
on peut faire fetch from c mais pas copy (fetch from c) to stdout csv.
quand le résultat est produit par une méta-commande plutôt qu’une requête
directe: \crosstabview, \l, \d etc…
Jusqu’à présent, les besoins d’export simple au format tabulaire sont
souvent couverts par le format “unaligned” et son séparateur de champ
paramétrable “fieldsep” (option -F).
Mais ce format a deux faiblesses:
Exemple:
$ query="select 'ab,cd' as col1, 'ef'||chr(10)||'gh' as col2"
$ psql -AtF',' -c "$query"
ab,cd,ef
gh
Le problème est qu’en partant de cette sortie, on ne sait plus où démarrent et finissent
les champs ni combien d’enregistrements il y avait au départ.
Alors qu’il y a un seul enregistrement et deux colonnes, cette sortie
pourrait aussi bien représenter une seule colonne avec ab,cd,ef
en valeur du premier enregistrement et gh pour le second.
Le “vrai” CSV règle ce problème:
$ psql --csv -t -c "$query"
"ab,cd","ef
gh"
La règle qui fait toute la différence est que des guillemets sont ajoutés pour encadrer un champ dès qu’il contient le séparateur ou un saut de ligne, ou un guillemet (dans ce dernier cas les guillemets intra-champs sont doublés).
En principe, les scripts qui importent du CSV sont capables de relire ce contenu en restituant les valeurs de départ dans tous les cas, y compris les champs multi-lignes.
Le CSV peut aussi être utilisé comme intermédiaire pour produire au final un autre format, que psql ne gèrerait pas directement.
Il faut pour ça intercaler un script entre la sortie de psql et une commande de transformation de CSV vers cet autre format. Pour la partie lecture des données CSV, le script sera souvent simple parce que la plupart des languages de script sont dotés d’analyseurs CSV déjà faits, l’intérêt de ce format étant justement son ubiquité.
Voici par exemple un programme Perl csv-to-markdown qui transforme
du CSV UTF-8 vers le format markdown tel qu’utilisé notamment par github,
et destiné à produire du HTML.
Il ne faudra probablement pas plus de code pour faire la même chose
en Ruby, Python ou un language comparable, et aussi certainement
guère plus d’effort pour gérer d’autres formats que markdown, ou
encore personnaliser cette sortie.
#!/usr/bin/perl
use Text::CSV;
use open qw( :std :encoding(UTF-8) );
my $csv = Text::CSV->new({ binary => 1, eol => $/ });
sub do_format {
s/&/&/g;
s/</</g;
s/>/>/g;
s/\n/<br>/g;
s/\|/|/g;
return $_;
}
my $header = $csv->getline(STDIN);
for (@{$header}) {
$_ = do_format($_);
}
print join ('|', @{$header}), "\n";
print join ('|', map { "---" } @{$header}), "\n";
while (my $row = $csv->getline(STDIN)) {
my @contents = map { do_format($_) } @{$row};
print join('|', @contents), "\n";
}Et voici quelques possibilités d’appeler ce programme depuis psql pour lui faire sortir du format markdown:
\pset format csv
-- méthode 1 avec \g (par requête)
select 'ab,cd' as col1, 'ef'||chr(10)||'gh' as col2
\g |csv-to-markdown >/tmp/table1.md
-- méthode 2 avec \o (pour toutes les requêtes et métacommandes à suivre)
\o |csv-to-markdown >/tmp/table2.md
select 'ab,cd' as col1, 'ef'||chr(10)||'gh' as col2;
-- revient à l'affichage normal (aligned) et à l'écran
\a \o
Si on a une seule requête à sortir, on peut aussi l’écrire en une seule ligne de commande.
$ psql --csv -c "$query" | csv-to-markdown >/tmp/table3.md
Dans tous les cas ci-dessus, le résultat produit est:
col1|col2
---|---
ab,cd|ef<br>gh
où les deux champs sont bien interprétés comme ils doivent.
Cet article fait partie d’une série d’article sur la beta de PoWA 4, et décrit les changements présents dans powa-archivist.
Pour plus d’information sur cette version 4, vous pouvez consulter l’article de présentation général.
Tout d’abord, il faut savoir qu’il n’y a pas d’upgrade possible depuis la v3
vers la v4, il est donc nécessaire d’effectuer un DROP EXTENSION powa si vous
utilisiez déjà PoWA sur vos serveurs. Cela est du au fait que la v4 apporte
de très nombreux changements dans la partie SQL de l’extension, ce qui en
fait le changement le plus significatif dans la suite PoWA pour cette nouvelle
version. Au moment où j’écris cet article, la quantité de changements apportés
dans cette extension est :
CHANGELOG.md | 14 +
powa--4.0.0dev.sql | 2075 +++++++++++++++++++++-------
powa.c | 44 +-
3 files changed, 1629 insertions(+), 504 deletions(-)L’absence d’upgrade ne devrait pas être un problème en pratique. PoWA est un outil pour analyser les performances, il est fait pour avoir des données avec une grande précision mais un historique très limité. Si vous cherchez une solution de supervision généraliste pour conserver des mois de données, PoWA n’est définitivement pas l’outil qu’il vous faut.
En ce qui concerne les changements à proprement parler, le premier petit changement est que le background worker n’est plus nécessaire pour le fonctionnement de powa-archivist, car il n’est pas utilisé pour le mode distant. Cela signifie qu’un redémarrage de PostgreSQL n’est plus nécessaire pour installer PoWA. Bien évidemment, un redémarrage est toujours nécessaire si vous souhaitez utiliser le mode local, en utilisant le background worker, or si vous voulez installer des extensions additionelles qui nécessitent elles-même un redémarrage.
Ensuite, comme PoWA requiert un peu de configuration (fréquence des snapshot,
rétention des données et ainsi de suite), certaines nouvelles tables sont
ajouter pour permettre de configurer tout ça. La nouvelle table powa_servers
stocke la configuration de toutes les instances distantes dont les données
doivent être stockées sur cette instance. Cette instance PoWA locale est
appelée un serveur repository (qui devrait typiquement être dédiée à
stocker des données PoWA), en opposition aux instances distantes qui sont
les instances que vous voulez monitorer. Le contenu de cette table est tout ce
qu’il y a de plus simple :
\d powa_servers
Table "public.powa_servers"
Column | Type | Collation | Nullable | Default
-----------+----------+-----------+----------+------------------------------------------
id | integer | | not null | nextval('powa_servers_id_seq'::regclass)
hostname | text | | not null |
alias | text | | |
port | integer | | not null |
username | text | | not null |
password | text | | |
dbname | text | | not null |
frequency | integer | | not null | 300
powa_coalesce | integer | | not null | 100
retention | interval | | not null | '1 day'::intervalSi vous avez déjà utilisé PoWA, vous devriez reconnaître la plupart des options de configuration qui sont maintenant stockées ici. Les nouvelles options sont utilisées pour décrire comment se connecter aux instances distances, et peuvent fournir un alias à afficher sur l’UI.
Vous avez également probablement remarqué une colonne password. Stocker un mot de passe en clair dans cette table est une hérésie pour n’importe qui désirant un minimum de sécurité. Ainsi, comme mentionné dans la section sécurité de la documentation de PoWA , vous pouvez stocker NULL pour le champ password et à la place utiliser n’importe laquelle des autres méthodes d’authentification supportée par la libpq (fichier .pgpass, certificat…). Une authentification plus sécurisée est chaudement recommandée pour toute installation sérieuse.
Une autre table, la table powa_snapshot_metas, est également ajoutée pour
stocker quelques métadonnées concernant les informations de snapshot pour
chaque serveur distant.
Table "public.powa_snapshot_metas"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------------------------------------
srvid | integer | | not null |
coalesce_seq | bigint | | not null | 1
snapts | timestamp with time zone | | not null | '-infinity'::timestamp with time zone
aggts | timestamp with time zone | | not null | '-infinity'::timestamp with time zone
purgets | timestamp with time zone | | not null | '-infinity'::timestamp with time zone
errors | text[]Il s’agit tout simplement d’un compteur pour compter le nombre de snapshots effectués, un timestamp pour chaque type d’événement survenu (snapshot, aggrégation et purge) et un tableau de chaîne de caractères pour stocker toute erreur survenant durant le snapshot, afin que l’UI pour l’afficher.
Bien que ces tables soient très simples, une API SQL basique est disponible pour déclarer de nouveaux serveurs et les configurer. 6 fonctions de bases sont disponibles :
powa_register_server(), pour déclarer un nouveau servuer distant, ainsi
que la liste des extensions qui y sont disponiblespowa_configure_server() pour mettre à jour un des paramètres pour le
serveur distant spécifié (en utilisant un paramètre JSON, où la clé est
le nom du paramètre à changer et la valeur la nouvelle valeur à utiliser)powa_deactivate_server() pour désactiver les snapshots pour le serveur
distant spécifiqué (ce qui concrètement positionnera le paramètre
frequency à -1)powa_delete_and_purge_server() pour supprimer le serveur distant
spécifié de la liste des serveurs et supprimer toutes les données associées
aux snapshotspowa_activate_extension(), pour déclarer qu’une nouvelle extension est
disponible sur le serveur distant spécifiépowa_deactivate_extension(), pour spécifier qu’une extension n’est plus
disponible sur le serveur distant spécifiéToute action plus compliquée que ça devra être effectuée en utilisant des requêtes SQL. Heureusement, il ne devrait pas y avoir beaucoup d’autres besoins, et les tables sont vraiment très simple donc cela ne devrait pas poser de soucis. N’hésitez cependant pas à demander de nouvelles fonctions si vous aviez d’autres besoins. Veuillez également noter que l’UI ne vous permet pas d’appeler ces fonctions, puisque celle-ci est pour le moment entièrement en lecture seule.
Puisque les métriques sont maintenant stockées sur une instance PostgreSQL différente, nous avons énormément changé la façon dont les snapshots (récupérer les données fournies par une extensions statistique et les stockées dans le catalogue PoWA de manière à optimiser le stockage) sont effectués.
La liste de toutes les extensions statistiques, ou sources de données, qui
sont disponibles sur un serveur (soit distant soit local) et pour
lesquelles un snapshot devrait être effectué est stockée dans une table
appelée powa_functions:
Table "public.powa_functions"
Column | Type | Collation | Nullable | Default
----------------+---------+-----------+----------+---------
srvid | integer | | not null |
module | text | | not null |
operation | text | | not null |
function_name | text | | not null |
query_source | text | | |
added_manually | boolean | | not null | true
enabled | boolean | | not null | true
priority | numeric | | not null | 10Un nouveau champ query_source a été rajouté. Celui-ci fournit le nom de la
fonction source, nécessaire pour la compatibilité d’une extension
statistique
avec les snapshots distants. Cette fonction est utilisée pour exporter les
compteurs fournis par cette extension sur un serveur différent, dans une table
transitoire dédiée. La fonction de snapshot effectuera alors le snapshot
en utilisant automatiquement ces données exportées plutôt que celles fournies
par l’extension statististique locale quand le mode distant est utilisé. Il
est à noter que l’export de ces compteurs ainsi que le snapshot distant est
effectué automatiquement par le nouveau daemon
powa-collector
que je présenterai dans un autre article.
Voici un exemple montant comment PoWA effectue un snapshot distant d’une liste de base données. Comme vous allez le voir, c’est très simple ce qui signifie qu’il est également très simple d’ajouter cette même compatibilité pour une nouvelle extension statistique.
La table transitoire:
Unlogged table "public.powa_databases_src_tmp"
Column | Type | Collation | Nullable | Default
---------+---------+-----------+----------+---------
srvid | integer | | not null |
oid | oid | | not null |
datname | name | | not null |Pour de meilleurs performances, toutes les tables transitoires sont non journalisées (unlogged), puisque leur contenu n’est nécessaire que durant un snapshot et sont supprimées juste après. Dans cet examlple, la table transitoire ne stocke que l’identifiant du serveur distant correspondant à ces données, l’oid ainsi que le nom de chacune des bases de données présentes sur le serveur distant.
Et la fonction source :
CREATE OR REPLACE FUNCTION public.powa_databases_src(_srvid integer,
OUT oid oid, OUT datname name)
RETURNS SETOF record
LANGUAGE plpgsql
AS $function$
BEGIN
IF (_srvid = 0) THEN
RETURN QUERY SELECT d.oid, d.datname
FROM pg_database d;
ELSE
RETURN QUERY SELECT d.oid, d.datname
FROM powa_databases_src_tmp d
WHERE srvid = _srvid;
END IF;
END;
$function$Cette fonction retourne simplement le contenu de pg_database si les données
locales sont demandées (l’identifiant de serveur 0 est toujours le serveur
local), ou alors le contenu de la table transitoire pour le serveur distant
spécifié.
La fonction de snapshot peut alors facilement effectuer n’importe quel
traitement avec ces données pour le serveur distant voulu. Dans le cas de la
fonction powa_databases_snapshot(), il s’agit simplement de synchroniser la
liste des bases de données, et de stocker le timestamp de suppression si une
base de données qui existait précédemment n’est plus listée.
Pour plus de détails, vous pouvez consulter la documentation concernant l’ajout d’une source de données dans PoWA, qui a été mise à jour pour les spécificités de la version 4.
PoWA 4: nouveautés dans powa-archivist ! was originally published by Julien Rouhaud at rjuju's home on June 05, 2019.
PoWA 4 est disponible en beta.
Le nouveau mode remote est la plus grosse fonctionnalité ajoutée dans PoWA 4, bien qu’il y ait eu d’autres améliorations.
Je vais décrire ici ce que ce nouveau mode implique ainsi que ce qui a changé sur l’UI.
Si de plus amples détails sur le reste des changements apportés dans PoWA 4 vous intéresse, je publierai bientôt d’autres articles sur le sujet.
Pour les plus pressés, n’hésitez pas à aller directement sur la démo v4 de PoWA, très gentiment hébergée par Adrien Nayrat. Aucun authentification n’est requise, cliquez simplement sur “Login”.
Cette fonctionnalité a probablement été la plus fréquemment demandée depuis que PoWA a été publié, en 2014. Et c’est pour de bonnes raisons, car un mode local a quelques inconvénients.
Tout d’abord, voyons comment se présentait l’architecture avec les versions 3 et antérieures. Imaginons une instance contenant 2 bases de données (db1 et db2), ainsi qu’une base de données dédiée à PoWA. Cette base de données dédiée contient à la fois les extensions statistiques nécessaires pour récupérer compteurs de performances actuels ainsi que pour les stocker.
Un background worker est démarré par PoWA, qui est responsable d’effectuer des snapshots et de les stocker dans la base powa dédiée à intervalle réguliers. Ensuite, en utilisant powa-web, vous pouvez consulter l’activité de n’importe laquelle des bases de données locales en effectuant des requêtes sur les données stockées dans la base dédié, et potentiellement en se connectant sur l’une des autres bases de données locales lorsque les données complètes sont nécessaires, par exemple lorsque l’outil de suggestion d’index est utilisé.
Avec la version 4, l’architecture avec une configuration distante change de manière significative:
Vous pouvez voir qu’une base de donnée powa dédiée est toujours nécessaire, mais uniquement pour les extensions statistiques. Les données sont maintenant stockées sur une instance différente. Ensuite, le background worker est remplacé par un nouveau daemon collecteur, qui lit les métriques de performance depuis les serveurs distants, et les stocke sur le serveur repository dédié. Powa-web pourra présenter les données en se connectant sur le serveur repository, ainsi que sur les serveurs distants lorsque des données complètes sont nécessaires.
En résumé, avec le nouveau mode distant ajouté dans cette version 4
L’UI vous accueillera donc maintenant avec une page initiale afin de choisir
lequel des serveurs stockés sur la base de données cible vous voulez
travailler :
La principale raison pour laquelle il a fallu tellement de temps pour apporter ce mode distant est parce que cela apporte beaucoup de complexité, nécessitant une réécriture majeure de PoWA. Nous voulions également ajouter d’abord d’autres fonctionnalités, comme la suggestion globale d’index, avec une validation grâce à hypopg introduit avec PoWA 3.
L’interface graphique est le composant qui a le plus de changements visibles dans cette version 4. Voici les plus changements les plus importants.
Le changement le plus important est bien évidemment le support pour le nouveau mode remote. En conséquence, la première page affichée est maintenant une page de sélection de serveur, affichant tous les serveurs distants enregistrés. Après avoir choisi le serveur distant voulu (ou le serveur local si vous n’utilisez pas le mode distant), toutes les autres pages seront similaires à celles disponibles jusqu’à la version 3, mais afficheront les données pour un serveur distant spécifique uniquement, et bien entendu en récupérant les données depuis la base de données repository, avec en plus de nouvelles informations décrites ci-dessous.
Veuillez notez que puisque les données sont maintenant stockées sur un serveur repository dédié quand le mode remote est utilisé, la majorité de l’UI est utilisable sans se connecter au serveur distant sélectionné. Toutefois, powa-web nécessite toujours de pouvoir se connecter sur le serveur distant quand les données originales sont nécessaires (par exemple, pour la suggestion d’index ou pour montrer des plans avec EXPLAIN). Les mêmes considérations et possibilités concernant l’authentification que pour le nouveau daemon powa-collector (qui sera décrit dans un prochain article) s’appliquent ici.
Quand cette extension est correctement configurée, un nouveau widget timeline apparaîtra, placé entre chaque graph et son aperçu, affichant différents types de changements enregistrés si ceux-ci ont été détectés sur l’intervalle de temps sélectionné. Sur les pages par base de données et par requête, la liste sera également filtrée en fonction de la base de données sélectionnée.
La même timeline sera affichée sur chacun des graphs de chacune des pages, afin de facilement vérifier si ces changements ont eu un impact visible en utilisant les différents graphs.
Veuillez noter que les détails des changements sont affichés au survol de la souris. Vous pouvez également cliquer sur n’importe lequel des événements de la timeline pour figer l’affichage, et tracer une ligne verticale sur le graph associé.
Voici un exemple d’un tel changement de configuration en action :
Veuillez également noter qu’il est nécessaire d’avoir au minimum la version 2.0.0 de pg_track_settings, et que l’extension doit être installée à la fois sur les serveurs distants ainsi que sur le serveur repository.
Quand pg_stat_kcache est configuré, ses informations n’étaient auparavant affichées que sur la page par requête. Les informations sont maintenant également affichées sur les pages par serveur et par base, dans deux nouveaux graphs :
Voici un example de ce nouveau graph System Resources :
Il y avait également un graph Wait Events (disponible quand l’extension pg_wait_sampling est configuée) disponible uniquement sur la page par requête. Ce graph est maintenant disponible sur les pages par serveur et par base également.
Certaines métriques affichées sur l’interface sont assez parlante, mais certaines autres peuvent être un peu obscures. Jusqu’à maintenant, il n’y avait malheureusement aucune documentation pour les métriques. Le problème est maintenant réglé, et tous les graphs ont une icône d’information, qui affichent une description des métriques utilisée dans le graph au survol de la souris. Certains graphs incluent également un lien vers la documentation PoWA de extension statistiques pour les utilisateurs qui désirent en apprendre plus à leur sujet.
Voici un exemple :
Certains problèmes de longues dates ont également été rapportés :
Si un de ces problèmes vous a un jour posé problème, vous serez ravi d’apprendre qu’ils sont maintenant tous corrigés !
Cette 4ème version de PoWA représente un temps de développement très important, de nombreuses améliorations sur la documentation et beaucoup de tests. Nous somme maintenant assez satisfaits, mais il est possible que nous ayons ratés certains bugs. Si vous vous intéressez à ce projet, j’espère que vous essaierez de tester cette beta, et si besoin n’hésitez pas à nous remonter un bug!
PoWA 4 apporte un mode remote, disponible en beta ! was originally published by Julien Rouhaud at rjuju's home on May 17, 2019.
A partir de PostgreSQL 12, les colonnes oid des
tables systèmes perdront leur nature “spéciale”, et la clause
optionnelle WITH OIDS disparaîtra de CREATE TABLE. Concrètement
ça veut dire que quand on fait select * de ces tables, ces colonnes
seront maintenant visibles, ainsi que via
information_schema.columns, ou encore avec \d sous psql.
Jusqu’à présent elles étaient cachées, comme les colonnes systèmes xmin,
xmax, etc…
Le commit dans les sources indique la motivation de ce changement:
author Andres Freund <andres (at) anarazel (dot) de>
Wed, 21 Nov 2018 01:36:57 +0200 (15:36 -0800)
[…]
Remove WITH OIDS support, change oid catalog column visibility.
[…]
The fact that the oid column was not an ordinary column necessitated a significant amount of special case code to support oid columns. That already was painful for the existing, but upcoming work aiming to make table storage pluggable, would have required expanding and duplicating that “specialness” significantly.
En résumé, les caractéristiques spéciales des OIDs compliquait des évolutions importantes, en l’occurrence les formats de tables à la carte comme le très attendu zheap.
Si on regarde des années en arrière, ce changement peut être mis en perspective par rapport à d’autres, qui vont aussi dans le sens de l’obsolescence des OIDs:
oid devient optionnelle.default_with_oids est créé.default_with_oids passe à false par défaut.Mais pourquoi ces colonnes spéciales avaient-elles été inventées au départ? A l’origine, l’OID est lié à l’orientation objet.
Au milieu des années 80, le concept d’orientation objet faisait surface, avec des langages comme C++ qui en était à ses débuts. Dans les bases de données, il y avait cette idée que l’avenir était peut-être à considérer les données en tant qu’objets.
C’est donc assez naturellement que dans les premières versions de Postgres développées à l’Université de Berkeley, l’orientation objet était une composante importante du projet.
Dans les langages de programmation, le concept OO a été un succès avec notamment C++ ou Java par exemple. Mais concernant les bases de données, le concept n’a pas pris, ou en tout cas est resté cantonné à un usage de niche.
Lorsque la communauté de développeurs a repris Postgres au milieu des années 90 pour le faire évoluer en tant que moteur SQL, elle a hérité de fonctionnalités clairement influencées par le paradigme objet, notamment:
Mais la poursuite de la vision orientée objet n’a pas vraiment intéressé les développeurs depuis 1996, suivant en ça les autres communautés des bases de données. Ils ont préféré se concentrer sur d’autres objectifs, comme l’amélioration des performances, de la robustesse, et la conformité au standard SQL, lequel continuant d’ailleurs à évoluer.
Quoiqu’il en soit, la fonctionnalité où une ligne est considérée comme une instance de classe implique l’existence d’un identifiant au-delà des colonnes utilisateur, pour différencier une instance d’une autre. Par analogie avec les langages de programmation, où des classes sont instanciées en mémoire, une instance sera distincte d’une autre au minimum par le fait que leurs adresses en mémoire sont différentes. L’OID, c’est en quelque sorte l’adresse de l’instance de classe sérialisée sous une forme stockable sur disque.
Les anciennes documentations encore en ligne expliquent ce point de vue:
Concepts dans PostgreSQL 6.4 (1998):
The fundamental notion in Postgres is that of a class, which is a named collection of object instances. Each instance has the same collection of named attributes, and each attribute is of a specific type. Furthermore, each instance has a permanent object identifier (OID) that is unique throughout the installation. Because SQL syntax refers to tables, we will use the terms table and class interchangeably. Likewise, an SQL row is an instance and SQL columns are attributes.
Un exemple était donné dans Populating a Class with Instances:
The insert statement is used to populate a class with instances:
INSERT INTO weather VALUES (‘San Francisco’, 46, 50, 0.25, ‘11/27/1994’)
[…]
As previously discussed, classes are grouped into databases, and a collection of databases managed by a single postmaster process constitutes an installation or site.
C’est à partir de la version 7.1, sortie en 2001, que la référence aux classes disparaît, et qu’on parle de “Creating a New Table” au lieu de “Creating a New Class”.
Dans le catalogue il reste quelques vestiges de ce passé, comme la
table des tables qui s’appelle toujours pg_class
(mais il y a une vue pg_tables).
Les OIDs comme colonnes “normales” restent utilisées dans le catalogue
comme clef synthétique primaire partout où c’est utile.
Dans PostgreSQL-12, 39 tables ont un champ nommé oid et 278 colonnes
sont de type oid (contre 39 et 274 en version 11)
postgres=# SELECT
count(*) filter (where attname = 'oid') as "OID as name",
count(*) filter (where atttypid = 'oid'::regtype) as "OID as type"
FROM pg_attribute JOIN pg_class ON (attrelid=oid) WHERE relkind='r';
OID as name | OID as type
-------------+-------------
39 | 278
Par ailleurs, les OIDs restent essentiels dans la gestion des objets larges, qui
stockent des contenus binaires segmentés automatiquement, puisque l’API expose
ces objets exclusivement via leurs OIDs.
Le seul changement visible par les utilisateur en v12 est que
pg_largeobject_metadata.oid devient visible directement, mais un utilisateur n’a
pas vraiment besoin de requêter cette table s’il utilise l’API.
Les OIDs inférieurs à 16384 sont réservés au système de base comme avant.
Le générateur de valeurs pour les OIDs est un compteur au niveau du cluster, donc les valeurs sont distribuées séquentiellement comme si c’était une séquence commune à toutes les bases.
Ce qui donne par exemple:
postgres=# create database db1;
CREATE DATABASE
postgres=# \lo_import .bashrc
lo_import 16404
postgres=# \c db1
You are now connected to database "db1" as user "daniel".
db1=# \lo_import .bashrc
lo_import 16405
Ce comportement où un doublon d’OID est évité alors que les objets larges des deux bases sont totalement indépendants est possiblement un reliquat de l’époque où chaque OID était “unique throughout the installation” comme cité précédemment dans le passage de documentation de la 6.4.
Cette contrainte globale d’unicité a disparu il y a longtemps,
mais le générateur a conservé ce comportement anti-collision qui
fait que ça ne pourra arriver qu’après un cycle de plus de 4 milliards
de valeurs allouées dans le cluster.
(le compteur d’OID étant un entier de 32 bits non signé
qui repart à 16384 quand il atteint 2^32).
Les plus curieux peuvent aller consulter les commentaires de la fonction
GetNewOidWithIndex() pour voir comment le code gère
un possible conflit d’unicité avec un OID pré-existant dans une même table,
ainsi que la fonction SQL pg_nextoid qui est volontairement non mentionnée
dans la documentation.
Ajoutés dans PostgreSQL 9.3, les data checksums peuvent aider à détecter les corruptions de données survenant sur votre stockage.
Les checksums sont activés si l’instance a été initialisée en utilisant initdb
--data-checksums (ce qui n’est pas le comportement par défaut), ou s’ils ont
été activés après en utilisant la nouvelle utilitaire
activated afterwards with the new
pg_checksums
également ajouté dans PostgreSQL
12.
Quand les checksums sont ativés, ceux-ci sont écrits à chaque fois qu’un bloc de données est écrit sur disque, et vérifiés à chaque fois qu’un bloc est lu depuis le disque (ou depuis le cache du système d’exploitation). Si la vérification échoue, une erreur est remontée dans les logs. Si le bloc était lu par un processus client, la requête associée échouera bien évidemment, mais si le bloc était lu par une opération BASE_BACKUP (tel que pg_basebackup), la commande continuera à s’exécuter. Bien que les data checksums ne détecteront qu’un sous ensemble des problèmes possibles, ils ont tout de même une certaine utilisé, surtout si vous ne faites pas confiance à votre stockage.
Jusqu’à PostgreSQL 11, les erreurs de validation de checksum ne pouvaient être trouvées qu’en cherchant dans les logs, ce qui n’est clairement pas pratique si vous voulez monitorer de telles erreurs.
Pour rendre la supervision des erreurs de checksum plus simple, et pour aider
les utilisateurs à réagir dès qu’un tel problème survient, PostgreSQL 12 ajoute
de nouveaux compteurs dans la vue pg_stat_database :
commit 6b9e875f7286d8535bff7955e5aa3602e188e436
Author: Magnus Hagander <magnus@hagander.net>
Date: Sat Mar 9 10:45:17 2019 -0800
Track block level checksum failures in pg_stat_database
This adds a column that counts how many checksum failures have occurred
on files belonging to a specific database. Both checksum failures
during normal backend processing and those created when a base backup
detects a checksum failure are counted.
Author: Magnus Hagander
Reviewed by: Julien Rouhaud
commit 77bd49adba4711b4497e7e39a5ec3a9812cbd52a
Author: Magnus Hagander <magnus@hagander.net>
Date: Fri Apr 12 14:04:50 2019 +0200
Show shared object statistics in pg_stat_database
This adds a row to the pg_stat_database view with datoid 0 and datname
NULL for those objects that are not in a database. This was added
particularly for checksums, but we were already tracking more satistics
for these objects, just not returning it.
Also add a checksum_last_failure column that holds the timestamptz of
the last checksum failure that occurred in a database (or in a
non-dataabase file), if any.
Author: Julien Rouhaud <rjuju123@gmail.com>
commit 252b707bc41cc9bf6c55c18d8cb302a6176b7e48
Author: Magnus Hagander <magnus@hagander.net>
Date: Wed Apr 17 13:51:48 2019 +0200
Return NULL for checksum failures if checksums are not enabled
Returning 0 could falsely indicate that there is no problem. NULL
correctly indicates that there is no information about potential
problems.
Also return 0 as numbackends instead of NULL for shared objects (as no
connection can be made to a shared object only).
Author: Julien Rouhaud <rjuju123@gmail.com>
Reviewed-by: Robert Treat <rob@xzilla.net>
Ces compteurs reflèteront les erreurs de validation de checksum à la fois pour les processus clients et pour l’activité BASE_BACKUP, par base de données.
rjuju=# \d pg_stat_database
View "pg_catalog.pg_stat_database"
Column | Type | Collation | Nullable | Default
-----------------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
[...]
checksum_failures | bigint | | |
checksum_last_failure | timestamp with time zone | | |
[...]
stats_reset | timestamp with time zone | | |La colonne checksum_failures montrera un nombre cumulé d’erreurs, et la
colonne checksum_last_failure montrera l’horodatage de la dernière erreur de
validation sur la base de données (NULL si aucune erreur n’est jamais
survenue).
Pour éviter toute confusion (merci à Robert Treat pour l’avoir signalé), ces deux colonnes retourneront toujours NULL si les data checkums ne sont pas activés, afin qu’on ne puisse pas croire que les checksums sont toujours vérifiés avec succès.
Comme effet de bord, pg_stat_database montrera maintenant également les
statistiques disponibles pour les objets partagés (tels que la table
pg_database par exemple), dans une nouvelle ligne pour laquelle datid vaut
0, et datname vaut NULL.
Une sonde dédiée est également déjà
planifiée dans
check_pgactivity !
Une sonde dédiée est également déjà
disponible
dans check_pgactivity !
Nouveauté pg12: Statistiques sur les erreurs de checkums was originally published by Julien Rouhaud at rjuju's home on April 18, 2019.
J’entends régulièrement des complaintes sur la quantité d’espace disque gâchée par PostgreSQL pour chacune des lignes qu’il stocke. Je vais essayer de montrer ici quelques astuces pour minimiser cet effet, afin d’avoir un stockage plus efficace.
Si vous n’avez pas de table avec plus que quelques centaines de millions de lignes, il est probable que ce n’est pas un problème pour vous.
Pour chaque ligne stockée, postgres conservera quelques données additionnelles pour ses propres besoins. C’est documenté ici. La documentation indique :
| Field | Type | Length | Description |
|---|---|---|---|
| t_xmin | TransactionId | 4 bytes | XID d’insertion |
| t_xmax | TransactionId | 4 bytes | XID de suppresion |
| t_cid | CommandId | 4 bytes | CID d’insertion et de suppression (surcharge avec t_xvac) |
| t_xvac | TransactionId | 4 bytes | XID pour l’opération VACUUM déplaçant une version de ligne |
| t_ctid | ItemPointerData | 6 bytes | TID en cours pour cette version de ligne ou pour une version plus récente |
| t_infomask2 | uint16 | 2 bytes | nombre d’attributs et quelques bits d’état |
| t_infomask | uint16 | 2 bytes | différents bits d’options (flag bits) |
| t_hoff | uint8 | 1 byte | décalage vers les données utilisateur |
Ce qui représente 23 octets sur la plupart des architectures (il y a soit t_cid soit t_xvac).
Vous pouvez d’ailleurs consulter une partie de ces champs grâce aux colonnes cachées présentes dans n’importe quelle table en les ajoutant dans la partie SELECT d’une requête, ou en cherchant pour les numéros d’attribut négatifs dans le catalogue pg_attribute :
# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
# SELECT xmin, xmax, id FROM test LIMIT 1;
xmin | xmax | id
------+------+----
1361 | 0 | 1
# SELECT attname, attnum, atttypid::regtype, attlen
FROM pg_class c
JOIN pg_attribute a ON a.attrelid = c.oid
WHERE relname = 'test'
ORDER BY attnum;
attname | attnum | atttypid | attlen
----------+--------+----------+--------
tableoid | -7 | oid | 4
cmax | -6 | cid | 4
xmax | -5 | xid | 4
cmin | -4 | cid | 4
xmin | -3 | xid | 4
ctid | -1 | tid | 6
id | 1 | integer | 4Si vous comparez ces champs avec le tableau précédent, vous pouvez constater que toutes ces colonnes ne sont pas stockées sur disque. Bien évidemment, PostgreSQL ne stocke pas l’oid de la table pour chaque ligne. Celui-ci est ajouté après, lors de la construction d’une ligne.
Si vous voulez plus de détails techniques, vous pouvez regarder htup_detail.c, en commençant par TupleHeaderData struct.
Puisque ce surcoût est fixe, plus la taille des lignes croît plus il devient négligeable. Si vous ne stocker qu’une simple colonne de type intt (4 octets), chaque ligne nécessitera :
23B + 4B = 27Bsoit 85% de surcoût, ce qui est plutôt horrible.
D’une autre côté, si vous stockez 5 integer, 3 bigint et 2 colonnes de type texte (disons environ 80 octets en moyenne), cela donnera :
23B + 5*4B + 3*8B + 2*80B = 227BC’est “seulement” 10% de surcoût.
L’idée est de stocker les même données, mais avec moins d’enregistrements. Comment faire ? En aggrégeant les données dans des tableaux. Plus vous mettez d’enregistrements dans un seul tableau, plus vous minimiserez le surcoût. Et si vous aggrégez suffisamment de données, vous pouvez bénéficier d’une compression entièrement transparente grâce au mécanisme de TOAST.
Voyons ce que cela donne avec une table ne disposant que d’une seule colonne, avec 10 millions de lignes :
# CREATE TABLE raw_1 (id integer);
# INSERT INTO raw_1 SELECT generate_series(1,10000000);
# CREATE INDEX ON raw_1 (id);Les données utilisateur ne devrait nécessiter que 10M * 4 octets, soit environ 30 Mo, alors que cette table pèse 348 Mo. L’insertion des données prend environ 23 secondes.
NOTE : Si vous faites le calcul, vous trouverez que le surcoût est d’un peu plus que 32 octets par ligne, pas 23 octets. C’est parce que chaque bloc de données a également un surcoût, une gestion des colonnes NULL ainsi que des contraintes d’alignement. Si vous voulez plus d’informations à ce sujet, je vous recommande de regarder cette présentation
Comparons maintenant cela avec la version aggrégées des même données :
# CREATE TABLE agg_1 (id integer[]);
# INSERT INTO agg_1 SELECT array_agg(i)
FROM generate_series(1,10000000) i
GROUP BY i % 2000000;
# CREATE INDEX ON agg_1 (id);Cette requête insèrera 5 éléments par ligne. J’ai fait le même test avec 20, 100, 200 et 1000 éléments par ligne. Les résultats sont les suivants :
NOTE : La taille pour 1000 éléments par ligne est un peu plus importante que pour la valeur précédents. C’est parce que c’est le seul qui implique une taille suffisamment importante pour être TOAST-ée, mais pas assez pour être compressée. On peut donc voir ici un peu de surcoût lié au TOAST.
Jusqu’ici tout va bien, on peut voir de plutôt bonnes améliorations à la fois sur la taille et sur le temps d’insertion, même pour les tableaux les plus petits. Voyons maintenant l’impact pour récupérer des lignes. Je testerai la récupération de toutes les lignes, ainsi qu’une seule ligne au moyen d’un parcours d’index (j’ai utilisé pour les tests EXPLAIN ANALYZE afin de minimiser le temps passé par psql à afficher les données) : psql):
# SELECT id FROM raw_1;
# CREATE INDEX ON raw_1 (id);
# SELECT * FROM raw_1 WHERE id = 500;Pour correctement indexer le tableau, nous avons besoin d’un index GIN. Pour récupérer les valeurs de toutes les données aggrégées, il est nécessaire d’appeler unnest() sur le tableau, et pour récupérer un seul enregistrement il faut être un peu plus créatif :
# SELECT unnest(id) AS id FROM agg_1;
# CREATE INDEX ON agg_1 USING gin (id);
# WITH s(id) AS (
SELECT unnest(id)
FROM agg_1
WHERE id && array[500]
)
SELECT id FROM s WHERE id = 500;Voici le tableau comparant les temps de création de l’index ainsi que la taille de celui-ci, pour chaque dimension de tableau :
L’index GIN est un peu plus que deux fois plus volumineux que l’index btree, et si on accumule la taille de la table à la taille de l’index, la taille totale est presque identique avec ou sans aggrégation. Ce n’est pas un gros problème puisque cet exemple est très naïf, et nous verrons juste après comme éviter d’avoir recours à un index GIN pour conserver une taille totale faible. De plus, l’index est bien plus lent à créer, ce qui signifie qu’INSERT sera également plus lent.
Voici le tableau comparant le temps pour récupérer toutes les lignes ainsi qu’une seule ligne :
Récupérer toutes les lignes n’est probablement pas un exemple intéressant, mais il est intéressant de noter que dès que le tableau contient suffisamement d’éléments cela devient plus efficace que faire la même chose avec la table originale. Nous voyons également que récuérer un seul élément est bien plus rapide qu’avec l’index btree, grâce à l’efficacité de GIN. Ce n’est pas testé ici, mais puisque seul les index btree sont nativement triés, si vous devez récupérer un grand nombre d’enregistrements triés, l’utilisation d’un index GIN nécessitera un tri supplémentaire, ce qui sera bien plus lent qu’un simple parcours d’index btree.
Maintenant que nous avons vu les bases, voyons comment aller un peu plus loin : aggréger plus d’une colonne et éviter d’utiliser trop d’espce disque (et de ralentissements à l’écriture) du fait d’un index GIN. Pour cela, je vais présenter comme PoWA stocke ses données.
Pour chaque source de données collectée, deux tables sont utilisées : une pour les données historiques et aggrégées, ainsi qu’une pour les données courantes. Ces tables stockent les données dans un type de données personnalisé plutôt que des colonnes. Voyons les tables liées à l’extension pg_stat_statements :
Le type de données, grosso modo tous les compteurs présents dans pg_stat_statements ainsi que l’horodatage associé à l’enregistrement :
powa=# \d powa_statements_history_record
Composite type "public.powa_statements_history_record"
Column | Type | Modifiers
---------------------+--------------------------+-----------
ts | timestamp with time zone |
calls | bigint |
total_time | double precision |
rows | bigint |
shared_blks_hit | bigint |
shared_blks_read | bigint |
shared_blks_dirtied | bigint |
shared_blks_written | bigint |
local_blks_hit | bigint |
local_blks_read | bigint |
local_blks_dirtied | bigint |
local_blks_written | bigint |
temp_blks_read | bigint |
temp_blks_written | bigint |
blk_read_time | double precision |
blk_write_time | double precision |La table pour les données courrante stocke l’identifieur unique de pg_stat_statements (queryid, dbid, userid), ainsi qu’un enregistrement de compteurs :
powa=# \d powa_statements_history_current
Table "public.powa_statements_history_current"
Column | Type | Modifiers
---------+--------------------------------+-----------
queryid | bigint | not null
dbid | oid | not null
userid | oid | not null
record | powa_statements_history_record | not nullLa table pour les données aggrégées contient le même identifieur unique, un tableau d’enregistrements ainsi que quelques champs spéciaux :
powa=# \d powa_statements_history
Table "public.powa_statements_history"
Column | Type | Modifiers
----------------+----------------------------------+-----------
queryid | bigint | not null
dbid | oid | not null
userid | oid | not null
coalesce_range | tstzrange | not null
records | powa_statements_history_record[] | not null
mins_in_range | powa_statements_history_record | not null
maxs_in_range | powa_statements_history_record | not null
Indexes:
"powa_statements_history_query_ts" gist (queryid, coalesce_range)Nous stockons également l’intervalle d’horodatage (coalesce_range) contenant tous les compteurs aggrégés dans la ligne, ainsi que les valeurs minimales et maximales de chaque compteurs dans deux compteurs dédiés. Ces champs supplémentaires ne consomment pas trop d’espace, et permettent une indexation ainsi qu’un traitement très efficace, basé sur les modèles d’accès aux données de l’application associée.
Cette table est utilisée pour savoir combien de ressources ont été utilisée par une requête sur un intervalle de temps donné. L’index GiST ne sera pas très gros puisqu’il n’indexe que deux petites valeus pour X compteurs aggrégés, et trouvera les lignes correspondant à une requête et un intervalle de temps données de manière très efficace.
Ensuite, calculer les ressources consommées peut être fait de manière très efficace, puisque les compteurs de pg_stat_statements sont strictement monotones. L’algorithme pourrait être :
NOTE : Dans les faits, l’interface de PoWA dépilera toujours tous les enregistrements contenus dans l’intervalle de temps demandé, puisque l’interface est faite pour montrer l’évolution de ces compteurs sur un intervalle de temps relativement réduit, mais avec une grande précision. Heureusement, dépiler les tableaux n’est pas si coûteux que ça, surtout en regard de l’espace disque économisé.
Et voici la taille nécessaire pour les valeurs aggrégées et non aggrégées. Pour cela j’ai laissé PoWA générer 12 331 366 enregistrements (en configurant une capture toutes les 5 secondes pendant quelques heures, et avec l’aggrégation par défaut de 100 enregistrements par lignes), et créé un index btree sur (queryid, ((record).ts) pour simuler l’index présent sur les tables aggrégées :
Vous trouvez aussi que c’est plutôt efficace ?
Il y a quelques limitations avec l’aggrégation d’enregistrements. Si vous faites ça, vous ne pouvez plus garantir de contraintes telles que des clés étrangères ou contrainte d’unicité. C’est donc à utiliser pour des données non relationnelles, telles que des compteurs ou des métadonnées.
L’utilisation de type de données personnalisés vous permet de faire des choses sympathiques, comme définir des opérateurs personnalisés. Par exemple, la version 3.1.0 de PoWA fournit deux opérateurs pour chacun des types de données personnalisé définis :
Vous pouvez donc faire très facilement des requêtes du genre :
# SELECT (record - lag(record) over()).*
FROM from powa_statements_history_current
WHERE queryid = 3589441560 AND dbid = 16384;
intvl | calls | total_time | rows | ...
-----------------+--------+------------------+--------+ ...
<NULL> | <NULL> | <NULL> | <NULL> | ...
00:00:05.004611 | 5753 | 20.5570000000005 | 5753 | ...
00:00:05.004569 | 1879 | 6.40500000000047 | 1879 | ...
00:00:05.00477 | 14369 | 48.9060000000006 | 14369 | ...
00:00:05.00418 | 0 | 0 | 0 | ...
# SELECT (record / lag(record) over()).*
FROM powa_statements_history_current
WHERE queryid = 3589441560 AND dbid = 16384;
sec | calls_per_sec | runtime_per_sec | rows_per_sec | ...
--------+---------------+------------------+--------------+ ...
<NULL> | <NULL> | <NULL> | <NULL> | ...
5 | 1150.6 | 4.1114000000001 | 1150.6 | ...
5 | 375.8 | 1.28100000000009 | 375.8 | ...
5 | 2873.8 | 9.78120000000011 | 2873.8 | ...Si vous êtes intéressés sur la façon d’implémenter de tels opérateurs, vous pouvez regarder l’implémentation de PoWA.
Vous connaissez maintenant les bases pour éviter le surcoût de stockage par ligne. En fonction de vos besoins et de la spécificité de vos données, vous devriez pouvoir trouver un moyen d’aggréger vos données, en ajoutant potentiellement quelques colonnes supplémentaires, afin de conserver de bonnes performances et économiser de l’espace disque.
Minimiser le surcoût de stockage par ligne was originally published by Julien Rouhaud at rjuju's home on April 06, 2019.
Vous avez la possibilité de visualiser les Wait Events dans PoWA 3.2.0 grâce à l’extension pg_wait_sampling extension.
Les wait events sont une fonctionnalité connues, et bien utiles, dans de
nombreux moteurs de base de données relationnelles. Ceux-ci ont été ajouté à
PostgreSQL 9.6, il
y a maintenant quelques versions. Contrairement à la plupart des autres
statistiques exposées par PostgreSQL, ceux-ci ne sont qu’une vision à un
instant donné des événements sur lesquels les processus sont en attente, et non
pas des compteurs cumulés. Vous pouvez consulter cette information en
utilisant la vue pg_stat_activity, par exemple :
=# SELECT datid, pid, wait_event_type, wait_event, query FROM pg_stat_activity;
datid | pid | wait_event_type | wait_event | query
--------+-------+-----------------+---------------------+-------------------------------------------------------------------------
<NULL> | 13782 | Activity | AutoVacuumMain |
16384 | 16615 | Lock | relation | SELECT * FROM t1;
16384 | 16621 | Client | ClientRead | LOCK TABLE t1;
847842 | 16763 | LWLock | WALWriteLock | END;
847842 | 16764 | Lock | transactionid | UPDATE pgbench_branches SET bbalance = bbalance + 1229 WHERE bid = 1;
847842 | 16766 | LWLock | WALWriteLock | END;
847842 | 16767 | Lock | transactionid | UPDATE pgbench_tellers SET tbalance = tbalance + 3383 WHERE tid = 86;
847842 | 16769 | Lock | transactionid | UPDATE pgbench_branches SET bbalance = bbalance + -3786 WHERE bid = 10;
[...]Dans cet exemple, nous voyons que le //wait event// pour le pid 16615 est un
Lock sur une Relation. En d’autre terme, la requête est bloquée en
attente d’un verrou lourd, alors que le pid 16621, qui clairement détient le
verrou, est inactif en attente de commandes du client. Il s’agit
d’informations qu’il était déjà possible d’obtenir avec les anciennes versions,
bien que cela se faisait d’une autre manière. Mais plus intéressant, nous
pouvons également voir que le //wait event// pour le pid 16766 est un
LWLock, c’est-à-dire un Lightweight Lock, ou verrou léger. Les verrous
légers sont des verrous internes et transitoires qu’il était auparavant
impossible de voir au niveau SQL. dans cet exemple, la requête est en attente
d’un WALWriteLock, un verrou léger principalement utilisé pour contrôler
l’écriture dans les tampons des journaux de transaction. Une liste complète
des //wait events// disponible est disponible sur la documentation
officielle.
Ces informations manquaient curellement et sont bien utiles pour diagnostiquer
les causes de ralentissement. Cependant, n’avoir que la vue de ces //wait
events// à l’instant présent n’est clairement pas suffisant pour avoir une
bonne idée de ce qu’il se passe sur le serveur. Puisque la plupart des //wait
events// sont pas nature très éphémères, ce dont vous avez besoin est de les
échantilloner à une fréquence élevée. Tenter de faire cet échantillonage avec
un outil externe, même à une seconde d’intervalle, n’est généralement pas
suffisant. C’est là que l’extension
pg_wait_sampling apporte
une solution vraiment brillante. Il s’agit d’une extension écrite par
Alexander Korotkov et Ildus Kurbangaliev. Une
fois activée (il est nécessaire de la configurer dans le
shared_preload_libraries, un redémarrage de l’instance est donc nécessaire),
elle échantillonera en mémoire partagée les //wait events// toutes les 10
ms (par défaut), et aggèrega également les compteurs par type de //wait
event// (wait_event_type), //wait event// et queryid (si
pg_stat_statements est également acctivé). Pour plus de détails sur la
configuration et l’utilisation de cette extension, vous pouvez consulter le
README de
l’extension.
Comme tout le travail est fait en mémoire au moyen d’une extension écrite en C,
c’est très efficace. De plus, l’implémentation est faite avec très peu de
verouillage, le surcoût de cette extension devrait être presque négligable.
J’ai fait quelques tests de performance sur mon pc portable (je n’ai
malheureusement pas de meilleure machine sur laquelle tester) avec un
pgbench en
lecture seule où toutes les données tenaient dans le cache de PostgreSQL
(shared_buffers), avec 8 puis 90 clients, afin d’essayer d’avoir le maximum
de surcoût possible. La moyenne sur 3 tests était d’environ 1% de surcoût,
avec des fluctuations entre chaque test d’environ 0.8%.
Ainsi, grâce à cette extension, nous avons à notre disposition une vue cumulée et extrêmement précise des //wait events//. C’est très bien, mais comme toutes les autres statistiques cumulées dans PostgreSQL, vous devez échantillonner ces compteurs régulièrement si vous voulez pouvoir être capable de savoir ce qu’il s’est passé à un certain moment dans le passé, comme c’est d’ailleurs précisé dans le README de l’extension :
[…] Waits profile. It’s implemented as in-memory hash table where count of samples are accumulated per each process and each wait event (and each query with
pg_stat_statements). This hash table can be reset by user request. Assuming there is a client who periodically dumps profile and resets it, user can have statistics of intensivity of wait events among time.
C’est exactement le but de PoWA: sauvegarder les compteurs statistiques de manière efficace, et les afficher sur une interface graphique.
PoWA 3.2 détecte automatiquement si l’extension pg_wait_sampling est déjà présente ou si vous l’installez ultérieurement, et commencera à collecter ses données, vous donnant une vue vraiment précise des //wait events// dans le temps sur vos bases de données !
Les données sont centralisée dans des tables PoWA classiques,
powa_wait_sampling_history_current pour les 100 dernières collectes (valeur
par défaut de powa.coalesce), et les valeurs plus anciennes sont aggrégées
dans la table powa_wait_sampling_history, avec un historique allant jusqu’à
une période définie par powa.retention. Par exemple, voici une requête
simple affichant les 20 premiers changements survenus au sein des 100 premiers
instantanés :
WITH s AS (
SELECT (record).ts, queryid, event_type, event,
(record).count - lag((record).count)
OVER (PARTITION BY queryid, event_type, event ORDER BY (record).ts)
AS events
FROM powa_wait_sampling_history_current w
JOIN pg_database d ON d.oid = w.dbid
WHERE d.datname = 'bench'
)
SELECT *
FROM s
WHERE events != 0
ORDER BY ts ASC, event DESC
LIMIT 20;
ts | queryid | event_type | event | events
-------------------------------+----------------------+------------+----------------+--------
2018-07-09 10:44:08.037191+02 | -6531859117817823569 | LWLock | pg_qualstats | 1233
2018-07-09 10:44:28.035212+02 | 8851222058009799098 | Lock | tuple | 4
2018-07-09 10:44:28.035212+02 | -6860707137622661878 | Lock | tuple | 149
2018-07-09 10:44:28.035212+02 | 8851222058009799098 | Lock | transactionid | 193
2018-07-09 10:44:28.035212+02 | -6860707137622661878 | Lock | transactionid | 1143
2018-07-09 10:44:28.035212+02 | -6531859117817823569 | LWLock | pg_qualstats | 1
2018-07-09 10:44:28.035212+02 | 8851222058009799098 | LWLock | lock_manager | 2
2018-07-09 10:44:28.035212+02 | -6860707137622661878 | LWLock | lock_manager | 3
2018-07-09 10:44:28.035212+02 | -6860707137622661878 | LWLock | buffer_content | 2
2018-07-09 10:44:48.037205+02 | 8851222058009799098 | Lock | tuple | 14
2018-07-09 10:44:48.037205+02 | -6860707137622661878 | Lock | tuple | 335
2018-07-09 10:44:48.037205+02 | -6860707137622661878 | Lock | transactionid | 2604
2018-07-09 10:44:48.037205+02 | 8851222058009799098 | Lock | transactionid | 384
2018-07-09 10:44:48.037205+02 | -6860707137622661878 | LWLock | lock_manager | 13
2018-07-09 10:44:48.037205+02 | 8851222058009799098 | LWLock | lock_manager | 4
2018-07-09 10:44:48.037205+02 | 8221555873158496753 | IO | DataFileExtend | 1
2018-07-09 10:44:48.037205+02 | -6860707137622661878 | LWLock | buffer_content | 4
2018-07-09 10:45:08.032938+02 | 8851222058009799098 | Lock | tuple | 5
2018-07-09 10:45:08.032938+02 | -6860707137622661878 | Lock | tuple | 312
2018-07-09 10:45:08.032938+02 | -6860707137622661878 | Lock | transactionid | 2586
(20 rows)NOTE: Il y a également une version par base de données de ces valeurs pour
un traitement plus efficace au niveau des basesn dans les tables
powa_wait_sampling_history_current_db et powa_wait_sampling_history_db
Et ces données sont visibles avec l’interface powa-web. Voici quelques exemples d’affichage des //wait events// tels qu’affichés par PoWA avec un simple pgbench :
Cette fonctionnalité est disponible depuis la version 3.2 de PoWA. J’espère pouvoir afficher plus de vues de ces données dans le futur, en incluant d’autres graphes, puisque toutes les données sont déjà disponibles en bases. Également, si vous êtes un développeur python ou javascript, les contributions sont toujours bienvenues!
Support des Wait Events pour PoWA was originally published by Julien Rouhaud at rjuju's home on April 02, 2019.
La recherche plein texte se base sur une transformation
en type tsvector du texte brut initial. Par exemple:
test=> select to_tsvector('french', 'Voici notre texte à indexer.');
to_tsvector
-----------------------------
'index':5 'text':3 'voic':1
Ce résultat est une suite ordonnée de lexèmes, avec leurs positions relatives dans le texte initial, qui est obtenue schématiquement par cette chaîne de traitement:
Texte brut => Analyseur lexical (parser) => Dictionnaires configurables => tsvector
Dès qu’on a un volume significatif, on indexe aussi ces vecteurs avec un index GIN ou GIST pour accélérer les recherches.
En SQL on peut inspecter le travail de cette chaîne dans le détail
avec la fonction ts_debug:
test=> select * from ts_debug('french', 'Voici notre texte à indexer.');
alias | description | token | dictionaries | dictionary | lexemes
-----------+-------------------+---------+---------------+-------------+---------
asciiword | Word, all ASCII | Voici | {french_stem} | french_stem | {voic}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | notre | {french_stem} | french_stem | {}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | texte | {french_stem} | french_stem | {text}
blank | Space symbols | | {} | |
word | Word, all letters | à | {french_stem} | french_stem | {}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | indexer | {french_stem} | french_stem | {index}
blank | Space symbols | . | {} | |
L’analyseur lexical a découpé le texte en jetons (colonne token), qui ont chacun un type (colonne alias). Puis ces jetons, en fonction de leurs types, sont soumis en entrée à des dictionnaires, qui en ressortent des lexèmes, qui peuvent être vides pour éliminer le terme du vecteur final.
Dans l’exemple ci-dessus, les espaces et la ponctuation ont été
éliminés parce qu’ils ne sont pas associés à un dictionnaire, les
termes communs (“à”, “notre”) ont été éliminés par le dictionnaire
french_stem, et “Voici”, “texte” et “indexer” ont été
normalisés et réduits à leur racine supposée par ce même dictionnaire.
Parfois on a affaire à du texte brut qui n’est pas “propre”, qui contient des bribes de texte parasites. Par exemple dans l’indexation de messages e-mail, des messages mal formatés peuvent laisser apparaître des parties encodées en base64 dans ce qui devrait être du texte en clair. Et quand il s’agit d’une pièce jointe, ça peut occuper une place considérable. Si on examine le passage d’une ligne de contenu de ce genre dans la chaîne de traitement plein texte, voici ce qu’on peut voir:
=# \x
=# select * from ts_debug('french', 'Q29uc2lzdGVuY3kgYWxzbyBtZWFucyB0aGF0IHdoZW4gQWxpY2UgYW5kIEJvYiBhcmUgcnVubmlu');
-[ RECORD 1 ]+-------------------------------------------------------------------------------
alias | numword
description | Word, letters and digits
token | Q29uc2lzdGVuY3kgYWxzbyBtZWFucyB0aGF0IHdoZW4gQWxpY2UgYW5kIEJvYiBhcmUgcnVubmlu
dictionaries | {simple}
dictionary | simple
lexemes | {q29uc2lzdgvuy3kgywxzbybtzwfucyb0agf0ihdozw4gqwxpy2ugyw5kiejvyibhcmugcnvubmlu}
Ce texte est donc analysé comme un seul jeton de type numword, et produit un seul long lexème, vu que ce jeton est associé au dictionnaire simple qui se contente de convertir le terme en minuscules. Se pose la question de savoir si on ne pourrait pas éviter de polluer le vecteur avec ces termes inutiles.
Une idée simple est de considérer que les jetons de grande taille sont inintéressants et supprimables systématiquement. Même s’il y a des mots exceptionnellement longs dans certaines langues comme l’allemand Rindfleischetikettierungsüberwachungsaufgabenübertragungsgesetz avec ses 63 caractères (!), on peut imaginer se fixer une limite de taille au-delà de laquelle la probabilité que le mot soit un parasite est suffisante pour l’éliminer de l’espace de recherche.
Une solution relativement facile à mettre en oeuvre est de créer un dictionnaire qui
va filtrer ces termes comme si c’étaient des “stop words”.
Ce dictionnaire prend concrètement la forme de deux fonctions à
écrire en langage C, et de quelques ordres SQL pour déclarer et assigner notre
nouveau dictionnaire à une configuration de recherche
(ALTER TEXT SEARCH CONFIGURATION).
Dans le code source de PostgreSQL, il y a plusieurs exemples de dictionnaires dont on peut s’inspirer:
On peut démarrer en copier-collant un de ces exemples, car la partie vraiment distinctive de notre dictionnaire personnalisé tout simple s’exprime en seulement quelques lignes en C, comme on va le voir plus bas.
Les deux fonctions à produire sont:
une fonction d’initialisation INIT qui reçoit les paramètres de
configuration du dictionnaire. On en profite pour rendre notre longueur
maximale configurable via ce mécanisme, plutôt que de la coder en dur
dans le source.
une fonction LEXIZE reçevant un terme (le texte associé au jeton)
et chargée d’émettre zéro, un ou plusieurs lexèmes correspondant à
ce terme. Les lexèmes émis peuvent être passés ou non au reste des
dictionnaires de la chaîne s’il y en a d’autres, au choix de cette
fonction.
Dans le cas qui nous intéresse ici, on veut éliminer le
terme s’il est trop long, et sinon le passer tel quel.
On va appeler ce dictionnaire dictmaxlen et son paramètre length.
En suivant le modèle et les conventions des modules additionnels de
contrib/, on peut l’encapsuler dans une
extension Postgres, dans un répertoire dédié avec un Makefile et
un fichier de déclaration SQL spécifiques.
Pour être précis dans la terminologie, ces définitions créent un
modèle de dictionnaire (template) et non directement un dictionnaire.
Le dictionnaire à proprement parler est instancié à partir d’un modèle
par CREATE TEXT SEARCH DICTIONARY (TEMPLATE = ...), avec les valeurs
des éventuels paramètres.
Voici les déclarations SQL des fonctions et du modèle:
CREATE FUNCTION dictmaxlen_init(internal)
RETURNS internal
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT;
CREATE FUNCTION dictmaxlen_lexize(internal, internal, internal, internal)
RETURNS internal
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT;
CREATE TEXT SEARCH TEMPLATE dictmaxlen_template (
LEXIZE = dictmaxlen_lexize,
INIT = dictmaxlen_init
);La seule chose spécifique ici est la racine de nommage dictmaxlen, sinon
ce seront les mêmes déclarations pour n’importe quel modèle de dictionnaire.
Datum
dictmaxlen_init(PG_FUNCTION_ARGS)
{
List *options = (List *) PG_GETARG_POINTER(0);
DictMaxLen *d;
ListCell *l;
d = (DictMaxLen *) palloc0(sizeof(DictMaxLen));
d->maxlen = 50; /* 50 caracteres par defaut */
foreach(l, options)
{
DefElem *defel = (DefElem *) lfirst(l);
if (strcmp(defel->defname, "length") == 0)
d->maxlen = atoi(defGetString(defel));
else
{
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("unrecognized dictionary parameter: \"%s\"",
defel->defname)));
}
}
PG_RETURN_POINTER(d);
}Datum
dictmaxlen_lexize(PG_FUNCTION_ARGS)
{
DictMaxLen *d = (DictMaxLen *) PG_GETARG_POINTER(0);
char *token = (char *) PG_GETARG_POINTER(1);
int byte_length = PG_GETARG_INT32(2);
if (pg_mbstrlen_with_len(token, byte_length) > d->maxlen)
{
/*
* Si le mot est plus grand que notre limite, renvoie un
* tableau sans lexeme.
*/
TSLexeme *res = palloc0(sizeof(TSLexeme));
PG_RETURN_POINTER(res);
}
else
{
/* Si le mot est court, on le laisse passer en mode "non reconnu" */
PG_RETURN_POINTER(NULL);
}
}Comme pour la plupart des modules de contrib, l’empaquetage de ce code dans une extension est la manière la plus efficace pour le distribuer et le déployer.
La création d’une extension est assez simple en utilisant PGXS,
qui est fourni avec Postgres (pour les distributions Linux, il faudra installer
un paquet de développement, comme postgresql-server-dev-11 pour Debian).
Une extension a besoin d’un fichier de contrôle. Le contenu
ci-dessous fait l’affaire (fichier dict_maxlen.control):
# dict_maxlen extension
comment = 'text search template for a dictionary filtering out long words'
default_version = '1.0'
module_pathname = '$libdir/dict_maxlen'
relocatable = true
Grâce à PGXS on peut se contenter d’un Makefile simplifié qui va
inclure automatiquement les déclarations pour les chemins où
Postgres est installé, les bibliothèques dont il a besoin, etc.
Ci-dessous un Makefile complètement fonctionnel, qui permet de compiler
et installer l’extension avec make && make install:
EXTENSION = dict_maxlen
EXTVERSION = 1.0
PG_CONFIG = pg_config
MODULE_big = dict_maxlen
OBJS = dict_maxlen.o
DATA = $(wildcard *.sql)
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
Une fois les fichiers de l’extension compilés et installés, on peut la créer dans une base et y instantier le dictionnaire.
CREATE EXTENSION dict_maxlen;
CREATE TEXT SEARCH DICTIONARY dictmaxlen (
TEMPLATE = dictmaxlen_template,
LENGTH = 40 -- par exemple
);Ensuite il faut associer ce dictionnaire à des types de jetons (produits
par l’analyseur lexical), via ALTER TEXT SEARCH CONFIGURATION ... ALTER MAPPING.
Plus haut on a vu sur un exemple que le type de jeton qui ressortait sur
du contenu encodé était numword, mais on peut aussi vouloir
associer notre dictionnaire aux jetons ne contenant que des lettres:
word et asciiword, ou à n’importe quels autres des 23 types de
jeton
que l’analyseur peut générer actuellement.
Avec psql on peut visualiser ces associations avec \dF+. Pour french,
par défaut on a:
=# \dF+ french
Text search configuration "pg_catalog.french"
Parser: "pg_catalog.default"
Token | Dictionaries
-----------------+--------------
asciihword | french_stem
asciiword | french_stem
email | simple
file | simple
float | simple
host | simple
hword | french_stem
hword_asciipart | french_stem
hword_numpart | simple
hword_part | french_stem
int | simple
numhword | simple
numword | simple
sfloat | simple
uint | simple
url | simple
url_path | simple
version | simple
word | french_stem
On peut créer une configuration de texte spécifique, pour éviter de perturber celles qui existent déjà, et lui associer ce dictionnaire:
CREATE TEXT SEARCH CONFIGURATION mytsconf ( COPY = pg_catalog.french );
ALTER TEXT SEARCH CONFIGURATION mytsconf
ALTER MAPPING FOR asciiword, word
WITH dictmaxlen,french_stem;
ALTER TEXT SEARCH CONFIGURATION mytsconf
ALTER MAPPING FOR numword
WITH dictmaxlen,simple;Vérification faite avec psql:
=# \dF+ mytsconf
Text search configuration "public.mytsconf"
Parser: "pg_catalog.default"
Token | Dictionaries
-----------------+------------------------
asciihword | french_stem
asciiword | dictmaxlen,french_stem
email | simple
file | simple
float | simple
host | simple
hword | french_stem
hword_asciipart | french_stem
hword_numpart | simple
hword_part | french_stem
int | simple
numhword | simple
numword | dictmaxlen,simple
sfloat | simple
uint | simple
url | simple
url_path | simple
version | simple
word | dictmaxlen,french_stem
Et voilà, il n’y a plus qu’à vérifier avec un numword dépassant les 40 caractères:
=# select to_tsvector('mytsconf', 'Un lexème trop long: Q29uc2lzdGVuY3kgYWxzbyBtZWFucyB0aGF0IHdoZW4');
to_tsvector
-----------------------------
'lexem':2 'long':4 'trop':3
(1 row)Le jeton indésirable a bien été écarté.
En peut utiliser cette configuration mytsconf en la passant en
argument explicite aux fonctions de recherche et indexation plein
texte, mais aussi la mettre par défaut:
Pour la session:
SET default_text_search_config TO 'mytsconf';
Pour la base (permanent):
ALTER DATABASE nombase SET default_text_search_config TO 'mytsconf';
Le code source de cet exemple de dictionnaire est disponible sur github.
Un serveur PostgreSQL peut être accessible d’Internet, au sens d’avoir le service en écoute sur une adresse IP publique et un port TCP ouvert à toute connexion. A titre indicatif, shodan.io, un service qui sonde ce genre de choses, trouve plus de 650000 instances dans ce cas actuellement. Avec la popularisation du modèle DBaaS (“Database As A Service”), les serveurs PostgreSQL peuvent être légitimement accessibles d’Internet, mais ça peut être aussi le résultat involontaire d’une mauvaise configuration.
Car cette configuration réseau ouverte s’oppose à une autre plus traditionnelle et plus sécurisée lorsque les serveurs de bases de données sont au minimum protégés par un pare-feu, voire n’ont même pas d’interface réseau reliée à Internet, ou bien n’écoutent pas dessus.
La conséquence d’avoir des instances ouvertes est que des tentatives d’intrusion sur le port 5432 sont susceptibles de se produire à tout moment, tout comme il y a des tentatives de piratage en tout genre sur d’autres services comme ssh, le mail ou des applications web populaires comme Wordpress, Drupal ou phpMyAdmin.
Si vous avez un serveur accessible publiquement, il est possible de mettre son IP dans le champ de recherche de shodan.io, histoire de voir ce qu’il sait de vous.
Que vous ayez déjà des instances PostgreSQL ouvertes à l’Internet, que vous envisagiez d’en avoir, ou au contraire que vous vouliez vous assurer que vos instances ne sont pas accessibles, voici deux ou trois réflexions à ce sujet.
Quand on demande “comment activer l’accès à PostgreSQL à partir d’une
autre machine?”, la réponse
typique est d’ajouter
des règles dans pg_hba.conf et de mettre dans postgresql.conf:
listen_addresses = *
(en remplacement du listen_addresses = localhost initial)
Effectivement ça fonctionne, en faisant écouter toutes les interfaces réseau de la machine, pas seulement celle où les connexions PostgreSQL sont attendues. Dans le cas, assez typique, où ces connexions sont initiées exclusivement d’un réseau local privé, on pourrait plutôt préciser les adresses des interfaces concernées. Si par exemple le serveur a une IP privée 192.168.1.12, on pourrait mettre:
listen_addresses = localhost, 192.168.1.12
Pourquoi ces adresses plutôt que * ? On peut se poser plus généralement la
question: pourquoi PostgreSQL n’a pas listen_addresses = * par
défaut, de façon à ce qu’un poste distant puisse se connecter
directement, sans obliger un admin à modifier d’abord la
configuration?
MongoDB faisait ça, et l’ampleur des attaques réussies contre cette base illustre assez bien pourquoi ce n’est pas une bonne idée. En 2015 shodan estimait qu’au moins 30000 instances MongoDB étaient librement accessibles d’Internet, probablement dans leur configuration par défaut, laissant l’accès à 595 TB de données. Fin 2016, une campagne d’attaque dite “Mongo Lock” commençait à affecter une bonne partie de ces victimes potentielles. Le piratage consistait à effacer ou chiffrer les données et exiger une rançon en bitcoins pour les récupérer. Cet épisode a été une vraie débâcle pour la réputation de MongoDB.
Indépendamment de la question du mot de passe, dont l’absence par défaut est aussi un facteur important dans ces attaques, l’ampleur aurait été biensûr moindre si le service écoutait par défaut uniquement sur l’interface réseau locale, puisque c’est suffisant quand un site et sa base sont la même machine.
MongoDB a changé depuis cette configuration par défaut, mais des années après on voit toujours ce qui semble être des exploitations de ce problème, par exemple en janvier 2019, cette fuite de données: MongoDB : 202 millions de CV privés exposés sur internet à cause d’une base de données non protégée.
C’est qu’il y a toujours dans la nature des installations jamais mises à jour dont les gérants, quand il y en a, n’ont aucune idée qu’il y a un danger pour leurs données et qu’il faudrait changer une configuration alors même que “ça marche”…
Evidemment, il faut protéger les comptes utilisateur par des mots de passe solides, mais ça ne suffit pas.
Un pré-requis indispensable est de se tenir au courant des mises à jour
de sécurité et d’être prêt à les appliquer en urgence si nécessaire.
Par exemple en 2013, la faille
de sécurité CVE-2013-1899
permettait de prendre la main à distance sur n’importe quelle instance PostgreSQL,
indépendamment des mots de passe et des règles du pg_hba.conf,
tant qu’on avait un moyen de la joindre par le réseau
(d’où encore une fois l’intérêt de ne pas s’exposer inutilement en
mettant listen_addresses = * quand ce n’est pas indispensable).
Cette faille de sécurité est scrutée par des sondes à qui on a rien demandé, puisque si je regarde les logs récents de mon instance PostgreSQL ouverte sur Internet, je vois des entrées du style (modulo le masquage de la source):
2019-01-31 05:51:44 CET FATAL: no pg_hba.conf entry for host "185.x.x.x",
user "postgres", database "template0", SSL on
2019-01-31 05:51:44 CET FATAL: no pg_hba.conf entry for host "185.x.x.x",
user "postgres", database "template0", SSL off
2019-01-31 05:51:44 CET FATAL: unsupported frontend protocol 65363.19778: serve
r supports 1.0 to 3.0
2019-01-31 05:51:44 CET FATAL: no pg_hba.conf entry for host "185.x.x.x",
user "postgres", database "-h", SSL on
2019-01-31 05:51:44 CET FATAL: no pg_hba.conf entry for host "185.x.x.x",
user "postgres", database "-h", SSL off
Le nom de base “-h” n’est pas choisi au hasard, la faille ci-dessus étant décrite par:
Argument injection vulnerability in PostgreSQL 9.2.x before 9.2.4, 9.1.x before 9.1.9, and 9.0.x before 9.0.13 allows remote attackers to cause a denial of service (file corruption), and allows remote authenticated users to modify configuration settings and execute arbitrary code, via a connection request using a database name that begins with a “-“ (hyphen)
Ce genre de tentative peut venir d’un service comme shodan ou d’un bot malveillant, voire d’un attaquant qui vous vise spécifiquement, difficile à savoir.
Il y a des exemples d’attaques réussies sur postgres, notamment visant à faire miner de la cryptomonnaie Monero.
Pour autant qu’on puisse en juger de l’extérieur, ces attaques
n’exploitent pas une faille spécifique de postgres, mais parviennent
à se connecter en super-utilisateur postgres. On peut imaginer que
ça arrive à cause d’un mot de passe trop faible, d’un
pg_hba.conf trop laxiste, ou via le piratage d’un autre service
(typiquement un site web) qui se connecte à PostgreSQL en
super-utilisateur.
Par exemple dans cette question sur dba.stackexchange:
Mysterious postgres process pegging CPU at 100%; no running queries un utilisateur demande pourquoi postgres fait tourner une commande ./Ac2p20853 consommant tout le CPU disponible. L’explication de loin la plus plausible est un piratage dans
lequel ce binaire a été téléchargé et lancé via une fonction postgresql ayant les droits
super-utilisateur.
Cette autre question sur stackoverflow.com (CPU 100% usage caused by unknown postgres query) est assez similaire, mais en plus elle montre des requêtes servant de coquille au programme parasite:
pg_stat_activity:
pid datname username query
19882 postgres postgres select Fun013301 ('./x3606027128 &')
19901 postgres postgres select Fun013301 ('./ps3597605779 &')
top:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
19885 postgres 20 0 192684 3916 1420 S 98.3 0.1 5689:04 x3606027128
Ce comportement ressemble trait pour trait à l’attaque que Imperva a détecté via leurs instances “pot de miel”, et disséquée dans leur article A Deep Dive into Database Attacks [Part III]: Why Scarlett Johansson’s Picture Got My Postgres Database to Start Mining Monero.
En résumé, une fois qu’une connexion SQL sur un compte
super-utilisateur est obtenue (par un moyen non précisé), le code attaquant créé
une fonction SQL permettant d’exécuter n’importe quel programme sur disque.
Ensuite il créé sur le disque via lo_export() un programme qui a pour objet
d’aller récupérer sur Internet le vrai programme qui mine.
Le programme en question est sur un site d’images public, en l’occurrence
caché ici dans un fichier photo représentant Scarlett Johansson, d’où la référence improbable
à l’actrice dans le titre de l’article.
Moralité: il faut limiter les comptes super-utilisateur à un usage d’administration,
et éviter de leur attribuer le droit aux connexions distantes, via pg_hba.conf.
Avoir ssl=on dans la configuration serveur signifie que le chiffrage
est possible quand le client le demande, mais pas qu’il est
obligatoire. Le chiffrage évite qu’une tierce partie ayant accès
au réseau puisse lire tout ce qui passe entre le client et le serveur.
Si on veut l’obliger du côté serveur, on peut y arriver via les
règles du fichier pg_hba.conf (les règles sont interprétées
dans l’ordre et le test s’arrête dès qu’une correspondance est trouvée,
comme dans une cascade de IF…ELSEIF…ELSIF…ELSIF…END IF):
# autorise les connexions locales "Unix domain sockets"
# sans mot de passe pour le même utilisateur OS
local all all peer
# permet l'économie du chiffrage, mais pas du mot de passe
# pour les connexions TCP locales
host all all 127.0.0.1/32 md5 # plutôt scram avec postgresql 10 et plus
host all all ::1/128 md5
# rejette les connexions distantes non chiffrées
hostnossl all all 0.0.0.0/0 reject
hostnossl all all ::/0 reject
# ajouter les autres règles à partir d'ici
...
...
Par défaut la bibliothèque cliente la plus souvent utilisée,
libpq,
lorsqu’elle est compilée avec le support SSL, essaie d’abord une connexion
chiffrée, puis le cas échéant une connexion non chiffrée. Ce comportement
correspond à sslmode=prefer dans les paramètres de connexion
(voir le détail dans la section Support de
SSL de la doc).
C’est pour ça que dans les logs, une tentative de connexion infructueuse comme ci-dessus
apparaît en double, une première fois avec SSL=on et la seconde avec SSL=off.
Depuis la version 9.5, il est possible de savoir parmi les connexions établies
quelles sont celles qui sont chiffrées ou pas avec la vue système
pg_stat_ssl
SELECT datname,usename, ssl, client_addr
FROM pg_stat_ssl
JOIN pg_stat_activity
ON pg_stat_ssl.pid = pg_stat_activity.pid;A défaut d’interdire les connexions non chiffrées, cette requête permet de vérifier s’il y en a et d’où elles viennent.
Open Food Facts est une base de données contributive sur les produits alimentaires, en français et en anglais, sous licence ouverte ODBL.
Ce service permet sur le web, à partir d’un code de produit (code barres) d’en connaître les ingrédients, de renseigner ceux d’un produit non encore connu de la base pour l’enrichir, et de faire des requêtes en ligne avec un formulaire de recherche. Il a également une application mobile pour les smartphones et une API, le tout étant en source sur github.
Le service utilise le moteur NoSQL MongoDB, mais les données sont disponibles en téléchargement aux formats CSV et RDF entre autres, de sorte qu’on puisse les importer dans l’outil de notre choix. Comme sur ce blog on n’est pas trop branchés MongoDB, mais plutôt PostgreSQL, on va voir comment importer ces données pour les requêter avec notre SGBD préféré :)
Ca peut paraître trivial, puisque PostgreSQL supporte l’import de CSV nativement à travers la commande COPY, mais en pratique ça ne l’est pas pour diverses raisons:
CSV n’est pas un format rigoureusement spécifié a priori. Il est spécifié a posteriori par les règles de la RFC 4180 avec laquelle PostgreSQL est compatible, mais de nombreux exports CSV les ignorent. La seule chose à peu près garantie sur les fichiers annoncés comme CSV, c’est qu’ils ont des champs texte séparés par un certain caractère.
les données OpenData sont souvent plus ou moins “sales”, parfois au point de nécessiter un nettoyage ou filtrage pour les rendre importables.
les fichiers ont souvent un grand nombre de colonnes dont on connait les noms mais pas forcément les types, et une création à la main des tables est fastidieuse.
Ce billet détaille les étapes nécessaires pour importer une extraction d’OpenFoodFacts, jusqu’à arriver à une base requêtable. La majorité des problèmes et solutions mentionnés ne sont pas spécifiques à cet import, ils pourraient se retrouver dans d’autres jeux de données “OpenData”.
L’export se présente sous la forme d’un seul fichier de grande taille, c’est-à-dire, au 20/12/2018, une taille de 2 Go pour un peu plus de 700 mille lignes:
$ wget https://fr.openfoodfacts.org/data/fr.openfoodfacts.org.products.csv
$ wc -l fr.openfoodfacts.org.products.csv
709988 fr.openfoodfacts.org.products.csv
$ ls -sh fr.openfoodfacts.org.products.csv
2.0G fr.openfoodfacts.org.products.csv
Pour tenter l’import avec COPY, il faut d’abord créer une table correspondant à la structure du fichier. En effet COPY ne crée pas la table même quand il y a des noms de colonne sur la 1ere ligne (l’option HEADER ne sert à l’import qu’à ignorer cette ligne).
Les infos dont on dispose pour ça sont:
$ head -n 1 fr.openfoodfacts.org.products.csvPour avoir cette liste des colonnes sous forme lisible, on peut utiliser la commande Unix suivante qui va sortir les noms de colonnes alignés verticalement:
$ head -n 1 fr.openfoodfacts.org.products.csv | sed -e 's/\t/\n/g'
code
url
creator
created_t
created_datetime
last_modified_t
last_modified_datetime
product_name
...etc... (173 lignes)
Pour éviter de spécifier un par un les types de données de chaque colonne, dans un CREATE TABLE, on peut utiliser un programme qui fait ça automatiquement: csvkit.
csvkit est écrit en python et installable avec pip, ou via un paquet
d’une distribution Linux. Ici j’utilise Debian 9, qui propose la
version 0.9.1 de csvkit, installable par:
# apt install python3-csvkit
En plus de bibliothèques en python, ce paquet nous installe une série d’outils appelables en ligne de commande:
/usr/bin/csvformat
/usr/bin/csvcut
/usr/bin/csvjson
/usr/bin/csvclean
/usr/bin/csvsql
/usr/bin/csvstack
/usr/bin/sql2csv
/usr/bin/csvlook
/usr/bin/csvjoin
/usr/bin/csvstat
/usr/bin/in2csv
/usr/bin/csvpy
/usr/bin/csvgrep
/usr/bin/csvsort
Celui qui nous intéresse est csvsql, et d’après sa documentation, il peut générer un script de
création de table, et optionnellement insérer les données.
Par défaut il déclare les champs texte en VARCHAR(N) où N est la
taille maximale constatée dans les données, mais ce n’est pas forcément
pertinent pour PostgreSQL où limiter la taille déclarée n’apporte
aucun gain de performance, et surtout ce n’est pas parce qu’un champ
n’a pas dépassé N caractères jusque là que des entrées qu’on pourrait
ajouter plus tard devraient forcément se conformer à cette limite.
On utilise donc l’option --no-constraints pour éviter ça:
--no-constraints Generate a schema without length limits or null
checks. Useful when sampling big tables.
Donc voici l’invocation qui convient:
$ csvsql --dialect postgresql --tabs --table openfoodfacts \
--no-constraints fr.openfoodfacts.org.products.csv > create_table.sql
La commande prend quelques minutes, et produit ce fichier qui contient un ordre CREATE TABLE avec 173 colonnes comportant:
Dans l’ensemble le résultat semble correct, à part deux détails:
les champs created_datetime et last_modified_datetime mériteraient
d’être en timestamptz. On les modifiera après.
les colonnes nommées _100g sont pratiquement toutes en type “float”
(équivalent à “double precision”), sauf deux:
"nervonic-acid_100g" INTEGER,
"nutrition-score-fr_100g" INTEGER,
C’est probablement parce qu’il n’y avait aucun point décimal dans aucune des valeurs de tout le fichier pour ces deux colonnes. Par souci d’homogénéité, on pourra les retyper comme les autres en “float”.
Pour commençer on créé une base dédiée:
$ psql -d postgres -U postgres
=# CREATE DATABASE foodfacts ENCODING 'UTF8';
Puisque csvsql sait créer la table, autant lui demander d’insérer aussi le contenu,
avec son option --insert.
Une option --db attend une “SQL alchemy connection string” qui, dans le cas où on
reste sur l’utilisateur par défaut, peut se réduire au nom de la base:
--db postgresql:///foodfacts, ou être du style --db postgresql://user@host:port/dbname
Il faudra aussi installer le driver psycopg2 pour python pour profiter de l’import, par exemple sur Debian:
# apt install python3-psycopg2
$ csvsql --db postgresql:///foodfacts --table openfoodfacts --tabs \
--insert --no-constraints fr.openfoodfacts.org.products.csv
L’import fonctionne jusqu’au bout sans message d’erreur, en une douzaine de minutes, mais il s’avère qu’après l’import des contrôles basiques d’intégrité donnent des résultats inquiétants. Pour commencer, il manque des entrées par rapport au nombre de lignes du fichier, 660 exactement:
$ wc -l fr.openfoodfacts.org.products.csv
709988
$ psql -d foodfacts
=> select count(*) from openfoodfacts;
count
--------
709327
Seulement 0.1% des lignes du fichier manquent à l’appel, mais c’est mauvais signe. Il ne devrait y avoir une différence que de 1 entre ces deux nombres (correspondant à la ligne d’entête).
Un autre contrôle basique donne aussi un résultat qui confirme que cet import
pose problème: si on considère la colonne created_datetime, date de création
de l’entrée, et qu’on cherche son minimum, son maximum, et le nombre de fois où
elle n’est pas remplie, on obtient:
=> SELECT min(created_datetime),
max(created_datetime),
count(*) filter (where created_datetime is null)
FROM openfoodfacts;
Résultat:
min | max | count
----------------------+---------------------+-------
2012-01-31T14:43:58Z | Super U, Magasins U | 10
(1 row)
Le min semble correct, mais le max montre qu’un nom d’enseigne
s’est invité dans les dates, ce qui signifie que des données ont
“glissé” d’une colonne à l’autre, voire sur plusieurs colonnes. Par
ailleurs, 10 entrées n’ont pas de date de création, alors qu’on
s’attendrait à ce qu’il y en ait zéro.
Dans la mesure où csvkit ne sort pas de message d’erreur alors qu’il a clairement des problèmes avec ces données, je n’insiste pas avec cet outil pour l’import. Il a été utile pour sortir un CREATE TABLE, c’est déjà pas mal.
COPY FROM FILE est réservé aux superutilisateurs (parce qu’il peut
lire des fichiers sur le serveur avec les droits de postgres), on va
donc utiliser le \copy de psql qui est très proche à l’usage mais
ouvre le fichier côté client et le fait passer par la liaison
client-serveur, ce qui ne nécessite pas de droit particulier:
=> \copy openfoodfacts from 'fr.openfoodfacts.org.products.csv' with (format csv, header, delimiter E'\t')
Le premier résultat est que l’import échoue assez rapidement:
ERROR: missing data for column "bicarbonate_100g"
CONTEXT: COPY openfoodfacts, line 3193: "0011110084767 http://world-fr.openfoodfacts.org/produit/0011110084767/8-white-cake-decorated usda-nd..."
Il s’agit probablement d’un décalage de champ mais comment voir ce qui
ne va pas avec cette ligne? Pour ça on va l’isoler du fichier avec une
commande shell spécifique. Puis on va l’importer en base
en tant qu’un seul champ de texte, au format text au sens de la
commande COPY (et non CSV):
# extraction de la ligne sur laquelle l'erreur est signalée
$ sed '3193q;d' fr.openfoodfacts.org.products.csv > line-3193
# vérification qu'il n'y a pas d'antislash ni de caractère de code 0x01
# (pour l'utiliser comme échappement) dans la ligne de données:
$ fgrep '\' line-3193 # resultat vide
$ fgrep $'\x01' line-3193 # resultat vide
=> CREATE TEMPORARY TABLE ligne(data text);
CREATE TABLE
=> \copy ligne FROM 'line-3193' WITH (format text, delimiter E'\001')
COPY 1
Une fois importée on peut, en SQL, découper cette ligne en champs pour les apparier
avec les colonnes de notre table principale (via pg_attribute et par numéro de champ),
histoire de bien visualiser les associations [numéro de champ] / [nom de colonne] / [valeur]
=> SELECT num, attname, valeur
FROM ligne CROSS JOIN LATERAL regexp_split_to_table(data, E'\t')
WITH ORDINALITY AS res(valeur,num)
JOIN pg_attribute att ON (res.num = att.attnum AND attrelid='openfoodfacts'::regclass);(attention: cette requête suppose qu’on n’a pas supprimé de colonne avec
ALTER TABLE openfoodfacts DROP COLUMN ..., car les colonnes
supprimées restent perpétuellement, avec leurs numéros initiaux,
dans pg_attribute).
Je ne vais reproduire tout le résultat ici car même un seul enregistrement
produit beaucoup de données à l’écran, mais en regardant les champs
un par un, on voit qu’à la colonne serving_size, un guillemet qui en
principe est un caractère spécial en (CSV d’encadrement de champ) est
employé pour signifier le “pouce” anglo-saxon, et pas pour encadrer un
champ.
39 | traces_tags |
40 | traces_fr |
41 | serving_size | 28 g (1 " CUBE | ABOUT)
42 | serving_quantity | 28
43 | no_nutriments |
Ce qui gêne ici, c’est que ça va à l’encontre de cette règle du CSV (de la RFC 4180 citée plus haut):
5. Each field may or may not be enclosed in double quotes (however some programs, such as Microsoft Excel, do not use double quotes at all). If fields are not enclosed with double quotes, then double quotes may not appear inside the fields
Il aurait fallu que le champ soit formatté ainsi: "28 g (1 "" CUBE | ABOUT)".
Comment contourner ce problème? Il se trouve que l’interprétation du
guillemet est paramétrable dans COPY via l’option QUOTE, justement
pour les cas où le format de fichier s’éloigne du CSV strict. Pour
que le caractère guillemet devienne normal, il faut spécifier un autre
caractère à sa place. Les contraintes: que ce caractère n’apparaisse
pas dans les contenus par ailleurs (ou qu’il soit doublé mais on n’est
pas dans ce cas là), et que de plus son codage UTF-8 tienne en un seul
octet non nul, c’est-à-dire dans la plage 0x01-0x7F.
Donc cherchons le premier caractère qui convient (c’est-à-dire qui ait 0 occurrence dans les contenus) avec ces commandes shell successives:
$ fgrep -c $'\x01' fr.openfoodfacts.org.products.csv
4
$ fgrep -c $'\x02' fr.openfoodfacts.org.products.csv
2
$ fgrep -c $'\x03' fr.openfoodfacts.org.products.csv
0
A noter que des octets de valeurs 01 et 02 n’ont en principe pas leur
place dans des contenus texte, il s’agit de codes de contrôle qui ne correspondent à aucune lettre de l’alphabet,
et qui certainement sont du “bruit” supprimable dans ces données.
Quoiqu’il en soit, le code 03 est disponible pour l’indiquer à COPY au lieu
du guillemet pour rendre à ce dernier son caractère neutre, et on peut réessayer notre
import avec l’option quote '\003'. Cette fois-ci, ça va nettement plus loin:
=> \copy openfoodfacts from 'fr.openfoodfacts.org.products.csv' with (format csv, header, delimiter E'\t', quote E'\003')
ERROR: unquoted carriage return found in data
HINT: Use quoted CSV field to represent carriage return.
CONTEXT: COPY openfoodfacts, line 268349
Cette nouvelle erreur nous dit qu’il y a un retour chariot à l’intérieur d’un champ.
Ce n’est pas que le retour chariot soit interdit en CSV (aucun caractère n’est interdit),
mais il est autorisé seulement si l’ensemble du champ est encadré par
des guillemets (ou au pire par le caractère spécifié par l’option quote), mais là
le fichier n’utilise pas l’encadrement des champs comme vu plus haut.
Ceci nous mène à vérifier un point important: y-a-t’il des sauts de ligne (caractère de code 10) à l’intérieur des champs en plus des retours chariot (caractère de code 13)?
C’est vérifiable simplement avec awk, à qui on peut demander les
lignes qui ne contiennent pas strictement 173 champs séparés par des
tabulations:
$ awk -F'\t' '{if (NF!=173) print $0}' fr.openfoodfacts.org.products.csv
Il se trouve qu’il n’y a aucun résultat, c’est-à-dire qu’en fait il n’y a pas de saut de ligne à l’intérieur des champs, sinon forcément certains s’étendraient sur plusieurs lignes et donc certaines lignes porteraient moins de 173 champs.
Du côté des caractères retours chariot, leur nombre est trouvable par la commande:
$ fgrep -c $'\x0d' fr.openfoodfacts.org.products.csv
34
Ces 34 retours chariots dans les contenus, posent problème compte-tenu
du fait que les champs ne sont pas encadrés. On va filtrer
(=supprimer) ces caractères en utilisant une autre clause de \copy,
la clause program avec la commande Unix tr
comme filtre.
=> \copy openfoodfacts from program 'tr -d ''\r'' <fr.openfoodfacts.org.products.csv' with (format csv, header, delimiter E'\t', quote E'\003')
COPY 709987
Et enfin, cette commande-là ne rencontre pas d’erreur et permet d’intégrer la totalité des lignes (709988 moins l’entête), en à peu près une minute.
Une fois l’import passé, faisons encore quelques vérifications de cohérence sur des critères basiques:
=> SELECT
min(created_datetime),
max(created_datetime),
count(*) filter (where created_datetime is null)
FROM
openfoodfacts;
min | max | count
----------------------+----------------------+-------
2012-01-31T14:43:58Z | 2018-12-12T07:24:25Z | 1
On voit qu’on a une entrée avec une date de création nulle, ce qui est anormal mais pas inquiétant en volume.
En principe les valeurs de la colonne code devraient être uniques puisque
c’est le code barres du produit, mais on trouve un certain nombre de doublons:
=> SELECT code,count(*) FROM openfoodfacts GROUP BY code HAVING count(*)>1;
code | count
--------------------+-------
0016000459335 | 2
0019320001376 | 2
0051500006962 | 2
0055577102152 | 2
...
8901262260152 | 2
9415142005904 | 2
(113 rows)
On peut soit ignorer ce problème et décider de ne pas déclarer la clef primaire dans la table, soit créer une clef primaire synthétique (un compteur qui n’a pas d’autre signification que numéro unique), soit supprimer les entrées en doublon.
Si deux lignes sont exactement en doublon (les valeurs de toutes les colonnes sont identiques), il suffit d’en supprimer une des deux. Mais souvent, on a un quasi-doublon, dans le sens où l’application de saisie de ces données a produit deux fiches au lieu d’une pour la même chose, mais pas avec rigoureusement les mêmes contenus.
Voici une méthode pour afficher en SQL les colonnes qui diffèrent
entre deux lignes dont beaucoup de colonnes sont supposément égales
par ailleurs. On va utiliser ici une requête préparée qui prend en
argument ($1 dans le texte de la requête) la valeur de la clef qui
se présente en doublon:
=> PREPARE diff AS
WITH valeurs AS (SELECT key, value
FROM
(SELECT row_to_json(o.*) AS line
FROM openfoodfacts AS o
WHERE code=$1) AS r
CROSS JOIN LATERAL json_each_text(r.line))
SELECT distinct v1.key
FROM valeurs v1 JOIN valeurs v2 ON (v1.key=v2.key
AND v1.value IS DISTINCT FROM v2.value);
=> EXECUTE diff('0051500006962');
key
-----------
countries
(1 row)
=> SELECT code,countries FROM openfoodfacts WHERE code='0051500006962';
code | countries
---------------+---------------
0051500006962 | États-Unis
0051500006962 | United States
(2 rows)
=> EXECUTE diff('6003326008341');
key
------------------------
image_small_url
last_modified_t
image_url
last_modified_datetime
(4 rows)
Ici je vais aller au plus vite en ne gardant pour chaque doublon que
le dernier modifié sur la base de la colonne last_modified_datetime,
et en cas d’égalité sur cette colonne, un arbitrage sur la pseudo-colonne ctid
(emplacement physique de la ligne) qui est forcément différente d’une
ligne à l’autre.
DELETE FROM openfoodfacts o1
USING (SELECT code, max(last_modified_datetime), max(ctid) as mxid
FROM openfoodfacts GROUP BY code HAVING COUNT(*)>1) o2
WHERE o1.code=o2.code
AND (o1.last_modified_datetime < o2.max
OR (o1.last_modified_datetime = o2.max AND o1.ctid < o2.mxid));Finalement on peut créer un index unique:
=> CREATE UNIQUE INDEX code_index ON openfoodfacts(code);
Si on voulait mieux faire, il faudrait examiner au cas par cas ces doublons, et agir pour les supprimer dans la base source des données.
Les colonnes de type “horodatage” n’ont pas été typées en timestamptz par
csvsql, mais il est toujours temps de le faire après coup avec ALTER
TABLE.
On peut aussi supprimer les colonnes created_t et last_modified_t qui
sont des timestamps Unix (nombre de secondes depuis le 1er janvier 1970)
avec la même signification que created_datetime et last_modified_datetime.
En faisant tout dans la même commande:
ALTER TABLE openfoodfacts
ALTER COLUMN created_datetime TYPE timestamptz USING (created_datetime::timestamptz),
ALTER COLUMN last_modified_datetime TYPE timestamptz USING (last_modified_datetime::timestamptz),
ALTER COLUMN "nervonic-acid_100g" TYPE float USING ("nervonic-acid_100g"::float),
ALTER COLUMN "nutrition-score-fr_100g" TYPE float USING ("nutrition-score-fr_100g"::float),
DROP COLUMN created_t,
DROP COLUMN last_modified_t;Pour finir, une petite requête d’exemple montrant le nombre de références ajoutées par an:
=> SELECT extract(year from created_datetime), count(*)
FROM openfoodfacts
GROUP BY 1 ORDER BY 1 DESC;
date_part | count
-----------+--------
| 1
2018 | 316197
2017 | 284243
2016 | 46218
2015 | 35256
2014 | 13417
2013 | 10078
2012 | 4464
(8 rows)
La bêta 4 (déjà!) de PostgreSQL 11 a été annoncée le 20 septembre. Il est vraisemblable qu’on pourra utiliser cette version 11 en production dans quelques mois, mais en attendant il est possible et souhaitable de l’essayer dans nos environnements de test avec nos applis, soit pour vérifier leur compatibilité, soit pour tester les nouveautés et remonter d’éventuels problèmes.
La liste exhaustive des changements par rapport à la version 10 est donnée par les notes de version de la documentation.
Voici une sélection d’articles ou présentations (en anglais) qui détaillent ou mettent en perspective ces nouveautés de manière plus digeste que la liste de la doc:
PostgreSQL 11: something for everyone, par Peter Geoghegan.
Partitioning Improvements in PostgreSQL 11, par Álvaro Herrera.
Major Features: Postgres 11 (support de présentation PDF, 24 pages), par Bruce Momjian.
PostgreSQL 11 New Features With Examples (PDF très complet, 86 pages), par Noriyoshi Shinoda.
SERIALIZABLE in PostgreSQL 11… and beyond, par Thomas Munro.
Parallel PostGIS and PgSQL 11, par Paul Ramsey.
Just In Time Compilation in PostgreSQL 11 and onward, par Andres Freund (support de présentation PDF, 26 pages).
En français, on peut regarder la présentation de Jean-Christophe Arnu au PG Day France 2018, en vidéo (YouTube) ou sur slideshare.
Enfin, la mise à jour de la traduction de la doc est bien avancée par les volontaires francophones sur github.
GNU libc 2.28, sortie le 1er août 2018, comprend une mise à jour majeure des locales Unicode en général et des données relatives aux collations en particulier.
L’annonce indique:
The localization data for ISO 14651 is updated to match the 2016 Edition 4 release of the standard, this matches data provided by Unicode 9.0.0. This update introduces significant improvements to the collation of Unicode characters. […] With the update many locales have been updated to take advantage of the new collation information. The new collation information has increased the size of the compiled locale archive or binary locales.
Pour les instances PostgreSQL qui utilisent des collations glibc
dépendant de la région et de la langue (exemples: fr_FR.iso885915
ou `en_US.utf-8’),
cela signifie que certaines chaînes de caractères seront triées
différemment après cette mise à jour.
Une conséquence critique est que les index qui dépendent de ces collations
doivent impérativement être reconstruits immédiatement après la montée
de version de glibc.
Les serveurs en réplication WAL/streaming doivent aussi être mis à jour
simultanément car un secondaire doit tourner rigoureusement avec
les mêmes locales que son primaire.
Le risque autrement est d’engendrer des corruptions d’index, comme illustré par ces deux discussions sur la liste pgsql-general en anglais: “Issues with german locale on CentOS 5,6,7”, et “The dangers of streaming across versions of glibc: A cautionary tale”.
En résumé, si Postgres parcourt un index avec une fonction de comparaison qui diffère de celle utilisée pour écrire cet index, il est possible que des valeurs présentes ne soient plus trouvées en lecture. Et en cas d’insertion, c’est pire puisque les nouvelles entrées risquent d’être insérées à des emplacements incohérents par rapport à la version précédente, et corrompre irrémédiablement l’index.
Ce problème de mise à jour des locales n’est donc pas nouveau, mais ce qui est particulier avec cette version 2.28 de la glibc, c’est l’importance de la mise à jour, qui est sans précédent dans la période récente. En effet depuis l’an 2000, d’après le bug#14095, les données des locales dans la glibc étaient modifiées au cas par cas. Cette fois-ci, il s’agit d’un rattrapage massif pour recoller au standard Unicode.
Pour tester un peu l’effet de ces changements, j’ai installé ArchLinux qui a déjà la glibc-2.28, avec PostgreSQL 10.5, et comparé les résultats de quelques requêtes avec ceux obtenus sous Debian 9 (“stretch”), qui est en glibc-2.24.
Je m’attendais bien à quelques changements, mais pas aussi étendus. Car il s’avère que des tests simples sur des chaînes avec uniquement des caractères ASCII de base montrent tout de suite des différences importantes.
Par exemple, avec la locale en_US.UTF-8:
Debian stretch (glibc 2.24)
=# select version();
version
----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.5 (Debian 10.5-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)
=# show lc_collate ;
lc_collate
-------------
en_US.UTF-8
(1 row)
=# SELECT * FROM (values ('a'), ('$a'), ('a$'), ('b'), ('$b'), ('b$'), ('A'), ('B'))
AS l(x) ORDER BY x ;
x
----
a
$a
a$
A
b
$b
b$
B
(6 rows)
ArchLinux (glibc 2.28):
=# select version();
version
-----------------------------------------------------------------------------
PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.2.0, 64-bit
(1 row)
=# show lc_collate;
lc_collate
-------------
en_US.UTF-8
(1 row)
=# SELECT * FROM (values ('a'), ('$a'), ('a$'), ('b'), ('$b'), ('b$'), ('A'), ('B'))
AS l(x) ORDER BY x ;
x
----
$a
$b
a
A
a$
b
B
b$
(6 rows)
Ces changements ne sont pas limités aux locales UTF-8.
Les différences ci-dessus s’appliquent aussi à l’encodage LATIN9
avec lc_collate = 'fr_FR.iso885915@euro', par exemple.
Et voici une requête encore plus simple qui montre aussi des résultats de tri de chaînes différents entre versions:
Debian stretch (glibc 2.24)
=# SELECT * FROM (values ('"0102"'), ('0102')) AS x(x)
ORDER BY x;
x
--------
0102
"0102"
(2 rows)
ArchLinux (glibc 2.28):
=# SELECT * FROM (values ('"0102"'), ('0102')) AS x(x)
ORDER BY x;
x
--------
"0102"
0102
(2 rows)
J’ai pris l’habitude d’utiliser la requête ci-dessus
pour illustrer les différences entre FreeBSD et Linux/glibc
mais alors que la collation en_US dans FreeBSD 11 triait jusque-là ces chaînes
à l’opposé de glibc, maintenant il s’avère que la nouvelle glibc
donne un résultat identique aux locales et libc de FreeBSD…
Naturellement la plupart des utilisateurs ne changent pas de version de libc
de leur propre initiative, mais dans le cadre d’une montée de version
du système.
Si Postgres est mis à jour au passage avec un dump/reload,
les index seront recréés avec les nouvelles règles. Sinon un
REINDEX global de toutes les bases devrait être envisagé,
ou a minima des index concernés.
A noter que pg_upgrade
pour cette situation ne réindexe pas automatiquement, et ne signale pas
non plus l’obligation de le faire.
A la date de ce billet, les seules distributions Linux ayant déjà la glibc-2.28 doivent être les “bleeding edge” comme ArchLinux. Pour Fedora c’est prévu au 30 octobre 2018; Debian a actuellement la 2.27-5 dans testing, et Ubuntu “cosmic” (18.10) a la 2.27-3.
Si vous êtes utilisateur de Postgres sous Linux, ne manquez pas de vérifier si vos bases sont concernées par ces mises à jour de locales, et si oui, regardez bien quand vos systèmes passent à la glibc 2.28 pour prévoir une phase de réindexation pour éviter tout risque de corruption de données!
Pour savoir quelles collations chaque base utilise par défaut:
SELECT datname, datcollate FROM pg_database;
Pour savoir quelles collations sont plus spécifiquement utilisées dans les index (à faire tourner sur chaque base):
SELECT distinct collname FROM pg_collation JOIN
(SELECT regexp_split_to_table(n::text,' ')::oid AS o
FROM (SELECT distinct indcollation AS n FROM pg_index) AS a) AS b on o=oid
-- WHERE collprovider <> 'i'
;
Avec Postgres 10 ou plus récent, on peut décommenter la dernière ligne pour éviter les collations ICU, qui ne sont pas concernées par la mise à jour de la glibc. Les locales C et POSIX ne sont également pas concernées étant donné qu’elles comparent au niveau de l’octet, sans règle linguistique.
Depuis la version 10, Postgres peut être configuré avec
ICU, la bibliothèque de référence
pour Unicode, afin d’utiliser ses collations (règles de tri
et de comparaison de chaînes de caractères) via des clauses COLLATE.
Pour ce faire, les collations ICU pour la plupart des langues/pays
sont automatiquement créées au moment d’initdb (on les trouvera
dans pg_catalog.pg_collation), et d’autres peuvent être ajoutées
plus tard avec CREATE COLLATION.
Au-delà du support des collations, ICU fournit d’autres services relatifs aux locales et à la gestion du texte multilingue, suivant les recommandations d’Unicode.
A partir du moment où nos binaires Postgres sont liées à ICU (la plupart le sont parce que les installeurs les plus importants comme Apt, Rpm ou Rdb l’incluent d’office), pourquoi ne pas chercher à bénéficier de tous ces services à travers SQL?
C’est le but de icu_ext,
une extension en C implémentant des interfaces SQL aux fonctions d’ICU.
Actuellement elle comprend une vingtaine de fonctions permettant
d’inspecter les locales et les collations, de découper du texte,
de comparer et trier les chaînes de caractères, d’évaluer
l’utilisation trompeuse de caractères Unicode (spoofing),
d’épeler des nombres et de faire des conversions entre systèmes
d’écriture (translitération).
Avant de voir les fonctions de comparaison et tri, faisons un point sur ce qu’amène l’intégration d’ICU dans Postgres.
Versionnage: pg_collation.collversion contient le numéro de version
de chaque collation au moment de sa création, et si au cours de l’exécution
elle ne correspond plus à celle de la bibliothèque ICU (typiquement
après un upgrade), un avertissement est émis, invitant l’utilisateur
à reconstuire les index potentiellement affectés et à enregistrer
la nouvelle version de la collation dans le catalogue.
Cohérence inter-systèmes: une même collation ICU trie de la même
façon entre systèmes d’exploitation différents, ce qui n’est pas le cas
avec les “libc” (bibliothèque C de base sur laquelle s’appuie tous
les programmes du système).
Par example avec un même lc_collate à en_US.UTF-8 a un comportement
différent entre Linux and FreeBSD: des couples de chaînes de caractères
aussi simples que 0102 and "0102" se retrouvent ordonnés de manières opposées.
C’est une des raisons pour lesquelles il ne faut pas répliquer une
instance sur un serveur secondaire avec un système d’exploitation différent
du primaire.
Vitesse d’indexation: Postgres utilise les clefs abrégées (abbreviated keys)
quand c’est possible
(également appelées sort keys dans la terminologie ICU),
parce qu’elles peuvent vraiment accélérer l’indexation.
Mais comme le support de cette fonctionnalité via libc (strxfrm)
s’est avéré buggé dans plusieurs systèmes dont Linux pour certaines locales,
elle est seulement activée pour les collations ICU.
Comparaison de chaînes paramétrique: Avec la libc, il y a typiquement une association
figée entre la locale et la collation: par exemple fr_CA.UTF-8 compare
les chaînes avec les règles linguistiques du français tel qu’écrit au Canada,
mais sans possibilité de personnalisation ou de contrôle supplémentaire.
Avec ICU, les collations acceptent un bon nombre de paramètre qui
offre des possibilités au-delà de la spécification de la langue et du pays,
comme montré dans la démo online de collationnement ICU,
ou dans le billet de Peter Eisentraut “More robust collations with ICU support in PostgreSQL 10” annonçant l’intégration ICU l’année dernière,
ou encore dans
“What users can do with custom ICU collations in Postgres 10”
(fil de discussion dans pgsql-hackers).
Malheureusement un problème empêche d’utiliser les comparaisons avancées à leur plein potentiel: l’infrastructure actuelle des opérateurs de comparaison dans Postgres ne peut pas gérer le fait que des chaînes soient égales alors qu’elles ne sont pas équivalentes en comparaison octet par octet. Pour s’assurer que cette contrainte est bien respectée, dès que des chaînes sont considérées comme égales par un comparateur linguistique (avec une fonction de la famille de strcoll), Postgres cherche à les départager par comparaison binaire, via ce qu’on va appeler en anglais le strcmp tie-breaker; le résultat généré par le comparateur ICU (ou celui de libc d’ailleurs) est alors éliminé en faveur du résultat de la comparaison binaire.
Par exemple, on peut créer cette collation:
CREATE COLLATION "fr-ci" (
locale = 'fr-u-ks-level1', /* ou 'fr@colStrength=primary' */
provider = 'icu'
);ks-level1 ici signifie primary collation strength.
Il faut savoir que cette syntaxe avec les paramètres au format
BCP-47
ne fonctionne pas (sans pour autant émettre d’erreur) avec ICU 53 ou plus ancien.
Lorsqu’on n’est pas sûr de la syntaxe d’une collation,
elle peut être passée à icu_collation_attributes() pour vérifier
comment ICU l’analyse, comme montré par un exemple un peu plus loin
dans ce billet.
Quoiqu’il en soit, il y cinq niveaux de force de comparaison, le niveau primaire ne considérant que les caractères de base, c’est-à-dire qu’il ignore les différences engendrées par les accents et la casse (majucule ou minuscule).
Le principe de départager les chaînes égales via une comparaison binaire fait que l’égalité suivante, par exemple, ne va pas être satisfaite, contrairement à ce qu’on pourrait attendre:
=# SELECT 'Eté' = 'été' COLLATE "fr-ci" as equality;
equality
----------
fPeut-être (espérons) que dans le futur, Postgres pourra faire
fonctionner complètement ces collations indépendante de la casse
et autres, mais en attendant, il est possible de contourner
ce problème avec des fonctions de icu_ext. Voyons comment.
La fontion principale est:
icu_compare(string1 text, string2 text [, collator text]).
Elle renvoie le résultat de ucol_strcoll[UTF8](), comparant
string1 et string2 avec collator qui est la collation ICU.
C’est un entier signé, négatif si string1 < string2, zéro if string =
string2, et positif si string1 > string2.
Quand le 3ème argument collator est présent, ce n’est pas le nom d’une
collation de la base de données déclarée avec
CREATE COLLATION, mais la valeur qui serait passée
dans le paramètre locale ou lc_collate, si on devait
instancier cette collation. En d’autres termes, c’est un locale ID
au sens d’ICU, indépendamment de Postgres
(oui, ICU utilise le terme “ID” pour désigner une chaîne de caractères
dont le contenu est plus ou moins construit par aggrégation de paramètres).
Quand l’argument collator n’est pas spécifié, c’est la collation associée
à string1 et string2 qui est utilisée pour la comparaison.
Ca doit être une collation ICU et ça doit être la même pour les
deux arguments, ou la fonction sortira une erreur.
Cette forme avec deux arguments est significativement plus rapide
du fait que Postgres garde ses collations ouvertes (au sens de
ucol_open()/ucol_close()) pour la durée de la session,
tandis que l’autre forme avec l’argument collator explicite ouvre
et ferme la collation ICU à chaque appel.
Pour revenir à l’exemple précédent, cette fois on peut constater l’égalité des chaînes de caractère sous le régime de l’insensibilité à la casse et aux accents:
=# SELECT icu_compare('Eté', 'été', 'fr-u-ks-level1');
icu_compare
-------------
0Les comparaisons sensibles à la casse mais insensibles aux accents sont aussi possibles:
=# SELECT icu_compare('abécédaire','abecedaire','fr-u-ks-level1-kc'),
icu_compare('Abécédaire','abecedaire','fr-u-ks-level1-kc');
icu_compare | icu_compare
-------------+-------------
0 | 1Autre exemple, cette fois avec une collation Postgres implicite:
=# CREATE COLLATION mycoll (locale='fr-u-ks-level1', provider='icu');
CREATE COLLATION
=# CREATE TABLE books (id int, title text COLLATE "mycoll");
CREATE TABLE
=# insert into books values(1, 'C''est l''été');
INSERT 0 1
=# select id,title from books where icu_compare (title, 'c''est l''ete') = 0; id | title
----+-------------
1 | C'est l'été
Avec Unicode, les lettres accentuées peuvent être écrites sous une forme composée ou décomposée, cette dernière signifiant qu’il y a une lettre sans accent suivi d’un caractère d’accentuation faisant partie du bloc des diacritiques combinatoires.
Les deux formes avec décomposition, NFD or NFKD
ne sont pas fréquemment utilisées dans les documents UTF-8, mais elles
sont parfaitement valides et acceptées par Postgres.
Sur le plan sémantique, 'à' est supposément équivalent à
E'a\u0300'. En tout cas, le collationnement ICU semble les considérer comme
égaux, y compris sous le niveau de comparaison le plus strict:
=# CREATE COLLATION "en-identic" (provider='icu', locale='en-u-ks-identic');
CREATE COLLATION
=# SELECT icu_compare('à', E'a\u0300', 'en-u-ks-identic'),
'à' = E'a\u0300' COLLATE "en-identic" AS "equal_op";
icu_compare | equal_op
-------------+----------
0 | f(à nouveau, l’opérateur d’égalité de Postgres donne un résultat différent à cause de la comparaison binaire qui départage les deux arguments. C’est précisement pour contourner ça qu’on utilise une fonction au lieu de l’opérateur d’égalité).
Par ailleurs, les caractères combinatoires ne concernent pas seulement les accents, certains servent aussi à réaliser des effets sur le texte comme l’effet barré ou le soulignement. Voyons un exemple dans psql, tel qu’affiché dans un terminal gnome.
La requête de la capture d’écran ci-dessous prend le texte litéral ‘Hello’, insère les caractères combinatoires de U+0330 à U+0338 après chaque caractère, renvoie la chaîne résultante, ainsi que les résultats des comparaisons linguisitique primaire et secondaire avec le texte de départ.
En général, les fonctions ICU prennent en compte les caractères combinatoires à chaque fois que ça a du sens, alors que les fonctions de Postgres hors ICU (celles des expressions régulières par exemple) considèrent que le caractère combinatoire est un point de code comme un autre.
Les clauses ORDER BY et GROUP BY ne sont pas conçues pour fonctionner avec des fonctions à deux arguments, mais avec des fonctions à un seul argument qui le transforment en quelque chose d’autre.
Pour trier ou regrouper des résultats d’après les règles linguistiques avancées, icu_ext expose une fonction qui convertit une chaîne en une clé de tri:
function icu_sort_key(string text [, icu_collation text]) returns bytea
C’est la même clé de tri que celle utilisée implicitement par Postgres pour créer des index impliquant des collations ICU.
La promesse de la clé de tri est que, si icu_compare(s1, s2, collator) renvoie X,
alors la comparaison (plus rapide) au niveau octet entre icu_sort_key(s1, collator) et
icu_sort_key(s2, collator) renvoie X également.
La documentation ICU prévient que le calcul d’une clé de tri est susceptible d’être nettement plus lent que de faire une seule comparaison avec la même collation. Mais dans le contexte d’un ORDER BY sur des requêtes et pour autant que mes tests soient représentatifs, c’est plutôt très rapide.
Du reste, en comparant les performances de ORDER BY field COLLATE "icu-coll" par
rapport à
ORDER BY icu_sort_key(field, 'collation'), la plus grande part de la différence
est causée par le fait qu’ icu_sort_key doive analyser la spécification
de la collation à chaque appel, et cette différence semble d’autant plus
grande que la spécification est complexe.
Tout comme pour icu_compare(), pour bénéficier du fait que Postgres
garde ouvertes les collations ICU pour la durée de la session,
il est recommandé d’utiliser la forme à un seul argument,
qui s’appuie sur sa collation, par exemple avec notre
“fr-ci” définie précédemment:
=# SELECT icu_sort_key ('Eté' COLLATE "fr-ci")
Toujours sur les performances, voici une comparaison d’EXPLAIN ANALYZE pour
trier 6,6 million de mots courts (de 13 caractères en moyenne) avec
icu_sort_key versus ORDER BY directement sur le champ:
ml=# explain analyze select wordtext from words order by icu_sort_key(wordtext collate "frci");
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Gather Merge (cost=371515.53..1015224.24 rows=5517118 width=46) (actual time=3289.004..5845.748 rows=6621524 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=370515.51..377411.90 rows=2758559 width=46) (actual time=3274.132..3581.209 rows=2207175 loops=3)
Sort Key: (icu_sort_key((wordtext)::text))
Sort Method: quicksort Memory: 229038kB
-> Parallel Seq Scan on words (cost=0.00..75411.99 rows=2758559 width=46) (actual time=13.361..1877.528 rows=2207175 loops=3)
Planning time: 0.105 ms
Execution time: 6165.902 msml=# explain analyze select wordtext from words order by wordtext collate "frci";
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Gather Merge (cost=553195.63..1196904.34 rows=5517118 width=132) (actual time=2490.891..6231.454 rows=6621524 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=552195.61..559092.01 rows=2758559 width=132) (actual time=2485.254..2784.511 rows=2207175 loops=3)
Sort Key: wordtext COLLATE frci
Sort Method: quicksort Memory: 231433kB
-> Parallel Seq Scan on words (cost=0.00..68515.59 rows=2758559 width=132) (actual time=0.023..275.519 rows=2207175 loops=3)
Planning time: 0.701 ms
Execution time: 6565.687 msOn peut voir ici qu’il n’y a pas de dégradation notable des performances
lors de l’appel de icu_sort_key explicitement. En fait, dans cet exemple c’est
même un peu plus rapide, sans que je sache vraiment pourquoi.
GROUP BY et DISTINCT ON peuvent aussi utiliser des clés de tri:
=# select count(distinct title) from books;
count
-------
2402
=# select count(distinct icu_sort_key(title)) from books;
count
-------
2360La position post-tri ou l’unicité d’un texte sous une certaine
collation équivaut à la position post-tri ou l’unicité de la clé binaire
correspondante dans cette collation.
Par conséquent il est possible de créer un index, y compris pour appliquer
une contrainte unique, sur icu_sort_key(column) ou icu_sort_key(column, collator)
plutôt que simplement column, pour contourner le problème
de la règle Postgres “pas d’égalité si la représentation binaire est différente”.
En reprenant l’exemple précédent avec la table books, on pourrait faire:
=# CREATE INDEX ON books (icu_sort_key(title));
pour qu’ensuite cet index soit utilisé pour des recherches exactes avec une requête comme suit:
=# SELECT title FROM books WHERE
icu_sort_key(title) = icu_sort_key('cortege' collate "mycoll");
title
---------
Cortège
CORTÈGE
Juste pour tester que l’index est effectivement utilisé:
=# explain select title from books where icu_sort_key(title)=icu_sort_key('cortege' collate "mycoll");
QUERY PLAN
-------------------------------------------------------------------------------------
Bitmap Heap Scan on books (cost=4.30..10.64 rows=2 width=29)
Recheck Cond: (icu_sort_key(title) = '\x2d454b4f313531'::bytea)
-> Bitmap Index Scan on books_icu_sort_key_idx (cost=0.00..4.29 rows=2 width=0)
Index Cond: (icu_sort_key(title) = '\x2d454b4f313531'::bytea)
Comme mentionné plus haut, quand on se réfère à une collation par son identifiant ICU, les anciennes versions d’ICU ne comprennent pas la syntaxe plus moderne des tags BCP-47, ce qui ne se traduit pas nécessairement par une erreur, ils sont simplement ignorés.
Pour s’assurer qu’une collation est correctement nommée ou qu’elle
a les caractéristiques attendues, on peut contrôler la sortie de
icu_collation_attributes().
Cette fonction prend un nom de collation ICU en entrée,
récupère ses propriétés et les renvoie en tant qu’ensemble
de couples (attribute, value) comprenant son nom “affichable”
(displayname, probablement l’attribut le plus intéressant),
plus les tags kn / kb / kk / ka / ks / kf / kc correspondant à ses
caractéristiques, et enfin la version de la collation.
Exemple:
postgres=# select * from icu_collation_attributes('en-u-ks-identic');
attribute | value
-------------+---------------------------------
displayname | anglais (colstrength=identical)
kn | false
kb | false
kk | false
ka | noignore
ks | identic
kf | false
kc | false
version | 153.80
(9 rows)
-- Ci-dessus le displayname est en français, mais
-- on pourait le demander par exemple en japonais:
postgres=# select icu_set_default_locale('ja');
icu_set_default_locale
------------------------
ja
(1 row)
-- à noter le changement dans displayname
postgres=# select * from icu_collation_attributes('en-u-ks-identic');
attribute | value
-------------+------------------------------
displayname | 英語 (colstrength=identical)
kn | false
kb | false
kk | false
ka | noignore
ks | identic
kf | false
kc | false
version | 153.80
(9 rows)Au-delà des comparaisons de chaînes et des clés de tri, icu_ext
implémente des accesseur SQL à d’autres fonctionnalités d’ICU:
(voir le README.md
des sources pour les exemples d’appels aux fonctions):
icu_{character,word,line,sentence}_boundaries
Découpe un texte selon ses constituants et renvoie les morceaux
en type SETOF text.
En gros c’est regexp_split_to_table(string, regexp) en mieux dans
le sens où sont utilisées les règles linguistiques recommandées par
la standard Unicode, au lieu de simplement repérer les séparateurs sur
la base d’expressions rationnelles.
icu_char_name
Renvoie le nom Unicode de tout caractère (fonctionne avec les 130K+ du jeu complet).
icu_confusable_strings_check and icu_spoof_check
Indique si un couple de chaînes est similaire, visuellement et si une chaîne
comprend des caractères qui prêtent à confusion (spoofing).
icu_locales_list
Sort la liste des toutes les locales avec les langues et pays associés,
exprimés dans la langue en cours. Accessoirement, ça permet d’obtenir
les noms de pays et de langues traduits en plein de langues (utiliser
icu_set_default_locale() pour changer de langue).
icu_number_spellout
Exprime un nombre en version textuelle dans la langue en cours.
icu_transforms_list et icu_transform
Applique des translitération (conversions entre écritures) et autres
transformations complexes de texte. Il y a plus de 600
transformations de base listées par icu_transforms_list et elles
peuvent combinées ensemble et avec des filtres. Voir la démo en
ligne de ce service.
D’autres fonctions devraient être ajoutées dans le futur à
icu_ext,
ainsi que d’autres exemples d’utilisation des fonctions existantes.
En attendant n’hésitez pas à proposer des changements sur github
pour faire évoluer ces fonctions, ou exposer d’autres services
ICU en SQL, ou encore exposer différemment ceux qui le sont déjà,
ou bien entendu signaler des bugs…
Une nouvelle version de pg_stat_kcache est disponible, ajoutant la compatibilité avec Windows et d’autres plateformes, ainsi que l’ajout de nouveaux compteurs.
La version 2.1 de pg_stat_kcache vient d’être publiée.
Les deux nouvelles fonctionnalités principales sont:
getrusage() (comme Windows) ;getrusage() sont exposés.Comme je l’expliquais dans a previous article, cette extension est un wrapper sur getrusage, qui accumule des compteurs de performance par requête normalisée. Cela donnait déjà de précieuses informations qui permettaient aux DBA d’identifier des requêtes coûteuse en temps processeur par exemple, ou de calculer un vrai hit-ratio.
Cependant, cela n’était disponible que sur les plateforme disposant nativement
de la fonction getrusage, donc Windows and quelques autres platformes
n’étaient pas supportées. Heureusement, PostgreSQL permet un support basique
de getrusage()
sur ces plateformes. Cette infrastructure a été utilisée dans la version 2.1.0
de pg_stat_kcache, ce qui veut dire que vous pouvez maintenant utiliser cette
extension sur Windows et toutes les autres plateformes qui n’étaient auparavant
pas supportées. Comme il s’agit d’un support limité, seule le temps processeur
utilisateur et système sont supportés, les autres champs seront toujours NULL.
Cette nouvelle version expose également tous les autres champs de getrusage()
ayant un sens dans le cadre d’une accumulation par requête :
accumulated per query:
Un autre changement est de détecter automatiquement la précision du chronomètre système. Sans celas, les requêtes très rapides (plus rapides que la précision maximale du chronomètre) seraient détectées soit comme n’ayant pas consommé de temps processeur, soit ayant consommé le temps processeur d’autres requêtes très rapides. Pour les requêtes durant moins que 3 fois la précision du chronomètre système, où l’imprécision est importante, pg_stat_kcache utilisera à la place la durée d’exécution de la requête comme temps d’utilisation processeur utilisateur et gardera à 0 le temps d’utilisation processeur système.
En fonction de votre plateforme, certains des nouveaux compteurs ne sont pas
maintenus. Sur GNU/Linux par exemple, les swaps, messages IPC et signaux ne
sont malheureusement pas maintenus, mais ceux qui le sont restent tout à fait
intéressants. Par exemple, comparons les context switches si nous effectuons
le même nombre de transactions, mais avec 2 et 80 connexions concurrentes sur
une machine disposant de 4 cœeurs :
psql -c "SELECT pg_stat_kcache_reset()"
pgbench -c 80 -j 80 -S -n pgbench -t 100
[...]
number of transactions actually processed: 8000/8000
latency average = 8.782 ms
tps = 9109.846256 (including connections establishing)
tps = 9850.666577 (excluding connections establishing)
psql -c "SELECT user_time, system_time, minflts, majflts, nvcsws, nivcsws FROM pg_stat_kcache WHERE datname = 'pgbench'"
user_time | system_time | minflts | majflts | nvcsws | nivcsws
-------------------+--------------------+---------+---------+--------+---------
0.431648000000005 | 0.0638690000000001 | 24353 | 0 | 91 | 282
(1 row)
psql -c "SELECT pg_stat_kcache_reset()"
pgbench -c 2 -j 2 -S -n pgbench -t 8000
[...]
number of transactions actually processed: 8000/8000
latency average = 0.198 ms
tps = 10119.638426 (including connections establishing)
tps = 10188.313645 (excluding connections establishing)
psql -c "SELECT user_time, system_time, minflts, majflts, nvcsws, nivcsws FROM pg_stat_kcache WHERE datname = 'pgbench'"
user_time | system_time | minflts | majflts | nvcsws | nivcsws
-------------------+-------------+---------+---------+--------+---------
0.224338999999999 | 0.023669 | 5983 | 0 | 0 | 8
(1 row)Sans surprise, utiliser 80 connexions concurrentes sur un ordinateur portable n’ayant que 4 cœeurs n’est pas la manière la plus efficaces de traiter 8000 transactions. La latence est 44 fois plus lentes avec 80 connexions plutôt que 2. Au niveau du système d’exploitation, on peut voir qu’avec seulement 2 connexions concurrentes, nous n’avons que 8 context switches involontaires sur la totalités des requêtes de la base pgbench, alors qu’il y en a eu 282, soit 35 fois plus avec 80 connexions concurrentes.
Ces nouvelles métriques donnent de nombreuses nouvelles informations sur ce qu’il se passe au niveau du système d’exploitation, avec une granularité à la requête normalisée, ce qui pourra faciliter le diagnostique de problèmes de performances. Combiné avec PoWA, vous pourrez même identifier à quel moment n’importe laquelle de ces métriques a un comportement différent !
pg_stat_kcache 2.1 disponible was originally published by Julien Rouhaud at rjuju's home on July 17, 2018.
Cet article de blog est le résumé d’un problème rencontré en production que j’ai eu à diagnostiquer il y a quelques mois avec des gens d’ Oslandia, et puisqu’il s’agit d’un problème pour le moins inhabituel j’ai décidé de le partager avec la méthodologie que j’ai utilisée, au cas où cela puisse aider d’autres personnes qui rencontreraient le même type de problème. C’est également une bonne occasion de rappeler que mettre à jour PostgreSQL vers une nouvelle version est une bonne pratique.
Le problème de performance initialement rapporté contenait suffisamment d’informations pour savoir qu’il s’agissait d’un problème étrange.
Le serveur utilise un PostgreSQL 9.3.5. Oui, il y a plusieurs versions mineures de retard, et bien évidémment bon nombre de versions majeures de retard. La configuration était également quelque peu inhabituelle. Les réglages et dimensionnement physiques les plus importants sont :
Serveur
CPU: 40 cœurs, 80 avec l'hyperthreading activé
RAM: 128 Go
PostgreSQL:
shared_buffers: 16 Go
max_connections: 1500
La valeur élevée pour le shared_buffers, surtout puisqu’il s’agit d’une
versions de PostgreSQL plutôt ancienne, est une bonne piste d’investigation.
Le max_connections est également assez haut, mais malheureusement l’éditeur
logiciel mentionne qu’il ne supporte pas de pooler de connexion. Ainsi, la
plupart des connexions sont inactives. Ce n’est pas idéal car cela implique un
surcoût pour acquérir un snapshot, mais il y a suffisamment de cœurs de processeur pour gérer un grand nombre de connexions.
Le problème principale était que régulièrement, les même requêtes pouvaient être extrêmement lentes. Ce simple exemple de reqête était fourni :
EXPLAIN ANALYZE SELECT count(*) FROM pg_stat_activity ;
-- Quand le problème survient
"Aggregate (actual time=670.719..670.720 rows=1 loops=1)"
" -> Nested Loop (actual time=663.739..670.392 rows=1088 loops=1)"
" -> Hash Join (actual time=2.987..4.278 rows=1088 loops=1)"
" Hash Cond: (s.usesysid = u.oid)"
" -> Function Scan on pg_stat_get_activity s (actual time=2.941..3.302 rows=1088 loops=1)"
" -> Hash (actual time=0.022..0.022 rows=12 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 1kB"
" -> Seq Scan on pg_authid u (actual time=0.008..0.013 rows=12 loops=1)"
" -> Index Only Scan using pg_database_oid_index on pg_database d (actual time=0.610..0.611 rows=1 loops=1088)"
" Index Cond: (oid = s.datid)"
" Heap Fetches: 0"
"Total runtime: 670.880 ms"
-- Temps de traitement normal
"Aggregate (actual time=6.370..6.370 rows=1 loops=1)"
" -> Nested Loop (actual time=3.581..6.159 rows=1088 loops=1)"
" -> Hash Join (actual time=3.560..4.310 rows=1088 loops=1)"
" Hash Cond: (s.usesysid = u.oid)"
" -> Function Scan on pg_stat_get_activity s (actual time=3.507..3.694 rows=1088 loops=1)"
" -> Hash (actual time=0.023..0.023 rows=12 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 1kB"
" -> Seq Scan on pg_authid u (actual time=0.009..0.014 rows=12 loops=1)"
" -> Index Only Scan using pg_database_oid_index on pg_database d (actual time=0.001..0.001 rows=1 loops=1088)"
" Index Cond: (oid = s.datid)"
" Heap Fetches: 0"
"Total runtime: 6.503 ms"Ainsi, bien que le « bon » temps de traitement est un petit peu lent (bien qu’il y ait 1500 connections), le « mauvais » temps de traitement est plus de 100 fois plus lent, pour une requête tout ce qu’il y a de plus simple.
Un autre exemple de requête applicative très simple était fourni, mais avec un peu plus d’informations. Voici une versino anonymisée :
EXPLAIN (ANALYZE, BUFFERS) SELECT une_colonne
FROM une_table
WHERE une_colonne_indexee = 'valeur' AND upper(autre_colonne) = 'autre_value'
LIMIT 1 ;
"Limit (actual time=7620.756..7620.756 rows=0 loops=1)"
" Buffers: shared hit=43554"
" -> Index Scan using idx_some_table_some_col on une_table (actual time=7620.754..7620.754 rows=0 loops=1)"
" Index Cond: ((some_indexed_cold)::text = 'valeur'::text)"
" Filter: (upper((autre_colonne)::text) = 'autre_value'::text)"
" Rows Removed by Filter: 17534"
" Buffers: shared hit=43554"
"Total runtime: 7620.829 ms"
"Limit (actual time=899.607..899.607 rows=0 loops=1)"
" Buffers: shared hit=43555"
" -> Index Scan using idx_some_table_some_col on une_table (actual time=899.605..899.605 rows=0 loops=1)"
" Index Cond: ((some_indexed_cold)::text = 'valeur'::text)"
" Filter: (upper((autre_colonne)::text) = 'autre_value'::text)"
" Rows Removed by Filter: 17534"
" Buffers: shared hit=43555"
"Total runtime: 899.652 ms"Il y avait également beaucoup de données de supervision disponibles sur le
système d’exploitation, montrant que les disques, les processeurs et la
mémoire vive avaient toujours des ressources disponibles, et il n’y avait aucun
message intéressant dans la sortie de dmesg ou aucune autre trace système.
Pour la première requête, nous voyons que le parcours d’index interne augmente de 0.001ms à 0.6ms:
-> Index Only Scan using idx on pg_database (actual time=0.001..0.001 rows=1 loops=1088)
-> Index Only Scan using idx on pg_database (actual time=0.610..0.611 rows=1 loops=1088)Avec un shared_buffers particuli_rement haut et une version de PostgreSQL
ancienne, il est fréquent que des problèmes de lenteur surviennent si la taille
du jeu de données est plus important que le shared_buffers, du fait de
l’algorithme dit de « clocksweep » utilisé pour sortir les entrées du
shared_buffers.
Cependant, la seconde requête montre que le même problème survient alors que
tous les blocs se trouvent dans le shared_buffers. Cela ne peut donc pas
être un problème d’éviction de buffer dû à une valeur de shared_buffers trop
élevée, ou un problème de latence sur le disque.
Bien que des paramètres de configuration de PostgreSQL puissent être améliorés, aucun de ceux-ci ne peuvent expliquer ce comportement en particulier. Il serait tout à fait possible que la modification de ces paramètres corrige le problème, mais il faut plus d’informations pour comprendre ce qui se passe exactement et éviter tout problème de performance à l’avenir.
Puisque les explications les plus simples ont déjà été écartées, il faut penser à des causes de plus bas niveau.
Si vous avez suivi les améliorations dans les dernières versions de PostgreSQL, vous devriez avoir noté un bon nombre d’optimisations concernant la scalabilité et le verrouillage. Si vous voulez plus de détails sur ces sujets, il y a de nombreux articles de blogs, par exemple ce très bon article.
Du côté du du noyau Linux, étant donné le grand nombre de connexions cela peut ếgalement être, et c’est certainement l’explication la plus probable, dû à une saturation du TLB.
Dans tous les cas, pour pouvoir confirmer une théorie il faut utiliser des outils beaucoup plus pointus.
Sans aller trop dans le détail, il faut savoir que chaque processus a une zone
de mémoire utilisée par le noyau pour stocker les « page tables
entries », ou PTE,
c’est-à-dire les translations des adresses virtuelles utilisées par le
processus et la vrai adresse physique en RAM. Cette zone n’est normalement pas
très volumineuse, car un processus n’accès généralement pas à des dizaines de
giga-octets de données en RAM. Mais puisque PostgreSQL repose sur une
architecture où chaque connexion est un processus dédié qui accède à un gros
segment de mémoire partagée, chaque processus devra avoir une translation
d’adresse pour chaque zone de 4 Ko (la taille par défaut d’une page) du
shared_buffers qu’il aura accédé. Il est donc possible d’avoir une grande
quantité de mémoire utilisée pour la PTE, et même d’avoir au total des
translations d’adresse pour adresser bien plus que la quantité total de mémoire
physique disponible sur la machine.
Vous pouvez connaître la taille de la PTE au niveau du système d’exploitation
en consultant l’entrée VmPTE dans le statut du processus. Vous pouvez
également vérifier l’entrée RssShmem pour savoir pour combien de pages en
mémoire partagée il existe des translations. Par exemple :
egrep "(VmPTE|RssShmem)" /proc/${PID}/status
RssShmem: 340 kB
VmPTE: 140 kBCe processus n’a pas accédé à de nombreux buffers, sa PTE est donc petite. If nous essayons avec un processus qui a accédé à chacun des buffers d’un shared\hbuffers de 8 Go :
egrep "(VmPTE|RssShmem)" /proc/${PID}/status
RssShmem: 8561116 kB
VmPTE: 16880 kBIl y a donc 16 Mo utilisés pour la PTE ! En multipliant ça par le nombre
de connexion, on arrive à plusieurs giga-octets de mémoire utilisée pour la
PTE. Bien évidemment, cela ne tiendra pas dans le TLB. Par conséquent, les
processus auront de nombreux « échec de translation » (TLB miss) quand ils
essaieront d’accéder à une page en mémoire, ce qui augmentera la latence de
manière considérable.
Sur le système qui rencontrait ces problèmes de performance, avec 16 Go de shared_buffers et 1500 connexions persistente, la mémoire totale utilisée pour les PTE combinées était d’environ 45 Go ! Une approximation peut être faîte avec le script suivant:
for p in $(pgrep postgres); do grep "VmPTE:" /proc/$p/status; done | awk '{pte += $2} END {print pte / 1024 / 1024}'NOTE: Cet exemple calculera la mémoire utilisée pour la PTE de tous les processus postgres. Si vous avez de plusieurs instances sur la même machine et que vous voulez connaître l’utilisation par instance, vous devez adapter cette commande pour ne prendre en compte que les processus dont le ppid est le pid du postmaster de l’instance voulue.
C’est évidemment la cause des problèmes rencontrés. Mais pour en être sûr,
regardons ce que perf nous remonte lorsque les problèmes de performance
surviennent, et quand ce n’est pas le cas.
Voici les fonctions les plus consommatrices (consommant plus de 2% de CPU) remontées par perf lorsque tout va bien :
# Children Self Command Symbol
# ........ ........ ............... ..................
4.26% 4.10% init [k] intel_idle
4.22% 2.22% postgres [.] SearchCatCacheRien de vraiment bien intéressant, le système n’est pas vraiment saturé. Maintenant, quand le problème survient :
# Children Self Command Symbol
# ........ ........ ............... ....................
8.96% 8.64% postgres [.] s_lock
4.50% 4.44% cat [k] smaps_pte_entry
2.51% 2.51% init [k] poll_idle
2.34% 2.28% postgres [k] compaction_alloc
2.03% 2.03% postgres [k] _spin_lockNous pouvons voir s_lock, la fonction de PostgreSQL qui attend sur un
spinlock consommant preque 9% du
temps processeur. Mais il s’agit de PostgreSQL 9.3, et les ligthweight locks
(des verrous internes transitoires) étaient encore implémentés à l’aide de spin
lock (ils sont maintenant implémentés à l’aide d’opérations
atomiques).
Si nous regardons un peu plus en détails les appeks à s_lock :
8.96% 8.64% postgres [.] s_lock
|
---s_lock
|
|--83.49%-- LWLockAcquire
[...]
|--15.59%-- LWLockRelease
[...]
|--0.69%-- 0x6382ee
| 0x6399ac
| ReadBufferExtended
[...]99% des appels à s_lock sont en effet dûs à des lightweight locks. Cela
indique un ralentissement général et de fortes contentions. Mais cela n’est que la conséquence du vrai problème, la seconde fonction la plus consommatrice.
Avec presque 5% du temps processeur, smaps_pte_entry, une fonction du noyau
effectuant la translation d’addresse pour une entrée, nous montre le problème.
Cette fonction devrait normalement être extrêmement rapide, et ne devrait même
pas apparaître dans un rapport perf ! Cela veut dire que très souvent, quand
un processus veut accéder à une page en mémoire, il doit attendre pour obtenir
sa vraie adresse. Mais attendre une translation d’adresse veut dire beaucoup
de bulles (pipeline stalls).
Les processeurs ont des pipelines de plus en plus profonds, et ces bulles
ruinent complètement les bénéfices de ce type d’architecture. Au final, une
bonne proportion du temps est tout simplement gâchée à attendre des adresses.
Ça explique très certainement les ralentissements extrêmes, ainsi que le manque
de compteurs de plus haut niveau permettant de les expliquer.
Plusieurs solutions sont possibles pour résoudre ce problème.
La solution habituelle est de demande à PostgreSQL d’allouer le
shared_buffers dans des huge
pages.
En effet, avec des pages de 2 Mo plutôt que 4 ko, la mémoire utilisée pour la
PTE serait automatiquement diminuée d’un facteur 512. Cela serait un énorme
gain, et extrêment facile à mettre en place. Malheureusement, cela n’est
possible qu’à partir de la version 9.4, mais mettre à jour la version majeure
de PostgreSQL n’était pas possible puisque l’éditeur ne supporte pas une
version supérieure à la version 9.3.
Un autre moyen de réduire la taille de la PTE est de réduire le nombre de connexion, qui ici est assez haut, ce qui aurait probablement d’autres effets positifs sur les performances. Encore une fois, ce n’était malheureusement pas possible puisque l’éditeur affirme ne pas supporter les poolers de connexion et que le client a besoin de pouvoir gérer un grand nombre de connexions.
Ainsi, la seule solution restante était donc de réduire la taille du shared_buffers. Après quelques essais, la plus haute valeur qui pouvaient être utilisée sans que les ralentissements extrêmes ne surviennent était de 4 Go. Heureusement, PostgreSQL était capable de conserver des performances assez bonnes avec cette taille de cache dédié.
Si des des éditeurs logiciels lisent cette article, il faut comprendre que si on vous demande la compatibilité avec des versions plus récentes de PostgreSQL, ou avec des poolers de connexion, il y a de très bonnes raisons à cela. Il y a généralement très peu de changements de comportement avec les nouvelles versions, et elles sont toutes documentées !
Diagnostique de lenteurs inattendues was originally published by Julien Rouhaud at rjuju's home on July 11, 2018.
Depuis la version 10, PostgreSQL permet d’utiliser la bibliothèque de référence Unicode ICU pour trier et indexer les textes à travers les collations ICU.
Au-delà de ce que permet le coeur, d’autres fonctionnalités de cette bibliothèque sont exposables en SQL, c’est ce que fait l’extension icu_ext, qui couvre déjà une partie de l’API ICU, et devrait s’étoffer petit à petit. L’intérêt d’utiliser ICU en SQL est essentiellement de profiter de ses algorithmes gérant du texte multilingue en collant au plus près au standard Unicode.
J’aurais certainement l’occasion d’en reparler plus en détail dans ce blog en tant qu’auteur de l’extension, mais en attendant j’ai eu le plaisir de présenter sur ce thème au meetup PG Paris le 28 juin:
Un grand merci aux organisateurs du meetup et à MeilleursAgents pour l’accueil dans leurs très jolis locaux!
Il existe une morgue où l'on peut trouver les vieux paquets (.deb) pour debian (http://atalia.postgresql.org/morgue/) mais pour les distributions Centos/RedHat, la communauté ne propose pas de morgue... (à ma connaissance)
Vous trouverez à cette adresse une morgue pour les paquets RPM pour Centos/Redhat :
Elle se trouve ici : [http://pgyum-morgue.ng.pe/]
Si vous voulez créer un mirroir ou pour toutes questions/commentaires n'hésitez pas à me contacter :)
Le pivot est une opération par laquelle des données sous une colonne deviennent des noms de colonnes, de sorte que visuellement une sorte de rotation à 90° est opérée: ce qui se lisait verticalement de haut en bas se retrouve ordonné horizontalement de gauche à droite. Les termes “transposition”, “requête analyse croisée” ou “crosstab” sont aussi utilisés pour désigner ce concept, qui vient instiller un peu de la vision “tableur” des données dans l’approche relationnelle.
Dans le cas le plus simple, on part d’une colonne qui est fonction d’une autre. Considérons un exemple météorologique à 2 colonnes: l’année, et la pluviométrie correspondante, exprimée en nombre de jours où les précipitations ont dépassé 1mm.
Avant pivot:
Année | Pluie |
----------+----------+
2012 | 112 |
2013 | 116 |
2014 | 111 |
2015 | 80 |
2016 | 110 |
2017 | 102 |
Après pivot:
2012 | 2013 | 2014 | 2015 | 2016 | 2017
------+------+------+------+------+------
112 | 116 | 111 | 80 | 110 | 102
Souvent, il y a une deuxième dimension, c’est-à-dire qu’on part de 3 colonnes, dont une dépend fonctionnellement des deux autres: (dimension X, dimension Y)=>Valeur.
Dans l’exemple de la pluviométrie, la seconde dimension pourrait être un nom de ville, comme ci-dessous:
Avant pivot:
Année | Ville | Jours
-------+-----------+-------
2012 | Lyon | 112
2013 | Lyon | 116
2014 | Lyon | 111
... | ... | ...
2014 | Toulouse | 111
2015 | Toulouse | 83
Considérons un jeu de données réduit de 13 villes x 6 ans. La série ci-dessus ferait donc 78 lignes. (un dump SQL pour cet exemple est disponible en téléchargement ici: exemple-pluviometrie.sql; les données brutes sont à l’échelle du mois et proviennent originellement de https://www.infoclimat.fr/climatologie/).
Et voici une présentation typique après pivot:
Ville | 2012 | 2013 | 2014 | 2015 | 2016 | 2017
-----------+------+------+------+------+------+------
Ajaccio | 69 | 91 | 78 | 48 | 81 | 51
Bordeaux | 116 | 138 | 137 | 101 | 117 | 110
Brest | 178 | 161 | 180 | 160 | 165 | 144
Dijon | 114 | 124 | 116 | 93 | 116 | 103
Lille | 153 | 120 | 136 | 128 | 138 | 113
Lyon | 112 | 116 | 111 | 80 | 110 | 102
Marseille | 47 | 63 | 68 | 53 | 54 | 43
Metz | 98 | 120 | 110 | 93 | 122 | 115
Nantes | 124 | 132 | 142 | 111 | 106 | 110
Nice | 53 | 77 | 78 | 50 | 52 | 43
Paris | 114 | 111 | 113 | 85 | 120 | 110
Perpignan | 48 | 56 | 54 | 48 | 69 | 48
Toulouse | 86 | 116 | 111 | 83 | 102 | 89
(13 lignes)
Pour exprimer ça de manière générale, le résultat pivoté d’une série de tuples (X,Y,V) est une grille de N+1 colonnes fois M lignes, où:
N est le nombre de valeurs distinctes de X
M est le nombre de valeurs distinctes de Y.
la 1ere colonne (la plus à gauche) porte les valeurs distinctes de Y, généralement dans un ordre défini, par exemple ici les noms de ville dans l’ordre alphabétique.
les noms des autres colonnes sont constituées des valeurs distinctes de X, également dans un ordre défini. Dans l’exemple ci-dessus ce sont les années, par ordre croissant ou décroissant.
pour chaque couple (X,Y), si un tuple (X,Y,V) est présent dans le jeu de données avant pivot, la valeur V est placée dans la grille au croisement de la colonne de nom X et de la ligne commençant par Y. C’est pourquoi on parle de tableau croisé (crosstab). Si à (X,Y) ne correspond pas de V dans le jeu de données, la case correspondante reste vide (NULL), ou à une valeur spécifique si on préfère.
Quand le nombre de colonnes reste raisonnable, cette représentation a quelques avantages visuels par rapport à l’affichage en ligne des tuples (X,Y,V):
Contrairement à Oracle ou MS-SQL Server, PostgreSQL n’a pas de clause PIVOT dans son dialecte SQL, mais cette clause n’est pas essentielle. Une requête pour pivoter ces données à 3 colonnes (x,y,v) peut s’écrire sous cette forme:
SELECT
y,
(CASE WHEN x='valeur 1' THEN v END) "valeur 1",
(CASE WHEN x='valeur 2' THEN v END) "valeur 2",
...à répéter pour chaque valeur de x devenant une colonne
FROM table ou sous-requête
[ORDER BY 1]
Assez fréquemment une requête pivot agrège en même temps qu’elle pivote: La forme sera alors plutôt de ce genre-là:
SELECT
y,
AGG(v) FILTER (WHERE x='valeur 1') AS "valeur 1",
AGG(v) FILTER (WHERE x='valeur 2') AS "valeur 2",
...à répéter pour chaque valeur de x devenant une colonne
FROM table ou sous-requête
GROUP BY y [ORDER BY 1];
La clause FILTER est une nouveauté de PostgreSQL 9.4, sinon il est toujours possible d’utiliser une expression CASE WHEN. AGG(v) symbolise une fonction d’agrégation, qui pourrait être typiquement SUM(v) pour cumuler des valeurs, ou COUNT(v) pour compter des occurrences, ou encore MIN() ou MAX().
Pour la table d’exemple de pluviométrie sur 6 ans, si on a une mesure par mois et qu’on veut afficher un pivot Ville/Année, la requête serait la suivante:
SELECT
ville,
SUM(pluvio) FILTER (WHERE annee=2012) AS "2012",
SUM(pluvio) FILTER (WHERE annee=2013) AS "2013",
SUM(pluvio) FILTER (WHERE annee=2014) AS "2014",
SUM(pluvio) FILTER (WHERE annee=2015) AS "2015",
SUM(pluvio) FILTER (WHERE annee=2016) AS "2016",
SUM(pluvio) FILTER (WHERE annee=2017) AS "2017"
FROM pluviometrie
GROUP BY ville
ORDER BY ville;L’extension tablefunc
de contrib fournit entre autres une fonction:
crosstab(text source_sql, text category_sql)
qui est souvent citée en premier dans les questions sur les pivots avec Postgres.
Le premier argument de crosstab est une requête renvoyant les
données avant pivot. Le deuxième argument est une autre requête
renvoyant les noms des colonnes après pivot, dans l’ordre désiré. La
fonction renvoyant un type SETOF RECORD, en pratique il faut
re-spécifier ces colonnes via une clause AS (col1 type, col2 type,
etc...) pour qu’elles soient interprétables comme telles par le moteur
SQL.
Exemple:
SELECT * FROM crosstab(
-- requête pour le contenu de la grille
'SELECT ville,annee,SUM(pluvio)
FROM pluviometrie GROUP BY ville,annee ORDER BY ville',
-- requête pour l'entête horizontal
'SELECT DISTINCT annee FROM pluviometrie ORDER BY annee')
AS ("Ville" text,
"2012" int,
"2013" int,
"2014" int,
"2015" int,
"2016" int,
"2017" int);L’ennui avec ces deux formes de requêtes, aussi bien celle qui a recours à la fonction crosstab() que celle construite sur autant d’expressions que de colonnes, c’est qu’il faut lister les colonnes et que dès qu’il y a une donnée en plus à pivoter, l’ajouter manuellement à la liste. Sinon avec la première forme la nouvelle donnée sera ignorée, et avec crosstab() elle provoquera une erreur.
D’autre part, ces requêtes ne sont pas malléables: si on veut présenter les colonnes dans l’ordre inverse, ou bien pivoter sur une autre colonne (ici en l’occurrence mettre des villes horizontalement au lieu des années), elles doivent être modifiées presque intégralement.
Enfin, certains pivots génèrent des centaines de colonnes, et la perspective de gérer ça à la main en SQL paraît absurde.
Souvent, en tant qu’utilisateur de SQL, on voudrait faire ce qu’on pourrait appeler un pivot dynamique, c’est-à-dire une requête polymorphe qui, sans modification du SQL, se trouverait automatiquement avoir toutes les colonnes du résultat à partir des lignes correspondantes. Ce serait utile d’une part si ces données sont susceptibles de changer, et d’autre part quand il y a beaucoup de colonnes et qu’il est trop fastidieux de les spécifier.
Mais il se trouve qu’une requête SQL ne peut pas avoir des colonnes dynamiques
au sens où il le faudrait pour un pivot dynamique.
On pourra objecter que dans un SELECT * FROM table, le * est bien
remplacé dynamiquement par liste des colonnes, mais la différence est
que cette opération se fait dans l’étape d’analyse de la requête, pas
dans la phase d’exécution. Avant l’exécution, le moteur SQL doit impérativement
savoir quels sont le nombre, les types et les noms des colonnes de la requête
et des sous-requêtes qui la composent. C’est d’ailleurs pour ça
que la sortie de crosstab(), au même titre que n’importe quelle fonction qui renvoie
un SETOF RECORD, doit être qualifiée statiquement par une liste de noms
de colonnes avec leurs types sous la forme AS (col1 type1, col2 type2...)
La difficulté du pivot dynamique peut être résumée ainsi: pour toute requête SQL, il faut que le type du résultat (noms et types de toutes les colonnes) soit connu avant la phase d’exécution. Or pour savoir quelles sont les colonnes composant le résultat pivoté, il faudrait exécuter la requête: c’est un cercle vicieux, et pour s’en sortir il faut forcément changer un peu les termes du problème.
Une première solution est que la requête SQL renvoie le résultat pivoté non pas en colonnes séparées, mais encapsulé dans une seule colonne avec un type multi-dimensionnel: array[text], JSON, ou XML. Cette solution est par exemple intégrée dans Oracle avec sa clause PIVOT XML. C’est en une seule étape, mais le résultat a une structure non-tabulaire qui ne correspond pas forcément à ce que les utilisateurs espèrent.
Voici un exemple en PostgreSQL moderne avec JSON:
SELECT ville,
json_object_agg(annee,total ORDER BY annee)
FROM (
SELECT ville, annee, SUM(pluvio) AS total
FROM pluviometrie
GROUP BY ville,annee
) s
GROUP BY ville
ORDER BY ville;Sans avoir à lister les années dans cette requête, on retrouve notre résultat précédent complet, mais sous forme de deux colonnes, une pour l’“axe vertical”, l’autre pour tout le reste au format JSON:
ville | json_object_agg
-----------+----------------------------------------------------------------------------------------
Ajaccio | { "2012" : 69, "2013" : 91, "2014" : 78, "2015" : 48, "2016" : 81, "2017" : 51 }
Bordeaux | { "2012" : 116, "2013" : 138, "2014" : 137, "2015" : 101, "2016" : 117, "2017" : 110 }
Brest | { "2012" : 178, "2013" : 161, "2014" : 180, "2015" : 160, "2016" : 165, "2017" : 144 }
Dijon | { "2012" : 114, "2013" : 124, "2014" : 116, "2015" : 93, "2016" : 116, "2017" : 103 }
Lille | { "2012" : 153, "2013" : 120, "2014" : 136, "2015" : 128, "2016" : 138, "2017" : 113 }
Lyon | { "2012" : 112, "2013" : 116, "2014" : 111, "2015" : 80, "2016" : 110, "2017" : 102 }
Marseille | { "2012" : 47, "2013" : 63, "2014" : 68, "2015" : 53, "2016" : 54, "2017" : 43 }
Metz | { "2012" : 98, "2013" : 120, "2014" : 110, "2015" : 93, "2016" : 122, "2017" : 115 }
Nantes | { "2012" : 124, "2013" : 132, "2014" : 142, "2015" : 111, "2016" : 106, "2017" : 110 }
Nice | { "2012" : 53, "2013" : 77, "2014" : 78, "2015" : 50, "2016" : 52, "2017" : 43 }
Paris | { "2012" : 114, "2013" : 111, "2014" : 113, "2015" : 85, "2016" : 120, "2017" : 110 }
Perpignan | { "2012" : 48, "2013" : 56, "2014" : 54, "2015" : 48, "2016" : 69, "2017" : 48 }
Toulouse | { "2012" : 86, "2013" : 116, "2014" : 111, "2015" : 83, "2016" : 102, "2017" : 89 }
(13 lignes)
C’est déjà pas mal, mais visuellement ça manque d’alignement, et surtout si le but est de copier-coller dans un tableur, on voit bien que ça ne va pas vraiment le faire.
Les autres solutions via requête SQL tournent autour de l’idée de procéder en deux temps:
une première requête construit le résultat avec toutes ses colonnes, et renvoie une référence indirecte à ce résultat.
une deuxième requête va ramener réellement le résultat, sa structure étant maintenant connue par le moteur SQL du fait de l’étape précédente.
A ce niveau, il faut rappeler qu’encapsuler ces deux étapes en une seule
fonction annulerait l’intérêt de la solution:
car pour appeler cette fonction en SQL, il faudrait obligatoirement
spécifier avec une clause AS(...) toutes les colonnes du résultat,
et dans ce cas autant utiliser crosstab().
La référence créée par la première étape peut être un curseur: dans ce cas la
requête SQL est un appel de fonction prenant le même genre d’arguments
que crosstab() mais renvoyant un REFCURSOR. La fonction créé
dynamiquement une requête pivot, et instancie un curseur sur son
résultat. Le code client peut alors parcourir ce résultat avec
FETCH. C’est la solution mise en oeuvre dans la fonction
dynamic_pivot() dont le code est un peu plus bas.
Autre variante: la requête SQL est un appel de fonction prenant le
même genre d’arguments que crosstab() mais créant une vue dynamique
ou une table, temporaire ou permanente, avec les données
pivotées. Dans un second temps, le code client exécute un SELECT sur
cette table ou vue, puis la supprime. Une implémentation en plpgsql
pourrait être assez similaire à celle renvoyant un curseur, sauf
qu’une fois établie la requête dynamique, on exécuterait
CREATE [TEMPORARY] TABLE (ou VIEW) nom AS ... suivi de la requête.
Dans le code ci-dessous, je vous propose une fonction renvoyant un REFCURSOR qui peut être utilisée telle quelle, mais qui pourrait aussi servir de base pour une variante.
Ses arguments sont les mêmes que crosstab():
La fonction instancie et renvoie un curseur contenant le résultat,
lequel doit être consommé dans la même transaction (quoiqu’on pourrait
le déclarer WITH HOLD si on voulait garder le résultat toute la session).
Malheureusement la requête principale doit être exécutée en interne deux fois
par cette implémentation, car elle est incorporée en sous-requête
dans deux requêtes totalement distinctes.
De plus, le type des colonnes en sortie est forcé à text, faute de
pouvoir accéder à l’information du type des données source en
plpgsql. Une version en langage C lèverait potentiellement ces
inconvénients, qui sont liés aux limitations du plpgsql (et encore,
sans l’existence de row_to_json, ajoutée en version 9.2, je ne crois
pas qu’il aurait été possible du tout de trouver les noms des colonnes comme
le fait la première étape de la fonction). Quoiqu’il en soit, une
version plpgsql a un avantage considérable ici: elle n’exige qu’une
quarantaine de lignes de code pour faire ce travail, que voici:
CREATE FUNCTION dynamic_pivot(central_query text, headers_query text)
RETURNS refcursor AS
$$
DECLARE
left_column text;
header_column text;
value_column text;
h_value text;
headers_clause text;
query text;
j json;
r record;
curs refcursor;
i int:=1;
BEGIN
-- détermine les noms des colonnes de la source
EXECUTE 'select row_to_json(_r.*) from (' || central_query || ') AS _r' into j;
FOR r in SELECT * FROM json_each_text(j)
LOOP
IF (i=1) THEN left_column := r.key;
ELSEIF (i=2) THEN header_column := r.key;
ELSEIF (i=3) THEN value_column := r.key;
END IF;
i := i+1;
END LOOP;
-- génère dynamiquement la requête de transposition (sur le modèle canonique)
FOR h_value in EXECUTE headers_query
LOOP
headers_clause := concat(headers_clause,
format(chr(10)||',min(case when %I=%L then %I::text end) as %I',
header_column,
h_value,
value_column,
h_value ));
END LOOP;
query := format('SELECT %I %s FROM (select *,row_number() over() as rn from (%s) AS _c) as _d GROUP BY %I order by min(rn)',
left_column,
headers_clause,
central_query,
left_column);
-- ouvre le curseur pour que l'appelant n'ait plus qu'à exécuter un FETCH
OPEN curs FOR execute query;
RETURN curs;
END
$$ LANGUAGE plpgsql;Exemple d’utilisation:
=> BEGIN;
-- étape 1: obtenir le curseur (le nom du curseur est généré par Postgres)
=> SELECT dynamic_pivot(
'SELECT ville,annee,SUM(pluvio)
FROM pluviometrie GROUP BY ville,annee
ORDER BY ville',
'SELECT DISTINCT annee FROM pluviometrie ORDER BY 1'
) AS curseur
\gset
-- étape 2: extraire les résultats du curseur
=> FETCH ALL FROM :"curseur";
ville | 2012 | 2013 | 2014 | 2015 | 2016 | 2017
-----------+------+------+------+------+------+------
Ajaccio | 69 | 91 | 78 | 48 | 81 | 51
Bordeaux | 116 | 138 | 137 | 101 | 117 | 110
Brest | 178 | 161 | 180 | 160 | 165 | 144
Dijon | 114 | 124 | 116 | 93 | 116 | 103
Lille | 153 | 120 | 136 | 128 | 138 | 113
Lyon | 112 | 116 | 111 | 80 | 110 | 102
Marseille | 47 | 63 | 68 | 53 | 54 | 43
Metz | 98 | 120 | 110 | 93 | 122 | 115
Nantes | 124 | 132 | 142 | 111 | 106 | 110
Nice | 53 | 77 | 78 | 50 | 52 | 43
Paris | 114 | 111 | 113 | 85 | 120 | 110
Perpignan | 48 | 56 | 54 | 48 | 69 | 48
Toulouse | 86 | 116 | 111 | 83 | 102 | 89
(13 lignes)
=> CLOSE :"curseur";
=> COMMIT; -- libérera automatiquement le curseur si pas déjà fait par CLOSE.
La couche de présentation côté client peut aussi se charger de transposer les lignes en colonnes, sur la base d’un jeu de résultat non pivoté. En effet certains voient la transposition comme une pure question de présentation, et pour l’essentiel c’est un point de vue qui se tient.
L’application psql propose une solution basée sur cette approche depuis la version 9.6, via la commande \crosstabview.
En usage interactif, cette méthode est la plus rapide pour obtenir des résultats immédiatement visibles.
Par exemple, admettons qu’on veuille examiner dans le cadre de notre exemple, les couples (ville,année) dépassant 120 jours de pluie:
=# SELECT ville, annee, SUM(pluvio)
FROM pluviometrie
GROUP BY ville,annee
HAVING SUM(pluvio)>120
ORDER BY annee
\crosstabview ville | 2012 | 2013 | 2014 | 2015 | 2016 | 2017
----------+------+------+------+------+------+------
Brest | 178 | 161 | 180 | 160 | 165 | 144
Nantes | 124 | 132 | 142 | | |
Lille | 153 | | 136 | 128 | 138 |
Dijon | | 124 | | | |
Bordeaux | | 138 | 137 | | |
Metz | | | | | 122 |
L’axe horizontal est alimenté par la 2eme colonne de la source mais
pour avoir la transposition inverse, il suffit de mettre les colonnes
annee ville en argument de crosstabview dans cet ordre, sans rien changer
à la requête:
=# \crosstabview annee ville
annee | Brest | Nantes | Lille | Dijon | Bordeaux | Metz
-------+-------+--------+-------+-------+----------+------
2012 | 178 | 124 | 153 | | |
2013 | 161 | 132 | | 124 | 138 |
2014 | 180 | 142 | 136 | | 137 |
2015 | 160 | | 128 | | |
2016 | 165 | | 138 | | | 122
2017 | 144 | | | | |
Ci-dessus on n’a pas de tri particulier des villes. Mais on peut trier ces colonnes, y compris sur un critère complexe, à travers le 4ème argument de la commande. Le code suivant trie les colonnes-villes par rang de pluviosité, en l’ajoutant comme 4ème colonne à la requête et à crosstabview:
=# SELECT ville, annee, SUM(pluvio),
rank() OVER (ORDER BY SUM(pluvio))
FROM pluviometrie
GROUP BY ville,annee
HAVING SUM(pluvio)>120
ORDER BY annee
\crosstabview annee ville sum rankRésultat:
annee | Metz | Dijon | Nantes | Bordeaux | Lille | Brest
-------+------+-------+--------+----------+-------+-------
2012 | | | 124 | | 153 | 178
2013 | | 124 | 132 | 138 | | 161
2014 | | | 142 | 137 | 136 | 180
2015 | | | | | 128 | 160
2016 | 122 | | | | 138 | 165
2017 | | | | | | 144
On voit que les nombres se répartissent maintenant de telle sorte qu’en lisant de gauche à droite on a des villes globalement de plus en plus pluvieuses, et notamment Brest le gagnant incontestable de ce jeu de données.
L’opération UNPIVOT existe dans certains dialectes SQL, mais pas dans PostgreSQL. On peut toutefois dé-pivoter facilement avec Postgres et de manière générique, c’est-à-dire sans liste explicite des colonnes, en passant par une représentation intermédiaire du jeu de données en JSON.
Imaginons par exemple que les données de pluviométrie se présentent comme ci-dessous, avec une colonne distincte par mois de l’année, type “tableur”
=> \d pluvmois
Column | Type |
--------+---------+
ville | text |
annee | integer |
m1 | integer |
m2 | integer |
m3 | integer |
m4 | integer |
m5 | integer |
m6 | integer |
m7 | integer |
m8 | integer |
m9 | integer |
m10 | integer |
m11 | integer |
m12 | integer |
En appliquant la fonction json_each_text à chaque ligne de la table
mise au format JSON avec row_to_json, on va obtenir toutes les colonnes
sous forme de tuples (key,value):
SELECT key, value FROM
(SELECT row_to_json(t.*) AS line FROM pluvmois t) AS r
JOIN LATERAL json_each_text(r.line) ON (true);Pour avoir notre résultat final dé-pivoté, il ne reste plus qu’à enrichir cette requête pour garder l’année et la ville associée à chaque mesure, et filtrer et re-typer les colonnes de mois, comme ci-dessous:
SELECT
r.ville,
r.annee,
substr(key,2)::int AS mois, -- transforme 'm4' en 4
value::int AS pluvio
FROM (SELECT ville, annee, row_to_json(t.*) AS line FROM pluvmois t) AS r
JOIN LATERAL json_each_text(r.line) ON (key ~ '^m[0-9]+');Résultat:
ville | annee | mois | pluvio
-----------+-------+------+--------
Lille | 2017 | 1 | 9
Lille | 2017 | 2 | 10
Lille | 2017 | 3 | 9
etc...
(936 lignes)
On retrouve bien nos 13 villes x 6 ans x 12 mois du jeu de données initial.
PostgreSQL 10 apportait enfin un support natif du partitionnement. Cependant, il souffrait de plusieurs limitations qui ne le rendaient pas si séduisant que cela. La version 11, qui va bientôt arriver en version bêta, corrige un certain nombre de ces défauts. Mais pas tous. Commençons par voir les différents changements. Ce premier article nous permet d'explorer l'élimination dynamique des partitions.
Commençons par nous créer un premier jeu d'essai :
CREATE TABLE orders (
num_order INTEGER NOT NULL,
date_order DATE NOT NULL,
) PARTITION BY RANGE (date_order);
CREATE TABLE orders_201804 PARTITION OF orders
FOR VALUES FROM ('2018-04-01') TO ('2018-05-01');
CREATE TABLE orders_201805 PARTITION OF orders
FOR VALUES FROM ('2018-05-01') TO ('2018-06-01');
Ajoutons quelques données :
INSERT INTO orders VALUES (1, '2018-04-22'), (2, '2018-05-01'), (3, '2018-05-11');
On termine la création du jeu d'essai en mettant à jour les statistiques pour l'optimiseur :
ANALYZE;
Vérifions déjà que l'optimiseur est toujours capable de retirer les partitions inutiles à la planification. Ici le prédicat est connu avant l'exécution, l'optimiseur peut donc lire uniquement la partition du mois de mai :
SELECT * FROM orders WHERE date_order = '2018-05-11';
On vérifie que la partition du mois d'avril est bien éliminée à la planification en observant que le plan obtenu ne contient pas aucune lecture de cette partition :
Append (actual rows=1 loops=1)
-> Seq Scan on orders_201805 (actual rows=1 loops=1)
Filter: (date_order = '2018-05-11'::date)
Rows Removed by Filter: 1
Une forme de construction souvent vue sur le terrain utilise la fonction
to_date pour calculer un prédicat sur une date :
SELECT *
FROM orders
WHERE date_order = to_date('2018-05-11', 'YYYY-MM-DD');
Avec l'élimination dynamique des partitions, on pourrait penser que cette requête en bénéficiera bien. Mais ce n'est pas le cas, le prédicat est utilisé comme clause de filtrage à la lecture et PostgreSQL 11 ne sait pas l'utiliser directement pour exclure dynamiquement la partition d'avril. La lecture de cette partition est bien réalisée et ne ramène aucune ligne :
Append (actual rows=1 loops=1)
-> Seq Scan on orders_201804 (actual rows=0 loops=1)
Filter: (date_order = to_date('2018-05-11'::text, 'YYYY-MM-DD'::text))
Rows Removed by Filter: 2
-> Seq Scan on orders_201805 (actual rows=1 loops=1)
Filter: (date_order = to_date('2018-05-11'::text, 'YYYY-MM-DD'::text))
Rows Removed by Filter: 1
On peut forcer la création d'un InitPlan, donc en utilisant une
sous-requête pour calculer le prédicat :
SELECT *
FROM orders
WHERE date_order = (SELECT to_date('2018-05-11', 'YYYY-MM-DD'));
Le plan d'exécution obtenu est un peu différent : l'InitPlan correspondant à notre sous-requête apparaît. Mais ce qui nous intéresse le plus concerne la lecture de la partition d'avril. Elle apparaît dans le plan car elle n'est pas exclue à la planification, mais elle n'est pas exécutée car exclue à l'exécution.
Append (actual rows=1 loops=1)
InitPlan 1 (returns $0)
-> Result (actual rows=1 loops=1)
-> Seq Scan on orders_201804 (never executed)
Filter: (date_order = $0)
-> Seq Scan on orders_201805 (actual rows=1 loops=1)
Filter: (date_order = $0)
Rows Removed by Filter: 1
Ce mécanisme est également fonctionnel si l'on avait utilisé une
sous-requête sans la fonction to_date :
SELECT * FROM orders WHERE date_order = (SELECT date '2018-05-11');
On peut aussi vérifier que le mécanisme fonctionne avec une requête préparée :
PREPARE q0 (date) AS SELECT * FROM orders WHERE date_order = $1;
On exécutera au moins 6 fois la requête suivante. Les 5 premières exécutions vont servir à PostgreSQL à se fixer sur un plan générique :
EXECUTE q0 ('2018-05-11');
Le plan d'exécution de la requête montre que seule la partition du mois de mai est accédée et nous montre aussi qu'un sous-plan a été éliminé, il s'agit de la lecture sur la partition d'avril :
Append (actual rows=1 loops=1)
Subplans Removed: 1
-> Seq Scan on orders_201805 (actual rows=1 loops=1)
Filter: (date_order = $1)
Rows Removed by Filter: 1
On pourra aussi s'en assurer avec la requête préparée suivante :
PREPARE q1 AS SELECT * FROM orders WHERE date_order IN ($1, $2, $3);
Et l'execute suivant, où l'on fera varier les paramètres pour tantôt viser les deux partitions, tantôt une seule :
EXECUTE q1 ('2018-05-11', '2018-05-11', '2018-05-10');
Le mécanisme d'élimination dynamique est donc fonctionnel pour les requêtes préparées.
Ajoutons une table pour pouvoir réaliser une jointure :
CREATE TABLE bills ( num_bill SERIAL NOT NULL PRIMARY KEY, num_order INTEGER NOT NULL, date_order DATE NOT NULL ); INSERT INTO bills (num_order, date_order) VALUES (1, '2018-04-22');
On met également à jour les statistiques pour l'optimiseur, surtout pour que l'optimiseur sache qu'une Nested Loop sera plus adaptée pour la jointure que l'on va faire :
ANALYZE bills;
La jointure suivante ne concernera donc que des données du mois d'avril, du
fait des données présentes dans la table bills :
SELECT *
FROM bills b
JOIN orders o
ON ( b.num_order = o.num_order
AND b.date_order = o.date_order);
La jointure est donc bien réalisée avec une Nested Loop. L'optimiseur n'a fait aucune élimination de partition, il n'a aucun élément pour y parvenir. En revanche, l'étage d'exécution a tout simplement éliminé la partition de mai de la jointure :
Nested Loop (actual rows=1 loops=1)
-> Seq Scan on bills b (actual rows=1 loops=1)
-> Append (actual rows=1 loops=1)
-> Seq Scan on orders_201804 o (actual rows=1 loops=1)
Filter: ((b.num_order = num_order) AND (b.date_order = date_order))
Rows Removed by Filter: 1
-> Seq Scan on orders_201805 o_1 (never executed)
Filter: ((b.num_order = num_order) AND (b.date_order = date_order))
Que se passe-t-il si l'on force l'optimiseur à ne pas utiliser une Nested Loop :
SET enable_nestloop = off;
explain (analyze, costs off, timing off)
SELECT *
FROM bills b
JOIN orders o
ON ( b.num_order = o.num_order
AND b.date_order = o.date_order);
Le plan d'exécution montre que l'optimiseur préfère une jointure par
hachage. Mais les deux partitions sont lues dans ce cas. Le mécanisme
d'élimination dynamique de partition de PostgreSQL 11 n'est effectif que sur
les Nested Loop (ça ne marchera pas non plus avec un Merge
Join) :
Hash Join (actual rows=1 loops=1)
Hash Cond: ((o.num_order = b.num_order) AND (o.date_order = b.date_order))
-> Append (actual rows=4 loops=1)
-> Seq Scan on orders_201804 o (actual rows=2 loops=1)
-> Seq Scan on orders_201805 o_1 (actual rows=2 loops=1)
-> Hash (actual rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on bills b (actual rows=1 loops=1)
Le mécanisme d'élimination dynamique de partitions est donc fonctionnel dans un certain nombre de cas, mais reste encore perfectible.
Tous les plans d'exécution de cet article ont été obtenus avec EXPLAIN
(analyze, costs off, timing off).
Dans ce post, je vais vous expliquer comment mettre en place une streaming replication avec PostgreSQL 10. Par contre, je n’expliquerais pas comment installer PostgreSQL donc je suppose que cela est déjà le cas.
Si vous êtes un utilisateur d’OpenBSD et de PostgreSQL, vous pouvez utiliser l’authentification BSD pour vous authentifier sur vos bases. Nous allons voir comment faire cela.
PostgreSQL 10 vient tout juste de sortir et apporte une nouveauté
significative concernant la supervision d'une instance. Grâce au travail de
Dave Page, PostgreSQL 10 amène un rôle système pg_monitor qui
permet d'accéder à toutes les fonctions de supervision sans nécessiter d'être
super-utilisateur.
La sonde check_pgactivity permet naturellement de tirer partie de cette nouvelle possibilité avec votre environnement de supervision Nagios ou compatible.
La sonde de supervision check_pgactivity arrive en version 2.3 beta 1 et intègre le support de PostgreSQL 10.
En plus de cela, le service backend_status accepte maintenant
des seuils exprimés avec des unités de temps, par exemple pour détecter les
transactions en attente d'un verrou depuis plus d'un certain temps. Un bug
assez ancien a par ailleurs été corrigé dans ce même service.
Le service sequences_exhausted a été corrigé de manière à
accepter les séquences rattachées à une colonne d'un type non-numérique.
Enfin, pour les personnes souhaitant contribuer au projet, une nouvelle documentation vous permettra de mettre le pied à l'étrier.
Dans toutes les versions précédentes, il fallait nécessairement être super-utilisateur pour pouvoir superviser une instance PostgreSQL.
Dave Page a répondu à cette problématique en proposant un rôle système
pg_monitor qui permet de bénéficier des droits pour accéder aux
vues pg_stat_* et utiliser les fonctions
pg_database_size() et pg_tablespace_size(). Le commit
25fff40798fc4ac11a241bfd9ab0c45c085e2212 vous permettra d'avoir de plus
amples explications.
Le second changement important concerne le renommage de XLOG en WAL. Ainsi,
le répertoire pg_xlog, qui contient les journaux de transaction,
est renommé en pg_wal. Toutes les fonctions contenant le terme
xlog sont renommées de la même façon, comme
pg_current_xlog_location() qui devient
pg_current_wal_lsn(). À noter que location devient
lsn, mais je ne détaille pas ce changement. Etc.
Enfin, un dernier changement important concerne l'arrivée de la fonction
pg_ls_waldir() pour pouvoir lister le contenu du répertoire
pg_wal. Pour ce faire, nous utilisions auparavant la fonction
pg_ls_dir().
Pour en savoir plus sur tous ces changements, je vous invite à consulter les notes de version de PostgreSQL 10.
Commençons par créer un rôle monitor qui ne dispose pas de
privilèges particuliers, mais qui est membre de
pg_monitor :
CREATE ROLE monitor LOGIN IN ROLE pg_monitor;
Pour permettre aux services btree_bloat et
table_bloat de fonctionner, il faut aussi permettre au rôle
monitor d'effectuer un SELECT sur
pg_statistic, dans toutes les bases de données de
l'instance :
GRANT SELECT ON pg_statistic TO monitor;
À noter que vous devrez configurer l'authentification par vous-même, en affectant un mot de passe ou non, à votre guise.
Le service wal_files se connecte à une instance en utilisant le
rôle monitor créé plus haut. Il permet de superviser le nombre de
journaux de transaction présents dans le répertoire
$PGDATA/pg_wal. Nous utilisons la sortie human pour
obtenir un résultat lisible par le commun des mortels :
$ ./check_pgactivity -h localhost -p 5432 -U monitor -F human -s wal_files Service : POSTGRES_WAL_FILES Returns : 0 (OK) Message : 45 WAL files Perfdata : total_wal=45 Perfdata : recycled_wal=44 Perfdata : tli=1 Perfdata : written_wal=1 Perfdata : kept_wal=0 Perfdata : wal_rate=74.45Bps
Si vous utilisez la sonde check_pgactivity avec Nagios, vous
choisirez évidemment le format de sortie nagios, qui est d'ailleurs le
format de sortie par défaut.
Comme vu plus haut, les services btree_bloat et
table_bloat doivent avoir accès au catalogue
pg_statistic. Sans cet accès, il n'est pas possible de calculer
une estimation de la fragmentation des index et des tables le plus rapidement
possible dans toutes les situations. Nous avions rencontré un gros problème de
performance sur une base disposant de plusieurs milliers de tables lorsque nous
utilisions encore la vue pg_stats.
Ainsi, nous devons donc donner le privilège de SELECT sur
pg_statistic à notre rôle monitor (ou à
pg_monitor si l'on souhaite être moins restrictif) :
GRANT SELECT ON pg_statistic TO monitor;
UPDATE Le service temp_files, qui permet de
superviser le volume de fichiers temporaires créés, nécessite les privilèges de
super-utilisateur car il utilise la fonction pg_ls_dir(). La sonde
va être corrigée ultérieurement pour qu'elle fonctionne avec PostgreSQL 10,
mais il ne sera plus possible de superviser les fichiers temporaires créés en
"live".
N'hésitez pas à nous remonter les problèmes que vous avez pu rencontrer avec
cette dernière version check_pgactivity. Si nous n'avons aucun
retour négatif, la version 2.3 stable va suivre d'ici quelques jours.
Rendez-vous sur github :
Depuis la version 9.5 de PostgreSQL, la sécurité au niveau des lignes (Row Level Security) a été introduite. Cette fonctionnalité permet de contrôler l'accès aux lignes d'une table de base de données en fonction des caractéristiques de l'utilisateur exécutant une requête.
Nous pouvons donc l'utiliser pour limiter, par exemple, le type de requêtes acceptées par rapport à l'adresse IP source du client.
La fonction interne PostgreSQL inet_client_addr() permet d'obtenir l'adresse IP du client de la connexion en cours.
Dans l'exemple ci-dessous, nous allons interdire toutes les requêtes de modification, d'insertion et de suppression sur la table "matable" lorsque l'adresse IP source de la connexion est '10.1.1.65'.
Par défaut lorsqu'un utilisateur ne rentre pas dans le critère d'une règle, celui si se voit refuser l'accès aux données.
Concernant la charge supplémentaire constatée, elle oscille entre 1% et 10% suivant le nombre de lignes rapporté par la transaction.
| LINE | TPS RLS ON | TPS RLS OFF | DIFF | % |
|---|---|---|---|---|
| 1 | 49600 | 56270 | 6670 | 88 |
| 100 | 42030 | 45616 | 3586 | 92 |
| 1000 | 24585 | 25136 | 551 | 97 |
| 10000 | 4513 | 4529 | 16 | 99 |
La première colonne et le nombre de lignes récupéré par transaction, la seconde le nombre de transactions par seconde constaté lorsque le RLS est à ON, la troisième lorsque RLS est à OFF. La colonne DIFF est la différence entre les 2 en nombre de transactions.
Ces valeurs ont été obtenues par l'utilisation de pgbench sur une table contenant 1 colonne de taille fixe avec les paramètres suivants :
pgbench -n -j 6 -c 20 -P 1 -T 30 -U rlsdemo -h pg96.local.ng.pe -f rls_bench.sql rlsdemo
Après avoir attendu tellement de temps pour cette fonctionnalité, HypoPG ajoute le support des index hypothétiques dans PostgreSQl, sous la forme d’une extension.
Cela fait maintenant quelques temps que la deuxième version de PoWA a été annoncée. Une des nouvelles fonctionnalités de cette version est l’extension pg_qualstats, écrite par Ronan Dunklau.
Grâce à cette extension, il est maintenant possible de collecter des statistiques en temps réel afin de détecter des index manquants, et bien plus encore (si cette extension vous intéresse, je vous conseille de lire l’article de Ronan sur pg_qualstats, en anglais). De plus, si vous l’utilisez avec PoWA, vous aurez une interface qui vous permettra de trouver les requêtes les plus coûteuses, et suggèrera des index manquants si c’est le cas.
Ce sont des fonctionnalités vraiment intéressantes, mais maintenant de nombreuses personnes posent cette question toute naturelle : Ok, PoWA me dit qu’il faut que je créé cet index, maix au final est-ce que PostgreSQL l’utilisera ?. C’est une très bonne question, car en fonction de nombreux paramètres de configuration (entre autres), PostgreSQL pourrait choisir de simplement ignorer votre index fraîchement créé. Et si vous avez du attendre plusieurs heures pour sa construction, ça serait une surprise plutôt déplaisante.
Bien evidemment, la réponse à cette question est le support des index hypothétiques. Il ne s’agit vraiment pas d’une nouvelle idée, de nombreux moteurs de bases de données les supportent déjà.
Il y a d’ailleurs déjà eu de précédents travaux sur le sujet il y a quelques années, dont les résultats ont été présentés au pgCon 2010. Ces travaux allaient beaucoup plus loin que le support des index hypothétiques, mais il s’agissait d’un travail de recherche, ce qui signifie que les fonctionnalités qui avaient été développées n’ont jamais vues le jour dans la version officielle de PostgreSQL. Tout cet excellent travail est malheureusement uniquement disponible sous la forme de fork de quelques versions spécifiques de PostgreSQL, la plus récentes étant la 9.0.1.
J’ai utilisé une approche différente pour implémenter les index hypothétiques avec HypoPG.
La seule restriction pour implémenter cette fonctionnalité sous la forme d’une extension est qu’il n’est pas possible de modifier la syntaxe sans modifier le code source de PostgreSQL. Donc tout doit être géré dans des procédures stockées, et le comportement des fonctionnalités existantes, comme la commande EXPLAIN, doit être modifié. On verra cela en détail juste après.
Pour le moment, les fonctions suivantes sont disponibles :
Si des index hypothétiques existent pour des tables utilisées dans une commande EXPLAIN (sans ANALYZE), ils seront automatiquement ajoutés à la liste des vrais index. PostgreSQL choisira alors s’il les utilise ou non.
Installer HypoPG est plutôt simple. En partant du principe que vous avez téléchargé et extrait une archive tar dans le répertoire hypopg-0.0.1, que vous utilisez une version packagée de PostgreSQL et que vous disposez des paquets -dev :
$ cd hypopg-0.0.1
$ make
$ sudo make installHypoPG devrait alors être disponible :
rjuju=# CREATE EXTENSION hypopg ;
CREATE EXTENSIONVoyons quelques tests simplistes. D’abord, créons une petite table :
rjuju=# CREATE TABLE testable AS SELECT id, 'line ' || id val
rjuju=# FROM generate_series(1,1000000) id;
SELECT 100000
rjuju=# ANALYZE testable ;
ANALYZEEnsuite, voyons un plan d’exécution qui pourrait bénéficier d’un index qui n’est pas présent :
rjuju=# EXPLAIN SELECT * FROM testable WHERE id < 1000 ;
QUERY PLAN
---------------------------------------------------------------
Seq Scan on testable (cost=0.00..17906.00 rows=916 width=15)
Filter: (id < 1000)
(2 rows)Sans surprise, un parcours séquentiel est le seul moyen de répondre à cette requête. Maintenant, essayons d’ajouter un index hypothétique, et refaisons un EXPLAIN :
rjuju=# SELECT hypopg_create_index('CREATE INDEX ON testable (id)');
hypopg_create_index
---------------------
t
(1 row)
Time: 0,753 ms
rjuju=# EXPLAIN SELECT * FROM testable WHERE id < 1000 ;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Index Scan using <41079>btree_testable_id on testable (cost=0.30..28.33 rows=916 width=15)
Index Cond: (id < 1000)
(2 rows)Oui ! Notre index hypothétique est utilisé. On remarque aussi que le temps de création de l’index hypothétique est d’environ 1ms, ce qui est bien loin du temps qu’aurait pris la création de cet index.
Et bien entendu, cet index hypothétique n’est pas utilisé dans un EXPLAIN ANALYZE :
rjuju=# EXPLAIN ANALYZE SELECT * FROM testable WHERE id < 1000 ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on testable (cost=0.00..17906.00 rows=916 width=15) (actual time=0.076..234.218 rows=999 loops=1)
Filter: (id < 1000)
Rows Removed by Filter: 999001
Planning time: 0.083 ms
Execution time: 234.377 ms
(5 rows)Maintenant essayons d’aller un peu plus loin :
rjuju=# EXPLAIN SELECT * FROM testable
rjuju=# WHERE id < 1000 AND val LIKE 'line 100000%';
QUERY PLAN
---------------------------------------------------------------------------------------------
Index Scan using <41079>btree_testable_id on testable (cost=0.30..30.62 rows=1 width=15)
Index Cond: (id < 1000)
Filter: (val ~~ 'line 100000%'::text)
(3 rows)Notre index hypothétique est toujours utilisé, mais un index sur id et val devrait aider cette requête. De plus, comme il y a un joker sur le côté droit du motif de recherche du LIKE, la classe d’opérateur text_pattern_ops est requise. Vérifions ça :
rjuju=# SELECT hypopg_create_index('CREATE INDEX ON testable (id, val text_pattern_ops)');
hypopg_create_index
---------------------
t
(1 row)
Time: 1,194 ms
rjuju=# EXPLAIN SELECT * FROM testable
rjuju=# WHERE id < 1000 AND val LIKE 'line 100000%';
QUERY PLAN
------------------------------------------------------------------------------------------------------
Index Only Scan using <41080>btree_testable_id_val on testable on testable (cost=0.30..26.76 rows=1 width=15)
Index Cond: ((id < 1000) AND (val ~>=~ 'line 100000'::text) AND (val ~<~ 'line 100001'::text))
Filter: (val ~~ 'line 100000%'::text)
(3 rows)Et oui, PostgreSQL décide d’utiliser notre nouvel index !
Il y a pour le moment une estimation rapide de la taille d’index, qui peut nous donner un indice sur la taille que ferait un vrai index.
Vérifions la taille estimée de nos deux index hypothétiques :
rjuju=# SELECT indexname,pg_size_pretty(hypopg_relation_size(indexrelid))
rjuju=# FROM hypopg();
indexname | pg_size_pretty
-------------------------------+----------------
<41080>btree_testable_id | 25 MB
<41079>btree_testable_id_val | 49 MB
(2 rows)Maintenant, créons les vrais index, et comparons l’espace occupé :
rjuju=# CREATE INDEX ON testable (id);
CREATE INDEX
Time: 1756,001 ms
rjuju=# CREATE INDEX ON testable (id, val text_pattern_ops);
CREATE INDEX
Time: 2179,185 ms
rjuju=# SELECT relname,pg_size_pretty(pg_relation_size(oid))
rjuju=# FROM pg_class WHERE relkind = 'i' AND relname LIKE '%testable%';
relname | pg_size_pretty
---------------------+----------------
testable_id_idx | 21 MB
testable_id_val_idx | 30 MBLa taille estimée est un peu plus haute que la taille réelle. C’est volontaire. En effet, si la taille estimée était moindre que celle d’un index existant, PostgreSQL préférerait utiliser l’index hypothétique plutôt que le vrai index, ce qui n’est absolument pas intéressant. De plus, pour simuler un index fragmenté (ce qui est vraiment très fréquent sur de vrais index), un taux de fragmentation fixe de 20% est ajoutée. Cependant, cette estimation pourrait être largement améliorée.
Cette version 0.0.1 d’HypoPG est un travail en cours, et il reste encore beaucoup de travail à accomplir.
Voilà les principales limitations (du moins qui me viennent à l’esprit) :
Cependant, cette version peut déjà être utile dans de nombreux contextes.
Maintenant, la prochaine étape est d’implémenter le support d’HypoPG dans PoWA, pour aider les DBA à décider s’ils devraient ou non créer les index suggérés, et supprimer les limitations actuelles.
Si vous voulez essayer HypoPG, le dépôt est disponible ici : github.com/HypoPG/hypopg.
À très bientôt pour la suite !
Parlons des index hypothétiques was originally published by Julien Rouhaud at rjuju's home on July 02, 2015.
Le projet PostgreSQL possède depuis peu son propre dépôt APT pour les différentes versions des serveurs encore maintenu et PgAdmin3, sur les versions de Debian et Ubuntu suivantes
Si vous utilisiez déjà le dépôt squeeze backports par exemple, vous pouvez basculer facilement vers ce nouveau dépôt sans problème
Avant d'installer une version de PostgreSQL, il faut ajouter la clé d'authentification des paquets dans notre trousseau de clé.
wget -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
Il faut ensuite ajouter le dépôt au source.list, pour cela créer le fichier /etc/apt/sources.list.d/pgdg.list et ajouter les lignes suivantes (l'exemple ci-dessous est pour la version squeeze).
deb http://apt.postgresql.org/pub/repos/apt/ squeeze-pgdg main
Remplacer Squeeze par le nom de votre distribution.
Pour indiquer à votre distribution de prendre et mettre à jour votre ou vos PostgreSQL à partir de cette source, il faut rajouter une configuration dite de pinning
Créer le fichier /etc/apt/preferences.d/pgdg.pref et ajouter les lignes suivantes
Package: *
Pin: release o=apt.postgresql.org
Pin-Priority: 500
Une fois la configuration, il faut faire un update pour mettre a jour votre gestionnaire de paquet avec ce nouveau dépôt, et charger le trousseau de clé
apt-get update
apt-get install pgdg-keyring
Ensuite il en reste plus qu'a installer la version de PostgreSQL que vous souhaitez.
apt-get install postgresql-9.2 postgresql-client-9.2 postgresql-contrib-9.2 postgresql-plpython-9.2 postgresql-server-dev-9.2 libpq-dev
Mon activité professionnelle chez Bull m'amène à travailler très régulièrement avec la CNAF (Caisse Nationale d'Allocations Familiales).
En 2008-2009, j'ai eu la joie de participer activement à la mise en place de PostgreSQL pour une application éminemment critique, celle qui supporte le cœur du métier des CAF : le calcul et le paiement des prestations sociales.
Cette semaine, j'ai pu compiler diverses statistiques techniques. Quelques règles de 3 plus tard, j'en suis arrivé à la conclusion que nous venions tout juste de franchir la barre symbolique des :
1 500 milliards de requêtes SQL exécutées !
et ceci sans que le SGBD ne soit jamais pris en défaut.
Quelqu'un doutait-il de la robustesse de PostgreSQL ?
L'AFUP organise une soirée intitulée les bases de données relationnelles avec PHP
. Je viendrai
présenter PostgreSQL, son développement et sa communauté, ses principales
fonctionnalités et quelques retours d'expérience.
Si vous ne connaissez pas PostgreSQL, c'est le moment de venir le découvrir à cette occasion, près de la Défense, à Paris.
Ce billet est le premier d'une série consacrée au projet open source E-Maj.
En deux mots, de quoi s'agit-il ?
E-Maj est une extension au SGBD PostgreSQL. Comme lui, il est disponible sous licence open source, en l’occurrence pour E-Maj la licence GPL.
Il permet d'enregistrer les mises à jour apportées à des tables relationnelles dans l'intention soit de les consulter soit de les annuler. La consultation permet par exemple d'analyser le comportement d'une application ou simplement d'avoir une trace des changements de contenu de tables. Quant à l'annulation des mises à jour, elle peut permettre de repositionner des tables dans un état prédéfini, en annulant l'effet d'un ou plusieurs traitements sur leur contenu.
Et je vous entends déjà dire. « Bon d'accord, il s'agit encore d'un outil de log de mises à jour. Mais il en existe déjà plusieurs dans le monde PostgreSQL. Alors pourquoi en inventer un autre ? ».
Effectivement, des contribs existent, tel que l'excellent table_log d'Andreas Scherbaum. Et le wiki de la communauté montre en détail comment se faire ses propres fonctions (wiki.postgresql.org/wiki/Audit_trig... et //wiki.postgresql.org/wiki/Audit_tr...)
Mais E-Maj présente deux grandes caractéristiques qui le rendent unique (du moins à ma connaissance !) : la manipulation d'ensembles de tables et la présence d'une interface graphique. Alors détaillons un peu ces deux aspects.
L'un des concepts sur lesquels E-Maj a été bâti est le « groupe de tables » (ou Tables Group en anglais). Il s'agit de mettre dans un même paquet, le « groupe de tables », toutes les tables qui vivent au même rythme, c'est à dire dont il faudra nécessairement, le cas échéant, annuler les mises à jour de manière cohérente. Par exemple, si j'ai une table des commandes et une tables des lignes de commande, il serait absurde de pouvoir annuler un ensemble de mises à jour sur l'une des tables sans annuler également les mises à jour de l'autre table. D'ailleurs la présence probable d'une clé étrangère (Foreign Key) entre ces deux tables nous rappellerait rapidement à l'ordre ! Avec E-Maj, le seul objet manipulable par l'utilisateur est le groupe de tables, et annuler les mises à jour d'une seule table est impossible (à moins bien sûr d'avoir un groupe de tables ne comprenant qu'une unique table).
E-Maj comprend en fait deux grands composants :
Mais au fait, d'où vient ce nom étrange d'E-Maj ?
Il s'agit tout simplement de l'acronyme, en français, de « Enregistrement des Mises A Jour ». Oui, je sais, ce n'est pas très original. Mais il fallait trouver un nom. Et puis, prononcé à l'anglaise, cela ressemble au mot « image », E-Maj permettant en effet de reconstruire des sortes d'images de bases de données.
Nous aurons l'occasion de présenter plus en détail cette extension dans les prochains billets.
Une dernière information avant de vous quitter pour répondre aux impatients. Comment se procurer E-Maj ?
E-Maj est disponible sur pgfoundry, et sur le site des extensions PostgreSQL PGXN.org. Et toute la documentation, en français et en anglais, est bien sûr contenu dans le support.
Deux dépôts sur github sont aussi accessibles :
Le plugin phpPgAdmin n'est pas encore disponible en libre service (je vous dirai bientôt pourquoi). Mais il suffit de me le demander par email (phb point emaj at free point fr).
A suivre...
Pour connaitre la taille d'un base de données il faut utiliser la fonction pg_database_size
production=# select pg_database_size('production');
pg_database_size
------------------
513343780
(1 ligne)
Cette taille est donnée en octets, pour avoir une meilleur représentation en Méga ou Giga, il faut utiliser la fonction pg_size_pretty
production=# select pg_size_pretty(pg_database_size('production'));
pg_size_pretty
----------------
490 MB
(1 ligne)
Ensuite si l'on souhaite connaître la taille d'un table il faut utiliser la fonction pg_relation_size.
production=# select pg_size_pretty(pg_relation_size('res_partner'));
pg_size_pretty
----------------
152 kB
(1 ligne)
Si l'on souhaite également avoir la place prise par les indexes, il faut utiliser la fonction pg_total_relation_size
production=# select pg_size_pretty(pg_total_relation_size('res_partner'));
pg_size_pretty
----------------
528 kB
(1 ligne)
Lingora, société spécialisée dans le support et la maintenance de solutions libres et la société américaine EnterpriseDB, éditrice d'une version propriétaire de PostgreSQL apportant une compatibilité avec Oracle, se sont réunies dans le cadre d'un partenariat pour proposer des licences de produits et surtout du support autour de PostgreSQL et l'offre propriétaire d'EntrepriseDB.
Après une brève introduction, Alexandre Zapolsky, PDG de Linagora, a passé la main à Andy Astor, CEO d'EnterpriseDB pour présenter la suite logicielle Postgres Plus et Postgres Plus Advanced Server.
Le SGBD PostgreSQL, ici en version 8.3, reste bien évidemment à la base de la pile Postgres Plus. Celle-ci se propose en premier lieu d'intégrer des outils et des extensions libres ou préalablement libérées par EnterpriseDB, évidemment sous la forme de paquets binaires ; ainsi, l'appropriation de PostgreSQL par un néophyte sera largement facilitée.
Parmi les fonctionnalités ou produits proposés pré-packagés, on trouvera :
Auparavant, la société d'Andy Astor proposait - et d'ailleurs propose toujours - EnterpriseDB Advanced Server qui était une évolution propriétaire de PostgreSQL, connue pour sa compatibilité avec Oracle, ce produit trouve sa continuation dans Postgres Plus Advanced Server. Bien évidemment, la différence ne s'arrête pas là, l'édition Advanced Server se trouve complétée par des fonctionnalités d'audit et de tuning avancées (DynaTune, Hints pour l'optimiseur), des outils de migration depuis d'autres bases de données, un outil de réplication propre à EDB. Enfin, et voilà qui devrait rassurer les décideurs, EnterpriseDB offre des garanties à l'instar de n'importe quel autre éditeur de bases de données, à condition évidemment que l'on se soit acquitté de la licence pour le produit.
Je souhaite grandement remercier Linagora pour m'avoir permis de rencontrer et de discuter longuement avec Andy et Jim ! Je remercie enfin Andy et Jim pour avoir eu la gentillesse de subir mon mauvais anglais sans broncher et pour les intéressantes discussions que nous avons eu.
La version majeure 8.3 de PostgreSQL est enfin sortie ! Le développement a duré bien plus longtemps que prévu, mais ça valait la peine d'attendre !
Bien que PostgreSQL soit déjà, à mon goût, un SGBD mature, l'équipe de développement a fait un pas de géant avec cette version. J'avais surtout retenu de la version précédente, la 8.2, une simplification de la configuration, et par là, du tuning, du moteur, bien sûr ce n'était pas tout, mais c'était l'atout majeur de cette version à mes yeux.
Maintenant, de part mes nouvelles fonctions (ah oui, je n'en ai pas encore parlé), cette nouvelle version me permettra de mettre PostgreSQL en avant grâce aux fonctionnalités XML, bien que ce soit ma bête noire, et la recherche plein-texte intégrée. Les Enums me semblent être un bon apport, mais j'ai peur de voir des Enums à toutes les sauces, notamment pour représenter un état à Yes ou No, comme cela est fait abondamment sur MySQL, alors qu'un type booléen suffit (et PHP supporte traite correctement les booléens avec PG ?).
En terme d'exploitation, mon ancien métier, je salue l'activation par défaut d'autovacuum. Bien sûr, l'améliorations de performances est également à mettre en avant, mais il serait intéressant de monter un petit bench.
Pour plus d'informations, lisez l'annonce sur PostgreSQLfr.
J'aimerai également saluer Guillaume qui est pour beaucoup dans la traduction du manuel de PostgreSQL 8.3, qui est d'ailleurs déjà disponible ! Merci Guillaume !
C'est tout bête, j'ai simplement suivi les indications données sur le site de pgAdmin III pour installer une version à jour sur ma Gutsy, c'est à dire la version 1.8 activement supportée par Guillaume. <pub>Guillaume, grâce à qui j'ai une envie terrible de me jeter sur la Ronde de nuit de Terry Pratchett.</pub>
Ensuite, j'ai installé PostgreSQL 8.3 beta 4 en prenant soin d'activer les fonctionnalités XML :
./configure --prefix=/home/tom/pgsql/v8.3.0/ --enable-nls --enable-thread-safety --with-libxml make -j3 make check
Pour être gratifié d'un charmant message indiquant que tout s'est bien passé :
======================= All 114 tests passed. =======================
Et finir gaiement sur un :
make install
J'installe également les contributions, car il y a quelques petites choses intéressantes que je voudrais voir... En tout cas, la compilation raaaââââââaaaaame sur mon petit P3 de rien du tout !
Petite note à l'attention des testeurs sur distribution Red Hat Enterprise Linux: la libxml2 fournie jusqu'à la RHEL 4 n'est pas suffisante pour compiler un PostgreSQL 8.3 avec les fonctionnalités XML. En revanche, ça passe sous soucis, même sur RHEL 3 sans ces dites fonctionnalités.
Et sinon, il faut que ce soit dit, ma chérie a sauvé mon apéro en ouvrant la bouteille d'Amer Bière dont le bouchon était coincé par le sucre cristallisé sur le goulot.. Et figurez-vous, chers lecteurs, qu'elle n'a pas utilisé ses petits biscottos, mais sa puissance intellectuelle pour y arriver ! Un petit chiffon imbibé d'eau chaude a remédier à ça ! Alors je dis bravo ! Aaah, les femmes...
Le site PostgreSQLfr propose un excellent article de Greg Smith intitulé
Pourquoi préférer PostgreSQL à MySQL
.
J'en ai appris un peu plus sur les points négatifs que je trouvais à MySQL, et surtout l'existence du mode strict. Cependant, je ne suis toujours pas convaincu par MySQL, essentiellement parce qu'une telle base est difficilement exploitable. Ah oui, par exploitable, j'entends avoir une centaine de bases de données qui se laissent oublier, et j'aurai trop peur de laisser des MySQL dans la nature sans surveillance !
En attendant, je retourne sur mes problèmes PHP/Oracle, grmbl !
La page de status des patchs en attente pour la 8.3 indique que l'ensemble des
patchs attendus sont tous soit appliqués, soit en attente pour la 8.4, ou
encore rejetés.
Au menu de cette prochaine version, quelques nouveautés intéressantes :
core, c'est à dire que l'extension de recherche de textes devrait être simple à mettre en oeuvre, sans patchs de partout ;
Du boulot en perspective pour assimiler les nouveautés, d'autant plus que je
suis resté sur la 7.4 pour certains aspects du moteur (merci Guillaume
pour m'avoir mis à jour
).
MAJ: et petite surprise que j'aime, possibilité de placer
les WAL où on le souhaite, plus besoin de bidouiller avec des mv
et des ln. L'option d'initdb qui va bien est -X (ou
--xlogdir=), la seule contrainte étant que le répertoire
accueillant les WALs soit vide.
MAJ: une variable de configuration
temp_tablespaces a également fait son apparition. Les logiciels de
réplication devraient également mieux s'intégrer au backend.
Depuis la version 8i, Oracle implémente les index inversés. Voici une proposition d’implémentation équivalente pour PostgreSQL. Les index inversés permettent d’accélérer les recherches sur les motifs tels que « colonne LIKE '%chaîne' ». Dans un tel cas, PostgreSQL effectue un parcours séquentiel (ou « sequential scan ») de la table interrogée. Toutefois, il est possible d’émuler un index inverse au moyen d’une fonction de renversement de chaîne couplée à un index sur fonction.
L'article
précédent proposait l'implémentation d'un prototype en langage procédural
PL/pgSQL, qui fait office ici de prototype.
Cette implémentation a pour principal défaut d'être lente, pénalisant ainsi
gravement les performances en écriture (INSERT et UPDATE). Ainsi, à chaque mise
à jour, il est nécessaire de faire appel à la fonction reverse
pour mettre à jour l'index fonctionnel ; cela s'observe notamment à la
création de l'index.
En revanche, il est possible de tirer partie des capacités de traitement des
caractères multi-octets, que l'on rencontre notamment dans le cas d'une base de
données encodée en UTF-8.
Ainsi, l'implémentation en langage C se doit d'être à la fois plus rapide et
surtout se doit de supporter les jeux de caractères multi-octets. C'est à
partir de ce minuscule cahier des charges que nous allons construire notre
fonction reverse.
Pourquoi s'embêter à prendre le temps d'écrire une procédure stockée en
langage C alors qu'il est possible de faire la même chose en langage PL/pgSQL
?
Il y a plusieurs réponses à cette question :
fonction Cest obligatoire.
Le gain significatif de vitesse ne sera pas évident pour les requêtes de
sélection. En revanche, les écritures (surtout INSERT et UPDATE) peuvent être
fortement pénalisées par le coût de la mise à jour d'un index
fonctionnel.
Bien que cela ne soit pas évident pour une opération unitaire, il sera
parfaitement visible dans le cas d'une opération d'écriture en masse
(chargement massif de données), ou tout simplement pour la création de l'index
fonctionnel.
Dans un tel cas, l'option d'une réécriture en langage C est à envisager très
sérieusement.
Les possibilités d'extension de PostgreSQL s’appuient sur les mécanismes de chargement dynamique de bibliothèque du système d’exploitation. L’interface de programmation est relativement simple, à condition d’en connaître certaines clés.
Le projet est articulé autour de différents fichiers, qui seront tous placés dans un répertoire dédié :
Makefile simplifié, utilisant PGXS,
l'infrastructure de construction d'extension PostgreSQL ;reverse.sql.in ;uninstall_reverse.sql ;reverse.c.Avant toute chose, il faut disposer d’un fichier « Makefile » de
construction du module externe :
MODULES = reverse #PG_CPPFLAGS = -ggdb DATA_built = reverse.sql DATA = uninstall_reverse.sql PGXS := $(shell pg_config --pgxs) include $(PGXS)
Le Makefile utilise ici l’outil PGXS qui propose un fichier Makefile prédéfini,
à l’instar des fichiers Makefile fournis par Oracle.
Le fichier « reverse.sql.in » qui sert de modèle à la création du
fichier d'installation de l'extension « reverse.sql ». Ce
dernier fichier sera généré à partir du modèle en remplaçant
« MODULE_PATHNAME » par le chemin complet du fichier
objet généré.
-- Déclaration de la fonction reverse en tant que module C
SET search_path = public;
CREATE OR REPLACE FUNCTION reverse(varchar) RETURNS varchar
AS 'MODULE_PATHNAME', 'reverse'
LANGUAGE 'C' IMMUTABLE STRICT;
Le script « reverse.sql » sera exécuté par un utilisateur PostgreSQL
ayant le rôle d’administrateur, les fonctions C étant considérées comme
non-sûres et donc de la responsabilité de l’administrateur.
Un script de désinstallation « uninstall_reverse.sql » est
également prévu, ça fait toujours plaisir :
SET search_path = public; DROP FUNCTION reverse(varchar);
La lecture de la page « Fonctions en langage C » permet d’obtenir
les informations nécessaires au développement d’une fonction C, voir la
documentation « Fonctions en langage C ». Cependant la lecture des fichiers
d’en-têtes permet d’apporter un éclairage supplémentaire sur certaines
structures de données.
Sous PostgreSQL, les chaînes de caractères ne sont pas délimitées par un
caractère nul « \0 » terminal, mais, à l’instar du
langage Pascal, en stockant dans une structure d’abord sa longueur puis son
contenu. Une telle chaîne est décrite dans une structure de type
« varlena ». Ce type de données offre en fait un moyen
uniforme de stocker tout type de données à longueur variable, comme les chaînes
de caractères, les tableaux ou encore les types utilisateurs.
Voici sa définition, obtenu dans le fichier d'en-tête c.h, à la ligne 409 :
struct varlena
{
int32 vl_len_; /* Do not touch this field directly! */
char vl_dat[1];
};
Ainsi, l'entier vl_len contient la longueur, en octets, de la
chaîne d'octets vl_dat.
Quelques macros permettent de manipuler facilement cette structure.
VARDATA(varlena) obtient un pointeur sur la
donnée ;VARSIZE(varlena) obtient la taille en octets de la
structure varlena (vl_len + vl_dat) ;VARHDRSZ représente la taille en octet de
vl_len ;VARATT_SIZEP, remplacée par SET_VARSIZE à
partir de la 8.3, permet de définir la longueur en octets de la donnée.Ainsi, pour obtenir la longueur en octets de la données, on utilisera
(VARSIZE - VARHDRSZ).
L'implémentation proposée supporte les jeux de caractères multi-octets,
comme l'UTF8 (ou Unicode) et les jeux de caractères asiatiques, qui représente
certains caractères sous la forme d'une séquence de deux octets ou plus (voir
référence).
PostgreSQL met à disposition des fonctions utiles pour manipuler les chaînes de
caractères, peu importe l'encodage, notamment pg_verifymbstr qui
valide une chaîne de caractère selon l'encodage de la base de données, ou
encore pg_mblen qui donne la longueur en octets d'un caractère.
Pour le prototype des fonctions citées et d'autres fonctions, se référer au
fichier d'en-tête « mb/pg_wchar.h ».
Il existe deux conventions d'appel de fonctions externes :
La convention d'appel version 1 sera utilisée dans le but de donner d'entrée de jeu de bonnes habitudes. La complexité de cette convention est masquée par une batterie de macros qui rendent son utilisation tout aussi simple, voire encore plus simple que la version 0, notamment pour le passage d'arguments.
Le source C est structuré en quatre parties :
Voici ci-après, le code source en langage C de la fonction
reverse.
/*
* reverse procedural function
*
* Thomas Reiss, 12/07/2007 – 24/07/2007 - 02/08/2007
* Alain Delorme, 24/07/2007
* Merci à depesz pour ses tests sur la version 8.3devel
*
*/
#include "pg_config.h"
#include "postgres.h"
#include "fmgr.h"
#include "mb/pg_wchar.h"
#include "utils/elog.h"
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
Datum
reverse(PG_FUNCTION_ARGS);
/* SET_VARSIZE correspond à la nouvelle API, nous définissons cette
macro pour les versions ne la possédant pas. */
#ifndef SET_VARSIZE
#define SET_VARSIZE(n,s) VARATT_SIZEP(n) = s;
#endif
/* fonction reverse */
PG_FUNCTION_INFO_V1(reverse);
Datum
reverse(PG_FUNCTION_ARGS)
{
int len, pos = 0;
VarChar *str_out, *str_in;
/* Obtient l'adresse de l'argument */
str_in = PG_GETARG_VARCHAR_P_COPY(0);
/* Calcul de la taille en octet de la chaîne */
len = (int) (VARSIZE(str_in) - VARHDRSZ);
/* Créer une chaîne vide de taille identique */
str_out = (VarChar *)palloc(VARSIZE(str_in));
/* La structure résultante aura une longueur identique */
SET_VARSIZE(str_out, VARSIZE(str_in));
/* Vérifie que l'encodage de la chaîne en argument
* concorde avec l'encodage de la BDD */
pg_verifymbstr(VARDATA(str_in), len, false);
/* Copie à l'envers de la chaîne */
while (pos < len)
{
int charlen = pg_mblen(VARDATA(str_in) + pos);
int i = charlen;
/* Copie un caractère.
* !! Un caractère != un octet */
while (i--)
*(VARDATA(str_out) + len - charlen + i - pos) = *(VARDATA(str_in) + i + pos);
pos = pos + charlen; /* incrémente le compteur */
}
PG_FREE_IF_COPY(str_in, 0);
/* Retourne la copie */
PG_RETURN_VARCHAR_P(str_out);
}
La construction de l'extension PostgreSQL est réalisée en invoquant
make
tom@clementina:~/src/reverse$ make cc -g -Wall -O2 -fPIC -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -g -fpic -I. -I/usr/include/postgresql/8.2/server -I/usr/include/postgresql/internal -D_GNU_SOURCE -I/usr/include/tcl8.4 -c -o reverse.o reverse.c cc -shared -o reverse.so reverse.o rm reverse.o
Si tout s'est bien passé, l'installation sera finalisée en exécutant la
commande make install, éventuellement précédé de sudo
en fonction de sa distribution et de son installation de PostgreSQL.
tom@clementina:~/src/reverse$ sudo make install Password: xxxx /bin/sh /usr/lib/postgresql/8.2/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./reverse.sql '/usr/share/postgresql/8.2/contrib' /bin/sh /usr/lib/postgresql/8.2/lib/pgxs/src/makefiles/../../config/install-sh -c -m 755 reverse.so '/usr/lib/postgresql/8.2/lib'
Les fichiers produits seront ainsi installés dans le répertoire d'installation
de PostgreSQL.
Il est toutefois possible de les positionner ailleurs, à condition d'adapter le
fichier « reverse.sql » de façon à indiquer à PostgreSQL
où se trouve la bibliothèque partagée (fichier
« reverse.so » sous Linux).
Dans un premier temps, on crée la fonction via l'outil
psql :
test=# \i reverse.sql CREATE FUNCTION
On vérifie que la fonction répond correctement :
test=# SHOW client_encoding;
client_encoding
-----------------
UTF8
(1 ligne)
test=# SELECT reverse('Chaîne à renverser');
reverse
--------------------
resrevner à enîahC
(1 ligne)
Ok, ça marche, y compris avec les chaînes encodées en UTF-8 !
Ce test a été réalisé par depesz, qui m'a aimablement autorisé a le réutiliser dans le cadre de cet article.
Petit aperçu du jeu de test :
test=# SELECT count(*), test-# min(length(filepath)), test-# max(length(filepath)), test-# sum(length(filepath)) test-# FROM test; count | min | max | sum -------+-----+-----+---------- 320136 | 7 | 174 | 18563865 (1 row)
Maintenant, voici une petite comparaison des 3 implémentations, à savoir le
prototype en PL/pgSQL, la version PL/perl de depesz et la version C. On oppose
à ces trois tests un parcours de la table via la fonction d'agrégat count(), permettant ainsi de mesurer l'overhead due à chaque
implémentation de la fonction reverse.
A chaque fois, 3 exécutions permettent de vérifier les résultats.
Voici l'ordre SQL utilisé pour réaliser ce test :
test=# EXPLAIN ANALYZE test-# SELECT count(filepath) test-# FROM test;
Et voici les temps de réponse obtenus :
Exécution #1 : 1269.535 ms
Exécution #2 : 1268.421 ms
Exécution #3 : 1257.926 ms
Moyenne : 1265,29 ms
test=# EXPLAIN ANALYZE test-# SELECT count(reverse_plpgsql(filepath)) test-# FROM test;
Exécution #1 : 55269.941 ms
Exécution #2 : 56047.004 ms
Exécution #3 : 56149.888 ms
Moyenne : 55822,28 ms
test=# EXPLAIN ANALYZE test-# SELECT count(text_reverse(filepath)) test-# FROM test;
Exécution #1 : 4088.625 ms
Exécution #2 : 4089.729 ms
Exécution #3 : 4020.500 ms
Moyenne : 4066,28 ms
test=# EXPLAIN ANALYZE test-# SELECT count(reverse(filepath)) test-# FROM test;
Exécution #1 : 1596.176 ms
Exécution #2 : 1647.046 ms
Exécution #3 : 1657.531 ms
Moyenne : 1633,58 ms
Voici un graph faisant la synthèse des moyennes des temps de réponse :
Le graph suivant permet de mieux se rendre compte de l'overhead induie par
l'implémentation PL/perl et l'implémentation C.
Chose très intéressante : l'overhead pour renverser ~320000 enregistrements est de seulement 300ms, ce qui est bien entendu excellent et laisse présager de très bonnes performances quant au coût de la mise à jour d'un index fonctionnel.
Ainsi, comme cela pouvait être aisément imaginé, la version C est la plus rapide, suivie par la version PL/Perl. La version PL/pgSQL se traîne lamentablement derrière, ce qui justifie complètement la réécriture de la procédure stockée en C.
Quant à l'utilisation de cette fonction dans un cas concret, il faut se reporter à l'article précédent qui est cité en référence.
Cette fonction a été testé sur une base en PostgreSQL 8.0, 8.2 et 8.3devel (merci à depesz).
Je regrette de ne pas avoir pu aller un peu plus loin pour le précédent article, des impératifs de place m'ayant obligé à aller à l'essentiel sans montrer les différents plans d'exécution. Heureusement, l'article de hubert depesz lubaczewski montre tous les aspects que j'ai négligé, malheureusement c'est en anglais.
De plus amples précisions sont également disponibles en langue anglaise sur les sites Internet suivant :
Je remercie vivement les personnes suivantes :
Hubert "depesz" Lubaczewski a réalisé une implémentation d'index inversés sans que nous nous consultions.
La solution de depesz utilise une fonction écrite en PL/Perl et offre un gain de vitesse très appréciable par rapport à la version PL/pgSQL. On apprécie le petit test de performance des deux implémentations. Se référer à l'article indexable ” field like ‘%something’” pour plus de précisions.
Depuis, la version C de la fonction reverse a bien avancé et les gains sont encore plus significatifs. Ce sera très bientôt en ligne.
Voici un petit article technique que j'ai écrit dans le cadre du travail et dont on m'a autorisé la diffusion. Je remercie Guillaume Lelarge pour l'avoir posté sur le site de la communauté francophone des utilisateurs de PostgreSQL : Utiliser un index pour les recherches sur des motifs tels que « colonne LIKE '%chaîne' ».
Depuis la version 8i, Oracle implémente les index inversés. Voici une proposition d’implémentation équivalente pour PostgreSQL. Les index inversés permettent d’accélérer les recherches sur les motifs tels que « colonne LIKE '%chaîne' ». Dans un tel cas, PostgreSQL effectue un parcours séquentiel (ou « sequential scan ») de la table interrogée. Toutefois, il est possible d’émuler un index inverse au moyen d’une fonction de renversement de chaîne couplée à un index sur fonction.
Tout d’abord, il est nécessaire d’activer le support du langage procédural PL/pgSQL au sein de la base de données cible à l’aide de la commande Unix « createlang plpgsql BASECIBLE ».
La fonction appelée « reverse » prendra comme seul et unique argument une chaîne de type varchar et retournera une chaîne de type varchar.
CREATE OR REPLACE FUNCTION reverse(varchar) RETURNS varchar AS $PROC$
DECLARE
str_in ALIAS FOR $1;
str_out varchar;
str_temp varchar;
position integer;
BEGIN
-- Initialisation de str_out, sinon sa valeur reste à NULL
str_out := '';
-- Suppression des espaces en début et fin de chaîne
str_temp := trim(both ' ' from str_in);
-- position initialisée a la longueur de la chaîne
-- la chaîne est traitée a l’envers
position := char_length(str_temp);
-- Boucle: Inverse l'ordre des caractères d'une chaîne de caractères
WHILE position > 0 LOOP
-- la chaîne donnée en argument est parcourue
-- à l’envers,
-- et les caractères sont extraits individuellement au
-- moyen de la fonction interne substring
str_out := str_out || substring(str_temp, position, 1);
position := position - 1;
END LOOP;
RETURN str_out;
END;
$PROC$ LANGUAGE plpgsql
IMMUTABLE;
La fonction reverse est structurée en trois partie :
On notera que la fonction reverse est de catégorisée « IMMUTABLE », indiquant au SGBD que celle-ci ne modifie pas les données et garantie que la fonction retournera toujours le même résultat quand elle est appelée avec les mêmes arguments, condition indispensable à la création d’un index sur fonction. Voir la documentation PostgreSQL « Catégories de volatilité des fonctions » dans la partie « Etendre le SQL ».
Un essai de la procédure permet de s’assurer de son bon fonctionnement :
DPAR=# SELECT reverse('Chaîne à renverser');
reverse
resrevner à enîahC
(1 ligne)
Pour optimiser les recherches par suffixes, il est nécessaire de créer un index sur fonction, sans oublier une collecte des statistiques pour l’optimiseur:
CREATE INDEX reverse_index_prenom ON personnes (REVERSE(prenom) varchar_pattern_ops); ANALYZE TABLE personnes;
Ensuite, au lieu d’écrire un prédicat du type « WHERE prenom LIKE ’%mas’», on écrira :
SELECT * FROM personnes WHERE reverse(prenom) LIKE reverse(’%mas’);
PostgreSQL utilisera alors l’index créé précédemment et répondra instantanément. Sur une base de test contenant 4 millions d’enregistrement, les temps de réponse sont passés de 10s à 33ms pour la requête.
La fonction « reverse » pourrait être améliorée, mais l’implémentation décrite a l’avantage de montrer une réalisation simple et donne un exemple d’écriture de fonction pour PostgreSQL. On notera que la fonction renvoie une chaîne vide lorsque la valeur NULL est donnée en entrée. Un autre axe d’amélioration de la vitesse d’exécution serait la réécriture de cette fonction sous forme de fonction native en C couplée au choix d’un algorithme de renversement efficace.
Dernière mise à jour:
mardi 17 septembre 2019 à 10h31
Propulsé par:
