Accueil / Articles PiApplications. / Les bases de données relationelles. / PostgreSQL.

Retour d'expérience PostgreSQL.

La base de données PostgreSQL a une longue expérience derrière elle car elle hérite du projet Ingres qui était le SGBD des UNIX dits "System V" (Berkley). En 1985, le créateur d'Ingres (Michael Stonebraker) décide de refondre ce SGBD. Initialement nommé post-ingres, il devient Postgres95 puis fin 1996, PostgreSQL. Il s'agit d'un projet "libre" écrit en langage C et installables sur les principaux systèmes d'exploitation. C'est un système performant et robuste nettement plus ancien que MySQL. et que la plupart des autres SGBD "libres". Il fait partie de nos choix techniques en matière de SGBD. Son évolution se poursuit toujours malgré une féroce concurrence... Cet article traite de retours d'expériences sur quelques points rencontrés au cours de son utilisation.

Il dispose de la plupart des pilotes permettant l'accès à distance (ODBC, JDBC, etc.).

 Où est comment sont gérés les identifiants des BLOBs ?

La structure BYTEA est trop limitée pour permettre le stockage des flux binaires d'un certain volume. Cela est lié à un héritage du fameux système TOAST. Dès lors, il faut avoir recours aux BLOBs (Binary Large OBjects). Nous supposons ici que les techniques particulières à l'interface d'accès sont maitrisées par le lecteur dans son langage préféré (Java, C#, etc.).

La vue simplifiée que l'on peut en avoir est que la création d'un BLOB fourni un identifiant de type oid (entier long) qui permet ensuite de retrouver et de manipuler ce BLOB. Très vite on arrive à se poser ces questions : qu'arrive-t-il si on perd cet identifiant ? qu'arrive-t-il au premier BLOB si on écrase son identifiant par celui d'un nouveau BLOB ?

En fouillant l'Internet, on se rend compte de deux choses :

  1. Le sujet des BLOBs sous PostgreSQL est un sujet assez peu abordé et le plus souvent les articles se limitent à présneter la gestion de l'interface d'accès aux BLOBs.
  2. Il y a peu d'articles qui abordent les sujets de leur structure.

Pour avoir une réponse pertinente, le plus simple est de recourir à la documentation de PostgreSQL sur ce sujet. Après quelques détours, on finit par comprendre que les BLOBS sont stockés dans une table système nommée pg_largeobject et que la sécurité de leur accès est protégé par une autre table système nommée pg_large_object_metadata.

Après cela devient assez simple, la table pg_largeobject est constituée de lignes qui représentent les pages des BLOBs. Chaque page dispose d'un numéro et il est facile de reconstituer le document en lisant les pages dans l'ordre de leurs numéros. D'autre part, chaque page a une taille maximum de 2 ko actuellement.

Muni de ces informations, quelques requêtes simples exécutées avec un compte autorisé à lire les tables système permettent de mieux comprendre le stockage des BLOBS.

Muni de ces connaissances, la réponses aux questions précédentes devient aisé :

  1. Si vous perdez l'identifiant d'un BLOB, ce dernier reste néanmoins stocké. Le système ne vérifie pas qu'une entité hors objet système maintient une référence sur l'OID du BLOB. Le risque est donc grand que la table pg_largeobject contienne alors des BLOBs inutiles. Sans accès à un compte autorisé en lecture sur les tables système, vous ne récupèrerez jamais cet identifiant.
  2. La réponse à la seconde question est un corolaire de la précédente. Si vous n'effacez pas un BLOB avant d'écraser son identifiant, ce dernier restera stocké dans la table système de façon inutile.

En résumé, une tâche d'administration consiste à écrire une requête qui va s'assurer qu'il n'y a pas de BLOBs "mort" (c'est à dire inutile au plan fonctionnel) stocké dans la table système.

Le danger de l'instruction SQL "NOT IN".

Le danger provient du fait que si la sous-requête retourne NULL, la requête principale ne retournera aucun résultat. Soit par exemple la requête :

SELECT * FROM t_property
WHERE c_id NOT IN (
  SELECT c_property FROM r_document_property)
ORDER BY c_id;

Cette requête demande s'il existe une colonne c_id (en fait la clef primaire dans l'exemple réel) qui ne soit pas liée à la table R_DOCUMENT_PROPERTY via la colonne c_property de cette dernière table. Si la table R_DOCUMENT_PROPERTY n'a aucune ligne pour laquelle la colonne c_property soit NULL, la requête retourne un résultat conforme aux attentes. Si par contre, elle contient au moins une ligne pour laquelle c_property est NULL, alors la requête ne retourne aucune ligne.

Aussi surprenant que cela paraisse, ce résultat est "normal" et devrait être conforme aux attentes. En effet, en SQL, NULL ne signifie pas "sans valeur" comme on le croit souvent mais "valeur inconnue". Lorsque la sous-requête retourne NULL, l'interpréteur SQL ne peut pas se prononcer sur le fait que la valeur c_id de la requête principale est égale ou non à la valeur c_property car cette dernière est indéfinie. Il considère donc qu'il ne peut pas répondre et ne retourne aucune valeur.

Cette "subtilité" rend l'emploi de NOT IN (et pas seulement avec PostgreSQL) assez délicate. Si vous craignez que des NULL soient présents dans la réponse de la sous-requête préférez alors une requête du type :

SELECT * FROM t_property WHERE c_id IN (
  SELECT c_id FROM t_property
  EXCEPT
  SELECT c_property FROM r_document_property)
ORDER BY c_id;

Dans la réalité, malgré un étage de plus à la requête pour ramener les valeurs de toutes les colonnes, ces deux requêtes ont un niveau de performance sensiblement équivalent (sur une base correctement paramétrée en ressource mémoire).

Obtenir des information sur les structures des bases de données.

Le programme console pgsql permet grâce aux commutateurs\d, \dt et autres d'obtenir facilement une visualisation des structures des objets comme la celle des tables par exemple. Du point de vue programmatique, cela est un peu plus compliqué et il faut reconnaitre que la documentation PostgeSQL est peu loquace sur le sujet.

En fait, les structures de tous les objets sont entièrement décrites dans un catalogue système nommé INFORMATION_SCHEMA. Toutefois, ne tentez pas une requête du type SELECT FROM information_schema... car information_schema n'est pas reconnu à ce niveau. En effet, information_schema est un ensemble de vues. Il est nécessaire de déréférencer la vue à partir de laquelle vous souhaitez des informations en faisant suivre information_schema d'un point suivi du nom de la vue.

Le nombre de ces vues est d'un peu moins d'une soixantaine avec PostgreSQL 9.2. Elles sont données dans la première page du chapitre intitulé "The information schema" (34 avec la version 9.2). Pour fixer les idées, imaginons que nous disposons d'une table t_document dont nous souhaitions connaître le nom des colonnes. La documentation nous apprend que le nom de toutes les colonnes se trouve dans la vue "columns". Cette vue dispose d'une colonne table_name qui va nous permettre de filtrer les colonnes de notre table. Voici la requête à exécuter :

SELECT column_name FROM information_schema.columns WHERE table_name='t_document';

Le multi instances.

L'activité de développement présente toujours un certain risque pour les données opérationnelles. Cela est particulièrement vrai si ces données sont stockées dans une base de données. Le fait de décomposer le cluster en bases différentes n'est pas suffisant pour protéger efficacement vos précieuses données car une erreur sur le nom de la base est vite arrivée.

Idéalement, il faudrait disposer d'une plate-forme opérationnelle totalement et physiquement distincte de la plate-forme de développement. Cela a un coût élevé et double le coût de maintenance de vos systèmes. La virtualisation est une première réponse mais elle présente elle aussi un coût.

Il se trouve que PostgreSQL autorise le lancement d'instances différentes du serveur sur une même machine tant que les numéros de port d'attaque de ces instances sont différents (le système d'exploitation quel qu'il soit, refuse d'avoir un même couple [adresse IP, numéro de port]). Si vous tenez à conserver le même port, alors il faudra modifier l'adresse de l'instance (machine en mode multi-home). Vous pouvez disposer d'une instance réservée au développement dont le cluster (ensemble de fichiers qui constitue le système de base de données) se trouve sur un disque de votre machine et d'une autre instance réservée aux données opérationnelles dont le cluster se trouve sur une autre partition, un autre disque ou encore un lecteur SCSI d'un NAS ou d'un SAN. Chaque instance s'exécute sur un processus différent et attaque des données stockées physiquement en des endroits différents. Cela réduit notablement le risque de pollution voire de destruction accidentelle de données opérationnelles.

Sous Windows, un cas d'utilisation consiste à lancer l'instance opérationnelle depuis le service installé sur un port spécifique. La commande de lancement associée au service pourrait être du type C:/Program Files/PostgreSQL/9.1/bin/pg_ctl.exe runservice -N "postgresql-x64-9.1" -D "F:/pgsql" -w -o "-i --port=3456". Ainsi les données opérationnelles sont accessibles de manière permanente. Une autre instance réservée au développement pourrait être lancée à la demande par un script dont voici un exemple :

@echo off
cls
PATH="C:\Program Files\PostgreSQL\9.1\bin";%PATH%
cd "C:\Program Files\PostgreSQL\9.1\bin"
echo "Lancement de la base PostgreSQL reservee aux tests..."
pg_ctl start -w --pgdata=c:/pgsql -o "-i --port=5432 -d 3"

Nous avons augmenté ici le niveau de trace pour voir les difficultés rencontrées par le SGBD en cours de développement.

Il existe bien entendu d'autres cas d'utilisation y compris le lancement de plusieurs services (tant que les numéros de port des clusters sont différents). Cela dépend de vos besoins propres. Ne perdez cependant pas de vu que plus un disque est rapide, meilleurs sont les temps de réponse.

Présentation du résultat.

Les requêtes traditionnelles SELECT colonne1,colonne2,colonne3 FROM table... produisent un résultat scindé en autant de colonnes qu'en contient la requête. PostgreSQL permet toutefois de présenter ces résultats en une chaîne unique via l'opérateur de concaténation ||. Ainsi la requête SELECT colonne1 || ' ' || colonne2 || ' '|| colonne3 FROM table... présente une liste de lignes formée de la concaténation des 3 colonnes avec l'espace comme séparateur. Notez que dans de telles requêtes l'usage de la fonction coalesce est fortement recommandé. Cette fonction permet de donner une représentation de la valeur indéfinie NULL. Sans cela, la totalité du résultat est indéfini même si une seul de colonnes produit un résultat NULL.

(c) PiApplications 2015