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.

Le PGDG a publié un ensemble de correctifs. Il s’agit d’une mise à jour cumulative pour toutes les versions actuellement supportées de PostgreSQL.

Les versions publiées sont 11.2, 10.7, 9.6.12, 9.5.16 et 9.4.21.

Cette publication change le comportement de PostgreSQL vis-à-vis de fsync() et inclut des correctifs pour le partitionnement et 70 autres bugs rapportés ces 3 derniers mois.

Les utilisateurs sont encouragés à mettre à jour leurs serveurs lors de la prochaine maintenance planifiée.

Changement de comportement vis-à-vis de fsync()

Lorsque cette fonction est disponible pour le système d’exploitation, et qu’elle est activée dans le fichier de configuration (par défaut), PostgreSQL utilise la fonction fsync() du noyau pour écrire les données sur disque. Certains systèmes d’exploitation qui fournissent la fonction fsync() remontent une erreur et effacent les données qu’ils auraient dû écrire lorsqu’ils rencontrent un problème d’écriture sur disque.

Cette opération d’effacement des données a un malheureux effet secondaire pour PostgreSQL : si PostgreSQL essaie d’écrire les données sur disque en appelant à nouveau la fonction fsync(), fsync() va répondre que l’opération est un succès, mais les données que PostgreSQL pense avoir sauvegardées sur le disque ne seront pas réellement écrites. Il s’agit donc d’un cas possible de corruption de données.

Cette mise-à-jour modifie la façon dont PostgreSQL gère l’erreur de fsync(). PostgreSQL ne retente pas l’appel à fsync() mais sort en mode panic. Dans ce cas, PostgreSQL pourra rejouer les données depuis le fichier de WAL pour assurer l’écriture de ces données. Bien que cela puisse paraître une solution suboptimale, il n’y a pas, à ce jour, beaucoup d’autres alternatives, et, d’après les rapports reçus, ce cas n’arrive que très rarement.

Un nouveau paramètre data_sync_retry a été ajouté pour gérer ce fonctionnement. Dans le cas où il est certain que le noyau ne supprime pas les tampons de données invalides en cas d’erreur fsync() alors, le paramètre data_sync_retry peut être positionné à on pour restaurer le comportement précédent.

Améliorations et corrections de bugs

Cette mise à jour introduit un changement dans la manière de packager les release notes. À partir de ce correctif, toutes les versions supportées de PostgreSQL ne contiendront que les notes de versions correspondant à la version majeure concernée. Par exemple, PostgreSQL 11 ne comporte que les notes de version des versions 11.2, 11.1 et 11.0. Les release notes pour les versions non supportées (PostgreSQL 9.3 et inférieur) seront disponibles dans les anciennes versions et dans un archivage spécifique bientôt mis en place sur le site postgresql.org.

Cette mise à jour corrige également près de 70 bugs reportés ces derniers mois. Certains problèmes ne concernent que la version 11, mais beaucoup concernent toutes les versions supportées.

Ces correctifs comprennent (entre autres):

• la correction de la gestion des index uniques avec la clause INCLUDE sur les tables partitionnées ;
• l’assurance que les contraintes NOT NULL sur une table partitionnée sont vérifiées sur toutes ses partitions ;
• plusieurs correctifs sur la gestion des contraintes des tables partitionnées ;
• la correction de problèmes lors de la gestion de ON COMMIT DROP ou ON COMMIT DELETE ROWS sur des tables partitions et sur des tables avec héritage ;
• l’interdiction de l’ordre COPY FREEZE sur des tables partitionnées ;
• plusieurs correctifs pour l’utilisation de ALTER TABLE .. ADD COLUMN avec une valeur par défaut non null, y compris un cas possible de corruption d’index ;
• plusieurs correctifs concernant les index GIN, y compris la correction d’un possible verrou mortel entre le process de vacuum et des insertions concurrentes dans l’index (ce qui entraîne une annulation partielle des améliorations de performances apportées en PostgreSQL 10) ;
• la résolution d’un possible crash de la réplication logique quand des index partiels sont utilisés ;
• plusieurs correctifs pour les write-ahead log (WAL) ;
• la résolution d’un possible crash si un UPDATE est utilisé avec plusieurs clauses SET utilisant un sous-SELECT ;
• la résolution d’un crash quand aucune ligne n’est retournée par json[b]_populate_recordset() ou json[b]_to_recordset() ;
• plusieurs correctifs liés à la gestion des collations, incluant le parsing des expressions sensibles à la collation comme argument d’un appel CALL ;
• plusieurs correctifs concernant le planificateur, y compris l’accélération de la planification pour un grand nombre de tables héritées ou partitionnées ;
• plusieurs correctifs pour l’ordre TRUNCATE ;
• l’assurance que ALTER TABLE ONLY ADD COLUMN IF NOT EXISTS est géré correctement ;
• l’autorisation de la clause UNLISTEN en mode hot-standby (répliqua) ;
• la résolution du problème de parsing des listes de nom d’hôtes séparés par des espaces dans ldapserver ;
• plusieurs correctifs pour ecpg ;
• plusieurs correctifs pour psql, y compris permettre \g target après COPY TO STDOUT ;
• la génération de nombres aléatoires pour pgbench est maintenant entièrement déterministe et indépendante de la plateforme quand --random-seed=N est utilisé ;
• pg_basebackup et pg_verify_checksums ignorent correctement les fichiers temporaires ;
• plusieurs correctifs pour pg_dump, y compris l’ajout d’une clause ALTER INDEX SET STATISTICS si nécessaire ;
• la prévention d’un faux rapport de corruption d’index par la contribution amcheck causée par des données compressées à la volée ;
• le support de nouvelles variables pour le Makefile pour permettre la compilation des extensions.

Cette mise à jour contient également les informations de timezone pour les changements intervenus au Kazakhstan, au Metlakatla et à Sao Tomé et Principe en 2018. La zone Qyzylorda du Kazakhstan est coupée en 2, ce qui a entraîné la création d’une nouvelle zone nommée Asia/Qostanay, car certaines zones n’ont pas changé leur décalage avec UTC. Il y a eu également des corrections historiques pour Honk Kong et plusieurs îles du Pacifique.

Mise à jour

Toutes les mises à jour de PostgreSQL sont cumulatives. Comme pour toute mise à jour mineure, il n’est pas utile de faire de dump/reload des bases de données ou d’utiliser pg_upgrade pour l’appliquer. Un simple arrêt de PostgreSQL et le remplacement des binaires suffit.

Dans le cas où des mises à jour mineures ont été omises, il pourrait s’avérer nécessaire d’effectuer des étapes supplémentaires. Veuillez vous référer aux notes de versions précédentes pour les détails.

PostgreSQL 9.4 sera en fin de support le 13 février 2020. Veuillez lire notre politique de versioning pour plus d’informations.

Liens utiles

• Téléchargement: https://www.postgresql.org/download/
• Release Notes: https://www.postgresql.org/docs/11/release.html
• Sécurité: https://www.postgresql.org/support/security/
• Politique de versioning: https://www.postgresql.org/support/versioning/
• Suivre @postgresql sur Twitter: https://twitter.com/postgresql

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.

Cet article commence une série présentant des fonctionnalités du langage SQL disponibles dans PostgreSQL. Vous pouvez retrouver une comparaison de la disponibilité des fonctionnalités ici: http://www.sql-workbench.eu/dbms_comparison.html. Ces fonctionnalités ne sont pas forcément présentes dans la norme SQL.

Ce premier article présente une fonctionnalité très utile lorsqu’on souhaite manipuler des données dont le type est plus complexe qu’un simple scalaire : la contrainte d’exclusion.

Le but de cette fonctionnalité est de vérifier que les données qu’on insère dans une table ne sont pas redondantes, qu’il n’y a pas de doublons.

La contrainte d’unicité

Pour ceci, il existe une fonctionnalité très courante lorsqu’on utilise une base de données relationnelle : la contrainte d’unicité.

Une contrainte d’unicité garantit l’unicité des données contenues dans une colonne ou un groupe de colonnes par rapport à toutes les lignes de la table. Elle n’autorise donc pas l’insertion de doublon dans une table, ce qui permet de s’assurer de la qualité des données.

Par exemple, la table suivante permet de connaitre l’ensemble des plages d’adresses IPs utiles :

CREATE TABLE reseau
(
  id int GENERATED BY DEFAULT AS IDENTITY primary key
  , adresses ip4r
  , commentaire text
);

L’attribut adresses contient donc les adresses des réseaux, avec le type de données ip4r fourni par l’extension du même nom : https://github.com/RhodiumToad/ip4r.

Si on veut éviter d’enregistrer des doublons, il est alors nécessaire d’ajouter une contrainte d’unicité :

ALTER TABLE reseau ADD UNIQUE ( adresses ) ;

Cet ordre crée un index btree d’unicité (on parle aussi d’index unique, mais ce n’est pas l’index qui est unique), mettant en œuvre la contrainte. Il n’est alors plus possible d’ajouter de plage d’adresse IP en doublon :

INSERT INTO reseau (adresses)
  VALUES ('192.168.122.0/24')
       , ('192.168.89.0/24');
INSERT 0 2

INSERT INTO reseau (adresses)
  VALUES ('192.168.122.0/24');
ERROR:  duplicate key value violates unique constraint "reseau_adresses_key"
DETAIL:  Key (adresses)=(192.168.122.0/24) already exists.

Mais si on ajoute un réseau qui interfère avec une entrée existante, sans être strictement égal, que se passe-t-il ?

INSERT INTO reseau (adresses)
  VALUES ('192.168.122.0/28');
INSERT 0 1

On constate que l’entrée est valide, alors qu’il y a potentiellement un problème d’intégrité de nos données : la plage d’adresses IP 192.168.122.0/28 est en réalité comprise dans la plage 192.168.122.0/24, et il y a donc un problème fonctionnel.

La raison est simple : la contrainte d’unicité ne sait utiliser que l’opérateur d’égalité (=). Pour constater le conflit d’adresses réseau, il nous faut utiliser l’opérateur de chevauchement : &&.

La contrainte d’exclusion

On pourrait simplement résumer la contrainte d’exclusion comme étant une extension de la contrainte d’unicité, avec un opérateur de comparaison différent de l’égalité.

L’opérateur de comparaison doit être commutatif, indexable (btree ou gist) et renvoyer un booléen (le type d’index gin n’est pas supporté).

Dans le cas de notre exemple, l’opérateur de comparaison && fourni par l’extension ip4r remplit les conditions, grâce à la classe d’opérateurs gist_ip4r_ops autorisant le parcours d’un index gist.

La création de la table est alors :

CREATE TABLE reseau
(
  id int GENERATED BY DEFAULT AS IDENTITY primary key
  , adresses ip4r
  , commentaire text
);

Et la création de la contrainte d’exclusion est :

ALTER TABLE reseau
  ADD CONSTRAINT reseau_adresses_excl EXCLUDE
    USING gist
    ( adresses WITH && );

Comme pour la contrainte d’unicité, la contrainte crée implicitement un index, ici de type gist, précisé avec le mot-clé USING.

Le mot-clé EXCLUDE permet donc de préciser le type de contrainte, et le mot-clé WITH indique l’opérateur utilisé.

Ensuite, l’insertion de données permet de mettre en évidence le contrôle d’intégrité :

INSERT INTO reseau (adresses)
  VALUES ('192.168.122.0/24'), ('192.168.89.0/24');
INSERT 0 2

INSERT INTO reseau (adresses)
  VALUES ('192.168.122.0/24');
ERROR:  conflicting key value violates
        exclusion constraint "reseau_adresses_excl"
DETAIL:  Key (adresses)=(192.168.122.0/24) conflicts with
         existing key (adresses)=(192.168.122.0/24).

INSERT INTO reseau (adresses)
  VALUES ('192.168.122.0/28');
ERROR:  conflicting key value violates
        exclusion constraint "reseau_adresses_excl"
DETAIL:  Key (adresses)=(192.168.122.0/28) conflicts with
         existing key (adresses)=(192.168.122.0/24).

On comprend aisément en lisant les messages d’erreurs que la contrainte d’exclusion permet de couvrir les fonctionnalités de la contrainte d’unicité, et de mettre un œuvre une contrainte d’intégrité liée à la nature de la donnée (non-scalaire), implémentée par un opérateur spécifique (&&).

Conclusion

La notion de contrainte d’exclusion est un outil puissant pour assurer l’intégrité de données : une donnée non scalaire avec un opérateur autre que la simple égalité est alors stockée en toute sérénité.

Le prochain article de la série présentera une famille de type de données ressemblant à ip4r : les plages de valeurs. Et les contraintes d’exclusion seront à l’œuvre !

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

Depuis la version 10, PostgreSQL permet d'utiliser la bibliothèque de référence Unicode ICU pour trier et indexer les textes à travers les collations ICU.

Au-delà de ce que permet le coeur, d'autres fonctionnalités de cette bibliothèque sont exposables en SQL, c'est ce que fait l'extension icu_ext, qui couvre déjà une partie de l'API ICU, et devrait s'étoffer petit à petit. L'intérêt d'utiliser ICU en SQL est essentiellement de profiter de ses algorithmes gérant du texte multilingue en collant au plus près au standard Unicode.

J'aurais certainement l'occasion d'en reparler plus en détail dans ce blog en tant qu'auteur de l'extension, mais en attendant j'ai eu le plaisir de présenter sur ce thème au meetup PG Paris le 28 juin:

PDF de la présentation.

Un grand merci aux organisateurs du meetup et à MeilleursAgents pour l'accueil dans leurs très jolis locaux!

Qu'est-ce qu'un pivot?

Le pivot est une opération par laquelle des données sous une colonne deviennent des noms de colonnes, de sorte que visuellement une sorte de rotation à 90° est opérée: ce qui se lisait verticalement de haut en bas se retrouve ordonné horizontalement de gauche à droite. Les termes "transposition", "requête analyse croisée" ou "crosstab" sont aussi utilisés pour désigner ce concept, qui vient instiller un peu de la vision "tableur" des données dans l'approche relationnelle.

Dans le cas le plus simple, on part d'une colonne qui est fonction d'une autre. Considérons un exemple météorologique à 2 colonnes: l'année, et la pluviométrie correspondante, exprimée en nombre de jours où les précipitations ont dépassé 1mm.

Avant pivot:

 Année    | Pluie    |
----------+----------+
 2012     | 112      |
 2013     | 116      |
 2014     | 111      |
 2015     |  80      |
 2016     | 110      |
 2017     | 102      |

Après pivot:

 2012 | 2013 | 2014 | 2015 | 2016 | 2017 
------+------+------+------+------+------
  112 |  116 |  111 |   80 |  110 |  102

Souvent, il y a une deuxième dimension, c'est-à-dire qu'on part de 3 colonnes, dont une dépend fonctionnellement des deux autres: (dimension X, dimension Y)=>Valeur.

Dans l'exemple de la pluviométrie, la seconde dimension pourrait être un nom de ville, comme ci-dessous:

Avant pivot:

 Année |   Ville   | Jours 
-------+-----------+-------
  2012 | Lyon      |   112
  2013 | Lyon      |   116
  2014 | Lyon      |   111
  ...  | ...       |   ...
  2014 | Toulouse  |   111
  2015 | Toulouse  |    83

Considérons un jeu de données réduit de 13 villes x 6 ans. La série ci-dessus ferait donc 78 lignes. (un dump SQL pour cet exemple est disponible en téléchargement ici: exemple-pluviometrie.sql; les données brutes sont à l'échelle du mois et proviennent originellement de https://www.infoclimat.fr/climatologie/).

Et voici une présentation typique après pivot:

   Ville   | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 
-----------+------+------+------+------+------+------
 Ajaccio   |   69 |   91 |   78 |   48 |   81 |   51
 Bordeaux  |  116 |  138 |  137 |  101 |  117 |  110
 Brest     |  178 |  161 |  180 |  160 |  165 |  144
 Dijon     |  114 |  124 |  116 |   93 |  116 |  103
 Lille     |  153 |  120 |  136 |  128 |  138 |  113
 Lyon      |  112 |  116 |  111 |   80 |  110 |  102
 Marseille |   47 |   63 |   68 |   53 |   54 |   43
 Metz      |   98 |  120 |  110 |   93 |  122 |  115
 Nantes    |  124 |  132 |  142 |  111 |  106 |  110
 Nice      |   53 |   77 |   78 |   50 |   52 |   43
 Paris     |  114 |  111 |  113 |   85 |  120 |  110
 Perpignan |   48 |   56 |   54 |   48 |   69 |   48
 Toulouse  |   86 |  116 |  111 |   83 |  102 |   89

(13 lignes)

Pour exprimer ça de manière générale, le résultat pivoté d'une série de tuples (X,Y,V) est une grille de N+1 colonnes fois M lignes, où:

• N est le nombre de valeurs distinctes de X

• M est le nombre de valeurs distinctes de Y.

• la 1ere colonne (la plus à gauche) porte les valeurs distinctes de Y, généralement dans un ordre défini, par exemple ici les noms de ville dans l'ordre alphabétique.

• les noms des autres colonnes sont constituées des valeurs distinctes de X, également dans un ordre défini. Dans l'exemple ci-dessus ce sont les années, par ordre croissant ou décroissant.

• pour chaque couple (X,Y), si un tuple (X,Y,V) est présent dans le jeu de données avant pivot, la valeur V est placée dans la grille au croisement de la colonne de nom X et de la ligne commençant par Y. C'est pourquoi on parle de tableau croisé (crosstab). Si à (X,Y) ne correspond pas de V dans le jeu de données, la case correspondante reste vide (NULL), ou à une valeur spécifique si on préfère.

Quand le nombre de colonnes reste raisonnable, cette représentation a quelques avantages visuels par rapport à l'affichage en ligne des tuples (X,Y,V):

• elle occupe mieux l'espace 2D.
• elle est plus intuitive, parce qu'il n'y a pas de répétition des dimensions.
• l'absence éventuelle de valeur dans une case saute aux yeux.
• il y a deux axes de tri indépendants.

Quelles requêtes pour pivoter un jeu de données?

La forme canonique

Contrairement à Oracle ou MS-SQL Server, PostgreSQL n'a pas de clause PIVOT dans son dialecte SQL, mais cette clause n'est pas essentielle. Une requête pour pivoter ces données à 3 colonnes (x,y,v) peut s'écrire sous cette forme:

SELECT
  y,
  (CASE WHEN x='valeur 1' THEN v END) "valeur 1",
  (CASE WHEN x='valeur 2' THEN v END) "valeur 2",
  ...à répéter pour chaque valeur de x devenant une colonne
  FROM table ou sous-requête
  [ORDER BY 1]

Assez fréquemment une requête pivot agrège en même temps qu'elle pivote: La forme sera alors plutôt de ce genre-là:

SELECT
  y,
  AGG(v) FILTER (WHERE x='valeur 1') AS "valeur 1",
  AGG(v) FILTER (WHERE x='valeur 2') AS "valeur 2",
  ...à répéter pour chaque valeur de x devenant une colonne
  FROM table ou sous-requête
  GROUP BY y [ORDER BY 1];

La clause FILTER est une nouveauté de PostgreSQL 9.4, sinon il est toujours possible d'utiliser une expression CASE WHEN. AGG(v) symbolise une fonction d'agrégation, qui pourrait être typiquement SUM(v) pour cumuler des valeurs, ou COUNT(v) pour compter des occurrences, ou encore MIN() ou MAX().

Pour la table d'exemple de pluviométrie sur 6 ans, si on a une mesure par mois et qu'on veut afficher un pivot Ville/Année, la requête serait la suivante:

SELECT
  ville,
  SUM(pluvio) FILTER (WHERE annee=2012) AS "2012",
  SUM(pluvio) FILTER (WHERE annee=2013) AS "2013",
  SUM(pluvio) FILTER (WHERE annee=2014) AS "2014",
  SUM(pluvio) FILTER (WHERE annee=2015) AS "2015",
  SUM(pluvio) FILTER (WHERE annee=2016) AS "2016",
  SUM(pluvio) FILTER (WHERE annee=2017) AS "2017"
FROM pluviometrie 
GROUP BY ville
ORDER BY ville;

La forme utilisant crosstab()

L'extension tablefunc de contrib fournit entre autres une fonction:
crosstab(text source_sql, text category_sql)
qui est souvent citée en premier dans les questions sur les pivots avec Postgres.

Le premier argument de crosstab est une requête renvoyant les données avant pivot. Le deuxième argument est une autre requête renvoyant les noms des colonnes après pivot, dans l'ordre désiré. La fonction renvoyant un type SETOF RECORD, en pratique il faut re-spécifier ces colonnes via une clause AS (col1 type, col2 type, etc...) pour qu'elles soient interprétables comme telles par le moteur SQL.

Exemple:

SELECT * FROM crosstab(
   -- requête pour le contenu de la grille
   'SELECT ville,annee,SUM(pluvio)
     FROM pluviometrie GROUP BY ville,annee ORDER BY ville',
   -- requête pour l'entête horizontal
   'SELECT DISTINCT annee FROM pluviometrie ORDER BY annee')
  AS ("Ville" text,
      "2012" int,
      "2013" int,
      "2014" int,
      "2015" int,
      "2016" int,
      "2017" int);

Les limites des pivots statiques

L'ennui avec ces deux formes de requêtes, aussi bien celle qui a recours à la fonction crosstab() que celle construite sur autant d'expressions que de colonnes, c'est qu'il faut lister les colonnes et que dès qu'il y a une donnée en plus à pivoter, l'ajouter manuellement à la liste. Sinon avec la première forme la nouvelle donnée sera ignorée, et avec crosstab() elle provoquera une erreur.

D'autre part, ces requêtes ne sont pas malléables: si on veut présenter les colonnes dans l'ordre inverse, ou bien pivoter sur une autre colonne (ici en l'occurrence mettre des villes horizontalement au lieu des années), elles doivent être modifiées presque intégralement.

Enfin, certains pivots génèrent des centaines de colonnes, et la perspective de gérer ça à la main en SQL paraît absurde.

Souvent, en tant qu'utilisateur de SQL, on voudrait faire ce qu'on pourrait appeler un pivot dynamique, c'est-à-dire une requête polymorphe qui, sans modification du SQL, se trouverait automatiquement avoir toutes les colonnes du résultat à partir des lignes correspondantes. Ce serait utile d'une part si ces données sont susceptibles de changer, et d'autre part quand il y a beaucoup de colonnes et qu'il est trop fastidieux de les spécifier.

Mais il se trouve qu'une requête SQL ne peut pas avoir des colonnes dynamiques au sens où il le faudrait pour un pivot dynamique. On pourra objecter que dans un SELECT * FROM table, le * est bien remplacé dynamiquement par liste des colonnes, mais la différence est que cette opération se fait dans l'étape d'analyse de la requête, pas dans la phase d'exécution. Avant l'exécution, le moteur SQL doit impérativement savoir quels sont le nombre, les types et les noms des colonnes de la requête et des sous-requêtes qui la composent. C'est d'ailleurs pour ça que la sortie de crosstab(), au même titre que n'importe quelle fonction qui renvoie un SETOF RECORD, doit être qualifiée statiquement par une liste de noms de colonnes avec leurs types sous la forme AS (col1 type1, col2 type2...)

Méthodes pour des pivots dynamiques

La difficulté du pivot dynamique peut être résumée ainsi: pour toute requête SQL, il faut que le type du résultat (noms et types de toutes les colonnes) soit connu avant la phase d'exécution. Or pour savoir quelles sont les colonnes composant le résultat pivoté, il faudrait exécuter la requête: c'est un cercle vicieux, et pour s'en sortir il faut forcément changer un peu les termes du problème.

Résultat encapsulé dans une colonne

Une première solution est que la requête SQL renvoie le résultat pivoté non pas en colonnes séparées, mais encapsulé dans une seule colonne avec un type multi-dimensionnel: array[text], JSON, ou XML. Cette solution est par exemple intégrée dans Oracle avec sa clause PIVOT XML. C'est en une seule étape, mais le résultat a une structure non-tabulaire qui ne correspond pas forcément à ce que les utilisateurs espèrent.

Voici un exemple en PostgreSQL moderne avec JSON:

SELECT ville,
       json_object_agg(annee,total ORDER BY annee)
   FROM (
     SELECT ville, annee, SUM(pluvio) AS total
        FROM pluviometrie
        GROUP BY ville,annee
   ) s
  GROUP BY ville
  ORDER BY ville;

Sans avoir à lister les années dans cette requête, on retrouve notre résultat précédent complet, mais sous forme de deux colonnes, une pour l'"axe vertical", l'autre pour tout le reste au format JSON:

   ville   |                                    json_object_agg                                     
-----------+----------------------------------------------------------------------------------------
 Ajaccio   | { "2012" : 69, "2013" : 91, "2014" : 78, "2015" : 48, "2016" : 81, "2017" : 51 }
 Bordeaux  | { "2012" : 116, "2013" : 138, "2014" : 137, "2015" : 101, "2016" : 117, "2017" : 110 }
 Brest     | { "2012" : 178, "2013" : 161, "2014" : 180, "2015" : 160, "2016" : 165, "2017" : 144 }
 Dijon     | { "2012" : 114, "2013" : 124, "2014" : 116, "2015" : 93, "2016" : 116, "2017" : 103 }
 Lille     | { "2012" : 153, "2013" : 120, "2014" : 136, "2015" : 128, "2016" : 138, "2017" : 113 }
 Lyon      | { "2012" : 112, "2013" : 116, "2014" : 111, "2015" : 80, "2016" : 110, "2017" : 102 }
 Marseille | { "2012" : 47, "2013" : 63, "2014" : 68, "2015" : 53, "2016" : 54, "2017" : 43 }
 Metz      | { "2012" : 98, "2013" : 120, "2014" : 110, "2015" : 93, "2016" : 122, "2017" : 115 }
 Nantes    | { "2012" : 124, "2013" : 132, "2014" : 142, "2015" : 111, "2016" : 106, "2017" : 110 }
 Nice      | { "2012" : 53, "2013" : 77, "2014" : 78, "2015" : 50, "2016" : 52, "2017" : 43 }
 Paris     | { "2012" : 114, "2013" : 111, "2014" : 113, "2015" : 85, "2016" : 120, "2017" : 110 }
 Perpignan | { "2012" : 48, "2013" : 56, "2014" : 54, "2015" : 48, "2016" : 69, "2017" : 48 }
 Toulouse  | { "2012" : 86, "2013" : 116, "2014" : 111, "2015" : 83, "2016" : 102, "2017" : 89 }
(13 lignes)

C'est déjà pas mal, mais visuellement ça manque d'alignement, et surtout si le but est de copier-coller dans un tableur, on voit bien que ça ne va pas vraiment le faire.

Résultat tabulaire obtenu en deux temps

Les autres solutions via requête SQL tournent autour de l'idée de procéder en deux temps:

1. une première requête construit le résultat avec toutes ses colonnes, et renvoie une référence indirecte à ce résultat.

2. une deuxième requête va ramener réellement le résultat, sa structure étant maintenant connue par le moteur SQL du fait de l'étape précédente.

A ce niveau, il faut rappeler qu'encapsuler ces deux étapes en une seule fonction annulerait l'intérêt de la solution: car pour appeler cette fonction en SQL, il faudrait obligatoirement spécifier avec une clause AS(...) toutes les colonnes du résultat, et dans ce cas autant utiliser crosstab().

La référence créée par la première étape peut être un curseur: dans ce cas la requête SQL est un appel de fonction prenant le même genre d'arguments que crosstab() mais renvoyant un REFCURSOR. La fonction créé dynamiquement une requête pivot, et instancie un curseur sur son résultat. Le code client peut alors parcourir ce résultat avec FETCH. C'est la solution mise en oeuvre dans la fonction dynamic_pivot() dont le code est un peu plus bas.

Autre variante: la requête SQL est un appel de fonction prenant le même genre d'arguments que crosstab() mais créant une vue dynamique ou une table, temporaire ou permanente, avec les données pivotées. Dans un second temps, le code client exécute un SELECT sur cette table ou vue, puis la supprime. Une implémentation en plpgsql pourrait être assez similaire à celle renvoyant un curseur, sauf qu'une fois établie la requête dynamique, on exécuterait CREATE [TEMPORARY] TABLE (ou VIEW) nom AS ... suivi de la requête.

Dans le code ci-dessous, je vous propose une fonction renvoyant un REFCURSOR qui peut être utilisée telle quelle, mais qui pourrait aussi servir de base pour une variante.

Ses arguments sont les mêmes que crosstab():

• une requête principale sortant 3 colonnes avant pivot dans l'ordre (catégorie, valeur à transposer, valeur centrale).
• une requête sortant la liste des colonnes dans l'ordre attendu.

La fonction instancie et renvoie un curseur contenant le résultat, lequel doit être consommé dans la même transaction (quoiqu'on pourrait le déclarer WITH HOLD si on voulait garder le résultat toute la session).

Malheureusement la requête principale doit être exécutée en interne deux fois par cette implémentation, car elle est incorporée en sous-requête dans deux requêtes totalement distinctes. De plus, le type des colonnes en sortie est forcé à text, faute de pouvoir accéder à l'information du type des données source en plpgsql. Une version en langage C lèverait potentiellement ces inconvénients, qui sont liés aux limitations du plpgsql (et encore, sans l'existence de row_to_json, ajoutée en version 9.2, je ne crois pas qu'il aurait été possible du tout de trouver les noms des colonnes comme le fait la première étape de la fonction). Quoiqu'il en soit, une version plpgsql a un avantage considérable ici: elle n'exige qu'une quarantaine de lignes de code pour faire ce travail, que voici:

CREATE FUNCTION dynamic_pivot(central_query text, headers_query text)
 RETURNS refcursor AS
$$
DECLARE
  left_column text;
  header_column text;
  value_column text;
  h_value text;
  headers_clause text;
  query text;
  j json;
  r record;
  curs refcursor;
  i int:=1;
BEGIN
  -- détermine les noms des colonnes de la source
  EXECUTE 'select row_to_json(_r.*) from (' ||  central_query || ') AS _r' into j;
  FOR r in SELECT * FROM json_each_text(j)
  LOOP
    IF (i=1) THEN left_column := r.key;
      ELSEIF (i=2) THEN header_column := r.key;
      ELSEIF (i=3) THEN value_column := r.key;
    END IF;
    i := i+1;
  END LOOP;

  -- génère dynamiquement la requête de transposition (sur le modèle canonique)
  FOR h_value in EXECUTE headers_query
  LOOP
    headers_clause := concat(headers_clause,
     format(chr(10)||',min(case when %I=%L then %I::text end) as %I',
           header_column,
	   h_value,
	   value_column,
	   h_value ));
  END LOOP;

  query := format('SELECT %I %s FROM (select *,row_number() over() as rn from (%s) AS _c) as _d GROUP BY %I order by min(rn)',
           left_column,
	   headers_clause,
	   central_query,
	   left_column);

  -- ouvre le curseur pour que l'appelant n'ait plus qu'à exécuter un FETCH
  OPEN curs FOR execute query;
  RETURN curs;
END 
$$ LANGUAGE plpgsql;

Exemple d'utilisation:

=> BEGIN;

-- étape 1: obtenir le curseur (le nom du curseur est généré par Postgres)
=> SELECT dynamic_pivot(
       'SELECT ville,annee,SUM(pluvio) 
          FROM pluviometrie GROUP BY ville,annee
          ORDER BY ville',
       'SELECT DISTINCT annee FROM pluviometrie ORDER BY 1'
     ) AS curseur
     \gset

-- étape 2: extraire les résultats du curseur
=> FETCH ALL FROM :"curseur";

   ville   | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 
-----------+------+------+------+------+------+------
 Ajaccio   | 69   | 91   | 78   | 48   | 81   | 51
 Bordeaux  | 116  | 138  | 137  | 101  | 117  | 110
 Brest     | 178  | 161  | 180  | 160  | 165  | 144
 Dijon     | 114  | 124  | 116  | 93   | 116  | 103
 Lille     | 153  | 120  | 136  | 128  | 138  | 113
 Lyon      | 112  | 116  | 111  | 80   | 110  | 102
 Marseille | 47   | 63   | 68   | 53   | 54   | 43
 Metz      | 98   | 120  | 110  | 93   | 122  | 115
 Nantes    | 124  | 132  | 142  | 111  | 106  | 110
 Nice      | 53   | 77   | 78   | 50   | 52   | 43
 Paris     | 114  | 111  | 113  | 85   | 120  | 110
 Perpignan | 48   | 56   | 54   | 48   | 69   | 48
 Toulouse  | 86   | 116  | 111  | 83   | 102  | 89
(13 lignes)

=> CLOSE :"curseur";

=> COMMIT;   -- libérera automatiquement le curseur si pas déjà fait par CLOSE.

Pivot par le code client

La couche de présentation côté client peut aussi se charger de transposer les lignes en colonnes, sur la base d'un jeu de résultat non pivoté. En effet certains voient la transposition comme une pure question de présentation, et pour l'essentiel c'est un point de vue qui se tient.

L'application psql propose une solution basée sur cette approche depuis la version 9.6, via la commande \crosstabview.

En usage interactif, cette méthode est la plus rapide pour obtenir des résultats immédiatement visibles.

Par exemple, admettons qu'on veuille examiner dans le cadre de notre exemple, les couples (ville,année) dépassant 120 jours de pluie:

=#  SELECT ville, annee, SUM(pluvio)
    FROM pluviometrie
    GROUP BY ville,annee 
    HAVING SUM(pluvio)>120
    ORDER BY annee
    \crosstabview

  ville   | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 
----------+------+------+------+------+------+------
 Brest    |  178 |  161 |  180 |  160 |  165 |  144
 Nantes   |  124 |  132 |  142 |      |      |     
 Lille    |  153 |      |  136 |  128 |  138 |     
 Dijon    |      |  124 |      |      |      |     
 Bordeaux |      |  138 |  137 |      |      |     
 Metz     |      |      |      |      |  122 |     

L'axe horizontal est alimenté par la 2eme colonne de la source mais pour avoir la transposition inverse, il suffit de mettre les colonnes annee ville en argument de crosstabview dans cet ordre, sans rien changer à la requête:

=# \crosstabview annee ville

 annee | Brest | Nantes | Lille | Dijon | Bordeaux | Metz 
-------+-------+--------+-------+-------+----------+------
  2012 |   178 |    124 |   153 |       |          |     
  2013 |   161 |    132 |       |   124 |      138 |     
  2014 |   180 |    142 |   136 |       |      137 |     
  2015 |   160 |        |   128 |       |          |     
  2016 |   165 |        |   138 |       |          |  122
  2017 |   144 |        |       |       |          |     

Ci-dessus on n'a pas de tri particulier des villes. Mais on peut trier ces colonnes, y compris sur un critère complexe, à travers le 4ème argument de la commande. Le code suivant trie les colonnes-villes par rang de pluviosité, en l'ajoutant comme 4ème colonne à la requête et à crosstabview:

=#  SELECT ville, annee, SUM(pluvio),
      rank() OVER (ORDER BY SUM(pluvio))
    FROM pluviometrie
    GROUP BY ville,annee 
    HAVING SUM(pluvio)>120
    ORDER BY annee
    \crosstabview annee ville sum rank

Résultat:

 annee | Metz | Dijon | Nantes | Bordeaux | Lille | Brest 
-------+------+-------+--------+----------+-------+-------
  2012 |      |       |    124 |          |   153 |   178
  2013 |      |   124 |    132 |      138 |       |   161
  2014 |      |       |    142 |      137 |   136 |   180
  2015 |      |       |        |          |   128 |   160
  2016 |  122 |       |        |          |   138 |   165
  2017 |      |       |        |          |       |   144

On voit que les nombres se répartissent maintenant de telle sorte qu'en lisant de gauche à droite on a des villes globalement de plus en plus pluvieuses, et notamment Brest le gagnant incontestable de ce jeu de données.

Comment dé-pivoter un jeu de données?

L'opération UNPIVOT existe dans certains dialectes SQL, mais pas dans PostgreSQL. On peut toutefois dé-pivoter facilement avec Postgres et de manière générique, c'est-à-dire sans liste explicite des colonnes, en passant par une représentation intermédiaire du jeu de données en JSON.

Imaginons par exemple que les données de pluviométrie se présentent comme ci-dessous, avec une colonne distincte par mois de l'année, type "tableur"

=> \d pluvmois

 Column |  Type   |
--------+---------+
 ville  | text    |
 annee  | integer |
 m1     | integer |
 m2     | integer |
 m3     | integer |
 m4     | integer |
 m5     | integer |
 m6     | integer |
 m7     | integer |
 m8     | integer |
 m9     | integer |
 m10    | integer |
 m11    | integer |
 m12    | integer |

En appliquant la fonction json_each_text à chaque ligne de la table mise au format JSON avec row_to_json, on va obtenir toutes les colonnes sous forme de tuples (key,value):

SELECT key, value FROM
  (SELECT row_to_json(t.*) AS line FROM pluvmois t) AS r
  JOIN LATERAL json_each_text(r.line) ON (true);

Pour avoir notre résultat final dé-pivoté, il ne reste plus qu'à enrichir cette requête pour garder l'année et la ville associée à chaque mesure, et filtrer et re-typer les colonnes de mois, comme ci-dessous:

SELECT
   r.ville,
   r.annee,
   substr(key,2)::int AS mois,  -- transforme 'm4' en 4
   value::int AS pluvio
 FROM (SELECT ville, annee, row_to_json(t.*) AS line FROM pluvmois t) AS r
  JOIN LATERAL json_each_text(r.line) ON (key ~ '^m[0-9]+');

Résultat:

   ville   | annee | mois | pluvio 
-----------+-------+------+--------
 Lille     |  2017 |    1 |      9
 Lille     |  2017 |    2 |     10
 Lille     |  2017 |    3 |      9
etc...
(936 lignes)

On retrouve bien nos 13 villes x 6 ans x 12 mois du jeu de données initial.

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

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

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

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

Ajoutons quelques données :

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

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

ANALYZE;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ajoutons une table pour pouvoir réaliser une jointure :

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

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

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

ANALYZE bills;

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

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

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

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

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

SET enable_nestloop = off;

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

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

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

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

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

Introduction

Une mise à jour de toutes les versions de PostgreSQL est sortie le 1er mars, motivée principalement par le CVE-2018-1058. Cette vulnérabilité ne correspond pas à un bug particulier dans le code, auquel cas on pourrait passer à autre chose dès nos instances mises à jour, mais à un problème plus général dans la gestion du schéma par défaut public où tout utilisateur peut écrire.

Concrètement un risque est présent dans les installations où des utilisateurs d’une même base ne se font pas confiance alors que le schéma public est utilisé avec ses droits par défaut et le search_path par défaut.

Le "core team" de PostgreSQL a dû estimer ce risque suffisant pour justifier d'une part des mesures de mitigation immédiates, et d'autre part une réflexion sur des changements plus étendus dans les prochaines versions. Dans l'immédiat, c'est surtout les outils pg_dump et pg_restore qui ont été modifiés (cf commit 3d2aed664), ainsi que la documentation (cf commit 5770172cb).

De quel risque s'agit-il exactement? Voyons un exemple d'abus du schéma public par un utilisateur malveillant au détriment d'un autre. A noter que les correctifs sortis dans les versions actuelles ne changent rien à cet exemple.

Exemple d'exploitation de la "faille"

Supposons deux utilisateurs d'une même base, au hasard, Alice et Bob. Chacun a une table, A et B, et peut lire celle de l'autre, mais pas y écrire.

Voici les ordres de création, passés dans le schéma public. Le fait que le champ nom soit de type varchar et non text est essentiel pour que la vulnérabilité soit exploitable, on verra pourquoi un peu plus bas.

Par Alice:

CREATE TABLE A(
   id serial,
   nom varchar(60),
   date_insert timestamptz default now()
);
GRANT SELECT ON A to bob;

Par Bob:

CREATE TABLE B(
   id serial,
   nom varchar(60),
   date_insert timestamptz default now()
);
GRANT SELECT ON B TO alice;

Un trigger se charge de mettre le nom entré en majuscules:

CREATE OR FUNCTION maj() RETURNS TRIGGER AS
'BEGIN
  NEW.nom := upper(NEW.nom);
  RETURN NEW;
END' LANGUAGE plpgsql;

CREATE TRIGGER tA BEFORE INSERT OR UPDATE ON A FOR EACH ROW EXECUTE PROCEDURE maj();
CREATE TRIGGER tB BEFORE INSERT OR UPDATE ON B FOR EACH ROW EXECUTE PROCEDURE maj();

En fonctionnement normal, Alice ajoute une entrée avec:

alice@test=> INSERT INTO A(nom) values('Nom du client');

et Bob a le droit de lire par exemple la dernière entrée d'Alice, via

bob@test=> SELECT * FROM A ORDER BY id DESC LIMIT 1;

Si Alice essaie d'effacer de l'autre table, le serveur refuse:

alice@test=> DELETE FROM b;
ERROR:  permission denied for table b

Jusque là tout est basique et normal.

Mais si Alice crée cette fonction upper() dans le schéma public:

CREATE FUNCTION upper(varchar) RETURNS varchar AS
  'delete from B; select pg_catalog.upper($1);'
LANGUAGE SQL;

lorsque l'utilisateur bob insèrera ou modifiera une ligne, l'appel dans la trigger upper(NEW.nom) trouvera la fonction d'Alice public.upper(varchar) et la prendra en priorité par rapport à pg_catalog.upper(text). Cette fonction "cheval de Troie" et son delete from b seront donc exécutés dans le contexte de la session de Bob et toutes les lignes pré-existantes de B se retrouveront effacées. Pwned!

Les deux axes de la résolution de nom de fonction

Si la colonne était de type text, c'est la fonction pg_catalog.upper(text) qui serait appelée au lieu de la fonction malveillante, parce que d'une part pg_catalog est inséré implicitement en tête de search_path, et que d'autre part la doc sur les conversions de type dans les appels de fonctions nous dit que

"If the search path finds multiple functions of identical argument types, only the one appearing earliest in the path is considered"

Mais comme varchar n'est pas text, c'est une partie différente de l'algorithme de résolution de nom de fonction qui s'active, celle qui va chercher le meilleur appariement entre les types de la déclaration de fonction et les types effectivement passés à l'appel. Dans notre cas, la règle suivante fait que pg_catalog n'est plus dominant:

Functions of different argument types are considered on an equal footing regardless of search path position

et c'est cette règle-ci qui domine:

Check for a function accepting exactly the input argument types. If one exists (there can be only one exact match in the set of functions considered), use it.

parce que la fonction d'Alice prend exactement un argument varchar alors que le upper(text) du système de base prend, comme sa signature l'indique, du type text.

En résumé, la faille utilise le fait que l'axe "conformité des types passés au types déclarés" est privilégié par rapport à l'axe du search_path. On peut présumer que cette règle ne pas être changée sans causer des problèmes de compatibilité plus dangereux que le problème lui-même, c'est pourquoi les développeurs n'ont pas choisi cette solution.

Les solutions

La solution préconisée est connue depuis que les schémas existent (version 7.3 sortie en 2002), et les modifications récentes à la documentation ne font, me semble-t-il, qu'insister dessus et la diffuser dans plusieurs chapitres. Dans notre scénario, un DBA devrait certainement faire:

REVOKE CREATE ON SCHEMA public FROM public;
CREATE SCHEMA alice AUTHORIZATION alice;
CREATE SCHEMA bob AUTHORIZATION bob;

voire éventuellement:

ALTER USER alice SET search_path="$user";
ALTER USER bob SET search_path="$user";

voire même:

DROP SCHEMA public;

Le problème est que ces mesures ne conviennent pas forcément à tout le monde, et ne pas avoir du tout le schéma public risquerait d'être fort perturbant pour beaucoup de programmes et utilisateurs existants.

Dans les versions déjà sorties et "corrigées" jusqu'à la 10, le schéma public garde ses propriétés, mais il n'est pas dit qu'il n'y aura pas de changement majeur à ce sujet dans la 11 ou la suivante (la version 11 étant déjà quasi-gelée pour de nouveaux patchs). Le fait que les développeurs aient choisi de considérer tout ça comme une vraie faille plutôt que de rejeter le problème sur l'utilisateur laisse à penser qu'ils ne laisseront pas le sujet en l'état.

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.

Dans un billet précédent, je mentionnais les différences entre objets larges et colonnes bytea pour stocker des données binaires, via un tableau d'une quinzaine de points de comparaison. Ici, détaillons un peu une de ces différences: les verrouillages induits par les opérations.

Effacement en masse

Pour le bytea, ça se passe comme avec les autres types de données de base. Les verrous au niveau ligne sont matérialisés dans les entêtes de ces lignes sur disque, et non pas dans une structure à part (c'est pourquoi ils ne sont pas visibles dans la vue pg_locks). En conséquence, il n'y pas de limite au nombre de lignes pouvant être effacées dans une transaction.

Mais du côté des objets larges, c'est différent. Une suppression d'objet via lo_unlink() est conceptuellement équivalent à un DROP, et prend un verrou en mémoire partagée, dans une zone qui est pré-allouée au démarrage du serveur. De ce fait, ces verrous sont limités en nombre, et si on dépasse la limite, on obtient une erreur de ce type:

ERROR: out of shared memory
HINT:  You might need to increase max_locks_per_transaction.

La documentation nous dit à propos de cette limite:

La table des verrous partagés trace les verrous sur max_locks_per_transaction * (max_connections + max_prepared_transactions) objets (c'est-à-dire des tables) ; de ce fait, au maximum ce nombre d'objets distincts peuvent être verrouillés simultanément.

les valeurs par défaut de ces paramètres étant (pour la version 10):

Ca nous donne 4096 verrous partagés par défaut. Même si on peut booster ces valeurs dans postgresql.conf, le maximum sera donc d'un ordre de grandeur peu élevé, disons quelques dizaines de milliers, ce qui peut être faible pour des opérations en masse sur ces objets. Et n'oublions pas qu'un verrou n'est libérable qu'à la fin de la transaction, et que la consommation de ces ressources affecte toutes les autres transactions de l'instance qui pourraient en avoir besoin pour autre chose.

Une parenthèse au passage, ce nombre d'objets verrouillables n'est pas un maximum strict, dans le sens où c'est une estimation basse. Dans cette question de l'an dernier à la mailing-liste: Maximum number of exclusive locks, j'avais demandé pourquoi en supprimant N objets larges, le maximum constaté pouvait être plus de deux fois plus grand que celui de la formule, avec un exemple à 37132 verrous prenables au lieu des 17920=(512*30+5) attendus. L'explication est qu'à l'intérieur de cette structure en mémoire partagée, il y a des sous-ensembles différents. Le nombre de verrous exclusifs réellement disponibles à tout moment dépend de l'attente ou non des verrous par d'autres transactions.

Pour être complet, disons aussi que le changement de propriétaire, via ALTER LARGE OBJECT nécessite aussi ce type de verrou en mémoire partagée, et qu'en revanche le GRANT … ON LARGE OBJECT … pour attribuer des permissions n'en a pas besoin.

Ecriture en simultané

Ce n'est pas ce qu'il y a de plus commun, mais on peut imaginer que deux transactions concurrentes veulent mettre à jour le même contenu binaire.

Dans le cas du bytea, la transaction arrivant en second est bloquée dans tous les cas sur le verrou au niveau ligne posé par un UPDATE qui précède. Et physiquement, l'intégralité du contenu va être remplacé, y compris si certains segments TOAST sont identiques entre l'ancien et le nouveau contenu.

Dans le cas de l'objet large, c'est l'inverse. Seul les segments concernés par le changement de valeur vont être remplacés par la fonction lowrite(). Rappelons la structure de pg_largeobject:

=# \d pg_largeobject
 Colonne |  Type   | Collationnement | NULL-able | Par défaut 
---------+---------+-----------------+-----------+------------
 loid    | oid     |                 | not null  | 
 pageno  | integer |                 | not null  | 
 data    | bytea   |                 | not null  | 
Index :
    "pg_largeobject_loid_pn_index" UNIQUE, btree (loid, pageno)

Chaque entrée de cette table représente le segment numéro pageno de l'objet large loid avec son contenu data d'une longueur maximale de 2048 octets (LOBLKSIZE).

Deux écritures concurrentes dans le même objet large vont se gêner seulement si elles modifient les mêmes segments.

Conclusion

Même si le stockage TOAST des colonnes bytea et les objets larges ont des structures très similaires, en pratique leurs stratégies de verrouillage sont quasiment opposées. L'effacement d'un bytea (DELETE) est une opération à verrouillage léger alors que l'effacement d'un objet large est plutôt comparable à un DROP TABLE. En revanche, la nature segmentée de l'objet large permet des modifications plus ciblées et légères en verrouillage, alors qu'une modification de colonne bytea induit un verrouillage (et remplacement) intégral de toute la ligne qui la porte.

On m'a demandé à corps et à cri (oui, c'est un brin exagéré mais merci Christophe quand même ) de détailler un peu les changements entrepris dans la deuxième édition de mon livre, « PostgreSQL, Architecture et notions avancées ». J'ai mis un peu de temps à retrouver ça et à en faire une liste intéressante et qui a du sens. Mais voilà, c'est fait.

J'aurais tendance à dire que le plus gros changement est l'arrivée de Julien Rouhaud en tant que co-auteur. Ça aide de ne pas être seul à la rédaction. Ça permet de s'assurer qu'on n'écrit pas trop de bêtises, ça permet de discuter sur certains changements, et chacun peut apporter ses connaissances. Bref, pour moi (et certainement pour les lecteurs aussi), c'est un changement très important.

Mais au niveau contenu ? principalement, comme indiqué sur le site de l'éditeur, les changements concernent les améliorations de la v10 de PostgreSQL. Pas que ça, mais en très grande majorité. Le reste, ce sont des retours de lecteurs, qui me permettent d'améliorer le livre, ce sont aussi des découvertes, soit par la lecture d'articles, soit par des missions en clientèle, qui me forcent à creuser certains aspects ou qui me forcent à m'interroger sur certains points.

Voici une liste détaillée des changements, chapitre par chapitre. Quand cela concerne, la v10, c'est indiqué explicitement.

Changements transversaux pour la v10 (ie, ça a touché pratiquement tous les chapitres)

• renommage des répertoires, des fonctions et des outils
• explications sur les changements concernant pg_ctl
• ajout des nouveaux paramètres
• ajout des nouvelles valeurs par défaut

Chapitre fichiers

• explications sur les nouvelles fonctions pg_ls_waldir, pg_ls_logdir et pg_current_logfile - v10
• explications sur le nouveau fichier current_logfile - v10
• explication sur la nouvelle ligne du fichier postmaster.pid - v10

Chapitre Contenu des fichiers

• meilleures explications de la partie TOAST
• explications sur la taille maximale d'une clé dans un index Btree
• explications sur les améliorations autour des index hash - v10
• meilleures explications sur les index BRIN
• indication de l'existence des index bloom
• ajout d'informations sur l'outil amcheck - v10
• nouveaux types d'enregistrement dans les WAL - v10

Chapitre mémoire

• meilleure description des structures de métadonnées autour du cache disque de PostgreSQL
• meilleure description de la structure Checkpointer Data
• meilleure description de la clé utilisée pour le sémaphore
• plus de précisions sur les Huge Pages

Chapitre connexions

• explication sur la nouvelle méthode d'authentification (scram) - v10

Chapitre transactions

• explications sur la nouvelle fonction txid_status
• explications sur les lignes affichées par un VACUUM VERBOSE

Chapitre objets

• explications sur le nouveau système d'import d'encodages - v10
• ajout d'une information sur l'emplacement des séquences par rapport aux tablespaces
• explications sur le partitionnement - v10
• explications sur la nouvelle contrainte (GENERATED AS IDENTITY) - v10
• explications sur les améliorations autour des index hash - v10
• explications sur les changements autour des séquences - v10
• explications sur les tables de transition - v10

Chapitre planification

• meilleure explication de la génération des différents plans
• meilleures explications sur les Bitmap Scans
• meilleures explications sur les plans parallélisés
• explications sur les nouveautés en terme de parallélisation (Parallel Index Scan, Gather Merge) - v10
• explications pour les nouveaux noeuds Table Function Scan et Project Set - v10
• meilleures explications sur le fonctionnement du noeud Limit
• explications sur la nouvelle option de la commande EXPLAIN - v10
• explications sur les statistiques étendues - v10
• amélioration des explications sur GeQO
• ajout d'une section sur l'exécuteur

Chapitre sauvegarde

• explications sur les nouvelles options des outils pg_dump/pg_dumpall/pg_restore - v10
• explications sur la nouvelle cible de restauration (recovery_target_lsn) - v10

Chapitre réplication

• explications sur la réplication logique - v10
• explications sur le quorum de réplication - v10

Chapitre sécurité

• ajout d'un message exemple sur la découverte de corruption d'un bloc
• explications sur les nouveaux rôles créés par défaut - v10

Chapitre statistiques

• explications des changements sur la vue pg_stat_activity, pg_stat_replication, pg_stat_statements - v10
• explications sur le risque de réinitialisation des statistiques sur les tables (blocage de l'autovacuum)

Il manque évidemment certains chapitres (comme celui sur le protocole de communication et celui sur la maintenance). Cette absence ne signifie pas qu'il n'y a rien eu de fait sur ces chapitres, mais plutôt que, si changements il y a eu, ils ne sont pas majeurs.

Pour les lecteurs ayant acheté la première édition en version numérique sur le site des éditions D-BookeR, ils profitent automatiquement (ie, gratuitement) de la 2e édition. Il leur suffit de retélécharger le livre ! (comme pour les versions 1.1 à 1.4). Ceux qui voudraient acquérir la deuxième édition ont vraiment intérêt à passer par le site de l'éditeur pour profiter eux-aussi des prochaines mises à jour.

Parce que, oui, l'aventure ne se termine pas là. Je continue à travailler sur le livre. J'espère que Julien en fera de même. Notamment, sa connaissance du code de PostgreSQL a été très bénéfique et il est très agréable de bosser avec lui. Au niveau des prochaines améliorations, je pense que le chapitre sur les processus a été un peu oublié et devrait être revu. On avait déjà eu ce problème lors de la mise à jour du livre pour la version 9.6, on l'a de nouveau. Il va donc falloir que je recreuse cette question des processus. Et puis j'espère que sortira un jour une troisième édition. Je ferais tout pour en tout cas.

En attendant ça, bonne lecture. Et sachez que je suis toujours preneur de vos retours. Ça aide vraiment beaucoup à améliorer le livre (et donc ça vous aide ).

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

J'ai enfin terminé le merge du manuel de la version 10. Je n'aime pas ce boulot mais il est nécessaire pour pouvoir organiser la traduction. Bref, c'est fait, et on peut commencer le boulot intéressant, à savoir la traduction. Pour les intéressés, c'est par là : https://github.com/gleu/pgdocs_fr/wiki/Traduction-v10

N'hésitez pas à m'envoyer toute question si vous êtes intéressé pour participer.

Un événement communautaire

Cette année encore 2ndQuadrant aide la communauté PostgreSQL en France en étant Partenaire du pgDay 2017 à Paris

Cette journée de conférences, en anglais exclusivement, est une opportunité unique d’en apprendre plus sur le fonctionnement et l’activité de PostgreSQL. Sécurité, benchmarks, supervision, roadmap pour la version 10, réplication, … de nombreux sujets, variés et d’actualité. A noter qu’il n’est pas nécessaire de savoir lire Shakespeare dans la langue pour comprendre ce qu’il va se dire, c’est donc également une bonne occasion de renforcer votre anglais technique!

2ndQuadrant tient à remercier Vik Fearing pour sa très forte implication dans l’organisation de cet événement communautaire appuyé par l’association Européenne.

pgDay 2017 à Paris, soutenu par 2ndQuadrant

Simon Riggs, committer PostgreSQL et CTO de 2ndQuadrant, viendra y présenter les dernières évolutions dans le domaine de la réplication: un travail qu’il a commencé avec la version 8.0 et qui nous permet aujourd’hui d’adresser des besoins très importants de réplication multi-master (BDR) ou de réplication logique.

Venez nombreux le 23 mars pour découvrir tout cela et rencontrer en personne les experts 2ndQuadrant.

S’enregistrer et venir au pgDay 2017

Pour s’enregistrer, il est nécessaire d’avoir un compte communautaire, que cela ne vous arrête pas: il s’agit d’un compte simple à créer et qui vous permet aussi d’éditer le wiki de PostgreSQL! Laissez-vous guider depuis: http://www.pgday.paris/registration/

Les conférences ont lieu de 9h à 18h au 2^bis rue Mercœur dans le 11^ème, entre métro Charonne et Voltaire, facile à trouver: voir la carte Open Street Map

Mon livre, PostgreSQL - Architecture et notions avancées, a été écrit pendant le développement de la version 9.5. Il est sorti en version finale un peu après la sortie de la version 9.5, donc en plein développement de la 9.6. À la sortie de la version beta de la 9.6, je me suis mis à travailler sur une mise à jour du livre, incluant des corrections suite à des commentaires qui m'étaient parvenus, ainsi que de nombreux ajouts pour traiter les nouveautés de la version 9.6. Le résultat final est disponible depuis hier. Comme il ne s'agit pas d'une nouvelle édition complète, il est disponible gratuitement en téléchargement aux personnes qui ont acheté une version électronique précédente. Ceux qui commanderaient maintenant la version papier aurait cette nouvelle version, intitulée 1.3.

Évidemment, nous allons continuer la mise à jour du livre pour qu'il ne perde pas en intérêt. Une (vraie) deuxième édition sera disponible en fin d'année prochaine, après la sortie de la version 10. Cette version promet de nombreuses nouveautés très intéressantes, comme tout dernièrement un partitionnement mieux intégré dans le cœur de PostgreSQL.

pgFincore 1.2 est une extension PostgreSQL pour auditer et manipuler le cache de pages de données du système d’exploitation. L’extension a déjà une histoire de 7 ans d’utilisation, avec des évolutions correspondant aux besoins de production.

Télécharger ici la dernière version 1.2, compatible avec PostgreSQL 9.6.

Cache de données

Le cache de pages de données est une opération qui se réalise «naturellement», à plusieurs niveaux dans la gestion des données. L’objet est simple: une multitude de couches se superposent entre les données physiquement enregistrées sur disque et la restitution à l’utilisateur. Actuellement quasiment chaque couche de données possède une abstraction pour servir plus rapidement des ordres de lecture et d’écriture. Ainsi la majorité des disques durs proposent un cache en écriture, qui permet de retarder l’écriture physique, et un cache en lecture qui permet d’anticiper sur des prochaines demandes et servir des données plus rapidement. Un système équivalent existe dans les SAN, les cartes RAID, les système d’exploitation, les logiciels, etc.

PostgreSQL possède bien sûr son propre système de gestion pour les écritures et les lectures, les shared buffers, que l’on peut auditer avec l’extension pg_buffercache.

Il est possible d’auditer le cache du système d’exploitation avec des outils systèmes, et pgFincore porte cela dans PostgreSQL.

Read Ahead

La plupart des systèmes d’exploitation optimisent les parcours de données en proposant une fenêtre de lecture en avance, cela permet de pré-charger des données dans le cache et ainsi les fournir plus rapidement aux applications. PostgreSQL contient plusieurs optimisations pour favoriser ce comportement au niveau système, et porte également une fonctionnalité similaire avec l’option effective_io_concurrency.

Une solution pour faciliter ces optimisations consiste à utiliser des appels systèmes POSIX_FADVISE. Là-aussi pgFincore porte cette solution dans PostgreSQL.

pgFincore 1.2

Cette extension permet donc:

• d’obtenir des informations précises sur l’occupation d’une table ou d’un index (et quelques autres fichiers utilisés par PostgreSQL) dans le cache du système supportant POSIX (linux, BSD, …),
• de manipuler ce cache: en faire une carte et la restaurer ultérieurement ou sur un autre serveur,
• d’optimiser les parcours via les appels posix_fadvise.

Obtenir pgFincore

Paquets Debian et Red Hat disponibles dans les distributions, et pour chaque version de PostgreSQL sur les dépôts Apt PGDG et RPM PGDG.

Et les sources sur le dépôt git pgfincore.

Besoin d’aide ?

En plus du support communautaire, vous pouvez contacter 2ndQuadrant.

Exemples d’utilisation

Installation

$ sudo apt-get install postgresql-9.6-pgfincore
$ psql -c 'CREATE EXTENSION pgfincore;'

Information système

# select * from pgsysconf_pretty();
 os_page_size | os_pages_free | os_total_pages 
--------------+---------------+----------------
 4096 bytes   | 314 MB        | 16 GB

Optimiser le parcours aléatoire (réduction de la fenêtre de read-ahead)

# select * from pgfadvise_random('pgbench_accounts_pkey');
          relpath | os_page_size | rel_os_pages | os_pages_free 
------------------+--------------+--------------+---------------
 base/16385/24980 | 4096         | 2            | 1853808

Optimiser le parcours séquentiel (augmentation de la fenêtre de read-ahead)

# select * from pgfadvise_sequential('pgbench_accounts');
 relpath          | os_page_size | rel_os_pages | os_pages_free 
------------------+--------------+--------------+---------------
 base/16385/25676 | 4096         | 3176         | 1829288

Audit du cache

# select * from pgfincore('pgbench_accounts');
      relpath       | segment | os_page_size | rel_os_pages | pages_mem | group_mem | os_pages_free | databit 
--------------------+---------+--------------+--------------+-----------+-----------+---------------+---------
 base/11874/16447   |       0 |         4096 |       262144 |         3 |         1 |        408444 | 
 base/11874/16447.1 |       1 |         4096 |        65726 |         0 |         0 |        408444 | 

Charger une table en mémoire

# select * from pgfadvise_willneed('pgbench_accounts');
      relpath       | os_page_size | rel_os_pages | os_pages_free 
--------------------+--------------+--------------+---------------
 base/11874/16447   |         4096 |       262144 |         80650
 base/11874/16447.1 |         4096 |        65726 |         80650

Vider le cache d’une table

# select * from pgfadvise_dontneed('pgbench_accounts');
      relpath       | os_page_size | rel_os_pages | os_pages_free
--------------------+--------------+--------------+---------------
 base/11874/16447   |         4096 |       262144 |        342071
 base/11874/16447.1 |         4096 |        65726 |        408103

Restaurer des pages en cache

On utilise ici un paramètre de type bit-string représentant les pages à charger et décharger de la mémoire.

# select * 
  from pgfadvise_loader('pgbench_accounts', 0, true, true, 
                       B'101001'); -- Varbit décrivant les pages à manipuler
     relpath      | os_page_size | os_pages_free | pages_loaded | pages_unloaded 
------------------+--------------+---------------+--------------+----------------
 base/11874/16447 |         4096 |        408376 |            3 |              3

NOTE: pour la démo, seul 6 pages de données sont manipulées ci-dessus, 1 charge la page, 0 décharge la page.

J'ai passé beaucoup de temps ces derniers temps sur la traduction du manuel de PostgreSQL.

• mise à jour pour les dernières versions mineures
• corrections permettant de générer les PDF déjà disponibles (9.1, 9.2, 9.3 et 9.4) mais aussi le PDF de la 9.5
• merge pour la traduction de la 9.6

Elles sont toutes disponibles sur le site docs.postgresql.fr.

De ce fait, la traduction du manuel de la 9.6 peut commencer. Pour les intéressés, c'est par là : https://github.com/gleu/pgdocs_fr/wiki/Translation-9.6

N'hésitez pas à m'envoyer toute question si vous êtes intéressé pour participer.

Beaucoup de retard pour cette fois, mais malgré tout, on a fini la traduction du manuel 9.5 de PostgreSQL. Évidemment, tous les manuels ont aussi été mis à jour avec les dernières versions mineures.

N'hésitez pas à me remonter tout problème sur la traduction.

De même, j'ai pratiquement terminé la traduction des applications. Elle devrait être disponible pour la version 9.5.2 (pas de date encore connue).

J'ai enfin fini le merge du manuel de la version 9.5. Très peu de temps avant la 9.5, le peu de temps que j'avais étant consacré à mon livre. Mais là, c'est bon, on peut bosser. D'ailleurs, Flavie a déjà commencé et a traduit un paquet de nouveaux fichiers. Mais il reste du boulot. Pour les intéressés, c'est par là : https://github.com/gleu/pgdocs_fr/wiki/Translation-9.5

N'hésitez pas à m'envoyer toute question si vous êtes intéressé pour participer.

Après pratiquement deux ans de travail, mon livre est enfin paru. Pour être franc, c'est assez étonnant de l'avoir entre les mains : un vrai livre, avec une vraie reliure et une vraie couverture, écrit par soi. C'est à la fois beaucoup de fierté et pas mal de questionnements sur la façon dont il va être reçu.

Ceci étant dit, sans savoir si le livre sera un succès en soi, c'est déjà pour moi un succès personnel. Le challenge était de pouvoir écrire un livre de 300 pages sur PostgreSQL, le livre que j'aurais aimé avoir entre les mains quand j'ai commencé à utiliser ce SGBD il y a maintenant plus de 15 ans sous l'impulsion de mon ancien patron.

Le résultat est à la hauteur de mes espérances et les premiers retours sont très positifs. Ce livre apporte beaucoup d'explications sur le fonctionnement et le comportement de PostgreSQL qui, de ce fait, n'est plus cette espèce de boîte noire à exécuter des requêtes. La critique rédigée par Jean-Michel Armand dans le GNU/Linux Magazine France numéro 190 est vraiment très intéressante. Je suis d'accord avec son auteur sur le fait que le début est assez ardu : on plonge directement dans la technique, sans trop montrer comment c'est utilisé derrière, en production. Cette partie-là n'est abordée qu'après. C'est une question que je m'étais posée lors de la rédaction, mais cette question est l'éternel problème de l'oeuf et de la poule ... Il faut commencer par quelque chose : soit on explique la base technique (ce qui est un peu rude), puis on finit par montrer l'application de cette base, soit on fait l'inverse. Il n'y a certainement pas une solution meilleure que l'autre. Le choix que j'avais fait me semble toujours le bon, même maintenant. Mais en effet, on peut avoir deux façons de lire le livre : en commençant par le début ou en allant directement dans les chapitres thématiques.

Je suis déjà prêt à reprendre le travail pour proposer une deuxième édition encore meilleure. Cette nouvelle édition pourrait se baser sur la prochaine version majeure de PostgreSQL, actuellement numérotée 9.6, qui comprend déjà des nouveautés très excitantes. Mais cette édition ne sera réellement intéressante qu'avec la prise en compte du retour des lecteurs de la première édition, pour corriger et améliorer ce qui doit l'être. N'hésitez donc pas à m'envoyer tout commentaire sur le livre, ce sera très apprécié.

Elle n'est pas encore sortie. Elle est pratiquement terminée, on attend d'avoir le livre en version imprimée.

Néanmoins, je peux déjà dire les nouveautés par rapport à la beta 0.4 :

• Global
  • mise à jour du texte pour la 9.5
  • ajout du chapitre sur la sécurité
  • ajout du chapitre sur la planification
  • mise à jour des exemples avec PostgreSQL 9.5 beta 1
• Fichiers
  • Ajout d'un schéma sur les relations entre tables, FSM et VM
  • Ajout de la description des répertoires pg_dynshmem et pg_logical
• Contenu des fichiers
  • Ajout d'informations sur le stockage des données, colonne par colonne
  • Ajout d'un schéma sur la structure logique et physique d'un index B-tree
  • Ajout de la description des index GIN
  • Ajout de la description des index GiST
  • Ajout de la description des index SP-GiST
• Architecture mémoire
  • calcul du work_mem pour un tri
  • calcul du maintenance_work_mem pour un VACUUM
• Gestion des transactions
  • Gestion des verrous et des accès concurrents
• Maintenance
  • Description de la sortie d'un VACUUM VERBOSE

J'avoue que j'ai hâte d'avoir la version finale entre mes mains Bah, oui, c'est quand même 1 an et demi de boulot acharné !

La dernière beta datait de mi-mai. Beaucoup de choses se sont passées pendant les 4 mois qui ont suivi. Quatre nouveaux chapitres sont mis à disposition :

• Sauvegarde
• Réplication
• Statistiques
• Maintenance

Mais ce n'est évidemment pas tout. Dans les nouveautés importantes, notons :

• Chapitres Fichiers, Processus et Mémoire
  • Ajout des schémas disques/processus/mémoire
• Chapitre Contenu physique des fichiers
  • Déplacement des informations sur le contenu des journaux de transactions dans ce chapitre
  • Ajout de la description du contenu d'un index B-tree
  • Ajout de la description du contenu d'un index Hash
  • Ajout de la description du contenu d'un index BRIN
  • Restructuration du chapitre dans son ensemble
• Chapitre Architecture des processus
  • Ajout de sous-sections dans la description des processus postmaster et startup
  • Ajout d'un exemple sur la mort inattendue d'un processus du serveur PostgreSQL
• Chapitre Architecture mémoire
  • Ajout de plus de détails sur la mémoire cache (shared_buffers)
• Chapitre Gestion des transactions
  • Ajout d'informations sur le CLOG, le FrozenXid et les Hint Bits
• Chapitre Gestion des objets
  • Ajout d'une section sur les options spécifiques des vues et fonctions pour la sécurité
  • Ajout d'un paragraphe sur le pseudo-type serial
• Divers
  • Mise à jour des exemples avec PostgreSQL 9.4.4

Bref, c'est par ici.

Quant à la prochaine version ? cela devrait être la version finale. Elle comportera le chapitre Sécurité (déjà écrit, en cours de relecture) et le chapitre sur le planificateur de requêtes (en cours d'écriture). Elle devrait aussi disposer d'une mise à jour complète concernant la version 9.5 (dont la beta devrait sortir début octobre).

Bonne lecture et toujours intéressé pour savoir ce que vous en pensez (via la forum mis en place par l'éditrice ou via mon adresse email).

Ce billet fait partie d'une série sur l'écriture de mon livre, « PostgreSQL - Architecture et notions avancées ».

Je suis en train d'écrire le chapitre sur la maintenance. Parmi les opérations de maintenance se trouve l'ordre VACUUM. Beaucoup de choses ont déjà été écrites dans le livre sur le VACUUM mais j'avais bizarrement oublié une chose. Une bonne configuration du paramètre maintenance_work_mem permet d'avoir un VACUUM performant. Mais comment peut-on savoir que la valeur du maintenance_work_mem est suffisante ?

J'ai donc creusé hier soir dans les sources de PostgreSQL à la recherche de ce qui est stocké dans cette mémoire. Tout se trouve dans src/backend/commands/vacuumlazy.c, principalement dans la fonction lazy_space_alloc(). En gros, PostgreSQL y place un tableau de la structure ItemPointerData. Cette structure prend six octets. Donc une estimation (très grosse) serait de dire qu'on peut stocker maintenance_work_mem/6 positions d'enregistrements morts dans cette mémoire. Un patch rapide (voir le fichier joint) nous prouve cette théorie :

Nous plaçons le paramètre client_min_messages au niveau log pour voir les traces ajoutées par le patch :

postgres=# SET client_min_messages TO log;
SET

Nous créons la table et désactivons l'autovacuum sur cette table pour le gérer nous-même :

postgres=# DROP TABLE IF EXISTS t1;
DROP TABLE
postgres=# CREATE TABLE t1(id INTEGER PRIMARY KEY);
CREATE TABLE
postgres=# ALTER TABLE t1 SET (autovacuum_enabled = OFF);
ALTER TABLE

Nous insérons un million de lignes, puis en supprimons 900000 :

postgres=# INSERT INTO t1 SELECT generate_series(1, 1000000);
INSERT 0 1000000
postgres=# DELETE FROM t1 WHERE id<900000;
DELETE 899999

Nous configurons maintenance_work_mem à 1 Mo (en fait, suffisamment petit pour voir que le VACUUM a besoin de plusieurs passes dû au manque de mémoire) :

postgres=# SET maintenance_work_mem TO '1MB';
SET
postgres=# VACUUM t1;
LOG:  patch - vac_work_mem: 1024
LOG:  patch - sizeof(ItemPointerData): 6
LOG:  patch - maxtuples: 174762
LOG:  patch - step 1
LOG:  patch - step 2
LOG:  patch - step 3
LOG:  patch - step 4
LOG:  patch - step 5
LOG:  patch - step 6
VACUUM

La fonction de calcul de la taille mémoire a bien noté le maintenance_work_mem à 1 Mo (1024 Ko). La taille de la structure est bien de 6 octets. Il est donc possible de stocker 1024*1024/6 enregistrements, soit 174762 enregistrements. Ayant supprimé 900000 enregistrements, il me faut 6 passes (l'arrondi supérieur de l'opération 900000/174762) pour traiter la table entière. Pas efficace.

Essayons dans les mêmes conditions mais avec un maintenance_work_mem trois fois plus gros :

postgres=# TRUNCATE t1;
TRUNCATE TABLE
postgres=# INSERT INTO t1 SELECT generate_series(1, 1000000);
INSERT 0 1000000
postgres=# DELETE FROM t1 WHERE id<900000;
DELETE 899999
postgres=# SET maintenance_work_mem TO '3MB';
SET
postgres=# VACUUM t1;
LOG:  patch - vac_work_mem: 3072
LOG:  patch - sizeof(ItemPointerData): 6
LOG:  patch - maxtuples: 524288
LOG:  patch - step 1
LOG:  patch - step 2
VACUUM

Nous ne faisons plus que deux passes (tout d'abord 524288 enregistrements, puis 375711), c'est plus efficace mais non optimal.

Essayons maintenant avec le maintenance_work_mem de base (64 Mo) :

postgres=# TRUNCATE t1;
TRUNCATE TABLE
postgres=# INSERT INTO t1 SELECT generate_series(1, 1000000);
INSERT 0 1000000
postgres=# DELETE FROM t1 WHERE id<900000;
DELETE 899999
postgres=# RESET maintenance_work_mem;
RESET
postgres=# VACUUM VERBOSE t1;
INFO:  vacuuming "public.t1"
LOG:  patch - vac_work_mem: 65536
LOG:  patch - sizeof(ItemPointerData): 6
LOG:  patch - maxtuples: 1287675
LOG:  patch - step 1
VACUUM

Seule une passe est réalisée. Il est à noter que la mémoire prise ne correspond pas au 64 Mo. 64 Mo me permet de stocker 11 millions d'enregistrements morts, mais je n'ai dans la table que 1000000 d'enregistrements dont 90% est mort. Autrement dit, j'ai besoin de beaucoup moins de mémoire. C'est bien le cas ici où, au lieu de 11 millions d'enregistrements, on peut en stocker 1287675 (soit un peu plus de 7 Mo).

De tout ça, comment puis-je savoir si mon maintenance_work_mem est bien configuré ? Il faut se baser sur le nombre d'enregistrements (morts) contenus dans les tables. Ça correspond à cette requête pour les tables de ma base de connexion :

SELECT pg_size_pretty(max(n_dead_tup*6)) AS custom_maintenance_work_mem
FROM pg_stat_all_tables;

Dans l'exemple précédent, cela me donnerait ceci :

postgres=# TRUNCATE t1;
TRUNCATE TABLE
postgres=# INSERT INTO t1 SELECT generate_series(1, 1000000);
INSERT 0 1000000
postgres=# DELETE FROM t1 WHERE id<900000;
DELETE 899999
postgres=# SELECT pg_size_pretty(max(n_dead_tup*6)) AS custom_maintenance_work_mem,
           current_setting('maintenance_work_mem') AS current_maintenance_work_mem
          FROM pg_stat_all_tables;

 custom_maintenance_work_mem | current_maintenance_work_mem 
-----------------------------+------------------------------
 5273 kB                     | 64MB
(1 row)

Il me faut au minimum 5,2 Mo. Je suis donc tranquille.

Évidemment, le nombre d'enregistrements morts évolue dans le temps et il est tout à fait possible que la quantité de mémoire nécessaire soit bien plus importante. On peut se baser sur le nombre d'enregistrements total pour avoir le pire des cas comme ici :

b1=# SELECT pg_size_pretty(max((n_live_tup+n_dead_tup)*6)) AS custom_maintenance_work_mem,
     current_setting('maintenance_work_mem') AS current_maintenance_work_mem
     FROM pg_stat_all_tables;

 custom_maintenance_work_mem | current_maintenance_work_mem 
-----------------------------+------------------------------
 472 MB                      | 512MB
(1 row)

Ce qui révèle donc une configuration adéquate pour cet utilisateur.

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.

Je me suis rendu compte ce week-end qu'on n'avait pas publié d'informations sur ce qui avait été ajouté entre les différentes versions beta, en dehors des nouveaux chapitres. Voici donc la liste des modifications, un peu éditée pour être plus lisible :

Pour la beta2 :

• Nouveaux chapitres
  • protocole de communication
  • connexions
• Chapitre fichiers
  • ajout d'une note sur l'option --no-clean de la commande initdb
  • ajout d'une note sur les versions 9.1 (et inférieures) et la colonne spclocation du catalogue pg_tablespace
  • ajout d'une note sur le fichier pgstat.stat des versions 9.3 et antérieures
• Chapitre processus
  • refonte des sections pour trier les processus par activité (et non par nom)
  • revue du résumé sur les processus d'écriture dans les fichiers de données suite à une remarque d'un lecteur dans le forum du livre (forum uniquement accessible par les lecteurs actuels)
  • correction des processus équivalents au niveau Oracle, suite là-aussi à un autre commentaire d'un lecteur
• Chapitre mémoire
  • ajout de deux paragraphes sur l'utilisation (partielle) du cache pour les requêtes
  • présentation de deux colonnes de la vue pg_stat_database permettant de quantifier l'utilisation des fichiers temporaires

Et pour la beta 3 :

• Nouveaux chapitres
  • gestion des objets
  • transactions
• Global
  • remplacement du terme maître par serveur primaire et du terme esclave par serveur secondaire (suite à la demande d'un lecteur)
• Chapitre mémoire
  • ajout d'une note sur l'intérêt du paramètre maintenance_work_mem dans le cadre d'un import de données avec pg_restore
• Chapitre processus
  • ajout d'une partie sur les écritures dans les fichiers de données suite à un CHECKPOINT, avec quelques graphes, pour mieux expliquer les différents paramètres checkpoint_*
• Chapitre Fichiers
  • ajout d'un paragraphe sur la génération des relfilnode
  • ajout d'infos sur le LSN et les journaux de transactions

Je publierais dans ce blog les nouveautés de chaque version beta à chaque sortie, ce sera plus facile pour les lecteurs.

Ce billet fait partie d'une série sur l'écriture de mon livre, « PostgreSQL - Architecture et notions avancées ».

Et voilà, deux nouveaux chapitres écrits, dont un sur le système transactionnel de PostgreSQL. Ce dernier m'a demandé d' étudier plus attentivement le travail de l'opération VACUUM. J'en connaissais le principe et son fonctionnement, à savoir un fonctionnement en trois phases : recherche des éléments à flagguer comme invisibles, suppression de ces éléments dans les index, puis suppression dans la table (pas physiquement). Cependant, je ne l'avais pas regardé plus précisément.

J'ai donc lu le code, puis écrit un petit patch pour mieux suivre cela (disponible en pièce jointe). J'ai exécuté un script SQL pour visualiser différents comportements. Par exemple, on ajoute dix lignes dans une nouvelle table, puis on met à jour une ligne sur trois, et enfin on exécute un VACUUM sur cette table :

CREATE TABLE t2(c1 integer);
ALTER TABLE t2 SET (autovacuum_enabled=off);
INSERT INTO t2 SELECT generate_series(1, 10);
UPDATE t2 SET c1=-c1 where c1%3=1;
SET client_min_messages to log;
VACUUM t2;

Voici le log fourni par le patch :

psql:script.sql:9: LOG:  patch - vacuum.c - vacuum[117] - VACUUM on 0, toast included, no wraparound
psql:script.sql:9: LOG:  patch - vacuum.c - vacuum[249] - vacuuming 82231
psql:script.sql:9: LOG:  patch - vacuum.c - vacuum_rel[1207] - relation t2 (82231) opened with lockmode 4
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_rel[194] - vacuuming...
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[465] - relation has 1 blocks
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 0
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 4 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 1 is now REDIRECTed to item 11
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 4 is now REDIRECTed to item 12
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 7 is now REDIRECTed to item 13
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 10 is now REDIRECTed to item 14
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (10 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 320)

Il n'y a là qu'une seule étape exécutée. En effet, dû au très petit nombre de lignes dans la table, le seul bloc de 8 Ko n'a pas été entièrement occupé. Du coup, PostgreSQL place les nouvelles versions des lignes mises à jour dans le même bloc que les anciennes versions et utilise le Heap Over Tuple pour lier les enregistrements. De plus, comme il n'y a pas d'index, pas besoin de les mettre à jour.

Maintenant, faisons la même chose avec 400 lignes (en fait, suffisamment pour remplir plus d'un bloc). Les logs sont beaucoup plus importants.

psql:script.sql:9: LOG:  patch - vacuum.c - vacuum[117] - VACUUM on 0, toast included, no wraparound
psql:script.sql:9: LOG:  patch - vacuum.c - vacuum[249] - vacuuming 82234
psql:script.sql:9: LOG:  patch - vacuum.c - vacuum_rel[1207] - relation t2 (82234) opened with lockmode 4
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_rel[194] - vacuuming...
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[465] - relation has 3 blocks
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 0
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 76 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 1 is now DEAD
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 4 is now DEAD
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 223 is now DEAD
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 226 is now DEAD
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (150 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 4800)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 1 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 4 DEAD
[...]
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 223 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 226 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1237] - block 0
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 1 is now UNUSED
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 4 is now UNUSED
[...]
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 223 is now UNUSED
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 226 is now UNUSED
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (150 have storage, 76 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 4800)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 1
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 58 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 3 is now DEAD
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 6 is now DEAD
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 171 is now DEAD
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 174 is now DEAD
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (168 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 5376)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 3 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 6 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 9 DEAD
[...]
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 171 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 174 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1237] - block 1
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 3 is now UNUSED
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 6 is now UNUSED
[...]
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 171 is now UNUSED
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 174 is now UNUSED
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (168 have storage, 58 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 5376)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 2
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 0 deleted items found

Chaque élément devenu invisible est déclaré DEAD lors de la première étape, puis UNUSED à la troisième étape.

Il est dit que le fillfactor permet d'augmenter l'utilisation du Heap Over Tuple. Voici ce que cela donne avec un fillfactor à 90% :

psql:script.sql:9: LOG:  patch - vacuum.c - vacuum[117] - VACUUM on 0, toast included, no wraparound
psql:script.sql:9: LOG:  patch - vacuum.c - vacuum[249] - vacuuming 82237
psql:script.sql:9: LOG:  patch - vacuum.c - vacuum_rel[1207] - relation t2 (82237) opened with lockmode 4
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_rel[194] - vacuuming...
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[465] - relation has 3 blocks
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 0
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 68 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 1 is now REDIRECTed to item 205
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 4 is now REDIRECTed to item 206
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 61 is now REDIRECTed to item 225
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 64 is now REDIRECTed to item 226
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 67 is now DEAD
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 70 is now DEAD
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 199 is now DEAD
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 202 is now DEAD
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (158 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 5056)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 67 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 70 DEAD
[...]
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 199 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 202 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1237] - block 0
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 67 is now UNUSED
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 70 is now UNUSED
[...]
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 199 is now UNUSED
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 202 is now UNUSED
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (158 have storage, 46 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 5056)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 1
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 66 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 1 is now REDIRECTed to item 205
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 4 is now REDIRECTed to item 206
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 61 is now REDIRECTed to item 225
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 64 is now REDIRECTed to item 226
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 67 is now DEAD
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 70 is now DEAD
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 193 is now DEAD
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[702] - item 196 is now DEAD
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (160 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 5120)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 67 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 70 DEAD
[...]
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 193 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[817] - item 196 DEAD
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1237] - block 1
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 67 is now UNUSED
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 70 is now UNUSED
[...]
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 193 is now UNUSED
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_page[1250] - item 196 is now UNUSED
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (160 have storage, 44 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 5120)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 2
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 0 deleted items found

Certains enregistrements bénéficient de HOT, mais la majorité deviennent UNUSED. Essayons avec un fillfactor de 50% :

psql:script.sql:9: LOG:  patch - vacuum.c - vacuum[117] - VACUUM on 0, toast included, no wraparound
psql:script.sql:9: LOG:  patch - vacuum.c - vacuum[249] - vacuuming 82240
psql:script.sql:9: LOG:  patch - vacuum.c - vacuum_rel[1207] - relation t2 (82240) opened with lockmode 4
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_vacuum_rel[194] - vacuuming...
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[465] - relation has 4 blocks
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 0
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 38 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 1 is now REDIRECTed to item 114
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 4 is now REDIRECTed to item 115
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 109 is now REDIRECTed to item 150
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 112 is now REDIRECTed to item 151
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (113 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 3616)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 1
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 38 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 2 is now REDIRECTed to item 114
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 5 is now REDIRECTed to item 115
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 110 is now REDIRECTed to item 150
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 113 is now REDIRECTed to item 151
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (113 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 3616)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 2
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 37 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 3 is now REDIRECTed to item 114
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 6 is now REDIRECTed to item 115
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 108 is now REDIRECTed to item 149
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 111 is now REDIRECTed to item 150
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (113 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 3616)
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[534] - working on block 3
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[629] - reading block
psql:script.sql:9: LOG:  patch - vacuumlazy.c - lazy_scan_heap[762] - pruning HOT update chains...
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune[220] - 21 deleted items found
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 1 is now REDIRECTed to item 62
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 4 is now REDIRECTed to item 63
[...]
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 58 is now REDIRECTed to item 81
psql:script.sql:9: LOG:  patch - pruneheap.c - heap_page_prune_execute[691] - item 61 is now REDIRECTed to item 82
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[489] - compacting page (61 have storage, 0 are unused)
psql:script.sql:9: LOG:  patch - bufpage.c - PageRepairFragmentation[514] - compacting page (totallen 1952)

Dans ce cas, tous les enregistrements bénéficient de HOT, aucun n'est UNUSED. Cela permet des mises à jour et une maintenance plus rapides, mais c'est au prix d'une table plus volumineuse (sur disque et dans le cache des relations de PostgreSQL).

Et du coup, vous vous demandez peut-être quand va sortir la version beta 0.3 du livre ? D'ici peu a priori. Un peu de relecture, quelques ajustements de dernières minutes, et ça devrait être prêt