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.

GNU libc 2.28, sortie le 1er août 2018, comprend une mise à jour majeure des locales Unicode en général et des données relatives aux collations en particulier.

L’annonce indique:

The localization data for ISO 14651 is updated to match the 2016 Edition 4 release of the standard, this matches data provided by Unicode 9.0.0. This update introduces significant improvements to the collation of Unicode characters. […] With the update many locales have been updated to take advantage of the new collation information. The new collation information has increased the size of the compiled locale archive or binary locales.

Pour les instances PostgreSQL qui utilisent des collations glibc dépendant de la région et de la langue (exemples: fr_FR.iso885915 ou `en_US.utf-8’), cela signifie que certaines chaînes de caractères seront triées différemment après cette mise à jour. Une conséquence critique est que les index qui dépendent de ces collations doivent impérativement être reconstruits immédiatement après la montée de version de glibc. Les serveurs en réplication WAL/streaming doivent aussi être mis à jour simultanément car un secondaire doit tourner rigoureusement avec les mêmes locales que son primaire.

Le risque autrement est d’engendrer des corruptions d’index, comme illustré par ces deux discussions sur la liste pgsql-general en anglais: “Issues with german locale on CentOS 5,6,7”, et “The dangers of streaming across versions of glibc: A cautionary tale”.

En résumé, si Postgres parcourt un index avec une fonction de comparaison qui diffère de celle utilisée pour écrire cet index, il est possible que des valeurs présentes ne soient plus trouvées en lecture. Et en cas d’insertion, c’est pire puisque les nouvelles entrées risquent d’être insérées à des emplacements incohérents par rapport à la version précédente, et corrompre irrémédiablement l’index.

Ce problème de mise à jour des locales n’est donc pas nouveau, mais ce qui est particulier avec cette version 2.28 de la glibc, c’est l’importance de la mise à jour, qui est sans précédent dans la période récente. En effet depuis l’an 2000, d’après le bug#14095, les données des locales dans la glibc étaient modifiées au cas par cas. Cette fois-ci, il s’agit d’un rattrapage massif pour recoller au standard Unicode.

Pour tester un peu l’effet de ces changements, j’ai installé ArchLinux qui a déjà la glibc-2.28, avec PostgreSQL 10.5, et comparé les résultats de quelques requêtes avec ceux obtenus sous Debian 9 (“stretch”), qui est en glibc-2.24.

Je m’attendais bien à quelques changements, mais pas aussi étendus. Car il s’avère que des tests simples sur des chaînes avec uniquement des caractères ASCII de base montrent tout de suite des différences importantes.

Par exemple, avec la locale en_US.UTF-8:

Debian stretch (glibc 2.24)

=# select version();
                                                             version                                                              
----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.5 (Debian 10.5-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)

=# show lc_collate ;
 lc_collate  
-------------
 en_US.UTF-8
(1 row)

=# SELECT * FROM (values ('a'), ('$a'), ('a$'), ('b'), ('$b'), ('b$'), ('A'), ('B'))
   AS l(x) ORDER BY x ;
 x  
----
 a
 $a
 a$
 A
 b
 $b
 b$
 B
(6 rows)

ArchLinux (glibc 2.28):

=# select version();
                                   version                                   
-----------------------------------------------------------------------------
 PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.2.0, 64-bit
(1 row)

=# show lc_collate;
 lc_collate  
-------------
 en_US.UTF-8
(1 row)

=# SELECT * FROM (values ('a'), ('$a'), ('a$'), ('b'), ('$b'), ('b$'), ('A'), ('B'))
   AS l(x) ORDER BY x ;
 x  
----
 $a
 $b
 a
 A
 a$
 b
 B
 b$
(6 rows)

Ces changements ne sont pas limités aux locales UTF-8. Les différences ci-dessus s’appliquent aussi à l’encodage LATIN9 avec lc_collate = 'fr_FR.iso885915@euro', par exemple.

Et voici une requête encore plus simple qui montre aussi des résultats de tri de chaînes différents entre versions:

Debian stretch (glibc 2.24)

=# SELECT * FROM (values ('"0102"'), ('0102')) AS x(x)
   ORDER BY x;
   x    
--------
 0102
 "0102"
(2 rows)

ArchLinux (glibc 2.28):

=# SELECT * FROM (values ('"0102"'), ('0102')) AS x(x)
   ORDER BY x;
   x    
--------
 "0102"
 0102
(2 rows)

J’ai pris l’habitude d’utiliser la requête ci-dessus pour illustrer les différences entre FreeBSD et Linux/glibc mais alors que la collation en_US dans FreeBSD 11 triait jusque-là ces chaînes à l’opposé de glibc, maintenant il s’avère que la nouvelle glibc donne un résultat identique aux locales et libc de FreeBSD…

Naturellement la plupart des utilisateurs ne changent pas de version de libc de leur propre initiative, mais dans le cadre d’une montée de version du système. Si Postgres est mis à jour au passage avec un dump/reload, les index seront recréés avec les nouvelles règles. Sinon un REINDEX global de toutes les bases devrait être envisagé, ou a minima des index concernés. A noter que pg_upgrade pour cette situation ne réindexe pas automatiquement, et ne signale pas non plus l’obligation de le faire.

A la date de ce billet, les seules distributions Linux ayant déjà la glibc-2.28 doivent être les “bleeding edge” comme ArchLinux. Pour Fedora c’est prévu au 30 octobre 2018; Debian a actuellement la 2.27-5 dans testing, et Ubuntu “cosmic” (18.10) a la 2.27-3.

Si vous êtes utilisateur de Postgres sous Linux, ne manquez pas de vérifier si vos bases sont concernées par ces mises à jour de locales, et si oui, regardez bien quand vos systèmes passent à la glibc 2.28 pour prévoir une phase de réindexation pour éviter tout risque de corruption de données!

Pour savoir quelles collations chaque base utilise par défaut:

 SELECT datname, datcollate FROM pg_database;

Pour savoir quelles collations sont plus spécifiquement utilisées dans les index (à faire tourner sur chaque base):

SELECT distinct collname FROM pg_collation JOIN
  (SELECT regexp_split_to_table(n::text,' ')::oid  AS o
    FROM (SELECT distinct indcollation AS n FROM pg_index) AS a) AS b on o=oid
 -- WHERE collprovider <> 'i'
;

Avec Postgres 10 ou plus récent, on peut décommenter la dernière ligne pour éviter les collations ICU, qui ne sont pas concernées par la mise à jour de la glibc. Les locales C et POSIX ne sont également pas concernées étant donné qu’elles comparent au niveau de l’octet, sans règle linguistique.

Depuis la version 10, Postgres peut être configuré avec ICU, la bibliothèque de référence pour Unicode, afin d’utiliser ses collations (règles de tri et de comparaison de chaînes de caractères) via des clauses COLLATE.

Pour ce faire, les collations ICU pour la plupart des langues/pays sont automatiquement créées au moment d’initdb (on les trouvera dans pg_catalog.pg_collation), et d’autres peuvent être ajoutées plus tard avec CREATE COLLATION.

Au-delà du support des collations, ICU fournit d’autres services relatifs aux locales et à la gestion du texte multilingue, suivant les recommandations d’Unicode.

A partir du moment où nos binaires Postgres sont liées à ICU (la plupart le sont parce que les installeurs les plus importants comme Apt, Rpm ou Rdb l’incluent d’office), pourquoi ne pas chercher à bénéficier de tous ces services à travers SQL?

C’est le but de icu_ext, une extension en C implémentant des interfaces SQL aux fonctions d’ICU. Actuellement elle comprend une vingtaine de fonctions permettant d’inspecter les locales et les collations, de découper du texte, de comparer et trier les chaînes de caractères, d’évaluer l’utilisation trompeuse de caractères Unicode (spoofing), d’épeler des nombres et de faire des conversions entre systèmes d’écriture (translitération).

Avant de voir les fonctions de comparaison et tri, faisons un point sur ce qu’amène l’intégration d’ICU dans Postgres.

Les bénéfices d’ICU par rapport aux collations du système

• Versionnage: pg_collation.collversion contient le numéro de version de chaque collation au moment de sa création, et si au cours de l’exécution elle ne correspond plus à celle de la bibliothèque ICU (typiquement après un upgrade), un avertissement est émis, invitant l’utilisateur à reconstuire les index potentiellement affectés et à enregistrer la nouvelle version de la collation dans le catalogue.

• Cohérence inter-systèmes: une même collation ICU trie de la même façon entre systèmes d’exploitation différents, ce qui n’est pas le cas avec les “libc” (bibliothèque C de base sur laquelle s’appuie tous les programmes du système). Par example avec un même lc_collate à en_US.UTF-8 a un comportement différent entre Linux and FreeBSD: des couples de chaînes de caractères aussi simples que 0102 and "0102" se retrouvent ordonnés de manières opposées. C’est une des raisons pour lesquelles il ne faut pas répliquer une instance sur un serveur secondaire avec un système d’exploitation différent du primaire.

• Vitesse d’indexation: Postgres utilise les clefs abrégées (abbreviated keys) quand c’est possible (également appelées sort keys dans la terminologie ICU), parce qu’elles peuvent vraiment accélérer l’indexation. Mais comme le support de cette fonctionnalité via libc (strxfrm) s’est avéré buggé dans plusieurs systèmes dont Linux pour certaines locales, elle est seulement activée pour les collations ICU.

• Comparaison de chaînes paramétrique: Avec la libc, il y a typiquement une association figée entre la locale et la collation: par exemple fr_CA.UTF-8 compare les chaînes avec les règles linguistiques du français tel qu’écrit au Canada, mais sans possibilité de personnalisation ou de contrôle supplémentaire. Avec ICU, les collations acceptent un bon nombre de paramètre qui offre des possibilités au-delà de la spécification de la langue et du pays, comme montré dans la démo online de collationnement ICU, ou dans le billet de Peter Eisentraut “More robust collations with ICU support in PostgreSQL 10” annonçant l’intégration ICU l’année dernière, ou encore dans “What users can do with custom ICU collations in Postgres 10” (fil de discussion dans pgsql-hackers).

Ce que Postgres ne peut pas (encore) faire avec les collations ICU

Malheureusement un problème empêche d’utiliser les comparaisons avancées à leur plein potentiel: l’infrastructure actuelle des opérateurs de comparaison dans Postgres ne peut pas gérer le fait que des chaînes soient égales alors qu’elles ne sont pas équivalentes en comparaison octet par octet. Pour s’assurer que cette contrainte est bien respectée, dès que des chaînes sont considérées comme égales par un comparateur linguistique (avec une fonction de la famille de strcoll), Postgres cherche à les départager par comparaison binaire, via ce qu’on va appeler en anglais le strcmp tie-breaker; le résultat généré par le comparateur ICU (ou celui de libc d’ailleurs) est alors éliminé en faveur du résultat de la comparaison binaire.

Par exemple, on peut créer cette collation:

    CREATE COLLATION "fr-ci" (
       locale = 'fr-u-ks-level1', /* ou 'fr@colStrength=primary' */
       provider = 'icu'
    );

ks-level1 ici signifie primary collation strength.

Il faut savoir que cette syntaxe avec les paramètres au format BCP-47 ne fonctionne pas (sans pour autant émettre d’erreur) avec ICU 53 ou plus ancien. Lorsqu’on n’est pas sûr de la syntaxe d’une collation, elle peut être passée à icu_collation_attributes() pour vérifier comment ICU l’analyse, comme montré par un exemple un peu plus loin dans ce billet.

Quoiqu’il en soit, il y cinq niveaux de force de comparaison, le niveau primaire ne considérant que les caractères de base, c’est-à-dire qu’il ignore les différences engendrées par les accents et la casse (majucule ou minuscule).

Le principe de départager les chaînes égales via une comparaison binaire fait que l’égalité suivante, par exemple, ne va pas être satisfaite, contrairement à ce qu’on pourrait attendre:

    =# SELECT 'Eté' = 'été' COLLATE "fr-ci" as equality;
     equality
    ----------
     f

Peut-être (espérons) que dans le futur, Postgres pourra faire fonctionner complètement ces collations indépendante de la casse et autres, mais en attendant, il est possible de contourner ce problème avec des fonctions de icu_ext. Voyons comment.

Comparer des chaînes avec des fonctions de icu_ext

La fontion principale est: icu_compare(string1 text, string2 text [, collator text]).

Elle renvoie le résultat de ucol_strcoll[UTF8](), comparant string1 et string2 avec collator qui est la collation ICU. C’est un entier signé, négatif si string1 < string2, zéro if string = string2, et positif si string1 > string2.

Quand le 3ème argument collator est présent, ce n’est pas le nom d’une collation de la base de données déclarée avec CREATE COLLATION, mais la valeur qui serait passée dans le paramètre locale ou lc_collate, si on devait instancier cette collation. En d’autres termes, c’est un locale ID au sens d’ICU, indépendamment de Postgres (oui, ICU utilise le terme “ID” pour désigner une chaîne de caractères dont le contenu est plus ou moins construit par aggrégation de paramètres).

Quand l’argument collator n’est pas spécifié, c’est la collation associée à string1 et string2 qui est utilisée pour la comparaison. Ca doit être une collation ICU et ça doit être la même pour les deux arguments, ou la fonction sortira une erreur. Cette forme avec deux arguments est significativement plus rapide du fait que Postgres garde ses collations ouvertes (au sens de ucol_open()/ucol_close()) pour la durée de la session, tandis que l’autre forme avec l’argument collator explicite ouvre et ferme la collation ICU à chaque appel.

Pour revenir à l’exemple précédent, cette fois on peut constater l’égalité des chaînes de caractère sous le régime de l’insensibilité à la casse et aux accents:

=# SELECT icu_compare('Eté', 'été', 'fr-u-ks-level1');
 icu_compare 
 -------------
      0

Les comparaisons sensibles à la casse mais insensibles aux accents sont aussi possibles:

=# SELECT icu_compare('abécédaire','abecedaire','fr-u-ks-level1-kc'),
          icu_compare('Abécédaire','abecedaire','fr-u-ks-level1-kc');
  icu_compare | icu_compare 
 -------------+-------------
            0 |           1

Autre exemple, cette fois avec une collation Postgres implicite:

=# CREATE COLLATION mycoll (locale='fr-u-ks-level1', provider='icu');
CREATE COLLATION

=# CREATE TABLE books (id int, title text COLLATE "mycoll");
CREATE TABLE

=# insert into books values(1, 'C''est l''été');
INSERT 0 1

=# select id,title from books where icu_compare (title, 'c''est l''ete') = 0;

 id |    title    
----+-------------
  1 | C'est l'été

La gestion des caractères diacritiques combinatoires

Avec Unicode, les lettres accentuées peuvent être écrites sous une forme composée ou décomposée, cette dernière signifiant qu’il y a une lettre sans accent suivi d’un caractère d’accentuation faisant partie du bloc des diacritiques combinatoires.

Les deux formes avec décomposition, NFD or NFKD ne sont pas fréquemment utilisées dans les documents UTF-8, mais elles sont parfaitement valides et acceptées par Postgres. Sur le plan sémantique, 'à' est supposément équivalent à E'a\u0300'. En tout cas, le collationnement ICU semble les considérer comme égaux, y compris sous le niveau de comparaison le plus strict:

=# CREATE COLLATION "en-identic" (provider='icu', locale='en-u-ks-identic');
CREATE COLLATION

=#  SELECT icu_compare('à', E'a\u0300', 'en-u-ks-identic'),
    'à' = E'a\u0300' COLLATE "en-identic" AS "equal_op";
 icu_compare | equal_op 
-------------+----------
           0 | f

(à nouveau, l’opérateur d’égalité de Postgres donne un résultat différent à cause de la comparaison binaire qui départage les deux arguments. C’est précisement pour contourner ça qu’on utilise une fonction au lieu de l’opérateur d’égalité).

Par ailleurs, les caractères combinatoires ne concernent pas seulement les accents, certains servent aussi à réaliser des effets sur le texte comme l’effet barré ou le soulignement. Voyons un exemple dans psql, tel qu’affiché dans un terminal gnome.

La requête de la capture d’écran ci-dessous prend le texte litéral ‘Hello’, insère les caractères combinatoires de U+0330 à U+0338 après chaque caractère, renvoie la chaîne résultante, ainsi que les résultats des comparaisons linguisitique primaire et secondaire avec le texte de départ.

En général, les fonctions ICU prennent en compte les caractères combinatoires à chaque fois que ça a du sens, alors que les fonctions de Postgres hors ICU (celles des expressions régulières par exemple) considèrent que le caractère combinatoire est un point de code comme un autre.

Tri et regroupements

Les clauses ORDER BY et GROUP BY ne sont pas conçues pour fonctionner avec des fonctions à deux arguments, mais avec des fonctions à un seul argument qui le transforment en quelque chose d’autre.

Pour trier ou regrouper des résultats d’après les règles linguistiques avancées, icu_ext expose une fonction qui convertit une chaîne en une clé de tri:

 function icu_sort_key(string text [, icu_collation text]) returns bytea

C’est la même clé de tri que celle utilisée implicitement par Postgres pour créer des index impliquant des collations ICU.

La promesse de la clé de tri est que, si icu_compare(s1, s2, collator) renvoie X, alors la comparaison (plus rapide) au niveau octet entre icu_sort_key(s1, collator) et icu_sort_key(s2, collator) renvoie X également.

La documentation ICU prévient que le calcul d’une clé de tri est susceptible d’être nettement plus lent que de faire une seule comparaison avec la même collation. Mais dans le contexte d’un ORDER BY sur des requêtes et pour autant que mes tests soient représentatifs, c’est plutôt très rapide.

Du reste, en comparant les performances de ORDER BY field COLLATE "icu-coll" par rapport à ORDER BY icu_sort_key(field, 'collation'), la plus grande part de la différence est causée par le fait qu’ icu_sort_key doive analyser la spécification de la collation à chaque appel, et cette différence semble d’autant plus grande que la spécification est complexe.

Tout comme pour icu_compare(), pour bénéficier du fait que Postgres garde ouvertes les collations ICU pour la durée de la session, il est recommandé d’utiliser la forme à un seul argument, qui s’appuie sur sa collation, par exemple avec notre “fr-ci” définie précédemment:

  =# SELECT icu_sort_key ('Eté' COLLATE "fr-ci")

Toujours sur les performances, voici une comparaison d’EXPLAIN ANALYZE pour trier 6,6 million de mots courts (de 13 caractères en moyenne) avec icu_sort_key versus ORDER BY directement sur le champ:

ml=# explain analyze select wordtext from words order by icu_sort_key(wordtext collate "frci");

                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Gather Merge  (cost=371515.53..1015224.24 rows=5517118 width=46) (actual time=3289.004..5845.748 rows=6621524 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Sort  (cost=370515.51..377411.90 rows=2758559 width=46) (actual time=3274.132..3581.209 rows=2207175 loops=3)
         Sort Key: (icu_sort_key((wordtext)::text))
         Sort Method: quicksort  Memory: 229038kB
         ->  Parallel Seq Scan on words  (cost=0.00..75411.99 rows=2758559 width=46) (actual time=13.361..1877.528 rows=2207175 loops=3)
 Planning time: 0.105 ms
 Execution time: 6165.902 ms

ml=# explain analyze select wordtext from words order by wordtext collate "frci";
                                                               QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------------------------------------
 Gather Merge  (cost=553195.63..1196904.34 rows=5517118 width=132) (actual time=2490.891..6231.454 rows=6621524 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Sort  (cost=552195.61..559092.01 rows=2758559 width=132) (actual time=2485.254..2784.511 rows=2207175 loops=3)
         Sort Key: wordtext COLLATE frci
         Sort Method: quicksort  Memory: 231433kB
         ->  Parallel Seq Scan on words  (cost=0.00..68515.59 rows=2758559 width=132) (actual time=0.023..275.519 rows=2207175 loops=3)
 Planning time: 0.701 ms
 Execution time: 6565.687 ms

On peut voir ici qu’il n’y a pas de dégradation notable des performances lors de l’appel de icu_sort_key explicitement. En fait, dans cet exemple c’est même un peu plus rapide, sans que je sache vraiment pourquoi.

GROUP BY et DISTINCT ON peuvent aussi utiliser des clés de tri:

=# select count(distinct title) from books;
 count 
-------
  2402

=# select count(distinct icu_sort_key(title)) from books;
 count
-------
  2360

Utiliser des clés de tri dans les index

La position post-tri ou l’unicité d’un texte sous une certaine collation équivaut à la position post-tri ou l’unicité de la clé binaire correspondante dans cette collation. Par conséquent il est possible de créer un index, y compris pour appliquer une contrainte unique, sur icu_sort_key(column) ou icu_sort_key(column, collator) plutôt que simplement column, pour contourner le problème de la règle Postgres “pas d’égalité si la représentation binaire est différente”.

En reprenant l’exemple précédent avec la table books, on pourrait faire:

 =# CREATE INDEX ON books (icu_sort_key(title));

pour qu’ensuite cet index soit utilisé pour des recherches exactes avec une requête comme suit:

=# SELECT title FROM books WHERE
      icu_sort_key(title) = icu_sort_key('cortege' collate "mycoll");
  title  
---------
 Cortège
 CORTÈGE

Juste pour tester que l’index est effectivement utilisé:

=# explain select title from books where icu_sort_key(title)=icu_sort_key('cortege' collate "mycoll");
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Bitmap Heap Scan on books  (cost=4.30..10.64 rows=2 width=29)
   Recheck Cond: (icu_sort_key(title) = '\x2d454b4f313531'::bytea)
   ->  Bitmap Index Scan on books_icu_sort_key_idx  (cost=0.00..4.29 rows=2 width=0)
         Index Cond: (icu_sort_key(title) = '\x2d454b4f313531'::bytea)

Inspecter des collations

Comme mentionné plus haut, quand on se réfère à une collation par son identifiant ICU, les anciennes versions d’ICU ne comprennent pas la syntaxe plus moderne des tags BCP-47, ce qui ne se traduit pas nécessairement par une erreur, ils sont simplement ignorés.

Pour s’assurer qu’une collation est correctement nommée ou qu’elle a les caractéristiques attendues, on peut contrôler la sortie de icu_collation_attributes(). Cette fonction prend un nom de collation ICU en entrée, récupère ses propriétés et les renvoie en tant qu’ensemble de couples (attribute, value) comprenant son nom “affichable” (displayname, probablement l’attribut le plus intéressant), plus les tags kn / kb / kk / ka / ks / kf / kc correspondant à ses caractéristiques, et enfin la version de la collation.

Exemple:

postgres=# select * from icu_collation_attributes('en-u-ks-identic');
  attribute  |              value              
-------------+---------------------------------
 displayname | anglais (colstrength=identical)
 kn          | false
 kb          | false
 kk          | false
 ka          | noignore
 ks          | identic
 kf          | false
 kc          | false
 version     | 153.80
(9 rows)

-- Ci-dessus le displayname est en français, mais
-- on pourait le demander par exemple en japonais:

postgres=# select icu_set_default_locale('ja');
 icu_set_default_locale 
------------------------
 ja
(1 row)

-- à noter le changement dans displayname
postgres=# select * from icu_collation_attributes('en-u-ks-identic');
  attribute  |            value             
-------------+------------------------------
 displayname | 英語 (colstrength=identical)
 kn          | false
 kb          | false
 kk          | false
 ka          | noignore
 ks          | identic
 kf          | false
 kc          | false
 version     | 153.80
(9 rows)

Autres fonctions

Au-delà des comparaisons de chaînes et des clés de tri, icu_ext implémente des accesseur SQL à d’autres fonctionnalités d’ICU: (voir le README.md des sources pour les exemples d’appels aux fonctions):

• icu_{character,word,line,sentence}_boundaries
  Découpe un texte selon ses constituants et renvoie les morceaux en type SETOF text. En gros c’est regexp_split_to_table(string, regexp) en mieux dans le sens où sont utilisées les règles linguistiques recommandées par la standard Unicode, au lieu de simplement repérer les séparateurs sur la base d’expressions rationnelles.

• icu_char_name
  Renvoie le nom Unicode de tout caractère (fonctionne avec les 130K+ du jeu complet).

• icu_confusable_strings_check and icu_spoof_check
  Indique si un couple de chaînes est similaire, visuellement et si une chaîne comprend des caractères qui prêtent à confusion (spoofing).

• icu_locales_list
  Sort la liste des toutes les locales avec les langues et pays associés, exprimés dans la langue en cours. Accessoirement, ça permet d’obtenir les noms de pays et de langues traduits en plein de langues (utiliser icu_set_default_locale() pour changer de langue).

• icu_number_spellout
  Exprime un nombre en version textuelle dans la langue en cours.

• icu_transforms_list et icu_transform
  Applique des translitération (conversions entre écritures) et autres transformations complexes de texte. Il y a plus de 600 transformations de base listées par icu_transforms_list et elles peuvent combinées ensemble et avec des filtres. Voir la démo en ligne de ce service.

D’autres fonctions devraient être ajoutées dans le futur à icu_ext, ainsi que d’autres exemples d’utilisation des fonctions existantes. En attendant n’hésitez pas à proposer des changements sur github pour faire évoluer ces fonctions, ou exposer d’autres services ICU en SQL, ou encore exposer différemment ceux qui le sont déjà, ou bien entendu signaler des bugs…

Une nouvelle version de pg_stat_kcache est disponible, ajoutant la compatibilité avec Windows et d’autres plateformes, ainsi que l’ajout de nouveaux compteurs.

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.

PostgreSQL 10 apportait enfin un support natif du partitionnement. Cependant, il souffrait de plusieurs limitations qui ne le rendaient pas si séduisant que cela. La version 11, qui va bientôt arriver en version bêta, corrige un certain nombre de ces défauts. Mais pas tous. Commençons par voir les différents changements. Ce premier article nous permet d'explorer l'élimination dynamique des partitions.

Commençons par nous créer un premier jeu d'essai :

CREATE TABLE orders (
 num_order    INTEGER NOT NULL,
 date_order   DATE NOT NULL,
) PARTITION BY RANGE (date_order);

CREATE TABLE orders_201804 PARTITION OF orders
    FOR VALUES FROM ('2018-04-01') TO ('2018-05-01');
CREATE TABLE orders_201805 PARTITION OF orders
    FOR VALUES FROM ('2018-05-01') TO ('2018-06-01');

Ajoutons quelques données :

INSERT INTO orders VALUES
(1, '2018-04-22'),
(2, '2018-05-01'),
(3, '2018-05-11');

On termine la création du jeu d'essai en mettant à jour les statistiques pour l'optimiseur :

ANALYZE;

Vérifions déjà que l'optimiseur est toujours capable de retirer les partitions inutiles à la planification. Ici le prédicat est connu avant l'exécution, l'optimiseur peut donc lire uniquement la partition du mois de mai :

SELECT *
  FROM orders
 WHERE date_order = '2018-05-11';

On vérifie que la partition du mois d'avril est bien éliminée à la planification en observant que le plan obtenu ne contient pas aucune lecture de cette partition :

 Append (actual rows=1 loops=1)
   ->  Seq Scan on orders_201805 (actual rows=1 loops=1)
         Filter: (date_order = '2018-05-11'::date)
         Rows Removed by Filter: 1

Une forme de construction souvent vue sur le terrain utilise la fonction to_date pour calculer un prédicat sur une date :

SELECT *
  FROM orders
 WHERE date_order = to_date('2018-05-11', 'YYYY-MM-DD');

Avec l'élimination dynamique des partitions, on pourrait penser que cette requête en bénéficiera bien. Mais ce n'est pas le cas, le prédicat est utilisé comme clause de filtrage à la lecture et PostgreSQL 11 ne sait pas l'utiliser directement pour exclure dynamiquement la partition d'avril. La lecture de cette partition est bien réalisée et ne ramène aucune ligne :

 Append (actual rows=1 loops=1)
   ->  Seq Scan on orders_201804 (actual rows=0 loops=1)
         Filter: (date_order = to_date('2018-05-11'::text, 'YYYY-MM-DD'::text))
         Rows Removed by Filter: 2
   ->  Seq Scan on orders_201805 (actual rows=1 loops=1)
         Filter: (date_order = to_date('2018-05-11'::text, 'YYYY-MM-DD'::text))
         Rows Removed by Filter: 1

On peut forcer la création d'un InitPlan, donc en utilisant une sous-requête pour calculer le prédicat :

SELECT *
  FROM orders
 WHERE date_order = (SELECT to_date('2018-05-11', 'YYYY-MM-DD'));

Le plan d'exécution obtenu est un peu différent : l'InitPlan correspondant à notre sous-requête apparaît. Mais ce qui nous intéresse le plus concerne la lecture de la partition d'avril. Elle apparaît dans le plan car elle n'est pas exclue à la planification, mais elle n'est pas exécutée car exclue à l'exécution.

 Append (actual rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Result (actual rows=1 loops=1)
   ->  Seq Scan on orders_201804 (never executed)
         Filter: (date_order = $0)
   ->  Seq Scan on orders_201805 (actual rows=1 loops=1)
         Filter: (date_order = $0)
         Rows Removed by Filter: 1

Ce mécanisme est également fonctionnel si l'on avait utilisé une sous-requête sans la fonction to_date :

SELECT *
  FROM orders
 WHERE date_order = (SELECT date '2018-05-11');

On peut aussi vérifier que le mécanisme fonctionne avec une requête préparée :

PREPARE q0 (date) AS
SELECT *
  FROM orders
 WHERE date_order = $1;

On exécutera au moins 6 fois la requête suivante. Les 5 premières exécutions vont servir à PostgreSQL à se fixer sur un plan générique :

EXECUTE q0 ('2018-05-11');

Le plan d'exécution de la requête montre que seule la partition du mois de mai est accédée et nous montre aussi qu'un sous-plan a été éliminé, il s'agit de la lecture sur la partition d'avril :

 Append (actual rows=1 loops=1)
   Subplans Removed: 1
   ->  Seq Scan on orders_201805 (actual rows=1 loops=1)
         Filter: (date_order = $1)
         Rows Removed by Filter: 1

On pourra aussi s'en assurer avec la requête préparée suivante :

PREPARE q1 AS
SELECT *
  FROM orders
 WHERE date_order IN ($1, $2, $3);

Et l'execute suivant, où l'on fera varier les paramètres pour tantôt viser les deux partitions, tantôt une seule :

EXECUTE q1 ('2018-05-11', '2018-05-11', '2018-05-10');

Le mécanisme d'élimination dynamique est donc fonctionnel pour les requêtes préparées.

Ajoutons une table pour pouvoir réaliser une jointure :

CREATE TABLE bills (
  num_bill SERIAL NOT NULL PRIMARY KEY,
  num_order INTEGER NOT NULL,
  date_order DATE NOT NULL
);

INSERT INTO bills (num_order, date_order) VALUES (1, '2018-04-22');

On met également à jour les statistiques pour l'optimiseur, surtout pour que l'optimiseur sache qu'une Nested Loop sera plus adaptée pour la jointure que l'on va faire :

ANALYZE bills;

La jointure suivante ne concernera donc que des données du mois d'avril, du fait des données présentes dans la table bills :

SELECT *
  FROM bills b
  JOIN orders o
    ON (    b.num_order = o.num_order
        AND b.date_order = o.date_order);

La jointure est donc bien réalisée avec une Nested Loop. L'optimiseur n'a fait aucune élimination de partition, il n'a aucun élément pour y parvenir. En revanche, l'étage d'exécution a tout simplement éliminé la partition de mai de la jointure :

 Nested Loop (actual rows=1 loops=1)
   ->  Seq Scan on bills b (actual rows=1 loops=1)
   ->  Append (actual rows=1 loops=1)
         ->  Seq Scan on orders_201804 o (actual rows=1 loops=1)
               Filter: ((b.num_order = num_order) AND (b.date_order = date_order))
               Rows Removed by Filter: 1
         ->  Seq Scan on orders_201805 o_1 (never executed)
               Filter: ((b.num_order = num_order) AND (b.date_order = date_order))

Que se passe-t-il si l'on force l'optimiseur à ne pas utiliser une Nested Loop :

SET enable_nestloop = off;

explain (analyze, costs off, timing off)
SELECT *
  FROM bills b
  JOIN orders o
    ON (    b.num_order = o.num_order
        AND b.date_order = o.date_order);

Le plan d'exécution montre que l'optimiseur préfère une jointure par hachage. Mais les deux partitions sont lues dans ce cas. Le mécanisme d'élimination dynamique de partition de PostgreSQL 11 n'est effectif que sur les Nested Loop (ça ne marchera pas non plus avec un Merge Join) :

 Hash Join (actual rows=1 loops=1)
   Hash Cond: ((o.num_order = b.num_order) AND (o.date_order = b.date_order))
   ->  Append (actual rows=4 loops=1)
         ->  Seq Scan on orders_201804 o (actual rows=2 loops=1)
         ->  Seq Scan on orders_201805 o_1 (actual rows=2 loops=1)
   ->  Hash (actual rows=1 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on bills b (actual rows=1 loops=1)

Le mécanisme d'élimination dynamique de partitions est donc fonctionnel dans un certain nombre de cas, mais reste encore perfectible.

Tous les plans d'exécution de cet article ont été obtenus avec EXPLAIN (analyze, costs off, timing off).

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.

PostgreSQL 10 vient tout juste de sortir et apporte une nouveauté significative concernant la supervision d'une instance. Grâce au travail de Dave Page, PostgreSQL 10 amène un rôle système pg_monitor qui permet d'accéder à toutes les fonctions de supervision sans nécessiter d'être super-utilisateur.

La sonde check_pgactivity permet naturellement de tirer partie de cette nouvelle possibilité avec votre environnement de supervision Nagios ou compatible.

check_pgactivity 2.3 beta 1

La sonde de supervision check_pgactivity arrive en version 2.3 beta 1 et intègre le support de PostgreSQL 10.

En plus de cela, le service backend_status accepte maintenant des seuils exprimés avec des unités de temps, par exemple pour détecter les transactions en attente d'un verrou depuis plus d'un certain temps. Un bug assez ancien a par ailleurs été corrigé dans ce même service.

Le service sequences_exhausted a été corrigé de manière à accepter les séquences rattachées à une colonne d'un type non-numérique.

Enfin, pour les personnes souhaitant contribuer au projet, une nouvelle documentation vous permettra de mettre le pied à l'étrier.

Changements majeurs dans PostgreSQL 10

Dans toutes les versions précédentes, il fallait nécessairement être super-utilisateur pour pouvoir superviser une instance PostgreSQL.

Dave Page a répondu à cette problématique en proposant un rôle système pg_monitor qui permet de bénéficier des droits pour accéder aux vues pg_stat_* et utiliser les fonctions pg_database_size() et pg_tablespace_size(). Le commit 25fff40798fc4ac11a241bfd9ab0c45c085e2212 vous permettra d'avoir de plus amples explications.

Le second changement important concerne le renommage de XLOG en WAL. Ainsi, le répertoire pg_xlog, qui contient les journaux de transaction, est renommé en pg_wal. Toutes les fonctions contenant le terme xlog sont renommées de la même façon, comme pg_current_xlog_location() qui devient pg_current_wal_lsn(). À noter que location devient lsn, mais je ne détaille pas ce changement. Etc.

Enfin, un dernier changement important concerne l'arrivée de la fonction pg_ls_waldir() pour pouvoir lister le contenu du répertoire pg_wal. Pour ce faire, nous utilisions auparavant la fonction pg_ls_dir().

Pour en savoir plus sur tous ces changements, je vous invite à consulter les notes de version de PostgreSQL 10.

Mise en œuvre de la supervision

Création d'un utilisateur de supervision

Commençons par créer un rôle monitor qui ne dispose pas de privilèges particuliers, mais qui est membre de pg_monitor :

CREATE ROLE monitor LOGIN IN ROLE pg_monitor;

Pour permettre aux services btree_bloat et table_bloat de fonctionner, il faut aussi permettre au rôle monitor d'effectuer un SELECT sur pg_statistic, dans toutes les bases de données de l'instance :

GRANT SELECT ON pg_statistic TO monitor;

À noter que vous devrez configurer l'authentification par vous-même, en affectant un mot de passe ou non, à votre guise.

Supervision d'un service

Le service wal_files se connecte à une instance en utilisant le rôle monitor créé plus haut. Il permet de superviser le nombre de journaux de transaction présents dans le répertoire $PGDATA/pg_wal. Nous utilisons la sortie human pour obtenir un résultat lisible par le commun des mortels :

$ ./check_pgactivity -h localhost -p 5432 -U monitor -F human -s wal_files
Service        : POSTGRES_WAL_FILES
Returns        : 0 (OK)
Message        : 45 WAL files
Perfdata       : total_wal=45
Perfdata       : recycled_wal=44
Perfdata       : tli=1
Perfdata       : written_wal=1
Perfdata       : kept_wal=0
Perfdata       : wal_rate=74.45Bps

Si vous utilisez la sonde check_pgactivity avec Nagios, vous choisirez évidemment le format de sortie nagios, qui est d'ailleurs le format de sortie par défaut.

Incompatibilités

Comme vu plus haut, les services btree_bloat et table_bloat doivent avoir accès au catalogue pg_statistic. Sans cet accès, il n'est pas possible de calculer une estimation de la fragmentation des index et des tables le plus rapidement possible dans toutes les situations. Nous avions rencontré un gros problème de performance sur une base disposant de plusieurs milliers de tables lorsque nous utilisions encore la vue pg_stats.

Ainsi, nous devons donc donner le privilège de SELECT sur pg_statistic à notre rôle monitor (ou à pg_monitor si l'on souhaite être moins restrictif) :

GRANT SELECT ON pg_statistic TO monitor;

UPDATE Le service temp_files, qui permet de superviser le volume de fichiers temporaires créés, nécessite les privilèges de super-utilisateur car il utilise la fonction pg_ls_dir(). La sonde va être corrigée ultérieurement pour qu'elle fonctionne avec PostgreSQL 10, mais il ne sera plus possible de superviser les fichiers temporaires créés en "live".

À vous !

N'hésitez pas à nous remonter les problèmes que vous avez pu rencontrer avec cette dernière version check_pgactivity. Si nous n'avons aucun retour négatif, la version 2.3 stable va suivre d'ici quelques jours.

Téléchargement

Rendez-vous sur github :

• version 2.3 beta 1 : https://github.com/OPMDG/check_pgac...
• site et documentation du projet : https://github.com/OPMDG/check_pgac...

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 ?

L'AFUP organise une soirée intitulée les bases de données relationnelles avec PHP. Je viendrai présenter PostgreSQL, son développement et sa communauté, ses principales fonctionnalités et quelques retours d'expérience.

Si vous ne connaissez pas PostgreSQL, c'est le moment de venir le découvrir à cette occasion, près de la Défense, à Paris.

Pour vous inscrire, ça se passe ici :

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)

Lingora, société spécialisée dans le support et la maintenance de solutions libres et la société américaine EnterpriseDB, éditrice d'une version propriétaire de PostgreSQL apportant une compatibilité avec Oracle, se sont réunies dans le cadre d'un partenariat pour proposer des licences de produits et surtout du support autour de PostgreSQL et l'offre propriétaire d'EntrepriseDB.

Après une brève introduction, Alexandre Zapolsky, PDG de Linagora, a passé la main à Andy Astor, CEO d'EnterpriseDB pour présenter la suite logicielle Postgres Plus et Postgres Plus Advanced Server.

Le SGBD PostgreSQL, ici en version 8.3, reste bien évidemment à la base de la pile Postgres Plus. Celle-ci se propose en premier lieu d'intégrer des outils et des extensions libres ou préalablement libérées par EnterpriseDB, évidemment sous la forme de paquets binaires ; ainsi, l'appropriation de PostgreSQL par un néophyte sera largement facilitée.

Parmi les fonctionnalités ou produits proposés pré-packagés, on trouvera :

• tous les connecteurs vers PostgreSQL : JDBC, ODBC, .Net, etc.
• l'extension de géomatique PostGIS ;
• l'outil de réplication Slony-I ;
• la contribution Pgcrypto se voit
• un outil de migration de MySQL vers PostgreSQL ;
• un gestionnaire de pool de connexion et un autre gestionnaire de cache mémoire distribué ;
• Grid SQL, récemment acquis par EnterpriseDB, il s'agit d'un produit très adapté à la BI et au datawarehouse. Il permet de répartir les données sur les serveurs composant une grappe afin ensuite de paralléliser les requêtes sur tous les serveurs de celle-ci, augmentant ainsi très avantageusement les temps de réponse ;
• le débugger PL/pgSQL, libéré courant 2007 par EnterpriseDB ;
• ainsi que PgAdmin-III, Postgres Studio, la configuration automatique d'une instance, etc ;

Auparavant, la société d'Andy Astor proposait - et d'ailleurs propose toujours - EnterpriseDB Advanced Server qui était une évolution propriétaire de PostgreSQL, connue pour sa compatibilité avec Oracle, ce produit trouve sa continuation dans Postgres Plus Advanced Server. Bien évidemment, la différence ne s'arrête pas là, l'édition Advanced Server se trouve complétée par des fonctionnalités d'audit et de tuning avancées (DynaTune, Hints pour l'optimiseur), des outils de migration depuis d'autres bases de données, un outil de réplication propre à EDB. Enfin, et voilà qui devrait rassurer les décideurs, EnterpriseDB offre des garanties à l'instar de n'importe quel autre éditeur de bases de données, à condition évidemment que l'on se soit acquitté de la licence pour le produit.

Je souhaite grandement remercier Linagora pour m'avoir permis de rencontrer et de discuter longuement avec Andy et Jim ! Je remercie enfin Andy et Jim pour avoir eu la gentillesse de subir mon mauvais anglais sans broncher et pour les intéressantes discussions que nous avons eu.

La version majeure 8.3 de PostgreSQL est enfin sortie ! Le développement a duré bien plus longtemps que prévu, mais ça valait la peine d'attendre !

Bien que PostgreSQL soit déjà, à mon goût, un SGBD mature, l'équipe de développement a fait un pas de géant avec cette version. J'avais surtout retenu de la version précédente, la 8.2, une simplification de la configuration, et par là, du tuning, du moteur, bien sûr ce n'était pas tout, mais c'était l'atout majeur de cette version à mes yeux.

Maintenant, de part mes nouvelles fonctions (ah oui, je n'en ai pas encore parlé), cette nouvelle version me permettra de mettre PostgreSQL en avant grâce aux fonctionnalités XML, bien que ce soit ma bête noire, et la recherche plein-texte intégrée. Les Enums me semblent être un bon apport, mais j'ai peur de voir des Enums à toutes les sauces, notamment pour représenter un état à Yes ou No, comme cela est fait abondamment sur MySQL, alors qu'un type booléen suffit (et PHP supporte traite correctement les booléens avec PG ?).

En terme d'exploitation, mon ancien métier, je salue l'activation par défaut d'autovacuum. Bien sûr, l'améliorations de performances est également à mettre en avant, mais il serait intéressant de monter un petit bench.

Pour plus d'informations, lisez l'annonce sur PostgreSQLfr.

J'aimerai également saluer Guillaume qui est pour beaucoup dans la traduction du manuel de PostgreSQL 8.3, qui est d'ailleurs déjà disponible ! Merci Guillaume !

C'est tout bête, j'ai simplement suivi les indications données sur le site de pgAdmin III pour installer une version à jour sur ma Gutsy, c'est à dire la version 1.8 activement supportée par Guillaume. <pub>Guillaume, grâce à qui j'ai une envie terrible de me jeter sur la Ronde de nuit de Terry Pratchett.</pub>

Ensuite, j'ai installé PostgreSQL 8.3 beta 4 en prenant soin d'activer les fonctionnalités XML :

./configure --prefix=/home/tom/pgsql/v8.3.0/ --enable-nls --enable-thread-safety --with-libxml
make -j3
make check

Pour être gratifié d'un charmant message indiquant que tout s'est bien passé :

=======================
 All 114 tests passed. 
=======================

Et finir gaiement sur un :

make install

J'installe également les contributions, car il y a quelques petites choses intéressantes que je voudrais voir... En tout cas, la compilation raaaââââââaaaaame sur mon petit P3 de rien du tout !

Petite note à l'attention des testeurs sur distribution Red Hat Enterprise Linux: la libxml2 fournie jusqu'à la RHEL 4 n'est pas suffisante pour compiler un PostgreSQL 8.3 avec les fonctionnalités XML. En revanche, ça passe sous soucis, même sur RHEL 3 sans ces dites fonctionnalités.

Et sinon, il faut que ce soit dit, ma chérie a sauvé mon apéro en ouvrant la bouteille d'Amer Bière dont le bouchon était coincé par le sucre cristallisé sur le goulot.. Et figurez-vous, chers lecteurs, qu'elle n'a pas utilisé ses petits biscottos, mais sa puissance intellectuelle pour y arriver ! Un petit chiffon imbibé d'eau chaude a remédier à ça ! Alors je dis bravo ! Aaah, les femmes...

Le site PostgreSQLfr propose un excellent article de Greg Smith intitulé Pourquoi préférer PostgreSQL à MySQL.

J'en ai appris un peu plus sur les points négatifs que je trouvais à MySQL, et surtout l'existence du mode strict. Cependant, je ne suis toujours pas convaincu par MySQL, essentiellement parce qu'une telle base est difficilement exploitable. Ah oui, par exploitable, j'entends avoir une centaine de bases de données qui se laissent oublier, et j'aurai trop peur de laisser des MySQL dans la nature sans surveillance !

En attendant, je retourne sur mes problèmes PHP/Oracle, grmbl !

La page de status des patchs en attente pour la 8.3 indique que l'ensemble des patchs attendus sont tous soit appliqués, soit en attente pour la 8.4, ou encore rejetés.

Au menu de cette prochaine version, quelques nouveautés intéressantes :

• intégration de Tsearch2 au core, c'est à dire que l'extension de recherche de textes devrait être simple à mettre en oeuvre, sans patchs de partout ;
• un gros morceau, HOT, à décortiquer ;
• les transactions asynchrones, que l'on peut assimiler de loin aux tables en NOLOGGING sur Oracle ;
• les curseurs que l'on peut mettre à jour ;
• la gestion du tampon partagé améliorée, sans effet de bords suite à une lecture séquentielle importante ;
• le support XML selon la norme SQL ;
• évidemment, des améliorations de toute part pour aller toujours plus vite ;
• etc.

Du boulot en perspective pour assimiler les nouveautés, d'autant plus que je suis resté sur la 7.4 pour certains aspects du moteur (merci Guillaume pour m'avoir mis à jour).

MAJ: et petite surprise que j'aime, possibilité de placer les WAL où on le souhaite, plus besoin de bidouiller avec des mv et des ln. L'option d'initdb qui va bien est -X (ou --xlogdir=), la seule contrainte étant que le répertoire accueillant les WALs soit vide.

MAJ: une variable de configuration temp_tablespaces a également fait son apparition. Les logiciels de réplication devraient également mieux s'intégrer au backend.

Définition du besoin

Depuis la version 8i, Oracle implémente les index inversés. Voici une proposition d’implémentation équivalente pour PostgreSQL. Les index inversés permettent d’accélérer les recherches sur les motifs tels que « colonne LIKE '%chaîne' ». Dans un tel cas, PostgreSQL effectue un parcours séquentiel (ou « sequential scan ») de la table interrogée. Toutefois, il est possible d’émuler un index inverse au moyen d’une fonction de renversement de chaîne couplée à un index sur fonction.

L'article précédent proposait l'implémentation d'un prototype en langage procédural PL/pgSQL, qui fait office ici de prototype.
Cette implémentation a pour principal défaut d'être lente, pénalisant ainsi gravement les performances en écriture (INSERT et UPDATE). Ainsi, à chaque mise à jour, il est nécessaire de faire appel à la fonction reverse pour mettre à jour l'index fonctionnel ; cela s'observe notamment à la création de l'index.
En revanche, il est possible de tirer partie des capacités de traitement des caractères multi-octets, que l'on rencontre notamment dans le cas d'une base de données encodée en UTF-8.

Ainsi, l'implémentation en langage C se doit d'être à la fois plus rapide et surtout se doit de supporter les jeux de caractères multi-octets. C'est à partir de ce minuscule cahier des charges que nous allons construire notre fonction reverse.

Pourquoi écrire une procédure stockée en C

Pourquoi s'embêter à prendre le temps d'écrire une procédure stockée en langage C alors qu'il est possible de faire la même chose en langage PL/pgSQL ?
Il y a plusieurs réponses à cette question :

• Une fonction C permet de protéger le code. En effet, rien n'interdit à un utilisateur possédant les droits nécessaires de modifier la procédure stockée que l'on a écrite et validé par une autre procédure de son crue, rendant le système inopérant.
  
• Si le besoin de créer son propre type de données se fait sentir, le passage par la case fonction C est obligatoire.
• La satisfaction de connaître un peu mieux le fonctionnement interne de PostgreSQL, mais c'est surtout une satisfaction de geek :)
• La problématique de la vitesse est toutefois le facteur déterminant de la réécriture d'une fonction d'un langage procédural interprété en langage compilé.
  

Le gain significatif de vitesse ne sera pas évident pour les requêtes de sélection. En revanche, les écritures (surtout INSERT et UPDATE) peuvent être fortement pénalisées par le coût de la mise à jour d'un index fonctionnel.
Bien que cela ne soit pas évident pour une opération unitaire, il sera parfaitement visible dans le cas d'une opération d'écriture en masse (chargement massif de données), ou tout simplement pour la création de l'index fonctionnel.
Dans un tel cas, l'option d'une réécriture en langage C est à envisager très sérieusement.

Implémentation et discussion technique

Les possibilités d'extension de PostgreSQL s’appuient sur les mécanismes de chargement dynamique de bibliothèque du système d’exploitation. L’interface de programmation est relativement simple, à condition d’en connaître certaines clés.

Structure du projet

Le projet est articulé autour de différents fichiers, qui seront tous placés dans un répertoire dédié :

1. un fichier Makefile simplifié, utilisant PGXS, l'infrastructure de construction d'extension PostgreSQL ;
2. un modèle de script SQL d'installation reverse.sql.in ;
3. un fichier uninstall_reverse.sql ;
4. le fichier source en langage C, reverse.c.

Fichiers annexes

Avant toute chose, il faut disposer d’un fichier « Makefile » de construction du module externe :

MODULES = reverse
#PG_CPPFLAGS = -ggdb
DATA_built = reverse.sql
DATA = uninstall_reverse.sql
PGXS := $(shell pg_config --pgxs)
include $(PGXS)

Le Makefile utilise ici l’outil PGXS qui propose un fichier Makefile prédéfini, à l’instar des fichiers Makefile fournis par Oracle.

Le fichier « reverse.sql.in » qui sert de modèle à la création du fichier d'installation de l'extension « reverse.sql ». Ce dernier fichier sera généré à partir du modèle en remplaçant « MODULE_PATHNAME » par le chemin complet du fichier objet généré.

-- Déclaration de la fonction reverse en tant que module C
SET search_path = public;
CREATE OR REPLACE FUNCTION reverse(varchar) RETURNS varchar
     AS 'MODULE_PATHNAME', 'reverse'
     LANGUAGE 'C' IMMUTABLE STRICT;

Le script « reverse.sql » sera exécuté par un utilisateur PostgreSQL ayant le rôle d’administrateur, les fonctions C étant considérées comme non-sûres et donc de la responsabilité de l’administrateur.

Un script de désinstallation « uninstall_reverse.sql » est également prévu, ça fait toujours plaisir :

SET search_path = public;
DROP FUNCTION reverse(varchar);

Un peu de technique

La lecture de la page « Fonctions en langage C » permet d’obtenir les informations nécessaires au développement d’une fonction C, voir la documentation « Fonctions en langage C ». Cependant la lecture des fichiers d’en-têtes permet d’apporter un éclairage supplémentaire sur certaines structures de données.

Traitement des chaînes de caractères avec PostgreSQL

Sous PostgreSQL, les chaînes de caractères ne sont pas délimitées par un caractère nul « \0 » terminal, mais, à l’instar du langage Pascal, en stockant dans une structure d’abord sa longueur puis son contenu. Une telle chaîne est décrite dans une structure de type « varlena ». Ce type de données offre en fait un moyen uniforme de stocker tout type de données à longueur variable, comme les chaînes de caractères, les tableaux ou encore les types utilisateurs.

Voici sa définition, obtenu dans le fichier d'en-tête c.h, à la ligne 409 :

struct varlena
{
    int32       vl_len_;      /* Do not touch this field directly! */
    char        vl_dat[1];
};

Ainsi, l'entier vl_len contient la longueur, en octets, de la chaîne d'octets vl_dat.

Quelques macros permettent de manipuler facilement cette structure.

• VARDATA(varlena) obtient un pointeur sur la donnée ;
• VARSIZE(varlena) obtient la taille en octets de la structure varlena (vl_len + vl_dat) ;
• la constante VARHDRSZ représente la taille en octet de vl_len ;
• Enfin, VARATT_SIZEP, remplacée par SET_VARSIZE à partir de la 8.3, permet de définir la longueur en octets de la donnée.

Ainsi, pour obtenir la longueur en octets de la données, on utilisera (VARSIZE - VARHDRSZ).

Support des jeux de caractères multi-octets

L'implémentation proposée supporte les jeux de caractères multi-octets, comme l'UTF8 (ou Unicode) et les jeux de caractères asiatiques, qui représente certains caractères sous la forme d'une séquence de deux octets ou plus (voir référence).
PostgreSQL met à disposition des fonctions utiles pour manipuler les chaînes de caractères, peu importe l'encodage, notamment pg_verifymbstr qui valide une chaîne de caractère selon l'encodage de la base de données, ou encore pg_mblen qui donne la longueur en octets d'un caractère. Pour le prototype des fonctions citées et d'autres fonctions, se référer au fichier d'en-tête « mb/pg_wchar.h ».

Les conventions d'appel

Il existe deux conventions d'appel de fonctions externes :

1. La convention d'appel version 0, représentant l'ancien style, simple à utiliser ;
2. La convention d'appel version 1, qui est la norme dorénavant et qui ne présente pas de difficultés particulières.

La convention d'appel version 1 sera utilisée dans le but de donner d'entrée de jeu de bonnes habitudes. La complexité de cette convention est masquée par une batterie de macros qui rendent son utilisation tout aussi simple, voire encore plus simple que la version 0, notamment pour le passage d'arguments.

Implémentation en langage C

Le source C est structuré en quatre parties :

• L’inclusion des fichiers d’en-têtes nécessaires ;
• La définition d’un « magic » signant un module externe PostgreSQL ;
• La définition d’un « magic » déclarant la fonction reverse à PostgreSQL ;
• Le corps de fonction reverse, cette fois en langage C ;

Voici ci-après, le code source en langage C de la fonction reverse.

/* 
 * reverse procedural function 
 * 
 * Thomas Reiss, 12/07/2007 – 24/07/2007 - 02/08/2007
 * Alain Delorme, 24/07/2007 
 * Merci à depesz pour ses tests sur la version 8.3devel
 * 
 */ 

#include "pg_config.h" 
#include "postgres.h" 
#include "fmgr.h" 
#include "mb/pg_wchar.h" 
#include "utils/elog.h" 

#ifdef PG_MODULE_MAGIC 
PG_MODULE_MAGIC; 
#endif 

Datum 
reverse(PG_FUNCTION_ARGS); 

/* SET_VARSIZE correspond à la nouvelle API, nous définissons cette
   macro pour les versions ne la possédant pas. */
#ifndef SET_VARSIZE
#define SET_VARSIZE(n,s) VARATT_SIZEP(n) = s; 
#endif 

/* fonction reverse */ 
PG_FUNCTION_INFO_V1(reverse); 
Datum 
reverse(PG_FUNCTION_ARGS) 
{ 
    int len, pos = 0; 
    VarChar *str_out, *str_in; 

    /* Obtient l'adresse de l'argument */ 
    str_in = PG_GETARG_VARCHAR_P_COPY(0); 
    /* Calcul de la taille en octet de la chaîne */ 
    len = (int) (VARSIZE(str_in) - VARHDRSZ); 

    /* Créer une chaîne vide de taille identique */ 
    str_out = (VarChar *)palloc(VARSIZE(str_in)); 
    /* La structure résultante aura une longueur identique */
    SET_VARSIZE(str_out, VARSIZE(str_in)); 

    /* Vérifie que l'encodage de la chaîne en argument 
     * concorde avec l'encodage de la BDD */ 
    pg_verifymbstr(VARDATA(str_in), len, false); 

    /* Copie à l'envers de la chaîne */ 
    while (pos < len) 
    { 
       int charlen = pg_mblen(VARDATA(str_in) + pos); 
       int i = charlen; 
       /* Copie un caractère. 
        * !! Un caractère != un octet */
       while (i--) 
         *(VARDATA(str_out) + len - charlen + i - pos) = *(VARDATA(str_in) + i + pos); 
       pos = pos + charlen;     /* incrémente le compteur  */
    } 
    PG_FREE_IF_COPY(str_in, 0); 
    /* Retourne la copie */ 
    PG_RETURN_VARCHAR_P(str_out); 
} 

Construction

La construction de l'extension PostgreSQL est réalisée en invoquant make

tom@clementina:~/src/reverse$ make
cc -g -Wall -O2 -fPIC -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -g -fpic -I. -I/usr/include/postgresql/8.2/server -I/usr/include/postgresql/internal -D_GNU_SOURCE  -I/usr/include/tcl8.4  -c -o reverse.o reverse.c
cc -shared -o reverse.so reverse.o
rm reverse.o

Si tout s'est bien passé, l'installation sera finalisée en exécutant la commande make install, éventuellement précédé de sudo en fonction de sa distribution et de son installation de PostgreSQL.

tom@clementina:~/src/reverse$ sudo make install
Password: xxxx
/bin/sh /usr/lib/postgresql/8.2/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./reverse.sql '/usr/share/postgresql/8.2/contrib'
 /bin/sh /usr/lib/postgresql/8.2/lib/pgxs/src/makefiles/../../config/install-sh -c -m 755  reverse.so '/usr/lib/postgresql/8.2/lib'

Les fichiers produits seront ainsi installés dans le répertoire d'installation de PostgreSQL.
Il est toutefois possible de les positionner ailleurs, à condition d'adapter le fichier « reverse.sql » de façon à indiquer à PostgreSQL où se trouve la bibliothèque partagée (fichier « reverse.so » sous Linux).

Utilisation et performances

Vérification de bon fonctionnement

Dans un premier temps, on crée la fonction via l'outil psql :

test=# \i reverse.sql 
CREATE FUNCTION

On vérifie que la fonction répond correctement :

test=# SHOW client_encoding;
 client_encoding 
-----------------
 UTF8
(1 ligne)

test=# SELECT reverse('Chaîne à renverser');
      reverse       
--------------------
 resrevner à enîahC
(1 ligne)

Ok, ça marche, y compris avec les chaînes encodées en UTF-8 !

Petit test de performance

Ce test a été réalisé par depesz, qui m'a aimablement autorisé a le réutiliser dans le cadre de cet article.

Petit aperçu du jeu de test :

test=# SELECT count(*),
test-#        min(length(filepath)),
test-#        max(length(filepath)),
test-#        sum(length(filepath))
test-#  FROM test;
count  | min | max |   sum
-------+-----+-----+----------
320136 |   7 | 174 | 18563865
(1 row)

Maintenant, voici une petite comparaison des 3 implémentations, à savoir le prototype en PL/pgSQL, la version PL/perl de depesz et la version C. On oppose à ces trois tests un parcours de la table via la fonction d'agrégat count(), permettant ainsi de mesurer l'overhead due à chaque implémentation de la fonction reverse.
A chaque fois, 3 exécutions permettent de vérifier les résultats.

Simple comptage (count)

Voici l'ordre SQL utilisé pour réaliser ce test :

test=# EXPLAIN ANALYZE
test-# SELECT count(filepath)
test-# FROM test;

Et voici les temps de réponse obtenus :
Exécution #1 : 1269.535 ms
Exécution #2 : 1268.421 ms
Exécution #3 : 1257.926 ms
Moyenne : 1265,29 ms

Prototype PL/pgSQL

test=# EXPLAIN ANALYZE
test-# SELECT count(reverse_plpgsql(filepath))
test-# FROM test;

Exécution #1 : 55269.941 ms
Exécution #2 : 56047.004 ms
Exécution #3 : 56149.888 ms
Moyenne : 55822,28 ms

Version PL/perl

test=# EXPLAIN ANALYZE
test-# SELECT count(text_reverse(filepath))
test-# FROM test;

Exécution #1 : 4088.625 ms
Exécution #2 : 4089.729 ms
Exécution #3 : 4020.500 ms
Moyenne : 4066,28 ms

Version C

test=# EXPLAIN ANALYZE
test-# SELECT count(reverse(filepath))
test-# FROM test;

Exécution #1 : 1596.176 ms
Exécution #2 : 1647.046 ms
Exécution #3 : 1657.531 ms
Moyenne : 1633,58 ms

Synthèse du test de performance

Voici un graph faisant la synthèse des moyennes des temps de réponse :

Le graph suivant permet de mieux se rendre compte de l'overhead induie par l'implémentation PL/perl et l'implémentation C.

Chose très intéressante : l'overhead pour renverser ~320000 enregistrements est de seulement 300ms, ce qui est bien entendu excellent et laisse présager de très bonnes performances quant au coût de la mise à jour d'un index fonctionnel.

Ainsi, comme cela pouvait être aisément imaginé, la version C est la plus rapide, suivie par la version PL/Perl. La version PL/pgSQL se traîne lamentablement derrière, ce qui justifie complètement la réécriture de la procédure stockée en C.

Quant à l'utilisation de cette fonction dans un cas concret, il faut se reporter à l'article précédent qui est cité en référence.

Notes

Cette fonction a été testé sur une base en PostgreSQL 8.0, 8.2 et 8.3devel (merci à depesz).

Je regrette de ne pas avoir pu aller un peu plus loin pour le précédent article, des impératifs de place m'ayant obligé à aller à l'essentiel sans montrer les différents plans d'exécution. Heureusement, l'article de hubert depesz lubaczewski montre tous les aspects que j'ai négligé, malheureusement c'est en anglais.

Références

De plus amples précisions sont également disponibles en langue anglaise sur les sites Internet suivant :

• Fonction reverse avec PostgreSQL (PL/pgSQL)
• Les index fonctionnels
• Support des jeux de caractères dans PostgreSQL
• Fonctions en langage C
• Writing PostgreSQL Functions in C
• What's a Varlena ?
• Le minimum syndical à connaître sur les jeux de caractères multi-octets
• RFC 3629, UTF-8, a transformation format of ISO 10646 ou RFC 3629
• Documentation Doxygen du code de PostgreSQL

Remerciements

Je remercie vivement les personnes suivantes :

• Alain Delorme pour sa contribution,
• hubert depesz lubaczewski pour ses retours et tests préliminaires,
• Guillaume Lelarge pour ses relectures et ses conseils avisés.

Hubert "depesz" Lubaczewski a réalisé une implémentation d'index inversés sans que nous nous consultions.

La solution de depesz utilise une fonction écrite en PL/Perl et offre un gain de vitesse très appréciable par rapport à la version PL/pgSQL. On apprécie le petit test de performance des deux implémentations. Se référer à l'article indexable ” field like ‘%something’” pour plus de précisions.

Depuis, la version C de la fonction reverse a bien avancé et les gains sont encore plus significatifs. Ce sera très bientôt en ligne.

Voici un petit article technique que j'ai écrit dans le cadre du travail et dont on m'a autorisé la diffusion. Je remercie Guillaume Lelarge pour l'avoir posté sur le site de la communauté francophone des utilisateurs de PostgreSQL : Utiliser un index pour les recherches sur des motifs tels que « colonne LIKE '%chaîne' ».

Depuis la version 8i, Oracle implémente les index inversés. Voici une proposition d’implémentation équivalente pour PostgreSQL. Les index inversés permettent d’accélérer les recherches sur les motifs tels que « colonne LIKE '%chaîne' ». Dans un tel cas, PostgreSQL effectue un parcours séquentiel (ou « sequential scan ») de la table interrogée. Toutefois, il est possible d’émuler un index inverse au moyen d’une fonction de renversement de chaîne couplée à un index sur fonction.

Tout d’abord, il est nécessaire d’activer le support du langage procédural PL/pgSQL au sein de la base de données cible à l’aide de la commande Unix « createlang plpgsql BASECIBLE ».

La fonction appelée « reverse » prendra comme seul et unique argument une chaîne de type varchar et retournera une chaîne de type varchar.

CREATE OR REPLACE FUNCTION reverse(varchar) RETURNS varchar AS $PROC$
DECLARE
  str_in ALIAS FOR $1;
  str_out varchar;
  str_temp varchar;
  position integer;
BEGIN
  -- Initialisation de str_out, sinon sa valeur reste à NULL
  str_out := '';
  -- Suppression des espaces en début et fin de chaîne
  str_temp := trim(both ' ' from str_in);
  -- position initialisée a la longueur de la chaîne
  -- la chaîne est traitée a l’envers
  position := char_length(str_temp);
  -- Boucle: Inverse l'ordre des caractères d'une chaîne de caractères
  WHILE position > 0 LOOP
  -- la chaîne donnée en argument est parcourue
  -- à l’envers,
  -- et les caractères sont extraits individuellement au
  -- moyen de la fonction interne substring
    str_out := str_out || substring(str_temp, position, 1);
    position := position - 1;
  END LOOP;
  RETURN str_out;
END;
$PROC$ LANGUAGE plpgsql
IMMUTABLE;

La fonction reverse est structurée en trois partie :

• La déclaration elle-même via l’ordre CREATE OR REPLACE FUNCTION
• La déclaration des variables utilisées, sous le bloc DECLARE
• Le corps de la fonction, entre BEGIN et END;

On notera que la fonction reverse est de catégorisée « IMMUTABLE », indiquant au SGBD que celle-ci ne modifie pas les données et garantie que la fonction retournera toujours le même résultat quand elle est appelée avec les mêmes arguments, condition indispensable à la création d’un index sur fonction. Voir la documentation PostgreSQL « Catégories de volatilité des fonctions » dans la partie « Etendre le SQL ».

Un essai de la procédure permet de s’assurer de son bon fonctionnement :

DPAR=# SELECT reverse('Chaîne à renverser');
      reverse

 resrevner à enîahC
(1 ligne)

Pour optimiser les recherches par suffixes, il est nécessaire de créer un index sur fonction, sans oublier une collecte des statistiques pour l’optimiseur:

CREATE INDEX reverse_index_prenom ON personnes (REVERSE(prenom) varchar_pattern_ops);
ANALYZE TABLE personnes;

Ensuite, au lieu d’écrire un prédicat du type « WHERE prenom LIKE ’%mas’», on écrira :

SELECT * FROM personnes
  WHERE reverse(prenom) LIKE reverse(’%mas’);

PostgreSQL utilisera alors l’index créé précédemment et répondra instantanément. Sur une base de test contenant 4 millions d’enregistrement, les temps de réponse sont passés de 10s à 33ms pour la requête.

La fonction « reverse » pourrait être améliorée, mais l’implémentation décrite a l’avantage de montrer une réalisation simple et donne un exemple d’écriture de fonction pour PostgreSQL. On notera que la fonction renvoie une chaîne vide lorsque la valeur NULL est donnée en entrée. Un autre axe d’amélioration de la vitesse d’exécution serait la réécriture de cette fonction sous forme de fonction native en C couplée au choix d’un algorithme de renversement efficace.