Nouveautés de PostgreSQL 9.1, partie 2

1. Fonctionnalités SQL et applicatives

1.1. Support complet du collationnement

L'ordre de tri des données de type texte a toujours été complexe dans PostgreSQL.

Avant la 8.4, cet ordre était figé au moment de la création de l'instance (lors de l'initdb pour être précis). Il était impossible de spécifier un ordre différent par base de données. Du coup, en 8.4, un patch a permis d'avoir un ordre de tri différent par base de données. La 9.1 va bien plus loin en proposant un ordre de tri configurable par colonne. Il est donc possible d'avoir une table dont les différentes colonnes texte n'ont pas le même comportement pour le tri des données de type texte.

Le tri est toujours fait par la bibliothèque C pour la locale sélectionnée. PostgreSQL n'accepte donc, pour un collationnement particulier, que les locales actuellement installées sur le système. La liste des locales est disponible depuis PostgreSQL à partir de la vue système pg_collation :

L'ajout de locale dépend évidemment du système utilisé.

La locale peut être indiquée lors de l'ajout d'une colonne (il peut même être différent pour les colonnes d'une même table) :

N'oubliez pas les doubles guillemets si le nom de la locale contient des majuscules (le nom de la locale est un nom d'objet). Maintenant, insérons quelques données :

Essayons de lire les données en les triant sur la colonne c2 :

L'ordre observé est l'ordre français. Mais supposons que nous avons un utilisateur allemand qui veut trier les données suivant l'ordre allemand. Il est possible de le spécifier en ajoutant la clause COLLATE à la clause ORDER BY, ainsi :

Et en effet, l'ordre est différent.

Un tri peut facilement se faire en utilisant un index. Mais que se passe-t-il si nous voulons trier par un autre collationnement que celui par défaut ? Testons ça :

Par défaut, PostgreSQL n'utilisera pas l'index ici car la quantité de données est trop faible, et du coup, un parcours séquentiel suivi d'un tri sera forcément plus rapide que l'utilisation de l'index. Désavantageons fortement l'utilisation des parcours séquentiels en désactivant le paramètre enable_seqscan :

Cette fois, il passe bien par l'index. Changeons maintenant le collationnement.

Il ne passe plus par l'index. En effet, l'index est spécifique à la colonne et au collationnement choisi. Cependant, il est possible de créer un index pour un collationnement différent :

Du coup, le tri peut se faire avec ce nouvel index utilisant le bon collationnement.

1.2. Requêtes CTE en écriture

PostgreSQL dispose des requêtes CTE depuis la version 8.4. Cependant, ces requêtes étaient seulement disponibles en lecture. Avec la 9.1, il est possible d'utiliser les commandes INSERT, UPDATE et DELETE.

Disons que nous avons un outil du type forum web, et que ce dernier utilise une base PostgreSQL pour y stocker les messages. Il y a une table messages, contenant plusieurs colonnes dont une colonne id_utilisateur et une colonne cree_le. Supposons maintenant que nous voulons supprimer tous les messages datant de plus de six mois. La requête est simple :

Parfait. Seulement, nous n'avons aucune information sur ce que nous avons réellement supprimé. Or, il serait intéressant d'avoir des statistiques sur la suppression. Par exemple, nous souhaitons savoir quels ont été les utilisateurs qui se sont vus supprimer des messages, ainsi que le nombre de messages qui ont été supprimés par utilisateur. Cette information correspond à cette pseudo-requête :

Il est impossible de coller l'instruction DELETE derrière le FROM. De toute façon, l'instruction DELETE montrée ci-dessus ne renvoie pas les lignes supprimées. Cependant, il est possible de corriger ce dernier problème avec PostgreSQL en utilisant la clause RETURNING :

Il ne nous reste plus qu'à placer le DELETE et le SELECT dans une requête CTE pour obtenir ce que l'on souhaite :

Et voilà !

En une requête, les messages sont supprimés et comptabilisés. Notez qu'il n'y a pas d'autres moyens de le faire réellement. Il est évidemment possible d'exécuter les deux requêtes séparément, dans deux transactions ou dans la même transaction. Mais même dans ce cas, il est possible que la statistique ne corresponde pas exactement au résultat de la suppression (un autre utilisateur peut avoir ajouté, modifié ou supprimé des messages entre temps).

Disons maintenant que nous voulons en plus mettre à jour une table de statistiques. Prenons donc une table stats contenant au moins deux colonnes, id_utilisateur et nombre_messages. Nous voulons mettre à jour nombre_messages en y retirant le nombre de messages supprimés. En utilisant la même technique que tout à l'heure, nous obtenons la requête suivante :

Les possibilités offertes par les requêtes CTE sont impressionnantes, sans même parler des performances qu'elles procurent.

1.3. Extensions

PostgreSQL est un système très ouvert. Si un type de données n'existe pas, en créer un nouveau, répondant à votre contexte métier, est simple. Tout comme lui ajouter des fonctions, des opérateurs, etc. PostgreSQL est d'ailleurs livré depuis longtemps avec des extensions, appelées modules contrib. Ces modules couvrent de nombreux besoins qui dépendent vraiment de l'utilisation de la base. Par exemple, le module contrib adminpack fournit des fonctionnalités utiles aux outils d'administration (et est utilisé par pgAdmin) alors que le module contrib pgbench est un petit outil de benchmarks pour PostgreSQL et que le module contrib earthdistance sert à calculer la distance séparant deux points sur le globe. PostgreSQL fournit donc des modules contrib et il est aussi possible d'en télécharger ailleurs.

Il existe néanmoins des problèmes à l'utilisation des modules contrib. Certains demandent l'exécution d'un script SQL pour créer des objets dans la base où ils seront activés. Les objets en question dépendent souvent de l'installation préalable d'une bibliothèque partagée. Ils dépendent aussi de la version de cette bibliothèque et de la version de PostgreSQL. Lorsque la base contenant ces objets est sauvegardée, la sauvegarde contient les ordres de création de ces objets. Lors de la restauration, ils ne seront restaurés que si la bibliothèque partagée est déjà présente et d'une version compatible. En fait, les objets ajoutés par un module se comportent comme des objets utilisateurs alors qu'ils ont tout de l'objet système.

Apparaît donc en 9.1 le système des extensions. Ce système a pour but de faciliter la gestion des modules contrib, maintenant appelés extensions. PostgreSQL gère un nouvel objet (non standard) appelé extension. Après installation de l'extension sur le système d'exploitation, il ne reste plus à l'administrateur qu'à utiliser la commande CREATE EXTENSION. Il n'est plus nécessaire de savoir où se trouve le script SQL de l'extension, tout est géré par cette commande. Il suffit même d'une connexion à une base pour connaître la liste des extensions installées sur le système d'exploitation. Pour cela, il suffit d'interroger la vue système pg_available_extensions, par exemple avec la requête suivante :

La colonne name indique le nom de l'extension. La colonne default_version précise la version qui sera installée par défaut alors que installed_version indique la version déjà installée (elle vaut NULL si aucune version n'est installée). Enfin, la colonne comment est, comme son nom l'indique, un commentaire sur l'extension.

Adminpack n'étant pas activé, il suffit de la requête suivante pour le faire :

À partir de là, les objets nécessaires à cette extension sont installés, comme le montre la commande \dx+ de psql :

Ces objets sont bien créés dans la base mais sont liés à cette extension. Si jamais nous souhaitons supprimer cette extension, il nous suffit d'exécuter la commande DROP EXTENSION et nous n'avons pas à nous préoccuper de supprimer manuellement les différents objets ou à trouver un script de désinstallation. De même, nous pouvons mettre à jour une extension avec la commande ALTER EXTENSION.

De même, la sauvegarde, via pg_dump, ne contiendra que l'ordre CREATE EXTENSION pour chaque extension activée sur la base sauvegardée.

Bref, ce système permet de faciliter la vie des administrateurs en ce qui concerne les extensions. Il suffit de penser à PostGIS et à ses plus de 700 procédures stockées. La sauvegarde et la restauration étaient des problèmes permanents. La version 2.0 de PostGIS devrait permettre l'utilisation d'une extension avec PostgreSQL 9.1.

Ce système d'extension en a fait réfléchir plus d'un sur la possibilité de récupérer des extensions sur Internet. Il existe évidemment pgfoundry.org, mais ce dernier est vieux, pratiquement non maintenu et beaucoup réclament même sa suppression. David Wheeler a travaillé un bon moment sur un équivalent CPAN pour PostgreSQL. Le résultat de son travail s'appelle PGXN pour PostGresql Extension Network. Le site web associé, http://pgxn.org, permet à tout un chacun de récupérer des extensions sur PostgreSQL et de les installer facilement.

 

 

Un outil, baptisé pgxnclient, permet de rechercher les extensions, de les télécharger et de les installer. Son installation est très simple vu qu'il suffit d'utiliser l'outil easy_install :

Disons que nous voulons installer l'extension contenant le type de données period. Commençons par chercher cette extension :

Trouvé ! Il s'agit de l'extension temporal. Installons-la :

Téléchargée, décompressée, compilée, installée... prête à l'utilisation. Il est à noter que sur la majorité des distributions, il faudra avoir installé préalablement le paquet de développement de PostgreSQL et/ou de sa bibliothèque libpq, sans parler d'un environnement de développement.

Maintenant, il ne reste plus qu'à activer ce type dans une base :

Vérifions que l'extension est bien installée avec ses objets :

Créons une table avec le nouveau type :

Tout va bien.

Le système des extensions et l'infrastructure PGXN forment un duo étonnant qui permettra d'aller encore plus loin que ce qu'on imagine pour l'instant. Un grand nombre d'extensions sont développées pour ajouter des fonctionnalités à PostgreSQL, fonctionnalités pour lesquelles il ne sera plus nécessaire d'attendre un an et d'avoir à installer une nouvelle version.

1.4. SQL/MED

SQL/MED fait partie de la norme ISO SQL portant sur les données externes à la base de données. MED est l'acronyme de Management of External Data, soit la gestion de données externes. La gestion de SQL/MED dans PostgreSQL a commencé dès la version 8.4, mais pour cette version, seule une partie de l'infrastructure avait été mise en place. Il manquait le plus important : la gestion des tables externes. C'est exactement ce qu'ajoute la version 9.1.

PostgreSQL propose donc quatre objets pour SQL/MED :

- le Foreign Data Wrapper ;

- le Foreign Server ;

- le User Mapping ;

- et le Foreign Table.

Les trois premiers étaient déjà disponibles depuis la version 8.4, le dernier n'est supporté que depuis la version 9.1.

Le Foreign Data Wrapper est en quelque sorte un connecteur permettant d'accéder à des données qui se trouvent à l'extérieur de la base de données où vous êtes connecté. Il existe déjà un certain nombre de Foreign Data Wrapper disponibles. Suivant les cas, ils pourront lire un fichier, lire des données dans une autre base de données (SQL comme noSQL), lire un flux Twitter ou un autre webservice, etc. La seule limite est celle de votre imagination. Il existe une liste des différents Foreign Data Wrapper disponibles sur cette page : http://wiki.postgresql.org/wiki/Foreign_data_wrappers. À l'heure actuelle, il s'agit de MySQL, Oracle, ODBC, CouchDB, Redis, twitter, LDAP, Amazon S3 et d'un lecteur de fichiers CSV. Un grand nombre d'entre eux sont toujours en version beta, donc attention lors de leur utilisation. La version 9.1 ne propose pas de Foreign Data Wrapper pour PostgreSQL. Ce manque sera couvert normalement avec la version 9.2.

Le Foreign Server permet d'indiquer où se trouve la donnée à récupérer. Le User Mapping permet d'établir une correspondance entre l'utilisateur de connexion sur PostgreSQL et l'utilisateur du serveur distant (si un tel utilisateur est nécessaire). Enfin, le Foreign Table est une table un peu particulière.

Testons cette fonctionnalité avec le Foreign Data Wrapper file_fdw inclus dans les modules contrib de PostgreSQL. Ajoutons l'extension file_fdw dans la base de données b1 :

L'ajout de l'extension a créé le Foreign Data Wrapper :

Nous pouvons maintenant ajouter le serveur externe :

À présent, ajoutons la table externe. Nous allons prendre pour exemple le fichier /etc/passwd car c'est un fichier que tout utilisateur Unix possède :-)

Comme pour toute création de table, il faut indiquer les colonnes (nom et type). Par contre, nous spécifions en plus le serveur, qui définit par conséquent le Foreign Data Wrapper. Nous ajoutons aussi les options spécifiques de la table, qui seront utilisées par le Foreign Data Wrapper : chemin vers le fichier (option filename), format du fichier et délimiteur. D'autres options sont disponibles, mais cela suffira pour cet exemple.

Une fois la table créée, toute lecture de cette table fera une lecture sur le fichier /etc/passwd :

Toutes les opérations habituelles du SELECT (filtre, tri, utilisation d'expression, de sous-requête) sont permises. Toute mise à jour du fichier sera vue lors de la prochaine lecture de la table. Il faut bien comprendre que PostgreSQL ne stocke rien, il fait à chaque fois une lecture de ce fichier.

Cette fonctionnalité est indéniablement très intéressante mais souffre de quelques limitations. Par exemple, toute clause WHERE, LIMIT ou ORDER BY est exécutée sur le serveur PostgreSQL. Autrement dit, si vous demandez de lire une table de 5 Go en ajoutant un LIMIT 1, PostgreSQL récupère les 5 Go et fait le LIMIT après coup, même si le Foreign Data Wrapper concerne un moteur de bases de données qui serait capable de faire le LIMIT lui-même. Ce problème pourrait être résolu en version 9.2 mais ne l'est pas sur la 9.1. Il faut donc faire attention en utilisant cette fonctionnalité. Bien qu'elle soit très intéressante, elle peut avoir un comportement gênant dans certains cas. De plus, les écritures sont impossibles.

Notez qu'il est possible de coder son propre Foreign Data Wrapper, ce qu'a expliqué Dave Page dans la conférence qu'il a donnée lors des conférences européennes sur PostgreSQL de cette année.

1.5. SSI

Le standard SQL définit quatre niveaux d'isolation des transactions :

- read uncommitted : niveau d'isolation le plus bas, une transaction peut lire les données modifiées par une autre transaction, qu'elle ait été ou non validée (COMMIT).

- read committed : une transaction ne peut lire les données modifiées par une autre transaction que si cette dernière a été validée.

- repeatable read : une transaction ne verra aucune des modifications des autres transactions, validées ou non, à partir du moment où elle aura déjà lu les données (autrement dit, deux lectures de la même table donneront le même résultat pour une même transaction même si une autre transaction a modifié des données entre temps).

- serializable : niveau d'isolation le plus important, les transactions sont toutes exécutées comme si elles étaient exécutées les unes à la suite des autres.

Au niveau de PostgreSQL, seuls les niveaux read commited et repeatable read étaient implémentés. Un gros travail a été réalisé pendant le développement de la version 9.1 pour ajouter le niveau sérialisable. D'où l'ajout de SSI, acronyme pour Serializable Snapshot Isolation.

Prenons un exemple qui va expliquer le problème que nous cherchons à contourner.

Nous allons tout d'abord utiliser le mode read commited. Commençons avec la première session. Disons que nous voulons marquer comme payé toutes les factures du client d'identifiant 15. Commençons par vérifier les factures tout d'abord :

Parfait, ça correspond à ce que nous attendions, nous allons donc mettre à jour ces quatre factures. Supposons maintenant qu'un deuxième utilisateur travaille en même temps et ajoute une nouvelle facture pour ce client :

Tout s'est bien passé pour lui. Sur la session 1, rien ne nous indique qu'une nouvelle ligne a été ajoutée, nous sommes toujours dans notre transaction.

Oups, nous avons modifié cinq factures, pas quatre. Un utilisateur qui le fait manuellement peut voir le problème, mais rien ne dit qu'un programme verra la différence.

Notez qu'ici, l'astuce habituelle consistant à utiliser un SELECT ... FOR UPDATE ne fonctionnera pas.

Essayons maintenant avec le niveau d'isolation sérialisable. Pour cela, nous allons modifier le paramètre default_transaction_isolation dans le fichier de configuration postgresql.conf :

Cela étant fait, nous demandons à PostgreSQL de recharger sa configuration :

Et maintenant, nous recommençons notre scénario :

OK, maintenant, l'action du deuxième utilisateur :

Très bien, et enfin, la mise à jour des factures :

La session 2 ayant changé le contexte de la session 1 en ayant validé sa transaction avant celle de la session 1, la session 1 est forcée d'annuler sa transaction. L'application, constatant cette erreur, peut avertir l'utilisateur que le contexte du client 15 a changé et qu'il faut recommencer l'opération. L'utilisateur peut alors recommencer et finir son opération en modifiant exactement les factures qu'il souhaite.

PostgreSQL n'est pas le seul moteur qui implémente l'isolation sérialisable. Cependant, il le fait d'une façon très intelligente qui permet d'éviter le verrouillage généralement constaté sur les autres moteurs de bases de données. Par contre, il est à noter que ce mode d'isolation n'est pas utilisable sur les serveurs esclaves en mode Hot Standby.

1.6. Support des triggers sur les vues

Avant la version 9.1, il n'était pas possible d'ajouter des triggers sur les vues. Si un utilisateur voulait avoir une action automatique suite à une action sur les vues, il devait passer par le système (complexe) des règles.

L'ajout d'un trigger sur une vue est maintenant possible avec la nouvelle clause INSTEAD OF en lieu et place des clauses BEFORE et AFTER. Ainsi, il est simple de créer des vues dont les données sont modifiables. Le plus gros avantage est cependant le fait qu'il ne soit plus nécessaire d'utiliser le système des règles.

1.7. Dépendances fonctionnelles des clés primaires

Tout le monde a déjà eu à subir l'erreur suivante :

L'erreur est logique si la clé primaire n'est pas sur c1. Cependant, s'il se trouve que la table a été déclarée avec c1 comme clé primaire, alors le fait que c2 ne soit pas intégré dans la clause GROUP BY n'a aucune incidence sur le résultat. La version 9.1 accepte donc cette requête.

En dehors d'un comportement plus logique, cela apporte aussi une meilleure compatibilité entre les requêtes pour MySQL et celles pour PostgreSQL, étant donné que MySQL supportait déjà cette construction de requêtes.

2. Changement important : standard_conforming_strings

Le plus gros problème pour passer à cette nouvelle version concerne l'activation par défaut du paramètre standard_conforming_strings. Ce changement fait que la chaîne 'l\'hôpital' n'est plus comprise comme une chaîne complète, le standard SQL n'acceptant pas l'antislash comme caractère d'échappement. PostgreSQL l'acceptait quand même pour faciliter la vie des développeurs, mais il a été décidé de revenir sur cette décision pour mieux respecter le standard. Ce paramètre a été ajouté en version 8.2, mais était resté désactivé par défaut. Le but était de permettre à ceux qui le voulaient de forcer le respect de la norme tout en laissant les autres utiliser leurs anciennes applications. Seul un message d'avertissement indiquait la détection d'un problème. Au bout de quatre ans, il a été décidé d'activer par défaut ce paramètre. Pour les personnes peu prévoyantes, cela signifie que leurs applications ne sont pas compatibles par défaut avec PostgreSQL. Néanmoins, il leur reste la possibilité de désactiver ce paramètre en le positionnant à off. Mais quelqu'un de non averti peut facilement tomber dans le piège, il est donc important d'être prévenu.

Néanmoins, espérons que cela aidera les développeurs à corriger leurs applications pour être plus proche du standard SQL. La chaîne 'l\'hôpital' n'est plus acceptée, mais la chaîne 'l''hôpital' l'est. Et pour être complet, la chaîne E'l\'hôpital' l'est aussi, mais attention, c'est spécifique à PostgreSQL. Tant qu'à corriger son application, autant le faire complètement et utiliser la syntaxe SQL.

3. Quant à la prochaine version

Le développement de la version 9.2 a commencé plus tôt qu'habituellement, avec une première Commit Fest dès le 15 juin 2011. Et les nouvelles fonctionnalités ont commencé à arriver.

Le travail continue sur la réplication avec enfin la possibilité de faire de la réplication en cascade. De plus, un nouvel outil fait son apparition, pg_receivexlog. Ce dernier écoute le flux de réplication pour recréer les journaux de transactions sur un autre serveur. C'est une modification du protocole de réplication qui a permis le codage de ce nouvel outil.

Les utilisateurs devraient beaucoup apprécier l'ajout du type de données range. Il s'agit plus exactement d'un ensemble de types. Par exemple, int4range pour un intervalle d'entiers, mais il existe aussi le type numrange, tsrange (pour « timestamp range »), tstzrange, daterange, etc. Il est de toute façon toujours possible de définir son propre type range. Cette fonctionnalité ressemble énormément à l'extension temporal créée par Jeff Davis mais étendue à d'autres types de données comme les entiers et les numériques.

Une nouvelle méthode d'accès à un index a été ajoutée. Nommée SP-GiST (pour Space Partitioned GiST), elle est comparable à GiST en flexibilité, mais supporte aussi les structures de recherche partitionnées non balancées. Cette méthode peut battre la méthode GiST traditionnelle à la fois en vitesse de création des indexes et en rapidité de recherche.

Par ailleurs, il y a un énorme travail en cours sur les performances : support des parcours d'index seul, diminution du niveau des verrous pour les commandes ALTER TABLE, utilisation de latches, amélioration de la commande COPY permettant de gagner en performances grâce à une réduction du volume d'enregistrement dans les journaux de transactions, etc. L'ajout d'un nouveau processus appelé checkpointer devrait aussi participer à l'amélioration des performances. En fait, l'activité du processus d'écriture en tâche de fond (souvent appelé bgwriter) est divisée en deux parties : l'exécution des CHECKPOINT et le nettoyage du cache. Les développeurs ont fait en sorte que chaque tâche soit attribuée à un seul processus : à checkpointer l'exécution des CHECKPOINT et à bgwriter le nettoyage périodique du cache disque de PostgreSQL.

Les développeurs ont aussi ajouté l'export de snapshot (autrement dit l'image de la base qu'obtient une transaction). Ceci pourrait permettre une parallélisation de l'exécution de certaines requêtes ou de la sauvegarde. D'ailleurs, pour en savoir plus sur ce sujet, Heikki Linnakangas fera une conférence sur ce thème au FOSDEM 2012.

Conclusion

Après 13 mois de travail, 4 commit-fests, 3 versions bêta et une version RC, la communauté PostgreSQL a de nouveau fourni une version comportant de nombreuses améliorations, au niveau fonctionnalités comme au niveau performances. Est-ce que cette version vous intéressera ? Je pense que oui, mais le mieux est de tester vous-même et de vérifier qu'elle correspond bien à vos besoins.