PostgreSQL La base de donnees la plus sophistiquee au monde.

La planete francophone de PostgreSQL

mercredi 23 mai 2018

Thomas Reiss

PostgreSQL 11 : élimination dynamique de partitions

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

par Thomas Reiss le mercredi 23 mai 2018 à 08h19

mercredi 16 mai 2018

Sébastien Lardière

PostgreSQL Hebdo #15

par Sébastien Lardière le mercredi 16 mai 2018 à 14h50

lundi 14 mai 2018

Loxodata

PostgreSQL 10.4 et autres correctifs

2018-05-10 Publication de mises à jour de sécurité

Le PostgreSQL Global Development Group a publié une mise à jour de sécurité pour toutes les versions supportées du système de gestion de base de données PostgreSQL. Les mises à jour incluent les versions 10.4, 9.6.9, 9.5.13, 9.4.18 et 9.3.23.

L’objectif de cette publication est de corriger une faille de sécurité ainsi que plusieurs bogues rapportés ces 3 derniers mois. Il est conseillé aux utilisateurs de faire la mise à jour le plus tôt possible.

La section « Mise à jour » présente les étapes post-installation des correctifs de sécurité et de « volatilité incorrecte et marquage de sécurité pour la parallélisation ».

PostgreSQL a changé son système de numérotation avec la publication de la version 10.0. En conséquence, une mise à jour de 10.0, 10.1, 10.2 ou 10.3 vers 10.4 est considérée comme une mise à jour mineure.

Problème de sécurité

Une vulnérabilité a été fermée par cette publication :

  • CVE-2018-1115: Liste de contrôle d’accès à la fonction pg_logfile_rotate() trop permissive.

La section « Mise à jour » ci-dessous présente les étapes post-installation.

Corrections de bogues et améliorations

Cette mise à jour corrige également plus de 50 bogues rapportés au cours des derniers mois. Certains n’affectent que la version 10, mais beaucoup affectent toutes les versions supportées.

Ces corrections comprennent :

  • Correction du marquage incorrect de volatilité (volatility) et sécurisation de la parallélisation (parallel-safety) sur plusieurs fonctions internes pour assurer les optimisations du planificateur de requête ;
  • Plusieurs corrections sur le partitionnement, incluant de potentiels plantages tout en autorisant TRUE et FALSE à être utilisés comme bornes de partitions ;
  • Correction lorsque une nouvelle valeur de TOAST pouvait être assignée à un TOAST OID mort mais pas encore effacé (dead-but-not-yet-vacuumed), qui se traduit par une erreur du type “unexpected chunk number 0 (expected 1) for toast value nnnnn” ;
  • Correction sur “CREATE TABLE … LIKE” avec une colonne identité en bigint sur une plateforme 32 bits ;
  • Correction de la fuite mémoire lors de l’exécution d’une requête qui effectue des jointures de hashage (hash-join) de façon répétée ;
  • Correction de plusieurs plantages de requêtes utilisant GROUPING SET ;
  • Evite l’échec de l’interruption lors d’annulation de requête (query-cancel) ou fin de session (session-termination) lors du commit d’un transaction préparée (prepared transaction) ;
  • Réduction du verrouillage lors de la planification d’un worker d’autovacuum, ce qui prévient la perte potentielle de l’accès concurrent des workers ;
  • Correction d’une exécution éventuellement ralentie de REFRESH MATERIALIZED VIEW CONCURRENTLY ;
  • Plusieurs corrections concernant les plans d’exécution utilisant les parcours d’index seuls (index-only scans).
  • Evite les verrous d’interblocage (deadlocks) lors de commandes concurrentes de CREATE INDEX CONCURRENTLY sous isolation de transaction SERIALIZABLE ou REPEATABLE READ.
  • Plusieurs corrections pour les index SP-GiST, incluant une recherche en fonction des collations sur les colonnes de texte
  • Correction de plusieurs bogues relatifs au comptage du nombre de tuples dans les index partiels GiST, SP-GiST et Bloom.
  • Plusieurs corrections pour le décodage logique et la réplication
  • Correction de la mauvaise mise entre quotes (misquoting) des valeurs des variables de liste GUC (e.g. local_preload_libraries, session_preload_libraries, shared_preload_libraries, temp_tablespaces)
  • Plusieurs corrections pour pg_stat_activity
  • Plusieurs corrections pour ecpg
  • Correction de pg_recvlogical pour assurer la compatibilité avec les versions antérieures à PostgreSQL 10.
  • Plusieurs corrections pour pg_rewind

Cette mise à jour contient également la publication 2018d de tzdata, avec des mises à jour pour la Palestine et l’Antarctique (base Casey), auxquelles viennent s’ajouter des corrections historiques pour le Portugal et ses colonies, tout comme Enderbury, la Jamaïque, les îles Turques-et-Caïques, et l’Uruguay.

Mise à jour

Toutes les mises à jour sont cumulatives. Comme les autres publication mineures, pour les utilisateurs il n’est pas nécessaire de faire de dump suivi d’un rechargement des données ou d’utiliser pg_upgrade pour appliquer cette mise à jour (cependant pour cette publication, référez-vous aux notes sur « la volatilité et le marquage de sécurité de la parallélisation ») ; il suffit alors simplement d’arrêter PostgreSQL et de mettre à jour les binaires.

Les utilisateurs ayant sauté une ou plusieurs mises à jour, peuvent avoir besoin d’effectuer des étapes additionnelles après la mise à jour. Veuillez vous référer aux notes des précédentes versions pour plus de détails.

Étapes post-installation pour CVE-2018-1115

Si « adminpack » est installé, dans les versions 9.6 ou 10, l’administrateur de base de données devra alors effectuer les commandes suivantes dans toutes les base de données dans lesquelles adminpack a été installé :

ALTER EXTENSION adminpack UPDATE;

Étapes post-installation pour le marquage des fonctions

Fonctions qui doivent être marquées « volatile »

  • cursor_to_xml
  • cursor_to_xmlschema
  • query_to_xml
  • query_to_xml_and_xmlschema
  • query_to_xmlschema

Fonctions qui doivent être marquées « parallel-unsafe »

  • binary_upgrade_create_empty_extension,
  • brin_desummarize_range
  • brin_summarize_new_values
  • brin_summarize_range
  • cursor_to_xml
  • cursor_to_xmlschema
  • gin_clean_pending_list
  • pg_import_system_collations
  • ts_rewrite
  • ts_stat

Si vous utilisez des fonctions parmi celles ci-dessus, vous pouvez mettre à jour le marquage d’une des manières suivantes :

Option 1 : Mettre à jour le marquage manuellement dans la table “pg_proc” de toutes les bases de données utilisant ces fonctions. Pour réaliser cela, exécuter les commandes suivantes en tant que super-utilisateur :

/* Functions that should be marked "volatile" */
ALTER FUNCTION pg_catalog.cursor_to_xml(refcursor, int, boolean, boolean, text) VOLATILE;
ALTER FUNCTION pg_catalog.cursor_to_xmlschema(refcursor, boolean, boolean, text) VOLATILE;
ALTER FUNCTION pg_catalog.query_to_xml(text, boolean, boolean, text) VOLATILE;
ALTER FUNCTION pg_catalog.query_to_xml_and_xmlschema(text, boolean, boolean, text) VOLATILE;
ALTER FUNCTION pg_catalog.query_to_xmlschema(text, boolean, boolean, text) VOLATILE;
/* Functions that should be marked "parallel-unsafe" */
ALTER FUNCTION pg_catalog.binary_upgrade_create_empty_extension(text, text, bool, text, _oid, _text, _text) PARALLEL UNSAFE;
ALTER FUNCTION pg_catalog.brin_desummarize_range(regclass, bigint) PARALLEL UNSAFE;
ALTER FUNCTION pg_catalog.brin_summarize_new_values(regclass) PARALLEL UNSAFE;
ALTER FUNCTION pg_catalog.brin_summarize_range(regclass, bigint) PARALLEL UNSAFE;
ALTER FUNCTION pg_catalog.cursor_to_xml(refcursor, int, boolean, boolean, text) PARALLEL UNSAFE;
ALTER FUNCTION pg_catalog.cursor_to_xmlschema(refcursor, boolean, boolean, text) PARALLEL UNSAFE;
ALTER FUNCTION pg_catalog.gin_clean_pending_list(regclass) PARALLEL UNSAFE;
ALTER FUNCTION pg_catalog.pg_import_system_collations(regnamespace) PARALLEL UNSAFE;
ALTER FUNCTION pg_catalog.ts_rewrite(tsquery, text) PARALLEL UNSAFE;
ALTER FUNCTION pg_catalog.ts_rewrite(tsquery, tsquery, tsquery) PARALLEL UNSAFE;
ALTER FUNCTION pg_catalog.ts_stat(text) PARALLEL UNSAFE;
ALTER FUNCTION pg_catalog.ts_stat(text, text) PARALLEL UNSAFE;

Option 2 : Lancer pg_upgrade dans une version contenant le marquage correct des données (version 10.4 et supérieure).

Liens

par contact@loxodata.com (Loxodata) le lundi 14 mai 2018 à 17h00

mercredi 9 mai 2018

Sébastien Lardière

PostgreSQL Hebdo #14

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

par Sébastien Lardière le mercredi 9 mai 2018 à 08h20

mercredi 25 avril 2018

Sébastien Lardière

PostgreSQL Hebdo #13

par Sébastien Lardière le mercredi 25 avril 2018 à 10h05

mercredi 18 avril 2018

Philippe Florent

INCLUDE

Parcours d'index seul et indexation couvrante, mise à jour avec PostgreSQL 11 devel

mercredi 18 avril 2018 à 18h15

lundi 16 avril 2018

Adrien Nayrat

Les évolutions de PostgreSQL pour le traitement des fortes volumétries

Introduction Depuis quelques années, PostgreSQL s’est doté de nombreuses améliorations pour le traitement des grosses volumétries. Ce premier article va tenter de les lister, nous verrons qu’elles peuvent être de différents ordres : Parallélisation Amélioration intrinsèque du traitement des requêtes Partitionnement Méthodes d’accès Tâches de maintenance Ordres SQL Afin de conserver de la clarté, l’explication de chaque fonctionnalité restera succincte. Note : cet article a été écrit durant la phase de développement de la version 11.

lundi 16 avril 2018 à 07h00

vendredi 13 avril 2018

Sébastien Lardière

PostgreSQL Hebdo #12

par Sébastien Lardière le vendredi 13 avril 2018 à 15h15

jeudi 12 avril 2018

Philippe Florent

(pas de) MERGE

Mise à jour de la page sur l'UPSERT à l'occasion de l'introduction puis de l'annulation de la commande MERGE dans PostgreSQL 11 (version en développement)

jeudi 12 avril 2018 à 20h30

vendredi 6 avril 2018

Sébastien Lardière

PostgreSQL Hebdo #11

par Sébastien Lardière le vendredi 6 avril 2018 à 16h00

vendredi 30 mars 2018

Sébastien Lardière

PostgreSQL Hebdo #10

par Sébastien Lardière le vendredi 30 mars 2018 à 19h25

vendredi 23 mars 2018

Loxodata

De retour de PGDay Paris

PGDay Paris s’est tenu à l’espace Saint Martin le 15 mars dernier. Si vous n’avez pas pu y aller (l’évènement affichait complet), sachez qu’il y avait une captation vidéo des conférences. Cependant, la véritable richesse de ces rendez-vous se trouve lors les échanges au moment des pauses et des moments sociaux qui sont aussi instructifs que les conférences elles-mêmes ! Nous y étions et voici ce que nous y avons vu.

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 patch, comment le créer ?

Lien : Support de présentation

Constraints: a Developer’s Secret Weapon

Par Will Leinweber de Citus Data

Lors de cette conférence, Will a expliqué ce que ses différentes erreurs – alors qu’il était développeur – lui avaient appris sur l’utilisation des bases de données. Son propos est centré sur la cohérence des données et comment l’implanter correctement. En partie à l’aide des contraintes, mais pas uniquement. Une conférence conseillée pour tous les développeurs !

Lien : Support de présentation

Being a better developer with Explain

Par Louise Grandjonc d’Ulule

Cette conférence est éblouissante. Louise est une développeuse qui est devenue experte dans l’optimisation de requêtes SQL et l’utilisation de l’EXPLAIN de Postgres par la force des choses. En plus, d’avoir emmagasiné une très grande connaissance sur le sujet, elle l’explique extrêmement bien !

Lien : Support de présentation

Triggers - Friends To Handle With Care

Par Charles Clavadetscher de Swiss PostgreSQL Users Group

Une conférence très intéressante sur les dangers des triggers et les cas où ils peuvent être utiles.

Change Data Capture for a brave new world

Par Hannu Valtonen d’Aiven

Le Change Data Capture (CDC) est un ensemble de méthodes logicielles utilisées pour identifier, tracer, extraire et transmettre les changements de données. Dans sa présentation, Hannu reprend pour nous tout l’historique du CDC puis nous présente les solutions modernes de CDC disponibles dans PostgreSQL.

Herd of containers

Par Saâd Dif de BlaBlaCar

BlablaCar utilise différentes technologies pour gérer ses données. Naturellement, c’est PostgreSQL et la cartouche géographique PostGIS qui sont utilisés pour gérer les données géographiques. Saâd Dif nous explique comment ces outils ont été intégrés chez BlaBlaCar. Si la manière dont le système a été implanté a pu laisser certains perplexes, il est toujours intéressant de voir une entreprise expliquer comment elle utilise PostgreSQL.

A look at the Elephants trunk - PostgreSQL 11

Par Magnus Hagander de Redpill Linpro

Même si le dernier Commit Fest de PostgreSQL n’est pas fini, nous avons déjà une bonne idée de ce que sera PostgreSQL 11. Bien sûr, de nombreuses fonctionnalités sont en attente de relecture et tout le monde est invité à venir relire les patchs pour qu’ils soient commités.

Lien : Support de présentation

Quelques lightning talks

Les lightning talks (ou conférences éclair) sont des présentations de cinq minutes maximum.

Si leur durée est courte, le contenu, lui, peut être très important.

Trois présentations ont été faites.

La première présentation a été celle de Marco Slot de Citus Data. Marco nous a présenté comment distribuer des données temporelles géographiques.

La seconde, présentée par Stéphane Schildknecht, a dévoilé le choix d’une date pour rendre hommage à votre DBA préféré·e. Le DBA Appreciation Day, qui aura lieu chaque premier vendredi de juillet est une initiative de Federico Campoli à laquelle nous adhérons.

La dernière, présentée par Lætitia Avrot et Louise Grandjonc, a annoncé la création du groupe Postgres Women. Derrière ce groupe se retrouve l’idée que les femmes sont souvent sous-représentées dans les conférences et les projets techniques. La création de ce groupe, ouvert à tou·te·s, prouve l’ouverture de la communauté à tous les publics. Le groupe Postgres Women peut être joint par twitter : @PostgresWomen et sur une liste dédiée : women@lists.postgresql.org (inscription).

Et pour finir, une tombola !

Nous félicitons tous les heureux gagnants de la tombola. Bien sûr, le tirage au sort a été effectué avec PostgreSQL.

SELECT nom, prenom FROM participant ORDER BY random() LIMIT 1

Loxodata est très fier d’offrir un exemplaire du livre de Sébastien Lardière « Administration et exploitation de vos bases de données » ainsi qu’une place pour PGDay Paris l’année prochaine à notre heureux gagnant : Thomas McKay.

par contact@loxodata.com (Loxodata) le vendredi 23 mars 2018 à 09h55

vendredi 16 mars 2018

Sébastien Lardière

PostgreSQL Hebdo #9

Lu cette semaine :

par Sébastien Lardière le vendredi 16 mars 2018 à 17h00

mercredi 14 mars 2018

Daniel Verite

Schéma public et CVE-2018-1058

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.

par Daniel Vérité le mercredi 14 mars 2018 à 12h20

mardi 13 mars 2018

Pierre-Emmanuel André

Mettre en place une streaming replication avec PostgreSQL 10

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.

mardi 13 mars 2018 à 06h28

samedi 10 mars 2018

Adrien Nayrat

Replication Logique Fonctionnement Interne

Table des matières Introduction Sérialisation des changements sur disque Cas avec une seule transaction Cas avec deux transactions CPU Statistiques sur les enregistrements Réseau Réplication Trafic OLTP Bilan Introduction J’ai présenté la réplication à travers plusieurs articles : PostgreSQL 10 et la réplication logique - Fonctionnement PostgreSQL 10 et la réplication logique - Mise en oeuvre PostgreSQL 10 et la réplication logique - Restrictions Cet article va creuser un peu plus le sujet.

samedi 10 mars 2018 à 11h19

vendredi 2 mars 2018

Sébastien Lardière

PostgreSQL Hebdo #8

Lu cette semaine :

par Sébastien Lardière le vendredi 2 mars 2018 à 17h00

vendredi 23 février 2018

Sébastien Lardière

PostgreSQL Hebdo #7

Lu cette semaine :

À noter dans vos agendas :

par Sébastien Lardière le vendredi 23 février 2018 à 15h50

vendredi 16 février 2018

Sébastien Lardière

PostgreSQL Hebdo #6

Lu cette semaine :

À noter dans vos agendas :

par Sébastien Lardière le vendredi 16 février 2018 à 17h25

vendredi 9 février 2018

Sébastien Lardière

PostgreSQL Hebdo #5

Lu cette semaine :

À noter dans vos agendas :

par Sébastien Lardière le vendredi 9 février 2018 à 16h05

vendredi 2 février 2018

Sébastien Lardière

PostgreSQL Hebdo #4

Lu cette semaine :

À noter dans vos agendas :

par Sébastien Lardière le vendredi 2 février 2018 à 17h13

vendredi 26 janvier 2018

Sébastien Lardière

PostgreSQL Hebdo #3

Lu cette semaine :

Un pas de coté :

par Sébastien Lardière le vendredi 26 janvier 2018 à 20h24

vendredi 19 janvier 2018

Sébastien Lardière

PostgreSQL Hebdo #2

Lu cette semaine :

par Sébastien Lardière le vendredi 19 janvier 2018 à 16h40

jeudi 11 janvier 2018

Sébastien Lardière

PostgreSQL Hebdo #1

Veille hebdomadaire autour de PostgreSQL, la plupart en anglais :

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

par Sébastien Lardière le jeudi 11 janvier 2018 à 21h00

mercredi 27 décembre 2017

Daniel Verite

Large objects ou bytea: les différences de verrouillage

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

Paramètre Valeur
max_locks_per_transaction 64
max_connections 64
max_prepared_transactions 0

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.

par Daniel Vérité le mercredi 27 décembre 2017 à 14h04

dimanche 24 décembre 2017

Guillaume Lelarge

Changements dans la 2è édition de "PostgreSQL - Architecture et notions avancées"

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

par Guillaume Lelarge le dimanche 24 décembre 2017 à 08h53

jeudi 7 décembre 2017

Damien Clochard

De grandes sociétés françaises appellent les éditeurs logiciels à supporter PostgreSQL

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.

PostgreSQL Cross-Enterprise Work Group

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.

par Damien Clochard le jeudi 7 décembre 2017 à 17h52

mercredi 29 novembre 2017

Pierre-Emmanuel André

OpenBSD / PostgreSQL / Authentification

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.

mercredi 29 novembre 2017 à 11h31

mardi 21 novembre 2017

Daniel Verite

pspg, un pager dédié à psql

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

Copie d'écran pspg

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.

par Daniel Vérité le mardi 21 novembre 2017 à 13h15

mercredi 15 novembre 2017

Daniel Verite

Large objects ou bytea?

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:

Caractéristique Objet Large Colonne Bytea
Taille max par donnée 4 To 1 Go
Segmentation intra-donnée Oui Non
Stockage segmenté TOAST Non Oui
Compression LZ par segment sur totalité
Une seule table par base Oui Non
Référence indirecte (OID) Oui Non
Accès extra-requête Oui Non
Réplication logique Non Oui
Lignes par donnée Taille / 2048 1 (+ Toast)
Partitionnement Impossible Possible
Transferts en binaire Toujours Possible mais rare
Verrous en mémoire partagée Oui Non
Choix du tablespace Non Oui
Triggers possibles Non Oui
Droits d’accès par donnée Oui Non (hors RLS*)
Chargement par COPY Non Oui
Disponibilité dans langages Variable Toujours

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

Fonction SQL Fonction libpq Fichier (libc)
lo_create lo_create creat
lo_open lo_open open
loread lo_read read
lowrite lo_write write
lo_lseek[64] lo_lseek[64] lseek[64]
lo_tell[64] lo_tell[64] lseek/ftell
lo_truncate[64] lo_truncate[64] truncate
lo_close lo_close close
lo_unlink lo_unlink unlink
lo_import lo_import N/A
lo_export lo_export N/A
lo_put N/A N/A
lo_get N/A N/A
lo_from_bytea N/A N/A

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.

par Daniel Vérité le mercredi 15 novembre 2017 à 12h31

lundi 13 novembre 2017

Damien Clochard

Fin de parcours pour PostgreSQL 9.2

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 !

par Damien Clochard le lundi 13 novembre 2017 à 09h52

mercredi 11 octobre 2017

Thomas Reiss

Supervision de PostgreSQL 10 avec check_pgactivity

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 :

par Thomas Reiss le mercredi 11 octobre 2017 à 14h44

mardi 10 octobre 2017

Daniel Verite

Un diff générique entre deux tables

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)

par Daniel Vérité le mardi 10 octobre 2017 à 15h10

vendredi 1 septembre 2017

Daniel Verite

Du nouveau dans les triggers avec PostgreSQL 10

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.

Graphe performances triggers

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.

par Daniel Vérité le vendredi 1 septembre 2017 à 13h31

mardi 18 juillet 2017

Nicolas Gollet

Fitrage par IP avec les RLS

Depuis la version 9.5 de PostgreSQL, la sécurité au niveau des lignes (Row Level Security) a été introduite. Cette fonctionnalité permet de contrôler l'accès aux lignes d'une table de base de données en fonction des caractéristiques de l'utilisateur exécutant une requête.

Nous pouvons donc l'utiliser pour limiter, par exemple, le type de requêtes acceptées par rapport à l'adresse IP source du client.

La fonction interne PostgreSQL inet_client_addr() permet d'obtenir l'adresse IP du client de la connexion en cours.

Dans l'exemple ci-dessous, nous allons interdire toutes les requêtes de modification, d'insertion et de suppression sur la table "matable" lorsque l'adresse IP source de la connexion est '10.1.1.65'.

Par défaut lorsqu'un utilisateur ne rentre pas dans le critère d'une règle, celui si se voit refuser l'accès aux données.

Concernant la charge supplémentaire constatée, elle oscille entre 1% et 10% suivant le nombre de lignes rapporté par la transaction.

LINETPS RLS ON

TPS RLS OFF

DIFF%
14960056270667088
1004203045616358692
1000245852513655197
10000451345291699

La première colonne et le nombre de lignes récupéré par transaction, la seconde le nombre de transactions par seconde constaté lorsque le RLS est à ON, la troisième lorsque RLS est à OFF. La colonne DIFF est la différence entre les 2 en nombre de transactions.

Ces valeurs ont été obtenues par l'utilisation de pgbench sur une table contenant 1 colonne de taille fixe avec les paramètres suivants :

pgbench -n -j 6 -c 20 -P 1 -T 30 -U rlsdemo -h pg96.local.ng.pe -f rls_bench.sql rlsdemo

par Nicolas GOLLET le mardi 18 juillet 2017 à 19h45

lundi 12 juin 2017

Guillaume Lelarge

Début de la traduction du manuel de PostgreSQL v10

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.

par Guillaume Lelarge le lundi 12 juin 2017 à 19h45

mardi 6 juin 2017

Daniel Verite

mardi 23 mai 2017

Daniel Verite

Les branches if/else/endif dans psql (PostgreSQL 10)

Une nouveauté majeure du client psql de PostgreSQL 10 est le support des branchements conditionnels, exprimables via ces nouvelles méta-commandes:

\if EXPR1
  ...
\elif EXPR2
  ... 
\else
  ...
\endif

Voyons déjà quelles sont les différences entre cette approche et les instructions IF / ELSIF / ELSE / END IF du langage plpgsql, déjà disponibles dans les fonctions et les blocs anonymes DO, avec ce type de syntaxe:

 DO $$
   BEGIN
     IF expression-sql THEN
      -- instructions
     ELSIF autre-expression-sql
       -- instructions
     END IF;
   END
 $$ language plpgsql;

COMMIT et ROLLBACK conditionnel

Il se trouve que COMMIT ou ROLLBACK ne peuvent pas être initiés de l’intérieur d’un bloc DO, ce bloc étant confiné à la transaction dont il dépend. C’est le même modèle d’exécution que pour les fonctions, qui sont soumises à la même contrainte On peut y insérer des sous-transactions via SAVEPOINT, mais la transaction principale n’est réellement contrôlable que par le client SQL.

Justement les branches psql offrent une solution simple à ce problème, puisqu’un script peut maintenant comporter une séquence du type:

 BEGIN;
 -- écritures en base
 \if :mode_test
   \echo 'Mode test: Annulation transactionnelle des modifications'
   ROLLBACK;
 \else
   \echo 'Validation transactionnelle des modifications'
   COMMIT;
 \endif

La variable psql mode_test peut être initialisée de l’extérieur, comme une option, via un appel en shell de la forme: $ psql -v mode_test=1 -U user -d database, ce qui donne un équivalent du “–dry-run” qu’ont certaines commandes comme make.

Syntaxe des expressions

L’interpréteur psql n’est pas doté d’un évaluateur interne (pas encore?), et la condition derrière un \if doit être une chaîne de caractères interprétable en booléen, c’est-à-dire true et false ou leurs diminutifs t et f, ou 0 et 1, ou encore on et off.

Quand le script n’a pas la valeur à tester directement sous cette forme, il faut la produire, soit par une commande externe via l’opérateur backtick (guillemet oblique comme dans \set var `commande`), soit par une requête SQL.

Prenons à titre d’exemple le cas où il faut comparer deux numéros sémantiques de version au format MAJOR.MINOR.PATCHLEVEL (1, 2 ou 3 nombres), l’un correspondant à une version d’un ensemble d’objets déployés en base, l’autre à un niveau de mise à jour à faire via notre script.

Appeler le shell pour tester une condition

Un avantage d’utiliser une évaluation externe est qu’elle fonctionnera indépendamment de l’état de la session SQL (notamment non connectée ou dans une transaction en échec). Par ailleurs, il peut y avoir des situations où un programme externe est plus adapté. Dans l’exemple de la comparaison de version, les systèmes basés sur Debian ont la commande dpkg --compare-versions qui fait plutôt bien l’affaire, sinon cette entrée de stackoverflow propose diverses solutions.

Cependant l’opérateur backtick appelant une commande externe présente deux subtilités qui compliquent un peu la tâche avec \if:

  • il ne lit pas le statut de la commande mais ce qu’elle affiche sur sa sortie standard, alors que la plupart des commandes de test (à commencer par la commande test justement) n’affichent rien.
  • le standard en test shell est 0 pour positif et 1 pour négatif dans le statut, alors qu’on veut les valeurs inverses dans psql.

Sachant ça et en supposant bash comme shell, ça veut dire qu’on devra écrire:

\if `! dpkg --compare-versions ":version_db" ge ":version_script"; echo $?`

(si vous trouvez plus simple, n’hésitez pas à le dire en commentaire!)

A noter au passage que l’interpolation des variables psql par l’opérateur backtick est également une nouveauté de la version 10, c’est-à-dire qu’avec une version antérieure, les :version_db et :version_script auraient été présents tels quels dans la commande.

Et que se passe-t-il en cas d’erreur de la commande shell? Généralement, le résultat dans $? n’étant ni 1 ni 0 mais plutôt 2 et plus, le \if rejette cette valeur qui n’est pas assimilable à un booléen, émet un message d’erreur et poursuit l’exécution en supposant arbitrairement faux comme résultat de la comparaison.

Utiliser l’interpréteur SQL pour tester une condition

Bien sûr, on peut aussi produire la valeur de notre comparaison par une requête. La portabilité du SQL sera aussi préférable à des commandes shell si par exemple le script doit être déployé sous Windows. Même si bash et plein de commandes shell existent sous Windows (cf MSYS ou MSYS2) et que c’est plutôt une bonne idée de les installer, on ne peut pas trop espérer qu’elles le soient par défaut.

Pour la méthode SQL, cette entrée de dba.stackexchange: How to ORDER BY typical software release versions like X.Y.Z inspire une solution à base de tableaux d’entiers.

Le résultat booléen de la comparaison est transféré dans une variable via la méta-commande \gset en fin de requête:

SELECT (string_to_array(:version_db, '.')::int[] >=
        string_to_array(:version_script, '.')::int[]) AS a_jour \gset
\if :a_jour
   \echo 'Le schéma est déjà en version' :version_db
   \quit
\endif

Contrairement au cas de la commande shell, psql n’offre pas pour le moment de syntaxe pour mettre une requête SQL derrière un \if. Il faut nécessairement passer par une variable booléenne intermédiaire. Une consolation: on peut charger plusieurs variables en une seule requête puisque \gset apparie chaque colonne du résultat à une variable de même nom.

En cas d’erreur de la requête, ou si elle ne produit aucune ligne, et s’il y avait déjà une valeur dans les variables en question, elles restent inchangées. Il faudra se méfier dans les scripts qu’un \if ne teste pas la valeur précédente d’une variable dans le cas où une requête censée l’affecter a la moindre chance d’échouer. Au pire, on devra réinitialiser ces variables avant chaque requête, avec une séquence du style:

\unset var1
\unset var2
SELECT ... AS var1, ... AS var2 FROM tables... \gset
\if :var1   -- erreur et \if faux si problème avec la requête ci-dessus 
  ...
\elif :var2  -- erreur et \elif faux si problème avec la requête ci-dessus 
  ...
\else
  ...      -- mais cette branche sera exécutée si erreur de requête
\endif

Ca peut être l’occasion de rappeler qu’il est bon de mettre

\set ON_ERROR_STOP on

dans les scripts sensibles. Cette option est un peu l’équivalent du set -e du shell, elle provoque la sortie immédiate du script en cours en cas d’erreur.

par Daniel Vérité le mardi 23 mai 2017 à 10h01

mercredi 17 mai 2017

Daniel Verite

OpenData: importer les noms de domaines de l’AFNIC

Dans le cadre de l’initiative Open Data .fr, l’AFNIC met à disposition des fichiers de données actualisés régulièrement sur tous les noms de domaines qu’elle gère, ce qui permet à quiconque de produire notamment des statistiques.

Voyons comment importer ces données dans une base PostgreSQL.

Les fichiers sont au format ZIP contenant chacun un seul fichier CSV.
Les colonnes sont documentées dans le guide d’utilisation (PDF).

Le fichier principal “Fichier A” contient une ligne par domaine, soit à mars 2017 un peu plus de 4,5 millions de lignes:

$ wc -l 201703_OPENDATA_A-NomsDeDomaineEnPointFr.csv 
4539091 201703_OPENDATA_A-NomsDeDomaineEnPointFr.csv

Comme souvent dans les fichiers CSV, les noms de colonnes figurent à la première ligne. Il s’agit de:

"Nom de domaine";"Pays BE";"Departement BE";"Ville BE";"Nom BE";"Sous domaine";"
Type du titulaire";"Pays titulaire";"Departement titulaire";"Domaine IDN";"Date 
de création";"Date de retrait du WHOIS"

Les caractères sont au format iso-8859-1, et il y a un certain nombres d’accents dans le fichier, il faut donc en tenir compte pour l’import.

J’ai choisi de créer une seule table avec ces noms simplifiés:

CREATE TABLE domain_fr (
   domaine text,
   pays_be char(2),
   dept_be text,
   ville_be text,
   nom_be text,
   sous_dom text,
   type_tit text,
   pays_tit text,
   dept_tit text,
   idn smallint,
   date_creation date,
   date_retrait date
);

Les contenus peuvent être importés sans préfiltrage dans cette structure avec le COPY de PostgreSQL. L’import passe sans erreur en une quinzaine de secondes sur un serveur basique. Les commandes:

 SET datestyle TO european;
 SET client_encoding TO 'LATIN1';
 \copy domain_fr from '201703_OPENDATA_A-NomsDeDomaineEnPointFr.csv' with (format csv, header, delimiter ';')
 RESET client_encoding;

Faisons quelques requêtes au hasard pour tester les données. Le champ idn est à 0 ou 1 suivant qu’il s’agit d’un nom de domaine internationalisé, c’est-à-dire qui peut contenir des caractères Unicode au-delà du bloc “Basic Latin”. Pour voir combien sont concernés:

 SELECT idn,count(*) from domain_fr GROUP BY idn;
  idn |  count  
 -----+---------
    0 | 4499573
    1 |   39517
 (2 rows)

Pour voir la progression de ce type de domaine par année de création (et constater d’ailleurs qu’en nombre d’ouvertures c’est plutôt en régression après l’année de démarrage):

SELECT date_trunc('year',date_creation)::date as annee, count(*)
FROM domain_fr  WHERE idn=1 GROUP BY 1 ORDER BY 1;
   annee     | count 
 ------------+-------
  2012-01-01 | 20001
  2013-01-01 |  6900
  2014-01-01 |  4734
  2015-01-01 |  3754
  2016-01-01 |  3353
  2017-01-01 |   775
 (6 rows)

On peut aussi apprendre par exemple, quels sont les bureaux d’enregistrement (prestataires) les plus actifs. Regardons le top 10 pour 2016:

SELECT nom_be,count(*)
 FROM domain_fr
 WHERE date_creation>='2016-01-01'::date AND date_creation<'2017-01-01'
 GROUP BY 1
 ORDER BY 2 DESC
 LIMIT 10;
                                  nom_be                                  | count  
 -------------------------------------------------------------------------+--------
  OVH                                                                     | 207393
  1&1 Internet SE                                                         |  74446
  GANDI                                                                   |  63861
  ONLINE SAS                                                              |  15397
  LIGNE WEB SERVICES - LWS                                                |  14138
  AMEN / Agence des Médias Numériques                                     |  13907
  KEY-SYSTEMS GmbH                                                        |  12558
  PAGESJAUNES                                                             |  11500
  Ascio Technologies Inc. Danmark - filial af Ascio Technologies Inc. USA |   9303
  InterNetX GmbH                                                          |   9028

Sans surprise on retrouve les hébergeurs français populaires, avec OVH loin devant, mais aussi 1&1 (allemand) en deuxième.

L’Open Data ouvre la possibilité de croiser des données de sources diverses. Par exemple on pourrait être intéressé par les relations entre les villes françaises et ces noms de domaines.

Un fichier CSV des communes de France peut-être récupéré via l’OpenData gouvernemental. Celui-là est en UTF-8.

Ici on va importer seulement le nom et département des communes. Puis on va utiliser le module PostgreSQL pg_trgm (trigrammes) pour son opérateur de comparaison approchée de chaînes de caractères.

CREATE EXTENSION pg_trgm;

CREATE TABLE communes(nom_commune text,dept char(3));

/*
Le COPY de PostgreSQL ne permet pas de filtrer certaines colonnes, mais
c'est faisable indirectement via la clause PROGRAM appelant le cut d'Unix
*/
\copy communes(dept,nom_commune) FROM program '(cut -d";" -f5,9) < eucircos_regions_departements_circonscriptions_communes_gps.csv' WITH (format csv,delimiter ';',header)


CREATE INDEX trgm_idx1 on communes using gist(nom_commune gist_trgm_ops);
CREATE INDEX trgm_idx2 on domain_fr using gist(domaine gist_trgm_ops);

On va chercher à titre d’exemple les domaines contenant le terme metz et qui ont une correspondance lexicale avec une ville du département 57 (Moselle).

Le degré de similarité de l’opérateur % peut être réglé via le paramètre de configuration pg_trgm.similarity_threshold (ou à l’ancienne via la fonction set_limit()). Par défaut c’est 0,3. Plus la valeur est proche de 1, plus les résultats sont resserrés autour de la correspondance exacte.

SET pg_trgm.similarity_threshold TO 0.5;

WITH v as (SELECT domaine FROM domain_fr WHERE domaine LIKE '%metz%')
SELECT domaine,nom_commune FROM v join communes ON (dept='57' and domaine % nom_commune);

Ca donne 33 résultats:

            domaine            |     nom_commune     
-------------------------------+---------------------
 agmetzervisse.fr              | Metzervisse
 aikido-longeville-les-metz.fr | Longeville-lès-Metz
 a-metz.fr                     | Metz
 a-metz.fr                     | Metz
 a-metz.fr                     | Metz
 aquabike-metzervisse.fr       | Metzervisse
 aumetz.fr                     | Aumetz
 canton-metzervisse.fr         | Metzervisse
 i-metz.fr                     | Metz
 i-metz.fr                     | Metz
 i-metz.fr                     | Metz
 institut-metzervisse.fr       | Metzervisse
 judo-metzervisse.fr           | Metzervisse
 lorry-les-metz.fr             | Lorry-lès-Metz
 lorry-metz-57.fr              | Lorry-lès-Metz
 mairie-longeville-les-metz.fr | Longeville-lès-Metz
 mairie-longeville-les-metz.fr | Longeville-lès-Metz
 metzervisse.fr                | Metzervisse
 metzervisse1972.fr            | Metzervisse
 metz.fr                       | Metz
 metz.fr                       | Metz
 metz.fr                       | Metz
 metzinger.fr                  | Metzing
 metzmetz.fr                   | Metz
 metzmetz.fr                   | Metz
 metzmetz.fr                   | Metz
 mjc-metzeresche.fr            | Metzeresche
 mma-longeville-les-metz.fr    | Longeville-lès-Metz
 mma-montigny-les-metz.fr      | Montigny-lès-Metz
 montigny-les-metz.fr          | Montigny-lès-Metz
 moulins-les-metz.fr           | Moulins-lès-Metz
 pompiers-metzervisse.fr       | Metzervisse
 rx-montigny-les-metz.fr       | Montigny-lès-Metz

On voit clairement l’effet de la correspondance approchée avec “lorry-metz-57.fr” qui se trouve apparié avec “Lorry-lès-Metz”.

Un certain nombre de domaines (4684 exactement) commençent par la chaîne “mairie-“.

On peut à nouveau utiliser l’opérateur de proximité des chaînes de caractères pour chercher, sur un département particulier, quelles communes ont choisi le nommage du type mairie-nom-de-la-commune:

WITH v AS (SELECT domaine FROM domain_fr WHERE domaine LIKE 'mairie-%')
SELECT domaine,nom_commune FROM v JOIN communes ON (dept='06' AND domaine % nom_commune);

Résultats:

             domaine              |      nom_commune      
----------------------------------+-----------------------
 mairie-beaulieu-sur-mer.fr       | Beaulieu-sur-Mer
 mairie-la-turbie.fr              | La Turbie
 mairie-le-cannet.fr              | Le Cannet
 mairie-mandelieu-la-napoule.fr   | Mandelieu-la-Napoule
 mairie-roquefort-les-pins.fr     | Roquefort-les-Pins
 mairie-roquefort-les-pins.fr     | Roquefort-les-Pins
 mairie-roquesteron.fr            | Roquesteron
 mairie-saint-jean-cap-ferrat.fr  | Saint-Jean-Cap-Ferrat
 mairie-saint-martin-du-mont.fr   | Saint-Martin-du-Var
 mairie-saint-paul.fr             | Saint-Paul
 mairie-villefranche-sur-mer.fr   | Villefranche-sur-Mer
 mairie-villefranche-sur-saone.fr | Villefranche-sur-Mer
 mairie-villeneuve-loubet.fr      | Villeneuve-Loubet
(13 rows)

A vous de jouer pour d’autres requêtes!

par Daniel Vérité le mercredi 17 mai 2017 à 08h01

mardi 7 février 2017

Nicolas Gollet

Backends générant des fichiers temporaires

Dans certain cas il est peut être utile d'obtenir en temps réel la liste des backends générant des fichiers temporaire sur disque.

Lors de certaines opérations (trie, hachages...), PostgreSQL écrit dans des fichiers temporaires. Ces fichiers sont placés dans le sous-répertoire pgsql_tmp et sont nommés de la façon suivante :

pgsql_tmpXXXX.YXXXX correspond au PID du processus qui a créé le fichier et Y au numéro de fichier créé. Par exemple voici deux fichiers temporaires : pgsql_tmp90630.8 et pgsql_tmp90630.9.

Lorsque vous utilisez des tablespaces ces fichiers temporaires sont stockés dans l'emplacement disque définit par ceux-ci ceux qui rajoute une complexité supplémentaire pour récupéré ces fichiers.

Une fois le PID identifié, vous pouvez obtenir les informations depuis la vue pg_stat_activity afin d'obtenir les informations sur le backend générant des fichiers temporaires :

select * from pg_stat_activity where pid = <PID>;

Afin de simplifier la récupération de ces informations vous pouvez obtenir ces informations de façon automatique en utilisant la requête SQL ci-dessous :

Cette requête adaptée du projet pgstats permet d'obtenir l'ensemble des requêtes en cours d’exécution générant des fichiers temporaires tout en prenant en compte les éventuelles tablespace.

Vous pouvez aussi utiliser le script bash psql_show_tempfiles.bash présent sur mon Gitub.

par Nicolas GOLLET le mardi 7 février 2017 à 15h44