Nouvelle colonne leader_pid dans la vue pg_stat_activity

Étonnamment, depuis que les requêtes parallèles ont été ajoutées dans PostgreSQL 9.6, il était impossible de savoir à quel processus client était lié un worker parallèle. Ainsi, comme Guillaume l’a fait remarquer, it makes il est assez difficile de construire des outils simples permettant d’échantillonner les événements d’attente liés à tous les processus impliqués dans une requête. Une solution simple à ce problème est d’exporter l’information de lock group leader disponible dans le processus client au niveau SQL :

commit b025f32e0b5d7668daec9bfa957edf3599f4baa8
Author: Michael Paquier <michael@paquier.xyz>
Date:   Thu Feb 6 09:18:06 2020 +0900

Add leader_pid to pg_stat_activity

This new field tracks the PID of the group leader used with parallel
query.  For parallel workers and the leader, the value is set to the
PID of the group leader.  So, for the group leader, the value is the
same as its own PID.  Note that this reflects what PGPROC stores in
shared memory, so as leader_pid is NULL if a backend has never been
involved in parallel query.  If the backend is using parallel query or
has used it at least once, the value is set until the backend exits.

Author: Julien Rouhaud
Reviewed-by: Sergei Kornilov, Guillaume Lelarge, Michael Paquier, Tomas
Vondra
Discussion: https://postgr.es/m/CAOBaU_Yy5bt0vTPZ2_LUM6cUcGeqmYNoJ8-Rgto+c2+w3defYA@mail.gmail.com

Avec cette modification, il est maintenant très simple de trouver tous les processus impliqués dans une requête parallèle. Par exemple :

=# SELECT query, leader_pid,
  array_agg(pid) filter(WHERE leader_pid != pid) AS members
FROM pg_stat_activity
WHERE leader_pid IS NOT NULL
GROUP BY query, leader_pid;
       query       | leader_pid |    members
-------------------+------------+---------------
 select * from t1; |      31630 | {32269,32268}
(1 row)

Attention toutefois, comme indiqué dans le message de commit, si la colonne leader_pid à la même valeur que la colonne pid, cela ne veut pas forcément dire que le processus client est actuellement en train d’effectuer une requête parallèle, car une fois que le champ est positionné il n’est jamais réinitialisé. De plus, pour éviter tout surcoût, aucun verrou supplémentaire n’est maintenu lors de l’affichage de ces données. Cela veut dire que chaque ligne est traitée indépendamment. Ainsi, bien que cela soit fort peu probable, vous pouvez obtenir des données incohérentes dans certaines circonstances, comme par exemple un worker paralèlle pointant vers un pid qui est déjà déconnecté.

Nouveau dans pg13: Colonne leader_pid dans pg_stat_activity was originally published by Julien Rouhaud at rjuju's home on March 08, 2020.

Les moteurs SQL permettent aux transactions concurrentes d’être isolées les unes des autres pour éviter les interférences. Cette propriété d’isolation correspond à la lettre I de l’acronyme bien connu “ACID”, les autres propriétés étant Atomicité, Cohérence (Consistency en anglais) et Durabilité.

Une particularité de l’isolation est qu’il y a plusieurs niveaux offerts au choix de l’utilisateur, qui diffèrent par leurs comportements en présence de transactions concurrentes.

Ces niveaux définis par le standard SQL dès la version 1992 sont dans l’ordre de l’isolation la plus faible à la plus forte:

• Read Uncommitted: une transaction voit les changements des autres transactions avant qu’elle ne soient validées. Ce mode n’est pas mis en oeuvre dans PostgreSQL.

• Read Committed: une transaction voit les changements d’une autre dès que l’autre a validé.

• Repeatable Read: quand une transaction relit une ligne déjà lue via une requête précédente, elle doit relire les mêmes valeurs, même si la ligne a été changée par une autre transaction validée entre temps.

• Serializable: une transaction ne peut pas voir ou produire des résultats qui n’auraient pas été possibles si d’autres transactions ne passaient pas concurremment.

Le niveau par défaut est Read Committed dans PostgreSQL et Repeatable Read dans MySQL ou MariaDB (en tout cas avec le moteur InnoDB). Au passage, le standard SQL indique que par défaut, c’est le mode Serializable qui doit être utilisé, donc cette recommandation n’est pas suivie (elle ne l’est pas non plus d’ailleurs par Oracle, ni MS SQL Server, ni DB2, ni Sybase ASE…).

Voyons un premier exemple très simple pour illustrer la différence entre MySQL et PostgreSQL dans leurs niveaux d’isolation par défaut:

Exemple 1

On a une table avec 4 valeurs:

CREATE TABLE a(x int);
INSERT INTO a VALUES (1),(2),(3),(4);

Une transaction Tx1 fait la somme et la moyenne des valeurs, pendant qu’une transaction Tx2 ajoute une valeur, avec un ordre d’exécution tel que l’insertion est committée entre les lectures.

Avec PostgreSQL dans son mode par défaut Read Committed:

-- Tx1                              -- Tx2
=# BEGIN;                           =# BEGIN;
BEGIN                               BEGIN

=# SELECT SUM(x) FROM a;
 sum
-----
  10
(1 ligne)
                                    =# INSERT INTO a VALUES(50);
                                    INSERT 0 1

                                    =# COMMIT;
                                    COMMIT
=# SELECT AVG(x) FROM a;
        avg
---------------------
 11.6666666666666667
   (1 ligne)

=# COMMIT;
COMMIT

La valeur 50 n’a pas été prise en compte dans la somme mais a été intégrée dans la moyenne. Si on regarde uniquement Tx1, on a donc deux résultats mathématiquement incohérents entre eux. C’est parce qu’en Read Committed, chaque nouvelle instruction SQL démarre à l’instant T sur un état de la base incluant tous les changements des autres transactions validées à cet instant T, en l’occurrence ici l’insertion de 50 par Tx2 et le COMMIT de Tx2 qui précèdent T.

Avec MySQL/MariaDB dans son isolation par défaut Repeatable Read, le résultat est différent:

-- Tx1                                      -- Tx2
mysql> BEGIN;                               mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)        Query OK, 0 rows affected (0.00 sec)

> SELECT SUM(x) FROM a;
+--------+             
| SUM(x) |             
+--------+             
|     10 |             
+--------+             
1 row in set (0.00 sec)

                                          > INSERT INTO a VALUES(50);         
                                          Query OK, 1 row affected (0.00 sec) 
                                          
                                          > COMMIT;
                                          Query OK, 0 rows affected (0.02 sec)


> SELECT AVG(x) FROM a;                   
+--------+                                
| AVG(x) |
+--------+
| 2.5000 |
+--------+
1 row in set (0.00 sec)

> COMMIT;
Query OK, 0 rows affected (0.00 sec)

Dans le cas de MySQL/MariaDB, la valeur 50 insérée par Tx2 est ignorée par Tx1, en application de l’isolation Repeatable Read. Pour avoir la même chose dans PostgreSQL sur cet exemple, il faudrait passer à un niveau d’isolation supérieur (Repeatable Read ou Serializable)

Ce qu’il faut retenir ici, c’est que dans les configurations par défaut, des requêtes SQL très simples exécutées dans des sessions concurrentes peuvent donner des résultats différents d’un moteur à l’autre.

On pourrait penser que pour aligner systématiquement les comportements entre MySQL et PostgreSQL, il suffit de les régler au mêmes niveaux d’isolation. Mais en réalité, les comportements à un même niveau d’isolation peuvent différer sensiblement d’un moteur SQL à l’autre. Voyons un autre exemple qui illustre ça.

Exemple 2

A présent on va utiliser l’isolation Repeatable Read dans les deux moteurs. Pour passer la session à ce niveau sans préjuger de la valeur par défaut, on peut exécuter les ordres suivants:

pour MySQL/MariaDB:

> SET SESSION transaction isolation level Repeatable Read;

pour PostgreSQL:

=# SET default_transaction_isolation TO 'Repeatable Read';

On considère deux tables vides:

CREATE TABLE a(xa int);
CREATE TABLE b(xb int);

Ensuite on exécute deux transactions concurrentes qui vont insérer dans chaque table le count(*) de l’autre table. Voici ce que ça donne avec PostgreSQL:

-- Tx1                              -- Tx2
=# BEGIN;                           =# BEGIN;
BEGIN                               BEGIN

=# INSERT INTO a
   SELECT count(*) FROM b;
INSERT 0 1
                                    =# INSERT INTO b
                                       SELECT count(*) FROM a;
                                    INSERT 0 1
=# COMMIT;
COMMIT
                                    =# SELECT COUNT(*) FROM a;
                                     count 
                                    -------
                                         0
                                    (1 ligne)
                                    
                                    =# COMMIT;
                                    COMMIT

Une fois que Tx1 et Tx2 sont terminées, voici les résultats:

=# SELECT * FROM a;
 xa 
----
  0
(1 ligne)

=# SELECT * FROM b;
 xb 
----
  0
(1 ligne)

Au final, la valeur 0 se retrouve dans les deux tables car pour chacune des transactions, la table dont elle calcule le count(*) est vide dans le cadre de sa visibilité.

Mais avec MySQL/MariaDB, le comportement et le résultat final sont différents, comme montrés dans la transcription ci-dessous. Tx2 se met en attente de Tx1 et récupère ses résultats avant de continuer, plutôt que d’être isolée de Tx1.

-- Tx1                                      -- Tx2
mysql> BEGIN;                               mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)        Query OK, 0 rows affected (0.00 sec)

> INSERT INTO a SELECT count(*) FROM b;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0


                                            > INSERT INTO b SELECT count(*)
                                              FROM a;
                                            -- (Ici Tx2 se trouve bloquée)

> COMMIT;
Query OK, 0 rows affected (0.01 sec)

                                            -- (Ici Tx2 est débloquée)
                                            
                                            Query OK, 1 row affected (5.03 sec)
                                            Records: 1  Duplicates: 0  Warnings: 0
                                            
                                            > -- résultat différent de PG
                                            > SELECT COUNT(*) FROM a;
                                            +----------+
                                            | count(*) |
                                            +----------+
                                            |        1 |
                                            +----------+
                                            
                                            > COMMIT;
                                            Query OK, 0 rows affected (0.01 sec)

-- Tx1 et Tx2 sont terminées
> SELECT * FROM a;
+------+
| xa   |
+------+
|    0 |
+------+
1 row in set (0.01 sec)

> -- résultat différent de PG
> SELECT * FROM b;
+------+
| xb   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Bien que la séquence d’instructions soit très simple et que les transactions soient en Repeatable Read pour les deux moteurs, le résultat obtenu diffère entre Postgres et MySQL/MariaDB au sens où la table b contient au final une ligne avec 0 dans PostgreSQL et 1 dans MySQL.

Dans la mesure où Tx1 et Tx2 n’écrivent pas sur la même ligne (et en l’occurrence même pas dans la même table), pour PostgreSQL l’INSERT de Tx1 n’interfère pas du tout avec ce que fait Tx2. Tx2 n’a pas besoin d’attendre que Tx1 finisse, elle peut compter les lignes de a via son “snapshot”.

En revanche avec MySQL, Tx2 attend la fin de Tx1, et compte les lignes de a en intégrant ce que Tx1 a fait (soit une insertion). Tx1 et Tx2 sont donc moins isolées. Du point de vue d’un utilisateur habitué au Repeatable Read de PostgreSQL, ce comportement est plutôt étonnant (c’est-à-dire qu’il correspond en fait au Read Committed de PostgreSQL)

Et on va voir que les différences ne s’arrêtent pas là avec une variante de cet exemple, dans laquelle Tx2 interroge la table a en début de transaction.

Exemple 3

Voici donc une variante de l’exemple 2 dans laquelle Tx2 va lire et renvoyer le count(*) FROM a en début de transaction. Pour PostgreSQL, ça ne change rien, tout au long de Tx2, count(*) FROM a vaut toujours 0, que ce soit pour le renvoyer ou l’insérer.

Mais pour MySQL, il y a une nette différence de comportement pour éviter le phénomène “Non Repeatable Read”, qui est le minimum attendu pour le niveau d’isolation Repeatable Read.

Voyons la séquence suivante avec MySQL/MariaDB:

-- Tx1                                      -- Tx2
mysql> BEGIN;                               mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)        Query OK, 0 rows affected (0.00 sec)

> INSERT INTO a SELECT count(*) FROM b;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
                                            > -- initie la lecture répétable
                                            > SELECT count(*) FROM a;
                                            +----------+
                                            | count(*) |
                                            +----------+
                                            |        0 |
                                            +----------+
                                            1 row in set (0.00 sec)


                                            > INSERT INTO b SELECT count(*)
                                              FROM a;
                                            -- (Ici Tx2 se trouve bloquée)

> COMMIT;
Query OK, 0 rows affected (0.01 sec)

                                            -- (Ici Tx2 est débloquée)
                                            
                                            Query OK, 1 row affected (3.13 sec)
                                            Records: 1  Duplicates: 0  Warnings: 0
                                            

                                            > SELECT * FROM b;
                                            +------+
                                            | xb   |
                                            +------+
                                            |    1 |
                                            +------+
                                            1 row in set (0.00 sec)
                                            
                                            > -- répète la lecture répétable 
                                            > SELECT COUNT(*) FROM a;
                                            +----------+
                                            | count(*) |
                                            +----------+
                                            |        0 |
                                            +----------+
                                            1 row in set (0.00 sec)
                                            
                                            > COMMIT;
                                            Query OK, 0 rows affected (0.01 sec)

Le résultat final dans a et b est le même que précédemment avec la table b qui contient 1, mais cette fois en fin de Tx2, il s’avère que SELECT COUNT(*) FROM a renvoie 0 alors que précédemment il renvoyait 1.

La différence est que l’interdiction du phénomène “Non Repeatable Read” est mise en oeuvre parce qu’il y a eu un SELECT count(*) FROM a qui renvoyait 0 en début de transaction. Il faut donc qu’un SELECT count(*) FROM a ultérieur continue de produire 0. Mais ceci est vrai uniquement quand il est exécuté directement, et pas quand il est exécuté en tant que sous-requête via INSERT INTO b SELECT count(*) FROM a, C’est pourquoi il y a cette incohérence assez troublante entre le 1 qu’on trouve dans b.xb et le 0 renvoyé au client, alors que c’est censé être le résultat d’un même calcul dans la même transaction.

Il faut voir aussi que cette différence de résultat perdure dans Tx2 jusqu’à sa fin. Par exemple avant le COMMIT on pourrait avoir la séquence suivante:

mysql> SELECT * FROM b;
+------+
| xb   |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

mysql> INSERT INTO b SELECT COUNT(*) FROM a;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM b;
+------+
| xb   |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM a;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Exemple 4

Ce dernier exemple illustre une spécificité du Repeatable Read de PostgreSQL qui évite un conflit d’écriture sur une même ligne au prix d’une annulation de la transaction. A contrario, MySQL/MariaDB au même niveau d’isolation n’annule pas la transaction, mais empêche l’effacement sans pour autant provoquer une erreur.

On va reprendre une table avec une colonne portant 4 nombres entiers, de 1 à 4. Les deux transactions concurrentes sont d’un côté une transaction Tx1 qui soustrait 1 à chaque valeur, et de l’autre une transaction Tx2 qui supprime la ligne portant la valeur maximale de la table. L’idée est que Tx2 veut supprimer une ligne que Tx1 change concurremment.

CREATE TABLE list(x int);
INSERT INTO list VALUES (1),(2),(3),(4);

Transcript PostgreSQL:

-- Tx1                                 -- Tx2
=# BEGIN;                              =# BEGIN;
BEGIN                                  BEGIN

=# UPDATE list SET x=x-1;              =# SELECT * FROM list; 
                                        x                     
                                       ---                    
                                        1                     
                                        2                     
                                        3                     
                                        4                     
                                       (4 lignes)             
                                       
                                       =# DELETE FROM list WHERE x=4;
                                       -- (Ici Tx2 se trouve bloquée)

=# COMMIT
COMMIT
                                        -- Tx2 part en erreur
                                        ERROR:  could not serialize access
                                        due to concurrent update
                                        
                                        =# \echo :SQLSTATE
                                        40001
                                        
                                        =# ROLLBACK;
                                        ROLLBACK

En isolation Repeatable Read, le moteur rejete l’écriture de Tx2 (au sens large du terme, la suppression d’une ligne étant une forme d’écriture) sur une ligne que Tx1 a déjà modifié. Ce refus se manifeste par une erreur spécifique (SQLSTATE 40001) et l’annulation de la transaction.

Avec MySQL/MariaDB ci-dessous, il n’y a pas d’annulation de la transaction. Le DELETE ne provoque pas d’erreur mais n’efface pas la ligne x=4, bien que cette ligne reste perpétuellement visible de Tx2.

-- Tx1                                      -- Tx2
mysql> BEGIN;                               mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)        Query OK, 0 rows affected (0.00 sec)

> UPDATE list SET x=x-1;                    > SELECT * FROM list;   
Query OK, 4 rows affected (0.00 sec)        +------+                
Rows matched: 4  Changed: 4  Warnings: 0    | x    |                
                                            +------+                
                                            |    1 |                
                                            |    2 |                
                                            |    3 |                
                                            |    4 |                
                                            +------+                
                                            4 rows in set (0.00 sec)
                                            
                                            > DELETE FROM list WHERE x=4;
                                            -- (Ici Tx2 se trouve bloquée)
                                            Query OK, 0 rows affected (5.73 sec)
> COMMIT;
Query OK, 0 rows affected (0.01 sec)

                                            > SELECT * FROM list;
                                            +------+
                                            | x    |
                                            +------+
                                            |    1 |
                                            |    2 |
                                            |    3 |
                                            |    4 |
                                            +------+
                                            4 rows in set (0.01 sec)

                                            > DELETE FROM list WHERE x=4;
                                            Query OK, 0 rows affected (0.00 sec)

                                            > SELECT * FROM list;
                                            +------+
                                            | x    |
                                            +------+
                                            |    1 |
                                            |    2 |
                                            |    3 |
                                            |    4 |
                                            +------+
                                            4 rows in set (0.00 sec)
                                            
                                            > COMMIT;
                                            Query OK, 0 rows affected (0.00 sec)

En répétant le SELECT * FROM list puis l’effacement, on voit que la ligne avec x=4 est toujours là mais que le DELETE n’arrive pas à l’atteindre. La seule indication que le moteur ne veut pas la supprimer est le 0 rows affected dans l’information d’accompagnement du retour du DELETE.

Par comparaison avec PostgreSQL, le non-effacement de la ligne dans Tx2 correspondrait au comportement de Read Committed de PostgreSQL, mais la persistence à montrer cette ligne alors que Tx1 l’a supprimée correspondrait plutôt à Repeatable Read. En ce sens le Repeatable Read de MySQL est une sorte d’entre-deux entre les deux niveaux Read Committed et Repeatable Read de Postgres.

Conclusion

Quand on porte des applications de MySQL à PostgreSQL ou inversement, ou qu’on veut faire des services qui fonctionnent sur les deux, il faut s’attendre à des comportements différents avec les transactions concurrentes, même quand on les configure sur le même niveau d’isolation.

Le standard SQL dit que certains niveaux d’isolation doivent éviter certaines anomalies d’accès concurrents (dits “phenomena” dans les termes du standard), mais chaque implémentation SQL interprète ça de manière plus ou moins étendue et contraignante, avec des résultats visibles clairement différents pour le code SQL.

PostgreSQL utilise Read Committed par défaut alors que MySQL a choisi Repeatable Read qui est mieux isolé, mais lorsque PostgreSQL est utilisé au niveau Repeatable Read, ses transactions sont concrètement mieux isolées qu’avec MySQL.

Remplacer une chaîne par une autre dans une chaîne plus large est simple en SQL, avec la fonction replace:

select replace('la valeur est bar', 'bar', 'foo');
     replace
-------------------
 la valeur est foo

Mais il n’y a pas dans les fonctions de base de PostgreSQL de fonction pour substituer des chaînes multiples, chacune par leur propre remplaçante, comme on peut trouver en PHP avec strtr, ou en Python ou Perl avec des opérateurs de substitution via expressions régulières (qu’on va utiliser plus loin dans ce billet justement).

En SQL, regexp_replace veut bien chercher une série de chaînes en une seule passe, mais pas les remplacer individuellement, c’est-à-dire que regexp_replace(string, 'foo|bar', 'baz', 'g') remplacerait foo et bar par baz, mais on ne peut pas lui demander de remplacer foo par une chaîne et bar par une autre.

Dans certaines situations, des appels successifs à replace peuvent convenir, et c’est cette solution qui semble émerger sur les réponses toutes faites trouvables sur le web, mais dans le cas général, cette méthode a un risque de produire des résultats faux par rapport à un remplacement en une seule passe.

Par exemple, dans la conversion d’un texte brut en entités HTML, ces cinq substitutions au minimum doivent être appliquées pour avoir du HTML valide:

 > → >
 < → &lt;
 & → &amp;
 " → &apos;
 ' → &quot;

Imaginons qu’on les applique dans cet ordre, avec une requête de ce genre:

select replace(replace(replace(replace(replace(
  rawtext,
  '>', '>'),
  '<', '&lt;'),
  '&', '&amp;'),
  '"', '&apos;'),
  '''', '&quot;');

Si le texte à transformer est <strong> AT&T </strong>, le résultat produit sera:

<strong> AT&T </strong>

ce qui est clairement faux (si on le reconvertit en texte brut on ne retrouve pas l’original). Le bon résultat devrait être:

<strong> AT&T </strong>

Le problème avec la requête ci-dessus est qu’une fois que > a été remplacé par > (ou < par <), l’étape suivante de remplacement n’a aucun moyen de distinguer l’esperluète dans AT&T (qui doit être remplacée) de celle dans > qui doit être laissée telle quelle.

Dans cet exemple en particulier, on peut réordonner les remplacements pour que & soit substitué par & en premier. Mais dans le cas général ça peut être impossible à faire, s’il y a des références circulaires entre les chaînes à remplacer, par exemple si on veut inverser deux mots:

-- remplacer foo par bar et bar par foo.

-- mauvais résultat, version 1
select replace(replace('foo et bar', 'foo', 'bar'), 'bar', 'foo');

  replace   
------------
 foo et foo

-- mauvais résultat, version 2
 select replace(replace('foo et bar', 'bar', 'foo'), 'foo', 'bar');
 
  replace   
------------
 bar et bar

Pour le remplacement multiple, il faut un algorithme parcourant la chaîne et faisant les substitutions en une seule passe. Il faut aussi gérer le fait qu’il y ait parfois plusieurs substitutions possibles, avec une règle discriminante (souvent, le segment le plus long possible est remplacé, mais on peut choisir une autre règle si besoin).

On n’a donc pas cette fonction de base dans PostgreSQL, mais l’opérateur de substitution de Perl (s/pattern/replacement/flags) a cette fonctionnalité, puisque pattern peut être une suite d’alternatives et replacement peut être un tableau associatif (hash) clef/valeur avec toutes les substitutions, c’est-à-dire qu’on peut écrire ça:

my %subs = (foo=>bar, bar=>foo);
my $string = "foo et bar";
$string =~ s/(foo|bar)/$subs{$1}/g;
print $string;

En plus l’implémentation depuis Perl 5.10 de cette forme d’expression régulière a été spécifiquement optimisée pour être particulièrement efficace:

Trie optimisation of literal string alternations

Alternations, where possible, are optimised into more efficient matching structures. String literal alternations are merged into a trie and are matched simultaneously. This means that instead of O(N) time for matching N alternations at a given point, the new code performs in O(1) time. A new special variable, ${^RE_TRIE_MAXBUF}, has been added to fine-tune this optimization. (Yves Orton)

PostgreSQL permet d’écrire notre fonction en Perl via l’extension plperl, et en voici une version prête à l’emploi:

CREATE FUNCTION multi_replace(string text, orig text[], repl text[])
RETURNS text
AS $BODY$
  my ($string, $orig, $repl) = @_;
  my %subs;

  if (@$orig != @$repl) {
     elog(ERROR, "array sizes mismatch");
  }
  if (ref @$orig[0] eq 'ARRAY' || ref @$repl[0] eq 'ARRAY') {
     elog(ERROR, "array dimensions mismatch");
  }

  @subs{@$orig} = @$repl;
  
  my $re = join "|", map quotemeta,
     sort { (length($b) <=> length($a)) } keys %subs;
  $re = qr/($re)/;

  $string =~ s/$re/$subs{$1}/g;
  return $string;
$BODY$ language plperl strict immutable;

Cette fonction en Perl s’avère très rapide, même avec beaucoup de chaînes à remplacer (testé jusqu’à 1000, sachant qu’elles se cumulent dans une seule expression régulière) et beaucoup de remplacements effectifs.

On peut aussi l’écrire en plpgsql, mais le fait que l’ensemble des substitutions ne puisse pas se faire en une seule passe en SQL est très pénalisant dès qu’il y en a beaucoup à faire. Je ne mets pas le code ici, mais il est sur le wiki de postgresql.org: multi_replace en plpgsql. A part qu’il prend en entrée les remplacements via un paramètre de type jsonb plutôt que deux tableaux séparés, parce que c’est pratique et que ça permet de faire coexister les fonctions sous le même nom, le résultat produit doit être identique.

Par exemple, cet appel inversera foo et bar et en plus laissera foobar inchangé en utilisant l’astuce de le remplacer par lui même:

 select multi_replace(                                                           
 'foo et bar ne sont pas foobar',
 '{foo,bar,foobar}',
 '{bar,foo,foobar}');
         multi_replace         
-------------------------------
 bar et foo ne sont pas foobar

Que ce soit en plperl ou plpgsql, cette fonction évite le problème des replace() imbriqués avec leurs remplacements qui se chevauchent, et elle permet indifférement une liste de substitutions fixe ou dynamique. Mais si vous en avez besoin sur des chaînes de grande taille avec potentiellement beaucoup de substitutions, c’est un cas où Perl est vraiment beaucoup plus efficace que l’équivalent avec les fonctions SQL de base.

Parvenir à une suggestion d’index de qualité peut être une tâche complexe. Cela nécessite à la fois une connaissance des requêtes applicatives et des spécificités de la base de données. Avec le temps de nombreux projets ont essayé de résoudre ce problème, l’un d’entre eux étant PoWA version 3, avec l’aide de pg_qualstats extension. Cet outil donne de plutôt bonnes suggestions d’index, mais il est nécessaire d’installer et configurer PoWA, alors que certains utilisateurs aimeraient n’avoir que la suggestion d’index globale. Pour répondre à ce besoin de simplicité, l’algorithme utilisé dans PoWA est maintenant disponible dans pg_qualstats version 2, sans avoir besoin d’utiliser des composants additionnels.

EDIT: La fonction pg_qualstats_index\_advisor() a été changée pour retourner du json plutôt que du jsonb, afin de conserver la compatibilité avec PostgreSQL 9.3. Les requêtes d’exemples sont donc également modifiées pour utiliser json_array_elements() plutôt que jsonb_array_elements().

Qu’est-ce que pg_qualstats

Une manière simple d’expliquer ce qu’est pg_qualstats serait de dire qu’il s’agit d’une extension similaire à pg_stat_statements mais travaillant au niveaux des prédicats.

Cette extension sauvegarde des statistiques utiles pour les clauses WHERE et JOIN : à quelle table et quelle colonne un prédicat fait référénce, le nombre de fois qu’un prédicat a été utilisé, le nombre d’exécutions de l’opérateur sous-jacent, si le prédicat provient d’un parcours d’index ou non, la sélectivité, la valeur des constantes et bien plus encore.

Il est possible de déduire beaucoup de choses depuis ces informations. Par exemple, si vous examinez les prédicats qui contiennent des références à des tables différentes, vous pouvez trouver quelles tables sont jointes ensembles, et à quel point les conditions de jointures sont sélectives.

Suggestion Globale ?

Comment je l’ai mentionné, la suggestion d’index globale ajoutée dans pg_qualstats 2 utilise la même approche que celle de PoWA, ainsi cet article peut servir à décrire le fonctionnement des deux outils. La seule différence est que vous obtiendrez probablement une suggestion de meilleure qualité avec PoWA, puisque plus de prédicats seront disponibles, et que vous pourrez également choisir sur quel intervalle de temps vous souhaitez effectuer une suggestion d’index manquants.

La chose importante à retenir ici est qu’il s’agit d’une suggestion effectuée de manière globale, c’est-à-dire en prenant en compte tous les prédicats intéressant en même temps. Cette approche est différente de toutes les autres dont j’ai connaissance, qui ne prennent en compte qu’une seule requête à la fois. Selon moi, une approche globale est meilleure, car il est possible de réduire le nombre total d’index, en maximisant l’efficacité des index multi-colonnes.

Comment marche la suggestion globale

La première étape consiste à récupérer tous les prédicats qui pourraient bénéficier de nouveaux index. C’est particulièrement facile à obtenir avec pg_qualstats. En filtrant les prédicats venant d’un parcours séquentiel, exécutés de nombreuses fois et qui filtrent de nombreuses lignes (à la fois en nombre et en pourcentage), vous obtenez une liste parfaite de prédicats qui auraient très probablement besoin d’un index (ou alors dans certains cas une liste des requêtes mal écrites). Voyons regardons par exemple le cas d’une applications qui utiliserait ces 4 prédicats:

Ensuite, il faut construire l’ensemble entier des chemins de toutes les prédicats joints par un AND logique, qui contiennent d’autres prédicats, qui peuvent être eux-meme également joints par des AND logiques. En utilisants les même 4 prédicats vus précédemments, nous obtenons ces chemins :

Une fois tous les chemins construits, il suffit d’obtenir le meilleur chemin pour trouver le meilleur index à suggérer. Le classement de ces chemins est pour le moment fait en donnant un poids à chaque nœud de chaque chemin qui correspond au nombre de prédicats simple qu’il contient, et en additionnant le poids pour chaque chemin. C’est une approche très simple, et qui permet de favoriser un nombre minimal d’index qui optimisent le plus de requêtes possible. Avec nos exemple, nous obtenons :

Bien évidemment, d’autres approches de classement pourraient être utilisée pour prendre en compte d’autres paramètres, et potentiellement obtenir une meilleur suggestion. Par exemple, en prenant en compte également le nombre d’exécution ou la sélectivité des prédicats. Si le ratio de lecture/écriture pour chaque table est connu (ce qui est disponible avec l’extension powa-archivist), il serait également possible d’adapter le classement pour limiter la suggestion d’index pour les tables qui ne sont accédées presque exclusivement en écriture. Avec cet algorithme, ces ajustements seraient relativement simples à faire.

Une fois que le meilleur chemin est trouvé, on peut générer l’ordre de création de l’index ! Comme l’ordre des colonnes peut être important, l’ordre est généré en récupérant les colonnes de chaque nœud par poids croissant. Avec notre exemple, l’index suivant est généré :

CREATE INDEX ON t1 (id, ts, val);

Une fois que l’index est trouvé, on supprime simplement les prédicats contenus de la liste globale de prédicats et on reprendre de zéro jusqu’à ce qu’il n’y ait plus de prédicats.

Un peu plus de détails et mise en garde

Bien évidemment, il s’agit ici d’une version simplifiée de l’algorithme de suggestion, car d’autres informations sont nécessaires. Par exemple, la liste des prédicats est en réalité ajustée avec les classes d’opérateurs et méthode d’acces en fonction du type de la colonne et de sont opérateur, afin de s’assurer d’obtenir des index valides. Si plusieurs méthodes d’accès aux index sont trouvées pour un même meilleur chemin, btree sera choisi en priorité.

Cela nous amène à un autre détail : cette approche est principalement pensée pour les index btree, pour lesqules l’ordre des colonnes est critiques. D’autres méthodes d’accès ne requièrent pas un ordre spécifique pour les colonnes, et pour ces méthodes d’accès il est possible qu’une suggestion plus optimale soit possible si l’ordre des colonnes n’était pas pris en compte.

Un autre point important est que les classes d’opérateurs et méthodes d’accès ne sont pas gérés en dur mais récupérés à l’exécution en utilisant les catalogues locaux. Par conséquent, vous pouvez obtenir des résultats différents (et potentiellement meilleurs) si vous faites en sorte d’avoir toutes les classes d’opérateur additionelles disponibles quand vous utilisez la suggestion d’index globale. Cela pourrait être les extensions btree_gist et btree_gist, mais également d’autres méthodes d’accès aux index. Il est également possible que certain types / opérateurs n’aient pas de méthode d’accès associée dans les catalogues. Dans ce cas, ces prédicats sont retournées séparément dans une liste de prédicats non optimisables automatiquement, et pour lequel une analyse manuelle est nécessaire.

Enfin, comme pg_qualstats ne traite pas les prédicats composés d’expressions, l’outil ne peut pas suggérer d’index sur des expressions, par exemple en cas d’utilisateur de recherche plein texte.

Exemple d’utilisation

Une simple fonction est fournie, avec des paramètres facultatifs, qui retourne une valeur de type json :

CREATE OR REPLACE FUNCTION pg_qualstats_index_advisor (
    min_filter integer DEFAULT 1000,
    min_selectivity integer DEFAULT 30,
    forbidden_am text[] DEFAULT '{}')
    RETURNS json

Les noms de paramètres sont parlants :

• min_filter: combien de lignes le prédicat doit-il filtrer en moyenne pour être pris en compte par la suggestion globale, par défaut 1000 ;
• min_selectivity: quelle doit être la sélectivité moyenne d’un prédicat pour qu’il soit pris en compte par la suggestion globale, par défaut 30% ;
• forbidden_am: liste des méthodes d’accès aux index à ignorer. Aucune par défaut, bien que pour les version 9.6 et inférieures les index hash sont ignoré en interne, puisque ceux-ci ne sont sur que depuis la version 10.

Voici un exemple simple, tirés des tests de non régression de pg_qualstats :

CREATE TABLE pgqs AS SELECT id, 'a' val FROM generate_series(1, 100) id;
CREATE TABLE adv (id1 integer, id2 integer, id3 integer, val text);
INSERT INTO adv SELECT i, i, i, 'line ' || i from generate_series(1, 1000) i;
SELECT pg_qualstats_reset();
SELECT * FROM adv WHERE id1 < 0;
SELECT count(*) FROM adv WHERE id1 < 500;
SELECT * FROM adv WHERE val = 'meh';
SELECT * FROM adv WHERE id1 = 0 and val = 'meh';
SELECT * FROM adv WHERE id1 = 1 and val = 'meh';
SELECT * FROM adv WHERE id1 = 1 and id2 = 2 AND val = 'meh';
SELECT * FROM adv WHERE id1 = 6 and id2 = 6 AND id3 = 6 AND val = 'meh';
SELECT * FROM adv WHERE val ILIKE 'moh';
SELECT COUNT(*) FROM pgqs WHERE id = 1;

Et voici ce que la fonction retourne :

SELECT v
  FROM json_array_elements(
    pg_qualstats_index_advisor(min_filter => 50)->'indexes') v
  ORDER BY v::text COLLATE "C";
                               v
---------------------------------------------------------------
 "CREATE INDEX ON public.adv USING btree (id1)"
 "CREATE INDEX ON public.adv USING btree (val, id1, id2, id3)"
 "CREATE INDEX ON public.pgqs USING btree (id)"
(3 rows)

SELECT v
  FROM json_array_elements(
    pg_qualstats_index_advisor(min_filter => 50)->'unoptimised') v
  ORDER BY v::text COLLATE "C";
        v
-----------------
 "adv.val ~~* ?"
(1 row)

La version 2 de pg_qualstats n’est pas encore disponible en version stable, mais n’hésitez pas à la tester et rapporter tout problème que vous pourriez rencontrer !

pg qualstats 2: Suggestion d'index globale was originally published by Julien Rouhaud at rjuju's home on January 06, 2020.

Cet article fait partie d’une série d’article sur la beta de PoWA 4, et décrit le nouveau daemon powa-collector.

Nouveau daemon powa-collector

Ce daemon remplace le précédent background worker lorsque le nouveau mode remote est utilisé. Il s’agit d’un simple daemon écrit en python, qui s’occupera de toutes les étapes nécessaires pour effectuer des snapshots distants. Il est disponible sur pypi.

Comme je l’ai expliqué dans mon précédent article introduistant PoWA 4, ce daemon est nécessaire pour la configuration d’un mode remote, en gardant cette architecture à l’esprit :

Sa configuration est très simple. Il vous suffit tout simplement de renommer le fichier powa-collector.conf.sample fourni, et d’adapter l’URI de connexion pour décrire comment se connecter sur votre serveur repository dédié, et c’est fini.

Une configuration typique devrait ressembler à :

{
    "repository": {
        "dsn": "postgresql://powa_user@server_dns:5432/powa",
    },
    "debug": true
}

La liste des serveur distants, leur configuration ainsi que tout le reste qui est nécessaire pour le bon fonctionnement sera automatiquement récupéré depuis le serveur repository que vous ave déjà configuré. Une fois démarré, il démarrera un thread dédié par serveur distant déclaré, et maintiendra une connexion persistente sur ce serveur distant. Chaque thread effectuera un snapshot distant, exportant les données sur le serveur repository en utilisant les nouvelles fonctions sources. Chaque thread ouvrira et fermera une connexion sur le serveur repository lors de l’exécution du snapshot distant.

Bien évidemment, ce daemon a besoin de pouvoir se connecter sur tous les serveurs distants déclarés ainsi que le serveur repository. La table powa_servers, qui stocke la liste des serveurs distants, a un champ pour stocker les nom d’utilisateur et mot de passe pour se connecter aux serveur distants. Stocker un mot de passe en clair dans cette table est une hérésie, si l’on considère l’aspect sécurité. Ainsi, comme indiqué dans la section sécurité de PoWA, vous pouve stocker un mot de passe NULL et utiliser à la place n’importe laquelle des autres méthodes d’authentification supportées par la libpq (fichier .pgpass, certificat…). C’est très fortement recommandé pour toute installation sérieuse.

La connexion persistente sur le serveur repository est utilisée pour superviser la daemon :

• pour vérifier que le daemon est bien démarré
• pour communiquer au travers de l’UI en utilisant un protocole simple afin d’effectuer des actions diverses (recharger la configuration, vérifier le status d’un thread dédié à un serveur distant…)

Il est à noter que vous pouvez également demander au daemon de recharger sa configuration en envoyant un SIGHUP au processus du daemon. Un rechargement est nécessaire pour toute modification effectuée sur la liste des serveurs distants (ajout ou suppression d’un serveur distant, ou mise à jour d’un existant).

Veuillez également noter que, par choix, powa-collector n’effectuera pas de snapshot local. Si vous voulez utiliser PoWA pour le serveur repository, il vous faudra activer le background worker original.

Nouvelle page de configuration

La page de configuration est maintenant modifiée pour donner toutes les informations nécessaires sur le status du background worker, le powa-collector daemon (incluant tous ses threads dédiés) ainsi que la liste des serveurs distants déclarés. Voici un exemple de cette nouvelle page racine de configuration :

Si le daemon powa-collector est utilisé, le status de chaque serveur distant sera récupéré en utilisant le protocole de communication. Si le collecteur rencontre des erreurs (lors de la connexion à un serveur distant, durant un snapshot par exemple), celles-ci seront également affichées ici. À noter également que ces erreurs seront également affichées en haut de chaque page de toutes les pages de l’UI, afin d’être sûr de ne pas les rater.

De plus, la section configuration a maintenant une hiérarchie, et vous pourrez voir la liste des extensions ainsi que la configuration actuelle de PostgreSQL pour le serveur local ou distant en cliquant sur le serveur de votre choix!

Il y a également un nouveau bouton Reload collector sur le bandeau d’en-tête qui, comme on pourrait s’y attendre, demandera au collecteur de recharger sa configuration. Cela peut être utile si vous avez déclarés de nouveaux serveurs mais n’ave pas d’accès au serveur sur lequel le collecteur s’exécute.

Conclusion

Cette article est le dernier de la séurie concernant la nouvelle version de PoWA. Il est toujours en beta, n’hésitez donc pas à le tester, rapporter tout bug rencontré ou donner tout autre retour!

PoWA 4: Nouveau daemon powa-collector was originally published by Julien Rouhaud at rjuju's home on December 10, 2019.

Depuis la version 12, les collations de PostgreSQL peuvent être créées avec un paramètre nommé deterministic, qui peut être vrai ou faux, si bien que les collations sont maintenant soit déterministes (ce qu’elles sont par défaut), soit non déterministes.

Qu’est-ce que ce terme signifie exactement? Il se réfère à ce qu’Unicode appelle comparaisons déterministes ou en VO deterministic comparisons entre chaînes de caractères:

This is a comparison where strings that do not have identical binary contents (optionally, after some process of normalization) will compare as unequal

En français, c’est une comparaison où les chaînes qui n’ont pas un contenu binaire identique (optionnellement, après un processus de normalisation) ne sont pas égales.

Avant la version 12, les comparaisons de types concernés par les collations dans Postgres sont toujours déterministes d’après la définition ci-dessus. Spécifiquement, lorsque le fournisseur de collation (libc ou ICU) indique que deux chaînes sont égales, un tie-breaker binaire est appliqué, de sorte que seules les chaînes égales au niveau binaire sont considérées comme vraiment égales par le SGBD.

Depuis la version 12, le nouveau paramètre “deterministic” peut être mis à faux dans CREATE COLLATION pour demander que les comparaisons évitent le tie-breaker, de sorte qu’une différence de représentation en mémoire entre les chaînes ne soit pas un obstacle à les reconnaître comme égales si la locale sous-jacente dit qu’elles le sont.

Non seulement cela affecte les comparaisons directes ou les recherches en tables avec une clause WHERE, mais aussi les résultats de GROUP BY, ORDER BY, DISTINCT, PARTITION BY, les constraintes uniques, et tout ce qui implique l’opérateur d’égalité entre chaînes.

Qu’est-ce qui peut être fait avec les collations non déterministes?

La fonctionnalité la plus évidente est la comparaison en ignorant la casse ou les accents, qui peut maintenant être mise en oeuvre avec une clause COLLATE, plutôt qu’en appelant explicitement les fonctions pour translater la casse (upper, lower) et supprimer les accents (unaccent). Maintenant que ceci est accessible via le service de collation, la recommandation classique d’utiliser le type citext datatype pour ces usages peut être reconsidérée.

Au-delà de cet usage, les collations non déterministes permettent de reconnaître l’égalité de chaînes qui sont équivalentes canoniquement (différant uniquement par leur forme normale Unicode), ou qui diffèrent seulement par des séquences compatibles entre elles, ou par la ponctuation, ou par des caractères non affichables.

Excepté pour l’équivalence canonique, ces fonctionnalités de comparaison sont optionnelles, et elles sont activables en déclarant des attributs dans le paramètre locale, et en particulier les niveaux de comparaison.

La doc Unicode Technical Report #35 fournit une table de paramètres de collation avec des clés et valeurs au format BCP47, mais les exemples de ce billet utiliseront la syntaxe “ancienne” des attributs avec ICU: colStrength, colCaseLevel, colAlternate plutôt que les “nouvelles” clés en question (respectivement ks, kc, ka). La raison est que les attributs pré-cités fonctionnent avec toutes les versions de ICU, alors que les clés ne sont reconnues que lorsque PostgreSQL est compilé avec ICU version 54 (sorti en 2014) ou plus récent. Il se trouve que que les binaries pré-compilés pour Windows sont compilés avec ICU version 53, donc au moins pour cette raison il est préférable d’utiliser la syntaxe la plus ancienne.

A présent, voyons une liste des fonctionnalités avancées de comparaison qui sont rendues possibles par les collations non déterministes.

1. Égalité entre séquences équivalentes de points de codes

Il s’agit d’une exigence d’Unicode que PostgreSQL ne pouvait pas satisfaire jusqu’à présent.

Pour comprendre cela, il faut se rappeler que le point de code (codepoint en anglais) est un numéro Unicode, qui désigne le plus souvent un caractère, mais qui par extension peut désigner aussi certains marqueurs dans le flux de texte, comme l’indicateur de direction pour dire que le texte doit s’afficher de gauche à droite ou de droite à gauche, ou encore un accent tout seul qui doit être interprété en lien avec un caractère précédent. Pour le meilleur ou pour le pire, Unicode est nettement plus compliqué qu’une association un-à-un de caractères avec des numéros.

Comme expliqué dans Équivalence Unicode:

L’équivalence canonique est une forme d’équivalence qui préserve visuellement et fonctionnellement les caractères équivalents. Ils ont un codage binaire différents mais représentent un texte identique.

Les collations non déterministes reconnaissent les séquences équivalentes comme égales sans avoir à spécifier d’attribuer particulier dans l’argument locale.

L’exemple ci-dessous qui illustre cela utilise une locale non liée à un langage particulier: déclarée par une chaîne vide, qui sélectionne la collation racine. und peut aussi être utilisée, en tant que tag BCP-47 de 3 lettres signifiant “undefined”. Autrement un code de langue peut être utilisé, suivi optionnellement par un code de “script” (type d’écriture), et un code de région. Par exemple on pourra utiliser 'fr-CA' pour “français tel que pratiqué au Canada”.

Exemple d’équivalent canonique entre les formes normales NFD et NFC:

CREATE COLLATION nd (
   provider = 'icu',
   locale='',   -- or 'und' (pas de langue ou région spécifiée)
   deterministic = false
);

SELECT s1, s2, s1 = s2 COLLATE nd AS equal FROM
  (VALUES (E'El Nin\u0303o', E'El Ni\u00F1o')) AS s(s1,s2);

   s1    |   s2    | equal 
---------+---------+-------
 El Niño | El Niño | t

Par opposition, avec n’importe quelle collation déterministe, on obtiendrait le résultat f pour false dans la colonne equal, étant donné que les chaînes s1 and s2 ne sont pas égales au niveau binaire (c.a.d au niveau des octets qui les composent après encodage).

2. Égalité entre séquences compatibles de points de codes

Sans être équivalentes, des séquences de points de codes peuvent être simplement compatibles, auquel cas elles peuvent optionnellement être considérées comme égales.

Notamment, une ligature typographique peut souvent être représentée avec un seul point de code spécifique ou comme deux caractères distincts qui se suivent.

Au niveau tertiaire (le niveau par défaut), ces séquences ne sont pas égales. Voyons ça en SQL, en réutilisation la collation "nd" définie plus haut:

SELECT s1, s2, s1 = s2 COLLATE nd AS equal FROM
 (VALUES ('shelffull', E'shel\ufb00ull')) AS s(s1,s2);

    s1     |    s2    | equal 
-----------+----------+-------
 shelffull | shelffull | f

Au niveau secondaire en revanche, ces séquences sont considérées comme égales:

CREATE COLLATION nd2 (
  provider = 'icu',
  locale = '@colStrength=secondary', -- ou 'und-u-ks-level2'
  deterministic = false
);

SELECT s1, s2, s1 = s2 COLLATE nd2 AS equal FROM
 (values ('shelffull', E'shel\ufb00ull')) AS s(s1,s2);

    s1     |    s2    | equal 
-----------+----------+-------
 shelffull | shelffull | t

3. Égalité entre chaînes qui diffèrent par la casse

L’usage le plus typique des collations non déterministes est certainement la comparaison insensible à la casse.

Au niveau secondaire, les chaînes qui diffèrent par la casse sont considérées comme égales:

SELECT s1, s2, s1 = s2 COLLATE nd2 AS equal FROM
 (values ('Abc', 'ABC')) AS s(s1,s2);

 s1  | s2  | equal 
-----+-----+-------
 Abc | ABC | t

4. Égalité entre chaînes qui diffèrent par la casse et les accents

Les chaînes qui diffèrent par les accents ou la casse (ou les deux) sont considérées comme égales au niveau primaire:

CREATE COLLATION nd1 (
  provider = 'icu',
  locale = '@colStrength=primary', -- ou 'und-u-ks-level1'
  deterministic = false
);
  
SELECT s1, s2, s1 = s2 COLLATE nd1 AS "equal-nd1",
               s1 = s2 COLLATE nd2 AS "equal-nd2"
 FROM (values ('Été', 'ete')) AS s(s1,s2);

 s1  | s2  | equal-nd1 | equal-nd2 
-----+-----+-----------+-----------
 Été | ete | t         | f

5. Égalité entre chaînes qui diffèrent par les accents mais pas la casse

Il est possible d’ignorer les accents mais pas la casse en restant au niveau primaire de comparaison, et en activant un attribut booléen spécifique: colCaseLevel.

Exemple:

CREATE COLLATION nd2c (
  provider = 'icu',
  locale = 'und@colStrength=primary;colCaseLevel=yes' , -- ou 'und-u-ks-level1-kc'
  deterministic = false
);

SELECT 'Ete' = 'Eté' COLLATE nd2c AS eq1,
       'Ete' = 'ete' COLLATE nd2c AS eq2;
 eq1 | eq2 
-----+-----
 t   | f

6. Ignorer les espaces et la ponctuation

Pour la ponctuation, on peut d’abord l’ignorer complètement, comme indiqué dans le chapitre “Ignore Punctuation” Options de la documentation ICU.

C’est faisable en activant “Alternate Handling” aux niveaux de comparaison de 1 à 3. Etant donné que colStrength=tertiary par défaut, on peut laisser colStrength non spécifié, comme ci-dessous:

CREATE COLLATION "nd3alt" (
  provider = 'icu',
  locale='und@colAlternate=shifted',
  deterministic = false
);

SELECT '{your-name?}' = 'your name' COLLATE "nd3alt" AS equal;
 equal
-------
 t

7. Égalité entre symboles et ponctuation compatibles

colAlternate mis à shifted au niveau de comparaison “quaternary” peut être utilisé pour faire reconnaître l’égalité entre des symboles y compris de ponctuation qui sont équivalent sur le plan linguistique, mais sont exprimés via des codes de points différents. Par exemple HORIZONTAL ELLIPSIS (U+2026) est équivalent à trois points consécutifs en US-ASCII (FULL STOP, U+002E), et FULLWIDTH COMMERCIAL AT (U+FF20) est équivalent à COMMERCIAL AT (U+0040) tel qu’utilisé dans les adresses mail exprimées en caractères US-ASCII.

CREATE COLLATION "nd4alt" (
  provider = 'icu',
  locale='und@colStrength=quaternary;colAlternate=shifted',
  deterministic = false
);

SELECT 'Wow…!' = 'Wow...!' COLLATE "nd4alt" AS equal;
 equal
-------
 t

8. Ignorer les points de codes affectés à des caractères invisibles

Au niveau de comparaison tertiaire ou inférieur, les points de codes qui sont dans les intervalles [\u0001-\u0008], [\u000E-\u001F] [\u007f-\u009F] (caractère de contrôles) sont ignorés dans les comparaisons.

C’est aussi vrai des points de codes pour les caractères d’espacement comme ceux-là (liste non exhaustive ne comprenant que les plus fréquents):

• SOFT HYPHEN (U+00AD)
• ZERO WIDTH SPACE (U+200B)
• INVISIBLE SEPARATOR (U+2063)
• LEFT-TO-RIGHT MARK (U+200E)
• RIGHT-TO-LEFT MARK (U+200F)
• WORD JOINER (U+2060)
• …et plein d’autres…

Exemple:

SELECT s1, s2, s1 = s2 COLLATE nd AS equal FROM
  (VALUES ('ABC', E'\u200eA\u0001B\u00adC')) AS s(s1,s2);

 s1  |    s2     | equal 
-----+-----------+-------
 ABC | ‎A\x01B­C | t

Pour que ces points de codes ne soient pas ignorés, le niveau de comparaison doit être mis au maximum, c’est-à-dire colStrength=identical (ou ks-identic pour la syntaxe avec tags). A ce niveau, la seule différence avec l’égalité binaire est l’interprétation des séquences en équivalence canonique.

CREATE COLLATION "nd-identic" (
  provider = 'icu',
  locale='und@colStrength=identical', -- or und-u-ks-identic
  deterministic = false
);

SELECT 'abc' = E'a\u0001bc' COLLATE "nd-identic" AS equal;
 equal 
-------
 f

Transformations (au-delà des collations)

Le umlaut allemand est parfois converti en séquences de caractères US-ASCII comme suit:

• ü => ue, Ü => Ue
• ö => oe, Ö => Oe
• ä => ae, Ä => Ae

Ces équivalences ne sont pas reconnues comme égales par les collations ICU, y compris au niveau primaire de comparaison et en spécifiant Allemand (de) comme langue. En revanche ß (eszett) et ss sont égaux au niveau primaire.

A partir de la version 60, ICU fournit de-ASCII comme en tant que règle de transformation. Les transformations sont réalisées par un service différent des collations, et qui n’est pas exposé par PostgreSQL (voir icu_transform() dans icu_ext si vous avez besoin de ce genre de transformation, ou plus généralement des translitérations entre systèmes d’écritures (scripts)).

Dans des tables utilisateurs, on peut avoir besoin de stocker une référence aux rôles PostgreSQL, par exemple pour représenter des droits d’accès à des fonctionnalités applicatives, ou des méta-données associées aux comptes. Se pose alors la question de ce qu’on peut utiliser comme référence pour désigner un rôle.

Bien sûr un rôle en tant qu’objet dans le catalogue a une clef unique, et même deux. Il a premièrement un OID (entier 32 bits non signé) attribué automatiquement, et deuxièmement le nom du rôle qui est aussi évidemment unique. Mais l’OID n’est pas portable d’une instance à l’autre, et un nom de rôle peut aussi changer dans le dos de l’applicatif, si bien qu’on peut vouloir chercher une troisième voie. Regardons ça de plus près.

Le stockage des rôles

Un rôle est stocké avec ses propriétés principales dans la table système pg_catalog.pg_authid, qui est partagée par toutes les bases:

postgres=# \d pg_authid
                        Table "pg_catalog.pg_authid"
     Column     |           Type           | Collation | Nullable | Default 
----------------+--------------------------+-----------+----------+---------
 oid            | oid                      |           | not null | 
 rolname        | name                     |           | not null | 
 rolsuper       | boolean                  |           | not null | 
 rolinherit     | boolean                  |           | not null | 
 rolcreaterole  | boolean                  |           | not null | 
 rolcreatedb    | boolean                  |           | not null | 
 rolcanlogin    | boolean                  |           | not null | 
 rolreplication | boolean                  |           | not null | 
 rolbypassrls   | boolean                  |           | not null | 
 rolconnlimit   | integer                  |           | not null | 
 rolpassword    | text                     | C         |          | 
 rolvaliduntil  | timestamp with time zone |           |          | 
Indexes:
    "pg_authid_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_authid_rolname_index" UNIQUE, btree (rolname), tablespace "pg_global"
Tablespace: "pg_global"

Avec une version 11 ou inférieure, on ne verra pas la première colonne oid visible ici, mais elle est quand même là, et avec un index unique pg_authid_oid_index qui porte dessus. La sortie ci-dessus est faite avec PostgreSQL 12, dans laquelle l’OID est devenue une colonne normale.

Généralement on accèdera plutôt aux rôles via la vue pg_roles, qui porte aussi cet OID mais qui diffère en ce que:

• elle n’est pas réservée aux super-utilisateurs.

• rolpassword est forcé en dur à '********' pour ne rien révéler des mots de passe.

• elle a en plus rolconfig avec les variables de configuration optionnelles associées au rôle, qu’on va justement utiliser pour ajouter des méta-données un peu plus loin dans ce billet.

postgres=# \d pg_roles
                         View "pg_catalog.pg_roles"
     Column     |           Type           | Collation | Nullable | Default 
----------------+--------------------------+-----------+----------+---------
 rolname        | name                     |           |          | 
 rolsuper       | boolean                  |           |          | 
 rolinherit     | boolean                  |           |          | 
 rolcreaterole  | boolean                  |           |          | 
 rolcreatedb    | boolean                  |           |          | 
 rolcanlogin    | boolean                  |           |          | 
 rolreplication | boolean                  |           |          | 
 rolconnlimit   | integer                  |           |          | 
 rolpassword    | text                     |           |          | 
 rolvaliduntil  | timestamp with time zone |           |          | 
 rolbypassrls   | boolean                  |           |          | 
 rolconfig      | text[]                   | C         |          | 
 oid            | oid                      |           |          | 

Pas de clef étrangère possible

Si pg_authid était une table normale, on pourrait écrire une déclaration s’y référant via une clef étrangère, du style:

CREATE TABLE nomtable(u_id oid REFERENCES pg_authid(oid), ...);

Mais comme c’est une table système, ce n’est pas possible. Si on essaie, on obtient:

ERREUR: droit refusé : « pg_authid » est un catalogue système

Et même si on pouvait faire des contraintes d’intégrité référentielles vers des tables systèmes, il est probable que pg_authid serait exclue pour une autre raison: c’est une table partagée par toutes les bases, donc à chaque fois qu’une ligne change, il faudrait que Postgres vérifie dans toutes les bases pour tester la validité de la contrainte, et ce ne serait pas compatible avec son architecture étanche où une connexion établie sur une base donnée n’a un accès direct qu’à cette seule base.

Le login comme clef de référence?

Le login ou nom du rôle est conservé lors d’un pg_restore, et il est unique, mais est-ce une bonne clef primaire?

Un renommage peut être fait avec un ordre SQL du genre:

ALTER ROLE nom_role RENAME to nouveau_nom;

qui peut être exécuté de n’importe quelle base.

Cet ordre ne déclenche pas de trigger, parce que comme répondu dans la discussion Event trigger and CREATE/ALTER ROLE/USER sur pgsql-hackers, un trigger est rattaché à une seule base alors qu’un rôle est global à l’instance:

If you were to create an event trigger in database A, then a user gets created in database B, your function would not be invoked, which becomes a problem.

Pour être tranquille, il faudrait interdire les changements de login, par principe. Mais imposer qu’un compte ne changera jamais de login n’est pas forcément réaliste quand des comptes sont associés à des personnes physiques. Une personne dont le login est son nom de famille peut vouloir le changer pour des raisons liées à son état civil. Ou encore quand un utilisateur hérite du compte nominatif d’un prédécesseur, difficile de lui imposer d’utiliser ad aeternam le nom de l’autre personne.

Les OIDs ne sont pas portables entre instances

Quand on exporte une instance avec pg_dumpall, les rôles sont exportés en commandes de création du style:

CREATE ROLE foo PASSWORD '...' ;

L’ennui est qu’il n’y a rien dans cette commande qui conserverait l’OID. Au moment de la restauration, les rôles seront donc certainement recréés avec des OIDs différents, qui ne correspondront plus aux références stockées dans les tables utilisateurs.

On aura d’ailleurs le même problème avec toute forme de réplication autre que réplication physique, où le réplicat est un clone de la source à tous points de vue.

Les OIDs via le type regrole comme référence?

Cette possibilité qui m’a été suggérée sur Twitter permet de faire face élégamment au problème de changement des OIDs par pg_restore, ainsi qu’à un éventuel renommage du rôle. regrole est un type alias d’OID, c’est-à-dire qu’au stockage c’est physiquement un OID, mais lorsqu’on y accède en lecture ou écriture, le nom du rôle remplace dynamiquement la valeur numérique de l’OID. La documentation v11 exprime ça en ces termes:

Les types alias d’OID ne disposent pas d’opérations propres à l’exception des routines spécialisées de saisie et d’affichage. Ces routines acceptent et affichent les noms symboliques des objets système, plutôt que la valeur numérique brute que le type oid utilise.

Voyons un exemple:

CREATE TABLE users(
 id regrole,
 fonction text,
 permissions text[]
);

En entrée dans la table, on peut utiliser le nom du rôle en tant que littéral:

CREATE ROLE ope_front;
INSERT INTO users VALUES ('ope_front', 'Opérateur Front Office', '{saisie}');

En sortie, il ressort aussi en littéral:

SELECT * FROM users;

    id     |        fonction        | permissions 
-----------+------------------------+-------------
 ope_front | Opérateur Front Office | {saisie}

Et c’est pareil en export avec pg_dump, où les données de la table ressortiront sous cette forme:

COPY public.users (id, fonction, permissions) FROM stdin;
ope_front	Opérateur Front Office	{saisie}
\.

L’intérêt est que si ce dump est rejoué dans une instance où l’utilisateur a été recréé, l’OID qui se retrouvera dans users.id sera correct contrairement à ce qui se passerait si l’OID était en format numérique dans l’export.

Par ailleurs, un éventuel changement de nom du rôle est transparent pour notre table users, ce qui est un avantage par rapport à une colonne de type text.

Par exemple:

ALTER USER ope_front RENAME TO operateur_front;
SELECT * FROM users;

       id        |        fonction        | permissions 
-----------------+------------------------+-------------
 operateur_front | Opérateur Front Office | {saisie}

SYSID comme clef de référence?

CREATE ROLE a un paramètre SYSID qui semble avoir été pensé au départ pour être un identifiant se référant à l’extérieur de Postgres. Mais on peut refermer cette piste tout de suite car il est obsolète et ignoré si on essaie de l’utiliser:

=# CREATE USER test SYSID 42;
NOTICE:  SYSID can no longer be specified
CREATE ROLE

Un paramètre de configuration comme clef de référence?

On peut associer à un rôle des valeurs pour certains paramètres de configuration qu’on trouve dans postgresql.conf, comme work_mem, default_transaction_isolation, etc. via ALTER ROLE, par exemple:

ALTER ROLE web_user SET work_mem TO '128MB';

Depuis PostgreSQL 9.3, cette possibilité est étendue à n’importe quel paramètre personnalisé déclaré avec un préfixe (avant il fallait une déclaration préalable dans custom_variable_classes). En fait le préfixe est plutôt prévu pour être le nom d’une extension, mais ça fonctionne très bien aussi sans extension, si bien qu’on peut faire, par exemple, en étant super-utilisateur:

ALTER ROLE nom_role SET users.id = '759f5abb-64b1-4dbc-8b71-f1d7bd18ad85';

Les choix de users comme espace de nom et id comme nom de paramètre sont arbitraires. Tous ces paramètres sont de toute manière exportés par pg_dumpall sous forme de commandes ALTER ROLE.

Les paramètres de configuration sont accessibles dans la colonne pg_roles.rolconfig de type text[], contenant un tableau avec des chaînes au format nom=valeur. Pour extraire le nom et la valeur, on peut utiliser la fonction plpgsql ci-dessous inspirée de la fonction ParseLongOption() dans le code de Postgres en langage C:

CREATE FUNCTION parse_option(string text, name OUT text,  value OUT text)
  RETURNS record
AS $$
declare
 p int := strpos(string, '=');
begin
  if (p > 0) then
    name := replace(left(string, p-1), '-', '_');
    value := substr(string, p+1);
  else
    name := replace(string, '-', '_');
    value := NULL;
  end if;
end
$$ LANGUAGE plpgsql immutable strict;

On peut donc obtenir l’ID personnalisé d’un rôle à partir de son login avec une requête du style:

SELECT o.value
 FROM pg_roles,
   unnest(rolconfig) as c(x),
   parse_option(c.x) as o
WHERE rolname = :'nom_du_role'
AND o.name = 'users.id';

Si c’est juste pour le compte avec lequel on est connecté, pas la peine de faire si compliqué, un SHOW suffira:

SHOW users.id; -- ou SELECT pg_catalog.current_setting('users.id')

Si une session a besoin d’accéder répétitivement à cette information sur tous les rôles, elle peut avoir intérêt à créer une table temporaire pour l’avoir en accès rapide, ce qui peut se faire via une requête de ce style:

CREATE TEMP TABLE cache_users AS
 SELECT r.oid, r.rolname, o.value AS user_id
 FROM pg_roles,
   unnest(rolconfig) as c(x),
   parse_option(c.x) as o
 WHERE o.name='users.id';
 

Conclusion

En utilisant le type regrole, on peut éviter les principaux inconvénients d’une référence directe à l’OID ou au nom du rôle.

En attribuant à des rôles des IDs synthétiques stockés sous forme de paramètres de configuration, on atteint les buts suivants:

• ils sont conservés par pg_dumpall / pg_restore.
• ils sont accessibles à l’identique dans toutes les bases, comme pg_roles.
• ils peuvent être vraiment immuables, puisqu’on n’a jamais aucune raison de changer un ID arbitraire.

En revanche, dans les tables utilisateurs, avoir une vraie contrainte d’intégrité référentielle vers cette information reste impossible, quelle que soit l’option retenue.

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…

Le CSV en remplacement du format unaligned (-A)

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:

• quand le séparateur se trouve dans les données, il n’y a aucun moyen de le distinguer (pas d’échappement).
• quand des retours à la ligne sont présents dans les champs, il n’y a pas de moyen de distinguer un saut de ligne intra-champ d’un séparateur d’enregistrement.

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.

CSV en format intermédiaire

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/</&lt;/g;
  s/>/&gt;/g;
  s/\n/<br>/g;
  s/\|/&#x7C;/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.

Aperçu rapide

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.

Configurer la liste des serveurs distants

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'::interval

Si 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.

API SQL pour configurer les serveurs distants

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 disponibles
• powa_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 snapshots
• powa_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.

Effectuer des snapshots distants

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 | 10

Un 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.

Nouveau mode remote !

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”.

Pourquoi un mode remote est-il important

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

• un redémarrage de PostgreSQL n’est plus nécessaire pour installer powa-archivist
• il n’y a plus de surcoût du au fait de stocker et requêter les données sur le même serveur PostgreSQL que vos serveurs de productions (il y a toujours certaines partie de l’UI qui nécessitent d’effectuer des requêtes sur le serveur d’origine, par exemple pour montrer des plans avec EXPLAIN, mais le surcoût est négligeable)
• il est maintenant possible d’utiliser PoWA sur un serveur en hot-standby

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.

Changements dans powa-web

L’interface graphique est le composant qui a le plus de changements visibles dans cette version 4. Voici les plus changements les plus importants.

Compatibilité avec le mode distant

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.

pg_track_settings support

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.

Nouveaux graphs disponibles

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 :

• dans le graph Block Access, où les métriques OS cache et disk read remplaceront la métrique read
• dans un nouveau graph System Resources (qui est également ajouté dans la page par requête), montrant les metrics ajoutées dans pg_stat_kcache 2.1

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.

Documentation des métriques et liens vers la documentation

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 :

Et des correctifs de bugs divers

Certains problèmes de longues dates ont également été rapportés :

• la boîte affichée au survol d’un graph montant les valeurs des métriques avait une position verticale incorrecte
• la sélection temporelle en utilisant l’aperçu des graphs ne montrait pas un aperçu correct après avoir appliqué la sélection
• les erreurs lors de la création d’index hypothétiques ou dans certains cas leur affichage n’était pas correctement gérés sur plusieurs pages
• les filtres des tableaux n’était pas réappliqués quand l’intervalle de temps sélectionné était changé

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 !

Conclusion

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:

• 7.2 (Feb 2002), la colonne oid devient optionnelle.
• 8.0 (Jan 2005), le paramètre default_with_oids est créé.
• 8.1 (Nov 2005), 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.

L’historique orienté-objet de Postgres

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:

• les tables sont des classes.
• les lignes des tables sont des instances de classe.
• une table peut hériter de la structure d’une table parente.
• les fonctions sont polymorphes (par la surcharge).

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 en PostgreSQL moderne

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.

Data checksums

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.

Nouveaux compteurs disponibles dans pg_stat_database

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.

Quel surcoût ?

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 :

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  |      4

Si 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.

Combien est-ce que ça coûte ?

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 = 27B

soit 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 = 227B

C’est “seulement” 10% de surcoût.

Et donc, comment minimiser ce 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.

Un exemple plus réaliste

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 null

La 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 :

• si l’intervalle de temps de la ligne est entièrement contenu dans l’intervalle de temps demandé, nous n’avons besoin de calculer que le delta du résumé de l’enregistrement : maxs_in_range.counter - mins_in_range.counter
• sinon (c’est-à-dire pour uniquement deux lignes par queryid) nous dépilons le tableau, filtrons les enregistrements qui ne sont pas compris dans l’intervalle de temps demandé, conservons la première et dernière valeur et calculons pour chaque compteur le maximum moins le minimum.

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 ?

Limitations

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.

Bonus

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 :

• l’opérateur -, pour obtenir la différent entre deux enregistrements
• l’opérateur /, pour obtenir la différence par seconde

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.

Conclusion

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.

Wait Events & pg_wait_sampling

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%.

Et PoWA ?

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 :

Wait events pour l’instance entière

Wait events pour une base de données

Wait events pour une seule requête

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.

Quid du contenu indésirable?

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.

Filtrage des mots par leur taille

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:

• dict_simple qui est installé de base.
• dict_int, module additionnel (contrib) pour éliminer les nombres au-delà d’un certain nombre de chiffres, très proche de ce qu’on cherche ici.
• dict_xsyn, module additionnel qui permet de déclarer des synonymes, pour générer plusieurs lexèmes à partir d’un seul terme.
• unaccent, module additionnel qui expose la suppression d’accents en tant que fonction SQL, mais aussi en dictionnaire filtrant, c’est-à-dire qui peut injecter un lexème passé aux dictionnaires d’après.

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.

Fonctions en C

Instantiation du 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);
}

Génération des lexèmes

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);
  }

}

Encapsulation dans une extension

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)

Utilisation

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.

Ne pas ouvrir involontairement son instance à l’Internet!

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”…

Quand on ouvre volontairement son instance

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.

L’attaque à la cryptomonnaie

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.

Interdire les connexions distantes non chiffrées

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

Création de la table

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:

• la 1ère ligne du fichier CSV avec les noms de colonnes, qu’on peut voir avec la commande:
  $ head -n 1 fr.openfoodfacts.org.products.csv
  On voit aussi sur cette première ligne que les colonnes sont séparées par une tabulation (on pourrait dire que le fichier est au format TSV en fait).
• le texte descriptif de la plupart des colonnes.

Pour 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:

• 76 float
• 95 varchar
• 2 integer

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”.

Insertion du contenu avec csvsql

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.

Import avec COPY

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.

Vérification de cohérence de l’import

Une fois l’import passé, faisons encore quelques vérifications de cohérence sur des critères basiques:

• l’unicité des valeurs si une des colonnes semble une clef primaire.
• la plausibilité des valeurs sur les colonnes de date.

=> 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.

Distinguer un doublon d’un quasi-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.

Retypage

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.

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.

Nouveautés

La version 2.1 de pg_stat_kcache vient d’être publiée.

Les deux nouvelles fonctionnalités principales sont:

• compatibilité avec les plateformes ne disposant pas nativement de la fonction getrusage() (comme Windows) ;
• plus de champs de la fonction 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:

• soft page faults ;
• hard page faults ;
• swaps ;
• messages IPC envoyés et reçus :
• signaux reçus ;
• context switch volontaires et involontaires.

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.

Un exemple rapide

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

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.

Que savons-nous?

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.

Une idée?

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.

Analyse poussée: saturation du TLB

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 kB

Ce 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 kB

Il 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         [.] SearchCatCache

Rien 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_lock

Nous 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.

La solution

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.

Streaming replication avec PostgreSQL 10

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.

PostgreSQL et l’authentification BSD

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.

Avec pgBouncer 1.6 sont arrivés deux nouveaux paramètres, ̀auth_user et auth_query`, qui permettent de faciliter la gestion du fichier des utilisateurs. Le fichier des utilisateurs contient les couples utilisateur / mot de passe hashé pour s’authentifier à la place de l’utilisateur final sur le serveur PostgreSQL. Il y a deux problèmes majeurs avec ce fichier utilisateur : son maintien fastidieux et le risque de collision entre nom de roles identiques mais avec des mots de passes différents si le même pgBoucner accède à plusieurs instances PostgreSQL.

auth_query permet de définir une requête à exécuter pour obtenir le couple utilisateur / mot de passe de l’instance lorsqu’ils sont introuvables dans le fichier.

Pour se connecter à l’instance PostgreSQL et exécuter la requête, pgBouncer utilise la valeur de auth_user comme nom d’utilisateur. Si PostgreSQL demande un mot de passe, pgBouncer utilise celui défini dans le fichier des utilisateurs. On peut utiliser un utilisateur commun à toutes les instances, qu’il faut alors créer sur chaque instance, ou ajouter le paramètre auth_user au niveau de la définir de l’accès à la base de données dans la section [databases].

Enfin, l’utilisateur auth_user doit pouvoir lire le contenu de pg_shadow pour obtenir les mots de passe hashés, il est alors recommandé de créer une fonction dans chaque base de données accédée par pgBouncer sur l’instance cible. Sinon, il faut donner accès à pg_shadow par un GRANT.

Concrètement cela donne, dans /etc/pgbouncer/pgbouncer.ini :

[pgbouncer]

; ...

auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

;; si on choisit de faire un GRANT sur pg_shadow
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

;; si on choisit de créer la fonction donnée dans la doc de pgBouncer
auth_query = SELECT pgbouncer.user_lookup($1)

Sur l’instance PostgreSQL, pour créer l’utilisateur pgbouncer chargé d’exécuter l’auth_query :

CREATE ROLE pgbouncer WITH PASSWORD 'le mot de passe';
SELECT usename, passwd FROM pg_shadow WHERE username = 'pgbouncer';

Créer le fichier /etc/pgbouncer/userlist.txt et mettre le résultat du SELECT sous cette forme :

"pgbouncer" "md5ac0b6e3b0d0904c0b602607e36c256eb"

Si on choisit de faire un GRANT sur pg_shadow, faire cette opération dans toutes les bases de données accédées via pgBouncer :

GRANT SELECT ON pg_catalog.pg_shadow TO pgbouncer;

Sinon exécuter le script SQL suivant dans toutes les bases de données accédées via pgBouncer, c’est la fonction donnée dans la doc avec la création du schéma en plus :

CREATE SCHEMA pgbouncer;
REVOKE ALL ON SCHEMA pgbouncer FROM public, pgbouncer;
GRANT USAGE ON SCHEMA pgbouncer TO pgbouncer;

CREATE OR REPLACE FUNCTION pgbouncer.user_lookup(in i_username text, out uname text, out phash text)
RETURNS record AS $$
BEGIN
    SELECT usename, passwd FROM pg_catalog.pg_shadow
    WHERE usename = i_username INTO uname, phash;
    RETURN;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
REVOKE ALL ON FUNCTION pgbouncer.user_lookup(text) FROM public, pgbouncer;
GRANT EXECUTE ON FUNCTION pgbouncer.user_lookup(text) TO pgbouncer;

On peut ajouter la fonction ou le GRANT à template1 pour les futures bases de données.

Autorité de certification avec EasyRSA

Installation

Télécharger et installer la dernière version de EasyRSA. La version 3 est différente de la version 2, fournie dans Debain Stretch.

wget https://github.com/OpenVPN/easy-rsa/releases/download/v3.0.3/EasyRSA-3.0.3.tgz
tar xzf EasyRSA-3.0.3.tgz
cd EasyRSA-3.0.3

Création de l’autorité de certification

Créer un fichier de configuration :

cp vars.example vars
vi vars

Changer quelques informations :

set_var EASYRSA_REQ_COUNTRY	"FR"
set_var EASYRSA_REQ_PROVINCE	"Ile-de-France"
set_var EASYRSA_REQ_CITY	"Paris"
set_var EASYRSA_REQ_ORG		"MyOrg"
set_var EASYRSA_REQ_EMAIL	"admin@myorg.lo"
set_var EASYRSA_REQ_OU		"Clients"

Créer l’autorité :

./easyrsa init-pki
./easyrsa build-ca

Il faut lui donner un nom, par exemple CA Clients ou PostgreSQL ou un truc significatif. Il faut aussi saisir un mot de passe pour la clé du certificat racine, celui qui permet de signer tout le reste.

Création des certificats

Pour créer un bi-clé pour un serveur :

./easyrsa build-server-full postgresql nopass

Si la clé et le certificat du serveur doivent être transmis pas un moyen peu sécurisé, comme le mail, créer un fichier PKCS#12 plutot qu’un tar :

./easyrsa export-p12 postgresql

On peut mettre un mot de passe au fichier.

./easyrsa build-server-full pgbouncer nopass

Générer aussi la liste de révocation, qui permet rendre des certificats invalides.

./easyrsa gen-crl

Extraction des éléments du p12

La clé privée :

openssl pkcs12 -in postgresql.p12 -nocerts -nodes -out postgresql.key
chmod 600 postgresql.key

Le certificat :

openssl pkcs12 -in postgresql.p12 -clcerts -out postgresql.crt

Le certificat de l’AC :

openssl pkcs12 -in postgresql.p12 -cacerts -out ca.crt

Configurer PostgreSQL pour le TLS

il faut les fichiers suivants :

pki/ca.crt
pki/crl.pem
pki/issued/postgresql.crt
pki/private/postgresql.key

ou

pki/crl.pem
pki/private/postgresql.p12

et extraire le contenu du fichier PKCS#12 (voir plus haut).

Le plus simple est de placer ces fichiers dans le répertoire $PGDATA, les chemins dans la configuration sont relatifs à ce répertoire.

Dans $PGDATA/postgresql.conf, configurer au minimum :

ssl = on
ssl_cert_file = 'postgresql.crt'
ssl_key_file = 'postgresql.key'
ssl_ca_file = 'ca.crt'
ssl_crl_file = 'crl.pem'

Un reload de la configuration suffit pour prendre en compte les modifications. Ce paramétrage permet à l’instance de chiffrer les connexions. On définit si la connexion TLS est imposé au clients et l’authentification par certificat dans le fichier pg_hba.conf

Le TLS ne concerne pas les connections locales par socket unix, géré par le type ‘local’.

Le type ‘host’, concerne les connexions en clair ou TLS, utiliser ‘hostssl’ ou ‘hostnossl’ pour distinguer le TLS dans les autorisations.

On peut interdire les connexions en clair avec ces deux lignes :

hostnossl   all   all   0.0.0.0/0   reject
hostnossl   all   all   ::/0        reject

Toutes les lignes ‘host’ suivantes ne concerneront que les connexions TLS, du fait de l’interdiction précédente.

Configurer pgBouncer pour le TLS

Clients

il faut les fichiers suivants :

pki/ca.crt
pki/crl.pem
pki/issued/postgresql.crt
pki/private/postgresql.key

ou

pki/crl.pem
pki/private/postgresql.p12

et extraire le contenu du fichier PKCS#12 (voir plus haut).

Dans /etc/pgbouncer/pgbouncer.ini, il faut configurer ‘client_tls_sslmode’ :

• disable : n’accepte pas les connexions TLS, le paramétrage par défaut
• allow : accepte les connexions TLS et les connexions en clair
• require : n’accepte que les connexions TLS
• verify-ca, verify-full : n’accepte que les connexions TLS, et demande un certificat au client issue de la même AC

Pour les chemins vers les fichiers :

client_tls_ca_file = /etc/pgbouncer/ca.crt
client_tls_key_file = /etc/pgbouncer/pgbouncer.key
client_tls_cert_file = /etc/pgbouncer/pgbouncer.crt

Pour authentifier les clients avec un certificat, il faut :

auth_type = cert

Le CN du certificat doit alors être identique au nom d’utilisateur de connexion.

Cela ne dispense pas de la configuration de l’authenfication entre pgBouncer et PostgreSQL, avec ‘auth_file’, voire ‘auth_user’ et ‘auth_query’.

Backends PostgreSQL

Pour que pgBouncer se connecte aux serveurs PostgreSQL en TLS, dans /etc/pgbouncer/pgbouncer.ini, il faut configurer :

server_tls_sslmode = require

Si on souhaite que pgBouncer vérifie le certificat de l’AC du serveur PostgreSQL :

server_tls_sslmode = verify-ca
server_tls_ca_file = /etc/pgbouncer/ca.crt

On peut enfin configurer pgBouncer et PostgreSQL pour que pgBouncer s’authentifie avec un certificat client, dans /etc/pgbouncer.ini :

server_tls_sslmode = verify-ca
server_tls_ca_file = /etc/pgbouncer/ca.crt
server_tls_key_file = /etc/pgbouncer/pgbouncer_back.key
server_tls_cert_file = /etc/pgbouncer/pgbouncer_back.crt

Le fichier pg_hba.conf devra alors demander un certificat avec l’option clientcert=1 :

hostssl all all ip_pgbouncer/32 md5 clientcert=1

On ne peut utiliser la méthode cert uniquement si le CN du certificat client de pgBouncer est identique au nom de l’utilisateur qui se connecte. Ainsi, on ne peut plus utiliser qu’un seul nom d’utilisateur pour les accès à PostgreSQL, dans ce cas là il vaut mieux forcer le nom d’utilisateur dans la section [database].

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.

Introduction

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.

Index Hypothétiques

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.

Une implémentation plus légère : HypoPG

J’ai utilisé une approche différente pour implémenter les index hypothétiques avec HypoPG.

• Pour commencer, cela doit pouvoir s’ajouter sur une version standard de PostgreSQL. C’est disponible en tant qu’extension et peut être utilisé (pour le moment) sur n’importe quelle version de PostgreSQL en version 9.2 ou plus ;
• Cela doit être le moins intrusif possible. C’est utilisable dès que l’extension a été créée, sans avoir besoin de redémarrer. De plus, chaque processus client dispose de son propre ensemble d’index hypothétiques. Concrètement, si vous ajoutez un index hypothétiques, cela ne perturbera absolument pas les autres connexions. De plus, les index hypothétiques sont stockés en mémoire, donc ajouter et supprimer un grand nombre d’index hypothétiques ne fragmentera pas le catalogue système.

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.

Fonctionnalités

Pour le moment, les fonctions suivantes sont disponibles :

• hypopg(): retourne la liste des index hypothétiques (dans un format similaire à pg_index).
• hypopg_add_index(schema, table, attribute, access_method): créé un index hypothétique sur une seule colonne.
• hypopg_create_index(query): créé un index hypothétique en utilisant un ordre standard CREATE INDEX.
• hypopg_drop_index(oid): supprime l’index hypothétique spécifié.
• hypopg_list_indexes(): retourne une courte version lisible de la liste
• des index hypothétiques.
• hypopg_relation_size(oid): retourne la taille estimée d’un index hypothétique.
• hypopg_reset(): supprime tous les index hypothétiques.

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.

Utilisation

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 install

HypoPG devrait alors être disponible :

rjuju=# CREATE EXTENSION hypopg ;
CREATE EXTENSION

Voyons 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 ;
ANALYZE

Ensuite, 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 !

Estimation de la taille d’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 MB

La 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.

Limitations

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) :

• seuls les index hypothétiques de type btree sont gérés ;
• pas d’index hypothétiques sur des expressions ;
• pas d’index hypothétiques sur des prédicats ;
• il n’est pas possible de spécifier le tablespace ;
• l’estimation de la taille de l’index pourrait être améliorée, et il n’est pas possible de changer le pourcentage de fragmentation.

Cependant, cette version peut déjà être utile dans de nombreux contextes.

Et pour la suite ?

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

• Debian
  • Etch
  • Lenny
  • Squeeze
  • Wheezy
  • Sid
• Ubuntu
  • Precise (12.04)

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

wget -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -

deb http://apt.postgresql.org/pub/repos/apt/ squeeze-pgdg main

Package: *
Pin: release o=apt.postgresql.org
Pin-Priority: 500

apt-get update
apt-get install pgdg-keyring

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 ?

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.

1. Les « groupes de tables »

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).

1. L'interface graphique

E-Maj comprend en fait deux grands composants :

• une infrastructure installée dans chaque base de données cible, l'extension E-Maj proprement dite. Elle comprend quelques tables techniques et surtout un ensemble de fonctions permettant de réaliser en SQL toutes les opérations souhaitées,
• une interface graphique sous la forme d'un plugin pour phpPgAdmin, l'outil web standard d'administration des bases PostgreSQL. Bien qu'optionnel, ce second composant facilite l'utilisation d'E-Maj, notamment pour les environnements de test,

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 dépôt principal : https://github.com/beaud76/emaj htt...
• et un dépôt spécifique pour la documentation : https://github.com/beaud76/emaj_doc...

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)