• Outil de comparaison de schéma : migra
• Using PostgreSQL to Generate Slugs
• Client CLI universel : https://github.com/xo/usql
• Adding an index can decrease SELECT performance
• Des améliorations dans plpgsql_check : plpgsql_check can identify variables with wrong type used in predicates and breaks index usage
• Déboguer du plpgsql dans Dbeaver : https://github.com/dbeaver/dbeaver/...
• À propos d'authentification : Integrating PostgreSQL with Authentication Systems

• À venir dans la version 11 :
  • PostgreSQL 11 beta2 est prévue le 28 juin : https://www.postgresql.org/message-...
  • Note de publication en cours de rédaction : https://momjian.us/pgsql_docs/relea...

• À noter dans vos agendas, pgday.fr le 26 juin à Marseille : https://www.postgresql.org/about/ev... (j'y serai !)

La PGCon s’est tenue à Ottawa, comme tous les ans. Il s’agit du plus grand rassemblement de développeurs de PostgreSQL et d’un événement annuel assez particulier.

Cet événement a lieu à l’université d’Ottawa. Les lieux sont spacieux, climatisés et bien équipés. Nous y étions et voici ce que nous avons retenu.

En dehors des conférences, il y a deux choses à savoir sur Ottawa et la communauté. Si vous souhaitez rencontrer la communauté le soir, il faut aller au Aulde Dubliner. Ce pub irlandais propose différentes bières ainsi que des boissons plus locales (comme le Bloody Caesar) et quelques plats (dont la Poutine). Si c’est le matin, direction chez Zack’s où, en plus de profiter d’un excellent petit déjeuner, vous pourrez discuter PostgreSQL.

Jour 1 : unconference day

Ce premier jour est très particulier. Chacun vient avec un sujet (et des diapositives, mais ce point n’est pas obligatoire). En début de journée, les sujets sont écrits sur des post-it géants et l’assemblée vote pour les sujets les plus intéressants. Quand des sujets sont proches, il est proposé aux auteurs de les fusionner.

Ensuite, les sujets sont choisis, ainsi que l’heure, en essayant de réduire au maximum les conflits pour que chacun puisse voir les sujets qui l’intéressent.

C’était très intéressant de pouvoir discuter du fonctionnement interne de PostgreSQL et de l’intérêt de nouvelles fonctionnalités.

Jour 2 : les conférences

Il était très difficile de faire des choix, mais le don d’ubiquité n’ayant toujours pas été accordé aux humains, nous avons dû nous y résoudre.

Hacking on Postgres

Par Stephen FROST de Crunchy Data

Cette conférence explique dans le détail comment est composé le code de PostgreSQL et comment écrire un correctif de A à Z. C’est vraiment un guide du débutant (et même intermédiaire) super intéressant pour qui veut aider la communauté.

Continuous Integration for Commitfests

Par Thomas MUNROE d’EnterpriseDB

Lors de cette conférence, Thomas a montré comment il a mis en place des robots qui testent tous les patchs inscrits au CommitFest, que ce soit sous Linux ou sous Windows. Cet outil très intéressant devrait être intégré à la communauté.

De-mystifying contributing to PostgreSQL

Par Lætitia AVROT de Loxodata

Lætitia tente de démystifier la contribution au projet PostgreSQL en répondant notamment aux questions courantes :

• Que puis-je faire pour aider ? (Même si je ne suis pas développeur.)
• Qu’est-ce qu’une contribution ?
• C’est quoi un correctif, comment le créer ?

Growing up new PostgreSQL developers

Par Aleksander Alekseev et Anastasia Lubennikova de Postgres Professional

Durant cette présentation, Aleksander et Anastasia sont revenus sur les erreurs qui ont été effectuées par Postgres Professional pour créer leur équipe de développeurs de PostgreSQL et tout ce qu’ils ont appris de ces erreurs. C’est vraiment très enrichissant.

Jour 3 : les conférences, la suite

pg_chameleon

Par Federico Campoli de Transferwise

Federico travaille chez Transferwise avec des bases MySQL. Lorsqu’il a fallu faire de la BI sur ces bases, il a rencontré les limites de cette technologie. C’est tout naturellement qu’il a proposé de passer à PostgreSQL. Cependant, la base d’origine est toujours en MySQL, il a donc développé une réplication logique de MySQL vers PostgreSQL (qui est même capable de chiffrer les données à la volée).

À la fin de la conférence, Dimitri Fontaine a proposé à Federico un travail commun avec pg_loader. Ça semble très prometteur !

Reviewing PostgreSQL Patches for Fun and Profit

Par David Steele de Crunchy Data

David Steele explique dans cette présentation comment devenir un membre actif de la communauté en relisant les patchs durant les CommitFests pour améliorer la qualité de ceux-ci. Une conférence très dynamique et super intéressante.

Standard SQL Gap Analysis (Standard SQL features where PostgreSQL lags behind its competitors)

Par Markus WINAND

Dans cette conférence, Markus met le doigt là où ça fait mal pour montrer tous les points sur lesquels PostgreSQL n’est pas tout à fait conforme à la norme. Cette présentation est très utile, car dès la fin de celle-ci des idées d’amélioration de nombreux points avaient été proposées.

Securing Your Data On PostgreSQL

Par Payal SINGH d’OmniTI

Payal explique dans les détails toutes les lignes de défense que vous avez à disposition pour sécuriser vos données dans une base de données PostgreSQL. Son exposé était très complet et la présentation très dynamique.

zheap : less bloat, fewer writes, and just plain smaller

Par Amit KAPILA et Robert HAAS d’EnterpriseDB

Cette conférence était peut-être la plus attendue depuis le post de blog de Robert Haas sur la technologie dénommée « zheap ». Il s’agit de mettre en place un espace de stockage des versions précédentes d’une ligne afin de pouvoir faire des updates « in place », de supprimer le besoin de vacuums réguliers et de réduire le « bloat ». La conférence était d’un très haut niveau technique.

What’s in a Plan? (And how did it get there, anyway?)

Par Robert HAAS d’EnterpriseDB

Dans cette conférence, Robert Haas explique comment fonctionne le plan d’exécution, quelles données il affiche, mais également quelles données il n’affiche pas.

Et en dehors des conférences ?

Si comme nous, vous avez un peu de temps à Ottawa, nous vous conseillons le National Art Gallery qui renferme des trésors magnifiques.

Et bien sûr, ne quittez pas Ottawa sans avoir goûté à la queue de castor !

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.

• Dans le wiki de PostgreSQL : Don't do this
• Dimitri Fontaine nous explique les différents types de données : PostgreSQL Data Types
• A Guide to PGpool : Part One, Two, Three
• Exécution distante des agrégats : PostgreSQL foreign data wrapper: Aggregate Pushdown
• À propos des triggers : Are triggers really that slow in Postgres?
• Minutes de la rencontre annuelle des développeurs : https://wiki.postgresql.org/wiki/Pg...
• Odyssey, nouveau pooler de connexion, à évaluer : Odyssey

• À venir dans la version 11 :
  • PostgreSQL 11 beta1 est sortie le 24 mai : https://www.postgresql.org/about/ne...
  • Comme à chaque version, HP publie un document reprenant les nouveautés : PostgreSQL 11 new features with examples (PDF)
  • Note de publication en cours de rédaction : https://momjian.us/pgsql_docs/relea...

• À noter dans vos agendas, pgday.fr le 26 juin à Marseille : https://www.postgresql.org/about/ev... (j'y serai !)

Publication de PostgreSQL 11 Bêta 1

Le PostgreSQL Global Development Group annonce la publication de la première bêta de PostgreSQL 11, disponible au téléchargement. Cette publication contient un aperçu de toutes les caractéristiques qui seront disponibles dans la publication finale de PostgreSQL 11, même si certains détails peuvent encore changer d’ici là.

Dans l’esprit de la communauté PostgreSQL, nous vous encourageons fortement à tester les nouvelles fonctionnalités avec vos bases de données pour nous aider à éliminer les bogues et autres problèmes qui peuvent exister. Bien qu’il ne soit pas recommandé d’utiliser cette publication dans vos environnements de production, nous vous encourageons à tester vos applications sur des charges réelles.

Fonctionnalités principales de PostgreSQL 11

Les fonctionnalités principales concernent la performance, la gestion des gros volumes de données et la facilité d’utilisation.

Améliorations importantes au partitionnement

PostgreSQL 11 contient plusieurs caractéristiques améliorant l’utilisation du partitionnement :

• l’utilisation de hachages comme clé de partitionnement (“hash partitioning”);
• les ordres UPDATE utilisant la clé de partitionnement déplacent les lignes concernées dans la partition appropriée;
• l’amélioration des performances des requêtes SELECT due à l’élimination des partitions pendant le traitement et l’exécution de la requête, et parallélisation des lectures;
• le support des clés primaires (PRIMARY KEY), clés étrangères (FOREIGN KEY), index triggers sur les partitions.

Et bien d’autres.

PostgreSQL 11 introduit aussi la possibilité de distribuer les agrégations et regroupements sur les tables partitionnées avant l’agrégation finale. Cette fonctionnalité étant désactivée par défaut, il faut, pour l’activer, régler enable_partitionwise_aggregate = on dans le fichier de configuration, la session ou la transaction.

Amélioration du parallélisme

Plusieurs fonctionnalités ajoutées dans PostgreSQL 11 tirent parti de l’infrastructure de parallélisation et fournissent des améliorations significatives des performances, dont :

• la jointure HASH parallélisée;
• la création d’index B-tree parallélisée;
• la création de tables CREATE TABLE .. AS, CREATE MATERIALIZED VIEW, et certaines requêtes utilisant UNION parallélisées.

Procédures stockées

PostgreSQL introduit les procédures stockées SQL qui autorisent les utilisateurs à embarquer des transactions (i.e. BEGIN, COMMIT/ROLLBACK) dans une procédure. Les procédures peuvent être créées par la commande CREATE PROCEDURE et exécutées par la commande CALL.

Introduction de la compilation Just-In-Time (JIT)

PostgreSQL 11 introduit le support de la compilation Just-in-Time (JIT) pour optimiser l’exécution de code et d’autres opérations. Utilisant des composants du projet LLVM, l’introduction de JIT dans PostgreSQL accélère les requêtes utilisant des expressions (e.g. clauses WHERE), listes, agrégats, projections, ainsi que certaines opérations internes.

Le cadre de JIT dans PostgreSQL est conçu pour autoriser de futurs travaux pour des optimisations à venir. Si vous compilez PostgreSQL 11 depuis les sources, vous pouvez activer la compilation JIT avec --with-llvm flag.

Support de la norme SQL:2011 pour les Window Fonctions

Avec PostgreSQL 11, les window fonctions supportent maintenant toutes les options de la norme SQL:2011, incluant RANGE _distance_ PRECEDING/FOLLOWING, le mode GROUPS, et les options d’exclusion des cadres.

Agrégation de canaux pour l’authentification SCRAM

La version précédente de PostgreSQL a introduit l’authentification SCRAM pour améliorer le stockage et la transmission des mots de passe, en utilisant un protocole standard. PostgreSQL 11 introduit l’agrégation de canaux pour l’authentification SCRAM, ce qui ajoute à la sécurité de SCRAM en prévenant les attaques MITM (man-in-the-middle).

Amélioration de l’expérience utilisateur

Le groupe des développeurs de PostgreSQL (PGDG) reconnait que, bien que PostgreSQL contienne un solide ensemble de fonctionnalités, toutes ne sont pas faciles à utiliser et requièrent des contournements, à la fois pour le développement et l’exploitation.

Avant PostgreSQL 11, une fonctionnalité comme ALTER TABLE .. ADD COLUMN où la nouvelle colonne avait une colonne par défaut pouvait être considérée comme telle. En effet, pour l’exécution de cette commande, PostgreSQL réécrivait la table entière, ce qui, sur de grandes tables dans des systèmes actifs, entraînait des problèmes en cascade.

PostgreSQL 11 supprime la nécessité de réécrire la table dans la plupart des cas, et ALTER TABLE .. ADD COLUMN .. DEFAULT .. s’exécute plus rapidement.

Une autre fonctionnalité dans cette catégorie est l’incapacité de quitter intuitivement l’outil en ligne de commande psql. Il y a eu de nombreuses plaintes d’utilisateurs essayant de quitter avec les commandes quit et exit, alors que la commande à lancer est \q.

Nous avons entendu les frustrations et avons ajouté la possibilité de quitter l’outil en ligne de commande avec les mots clés quit et exit et espérons qu’ainsi quitter une session PostgreSQL soit aussi agréable qu’utiliser PostgreSQL.

Fonctionnalités supplémentaires

Beaucoup d’autres fonctionnalités et améliorations ont été ajoutées à PostgreSQL 11, certaines pouvant être plus importantes pour quelques utilisateurs que celles mentionnées ci-dessus. Merci de se référer aux notes de publications pour une liste exhaustive des nouveautés.

Tester la compatibilité et le débogage

La stabilité de chaque version de PostgreSQL dépend grandement de vous, la communauté, testant la version à venir avec votre propre charge et vos outils, afin de trouver les bogues et régressions avant la publication de PostgreSQL 11.

S’agissant d’une version bêta, des changements mineurs dans le comportement, des détails de fonctionnalités et des APIs sont possibles. Vos remontées d’informations et de tests aideront à déterminer les ajustements finaux des nouvelles caractéristiques.

Merci de tester dès maintenant. La publication de la version finale dépend de la qualité des tests.

Une liste des problèmes connus est disponible dans le wiki de PostgreSQL. Vous pouvez reporter les bogues à travers le formulaire sur le site web de PostgreSQL : https://www.postgresql.org/account/submitbug/.

Planning de la Bêta

Ceci est la première publication bêta de la version 11.

Le projet PostgreSQL publiera autant de bêtas supplémentaires qu’il sera nécessaire pour tester. Ces bêtas seront suivies par plusieurs versions candidates à la publication, jusqu’à la version finale, plus tard dans l’année 2018. Pour plus d’information, reportez-vous à la page Beta Testing.

Liens

• Télécharger
• Bêta test
• Notes de publication
• Sujets Ouverts
• Soumettre un bogue

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

• Comparaison entre PostgreSQL et MySQL : The Ultimate Postgres vs MySQL Blog Post
• Publication de pgBackRest 2.02 : https://pgbackrest.org/release.html
• Isoler PostgreSQL avec les Control Group de Linux : Linux cgroups for PostgreSQL
• SQL Récursif : Fun with SQL: Recursive CTEs in Postgres
• À propos du problème avec fsync : PostgreSQL visits LSFMM
• Collecteur d'informations et de statistiques : pgmetrics

• À venir dans la version 11 :
  • PostgreSQL 11 beta1 prévue le 24 mai : https://www.postgresql.org/message-...
  • Note de publication en cours de rédaction : https://momjian.us/pgsql_docs/relea...

• À noter dans vos agendas, pgday.fr le 26 juin à Marseille : https://www.postgresql.org/about/ev... (j'y serai !)
• Et très bientôt, le Meetup PostgreSQL Nantes #10

• À venir le 10 mai : publication des versions mineures : 10.4, 9.6.9, 9.5.13, 9.4.18, 9.3.23

• Jointures contre Diagramme de Venn : Say NO to Venn Diagrams When Explaining JOINs
• PostgreSQL disponible chez Google Cloud : Cloud SQL for PostgreSQL now generally available and ready for your production workloads
  • Outil de vérification des checksums : https://github.com/google/pg_page_v...
• Problème avec fsync : PostgreSQL's fsync() surprise
  • Analyse du problème sur la liste hackers : Postgres, fsync, and OSs (specifically linux)
• Migration depuis Oracle : ora_migrator: Moving from Oracle to PostgreSQL even faster
• L'histoire du logo de PostgreSQL : The History of Slonik, the PostgreSQL Elephant Logo
• À propos des index Bloom : Choosing PostgreSQL Bloom Index Parameters
• Retour d'experience sur l'incident survenu chez GitLab : GitLab crawling back online after breaking its brain in two

• À venir dans la version 11 :
  • PostgreSQL 11 beta1 prévue le 24 mai : https://www.postgresql.org/message-...
  • CREATE PROCEDURE
  • Indexes with INCLUDE columns and their support in B-tree
  • À propos des transactions SERIALIZABLE : SERIALIZABLE in PostgreSQL 11... and beyond
  • Sélection des partitions à l'execution : Waiting for PostgreSQL 11 – Support partition pruning at execution time

• Guide à propos de Slony : An Expert’s Guide to Slony Replication for PostgreSQL
• PostgreSQL sur FreeBSD : PostgreSQL/FreeBSD performance and scalability on a 40-core machine (pdf)
• Introduction à la réplication : Réplication par streaming PostgreSQL : usages et topologies
• PostgreSQL en tant que service chez Microsoft Azure : Announcing general availability of Azure database services for MySQL and PostgreSQL
• The PostgreSQL Machine
• PgAdmin4 sur Redhat
• à propos des identifiants de transaction : What’s in an xmax?

• À venir dans la version 11 :
  • Covering Index
  • À propos des sommes de contrôles : Manipulating checksums of a cluster
  • De la recherche plein texte en JSON : Waiting for PostgreSQL 11 – Add json(b)_to_tsvector function

• À noter dans vos agendas : le programme du prochain meetup PostgreSQL à Nantes est publié : https://www.meetup.com/PostgreSQL-U...

• Trouver les index inutilisés : Get rid of your unused indexes!
• Montée de version avec pglogical : Upgrading PostgreSQL from 9.4 to 10.3 with pglogical
• Publication de PgAdmin 4 v3.0 : https://www.postgresql.org/about/ne...
• Publication de DBeaver 5 : https://dbeaver.jkiss.org/2018/04/0...
• Le programme du meetup nantais est sorti : https://www.meetup.com/PostgreSQL-U...

• À venir dans PostgreSQL 11 :
  • Index unique couvrant : Covering + unique indexes
  • MERGE et l'activation des checksum sont annulés (revert)
  • Liste des items ouverts : https://wiki.postgresql.org/wiki/Po...

• Le fichier de configuration de PostgreSQL 10 annoté : New Annotated Config Files for PostgreSQL 10
• Documentation sur la réplication intégrée : The Internals of PostgreSQL : Streamin Replication
• Utilisation de PgLoader chez Heroku : Migrating a Heroku app from MySQL to Postgres using PGLoader
• Louise Grandjonc a repris sa conférence donnée à pgday.paris en une série d'articles : Understanding EXPLAIN - part 1, part 2, part 3, part 4

• À venir dans la version 11, dont le « feature freeze » est imminent :
  • Clé étrangère depuis une table partitionnée : Foreign keys on partitioned tables
  • Commande MERGE : MERGE SQL Command following SQL:2016
  • Vérification de l'intégrité des données : Waiting for PostgreSQL 11: Amcheck enhancement
  • Modification de table : Waiting for PostgreSQL 11 – Fast ALTER TABLE ADD COLUMN with a non-NULL default

• Les contraintes dans la base : The last line of defense
• Comment améliorer la lecture des plans d'éxecution : Using pg_badplan to CREATE STATISTICS is a good plan
• Migrer 1500 instances vers PostgreSQL 10 : Postgres 10 Upgrade
• Se battre contre le bloat : PostgreSQL Bloatbusters
• Données publiques sous formes de Dump PostgreSQL : Code officiel géographique 2018
• Sur le chemin vers PostgreSQL : A PostgreSQL path to database freedom? et How PostgreSQL just might replace your Oracle database
• Le dernier commitfest de la v11 se termine : https://commitfest.postgresql.org/1...
  • De nombreux commits récents à propos de la compilation à la volée des requêtes SQL avec LLVM !

Lu cette semaine :

• Tests de performance avec PostgreSQL suite à Meltdown : PostgreSQL Meltdown Benchmarks
• Mettre à jour 50TB de données : Updating a 50 terabyte PostgreSQL database ; la technique est simple, et tout le monde y a accès. S'ils peuvent le faire avec 50TB, vous pouvez aussi le faire !
• Pourquoi VACUUM ne nettoie pas tout : Three reasons why VACUUM won’t remove dead rows from a table
• Et pourquoi VACUUM est toujours une bonne idée : What PostgreSQL Full-Text-Search has to do with VACUUM avec ici de l'indexation GIN. (Idée d'article : pourquoi les Autrichiens font-ils des titres aussi longs ?!)
• La réplication en v10, c'est facile : Streaming replication with PostgreSQL 10
• Explication détaillée sur la réplication logique : Logical replication internals
• Quelques exemples d'utilisations des transactions : Where Postgres Wins
• À propos de Forme Normale et de clé primaire : Database Normalization and Primary Keys

• à venir dans la version 11:
  • on entre dans le dernier commitfest, donc, de nombreux commit à venir : https://commitfest.postgresql.org/1...
  • Parallélisation des UNION ALL : https://git.postgresql.org/gitweb/?...

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.

Lu cette semaine :

• Quelques sages conseils à propos de verouillage : 7 tips for dealing with locks
• Interview de Dimitri Fontaine, contributeur majeur à PostgreSQL
• Bel exemple d'utilisation des fonctionnalités de PostgreSQL 10 : Implementing a "distributed" reporting server using some of postgres10 features
• PostgreSQL 10 arrive chez Amazon RDS : Postgres10 in RDS, first impressions
• À propos des WALs et des checkpoints : Checkpoints and wals, fantastic beasts (and where to find them)
• Mise à jour mineure de PostgreSQL : PostgreSQL 10.3, 9.6.8, 9.5.12, 9.4.17, and 9.3.22 released!, ainsi que quelques explications dans le wiki : https://wiki.postgresql.org/wiki/A_...

• à venir dans la version 11
  • on entre dans le dernier commitfest, donc, de nombreux commit à venir : https://commitfest.postgresql.org/1...

Lu cette semaine :

• Quelle méthode efficace pour produire des UUIDs : Benchmarking UUIDs, v2
• Comment PostgreSQL traite les requêtes : The Internals of PostgreSQL : Query Processing
• Des « range types » et de la récursion : How to Search Availability with PostgreSQL
• Pourquoi migrer : http://why-upgrade.depesz.com/show?... Trouvez toutes les différences entre toutes les versions mineures de PostgreSQL ! Et Migrez !
• à venir dans la version 11 :
  • Index UNIQUE pour les tables partitionnées : Allow UNIQUE indexes on partitioned tables
  • Plus de flexibilité dans la recherche de texte : PostgreSQL Flexible Full Text Search

À noter dans vos agendas :

• Prochain Meetup PostgreSQL à Nantes, le 28 février : PostgreSQL Meetup Nantes #9

Lu cette semaine :

• Tableau de bord de supervision : OmniDB: Monitoring Dashboard
• Détecter les problèmes de performances : Detecting performance problems easily in PostgreSQL
• Encore de la supervision, les métriques clés : Key metrics for PostgreSQL monitoring
• Experience sur les requêtes parallèles : PostgreSQL 10 Parallel Queries and Performance
• À propos de sécurité d'accès aux données : A PostgreSQL Row Level Security Primer + Creating Large Policies
• Encore un article sur pgBackRest : Nobody expects the spanish inquisition
• à venir dans la version 11 :
  • de la validation dans les procédures stockées : Transaction control in PL procedures PostgreSQL 11 – Server-side Procedures (Part 2)

À noter dans vos agendas :

• Prochain Meetup PostgreSQL à Nantes, le 28 février : PostgreSQL Meetup Nantes #9
• PGDay-FR à Marseille le 26 juin, le Cfp est ouvert : http://pgday.fr/appel.html

Lu cette semaine :

• À propos de SQL et d'algèbre relationnelle : Find the number of the longest continuously rising days for a stock
• À propos des CTEs : Advanced SQL - Common Table Expressions
• Conférence de Laetitia Avrot au FOSDEM : De-mystifying contributing to PostgreSQL
• Conférence de Patrick Francelle au FOSDEM : The Fabulous Destiny of 0000000200000008000000BB
• Publication de versions mineures : PostgreSQL 10.2, 9.6.7, 9.5.11, 9.4.16, and 9.3.21 released!
• à venir dans la version 11 :
  • Création d'index B-tree en parallèle : Support parallel btree index builds
  • Support de RANGE offset PRECEDING/FOLLOWING dans les Window Functions : Support all SQL:2011 options for window frame clauses

À noter dans vos agendas :

• Prochain Meetup PostgreSQL à Nantes, le 28 février : PostgreSQL Meetup Nantes #9
• PGDay-FR à Marseille le 26 juin, le Cfp est ouvert : http://pgday.fr/appel.html

Lu cette semaine :

• Troisième partie du voyage vers PostgreSQL : Why I picked Postgres over Oracle, part III
• Fonctionnement du B-tree dans PostgreSQL : Postgres Indexes Under the Hood
• Suite des articles sur Vacuum par Robert Haas : DO or UNDO - there is no VACUUM
• Des nouvelles de pgwatch2 : pgwatch2 Feature Pack 3
• à venir dans la version 11 :
  • Des changements dans pg_dump : Move handling of database properties from pg_dumpall into pg_dump
  • Minutes de la Devroom du Fosdem : FOSDEM/PGDay 2018 Developer Meeting

À noter dans vos agendas :

• Prochain Meetup PostgreSQL à Nantes, le 28 février : PostgreSQL Meetup Nantes #9
• PGDay-FR à Marseille le 26 juin, le Cfp est ouvert : http://pgday.fr/appel.html

Lu cette semaine :

• Choisir son index : Using plantuner for debugging indexes in PostgreSQL
• UPSERT en détail : https://wiki.postgresql.org/wiki/UP...
• Quel type de sauvegarde ? : Postgres Backups: Logical vs. Physical an Overview
• File d'attente basée sur PostgreSQL et les mécanismes transactionels : How We Built A Job Queue System with PostgreSQL & Ruby For Our B2B SaaS Application
• à venir dans la version 11
  • Encore une amélioration sur le partitionnement : Allow UPDATE to move rows between partitions !
  • L'article sur le blog de Loxodata : Ce que nous réserve PostgreSQL 11 : le partitionnement
• Travaux en cours sur la compilation JIT avec LLVM : JIT compiling with LLVM v9.0

Un pas de coté :

• article très instructif sur la mémoire : How To Measure the Working Set Size on Linux

Lu cette semaine :

• Bel exemple d'architecture de réplication possible : PostgreSQL Log Shipping or when it’s advisable
• Relecture du livre de Dimitri Fontaine : PostgreSQL notes: Miscellaneous
• Comment une bête erreur de paramétrage peut avoir de lourdes conséquences : How a bad network configuration can cause table bloat
• Quelques éléments d'optimisation, pertinent quand on est développeur et qu'on souhaite optimiser ses requêtes : Advanced Postgres Performance Tips
• Nouvelle version du pilote JDBC, supportant SCRAM-SHA-256 : PostgreSQL JDBC 42.2.0 released
• Récit de voyage, d'Oracle vers PostgreSQL : Why I picked Postgres over Oracle, part I, Why I picked Postgres over Oracle, part II
• à venir dans la version 11
  • Parallel Hash for PostgreSQL
  • Gestion des index pour les tables partitionnées :Local partitioned indexes

Veille hebdomadaire autour de PostgreSQL, la plupart en anglais :

• Deux articles de Robert Haas à propos de VACUUM : MVCC and VACUUM suivi de The State of VACUUM
• À propos de Meltdown et PostgreSQL :
  • Fil de discussion sur la mailing list hackers
  • PostgreSQL Meltdown
  • PostgreSQL Performance Meltdown?
• À propos du verrouillage : Postgres Locks and SKIP LOCKED - Updating Records in Parallel
• Outil de sauvegarde pour PostgreSQL : introduction to pgBackRest
• Comment PostgreSQL stocke-t-il les données volumineuses : Postgres TOAST

Ceci est le premier billet d'une série, reflétant mes lectures à propos de PostgreSQL. À suivre, donc…

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

Le Groupe de Travail Inter-Entreprises de l’association PostgreSQLFr vient de publier une lettre ouverte destinée à tous les éditeurs de logiciels qui ne supportent pas encore PostgreSQL pour le demander d’être compatibles avec PostgreSQL.

L’ambition de ce message est de les inciter à s’adapter rapidement à la transition irrésistable qui est en cours actuemment dans le secteur public et au sein des sociétés privées.

Créé en 2016, le Groupe de Travail Inter-Entreprise PostgreSQL (PGTIE) est un espace de discussion dédié aux entreprises et aux établissement publics. Le groupe fait partie de l’association PostgreSQLFr. Au cours des derniers mois, il a grandi de manière impressionnante.

Via une annonce de presse (lien ci-dessous) publié hier, le groupe de travail envoie une lettre ouverte aux éditeurs logiciels pour les encourager à supporter PostgreSQL

https://www.postgresql.fr/entreprises/20171206_lettre_ouverte_aux_editeurs_de_logiciels

Il s’agit d’une étape majeure pour PostgreSQL France et dans les pays francophones. Pour la première fois, plus de 20 sociétés d’envergure nationale ou internationale prend explicitement position en faveur de PostgreSQL en reconnaissant la valeur technique de ce SGBD mais aussi en soulignant les bénéfices du modèle open source lui-même.

Parmi ces sociétés et ces établissement publics, on trouve:

Air France, Carrefour , CASDEN, CNES , EDF, MSA / GIE AGORA, E.Leclerc , MAIF , Météo France , Le ministère de l’éducation nationale, SNCF, PeopleDoc,
Société Générale, et Tokheim Services Group.

Ces institutions se sont regroupées pour partager leur expérience, promouvoir PostgreSQL et contribuer à son développement. Au dela de ces 3 missions, le point remarquable est que le groupe de travail s’est structuré en adoptant les grands principes de la communauté PostgreSQL : ouverture, entraide, transparence, auto-gouvernance, etc.

Pour découvrir les activités de ce groupe, vous pouvez venir rencontrer la communauté PostgreSQL pendant le salon Paris Open Summit 2017 ou lire page de wiki du groupe de travail :

https://www.postgresql.fr/entreprises/

Le groupe de travail entreprise est l’illustration même d’un principe fondamental de l’open source : la suppression de la frontière entre producteur et consommateur. Au sein de la communauté PostgreSQL, chaque utilisateurs peut devenir un “contributeur” et jouer un role dans l’essort et la promotion du logiciel.

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.

pspg est un outil annoncé récemment par Pavel Stehule, contributeur régulier bien connu de la communauté PostgreSQL. Il est disponible sur github et installable par le classique ./configure; make ; sudo make install sur les systèmes pourvus d’un compilateur C et de la bibliothèque ncurses.

Le “pager” ou paginateur est le programme externe appelé par psql lorsqu’un résultat à afficher ne tient pas à l’écran, en largeur ou en longueur. Par défaut c’est celui configuré pour le système, more et less étant les plus connus, sachant qu’il est remplaçable via la variable d’environnement PAGER.

pspg est spécialement adapté aux résultats produits par psql, parce qu’il intègre la notion que le contenu est fait de champs organisés en colonnes et en lignes séparées par des bordures. Sont gérées les bordures ASCII par défaut, et Unicode pour des lignes mieux dessinées.

Dès la première utilisation, ce qui frappe est la colorisation marquée à la “Midnight Commander”, et les particularités du défilement horizontal et vertical commandés par les touches du curseur: la colonne la plus à gauche reste en place au lieu de disparaître vers la gauche de l’écran, et la ligne du haut avec les noms de colonnes est également fixe par rapport au défilement vertical.

L’effet est qu’on se déplace dans le jeu de résultats avec les flèches du curseur sans perdre des yeux les données les plus importantes pour se repérer. En effet, la première colonne affichée correspond souvent à une clef primaire qui détermine les autres colonnes. Mais si on souhaite qu’elle défile comme les autres, il suffira d’appuyer sur la touche “0”, et pour y revenir la touche “1”, ou bien entre “1” et “4” pour figer entre 1 et 4 colonnes. C’est très simple et pratique.

Et voici l’inévitable copie d’écran (un SELECT * from pg_type avec les bordures par défaut):

Le mode étendu \x de psql, où chaque colonne est affiché sur une nouvelle ligne, est aussi pris en compte, le défilement est normal et le séparateur de lignes [ RECORD #N ] est colorisé pour être encore plus visible.

On peut également faire de la recherche de texte en avant et arrière avec les touches habituelles / et ?.

Outre les couleurs et ce mode de défilement vraiment spécifique, il y a quelques autres fonctionnalités qui diffèrent sensiblement de less:

• une ligne horizontale surlignée toujours présente qui accompagne les déplacements au curseur
• le support optionnel de la souris sur X-Window pour déplacer cette ligne au clic.
• une ligne d’état en haut de l’écran avec diverses infos de positionnement.

Cette addition à psql a été accueillie avec enthousiasme sur la liste pgsql-hackers, n’hésitez pas à l’installer si vous passez un tant soit peu de temps avec psql pour visualiser des données.

Les contenus binaires peuvent être stockés avec PostgreSQL soit dans des tables utilisateurs avec des colonnes de type bytea, soit instanciés en tant qu’objets larges et gérés dans des tables systèmes et par des fonctions spécifiques, côté client comme côté serveur.

Quelles sont les raisons de choisir l’un plutôt que l’autre?

Très schématiquement, on pourrait les résumer dans ce tableau comparant les deux approches:

^* RLS = Row Level Security

Voyons plus en détail certaines de ces différences et leurs implications.

Usage

Les colonnes en bytea sont plus simples à utiliser, dans le sens où elles s’intégrent de manière plus standard au SQL, et qu’elles sont accessibles via toutes les interfaces. Pour insérer une donnée binaire littérale dans une requête, il faut l’exprimer dans un format textuel, par exemple: '\x41420001'::bytea pour le format hex, ou encore 'AB\000\001'::bytea pour le format escape. Dans le sens inverse, pour un résultat retourné du serveur vers le client, les colonnes bytea sont encodées dans un de ces deux formats selon le paramètre bytea_output, sauf si l’appelant a appliqué une fonction explicite d’encodage telle que base64, ou encore demandé du binaire. Ce dernier cas est plutôt rare, car beaucoup de programmes et d’interfaces avec les langages ne gèrent pas les résultats de requête au format binaire, même si le protocole et la bibliothèque libpq en C le permettent.

Ce passage par un format texte présente un inconvénient: les conversions en texte gonflent la taille des données en mémoire et sur le réseau, d’un facteur 2 pour hex, variable (entre 1 et 4) pour escape, et 4/3 pour base64, et consomment du temps CPU pour coder et décoder.

Les objets larges, de leur côté, s’appuient sur une API particulière, où chaque contenu binaire se présente un peu comme un fichier, identifié par un numéro unique (OID), avec des permissions individuelles par objet, et accessible via des opérations sur le modèle de celles des fichiers:

La plupart de ces opérations sont appelables de deux manières différentes: d’une part en tant que fonctions SQL côté serveur, et d’autre part directement par le client, en-dehors d’une requête SQL. Par exemple avec psql, la commande \lo_import /chemin/fichier.bin insérera le contenu du fichier client sur le serveur sans passer par une requête INSERT ou COPY. En interne, elle appelera la fonction libpq lo_import dans une transaction, qui elle-même appelera les fonctions distantes de création et écriture à travers le protocole.

Avec les objets larges, il n’y a pas d’encodage intermédiaire en format texte, ce sont les données binaires brutes qui transitent. Par ailleurs, comme pour un fichier, le client accède généralement au contenu par morceaux, ce qui permet de travailler en flux (streaming), sans avoir besoin d’ingérer une donnée d’un seul tenant pour la traiter.

A contrario, dans le cas d’un SELECT ou COPY avec des colonnes bytea, le client ne peut pas accéder à une ligne partiellement récupérée, et encore moins à une partie de colonne, sauf à descendre au niveau du protocole et à lire directement la socket réseau.

Importons des photos

Soit un répertoire avec 1023 photos JPEG d’une taille moyenne de 4,5 Mo. On va importer ces photos dans des objets larges, puis dans une table pour faire quelques comparaisons.

Import

Déjà, comment importer un fichier dans une colonne bytea? psql n’offre pas de solution simple. Cette question sur DBA.stackexchange ouverte en 2011 : How to insert (file) data into a PostgreSQL bytea column? suggère différentes méthodes plus ou moins indirectes et compliquées, dont notamment celle de passer par un objet large temporaire.

Pour les objets larges, c’est assez simple:

$ (for i in *.JPG; do echo \\lo_import "$i" ; done) | psql

La sortie va ressembler à ça, et nos 4,5 Go sont importés en quelques minutes.

lo_import 16456
lo_import 16457
lo_import 16458
...

chacun de ces numéros étant l’OID d’un objet nouvellement créé.

Maintenant copions ces données en un seul bytea par photo avec une version simplifiée de la réponse de stackexchange (lo_get n’existait pas en 2011).

CREATE TABLE photos(id oid PRIMARY KEY, data BYTEA);

INSERT INTO photos SELECT oid, lo_get(oid) from pg_largeobject_metadata ;

Export

Pour réexporter ces images avec psql, dans le cas des objets larges il suffit d’utiliser pour chacun:

  \lo_export :oid /chemin/vers/fichier`

Pour les contenus de la table photos, le format le plus simple à restituer en binaire sur le client est le base64. Par exemple la commande psql ci-dessous fait que la donnée bytea est transformée explicitement via encode(data, 'base64') en SQL et conduite via l’opérateur ‘|’ (pipe) dans le programme base64 de la suite GNU coreutils.

SELECT encode(data, 'base64') FROM photos
  WHERE id= :id \g | base64 -d >/chemin/fichier

Stockage

Structure des objets larges

Les objets larges sont stockés dans deux tables systèmes dédiées.

pg_largeobject_metadata a une ligne par objet large, indiquant le possesseur et les droits d’accès. Comme d’autres tables systèmes (pg_class, pg_type, …), elle utilise la pseudo-colonne oid comme clef primaire.

=# \d pg_largeobject_metadata
 Colonne  |   Type    | Collationnement | NULL-able | Par défaut 
----------+-----------+-----------------+-----------+------------
 lomowner | oid       |                 | not null  | 
 lomacl   | aclitem[] |                 |           | 
Index :
    "pg_largeobject_metadata_oid_index" UNIQUE, btree (oid)

La seconde table pg_largeobject porte les données, découpées en segments ou mini-pages bytea d’un quart de bloc maximum, soit 2048 octets par défaut. Sa structure:

=# \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 ligne de cette table comporte l’OID qui référence l’entrée correspondante de pg_largeobject_metadata, le numéro de page en partant de 0, et la mini-page elle-même dans data.

Bien que le stockage de la colonne data soit déclaré extended, il n’y a délibérément pas de table TOAST associée, l’objectif étant que ces mini-pages tiennent dans les pages principales. Cette stratégie est expliquée en ces termes dans le code source:

src/include/storage/large_object.h:

/*
 * Each "page" (tuple) of a large object can hold this much data
 *
 * We could set this as high as BLCKSZ less some overhead, but it seems
 * better to make it a smaller value, so that not as much space is used
 * up when a page-tuple is updated.  Note that the value is deliberately
 * chosen large enough to trigger the tuple toaster, so that we will
 * attempt to compress page tuples in-line.  (But they won't be moved off
 * unless the user creates a toast-table for pg_largeobject...)
 *
 * Also, it seems to be a smart move to make the page size be a power of 2,
 * since clients will often be written to send data in power-of-2 blocks.
 * This avoids unnecessary tuple updates caused by partial-page writes.
 *
 * NB: Changing LOBLKSIZE requires an initdb.
 */
#define LOBLKSIZE		(BLCKSZ / 4)

Autrement cette taille est choisie pour:

• permettre des petites mises à jour intra-données peu coûteuses.
• être au-dessus du seuil de compression.
• être une puissance de 2.

Structure des tables TOAST

Au contraire de pg_largeobject, la table photos a une table TOAST associée. On n’a pas besoin de le savoir pour accéder aux données binaires, puisque qu’en sélectionnant photos.data, PostgreSQL va automatiquement lire dedans si nécessaire, mais regardons quand même sous le capot pour continuer la comparaison.

La table TOAST est identifiable via cette requête:

=# SELECT reltoastrelid,
  pg_total_relation_size(reltoastrelid) FROM pg_class
  WHERE oid='photos'::regclass;

 reltoastrelid | pg_total_relation_size 
---------------+------------------------
         18521 |             4951367680

La doc nous indique à quoi s’attendre au niveau de la structure:

Chaque table TOAST contient les colonnes chunk_id (un OID identifiant la valeur TOASTée particulière), chunk_seq (un numéro de séquence pour le morceau de la valeur) et chunk_data (la donnée réelle du morceau). Un index unique sur chunk_id et chunk_seq offre une récupération rapide des valeurs

Et là, surprise (ou pas): c’est exactement le même type de structure que pg_largeobject ! Vérifions dans psql:

=# select relname from pg_class where oid=18521;
    relname     
----------------
 pg_toast_18518

=# \d+ pg_toast.pg_toast_18518
Table TOAST « pg_toast.pg_toast_18518 »
  Colonne   |  Type   | Stockage 
------------+---------+----------
 chunk_id   | oid     | plain
 chunk_seq  | integer | plain
 chunk_data | bytea   | plain

Vu ces similarités, on pourrait penser que physiquement, les deux modèles de stockage pèsent pareillement sur disque. En fait, ce n’est pas vraiment le cas.

Poids réel des données

Calculons le surpoids global, c’est-à-dire tailles des tables versus tailles des données contenues, avec les deux méthodes de stockage, sur l’exemple du millier de photos.

D’abord les tailles des tables:

=# select n,pg_size_pretty(pg_total_relation_size(n))  from
   (values ('pg_largeobject'), ('pg_largeobject_metadata'), ('photos')) as x(n);
            n            | pg_size_pretty 
-------------------------+----------------
 pg_largeobject          | 6106 MB
 pg_largeobject_metadata | 112 kB
 photos                  | 4722 MB
(3 lignes)

La taille des données contenues à proprement parler étant:

=# select pg_size_pretty(sum(octet_length(data))) from photos;
 pg_size_pretty 
----------------
 4551 MB
(1 ligne)

Avec seulement 10% de surpoids pour la table photos versus 34% de surpoids pour pg_largeobject, il n’y a pas photo justement: sur le plan de l’espace disque, les objets larges et leur stockage “mini-page” en ligne perdent largement par rapport au stockage TOAST.

Alors on peut légitimement se demander pourquoi les mêmes contenus rangés dans des structures similaires consomment des espaces assez différents.

Première hypothèse: il y aurait plus de lignes, et le surcoût par ligne ferait la différence.

Les entêtes de ligne prennent effectivement de la place dans PostgreSQL, au minimim 27 octets comme détaillé dans le HeapTupleHeaderData.

Le nombre de lignes de la table TOAST diffère effectivement de celui de pg_largeobject, mais en fait il s’avère plus grand, ce qui invalide donc complètement cette hypothèse:

=# select (select count(*) from pg_toast.pg_toast_18518),
          (select count(*) from pg_largeobject);

  count  |  count  
---------+---------
 2390981 | 2330392

Deuxième hypothèse: les données seraient mieux compressées dans la table TOAST. Concernant des photos JPEG déjà compressées, en principe il ne faut pas s’attendre à une compression supplémentaire par l’algorithme LZ d’un côté comme de l’autre, mais vérifions quand même.

La taille nominative d’un bytea est donné par la fonction octet_length(), la taille sur disque (donc après compression éventuelle) correspond à pg_column_size() moins 4 octets pour l’entête varlena.

Muni de ça, voici une requête qui va calculer et comparer les taux de compression dans les deux cas:

SELECT
 100-avg(ratio_lo) as "% moyen compression LO",
 100-avg(ratio_bytea) as "% moyen compression BYTEA",
 sum(bcmp) as "taille post-compression BYTEA",
 sum(lcmp) as "taille post-compression LO",
 sum(braw) as "taille pré-compression BYTEA",
 sum(lraw) as "taille pré-compression LO"
FROM (
SELECT s.id, bcmp, braw, lcmp, lraw,
  (bcmp*100.0/braw)::numeric(5,2) as ratio_bytea,
  (lcmp*100.0/lraw)::numeric(5,2) as ratio_lo
FROM (
SELECT t.id,
     octet_length(t.data)::numeric as braw,
     (pg_column_size(t.data)-4)::numeric as bcmp,
     s.lraw::numeric,
     s.lcmp::numeric
   FROM photos as t
   JOIN
    (select loid,
       sum(octet_length(data)) as lraw,
       sum(pg_column_size(data)-4) as lcmp
      FROM pg_largeobject
      GROUP BY loid HAVING sum(pg_column_size(data)-4)>0) as s
   ON(loid=id)
) s
) s1;

Résultat:

-[ RECORD 1 ]-----------------+------------------------
% moyen compression LO        | 0.2545161290322581
% moyen compression BYTEA     | 0.0956207233626588
taille post-compression BYTEA | 4771356790
taille post-compression LO    | 4764013877
taille pré-compression BYTEA  | 4771604903
taille pré-compression LO     | 4771604903

Comme prévu, la compression par-dessus JPEG est très faible. Mais celle du bytea l’est encore plus celle des objets larges, avec 0,09% contre 0,25%, soit 7,1 MB de différence cumulée sur la totalité. Donc non seulement ça n’explique pas le surpoids de pg_largeobject, mais ça irait plutôt légèrement dans le sens inverse.

Troisième hypothèse: il y a trop de fragmentation ou espace inutilisé à l’intérieur de pg_largeobject par rapport à celles des tables TOAST. Au fait, quelle est cette taille des “chunks” ou mini-pages du côté TOAST? La doc nous dit encore:

Les valeurs hors-ligne sont divisées (après compression si nécessaire) en morceaux d’au plus TOAST_MAX_CHUNK_SIZE octets (par défaut, cette valeur est choisie pour que quatre morceaux de ligne tiennent sur une page, d’où les 2000 octets)

Pour estimer l’espace inutilisé dans les pages, sans aller jusqu’à les regarder à l’octet près, bien qu’en théorie faisable avec l’extension pg_pageinspect, on peut faire quelques vérifications en SQL de base. Comme le contenu dans notre exemple n’a pas été modifié après import, les données sont a priori séquentielles dans les pages. On peut donc se faire une idée de la relation entre les lignes et les pages les contenant juste en regardant comment évolue la colonne ctid sur des lignes logiquement consécutives.

Par exemple, en prenant une photo au hasard:

# select ctid,pageno,octet_length(data),pg_column_size(data)
  from pg_largeobject where loid=16460;
   ctid   | pageno | octet_length | pg_column_size 
----------+--------+--------------+----------------
 (2855,3) |      0 |         2048 |           1079
 (2855,4) |      1 |         2048 |            132
 (2855,5) |      2 |         2048 |            198
 (2855,6) |      3 |         2048 |           1029
 (2855,7) |      4 |         2048 |            589
 (2856,1) |      5 |         2048 |           2052
 (2856,2) |      6 |         2048 |           2052
 (2856,3) |      7 |         2048 |           2052
 (2857,1) |      8 |         2048 |           2052
 (2857,2) |      9 |         2048 |           2052
 (2857,3) |     10 |         2048 |           2052
 (2858,1) |     11 |         2048 |           2052
 (2858,2) |     12 |         2048 |           2052
 (2858,3) |     13 |         2048 |           2052
 (2859,1) |     14 |         2048 |           2052
 (2859,2) |     15 |         2048 |           2052
 (2859,3) |     16 |         2048 |           2052
... 1900 lignes sautées ...
 (3493,2) |   1917 |         2048 |           2052
 (3493,3) |   1918 |         2048 |           2052
 (3494,1) |   1919 |         2048 |           2052
 (3494,2) |   1920 |         2048 |           2052
 (3494,3) |   1921 |          674 |            678

Dans un ctid comme (2855,3), le premier nombre représente la page et le deuxième le numéro séquentiel de ligne relativement à cette page. On voit dans cet extrait qu’en dehors du début et de la fin, les lignes valent 1,2,3, puis ça passe à la page suivante et ainsi de suite. Très schématiquement, on a le plus souvent 3 lignes par page. C’est logique parce qu’il n’y a pas de place pour 4 lignes. 4*2052 dépasserait déjà 8192 octets, sans même compter les entêtes de ligne et les autres colonnes.

Maintenant regardons l’équivalent dans la table TOAST. C’est trop compliqué de retrouver le chunk_id qui corresponde à la même photo, donc je vais prendre le début de la table, mais on peut vérifier par échantillons aléatoires que le même motif se répète massivement dans toutes ces données.

# select ctid,chunk_id,chunk_seq,octet_length(chunk_data),pg_column_size(chunk_data)
  from pg_toast.pg_toast_18518 limit 20;

 ctid  | chunk_id | chunk_seq | octet_length | pg_column_size 
-------+----------+-----------+--------------+----------------
 (0,1) |    18526 |         0 |         1996 |           2000
 (0,2) |    18526 |         1 |         1996 |           2000
 (0,3) |    18526 |         2 |         1996 |           2000
 (0,4) |    18526 |         3 |         1996 |           2000
 (1,1) |    18526 |         4 |         1996 |           2000
 (1,2) |    18526 |         5 |         1996 |           2000
 (1,3) |    18526 |         6 |         1996 |           2000
 (1,4) |    18526 |         7 |         1996 |           2000
 (2,1) |    18526 |         8 |         1996 |           2000
 (2,2) |    18526 |         9 |         1996 |           2000
 (2,3) |    18526 |        10 |         1996 |           2000
 (2,4) |    18526 |        11 |         1996 |           2000
 (3,1) |    18526 |        12 |         1996 |           2000
 (3,2) |    18526 |        13 |         1996 |           2000
 (3,3) |    18526 |        14 |         1996 |           2000
 (3,4) |    18526 |        15 |         1996 |           2000
 (4,1) |    18526 |        16 |         1996 |           2000
 (4,2) |    18526 |        17 |         1996 |           2000
 (4,3) |    18526 |        18 |         1996 |           2000
 (4,4) |    18526 |        19 |         1996 |           2000

On retrouve la taille de 2000 octets mentionnée dans la doc, et les 4 lignes par page, dans la mesure où les numéros de ligne par page dans ctid sont typiquement 1,2,3,4 avant passage à la page suivante, et ainsi de suite.

4 lignes de chunk_data occupent 4*2000=8000 octets, et les 192 octets restants sur 8192 permettent manifestement de contenir tout le reste, notamment 27 octets d’entête par ligne plus 4+4 octets pour chunk_id et chunk_seq. Ajoutons à ça un entête par page de 24 octets, et il est clair que cette page est occupée presque totalement par des informations utiles: (27 + 4 + 4 + 2000) * 4 = 8140.

Au contraire de ça, nos pages de pg_largeobject semblent être majoritairement occupées par 3 lignes remplies de cette manière: (27 + 4 + 4 + 2052) * 3 = 6261 octets

Compte-tenu de toute ça une estimation grossière du ratio entre les tailles des photos et celle de pg_largeobject pourrait être (2048*3)/8192 = 0,75

Les données “pures” pèsent 4771604903 octets, et pg_largeobject pèse 6402637824 octets.
Le ratio réel d’utilité disque pour les objets larges vaut donc 4771604903 / 6402637824 = 0,745

Du côté TOAST, ce ratio estimé grossièrement est de (2000*4)/8192 = 0,9765625.
Le ratio réel d’utilité est de 4771604903 / 4951490560 = 0,9637

La réalité est remarquablement proche de l’estimation, du fait de la grande taille des objets, de la quasi-absence de compression, et du fait qu’il n’y a pas de désorganisation dans les pages, en l’absence de modifications post-chargement.

Mais cette structuration est assez réaliste par rapport à l’usage qui est souvent fait des contenus binaires, qui sont créés ou effaçés d’un seul tenant, mais dont l’intérieur n’est jamais modifié. Car qui saurait changer des pixels dans une image JPEG ou une phrase dans un PDF avec une requête UPDATE?

Conclusion

Dans ce billet, on a pas mal regardé la structure interne de ces contenus binaires, et observé à travers cet exemple comment une différence de paramétrage de 48 octets a des conséquences finalement non négligeables sur des données de grande taille, ici en faveur du bytea sur l’espace disque.

N’oubliez pas que ce résultat ne s’applique pas forcément à vos données, ça dépend comment elles se compressent et comment ces tables sont mises à jour sur le temps long.

Dans un ou deux autres billets à venir, j’essaierai de détailler d’autres éléments du tableau de comparaison en haut de page, avec d’autres différences assez nettes sur certains points, certaines en faveur des objets larges, d’autres en faveur des bytea.

PostgreSQL 10 est sortie il y a quelques semaines et un premier correctif de sécurité a été publié le 10 novembre.

Comme chaque année, la sortie d’une nouvelle version s’accompagne de la fin du
support d’une version précédente. En l’occurence, c’est PostgreSQL 9.2, sortie
en 2012, qui est désormais cataloguée comme “End Of Life” (EOL).

Pour les nostalgiques, la version 9.2 était une étape importante puisqu’elle a marqué l’arrivée du type JSON et de la réplication Hot Standby en cascade. 5 ans plus tard, PostgreSQL 10 complète le Hot Standby avec la réplication logique , et fait jeu égal avec MongoDB avec des fonctionnalités JSON pleinement intégrées : indexation, procédures stockées PL/V8, recherche plein texte

Tout ça pour vous dire que si vous avez des instances PostgreSQL 9.2 (ou antiérieures) en production, il est temps de prévoir une montée de version dès que possible… Vous ne serez pas déçus !

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

Comment calculer les lignes qui sont dans une table mais pas dans une autre et vice-versa, sans préjuger de la structure des tables, ni du fait qu’il y ait une clef primaire et des colonnes qui la portent éventuellement?

Le principe de base

Les opérateurs ensemblistes EXCEPT et UNION nous amènent une solution assez simple et élégante, car on peut voir les différences entre deux tables T1 et T2 de même structure comme:

 T1 except T2 (les lignes présentes dans T1 et pas dans T2)
   UNION  
 T2 except T1 (les lignes qui sont dans T2 et pas dans T1)

On peut aussi chercher à exprimer ça de manière assez générique, c’est-à-dire que T1 et T2 puissent être des paramètres plutôt que d’écrire une requête différente pour chaque couple de tables (T1,T2) qu’on peut être amené à comparer.

Faire du SQL générique, c’est souvent faire du SQL dynamique à l’aide de plpgsql. Voyons à quoi pourrait ressembler une fonction plpgsql qui nous renverrait les différences entre deux tables quelconques, un peu comme la sortie d’un diff avec des signes ‘+’ et ‘-‘ en tête de ligne pour marquer les insertions et suppressions.

Transférer toute une ligne dans une valeur

Pour pouvoir sortir toute une ligne sous la forme d’un champ, toujours sans préjuger de sa structure, on utilise le fait que PostgreSQL permet la conversion d’un enregistrement du type de la table vers un champ texte avec un simple CAST, soit une expression du style: SELECT table.*::text FROM table...

Le résultat a un format compatible avec un constructeur de ligne ROW(...), c’est-à-dire qui ressemble un peu à une ligne CSV avec des parenthèses autour. En gros:

• les champs sont séparés par des virgules.
• des guillemets délimitent les champs dès que nécessaire.
• les guillemets internes aux champs sont échappés.

Il reste à ajouter un ‘+’ ou ‘-‘ dans un champ à part et à retourner un type TABLE("+/-" text, text) qui représente le signe suivi de la ligne insérée ou supprimée entre une table et l’autre.

Passer en paramètre les tables à la fonction

Pour passer en paramètre nos tables à une fonction plpgsql, on pourrait utiliser leur nom en type text ou name, mais il y a plus intéressant: le type regclass.

C’est un type “alias d’OID” pour les entrées de pg_class, c’est-à-dire que le moteur SQL évalue 'nom_objet'::regclass au moment de l’exécution en cherchant cet objet dans le catalogue, avec un comportement qui gère un tas de détails pour nous:

• le fait que le nom puisse être qualifié par un schéma ou non, suivant que le search_path en cours inclut ou non ce schéma.

• la sensibilité à la casse (majuscule/minuscule) du nom suivant qu’il soit entouré ou non de guillemets.

• une erreur est déclenchée par la conversion de type vers regclass si l’objet n’est pas trouvé dans le catalogue. Donc notre fonction qui prend du regclass en entrée ne démarrera même pas si on lui donne des arguments incorrects, ce qui est sécurisant car on va quand même injecter ces arguments dans une requête SQL.

En bref regclass permet de fournir un nom de table dynamique via un paramètre en lui appliquant la même gestion que si ce nom avait été présent dans une requête statique. En interne ce type représente un OID mais on n’a pas vraiment besoin de le savoir si on ne veut pas fouiller plus que ça (dans le cas contraire \dC regclass sous psql sera utile pour examiner les différentes conversions).

La fonction

Avec ces éléments, le corps de fonction n’a plus qu’à appliquer format() sur le modèle de requête, en lui faisant remplacer les %I par les références aux tables passées en type regclass, et exécuter le résultat.

Avec RETURN QUERY EXECUTE format(...), une seule instruction plpgsql suffit à faire tout:

CREATE FUNCTION diff_tables(table1 regclass, table2 regclass) 
   RETURNS TABLE("+/-" text, ligne text)
AS $func$
BEGIN
  RETURN QUERY EXECUTE format($$
     SELECT '+', d1.*::text FROM (
        SELECT * FROM %I
           EXCEPT
        SELECT * FROM %I
     ) AS d1

     UNION ALL

     SELECT '-', d2.*::text FROM (
        SELECT * FROM %I
           EXCEPT
        SELECT * FROM %I
     ) AS d2
   $$, table2, table1, table1, table2);
END
$func$ language plpgsql;

Exemple d’utilisation

Le format, un peu similaire à la commande diff avec une ligne par différence, est pratique pour comparer des résultats obtenus avec des résultats supposés, par exemple dans des tests de non-régression, quand on s’attend à des déviations nulles ou faibles.

On peut aussi mesurer des différences entre avant et après un traitement, comme une installation. Par exemple si on fait un CREATE EXTENSION, le diff avant/après de pg_extension va donner la chose suivante:

=# create temporary table backup_pg_extension as select * from pg_extension;
SELECT 1

=# create extension ltree;
CREATE EXTENSION

=# select * from diff_tables('backup_pg_extension', 'pg_extension');
 +/- |          ligne          
-----+-------------------------
 +   | (ltree,10,2200,t,1.1,,)
(1 ligne)

PostgreSQL offre des triggers qui peuvent se déclencher chaque fois qu’une instruction est exécutée (AFTER ou BEFORE STATEMENT), ou chaque fois qu’une ligne est affectée (AFTER ou BEFORE ROW). Jusqu’avant la version 10, seules les fonctions associées au second type pouvaient accéder aux données modifiées par l’instruction déclencheuse, à travers les pseudo-variables OLD et NEW représentant l’état avant/après de la ligne affectée.

A compter de la version 10, les triggers AFTER STATEMENT peuvent avoir accès à l’ensemble des lignes modifiées, avant et après changement, à travers un nouveau genre de pseudo-variable de type table. Concrètement, la nouveauté est accessible via cette syntaxe, par exemple pour DELETE:

CREATE TRIGGER nom_trigger AFTER DELETE ON nom_table
REFERENCING OLD TABLE AS OLD
FOR EACH STATEMENT
EXECUTE PROCEDURE nom_procedure();

grâce à quoi dans la fonction, OLD sera utilisable comme une table en lecture seule.

Quelle est l’utilité d’accéder globalement aux lignes changées plutôt qu’une par une?

D’abord il y a d’autres SGBDs qui utilisent cette méthode, parfois exclusivement. Par exemple, MS-SQL server n’offre pas de déclenchement par ligne, mais uniquement par instruction, avec les données concernées dans des pseudo-tables inserted et deleted. Avec la version 10, il devient plus facile de porter ces trigger vers PostgreSQL, puisqu’on peut reproduire cette logique directement.

Il y aussi et surtout un intérêt pour les performances, dans les cas où il vaut mieux faire des opérations ensemblistes sur ces données plutôt que de les traiter ligne par ligne.

Voyons ça sur un exemple avec un benchmark tout simple.

Un schéma de test

Imaginons qu’on ait un million de documents, et une centaine de labels (tags) pour les catégoriser, avec ces structures de tables:

CREATE TABLE document (
 id serial primary key,
 title text,
 content text
);

CREATE TABLE tag (
 id serial primary key,
 name text
);

CREATE TABLE doc_tag (
 doc_id integer references document(id),
 tag_id integer references tag(id),
 unique(doc_id,tag_id)
);

L’information disant, pour chaque label, à combien de documents il a été affecté s’obtient normalement par:

SELECT tag_id, count(*) FROM doc_tag GROUP BY tag_id;

ou pour un seul label:

 SELECT count(*) FROM doc_tag WHERE tag_id =
  (SELECT tag_id FROM tag WHERE name = :nom_label);

Mais quand on a beaucoup d’entrées dans doc_tag du fait qu’il y a beaucoup de documents, ces requêtes seront lentes.

Si on sait que nos applis ont besoin de cette information instantanément, typiquement on va matérialiser et maintenir à jour un compteur permanent avec une seule ligne par label, dans une table de ce genre:

CREATE TABLE tag_count (
  tag_id integer references tag(id),
  cnt integer,
  unique(tag_id)
);

Si on part d’une table doc_tag déjà remplie on initialisera ces compteurs avec:

INSERT INTO tag_count(tag_id,cnt)
  SELECT tag_id,count(*) FROM doc_tag GROUP BY tag_id;

(sans les compteurs à zéro qu’on évite pour simplifier l’exemple).

Puis va créer un trigger qui met à jour ce compteur pour toute attribution ou désattribution d’un label.

En mode FOR EACH ROW, le code pour une version 9.5+ ressemblera à ça:

CREATE FUNCTION row_update_tag_count() RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
      INSERT INTO tag_count(tag_id,cnt)
       values (NEW.tag_id,1)
      ON CONFLICT (tag_id) DO UPDATE set cnt = tag_count.cnt + 1;

  ELSIF TG_OP = 'DELETE' THEN
      UPDATE tag_count SET cnt = cnt - 1
      WHERE tag_id = OLD.tag_id;
  END IF;

  RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER trigger1 AFTER INSERT OR DELETE on doc_tag 
FOR EACH ROW
EXECUTE PROCEDURE row_update_tag_count();

En mode FOR EACH STATEMENT, le code pour une version 10+ pourrait ressembler à ça:

CREATE FUNCTION set_update_tag_count() RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
      INSERT INTO tag_count(tag_id,cnt)
      select tag_id,count(*) AS insert_count from NEW group by tag_id
      ON CONFLICT (tag_id) DO UPDATE set cnt = tag_count.cnt + excluded.cnt;

  ELSIF TG_OP = 'DELETE' THEN
      UPDATE tag_count SET cnt = cnt - d.delete_count
        FROM (select tag_id,count(*) AS delete_count from OLD group by tag_id) AS d   
      WHERE tag_count.tag_id = d.tag_id;
  END IF;

  RETURN NULL;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger2 AFTER INSERT on doc_tag 
REFERENCING NEW TABLE AS NEW
FOR EACH STATEMENT
EXECUTE PROCEDURE set_update_tag_count();

CREATE TRIGGER trigger3 AFTER DELETE on doc_tag 
REFERENCING OLD TABLE AS OLD
FOR EACH STATEMENT
EXECUTE PROCEDURE set_update_tag_count();

Ici on ne peut pas regrouper dans le même trigger INSERT ET DELETE (bien qu’associés à la même fonction) pour des questions de syntaxe, car la doc nous dit:

OLD TABLE may only be specified once, and only on a trigger which can fire on UPDATE or DELETE. NEW TABLE may only be specified once, and only on a trigger which can fire on UPDATE or INSERT

Le test

Il consiste simplement à faire un INSERT unique de 10000 tags, puis 20000, 30000 etc. jusqu’à 500000, tirés au hasard et distribués sur les documents de manière équiprobable.

Le but est de mettre en évidence la différence sur des insertions en masse entre les différents de type de trigger. On mesure le temps pris dans trois cas:

• sans trigger pour avoir un temps de base.
• avec trigger1 (FOR EACH ROW)
• avec trigger2 (FOR EACH STATEMENT)

Avant chaque insertion la table doc_tag subit un TRUNCATE pour la ramener à zéro physiquement, index inclus, et autovacuum est désactivé pour éviter d’interférer.

Le résultat

Ce graphe montre les temps d’exécution en fonction du nombre de lignes insérées, sur Pg10 beta3 avec la configuration entièrement par défaut. Les lignes ne sont pas cumulées, chaque INSERT se faisant avec dog_tag et tag_count initialement vides.

C’est sans surprise que le trigger FOR EACH ROW fait s’envoler le temps d’exécution sur une insertion en masse, par rapport au cas où il n’y a pas de trigger.

Ce qui n’est pas forcément aussi prévisible est que quel que soit le volume de l’INSERT, la différence est insignifiante entre le cas FOR EACH STATEMENT et le cas où il n’y a aucun trigger,

Autrement dit, le coût induit par trigger2 est quasiment nul, alors que ce n’est pas du tout le cas pour trigger1.

En conclusion, une application avec ce style de comptage par trigger et de l’écriture en masse a potentiellement beaucoup à gagner, après un passage en PostgreSQL 10+, à les convertir de FOR EACH ROW en FOR EACH STATEMENT.

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.