Guide d'installation des vues matérialisées sous PostgreSQL
Table des matières
Introduction
La vaste majorité des systèmes de gestion de bases de données relationnelles (SGBD) permettent d'encapsuler une requête sous forme de «vue». Une vue est une table virtuelle représentant le résultat d’une requête sur la base de données. À la différence d'une vue standard, dans une «vue matérialisée» les données sont bel et bien dupliquées dans de nouvelles tables. Cette mécanique est utilisée notamment dans le but d'optimiser des requêtes complexes. Le concept de vues matérialisées n'est pas connu du SGBD PostgreSQL. Jonathan Gardner a présenté en 2004 une méthode simple permettant d'ajouter des vues matérialisées sous PostgreSQL. Cette méthode qui peut être utilisée avec n'importe qu'elle base de données PostgreSQL a été appliquée au SGDE / EDMS dans le cadre d'un projet visant à alimenter le portail de l'Observatoire global du St-Laurent (OGSL). Dans cette implantation, les données conservées dans les vues matérialisées sont mises à la disposition de l'OGSL par le biais d'un service Web connu sous le vocable de WEDS pour Web Environmental Data Service (Mettre le lien vers la doc.).
Fonctionnement
La méthode développée par Jonathan Gardner utilise une table de gestion (table matviews) et trois procédures stockées en langage PL/pgSQL permettant de manipuler les vues matérialisées soit, create_matview(), drop_matview() et refresh_matview().
Pour chaque vue materialisée à créer, on crée d'abord une vue ordinaire qui contient la requête SQL qui servira à créer la table de la vue matérialisée. Les noms de la vue matérialisée et de la vue servant à sa création sont ajoutés à la table de gestion matviews lors de la création de la vue matérialisée et par la suite, on n'a qu'à invoquer les fonctions de destruction et de mise à jour en utilisant le nom de la vue matérialisée.
La documentation complète de la méthode développée par Jonathan Gardner est disponible à l'adresse suivante: http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views.
Pour utiliser la méthode de gestion des vues matérialisées présentée ici, il faut au préalable avoir installé un serveur PostgreSQL et avoir à sa disposition une base de données SGDE / EDMS. Vous trouverez les information concernant l'installation d'un SGDE / EDMS en consultant le Guide d'installation du SGDE / EDMS.
installateur_gestion_vm.py est un programme interactif en mode texte qui permet de mettre en place la méthode de gestion des vues matérialisées dans une base de données SGDE / EDMS. Il peut être utilisé directement sur la machine qui héberge le serveur PostgreSQL ou sur un poste de travail distant, pourvu que celui-ci ait les droits de connexion appropriés. La connexion à la base de données est établie par le rôle de connexion associé au SGDE / EDMS (par exemple: sgde) et non par l'utilisateur postgres. Il faut donc que ce rôle de connexion dispose des droits appropriés (voir la section Configuration de connexion du serveur et des permissions des clients du Guide d'installation SGDE / EDMS). De plus, installateur_gestion_vm.py fait appel à l'interpréteur python et au client psql. Il faut donc porter une attention particulière à la section traitant de l'initialisation des chemins du Guide d'installation du SGDE / EDMS.
installateur_gestion_vm.py utilise un gabarit qui sert à produire un script SQL de création de la table et des procédures stockées de gestion des vues matérialisées. Le script SQL produit se nomme script_creation_vm.sql et est sauvegardé dans le répertoire à partir duquel est lancé l'installateur.
Dans la documentation qui suit, la variable <chemin_install> représente le chemin absolu ou relatif du répertoire contenant le script d'installation installateur_gestion_vm.py.
Sous Linux on ouvre un terminal et on tape les commandes suivantes:
$> <chemin_install>/installateur_gestion_vm.py
Sous Windows on ouvre une fenêtre Invite de commandes (ou Exécuter->cmd) et on y entre:
$>chcp 1252 # Passer à l'encodage Windows-1252
$> <chemin_install>\installateur_gestion_vm.py
L'installateur demandera alors de préciser les différents paramètres de connexion à votre SGDE / EDMS:
- Nom (ou adresse IP) du serveur de base de données:
- Nom ou adresse IP de la machine sur laquelle a été installé le serveur PostgreSQL. Si l'installateur est exécuté sur la même machine que le serveur de bases de données, on utilise localhost comme nom de machine. Il n'y a pas de valeur par défaut.
- Nom du rôle utilisateur pour votre SGDE [sgde]:
- Utilisateur de PostgreSQL ayant le droit de se connecter et d'administrer le SGDE / EDMS. Cet utilisateur a été créé lors de l'installation du SGDE / EDMS. Valeur par défaut: sgde.
- Nom de la base de données pour votre SGDE [sgde]:
- Nom qui a été attribué à la base de données. Valeur par défaut: sgde.
- Nom du schéma pour les données [sgde]:
- Nom qui a été attribué au schéma à l'intérieur duquel ont été créées les tables et procédures principales du SGDE / EDMS. Valeur par défaut: sgde.
Lorsque tous les paramètres nécessaires ont été fournis, installateur_gestion_vm.py produit un script SQL (script_creation_vm.sql) dans le répertoire courant puis fait un appel au programme psql qui se connecte au serveur PostgreSQL en utilisant ce script comme fichier de directives. Au moment de la connexion psql demandera le mot de passe du rôle de connexion au SGDE / EDMS pour se connecter directement à la base de données afin d'y créer tous les objets (table et procédures) requis pour la gestion des vues matérialisées.
Dans cet exemple, nous allons installer la gestion des vues matérialisées dans un SGDE / EDMS qui a été créé en utilisant les paramètres suivants:
- Nom du rôle utilisateur (rôle de connexion): admin_env
- Nom de la base de données: bd_env
- Nom du schéma pour les données : bd_env
Le serveur de base de données tourne sur la machine locale (localhost).
$>./installateur_sgde.py
Nom (ou adresse IP) du serveur de base de donnees: localhost
Nom du role utilisateur pour votre SGDE [sgde]: admin_env
Nom de la base de donnees pour votre SGDE [sgde]: bd_env
Nom du schema pour les donnees [sgde]: bd_env
Voulez-vous modifier ces parametres? [n | o] n
Mot de passe pour l'utilisateur admin_env : *******
Lors de la création de la base de données, quelques messages émanant du serveur seront affichés à l'écran:
psql:script_creation_vm.sql:25: ERREUR: la table « matviews » n'existe pas
psql:script_creation_vm.sql:30: NOTICE: CREATE TABLE / PRIMARY KEY créera un
index implicite « matviews_pkey » pour la table « matviews »
CREATE TABLE
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
Ces messages sont normaux et peuvent être ignorés.
Pour vérifier que l'installation s'est bien déroulée, on peut se connecter à la base de données à l'aide du client psql et faire quelques requêtes pour afficher les propriétés des nouveaux objets créés:
$>psql -U admin_env -d bd_env -h localhost
Mot de passe pour l'utilisateur admin_env : ********
psql (8.4.3)
Saisissez « help » pour l'aide.
bd_env=>
bd_env=>\dt matviews
Liste des relations
Schéma | Nom | Type | Propriétaire
--------+----------+-------+--------------
bd_env | matviews | table | admin_env
(1 ligne)
bd_env=>\dfn *matview
Liste des fonctions
Schéma | Nom | Type de données du résultat | Type de données des paramètres | Type
--------+-----------------+-----------------------------+--------------------------------+--------
bd_env | create_matview | void | name, name | normal
bd_env | drop_matview | void | name | normal
bd_env | refresh_matview | void | name | normal
(3 lignes)
bd_env=>\q
$>
On peut aussi utiliser le client graphique pgAdmin III pour vérifier les propriétés des nouveaux objets.
Nous avons décrit ici l'installation du mécanisme de gestion des vues matérialisées tel qu'il a été utilisé par notre SGDE / EDMS dans le cadre d'un projet servant à alimenter le portail de l'Observatoire global du St-Laurent (OGSL). Un exemple pratique de l'utilisation du gestionnaire de vues vues matérialisées est fourni avec le projet SGDE / EDMS. Cet exemple, composé d'un sous-ensemble de données provenant d'une bouée d'observation océanographique, permet de regrouper sous forme de vues matérialisées les données physiques (température de l'eau, salinité, vitesse du vent, etc.) transmises par la bouée pour la période du 9 au 23 juin 2005. Pour installer cet exemple de vues matérialisées, veuillez vous référer au Guide d'installation d'un exemple de vues matérialisées pour un service WEDS (Web Environmental Data Service).
Vous trouverez ici le manuel de Documentation de PostgreSQL 8.4 (en anglais).
Guide d'installation du SGDE / EDMS.
Un article traitant de la gestion des vues matérialisées avec PostgreSQL, par Jonathan Gardner.
Guide d'installation d'un exemple de vues matérialisées pour un service WEDS (Web Environmental Data Service).