PostgreSQL La base de donnees la plus sophistiquee au monde.

Articles

Fil des billets

mercredi 7 décembre 2011

Premiers pas avec Postgresql

I Introduction

A. Pourquoi ce document?

J'ai commencé à développer sous PostgreSQL assez récemment après une longue expérience sous Oracle. La documentation générale de PostgreSQL est excellente, et très riche, mais j'avais besoin d'un document plus léger expliquant la procédure d'installation sur différents systèmes et comment démarrer (créer un cluster, configurer les connexions), ainsi que des informations sur ce qu'on pouvait faire avec PostgreSQL. Je ne l'ai pas trouvé. Après quelques mois d'utilisation, je me suis rendu compte que les problèmes des débutants étaient toujours les mêmes. Ainsi, j'ai compilé mes notes des débuts et ce que j'ai appris depuis dans ce document. Voici le résultat, en espérant qu'il vous aide à débuter et qu'il vous encourage à continuer avec PostgreSQL.

B. À qui s'adresse ce document?



Ce document a pour but de vous aider à installer PostgreSQL sous Windows ou sous Linux, et à commencer à développer.

Il est écrit pour vous faire gagner du temps dans vos premiers pas avec PostgreSQL, tout en vous expliquant les points importants afin que vous puissiez progresser par vous-même. Il s'adresse donc principalement aux développeurs d'applications, afin de leur permettre de découvrir ce puissant moteur sur une petite base de test, ou aux personnes qui débutent complètement avec PostgreSQL. Vous n'aurez pas besoin de connaissances système avancées pour suivre ce document.

Une fois que vous aurez terminé la lecture de ce document, vous pourrez continuer par la lecture de la documentation officielle pour apprendre à administrer PostgreSQL ou devenir un développeur aguerri. La dernière section de ce document vous donne les liens et références nécessaires pour continuer à progresser. Parfois les informations ne sont volontairement pas complètes, et lorsque la documentation de référence est plus claire et précise que ce qui aurait pu être fait ici, les liens sont fournis vers la documentation française.

Ce document a été écrit initialement pour la version 8.3, puis mis à jour pour la version 9.0 (voir le chapitre sur les versions).

Avertissement : ce document n'est en aucun cas un document sur le tuning de la base. Il n'est pas fait non plus pour vous apprendre à administrer une base de production.

II Présentation de PostgreSQL

PostgreSQL est un moteur de bases de données relationnelle. C'est un moteur adapté à des bases métier, donc riche en fonctionnalités et puissant. Son installation est cependant plutôt simple. Il faut juste comprendre quelques principes de base (ce que cette présentation s'efforce de faire)

Si vous ne connaissez pas les principes relationnels ou le SQL, le mieux est de vous procurer un bon ouvrage sur le sujet. L'article de Wikipedia peut être une bonne introduction (http://fr.wikipedia.org/wiki/SQL), et donne de nombreuses références. Le tutoriel de la documentation PostgreSQL peut également vous rendre service si vous avez besoin de vous rafraîchir la mémoire : http://docs.postgresqlfr.org/9.0/tutorial-sql.html

A. Licence

La licence de PostgreSQL est une licence de type BSD, ce qui permet son utilisation sans restriction, dans un logiciel libre ou propriétaire. C'est un avantage certain, car cela permet par exemple d'utiliser PostgreSQL comme base de données pour un logiciel propriétaire.

PostgreSQL est un projet indépendant. Il n'est détenu par aucune entreprise. La communauté PostgreSQL est très réactive (allez voir les mailings-lists si vous voulez vérifier). De nombreuses entreprises soutiennent et participent également au développement de PostgreSQL.

B. Caractéristiques et fonctionnalités :

PostgreSQL comporte de nombreuses fonctionnalités intéressantes. Parmi celles-ci, on peut citer par exemple : moteur transactionnel respect des normes SQL MVCC (mécanisme permettant une concurrence efficace sans verrouiller les enregistrements pour assurer l'isolation des transactions) procédures stockées dans de nombreux langages triggers réplication maître-esclaves en continu par application des journaux binaires (archives WAL), esclaves accessibles en lecture.

PostgreSQL est conçu pour être robuste (aucune version ne sort sans avoir subi une suite extensive de tests) et peut supporter des volumes importants de données (ainsi par exemple Météo France gère une base de 3,5To).

PostgreSQL est conçu pour pouvoir supporter des extensions. Des extensions et outils sont disponibles pour compléter le moteur, par exemple :

  • PostGis : moteur de données spatiales.
  • Slony : réplication maître-esclaves.
  • Et de nombreux autres.

III Installation

Avant de passer aux procédures d'installation proprement dites, il est nécessaire de comprendre certaines notions fondamentales.

A. Vocabulaire

1. Base

Une base est un ensemble structuré de données. On utilise généralement une base de donnée par application. Pour pouvoir créer une base de données, vous devez disposer d'un cluster de bases de données.

2. Cluster (ou grappe de base de données)

Un cluster est un ensemble de bases de données qui partagent les mêmes ressources (processus, mémoire, disque...) .

3. Schéma

Un schéma est un espace de nommage au sein d'une base de données.

B. Principes de base

1. Comptes système

Les processus de PostgreSQL utilisent un compte système. Généralement c'est le compte postgres qui est utilisé pour cela, sauf si vous avez installé PostgreSQL sur votre compte (voir la partie compilation).

2. Rôles

Les droits de la base de données sont gérés par des rôles. Avant de pouvoir vous connecter à la base de données, le rôle que vous utilisez doit avoir les autorisation nécessaires.

http://docs.postgresql.fr/9.0/user-manag.html

À retenir: les comptes systèmes et les rôles de base de données sont distincts! Même s'il y a des possibilités de mapping entre les deux (cf. paragraphe sur pg_hba.conf) La confusion entre ces 2 notions est une des causes fréquentes d'erreurs et de problèmes d'installation pour les débutants.

3. Versions (mineures/majeures)

Les versions majeures comprennent le chiffre avant le point et un chiffre après. Exemple : 8.2 et 8.3 sont des versions majeures différentes. Les versions mineures incrémentent la 3ème partie : exemple : 8.3.7 Pour changer de version mineure, il suffit de mettre à jour le moteur. Mais pour changer de version majeure, il est nécessaire de décharger puis recharger les données.

Plus d'informations ici : http://www.postgresql.org/support/versioning

4. Client/serveur

PostgreSQL est une application client/serveur. Le serveur gère les fichiers de la base de données, accepte les connexions des clients, et effectue les opérations demandées par les clients (requêtes...) Le client peut prendre de nombreuses formes. Il existe par exemple un client en ligne de commande (psql), des clients graphiques (par exemple pgAdmin3)... Le client peut être sur la même machine que le serveur, ou bien communiquer avec lui par le réseau.

5. Processus serveur

Sous Windows, le serveur PostgreSQL tourne en tant que service. Sous Linux, ce sont des démons système qui effectuent ces tâches. (si vous êtes curieux, vous pouvez aller voir cet article : http://dalibo.org/glmf112_les_processus_de_postgresql) Il ne faut pas arrêter les processus du serveur n'importe comment. Pour arrêter le serveur, il faut utiliser les outils fournis (voir la section sur l'arrêt et le démarrage du serveur). NB : par défaut, PostgreSQL est configuré pour écouter sur le port 5432. Les outils se connectent par défaut sur ce port : pensez à cela si vous devez modifier ce paramètre.

6. Module de contribution

Ce sont des extensions intéressantes, maintenues par le projet, mais non intégrées au coeur du moteur. Exemples :

  • adminpack (fonctions supplémentaires, utilisées par les outils d'administrations comme pgAdmin3)
  • pg_buffercache (pour savoir ce qui est présent dans le cache)
  • pg_freespacemap : donne la liste des blocs vides et partiellement vides des tables et index (quantité d'espace libre dans chaque objet de la base)
  • pgcrypto : fonctions de cryptographie

C. Exemple

Pour l'installation et la suite, nous prendrons l'exemple de la création d'une base de données mabase, qui sera utilisée et gérée par un utilisateur tom.

D. Sous Windows

À partir de la version 8.0, PostgreSQL fonctionne nativement sous Windows (Windows XP, Windows 2000, Windows 2003, Vista, Windows 2008...). Malgré tout, seules les versions à partir de la 8.2 sont supportées sous Windows. Il s'installe en tant que service.

NB : si vous regardez dans la liste des processus, plusieurs processus postgres sont présents. Gardez à l'esprit que la mémoire est partagée entre ces processus : la mémoire utilisée par PostgreSQL est donc inférieure à la somme de la mémoire utilisée par chaque processus qui est affichée dans le gestionnaire de tâches...

1. Où trouver PostgreSQL pour Windows?

Vous pouvez trouver deux types d'installeurs pour Windows : l'installeur "en un clic", ou l'installeur "pgInstaller". Le premier est créé par EnterpriseDB, le seconde par la communauté. Vous les trouverez à partir d'ici : http://www.postgresql.org/download/windows "pgInstaller" n'est disponible que pour les versions 8.2 et 8.3, le document détaille donc le processus d'installation pour l'installeur «en un clic ». NB: il est possible de récupérer les binaires sans l'installeur (pour utilisateurs avancés uniquement), ou de faire une installation silencieuse (voir sur le site de EnterpriseDB)

2. Installation

Lancez l'installeur (pour Postgresql 9.0, le fichier s'appelle : postgresql-9.0.0-1-windows.exe )

NB: L'installeur logue toutes ses actions dans un fichier install-postgresql.log qui est dans le répertoire %TEMP% de Windows. En cas de problème, consulter ce fichier.

3_repertoire.jpg

Le répertoire est celui où vont s'installer le programme serveur (postgres.exe) et les outils client (psql, pgdump...), ainsi que la documentation, etc...

L'installeur ne permet actuellement pas d'installer les outils client et le serveur séparément.

4_donnees.jpg

L'installeur demande ensuite où sera créé le cluster de données. Il sera par la suite toujours possible de créér d'autres cluster avec l'outil initdb.

5_mot_passe.jpg

L'installeur demande le mot de passe de l'utilisateur postgres. Attention, en réalité ceci recouvre 2 notions différentes : un utilisateur du système d'exploitation, celui sur le compte duquel fonctionnent les programmes du serveur, le super-utilisateur de base de données. Ils peuvent très bien avoir des noms et des mots de passe différents, mais pour cet installeur, il a été choisi de donner le même nom et le même mot de passe.

Si l'utilisateur postgres du système d'exploitation existe déjà, il faut donner le mot de passe existant. Si vous l'avez oublié, vous pouvez le changer dans une console avec la commande net user : net user postgres <motdepasse>

Attention à ne pas mettre un mot de passe trivial à l'utilisateur postgres (c'est encore plus important si vous autorisez les connexions à partir du réseau!). Évitez également de lui donner le même mot de passe que celui de l'utilisateur système postgres. En effet, l'utilisateur postgres dispose de tous les droits sur le cluster.

6_port.jpg

Par défaut, le port sur lequel le serveur attend les connexions est le port 5432. Vous pouvez changer le numéro de port d'écoute. Attention dans ce cas à configurer correctement vos clients (JDBC, etc...)

Remarque : par défaut, postgres n'acceptera pas les connexions à partir du réseau. Ceci est parfait sur un poste de développement autonome, mais pas pour un serveur. Cela pourra être modifié par configuration.

7_locale.jpg

La locale définit le comportement du cluster pour les opérations de tri (ordre alphabétique) … Par défaut, c'est celle du système qui est utilisée, mais vous pouvez en préférer une autre.

8_pret.jpg

Si vous êtes certain(e) du paramétrage, vous pouvez cliquer sur « Suivant».

9_fin.jpg

L'installation est terminée. Si vous souhaitez installer des modules complémentaires (phppgAdmin, Slony...), lancez l' outil Stackbuilder.

110_utilisation_flou.jpg

L'installation sous Windows est prête à être utilisée. Dans le menu démarrer, vous pouvez retrouver tous les outils utiles pour gérer le serveur.

Si vous avez conservé les options par défaut, les fichiers du cluster se trouvent dans C:\Program Files\PostgreSQL\9.0, et vous trouverez l'outil pour désinstaller dans le même répertoire.

NB : notes sur la console Windows et psql La console Windows est par défaut dans un encodage compatible DOS (par exemple CP850). Lorsque vous démarrerez psql pour la première fois, vous aurez le message d'avertissement suivant :

Attention : l'encodage console (850) diffère de l'encodage Windows (1252).
Les caractères 8 bits peuvent ne pas fonctionner correctement.
Voir la section « Notes aux utilisateurs de Windows » de la page
référence de psql pour les détails.

Il est recommandé de modifier l'encodage de la console, Pour éviter cela, vous pouvez éditer le fichier C:\Program Files\PostgreSQL\9.0\scripts\runpsql.bat en ajoutant la ligne :

chcp 1252

avant le lancement de psql.

Remarque importante : si vous avez installé PostgreSQL sur un poste de travail (dans le but par exemple de l'évaluer ou de vous familiariser avec lui), vous avez maintenant une installation qui fonctionne « à la sortie de la boîte », et vous pouvez commencer à l'utiliser via l'outil pgAdmin (crééer des bases, etc...). Mais si vous souhaitez autoriser des connexions distantes, il est indispensable de lire la suite du document. Il apporte également des informations qui pourraient vous être utiles (emplacement et rôle des différents répertoires...) même si vous utilisez peu les outils en ligne de commande. Vous pouvez maintenant passer à la section « après l'installation » si vous le souhaitez.

IV Après l'installation

Dans toute la suite du document, nous supposons que l'utilisateur système sous lequel PostgreSQL a été installé est postgres. Si ce n'est pas le cas, remplacez par l'utilisateur qui démarre le serveur. Conseil : avant toute modification de fichier de configuration, pensez à sauvegarder la version initiale du fichier! Une erreur est si vite arrivée...

A. Processus et emplacement des fichiers.

L'emplacement des fichiers de configuration et des fichiers du cluster dépend de votre distribution. Le répertoire contenant les fichiers du cluster est couramment appelé PGDATA (du nom de la variable d'environnement correspondante). Par exemple : /var/lib/pgsql/data (Linux) ou C:\Program Files\PostgreSQL\9.0\data (Windows) Normalement, le fichier postgresql.conf est dans le répertoire du cluster. Cependant, cela peut être autrement (sur Debian, tous les fichiers de configuration doivent être dans /etc) Voici un moyen de retrouver leur emplacement sous Linux ou Unix si vous l'avez oublié. Liste des processus nommés "postgres" : (exemple sur une Debian):

flo:~# ps -ef | grep postgres | grep -v grep
postgres  2797     1  0 06:14 ?        00:00:00 /usr/lib/postgresql/9.0/bin/postgres -D /var/lib/postgresql/9.0/main -c config_file=/etc/postgresql   /9.0/main/postgresql.conf
postgres  2798  2797  0 06:14 ?        00:00:00 postgres: logger process                                                                                        
postgres  2800  2797  0 06:14 ?        00:00:00 postgres: writer process                                                                                        
postgres  2801  2797  0 06:14 ?        00:00:00 postgres: wal writer process                                                                                    
postgres  2802  2797  0 06:14 ?        00:00:00 postgres: autovacuum launcher process                                                                           
postgres  2803  2797  0 06:14 ?        00:00:00 postgres: stats collector process                                                                               
flo:~#

Voyez que le processus 2797 est le père de tous les autres :

postgres  2797     1  0 06:14 ?        00:00:00 /usr/lib/postgresql/9.0/bin/postgres -D /var/lib/postgresql/9.0/main -c config_file=/etc/postgresql/9.0/main/postgresql.conf

le chemin derrière le -D est l'emplacement du cluster. Celui derrière le -c l'emplacement du fichier de configuration.

config_file=/etc/postgresql/9.0/main/postgresql.conf

Normalement, les autres fichiers de configuration du cluster (pg_hba.conf, pg_ident.conf) sont dans le même répertoire .

/usr/lib/postgresql/9.0/bin/postgres

est l'emplacement des binaires.

Arborescence du répertoire du cluster:

flo:/var/lib/postgresql/9.0/main# ls -l
total 48 
drwx-- 11 postgres postgres 4096 mai 10 15:19 base 
drwx--  2 postgres postgres 4096 mai 10 18:29 global
drwx--  2 postgres postgres 4096 avr  4 19:58 pg_clog
drwxr-xr-x  2 postgres postgres 4096 mai 10 08:15 pg_log
drwx--  4 postgres postgres 4096 avr  4 19:58 pg_multixact
drwx--  2 postgres postgres 4096 avr  4 19:58 pg_subtrans
drwx--  2 postgres postgres 4096 avr  4 19:58 pg_tblspc
drwx--  2 postgres postgres 4096 avr  4 19:58 pg_twophase
-rw---  1 postgres postgres    4 avr  4 19:58 PG_VERSION
drwx--  3 postgres postgres 4096 avr  4 19:58 pg_xlog
-rw---  1 postgres postgres  133 mai 10 08:15 postmaster.opts
-rw---  1 postgres postgres   54 mai 10 08:15 postmaster.pid
lrwxrwxrwx  1 root     root       31 avr  4 19:58 root.crt -> /etc/postgresql-common/root.crt

Quelques sous-répertoires et fichiers :

  • base : répertoire des fichiers de base de données
  • pg_log : log de la base de données (c'est le seul répertoire du cluster où vous pouvez supprimer des fichiers!)
  • pg_clog et pg_xlog : commit log (état des transactions) et répertoire des fichiers WAL (Write Ahead Log, utilisé pour la durabilité ).
  • postmaster.pid : fichier verrou utilisé pour éviter que plusieurs instances ne soient actives sur le même répertoire de données.

Attention : le contenu de pg_clog et pg_xlog ne doit pas être supprimé!

B. Changer le mot de passe de l'utilisateur système postgres

À moins que vous n'ayez compilé les sources pour utiliser PostgreSQL sur votre compte utilisateur, un utilisateur postgres a été créé sur votre système. Afin de pouvoir l'utiliser, vous devez changer le mot de passe de cet utilisateur. Pour cela, sous Linux, connectez-vous en tant que root et exécutez la commande 'passwd postgres'. (ne pas utiliser un mot de passe trivial!)

C. Créer un cluster de base de données.

Avec certaines distributions (Redhat, Debian), un cluster est créé par défaut à l'installation des paquets. De même pour l'installation sous Windows. Si vous êtes dans un autre cas de figure, il vous faudra donc en créer un. Pour cela, utilisez la commande initdb.

D. Autoriser les connexions

L'installation de PostgreSQL positionne des valeurs par défaut dans les fichiers de configuration. Après l'installation, PostgreSQL est configuré de telle sorte que les connexions ne sont pas possibles à partir du réseau. Pour autoriser des clients distants à se connecter, il faut configurer deux fichiers : postgresql.conf et pg_hba.conf.

1. Connexions réseau (postgresql.conf)

À l'installation, PostgreSQL est configuré pour n'accepter que les connexions locales (c'est le paramètre listen_addresses). Si vous souhaitez pouvoir vous connecter à partir du réseau, il faut dé-commenter le paramètre listen_addresses du fichier postgresql.conf, et préciser sur quelle(s) adresse(s) postgres accepte les connexions.

Attention : ce sont bien les adresses IP d'écoute, c'est-à-dire les adresses IP du serveur sur lesquelles le serveur PostgreSQL va écouter. Si vous précisez une adresse '*', postgres va écouter les connexions sur toutes les interfaces réseau du serveur. Si vous précisez une adresse IP, cela signifie que postgres va écouter sur l'interface réseau de votre machine qui a cette adresse IP. Si vous souhaitez n'autoriser les connexions qu'à une liste de machines ou d'adresses IP, c'est dans pg_hba.conf que vous pouvez le faire (paragraphe suivant). Pour que les paramètres soient pris en compte, il faut redémarrer le serveur PostgreSQL. Exemples : (connexion locales)

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                       # comma-separated list of addresses;
                                       # defaults to 'localhost', '*' = all
                                       # (change requires restart)
port = 5432                             # (change requires restart)@@

(connexion sur l'adresse 192.168.0.4 et local, port 5433)

listen_addresses = '192.168.0.4, localhost'         # what IP address(es) to listen on;
                                       # comma-separated list of addresses;
                                       # defaults to 'localhost', '*' = all
                                       # (change requires restart)

port = 5432 # (change requires restart)@@

2. Authentification des clients (pg_hba.conf)

Le fichier pg_hba.conf configure les autorisations pour les bases du cluster. Chaque ligne précise une règle aidant à décider si l'utilisateur est habilité à se connecter ou non. Le fichier est lu dans l'ordre par postgres, et, dès qu'une ligne est rencontrée qui correspond au cas testé, la lecture s'arrête. Cela signifie que l'ordre des lignes est important. Sur chaque ligne est précisé le type de connexion, un nom de base de données, un nom d'utilisateur, et la méthode d'authentification. Les méthodes d'authentification les plus classiques sont : md5 (par mot de passe crypté), ident (à partir du nom d'utilisateur du système d'exploitation, non utilisable sous Windows).

Exemple :

# connection par socket Unix pour l'administration du serveur
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   all         postgres                               ident sameuser
# connection par socket Unix 
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   mabase      tom                                    md5
local   truc        all                                    ident sameuser
# Connexions locales en Ipv4 :
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
host    mabase      tom         127.0.0.1/32          md5
host    truc        all         127.0.0.1/32          md5
# Connexion distante en Ipv4 :
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
host    mabase      tom         192.168.12.10/32      md5
host    truc        all         192.168.12.10/32      md5

La première ligne : local all postgres ident sameuser signifie que, si postgres reçoit une demande de connexion sur n'importe quelle base (all) par socket Unix (local), pour l'utilisateur postgres, alors la méthode d'authentification utilisée est : ident. sameuser signifie que postgres vérifie que le nom de l'utilisateur Unix propriétaire de la socket est le même que celui utilisé pour se connecter à la base.

La ligne suivante : local mabase tom md5

signifie que, lorsque tom essaie de se connecter par socket Unix sur la base mabase, c'est l'authentification md5 qui est utilisée.

La ligne : local truc all ident sameuser

signifie que lorsque n'importe que n'importe quel utilisateur essaie de se connecter à la base truc par socket Unix, c'est l'authentification ident sameuser qui est utilisée.

La ligne :

host mabase tom 127.0.0.1/32 md5

signifie qu'une demande de connexion à partir pour la base mabase, par un utilisateur tom, en local par Ipv4 est authentifiée par md5.

La ligne :

host mabase tom 192.168.12.10/32 md5

signifie qu'une demande de connexion de l'utilisateur tom sur mabase, à partir de l'adresse 192.168.12.10 est authentifiée par md5.

On voit donc que tom est autorisé à se connecter sur la base mabase, soit par socket Unix, soit par Ipv4 en local, soit par Ipv4 à partir de : 192.168.12.10. Les autres utilisateurs (à part l'utilisateur postgres) ne peuvent se connecter que sur la base truc. Tom peut également se connecter sur la base truc, car tom fait partie de l'ensemble des utilisateurs (all). NB : CIDR est une façon de noter les ensembles d'adresses IP, avec le chiffre derrière le '/' indiquant la taille du masque en bits (ainsi un réseau de classe A est en /8, classe B, 16, classe C, 24, une IP unique /32, et tout le monde : 0.0.0.0/0 ) (voir l'article Wikipedia : http://fr.wikipedia.org/wiki/Adresse_IPv4 )

Remarques : Le fichier configure le cluster, il est donc commun à toutes les bases du cluster : attention à ne pas autoriser un utilisateur sur une base par erreur. Attention, ne surtout pas autoriser d'authentification trust ni ident par le réseau, parce que cela signifierait faire entièrement confiance au client... Si vous voulez en savoir plus sur l'authentification du client, allez voir la documentation ici : http://docs.postgresql.fr/9.0/client-authentication.html

3. Prise en compte des paramètres de configuration

Pour que PostgreSQL prenne en compte les modifications de paramètres sans redémarrer le serveur, vous avez les solutions suivantes :

  • utiliser pg_ctl reload (remplacé par pg_ctlcluster sous Debian)
  • envoyer un signal SIGHUP à postgres

Sous Windows, il est possible d'utiliser un raccourci dans le menu Démarrer (« Rechargez la configuration »).

Attention : certaines options ne sont prises en compte qu'au démarrage (voir la documentation, les commentaires de postgresql.conf ou la colonne context de la vue pg_settings)

4. Créer une base

Nous allons créer une base mabase sur le cluster, puis faire de tom le propriétaire de la base (afin qu'il puisse faire ce qu'il veut sur cette base)

postgres@flo:/etc/postgresql/9.0/main$ pg_lsclusters
Version Cluster   Port Status Owner    Data directory                     Log file
9.0 main      5432 online postgres /var/lib/postgresql/9.0/main       custom

Pour cela, lancez la commande createdb :

postgres@flo$ createdb mabase

NB : createdb lance en fait la commande CREATE DATABASE pour vous.

5. Créer un rôle et lui donner des droits sur une base

NB : les utilisateurs et les groupes sont tous gérés par des rôles.

En tant qu'utilisateur postgres, lancez psql :

postgres@flo:/usr/share/doc/postgresql-common$ psql Bienvenue dans psql 9.0.6, l'interface interactive de PostgreSQL.

Saisissez:
    \copyright pour les termes de distribution
    \h pour l'aide-mémoire des commandes SQL
    \? pour l'aide-mémoire des commandes psql
    \g ou point-virgule en fin d'instruction pour exécuter la requête
    \q pour quitter
postgres=#

Créez un rôle tom, avec les droits de login (pour qu'il ait le droit de se connecter au serveur), et le mot de passe : secret.

postgres=# CREATE ROLE tom LOGIN password 'secret';
CREATE ROLE
postgres=#

Pour que tom soit le propriétaire de mabase :

postgres=# ALTER DATABASE mabase OWNER TO tom;
ALTER DATABASE

Sortez de psql :

postgres=# \q
postgres@flo:/usr/share/doc/postgresql-common$

NB : les commandes CREATE DATABASE et CREATE ROLE (création de base et d'utilisateur) sont globales au cluster. Il est donc possible de les exécuter de n'importe quelle base.

Maintenant, l'utilisateur tom peut se connecter sur mabase : lancez psql, en précisant que vous vous connectez en tant que tom :

flo@flo:~$ psql -U tom mabase
Mot de passe pour l'utilisateur tom :
Bienvenue dans psql 9.0.6, l'interface interactive de PostgreSQL.
Saisissez:
    \copyright pour les termes de distribution
    \h pour l'aide-mémoire des commandes SQL
    \? pour l'aide-mémoire des commandes psql
    \g ou point-virgule en fin d'instruction pour exécuter la requête
    \q pour quitter
mabase=>

Remarque : il faut préciser la base! Sinon psql cherchera à se connecter à une base "tom".

Si vous souhaitez donner le droit à tom de créer des bases:

postgres=# ALTER ROLE tom CREATEDB;
ALTER ROLE
postgres=#

Pour les détails sur les droits, lisez le chapitre correspondant de la documentation : http://docs.postgresqlfr.org/9.0/privileges.html

E. Super-utilisateur

Le super-utilisateur est un utilisateur qui dispose de droits spéciaux (certaines fonctions ne sont utilisables que par un super-utilisateur). Les super-utilisateurs passent au travers des vérifications de droits. Si vous avez installé PostgreSQL en tant que root, classiquement vous avez un super-utilisateur postgres. Attention! Le super-utilisateur disposant de tous les droits, éviter de l'utiliser si ce n'est pas nécessaire, afin de limiter le risque d'erreur.

F. Je ne peux pas me connecter à la base? Que faire?

Que vérifier?

  • D'abord : lisez le message d'erreur! (ça peut suffire à trouver la solution à partir d'un bon moteur de recherche, des archives des mailing-lists ou de forums...)
  • Consultez la log (voir chapitre suivant)
  • Cherchez quels sont les clusters présents ? (sous Debian : pg_lsclusters...)
  • Vérifiez le fichier postgresql.conf (le paramètre listen_addresses est-il correct? Le port est-il celui souhaité? Le client essaie-t-il de se connecter sur le bon port?)
  • Vérifiez le fichier pg_hba.conf
  • Vérifiez le propriétaire de la base
  • Le rôle que vous utilisez a-t-il le droit de se loguer (autorisation LOGIN) ?
  • Le rôle utilisé a-t-il le droit de se connecter à la base de données (sinon utilisez GRANT CONNECT on mabase ...)

NB : vous obtenez la liste des bases d'un cluster avec la commande \l dans psql

G. Où se trouve la log ? Comment la configurer?

La configuration de la log est effectuée par le fichier postgresql.conf (voir les paramètres log_destination et log_directory) Dans une installation standard de PostgreSQL, la log se trouve dans un répertoire pg_log sous le répertoire PGDATA (répertoire du cluster). Par exemple, sous Windows :

C:\Program Files\PostgreSQL\9.0\data\pg_log



En fonction de votre utilisation (production, test, développement), vous pourrez régler les paramètres de la log. Par exemple, loguer tous les ordres SQL peut être fort utile en développement (surtout lorsque vous utilisez un ORM).

Pensez à recharger la configuration après modification.

H. Arrêter/démarrer le serveur PostgreSQL

Sous Windows : vous pouvez utiliser "stoppez le service" et "démarrez le service" dans le menu démarrer, ou bien dans un terminal, utiliser pg_ctl :

C:\Program Files\PostgreSQL\9.0\bin>pg_ctl start -D "C:\Program Files\PostgreSQL\9.0\data"
server starting

Sous Linux : c'est la commande pg_ctl (sous Debian : pg_ctlcluster ou service postresql start

sous Redhat)

V Outils

A. Outil graphique : pgAdmin3

PgAdmin3 est sans doute l'outil le plus populaire pour développer et administrer PostgreSQL. http://www.pgadmin.org/?lang=fr_FR Voici un apercu de ce à quoi il ressemble. Pour le reste, vous pourrez vous reporter à sa documentation.

pgadmin

B. psql (outil en ligne de commande)

Psql permet d'exécuter des ordres SQL sur les bases, et également des commandes de gestion et d'administration. Pour lancer psql :

1. Windows :

a) Via le menu démarrer (gère tout seul le changement d'utilisateur)

Remarque : si, à la première connexion, vous avez ce message d'avertissement :

Warning: Console code page (437) differs from Windows code page (1252)
        8-bit characters might not work correctly. See psql reference
        page "Notes for Windows users" for details.
postgres=#

reportez-vous à la partie installation sous Windows.

b) En ligne de commande dans une console :

Si vous lancez psql non pas avec le menu démarrer, mais à partir d'une console Windows, il faut être connecté en tant qu'utilisateur système postgres. Ceci est possible avec la commande runas de Windows.

runas user:postgres cmd.exe Puis modifiez la police de la console pour utiliser Lucida Console, et changez de code page :

cmd.exe /c chcp 1252

(pour la France)

Malheureusement, si votre base est en UTF8, la console Windows est incapable de gérer correctement l'affichage. Il faudra également éviter de saisir des données avec psql, et préférer pgAdmin pour cet usage (pgAdmin gère parfaitement les différents encodages).

2. Sous Linux :

psql mabase

3. Remarques :

Si vous ne précisez pas le nom de la base, psql essaie de se connecter à la base de même nom que l'utilisateur. Si vous ne précisez pas le nom d'utilisateur, c'est le nom de l'utilisateur du système qui est utilisé.

4. Commandes

Commandes psql à connaître absolument :

  • \? pour l'aide des commandes psql (si vous deviez n'en retenir qu'une)
  • \q quitter
  • \h aide des commandes sql

autres commandes intéressantes :

  • \l liste des bases de données
  • \c se connecter à une base
  • \d [nom] pour la description d'une table, d'un index, séquence, vue
  • \d liste des relations (tables, vues et séquences)
  • \i nom_fichier exécuter un fichier de commandes SQL

Attention! Pour la commande \i, les noms de fichiers sous Windows doivent utiliser le séparateur slash " / "et non antislash " \ "  . Exemple :

\i C:/tests.sql

C. phpPgAdmin

C'est un outil d'administration web pour PostgreSQL http://phppgadmin.sourceforge.net/

D. Copy

copy est un outil pour le chargement et déchargement de données en masse. Ce n'est pas une commande standard SQL. http://docs.postgresqlfr.org/9.0/sql-copy.html

VI Développement

A. SQL

Plusieurs outils permettent d'exécuter du code SQL de façon interactive : psql, pgAdmin (voir les sections qui leur sont consacrées). Vous pouvez également utiliser un outil tiers, si vous préférez...

B. Procédures stockées

L'intérêt des procédures stockées est de pouvoir exécuter des fonctions directement sur le serveur. Les procédures stockées sont efficaces et rapides, et permettent de traiter des données, soit pour consultation par un client, soit en mise à jour.

PostgreSQL vous donne le choix du langage de procédures stockées.

Vous pouvez utiliser:

  • PL/pgsql (proche de SQL, facile à utiliser, utilisable pour les triggers)
  • PL/Tcl
  • PL/Perl (pratique lorsqu'il y a des traitements de chaînes de caractères à effectuer)
  • PL/Python
  • D'autres langages ne sont pas inclus dans la distribution principale :
  • PL/Java, PL/PHP, PL/R, PL/Ruby, PL/Scheme, PL/sh
  • Vous pouvez aussi en définir un vous-même...

C. JDBC

Le pilote JDBC pour PostgreSQL est un pilote natif (il est entièrement écrit en Java)

Les différentes versions du pilote JDBC sont disponibles ici (ainsi que la documentation)

http://jdbc.postgresql.org/index.html Ensuite vous avez juste à utiliser le .jar de manière classique (le mettre dans le CLASSPATH de votre application)

NB : la syntaxe de l'URL String url="jdbc:postgresql:test_conn";

L'URL a une de ces formes :

  • jdbc:postgresql:database
  • jdbc:postgresql://host/database
  • jdbc:postgresql://host:port/database

Allez voir la documentation http://jdbc.postgresql.org/documentation/83/connect.html pour plus de détails.

Quel driver prendre ?

Normalement, la dernière version du driver devrait vous convenir (elle est compatible avec toutes les versions supportées de PostgreSQL). Mais il y en a 2 variétés : la JDBC3, à préférer pourt les JVM 1.4 et 1.5, et la JDBC4, pour la JVM 1.6. Plus de précisions et une matrice de compatibilité sur la page de téléchargement : http://jdbc.postgresql.org/download.html

D. Autres (PERL, Python, .Net, ODBC, Tcl...)

Voir ici : http://docs.postgresqlfr.org/9.0/external-projects.html

E. A savoir !

1. Majuscules/minuscules

Le nom des objets dans les ordres SQL est converti automatiquement en minuscules. Par exemple, si vous exécutez : SELECT Id, Valeur FROM Matable; l'ordre réellement exécuté sera : SELECT id, valeur FROM matable;

mabase=> SELECT Id, Valeur FROM Matable;
 id | valeur
+
  1 | azerty
(1 ligne)
mabase=>

Si vous souhaitez utiliser la casse dans les noms d'objets (ce qui n'est pas conseillé en général), utilisez les guillemets.

Par exemple : SELECT "Id", "Valeur" FROM "Matable"; Remarquez que ce comportement est différent d'autres moteurs, qui soit passent tous les noms en majuscule, soit conservent la casse. (Le comportement standard pour un SGBD est d'ignorer la casse, ainsi il est déconseillé généralement d'utiliser des noms d'objet avec des casses différentes : si vous utilisez toujours des minuscules, le comportement sera toujours le même, quel que soit le SGBD)

2. Erreurs et transactions

Avec PostgreSQL, lorsqu'une erreur se produit dans une transaction, il n'est pas possible de l'ignorer. L'erreur doit être gérée. Sinon tous les ordres suivants sont également en erreur. De plus, à la fin de la transaction, il n'est pas possible de commiter. L'ordre COMMIT provoque en réalité un ROLLBACK.

Exemple :

mabase=> begin;
BEGIN
mabase=> insert into matable(valeur, nb) values ('c',2);
INSERT 0 1
mabase=> insert into matable(valeur, nb) values ('c',2);
ERREUR:  la valeur d'une clé dupliquée rompt la contrainte unique « u_matable »
mabase=> insert into matable(valeur, nb) values ('d',2);
ERREUR:  la transaction est annulée, les commandes sont ignorées jusqu'à la fin du bloc
de la transaction
mabase=> commit;
ROLLBACK
mabase=> select valeur, nb from matable;
 valeur | nb
+
 a      |  2
 b      |  2
(2 lignes)
mabase=>

3. Savepoints

Les savepoints ne sont pas spécifiques à PostgreSQL. Mais c'est une fonctionalité SQL trop peu connue, et pourtant extrêmement utile, dans le cas de traitements lourds.

Un savepoint sert à marquer un point de reprise dans un traitement. Lorsque vous avez à effectuer un traitement long (par exemple lorqu'un programme doit mettre à jour tout un ensemble de données les unes après les autres), vous pouvez mettre des savepoints à intervalles réguliers. Lorsqu'une erreur se produit, vous faites en sorte que le programme effectue un ROLLBACK TO SAVEPOINT vers un point de sauvegarde où l'état de vos données est cohérent (généralement le dernier point de sauvegarde). Ensuite vous pouvez annuler le traitement (après par exemple pris la précaution de loguer les événements...)

L'intérêt est que seul les traitements effectués après le point de sauvegarde sont perdus. Cela évite à votre programme de faire un ROLLBACK sur l'ensemble du traitement! Votre programme peut ainsi effectuer des traitements partiellement.

4. DDL dans les transactions!

Une des fonctionnalités les plus épatantes de PostgreSQL est la possibilité d'inclure des ordres DDL dans des transactions.

Exemple :

Dans une transaction, on crée une table "test", puis une table "matable". La création de "matable" échoue (la table existe déjà). On fait un rollback sur la transaction : la table "test" n'existe pas.

mabase=> BEGIN;
BEGIN
mabase=> CREATE TABLE test (
    id serial NOT NULL,
    valeur character varying(20) NOT NULL);
NOTICE:  CREATE TABLE créera des séquences implicites « test_id_seq » pour la colonne serial « test.id »
CREATE TABLE
mabase=> ALTER TABLE test ADD CONSTRAINT pk_test PRIMARY KEY (id);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY créera un index implicite « pk_test » pour la table « test »
ALTER TABLE
mabase=> CREATE TABLE matable (
    id serial NOT NULL,
    valeur character varying(20) NOT NULL);
NOTICE:  CREATE TABLE créera des séquences implicites « matable_id_seq1 » pour la colonne serial « matable.id »
ERREUR:  la relation « matable » existe déjà
mabase=> ROLLBACK;
ROLLBACK
mabase=> \d
                 Liste des relations
 Schéma |       Nom        |   Type   | Propriétaire
+++--
 public | matable          | table    | tom
 public | matable_id_seq   | séquence | tom
 public | table_flo        | table    | flo
 public | table_flo_id_seq | séquence | flo
(4 lignes)
mabase=>

Intérêt :

On peut faire tout un ensemble de modification de façon atomique (par exemple la migration d'un schéma pour l'évolution d'une application), C'est un soulagement pour le DBA qui devra passer votre script de migration, de nuit, de savoir qu'il n'aura pas à restaurer la base en cas d'échec.

5. Count(*)

En raison de l'implémentation actuelle du MVCC, count(*) force le parcours complet de la table, ce qui est donc lent.

VII Et après?

A. Lire la documentation :

Lien vers la documentation en Français : http://docs.postgresql.fr/ En anglais : [http://www.postgresql.org/docs/ |http://www.postgresql.org/docs/|en]

B. Sites utiles :

http://www.postgresql.org/ : site officiel http://www.postgresql.fr/ : site de la communauté francophone.

C. Pour trouver de l'aide complémentaire :

La communauté PostgreSQL est très active, et vous trouverez facilement de l'aide pour les problèmes les plus simples aussi bien que pour les cas les plus tordus.

1. Listes de diffusion :

La liste francophone : http://archives.postgresql.org/pgsql-fr-generale/ Les autres : http://www.postgresql.org/community/lists/ Attention : les listes "developer" sont pour les développeurs DE PostgreSQL uniquement !

2. Forum de la communauté francophone :

http://forums.postgresql.fr/

3. Remarque : comment poser vos questions?

Si vous posez une question parce que vous avez un problème, vous voulez certainement qu'il soit résolu le plus vite possible. Alors pensez à ceux qui vont tenter de vous aider, et faites-leur gagner du temps en donnant les informations nécessaires. Soyez le plus clair possible.

Pensez à préciser au minimum :

  • La version de PostgreSQL utilisée,
  • Le système d'exploitation.,
  • ce que vous avez fait,
  • ce que vous vouliez faire,
  • le message d'erreur (ou son absence),
  • le résultat obtenu.

Si vous n'arrivez pas à vous connecter, précisez si le client est sur la même machine que le serveur. Recopiez les messages d'erreurs, consultez la log... enfin donnez le maximum d'informations pertinentes, et si on vous pose des questions, répondez-y le plus précisément possible.
Evitez également de dire qu'il y a un bug si vous n'en êtes pas absolement certain(e), et postez sur la mailing-list ou le forum approprié (par exemple, la mailing-list pour les novices n'est pas un endroit indigne, et des hackers y répondent régulièrement et avec bienveillance)

dimanche 3 octobre 2010

Premiers pas avec PostgreSQL 9.0

J'ai mis à jour le document que j'avais rédigé il y a un peu plus d'un an, afin qu'il soit à jour avec la sortie de la 9.0. À l'occasion, j'ai changé le titre, qui ne me plaisait pas beaucoup, pour "Premiers pas avec PostgreSQL". En attendant que je fasse la mise en forme pour le passer sur le blog − c'est très long − , voici la version pdf et la version OpenOffice, que vous pouvez dès à présent lire et distribuer. N'hésitez pas à me faire part de vos remarques.

mercredi 16 juin 2010

Présentation de la version 9.0 de PostgreSQL

Ce document tente de présenter les principaux changements apportés par PostgreSQL 9.0, par rapport à la version majeure précédente, la version 8.4. Dans la mesure du possible, chaque fonctionnalité sera expliquée et accompagnée d'une démonstration. Toutes les nouveautés ne sont bien sûr pas présentées (il y en a plus de 200).

La version 9.0, comme son nom l'indique, est une version capitale dans la progression de PostgreSQL. Même si les solutions de réplication pour PostgreSQL sont nombreuses et répondent à des problématiques variées, la version 9.0 apporte une réplication simple, robuste et intégrée au moteur, qui sera vraisemblablement utilisée par défaut dans la plupart des configurations de Haute Disponibilité reposant sur PostgreSQL.

Les changements ont été subdivisés en quatre parties:

  • Les deux nouveautés incontournables
  • Les nouveautés
  • Les changements pouvant entraîner des régressions
  • Les améliorations

Les incontournables

Ces deux nouveautés sont celles qui ont justifié à elles seules le renommage de 8.5 en 9.0.

Hot Standby

Cette nouvelle fonctionnalité est une des deux raisons du renommage en 9.0. Il s'agit de proposer une base de 'Standby', c'est-à-dire une baseappliquant les journaux binaires générés par la base de production, tout  en la rendant ouverte en lecture seule. Ceci est assez complexe car, pendant l'exécution de ces requêtes en lecture seule, la base en Standby doit aussi pouvoir appliquer les données binaires provenant de la base de production, être capable de décider si les modifications peuvent entrer en conflit avec les lectures et déterminer les actions à entreprendre en conséquence : mettre en pause la restauration ou tuer des requêtes en lecture seule. Ce patch est volumineux et complexe, il rajoute des informations dans la journalisation à l'intention de la base de Standby et un mécanisme de résolution des conflits. C'est donc une des fonctionnalités majeures, et une des principales fonctionnalités à aider à tester.

Pour mettre ceci en place, il suffit de paramétrer la base de production comme suit :

postgresql.conf Primaire:

wal_level = 'hot standby' # Génère les informations supplémentaires dans les journaux
# vacuum_defer_cleanup_age # Optionnellement, vous pourriez vouloir paramétrer ceci, mais son réglage pourrait être complexe

Ensuite, créer une base de standby (la procédure est la même que précédemment pour un Warm Standby : pg_start_backup sur la production, recopie des fichiers sur l'esclave, pg_end_backup sur la production).

Puis il suffit de recopier les journaux sur le secondaire et de rajouter ceci dans son postgresql.conf :

postgresql.conf Secondaire:

hot_standby=on
max_standby_delay=30s # -1= toujours attendre, 0= ne jamais attendre, sinon attendre cette durée

et d'utiliser un programme comme pg_standby sur le secondaire pour intégrer les journaux (à paramétrer dans le recovery.conf).

max_standby_delay permet de déterminer le comportement de la base de standby en cas de conflit entre l'application des journaux de transactions et les requêtes en lecture seule. En cas de conflit, la base de standby acceptera d'attendre au plus max_standby_delay avant de tuer les requêtes en lecture qui bloquent l'application des journaux.

Il est bien sûr vivement conseillé de lire la documentation avant de mettre en place cette fonctionnalité… Ne serait-ce que pour bien comprendre les conséquences du réglage de max_standby_delay et vacuum_defer_cleanup_age, qui ne sont pas simples à appréhender.

Streaming Replication

C'est la deuxième moitié de la raison du passage en 9.0. Il s'agit cette fois-ci de modifier le mécanisme d'archivage pour le rendre continu : les bases de standby peuvent donc se connecter au maître et récupérer à tout moment ce qui leur manque des journaux, non plus en termes de fichiers entiers, mais bien en termes d'enregistrements dans ces journaux (des fragments de ces fichiers donc). Il s'agit donc bien d'une réplication binaire, pas de la rééxécution dans le même ordre de requêtes SQL comme sur certains autres moteurs de bases de données, avec tous les risques de cohérence que cela impliquerait.

Les bases de production et de standby sont identiques au niveau binaire (enfin presque, on ne va pas rentrer dans les détails, mais ne vous en faites pas si les fichiers de données n'ont pas la même somme de contrôle).

wal_level devra valoir « archive » (ou « hot standby ») pour continuer à faire de l'archivage continu.

postgresql.conf primaire :

max_wal_senders = x # Nombre maximum de « wal_senders », les processus chargés de répondre à des serveurs de standby
wal_keep_segments # Nombre de fichiers de journaux de transactions à conserver en ligne quoi qu'il arrive (évite d'avoir à les recopier manuellement sur le(s) secondaires en cas de déconnection lente)

Sur le secondaire :

recovery.conf secondaire:

stanby_mode = on
primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass' # La chaîne de connexion pour une session sur le maître

postgresql.conf secondaire:

wal_level # à la même valeur que sur le maître (pour le retour de bascule…)
hot_standby=on/off # Suivant que vous voulez en même temps être en hot standby

fichier pg_hba.conf : On doit y créer une entrée pour les connexions de réplication. La base est « replication », l'utilisateur utilisé doit avoir l'attribut de superutilisateur. Attention à ne pas donner des droits trop importants ici, donner accès aux journaux de transactions en lecture à n'importe qui donne accès à des informations privilégiées.

pg_hba.conf primaire:

host    replication     foo             192.168.1.100/32        md5

Comme pour Hot Standby, cette fonctionnalité est suffisamment riche et complexe pour qu'il soit vivement conseillé de lire la doc. Et de faire des tests de bascule une fois l'ensemble mis en place.

Ce qui est très important avec ces deux fonctionnalités, c'est que vous pouvez les utiliser ensemble. Vous pouvez donc avoir donc une base de stanby répliquée de façon quasi-synchrone avec la production, et exécuter des requêtes en lecture seule sur cette base.

Les nouveautés

Contraintes d'exclusion

Il est maintenant possible de déclarer des contraintes d'unicité plus complexes que celles s'appuyant sur l'opérateur '=' (contrainte d'unicité, deux jeux de colonnes ne pouvant être identiques).

Nous allons, pour l'illustrer, utiliser l'exemple de l'auteur, en utilisant le type 'temporal' qu'il a aussi développé. Ce type de données permet de définir des 'plages de temps', c'est à dire par exemple 'la plage de 12h15 à 13h15'.

Il faut donc récupérer le module temporal à l'adresse suivante : http://pgfoundry.org/projects/temporal/ , le compiler et l'installer comme un contrib (exécuter le script SQL fourni).

CREATE TABLE reservation
(
salle TEXT,
professeur TEXT,
durant PERIOD);

ALTER TABLE reservation ADD CONSTRAINT test_exclude EXCLUDE USING gist (salle WITH =,durant WITH &&);

Par ceci, nous disons qu'un enregistrement doit être refusé (contrainte d'exclusion) s'il en existe déjà un vérifiant les deux conditions concerner la même salle, et être en intersection au niveau de l'intervalle de temps.

marc=# INSERT INTO reservation (professeur,salle,durant) VALUES ( 'marc', 'salle techno', period('2010-06-16 09:00:00', '2010-06-16 10:00:00'));
INSERT 0 1
marc=# INSERT INTO reservation (professeur,salle,durant) VALUES ( 'jean', 'salle chimie', period('2010-06-16 09:00:00', '2010-06-16 11:00:00'));
INSERT 0 1
marc=# INSERT INTO reservation (professor,room,during) VALUES ( 'marc', 'salle chimie', period('2010-06-16 10:00:00', '2010-06-16 11:00:00'));
ERROR: conflicting key value violates exclusion constraint "test_exclude"
DETAIL: Key (room, during)=(salle chimie, [2010-06-16 10:00:00+02, 2010-06-16 11:00:00+02)) conflicts with existing key (room, during)=(salle chimie, [2010-06-16 09:00:00+02, 2010-06-16 11:00:00+02)).

L'insertion est interdite, puisque la salle de chimie est déjà prise de 9h à 11h.

Triggers par colonne

Voici d'abord un trigger par colonne.

CREATE TRIGGER toto BEFORE UPDATE of a ON t1 FOR EACH ROW EXECUTE PROCEDURE mon_trigger();

Ce trigger ne se déclenche que si la colonne a de la table t1 a été modifiée.

Triggers WHEN

Voici maintenant des exemples tirés de la documentation officielle pour la clause WHEN des triggers:

CREATE TRIGGER check_update
BEFORE UPDATE ON accounts
FOR EACH ROW
WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
EXECUTE PROCEDURE check_account_update();

CREATE TRIGGER log_update
AFTER UPDATE ON accounts
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE PROCEDURE log_account_update();

DEFERRABLE UNIQUE CONSTRAINTS

Cette fonctionnalité aussi promet d'être pratique. Voici un exemple avec une clé primaire au lieu d'une simple clé unique, mais cela revient au même :

marc=# CREATE TABLE test (a int primary key);
marc=# INSERT INTO test values (1), (2);
marc=# UPDATE test set a = a+1;
ERROR: duplicate key value violates unique constraint "test_pkey"
DETAIL: Key (a)=(2) already exists.

Normal, mais dommage : à la fin de la transaction, mes données auraient été cohérentes. D'autant plus que si la table avait été triée physiquement par ordre descendant, ça passait ! En 8.4, il n'y avait pas d'échappatoire simple, il fallait trouver une astuce pour mettre à jour les enregistrements dans le bon ordre.

Nous pouvons maintenant faire ceci :

marc=# CREATE TABLE test (a int primary key deferrable);
marc=# INSERT INTO test values (2),(1);
marc=# UPDATE test set a = a+1;
ERROR: duplicate key value violates unique constraint "test_pkey"
DETAIL: Key (a)=(2) already exists.

Ah zut, ça ne marche pas.

En fait, je l'ai fait exprès : j'en profite pour faire un petit rappel sur les contraintes deferrable/deferred : une contrainte 'deferrable' PEUT être vérifiée en fin de transaction (elle est 'retardable'). Il faut toutefois dire à PostgreSQL expressément qu'on veut vraiment faire ce contrôle en fin de transaction.

On peut, pour la session en cours demander à passer toutes les contraintes en 'DEFERRED' :

marc=# SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINTS
marc=# UPDATE test set a = a+1;
UPDATE 2

Si on veut ne pas avoir à effectuer le SET CONSTRAINTS à chaque fois, il est aussi possible de déclarer la contrainte comme INITIALLY DEFERRED:

CREATE TABLE test (a int PRIMARY KEY DEFERRABLE INITIALLY DEFERRED);

Un autre rappel s'impose : les contraintes DEFERRED sont plus lentes que les contraintes IMMEDIATE. Par ailleurs, il faut bien stocker la liste des enregistrements à vérifier en fin de transaction quelque part, et cela consomme de la mémoire. Attention à ne pas le faire sur des millions d'enregistrements d'un coup. C'est la raison pour laquelle les contraintes 'DEFERRABLE' ne sont pas 'INITIALLY DEFERRED' par défaut.

Fonctions anonymes

Cette nouvelle fonctionnalité permet de créer des fonctions à usage unique. Elles seront très pratiques dans des scripts de livraison de version applicative par exemple. Voici une version un peu différente du GRANT SELECT ON ALL TABLES qui sera présenté plus loin dans ce document, qui donne le droit de sélection à tout un jeu de tables, en fonction du propriétaire des tables, et en ignorant deux schémas :

DO language plpgsql $$ 
DECLARE
vr record;

BEGIN

FOR vr IN SELECT tablename FROM pg_tables WHERE tableowner = 'marc' AND schemaname NOT IN ('pg_catalog','information_schema')
LOOP
EXECUTE 'GRANT SELECT ON ' || vr.tablename || ' TO toto';
END LOOP;
END
$$
;

En 8.4, il aurait fallu créer une fonction (via CREATE FUNCTION), l'exécuter puis la supprimer (avec DROP FUNCTION). Le tout demandant d'avoir les droits pour ça. La 9.0 facilite donc ce type d'exécution rapide.

Paramètres nommés

La syntaxe retenue pour nommer les paramètres est la suivante:

CREATE FUNCTION test (a int, b text) RETURNS text AS $$
DECLARE
valeur text;
BEGIN
valeur := 'a vaut ' || a::text || ' et b vaut ' || b;
RETURN valeur;
END;
$$ LANGUAGE plpgsql;

Jusque là, on écrivait :

SELECT test(1,'toto');
test
-------------------------
a vaut 1 et b vaut toto
(1 row)

Maintenant, on peut utiliser cette syntaxe explicite:

SELECT test( b:='toto', a:=1);
test
-------------------------
a vaut 1 et b vaut toto
(1 row)

De nombreux langages permettent ce type de syntaxe d'appel de fonction, qui améliore fortement la lisibilité du code.

GRANT/REVOKE IN SCHEMA

C'est un problème idiot, et un peu frustrant, qui est déjà arrivé à beaucoup d'administrateurs de base de données : créer 400 tables, puis devoir attribuer des droits à un utilisateur sur ces 400 tables. Jusque là, on en était quitte pour créer un script. Plus maintenant :

GRANT SELECT ON ALL TABLES IN SCHEMA public TO toto;

Et la marche arrière :

REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM toto;

Bien sûr, cette commande ne vaut que pour les tables en place au moment de la commande. Il faudra toujours faire de nouveaux GRANT pour les futures tables du schéma.

ALTER DEFAULT PRIVILEGES

Encore une commande permettant de gagner du temps dans la gestion des droits.

ALTER DEFAULT PRIVILEGES FOR ROLE marc GRANT SELECT ON TABLES TO PUBLIC ;
CREATE TABLE test_priv (a int);
\z test_priv
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+------------+-------+-------------------+--------------------------
public | test_priv | table | =r/marc +|
| | | marc=arwdDxt/marc |

Les informations sur les droits par défaut sont stockées dans pg_default_acl.

Les changements pouvant entraîner régression

Ces deux changements dans PL/pgSQL peuvent entraîner des régressions dans du code fonctionnant en 8.4. Si vous avez du code PL/pgSQL, vérifiez le avant de migrer en 9.0. Le moteur génère des erreurs à l'exécution, comme illustré ci-dessous.

PL/pgSQL : meilleur contrôle du nom des variables

marc=# DO LANGUAGE plpgsql
$$
DECLARE
a int;
BEGIN
SELECT a FROM test;
END
$$
ERROR: column reference "a" is ambiguous
LINE 1: select a from test
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
QUERY: select a from test
CONTEXT: PL/pgSQL function "inline_code_block" line 4 at SQL statement

Si vous voulez modifier ce comportement, vous pouvez le faire globalement mais il est préférable de le faire par fonction, en exécutant une de ces commandes au début de votre fonction:

variable_conflict error        # mode par défaut
variable_conflict use_variable # choisir le nom de variable
variable_conflict use_column # choisir le nom de colonne

Protection des mots réservés en PL/pgSQL

marc=# DO LANGUAGE plpgsql
$$
DECLARE
table int;
BEGIN
table :=table+1;
END
$$
;
ERROR: syntax error at or near "table"
LINE 6: table :=table+1;
^
marc=# DO LANGUAGE plpgsql
$$
DECLARE
"table" int;
BEGIN
"table" :="table"+1;
END
$$
;
DO

Les améliorations

Le planificateur de requête a reçu un grand nombre d'améliorations dans cette version. Nous allons donc commencer par lui:

Join Removal

marc=# CREATE TABLE t1 (a int);
CREATE TABLE
marc=# CREATE TABLE t2 (b int);
CREATE TABLE
marc=# CREATE TABLE t3 (c int);
CREATE TABLE
On insère quelques données avec le generate_series…
marc=# EXPLAIN SELECT t1.a,t2.b from t1 join t2 on (t1.a=t2.b) left join t3 on (t1.a=t3.c);
QUERY PLAN
------------------------------------------------------------------------------
Merge Right Join (cost=506.24..6146.24 rows=345600 width=8)
Merge Cond: (t3.c = t1.a)
-> Sort (cost=168.75..174.75 rows=2400 width=4)
Sort Key: t3.c
-> Seq Scan on t3 (cost=0.00..34.00 rows=2400 width=4)
-> Materialize (cost=337.49..853.49 rows=28800 width=8)
-> Merge Join (cost=337.49..781.49 rows=28800 width=8)
Merge Cond: (t1.a = t2.b)
-> Sort (cost=168.75..174.75 rows=2400 width=4)
Sort Key: t1.a
-> Seq Scan on t1 (cost=0.00..34.00 rows=2400 width=4)
-> Sort (cost=168.75..174.75 rows=2400 width=4)
Sort Key: t2.b
-> Seq Scan on t2 (cost=0.00..34.00 rows=2400 width=4)
Pour l'instant, c'est normal, et on a le même comportement qu'en 8.4. Mais imaginons que sur la table t3, on ait une contrainte UNIQUE sur la colonne c. Dans ce cas, théoriquement, la jointure sur la table t3 ne sert à rien : le nombre d'enregistrements du résultat ne sera pas modifié, pas plus, bien sûr, que leur contenu. C'est lié au fait que la colonne est UNIQUE, que la jointure est un LEFT JOIN, et qu'aucune colonne de t3 n'est récupérée. Si la colonne n'était pas UNIQUE, la jointure pourrait augmenter le nombre d'enregistrements du résultat. Si ce n'était pas un LEFT JOIN, la jointure pourrait diminuer le nombre d'enregistrements du résultat.

En 9.0 :

marc=# ALTER TABLE t3 ADD UNIQUE (c);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "t3_c_key" for table "t3"
ALTER TABLE
marc=# EXPLAIN SELECT t1.a,t2.b from t1 join t2 on (t1.a=t2.b) left join t3 on (t1.a=t3.c);
QUERY PLAN
------------------------------------------------------------------
Merge Join (cost=337.49..781.49 rows=28800 width=8)
Merge Cond: (t1.a = t2.b)
-> Sort (cost=168.75..174.75 rows=2400 width=4)
Sort Key: t1.a
-> Seq Scan on t1 (cost=0.00..34.00 rows=2400 width=4)
-> Sort (cost=168.75..174.75 rows=2400 width=4)
Sort Key: t2.b
-> Seq Scan on t2 (cost=0.00..34.00 rows=2400 width=4)
(8 rows)
Cette optimisation devrait pouvoir être très rentable, entre autre quand les requêtes sont générées par un ORM (mapping objet-relationnel). Ces outils ont la fâcheuse tendance à exécuter des jointures inutiles. Ici on a réussi à diviser le coût estimé de la requête par 10.

C'est aussi une optimisation qui pourra être très utile pour les applications utilisant beaucoup de jointures et de vues imbriquées.

Cela constitue encore une raison supplémentaire de déclarer les contraintes dans la base : sans ces contraintes, impossible pour le moteur d'être sûr que ces réécritures sont possibles.

IS NOT NULL peut utiliser les index

Pour cette démonstration, nous allons comparer la version 8.4 et la 9.0 (la table que j'ai créée contient majoritairement des valeurs NULL) :

En 8.4 :

marc=# EXPLAIN ANALYZE SELECT max(a) from test;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.03..0.04 rows=1 width=0) (actual time=281.320..281.321 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..0.03 rows=1 width=4) (actual time=281.311..281.313 rows=1 loops=1)
-> Index Scan Backward using idxa on test (cost=0.00..29447.36 rows=1001000 width=4) (actual time=281.307..281.307 rows=1 loops=1)
Filter: (a IS NOT NULL)
Total runtime: 281.360 ms
(6 rows)

En 9.0 :

marc=# EXPLAIN ANALYZE SELECT max(a) from test;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.08..0.09 rows=1 width=0) (actual time=0.100..0.102 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..0.08 rows=1 width=4) (actual time=0.092..0.093 rows=1 loops=1)
-> Index Scan Backward using idxa on test (cost=0.00..84148.06 rows=1001164 width=4) (actual time=0.089..0.089 rows=1 loops=1)
Index Cond: (a IS NOT NULL)
Total runtime: 0.139 ms
(6 rows)

On constate que la 9.0 parcourt uniquement les clés non nulles de l'index (Index cond, au lieu d'un filtre à posteriori). Dans ce cas précis, le gain est très net.

Utilisation d'index pour générer des statistiques à la volée

Avant de commencer à expliquer la nouveauté, un petit rappel sur les histogrammes: PostgreSQL, comme d'autres moteurs de bases de données, utilise un optimiseur statistique. Cela signifie qu'au moment de la planification d'une requête il a (ou devrait) avoir une idée correcte de ce que chaque étape de la requête va lui ramener, en termes de nombres d'enregistrements. Pour cela, il utilise des statistiques, comme le nombre d'enregistrements de la table approximatif, sa taille, la corrélation physique entre valeurs voisines dans la table, les valeurs les plus fréquentes, et les histogrammes, qui permettent d'évaluer assez précisément le nombre d'enregistrements ramenés par une clause WHERE sur une colonne, suivant la valeur ou la plage demandée sur la clause WHERE.

Il arrive que les statistiques soient rapidement périmées, et donc posent problème, pour certains ordres SQL. Par exemple, une table de trace dans laquelle on insèrerait des enregistrements horodatés, et sur laquelle on voudrait presque toujours sélectionner les enregistrements des 5 dernières minutes.

Dans ce cas, il était impossible avant la 9.0 d'avoir des statistiques à jour. Maintenant, quand PostgreSQL détecte qu'une requête demande un «range scan» sur une valeur supérieure à la dernière valeur de l'histogramme (ou inférieure à la première valeur), c'est à dire la plus grande valeur connue au dernier calcul de statistiques, et que la colonne est indexée, il récupère le max (ou le min si c'est la première valeur) de cette colonne en interrogeant l'index AVANT d'exécuter la requête, afin d'obtenir des statistiques plus réalistes. Comme il utilise un index pour cela, il faut qu'un index existe, bien sûr.

Voici un exemple. La colonne "a" de la table test a déjà été remplie avec de nombreuses dates, antérieures. Elle a donc des statistiques à jour, avec des histogrammes lui donnant la répartition des valeurs de a.

Il est 13:37, et je n'ai encore rien inséré dans la table de date supérieure à 13:37.

marc=# EXPLAIN ANALYZE select * from test where a > '2010-06-03 13:37:00';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Index Scan using idxtsta on test (cost=0.00..8.30 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=1)
Index Cond: (a > '2010-06-03 13:37:00'::timestamp without time zone)
Total runtime: 0.027 ms
(3 rows)
Tout est donc normal. La borne supérieure de mon histogramme est 2010-06-03 13:36:16.830007 (l'information se trouve dans pg_stats). Il n'a aucun moyen d'évaluer le nombre d'enregistrements supérieurs à 13:37, et en 8.4, il aurait continué à estimer '1' tant qu'un analyze n'aura pas été passé.
marc=# DO LANGUAGE plpgsql
$$
DECLARE
i int;
BEGIN
FOR i IN 1..10000 LOOP
   INSERT INTO test VALUES (clock_timestamp());
END LOOP;
END
$$
;
DO

(Décidément, j'aime bien les DO).

Nous venons d'insérer 10000 dates supérieures à 13:37.

marc=# EXPLAIN ANALYZE SELECT * FROM test WHERE a > '2010-06-03 13:37:00';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using idxtsta on test (cost=0.00..43.98 rows=1125 width=8) (actual time=0.012..13.590 rows=10000 loops=1)
Index Cond: (a > '2010-06-03 13:37:00'::timestamp without time zone)
Total runtime: 23.567 ms
(3 rows)

Le nombre d'enregistrements estimé n'est pas à 0 ou 1. Et pourtant les statistiques ne sont pas à jour :

marc=# SELECT last_autoanalyze FROM pg_stat_user_tables WHERE relname = 'test';
last_autoanalyze
-------------------------------
2010-06-03 13:36:21.553477+02
(1 row)

Dans cet exemple, nous avons tout de même une erreur d'un facteur 10. Ce n'est pas si mal: sans cette optimisation, l'erreur aurait été d'un facteur 10 000. En tout cas, une erreur d'un facteur 10 nous donne de plus fortes chances de choisir un plan intelligent.

seq_page_cost/random_page_cost par tablespace

marc=# ALTER TABLESPACE pg_default SET ( random_page_cost = 10, seq_page_cost=5);
ALTER TABLESPACE

Nous venons de modifier random_page_cost et seq_page_cost pour tous les objets du tablespace pg_default. Quel peut être le cas d'utilisation ?

C'est pour le cas où vous avez des tablespaces de performances différentes : par exemple, vous avez quelques données essentielles sur un disque SSD, ou bien des données d'historique sur une vieille baie moins performante que la baie flambant neuf que vous avez décidé d'utiliser pour les données actives. Cela vous permet de signaler à PostgreSQL que tous vos tablespaces ne sont pas forcément équivalents en termes de performance. Cela ne s'applique, bien sûr, que sur de très grosses bases.

Permettre de forcer le nombre de valeurs distinctes d'une colonne

Ceci permet de forcer le nombre de valeurs différentes d'une colonne. Ce n'est pas à utiliser à la légère, mais uniquement quand l'ANALYZE sur la table n'arrive pas à obtenir une valeur raisonnable.

Voici comment procéder :

marc=# ALTER TABLE test ALTER COLUMN a SET (n_distinct = 2);
ALTER TABLE

Il faut repasser un ANALYZE pour que la modification soit prise en compte :

marc=# ANALYZE test;
ANALYZE

Essayons maintenant :

marc=# EXPLAIN SELECT distinct * from test;
QUERY PLAN
------------------------------------------------------------------
HashAggregate (cost=6263.00..6263.02 rows=2 width=8)
-> Seq Scan on test (cost=0.00..5338.00 rows=370000 width=8)
(2 rows)

C'est l'exemple même de ce qu'il ne faut pas faire : j'ai bien 370 000 valeurs distinctes dans ma table. Maintenant mes plans d'exécution seront très mauvais.

Si la valeur n_distinct est positive, il s'agit du nombre de valeurs distinctes. Si la valeure est négative (entre 0 et -1), il s'agit du coefficient multiplicateur par rapport au nombre d'enregistrements estimés de la table : par exemple -0.2 signifie qu'il y a 1 enregistrement distinct pour 5 enregistrements dans la table. 0 ramène le comportement à celui par défaut (c'est ANALYZE qui effectue la mesure).

Ne touchez à ceci que si vous êtes absolument sûr d'avoir correctement diagnostiqué votre problème. Sinon, vous pouvez être sûr d'empirer les performances.


De nombreuses autres fonctionnalités de la base ont été améliorées. En voici quelques exemples parmi les plus significatifs :

VACUUM FULL amélioré

La commande VACUUM FULL était jusque ici très lente. Cette commande permet de récupérer l'espace perdu dans une table, principalement quand la commande VACUUM n'a pas été passée très régulièrement. Ceci était du à son mode de fonctionnement : les enregistrements étaient lus et déplacés un par un d'un bloc de la table vers un bloc plus proche du début de la table. Une fois que la fin de la table était vide, l'enveloppe était réduite à sa taille minimale.

Le problème était donc que ce mécanisme était très inefficace : le déplacement des enregistrements un à un entraine beaucoup d'entrées/sorties aléatoires (non contigues). Par ailleurs, durant cette réorganisation, les index doivent être maintenus, ce qui rend l'opération encore plus couteuse, et fait qu'à la fin d'un vacuum full, les index sont fortement désorganisés. Il est d'ailleurs conseillé de réindexer une table juste après y avoir appliqué un VACUUM FULL.

La commande VACUUM FULL, dans cette nouvelle version, crée une nouvelle table à partir de la table actuelle, en y recopiant tous les enregistrements de façon séquentielle. Une fois tous les enregistrements recopiés, les index sont recréés, et l'ancienne table détruite.

Cette méthode présente l'avantage d'être très largement plus rapide. Toutefois, VACUUM FULL demande toujours un verrou complet sur la table durant le temps de son exécution. Le seul défaut de cette méthode par rapport à l'ancienne, c'est que pendant le temps de son exécution, le nouveau VACUUM FULL peut consommer jusqu'à 2 fois l'espace disque de la table, puisqu'il en crée une nouvelle version.

Mesurons maintenant le temps d'exécution suivant les deux méthodes. Dans les deux cas, on prépare le jeu de test comme suit (en 8.4 et en 9.0:)

marc=# CREATE TABLE test (a int);
CREATE TABLE
marc=# CREATE INDEX idxtsta on test (a);
CREATE INDEX
marc=# INSERT INTO test SELECT generate_series(1,1000000);
INSERT 0 1000000
marc=# DELETE FROM test where a%3=0;
DELETE 333333
marc=# VACUUM test;
VACUUM
En 8.4 :
marc=# \timing
Timing is on.
marc=# VACUUM FULL test;
VACUUM
Time: 6306,603 ms
marc=# REINDEX TABLE test ;
REINDEX
Time: 1799,998 ms

Soit environ 8 secondes.

En 9.0 :
marc=# \timing
Timing is on.
marc=# VACUUM FULL test;
VACUUM
Time: 2563,467 ms

Ça ne veut toujours pas dire que VACUUM FULL est une bonne idée en production. Si vous en avez besoin, c'est probablement que votre politique de VACUUM n'est pas appropriée.

64 bits sous windows.

Il y a maintenant une version 64 bits native pour Windows. Pour l'instant aucune mesure de performance n'a été effectuée (à ma connaissance) pour en connaître les gains : peut on maintenant dépasser la limite de shared_buffers aux alentours de 500Mo sous Windows sans dégradations de performances ?

PL/pgSQL par défaut

Vous n'aurez plus à ajouter le langage plpgsql dans chaque base où vous en avez besoin car il est installé par défaut.

Beaucoup d'améliorations sur les langages PL.

Beaucoup de langages ont vu leur support grandement amélioré, PLPerl par exemple. Consultez les release notes si vous voulez davantage de détails, les modifications étant nombreuses.

Mot clé ALIAS

Nous pouvons maintenant utiliser le mot clé ALIAS. Comme son nom l'indique, il permet de créer des alias de variables.

La syntaxe est « nouveau_nom ALIAS FOR ancien_nom ». Cela se place dans la section DECLARE d'un code PL/pgSQL.

C'est utilisable dans deux cas principalement :

  • pour donner des noms aux variables d'une fonction PL:
   monparam ALIAS FOR $0
  • pour renommer des variables qui pourraient être en conflit. Dans un trigger par exemple:
   nouvelle_valeur ALIAS FOR new
(on aurait risqué le conflit avec la variable new dans la fonction trigger).

Passage de message dans NOTIFY/pg_notify

On peut donc passer des messages dans NOTIFY. Voici la méthode:

  • On s'abonne dans la session 1 à la file d'attente « messagerie_instantanee »
    Session 1 :
    marc=# LISTEN messagerie_instantanee;
    LISTEN
  • On envoie une notification dans la file d'attente « messagerie_instantanee » d'une autre session
    Session 2 :
    marc=# NOTIFY messagerie_instantanee, 'Vous avez reçu un popup';
    NOTIFY
  • On vérifie le contenu de la file d'attente
    Session 1 :
    LISTEN
    Asynchronous notification "messagerie_instantanee" with payload "Vous avez reçu un popup" received from server process with PID 5943.
  • On peut donc maintenant associer des messages (payloads) aux notifications, ce qui rend le mécanisme beaucoup plus puissant.

Signalons aussi la présence d'une nouvelle fonction pg_notify.

Le code de la session 2 peut donc être:

SELECT pg_notify('messagerie_instantanee','Vous avez reçu un popup');

Cela peut simplifier l'écriture, dans le cas d'un programme devant piloter de nombreuses files de messages.

get_bit et set_bit pour les bit strings

En voici un exemple très synthétique. Cet outil permet de manipuler les bits indépendamment dans un bit().


marc=# SELECT set_bit('1111'::bit(4),2,0);
set_bit
---------
1101
(1 row)


marc=# SELECT get_bit('1101'::bit(4),2);
get_bit
---------
0
(1 row)

application_name pour pg_stat_activity

Dans la session de supervision :

marc=# SELECT * from pg_stat_activity where procpid= 5991;

datid | datname | procpid | usesysid | usename | application_name | client_addr | client_port |         backend_start         | xact_start | query_start | waiting | current_query
------+---------+---------+----------+---------+------------------+-------------+-------------+-------------------------------+------------+-------------+---------+----------------
16384 | marc | 5991 | 10 | marc | psql | | -1 | 2010-05-16 13:48:10.154113+02 | | | f | <IDLE>

(1 row)

Dans la session '5991' :

marc=# SET application_name TO 'mon_appli'; SET

Dans la session de supervision :

marc=# SELECT * from pg_stat_activity where procpid= 5991;

datid | datname | procpid | usesysid | usename | application_name | client_addr | client_port |         backend_start         | xact_start |          query_start          | waiting | current_query
------+---------+---------+----------+---------+------------------+-------------+-------------+-------------------------------+------------+-------------+---------+----------------
16384 | marc | 5991 | 10 | marc | mon_appli | | -1 | 2010-05-16 13:48:10.154113+02 | | 2010-05-16 13:49:13.107413+02 | f | <IDLE>

(1 row)

À vous de le positionner correctement dans votre application, ou vos sessions. Votre DBA vous dira merci, sachant enfin qui lance quoi sur son serveur facilement.

Configuration par base de données+rôle

marc=# ALTER ROLE marc IN database marc set log_statement to 'all';
ALTER ROLE

Pour savoir qui a quelles modifications de variables dans quels rôles de quelles bases de données, il y a une nouvelle commande psql :

marc=# \drds
         List of settings
role | database | settings
-----+----------+-----------------
marc | marc | log_statement=all
(1 row)

Il y a donc eu une modification du catalogue pour gérer cette nouvelle fonctionnalité :

Table "pg_catalog.pg_db_role_setting"

  Column    |  Type  | Modifier
------------+--------+----------
setdatabase | oid | not null
setrole | oid | not null
setconfig | text |

Tracer les parametres modifiés lors d'un rechargement du postgresql.conf

Voici un exemple, lors de la modification du paramètre log_line_prefix :
LOG:  received SIGHUP, reloading configuration files
<%> LOG:  parameter "log_line_prefix" changed to "<%u%%%d> "

Nouvelles options de frame dans les fonctions de fenêtrage

Si vous ne vous connaissez pas les fonctions de fenêtrage, lisez la présentation de la 8.4 ici : Nouveautés PostgreSQL 8.4

Il y a donc des nouveautés dans le paramétrage du 'frame' des fonctions de fenêtrage. Soit la table suivante (faute de mieux…)

marc=# SELECT * FROM salaire ;
entite | personne  | salaire | date_embauche
-------+-----------+---------+---------------
R&D    | marc      |  700.00 | 2010-02-15
Compta | etienne   |  800.00 | 2010-05-01
R&D    | maria     |  700.00 | 2009-01-01
R&D    | kevin     |  500.00 | 2009-05-01
R&D    | jean      | 1000.00 | 2008-07-01
R&D    | tom       | 1100.00 | 2005-01-01
Compta | stephanie |  850.00 | 2006-01-01

Voici un exemple de fonctions de fenêtrage, sans préciser le frame.

marc=# SELECT entite, personne, salaire, date_embauche, avg(salaire) OVER (PARTITION BY entite ORDER BY date_embauche) FROM salaire;
entite | personne  | salaire | date_embauche |          avg         
-------+-----------+---------+---------------+-----------------------
Compta | stephanie |  850.00 | 2006-01-01    |  850.0000000000000000
Compta | etienne   |  800.00 | 2010-05-01    |  825.0000000000000000
R&D    | tom       | 1100.00 | 2005-01-01    | 1100.0000000000000000
R&D    | jean      | 1000.00 | 2008-07-01    | 1050.0000000000000000
R&D    | maria     |  700.00 | 2009-01-01    |  933.3333333333333333
R&D    | kevin     |  500.00 | 2009-05-01    |  825.0000000000000000
R&D    | marc      |  700.00 | 2010-02-15    |  800.0000000000000000

Le frame est le groupe d'enregistrements sur lequel la fonctions de fenêtrage est appliquée. Évidemment, si on ne précise pas la frame, il met une valeur par défaut. Voici la même requête, écrite avec une frame explicite.

marc=# SELECT entite, personne, salaire, date_embauche, avg(salaire) OVER (PARTITION BY entite ORDER BY date_embauche RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM salaire;
entite | personne | salaire | date_embauche | avg
-------+-----------+---------+---------------+-----------------------
Compta | stephanie | 850.00 | 2006-01-01 | 850.0000000000000000
Compta | etienne | 800.00 | 2010-05-01 | 825.0000000000000000
R&D | tom | 1100.00 | 2005-01-01 | 1100.0000000000000000
R&D | jean | 1000.00 | 2008-07-01 | 1050.0000000000000000
R&D | maria | 700.00 | 2009-01-01 | 933.3333333333333333
R&D | kevin | 500.00 | 2009-05-01 | 825.0000000000000000
R&D | marc | 700.00 | 2010-02-15 | 800.0000000000000000

La frame est donc par 'range', entre le début du range et l'enregistrement courant (pas vraiment l'enregistrement courant en fait, mais laissons de côté les subtilités, allez lire la doc si vous voulez en savoir plus). On constate que la fonction de moyenne (avg) est appliquée entre le premier des enregistrements du frame (les enregistrements de la même entité) et l'enregistrement courant.

Première nouveauté : en 9.0, la frame peut se calculer entre l'enregistrement courant et la fin du groupe (au lieu d'entre le début du groupe et l'enregistrement courant):

marc=# SELECT entite, personne, salaire, date_embauche, avg(salaire)  OVER (PARTITION BY entite ORDER BY date_embauche RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM salaire;
entite | personne | salaire | date_embauche | avg
-------+-----------+---------+---------------+----------------------
Compta | stephanie | 850.00 | 2006-01-01 | 825.0000000000000000
Compta | etienne | 800.00 | 2010-05-01 | 800.0000000000000000
R&D | tom | 1100.00 | 2005-01-01 | 800.0000000000000000
R&D | jean | 1000.00 | 2008-07-01 | 725.0000000000000000
R&D | maria | 700.00 | 2009-01-01 | 633.3333333333333333
R&D | kevin | 500.00 | 2009-05-01 | 600.0000000000000000
R&D | marc | 700.00 | 2010-02-15 | 700.0000000000000000

Deuxième nouveauté, on peut calculer des frames sur les n enregistrements précédents et n enregistrements suivants. Aucun intérêt avec ce jeu de données, mais il faut bien que je vous donne un exemple :

marc=# SELECT entite, personne, salaire, date_embauche, avg(salaire)  OVER (PARTITION BY entite ORDER BY date_embauche RANGE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM salaire;
entite | personne | salaire | date_embauche | avg
-------+-----------+---------+---------------+-----------------------
Compta | stephanie | 850.00 | 2006-01-01 | 825.0000000000000000
Compta | etienne | 800.00 | 2010-05-01 | 825.0000000000000000
R&D | tom | 1100.00 | 2005-01-01 | 1050.0000000000000000
R&D | jean | 1000.00 | 2008-07-01 | 933.3333333333333333
R&D | maria | 700.00 | 2009-01-01 | 733.3333333333333333
R&D | kevin | 500.00 | 2009-05-01 | 633.3333333333333333
R&D | marc | 700.00 | 2010-02-15 | 600.0000000000000000

On reste bien sûr sur le groupe (voir l'enregistrement de tom par exemple, l'enregistrement d'etienne ne rentre pas dans le calcul de sa moyenne).

Si on voulait la même requête que précédemment, mais avec des moyennes sur 3 enregistrements glissants, sans réinitialiser à chaque entité (toujours aucun intérêt pratique dans l'exemple).

marc=# SELECT entite, personne, salaire, date_embauche, avg(salaire) OVER (ORDER BY entite, date_embauche ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM salaire;
entite | personne | salaire | date_embauche | avg
--------+-----------+---------+---------------+----------------------
Compta | stephanie | 850.00 | 2006-01-01 | 825.0000000000000000
Compta | etienne | 800.00 | 2010-05-01 | 916.6666666666666667
R&D | tom | 1100.00 | 2005-01-01 | 966.6666666666666667
R&D | jean | 1000.00 | 2008-07-01 | 933.3333333333333333
R&D | maria | 700.00 | 2009-01-01 | 733.3333333333333333
R&D | kevin | 500.00 | 2009-05-01 | 633.3333333333333333
R&D | marc | 700.00 | 2010-02-15 | 600.0000000000000000

Bref, un outil à maîtriser d'urgence, si ce n'est pas déjà le cas (même si je n'ai pas été capable de vous donner un exemple décent).

Tris dans les aggrégations

Celle-ci est un peu subtile : le résultat de certaines fonctions d'aggrégation dépend de l'ordre dans lequel on leur fournit les données.

Il ne s'agit évidemment pas de count, avg, mais plutôt de array_agg, xml_agg, string_agg…

Ce qui va me permettre de vous présenter deux nouvelles fonctionnalités d'un coup, string_agg étant une nouveauté de la 9.0.

Reprenons la table salaire. Je voudrais la liste des employés, concaténés dans un seul champ, par entité. C'est pour stocker dans mon tableur.

marc=# SELECT entite,string_agg(personne,', ') FROM salaire GROUP BY entite;
entite | string_agg
-------+-------------------------------
Compta | etienne, stephanie
R&D | marc, maria, kevin, jean, tom

C'est déjà bien. Mais j'aimerais bien les avoir par ordre alphabétique, parce que je ne sais pas écrire de macro dans mon tableur pour retrier les données.

marc=# SELECT entite,string_agg(personne,', ' ORDER BY personne) FROM salaire GROUP BY entite;
entite | string_agg
--------+-------------------------------
Compta | etienne, stephanie
R&D | jean, kevin, marc, maria, tom

Il suffit donc de rajouter une clause de tri à l'intérieur de la fonction d'agrégat, sans virgule à la fin.

Amélioration des erreurs sur contrainte : message plus clair

En 8.4:

marc=# INSERT INTO test VALUES (1);
ERROR: duplicate key value violates unique constraint "test_a_key"

En 9.0:

marc=# INSERT INTO test VALUES (1);
ERROR: duplicate key value violates unique constraint "test_a_key"
DETAIL: Key (a)=(1) already exists.

Cela devrait aider à trouver les causes des violations de contrainte.

Explain buffers/statistiques sur les hash, xml, json, yaml, nouvelle syntaxe optionnelle explain

Voici un EXPLAIN ANALYZE classique :

marc=# EXPLAIN ANALYZE SELECT a, sum(c) FROM pere JOIN fils ON (pere.a = fils.b) WHERE b BETWEEN 1000 AND 300000 GROUP BY a;                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=905.48..905.86 rows=31 width=8) (actual time=0.444..0.453 rows=6 loops=1)
-> Nested Loop (cost=10.70..905.32 rows=31 width=8) (actual time=0.104..0.423 rows=6 loops=1)
-> Bitmap Heap Scan on fils (cost=10.70..295.78 rows=31 width=8) (actual time=0.040..0.154 rows=30 loops=1)
Recheck Cond: ((b >= 1000) AND (b <= 300000))
-> Bitmap Index Scan on fils_pkey (cost=0.00..10.69 rows=31 width=0) (actual time=0.023..0.023 rows=30 loops=1)
Index Cond: ((b >= 1000) AND (b <= 300000))
-> Index Scan using pere_pkey on pere (cost=0.00..19.65 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=30)
Index Cond: (pere.a = fils.b)
Total runtime: 0.560 ms
(9 rows)

Si vous voulez avoir accès aux nouvelles informations, il faut opter pour la nouvelle syntaxe :

EXPLAIN [ ( { ANALYZE boolean | VERBOSE boolean | COSTS boolean | BUFFERS boolean | FORMAT { TEXT | XML | JSON | YAML } } [, ...] ) ] instruction

Par exemple :

marc=# EXPLAIN (ANALYZE true, VERBOSE true, BUFFERS true) SELECT a, sum(c) FROM pere JOIN fils ON (pere.a = fils.b) WHERE b BETWEEN 1000 AND 300000 GROUP BY a;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=905.48..905.86 rows=31 width=8) (actual time=1.326..1.336 rows=6 loops=1)
Output: pere.a, sum(fils.c)
Buffers: shared hit=58 read=40
-> Nested Loop (cost=10.70..905.32 rows=31 width=8) (actual time=0.278..1.288 rows=6 loops=1)
Output: pere.a, fils.c
Buffers: shared hit=58 read=40
-> Bitmap Heap Scan on public.fils (cost=10.70..295.78 rows=31 width=8) (actual time=0.073..0.737 rows=30 loops=1)
Output: fils.b, fils.c
Recheck Cond: ((fils.b >= 1000) AND (fils.b <= 300000))
Buffers: shared hit=4 read=28
-> Bitmap Index Scan on fils_pkey (cost=0.00..10.69 rows=31 width=0) (actual time=0.030..0.030 rows=30 loops=1)
Index Cond: ((fils.b >= 1000) AND (fils.b <= 300000))
Buffers: shared hit=3
-> Index Scan using pere_pkey on public.pere (cost=0.00..19.65 rows=1 width=4) (actual time=0.013..0.014 rows=0 loops=30)
Output: pere.a
Index Cond: (pere.a = fils.b)
Buffers: shared hit=54 read=12
Total runtime: 1.526 ms
(18 rows)
VERBOSE apporte les lignes 'Output' (l'option existait déjà en 8.4).

BUFFERS indique les opérations sur les buffers (les entrées sorties de la requête): hit correspond aux données lues en cache, read aux données demandées au système d'exploitation. Ici, peu de données étaient en cache.

Vous pouvez aussi demander une sortie dans un autre format que texte. Pour un utilisateur, cela n'a aucune importance. Pour les développeurs d'interfaces graphiques présentant le résultat d'explain, cela permettra de faire l'économie d'un analyseur sur le texte du EXPLAIN, et des bugs qui vont avec.

On peut aussi désactiver l'affichage des coûts avec COSTS false.

Dictionnaire de filtrage (unaccent)

Il est possible maintenant de paramétrer des dictionnaires de filtrage. On parle bien sûr des dictionnaires du Full Text Search.

Le but de ces dictionnaires de filtrage est d'appliquer un premier filtrage sur les mots avant de les indexer. Le module présenté ci-dessous (unaccent) est l'illustration de ce mécanisme. Le filtrage peut consister en la suppression de mots ou en leur modification.

Unaccent ne supprime pas les mots, il supprime les accents (tous les signes diacritiques en fait), en remplaçant les caractères accentués par leur version sans accent. Unaccent est un module contrib.

Pour l'installer, comme pour toutes les contribs, psql mabase < chemin_contribs/unaccent.sql.

Nous allons à peu près suivre la documentation d'unaccent, les auteurs ayant eu la gentillesse de donner leurs exemples en français.

Nous créons un nouveau dictionnaire fr (pour ne pas polluer le dictionnaire french 'standard') :

marc=# CREATE TEXT SEARCH CONFIGURATION fr ( COPY = french );
CREATE TEXT SEARCH CONFIGURATION

Nous modifions le paramétrage de 'fr' pour les lexemes de type mot, en lui demandant de les faire passer par unaccent et french_stem (au lieu de seulement french_stem)

marc=# ALTER TEXT SEARCH CONFIGURATION fr
ALTER MAPPING FOR hword, hword_part, word
WITH unaccent, french_stem;
ALTER TEXT SEARCH CONFIGURATION
SELECT to_tsvector('fr','Hôtels de la Mer');
to_tsvector
-------------------
'hotel':1 'mer':4
(1 row)

marc=# SELECT to_tsvector('fr','Hôtel de la Mer') @@ to_tsquery('fr','Hotels');
?column?
----------
t
(1 row)
Cela vous permet donc, sans changer une ligne de code, et en gardant les caractères accentués, de rechercher maintenant sans accent.

vacuumdb --analyze-only

Comme son nom l'indique, on peut maintenant utiliser vacuumdb pour passer des analyses uniquement. Cela peut être pratique dans une crontab.

Amélioration du module contrib hstore

Ce contrib, déjà très pratique, devient encore plus puissant :

  • La limite de taille sur les clés et valeurs a été supprimée.
  • Il est maintenant possible d'utiliser GROUP BY et DISTINCT
  • De nombreux opérateurs et fonctions ont été ajoutés

Un exemple serait trop long, tellement ce module est riche. Lisez la documentation sans perdre de temps !

Texte requête dans auto_explain

Le module contrib auto_explain affichera maintenant le code de la requête en même temps que son plan, ce qui devrait en augmenter la lisibilité.

Compteurs sur buffers dans pg_stat_statements

Ce module contrib, déjà très utile, vient de rajouter des compteurs. Pour rappel, son intérêt est de stocker des statistiques sur les requêtes exécutées par le moteur. Jusque là, il donnait la requête, le nombre d'exécutions, son temps cumulé et le nombre d'enregistrements cumulés. Maintenant, il collecte aussi des informations sur les entrées sorties (en cache, et réelles).

marc=# SELECT * from pg_stat_statements order by total_time desc limit 2;
-[ RECORD 1 ]-------+---------------------
userid | 10
dbid | 16485
query | SELECT * from fils ;
calls | 2
total_time | 0.491229
rows | 420000
shared_blks_hit | 61
shared_blks_read | 2251
shared_blks_written | 0
local_blks_hit | 0
local_blks_read | 0
local_blks_written | 0
temp_blks_read | 0
temp_blks_written | 0
-[ RECORD 2 ]-------+---------------------
userid | 10
dbid | 16485
query | SELECT * from pere;
calls | 2
total_time | 0.141445
rows | 200000
shared_blks_hit | 443
shared_blks_read | 443
shared_blks_written | 0
local_blks_hit | 0
local_blks_read | 0
local_blks_written | 0
temp_blks_read | 0
temp_blks_written | 0

On peut donc, une fois ce contrib installé, répondre aux questions suivantes :

  • Quelle est la requête la plus gourmande en temps d'exécution cumulé ?
  • Quelle est la requête qui génère le plus d'entrées sorties ? (attention, les données peuvent être tout de même dans le cache système)
  • Quelles requêtes utilisent principalement le cache (et ne gagneront donc pas à le voir augmenté)
  • Qui effectue beaucoup de mises à jour de bloc ?
  • Qui génère des tris ?

'local' et 'temp' correspondent aux buffers et entrées des tables temporaires et autres opérations locales (tris, hachages) à un backend.

passwordcheck

Ce module contrib permet de vérifier les mots de passe, et d'empêcher les plus mauvais de rentrer. Après l'avoir installé comme décrit dans la documentation, voici le résultat :

marc=# ALTER USER marc password 'marc12';
<marc%marc> ERROR: password is too short
<marc%marc> STATEMENT: ALTER USER marc password 'marc12';
ERROR: password is too short
marc=# ALTER USER marc password 'marc123456';
<marc%marc> ERROR: password must not contain user name
<marc%marc> STATEMENT: ALTER USER marc password 'marc123456';
ERROR: password must not contain user name

Ce module souffre de limitations, principalement dues au fait que PostgreSQL permet l'envoi d'un mot de passe déjà encrypté à la base au moment de la déclaration, ce qui l'empêche de le vérifier correctement. Néanmoins, c'est une avancée dans la bonne direction.

Par ailleurs, le code du module contrib est bien documenté, ce qui permet de l'adapter à vos besoins (entre autres, il est très facile d'y activer la cracklib, afin d'effectuer des contrôles plus complexes).

marc.cousin@dalibo.com

jeudi 9 juillet 2009

ma fonctionnalité 8.4 préférée : pg_stat_statement

Dans ce billet, je vais essayer de faire la publicité de ma fonctionnalité préférée de la version 8.4.

Comme toujours, il y a une foule de nouveautés, toutes très intéressantes, et il est difficile d'en déclarer une comme étant la meilleure. Toutefois, j'ai un faible pour pg_stat_statement, et je vais essayer de vous expliquer pourquoi

Pour superviser l'activité sur un serveur PostgreSQL, et trouver les requêtes SQL qui dégradent les performances, jusqu'à aujourd'hui, il n'y avait à ma connaissance qu'une seule solution : on active les traces des ordres SQL et de leur durée (log_statement à all, log_duration à on). Ensuite, on récupère la log de postgresql et on la fait avaler à pgfouine ou pqa, on obtient un rapport et on va voir les développeurs (ou on rajoute un index dans son coin ...) (les paramètres de log sont documentés ici : http://docs.postgresql.fr/8.4/runtime-config-logging.html)

Cette méthode fonctionne, mais a des gros défauts :
  • On trace TOUS les ordres SQL dans la log, ce qui fait qu'on a une log gigantesque assez rapidement (ça peut monter rapidement à plusieurs gigas sur une base très active)
  • C'est gourmand en ressources, parce qu'il faut formater les traces, les écrire dans la log, découper le message en plusieurs morceaux si il est plus grand qu'une trame syslog et qu'on a décidé de tracer en syslog. Et le surcoût est le même pour une requête de 20 µs et une requête de 2h.
  • On n'a que la durée des requêtes


On peut mitiger l'impact de la fonction de log de plusieurs façons :
  • On ne trace de que les ordres SQL un peu longs
  • On ne trace que sur de courtes périodes d'activités

Dans le premier cas, le problème est qu'on risque de laisser échapper des requêtes unitaires très courtes exécutées des millions de fois. Je l'ai constaté assez souvent dans des développements objets avec de (trop?) nombreux niveaux d'abstraction. On peut facilement se retrouver avec des requêtes insignifiantes exécutées plusieurs milliers de fois à la minute (l'infâme SELECT * FROM DUAL sous Oracle par exemple d'un développeur qui veut vérifier que sa session marche bien avant de lancer un autre ordre, et qui l'a mis à chaque fois qu'il récupère une session d'un pool, au cas où ... ou bien les gens qui réintérrogent un référentiel en permanence). Bref, vaut-il mieux chasser les 10000 appels inutiles à la minute à une requête qui dure 10ms, ou améliorer la requête lancée une fois par minute qui dure 1 seconde? Ça dépend des cas, mais il est  préférable que l'audit de performance révèle les 2 (ce qui est très difficile si on ne trace pas tout...)

Dans le second cas, c'est garanti, on va rater une période intéressante. Et on ne pourra pas faire d'analyse à posteriori.

C'est ici qu'arrive ma fonctionnalité préférée : "et si, au lieu de tout tracer dans une log pour ensuite devoir reparser et réanalyser tout, on avait une zone de mémoire partagée dans laquelle les processus pouvaient mettre à jour des stats cumulées sur chaque requête ?".

Les avantages de cette méthode sont :
  • C'est très performant. Je n'ai pas réussi à en mesurer l'impact, et les benchs fait par le développeur montrent un impact négligeable
  • On a un peu plus d'informations qu'avec la log (le nombre d'enregistrements cumulés ramenés par la requête)
C'est donc un mécanisme qu'on peut avoir activé en permanence, consultable par une simple requête.

Je n'ai pas de base de production sous la main en ce moment, mais voici un exemple :
"Donne moi les 2 requêtes les plus gourmandes en temps d'exécution cumulé depuis le dernier reset des stats" :
test=# SELECT * from pg_stat_statements order by total_time desc limit 2;
-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
userid     | 10
dbid       | 16384
query      | INSERT INTO test SELECT * from test;
calls      | 12
total_time | 0.036099
rows       | 20475
-[ RECORD 2 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
userid     | 10
dbid       | 16384
query      | SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE c.relkind IN ('r', 'S', 'v') AND substring(pg_catalog.quote_ident(c.relname),1,6)='pg_sta' AND pg_catalog.pg_table_is_visible(c.oid)
           : UNION
           : SELECT pg_catalog.quote_ident(n.nspname) || '.' FROM pg_catalog.pg_namespace n WHERE substring(pg_catalog.quote_ident(n.nspname) || '.',1,6)='pg_sta' AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,6) = substring('pg_sta',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) > 1
           : UNION
           : SELECT pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind IN ('r', 'S', 'v') AND substring(pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname),1,6)='pg_sta' AND substring(pg_catalog.quote_ident(n.nspname) || '.',1,6) = substring('pg_sta',1,pg_catalog.length(pg_catalog.quote_ident(n.nspname))+1) AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,6) = substring('pg_sta',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) = 1
           : LIMIT 1000
calls      | 4
total_time | 0.006295
rows       | 87

Évidemment, sur une base de test, avec une table test, ce n'est pas très intéressant. Avec une vraie base de production c'est autre chose...

On peut aussi utiliser cette vue pour faire des snapshots de l'activité toutes les quelques minutes par exemple :

Pour le premier snapshot :
CREATE TABLE snapshot as select now(),* from pg_stat_statements;
Pour les suivants :
INSERT INTO snapshot select now(),* from pg_stat_statements;


On peut aussi imaginer remettre à zéro les compteurs (suivant ce qu'on veut faire des snapshots, cumulés ou indépendants) avec pg_stat_statements_reset.Avec cela il est possible de 'retrouver' le ou les ordres SQL qui ont fait 'ramer' l'application à une heure donnée (ou dédouaner la base de données...)

Les seuls points qui me chagrinent encore sur pg_stat_statement sont qu'il s'agit d'une contrib (c'est normal, mais ça veut dire que cette fonctionnalité sera moins exposée qu'elle le mérite), et que quelques statistiques importantes supplémentaires pourraient servir : la quantité de données lues du cache, la quantité lue du disque, et la quantité écrite dans le cache. Pourquoi pas aussi être capable de séparer le temps de parsing du temps d'exécution (pour repérer les requêtes qui pourraient gagner à être préparées).

Bref, une fonctionnalité à tester d'urgence si ce n'est pas déjà fait...

La doc officielle : http://docs.postgresql.fr/8.4/pgstatstatements.html

Un dernier point : l'autre raison pour laquelle c'est ma fonctionnalité préférée, c'est que c'est une des fonctionnalités qui manquait à PostgreSQL pour faciliter un audit ou un suivi des performances par rapport à Oracle (la vue V$SQLAREA). C'est une fonctionnalité à laquelle on s'habitue assez facilement, c'était assez frustrant de ne pas l'avoir sur son SGBD favori.

samedi 20 juin 2009

Guide de démarrage rapide avec PostgreSQL

Pourquoi ce document?

J'ai commencé à développer sous PostgreSQL assez récemment après une longue expérience sous Oracle. La documentation générale de PostgreSQL est excellente, et très riche, mais j'avais besoin d'un document plus léger expliquant la procédure d'installation sur différents systèmes et comment démarrer (créer un cluster, configurer les connexions), ainsi que des informations sur ce qu'on pouvait faire avec PostgreSQL. Je ne l'ai pas trouvé. Après quelques mois d'utilisation, je me suis rendu compte que les problèmes des débutants étaient toujours les mêmes. Ainsi, j'ai compilé mes notes des débuts et ce que j'ai appris depuis dans ce document. Voici le résultat, en espérant qu'il vous aide à débuter et qu'il vous encourage à continuer avec PostgreSQL.

À qui s'adresse ce document?

Ce document a pour but de vous aider à installer PostgreSQL sous Windows ou sous Linux, et à commencer à développer.

Il est écrit pour vous faire gagner du temps dans vos premiers pas avec PostgreSQL, tout en vous expliquant les points importants afin que vous puissiez progresser par vous-même. Il s'adresse donc principalement aux développeurs d'applications, afin de leur permettre de découvrir ce puissant moteur sur une petite base de test, ou aux personnes qui débutent complètement avec PostgreSQL. Vous n'aurez pas besoin de connaissances système avancées pour suivre ce document.

Une fois que vous aurez terminé la lecture de ce document, vous pourrez continuer par la lecture de la documentation officielle pour apprendre à administrer PostgreSQL ou devenir un développeur aguerri. La dernière section de ce document vous donne les liens et références nécessaires pour continuer à progresser. Parfois les informations ne sont volontairement pas complètes, et lorsque la documentation de référence est plus claire et précise que ce qui aurait pu être fait ici, les liens sont fournis vers la documentation française.

Ce document a été écrit initialement pour la version 8.3, mais les principes sont les mêmes avec les versions 8.2 et 8.4 (voir le chapitre sur les versions).

Avertissement : ce document n'est en aucun cas un document sur le tuning de la base. Il n'est pas fait non plus pour vous apprendre à administrer une base de production.

Lire la suite...

mardi 16 juin 2009

Postgresql et l'authentification Apache

Postgresql et l'authentification Apache

Comment utiliser Postgresql pour l'authentification Apache

Apache permet de protéger, à l'aide d'un login et d'un mot de passe, l'accès à un répertoire ou à un fichier, par utilisateur ou groupe d'utilisateurs. Apache permet d'utiliser plusieurs méthodes pour gérer les droits des utilisateurs. L'administrateur Web a à sa disposition un script Apache, « htpasswd », et des fichiers de configuration de type texte. Il peut utiliser un annuaire LDAP ou une base de données Postgresql. L'utilisation de Postgresql permet de simplifier la programmation. La gestion des droits d'accès dans le site Web se fait en modifiant les tables, users et groups. Pour mettre en place cette technique il faut configurer Apache et Postgresql, cette documentation explique comment faire.

Préalable

Considérons que vous avez un serveur Web Apache et une base de données Postgresql fonctionnels. Si vous souhaitez modifier dynamiquement les droits Apache, il faut que vous sachiez vous connecter à Postgresql de Apache/Web/PHP, par exemple. Les fichiers de configuration donnés en exemple, sont ceux utilisés sur une distribution Linux RedHat Enterprise.

Installation et configuration

Vous avez donc installé et configuré un serveur web Apache et une base de données Postgresql.

Il vous reste à:

  • Ajouter les tables « users » et « groups » dans une base de données Postgresql.
  • Installer le module Apache mod_auth_pgsql.
  • Configurer le fichier Apache /etc/httpd/conf.d/auth_pgsql.conf
  • Ajouter des fichiers .htaccess dans les répertoires à protéger.

La suite décrit chacune de ces tâches.

Descriptif des tables users et groups Postgresql

Il faut ajouter à votre base de données Postgresql, les tables « users » et « groups », si vous conservez les noms standards des tables. Les tables users et groups doivent être conformes au format attendu par Apache. De plus les tables « users » et « groups » seront lu fréquemment par Apache, elles n'ont pas pour vocation de gérer des données des utilisateurs, hors le login, le mot de passe et l'appartenance à un ou plusieurs groupes d'utilisateurs. Il y a une clé étrangère dans la table « users » proposée. Elle permet des jointures avec une table qui gère les identités des utilisateurs.

Exemple de tables users et groups

CREATE TABLE users
(
user_name character varying(64) NOT NULL,
user_passwd character varying(32) NOT NULL,
"uIdIdentite" integer NOT NULL,
CONSTRAINT users_pkey PRIMARY KEY (user_name),
CONSTRAINT "idIdentite" FOREIGN KEY ("uIdIdentite")
REFERENCES "TIdentite" ("IdIdentite") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
ALTER TABLE users OWNER TO basecnl;

CREATE TABLE groups
(
user_name character varying(64) NOT NULL,
user_group character varying(20) NOT NULL,
CONSTRAINT groups_pkey PRIMARY KEY (user_name, user_group),
CONSTRAINT groups_user_name_fkey FOREIGN KEY (user_name)
REFERENCES users (user_name) MATCH FULL
ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (OIDS=FALSE);
ALTER TABLE groups OWNER TO basecnl;

La modification de users et groups, à l'aide de pgadmin3 par exemple, permet de gérer finement les droits d'accès au site Web. Vous pouvez aussi le faire dynamiquement par programmation.

La base de données et les tables qu'Apache utilise pour l'authentification, sont à définir dans /etc/httpd/conf.d/auth_pgsql.conf.

Installer le module Apache mod_auth_pgsql

La façon de faire dépend de la distribution linux, il faut installer mod_auth_pgsql.so.

Configuration de Apache 2 pour Postgresql

Nous utiliserons le module Apache2, mod_auth_pgsql, avec une authentification type: mod_auth_basic".

/usr/lib64/httpd/modules/mod_auth_pgsql.so

Exemple de fichier /etc/httpd/conf.d/auth_pgsql.conf

Dans le fichier /etc/httpd/conf.d/auth_pgsql.conf, vous reconnaîtrez le nom des tables et des champs que vous avez défini dans Postgresql.

LoadModule auth_pgsql_module modules/mod_auth_pgsql.so
<Directory /var/www>
   AuthName "PostgreSQL group authenticated zone"
   AuthType Basic
   Auth_PG_database basecnl
   Auth_PG_pwd_table users
   Auth_PG_uid_field user_name
   Auth_PG_pwd_field user_passwd
   Auth_PG_grp_table groups
   Auth_PG_grp_user_field user_name
   Auth_PG_grp_group_field user_group 
</Directory>

Configuration de Apache – .htaccess des répertoires Web

Les fichiers .htaccess sont des fichiers de configuration d'Apache, permettant de définir des règles dans un répertoire et dans tous ses sous-répertoires (qui n'ont pas de tel fichier à l'intérieur).

Exemple de fichier .htaccess

Ce fichier autorise l'accès au répertoire pour les utilisateurs du groupe « correspondant »

AuthName "Acces restreint aux correspondants "
AuthType basic
#AuthBasicAuthoritative off
Auth_PG_authoritative on
Auth_PG_host localhost
Auth_PG_port xxxx
Auth_PG_user xxxx
Auth_PG_database xxxx
#Auth_PG_pwd xxxxxxxx
Auth_PG_pwd_table "users"
Auth_PG_uid_field user_name
Auth_PG_pwd_field user_passwd
Auth_PG_grp_table "groups"
Auth_PG_grp_user_field user_name
Auth_PG_grp_group_field user_group
Auth_PG_grp_whereclause " and user_group = 'correspondant' "
Auth_PG_encrypted off
<LIMIT GET POST>
require valid-group
</LIMIT>

« AuthBasicAuthoritative off » et « Auth_PG_pwd xxxxxxxx » sont en commentaire. Dans certaines configurations, il faut enlever les commentaires.

Descriptif des champs

Auth_PG_host : Nom ou adresse IP du serveur Postgresql
Auth_PG_port : Port utilisé, par default 5432
Auth_PG_user : Nom de l'utilisateur Postgresql
Auth_PG_pwd : Mot de passe de l'utilisateur
Auth_PG_database : Nom de la base de données
Auth_PG_pwd_table : Nom de la table ou sont stockés les utilisateurs
Auth_PG_uid_field : Nom du champ qui contient le Login de l'utilisateur
Auth_PG_pwd_field : Nom du champ qui contient les mots de passe
Auth_PG_encrypted : Si off les mots de passe sont stockés en clair dans la table
Auth_PG_hash_type : Soit MD5 ou CRYPT

Conclusion

Postgresql est particulièrement performant pour gérer l'authentification Apache. Choisir Postgresql pour réaliser des sites Web dynamique, c'est choisir la garanti d'avoir un produit à la fois professionnel et libre.

Références

mod_auth_pgsql documentation officielle en anglais : http://www.giuseppetanzilli.it/mod_auth_pgsql2/
Apache france: http://forums.apachefrance.com/
Wikipedia les fichiers .htaccess Apache : http://fr.wikipedia.org/wiki/Htaccess
Postgresqlfr le site de la communauté francophone; http://www.postgresql.fr/

Pascal Brognez , le 16 juin 2009

mardi 28 avril 2009

Nouveautés PostgreSQL 8.4

Le but de cet article est de détailler les nouveautés apportées par la version 8.4 de PostgreSQL. Il s'agit d'une partie de la liste réelle des changements, qui est bien plus vaste.

Lire la suite...

jeudi 22 janvier 2009

Sun fait un point sur l'acquisition de MySQL

« Nous avons des équipes qui travaillent sur d'autres bases de données Open Source, comme PostgreSQL, mais nous sommes vraiment en termes de stratégie et d'organisation en train de nous focaliser à fond sur MySQL pour en faire la base de données principale de Sun. »

Lire la suite...

mercredi 27 août 2008

Écrire et utiliser des fonctions retournant une valeur de type composite (ROWTYPE)

Lire la suite...

vendredi 23 mai 2008

Script OCF (heartbeat-2) pour pgbouncer

Voici en pièces jointes 2 fichiers permettant de configurer pgbouncer sur un pool de serveurs afin de créer un cluster HA pour pgbouncer.

Lire la suite...

lundi 4 février 2008

Script OCF (heartbeat-2) pour pgbouncer

Ce script est une adaptation du script ocf Evmsd fourni par heartbeat-2, il y a une ou deux gruikerie (return 7 au lieu du code retour OCF) mais il est fonctionnel (la bascule s'effectue bien en cas de standby, défaillance serveur, coupure heartbeat, crash pgbouncer ...)

Lire la suite...

jeudi 31 janvier 2008

Un article de 01net

À l'occasion de SolutionsLinux, 01net rappelle la robustesse de PostgreSQL et souligne le dynamisme de la communauté.

L'article est en ligne sur le site de 01net.

lundi 10 décembre 2007

PostgreSQL et Ruby

Extracteur de tables

Trouvé sur le site We are Geeks, un extracteur de tables pour PostgreSQL.

Il s'agit d'extraire d'une base de données les informations nécessaires à sa recréation.

Tout est là : Extracteur PG en ruby.

mercredi 24 octobre 2007

Pourquoi préférer PostgreSQL à MySQL

Lire la suite...

mercredi 5 septembre 2007

Index inversé, en C

Depuis la version 8i, Oracle implémente les index inversés. Voici une proposition d’implémentation équivalente pour PostgreSQL. Les index inversés permettent d’accélérer les recherches sur les motifs tels que « colonne LIKE '%chaîne' ». Dans un tel cas, PostgreSQL effectue un parcours séquentiel (ou « sequential scan ») de la table interrogée. Toutefois, il est possible d’émuler un index inverse au moyen d’une fonction de renversement de chaîne couplée à un index sur fonction.

L'article précédent proposait l'implémentation d'un prototype en langage procédural PL/pgSQL, qui fait office ici de prototype. Cette implémentation a pour principal défaut d'être lente, pénalisant ainsi gravement les performances en écriture (INSERT et UPDATE). Ainsi, à chaque mise à jour, il est nécessaire de faire appel à la fonction reverse pour mettre à jour l'index fonctionnel ; cela s'observe notamment à la création de l'index. En revanche, il est possible de tirer partie des capacités de traitement des caractères multi-octets, que l'on rencontre notamment dans le cas d'une base de données encodée en UTF-8.

Ainsi, l'implémentation en langage C se doit d'être à la fois plus rapide et surtout se doit de supporter les jeux de caractères multi-octets. C'est à partir de ce minuscule cahier des charges que nous allons construire notre fonction reverse.

Pourquoi écrire une procédure stockée en C

Pourquoi s'embêter à prendre le temps d'écrire une procédure stockée en langage C alors qu'il est possible de faire la même chose en langage PL/pgSQL ? Il y a plusieurs réponses à cette question :

  • Une fonction C permet de protéger le code. En effet, rien n'interdit à un utilisateur possédant les droits nécessaires de modifier la procédure stockée que l'on a écrite et validé par une autre procédure de son crue, rendant le système inopérant.
  • Si le besoin de créer son propre type de données se fait sentir, le passage par la case fonction C est obligatoire.
  • La satisfaction de connaître un peu mieux le fonctionnement interne de PostgreSQL, mais c'est surtout une satisfaction de geek :)
  • La problématique de la vitesse est toutefois le facteur déterminant de la réécriture d'une fonction d'un langage procédural interprété en langage compilé.

Le gain significatif de vitesse ne sera pas évident pour les requêtes de sélection. En revanche, les écritures (surtout INSERT et UPDATE) peuvent être fortement pénalisées par le coût de la mise à jour d'un index fonctionnel. Bien que cela ne soit pas évident pour une opération unitaire, il sera parfaitement visible dans le cas d'une opération d'écriture en masse (chargement massif de données), ou tout simplement pour la création de l'index fonctionnel. Dans un tel cas, l'option d'une réécriture en langage C est à envisager très sérieusement.

Implémentation et discussion technique

Les possibilités d'extension de PostgreSQL s’appuient sur les mécanismes de chargement dynamique de bibliothèque du système d’exploitation. L’interface de programmation est relativement simple, à condition d’en connaître certaines clés.

Structure du projet

Le projet est articulé autour de différents fichiers, qui seront tous placés dans un répertoire dédié :

  1. un fichier Makefile simplifié, utilisant PGXS, l'infrastructure de construction d'extension PostgreSQL ;
  2. un modèle de script SQL d'installation reverse.sql.in ;
  3. un fichier uninstall_reverse.sql ;
  4. le fichier source en langage C, reverse.c.

Fichiers annexes

Avant toute chose, il faut disposer d’un fichier « Makefile » de construction du module externe :

MODULES = reverse #PG_CPPFLAGS = -ggdb DATA_built = reverse.sql DATA = uninstall_reverse.sql PGXS := $(shell pg_config --pgxs) include $(PGXS)

Le Makefile utilise ici l’outil PGXS qui propose un fichier Makefile prédéfini, à l’instar des fichiers Makefile fournis par Oracle.

Le fichier « reverse.sql.in » qui sert de modèle à la création du fichier d'installation de l'extension « reverse.sql ». Ce dernier fichier sera généré à partir du modèle en remplaçant « MODULE_PATHNAME » par le chemin complet du fichier objet généré.

-- Déclaration de la fonction reverse en tant que module C SET search_path = public; CREATE OR REPLACE FUNCTION reverse(varchar) RETURNS varchar      AS 'MODULE_PATHNAME', 'reverse'      LANGUAGE 'C' IMMUTABLE STRICT;

Le script « reverse.sql » sera exécuté par un utilisateur PostgreSQL ayant le rôle d’administrateur, les fonctions C étant considérées comme non-sûres et donc de la responsabilité de l’administrateur.

Un script de désinstallation « uninstall_reverse.sql » est également prévu, ça fait toujours plaisir :

SET search_path = public; DROP FUNCTION reverse(varchar);

Un peu de technique

La lecture de la page « Fonctions en langage C » permet d’obtenir les informations nécessaires au développement d’une fonction C, voir la documentation « Fonctions en langage C ». Cependant la lecture des fichiers d’en-têtes permet d’apporter un éclairage supplémentaire sur certaines structures de données.

Traitement des chaînes de caractères avec PostgreSQL

Sous PostgreSQL, les chaînes de caractères ne sont pas délimitées par un caractère nul « \0 » terminal, mais, à l’instar du langage Pascal, en stockant dans une structure d’abord sa longueur puis son contenu. Une telle chaîne est décrite dans une structure de type « varlena ». Ce type de données offre en fait un moyen uniforme de stocker tout type de données à longueur variable, comme les chaînes de caractères, les tableaux ou encore les types utilisateurs.

Voici sa définition, obtenu dans le fichier d'en-tête c.h, à la ligne 409 :

struct varlena {     int32 vl_len_; /* Do not touch this field directly! */     char vl_dat[1]; };

Ainsi, l'entier vl_len contient la longueur, en octets, de la chaîne d'octets vl_dat.

Quelques macros permettent de manipuler facilement cette structure.

  • VARDATA(varlena) obtient un pointeur sur la donnée ;
  • VARSIZE(varlena) obtient la taille en octets de la structure varlena (vl_len + vl_dat) ;
  • la constante VARHDRSZ représente la taille en octet de vl_len ;
  • Enfin, VARATT_SIZEP, remplacée par SET_VARSIZE à partir de la 8.3, permet de définir la longueur en octets de la donnée.

Ainsi, pour obtenir la longueur en octets de la données, on utilisera (VARSIZE - VARHDRSZ).

Support des jeux de caractères multi-octets

L'implémentation proposée supporte les jeux de caractères multi-octets, comme l'UTF8 (ou Unicode) et les jeux de caractères asiatiques, qui représente certains caractères sous la forme d'une séquence de deux octets ou plus (voir référence). PostgreSQL met à disposition des fonctions utiles pour manipuler les chaînes de caractères, peu importe l'encodage, notamment pg_verifymbstr qui valide une chaîne de caractère selon l'encodage de la base de données, ou encore pg_mblen qui donne la longueur en octets d'un caractère. Pour le prototype des fonctions citées et d'autres fonctions, se référer au fichier d'en-tête « mb/pg_wchar.h ».

Les conventions d'appel

Il existe deux conventions d'appel de fonctions externes :

  1. La convention d'appel version 0, représentant l'ancien style, simple à utiliser ;
  2. La convention d'appel version 1, qui est la norme dorénavant et qui ne présente pas de difficultés particulières.

La convention d'appel version 1 sera utilisée dans le but de donner d'entrée de jeu de bonnes habitudes. La complexité de cette convention est masquée par une batterie de macros qui rendent son utilisation tout aussi simple, voire encore plus simple que la version 0, notamment pour le passage d'arguments.

Implémentation en langage C

Le source C est structuré en quatre parties :

  • L’inclusion des fichiers d’en-têtes nécessaires ;
  • La définition d’un « magic » signant un module externe PostgreSQL ;
  • La définition d’un « magic » déclarant la fonction reverse à PostgreSQL ;
  • Le corps de fonction reverse, cette fois en langage C.

Voici ci-après, le code source en langage C de la fonction reverse.

/*
 * reverse procedural function *
 * Thomas Reiss, 12/07/2007 – 24/07/2007 - 02/08/2007
 * Alain Delorme, 24/07/2007
 * Merci à depesz pour ses tests sur la version 8.3devel
 *
 */

#include "pg_config.h"
#include "postgres.h"
#include "fmgr.h"
#include "mb/pg_wchar.h"
#include "utils/elog.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

Datum reverse(PG_FUNCTION_ARGS);

// SET_VARSIZE correspond à la nouvelle API, nous définissons cette
// macro pour les versions ne la possédant pas.
#ifndef SET_VARSIZE
#define SET_VARSIZE(n,s) VARATT_SIZEP(n) = s;
#endif

/* fonction reverse */
PG_FUNCTION_INFO_V1(reverse);
Datum reverse(PG_FUNCTION_ARGS)
{
  int len, pos = 0;
  VarChar *str_out, *str_in;

  /* Obtient l'adresse de l'argument */
  str_in = PG_GETARG_VARCHAR_P_COPY(0);

  /* Calcul de la taille en octet de la chaîne */
  len = (int) (VARSIZE(str_in) - VARHDRSZ);

  /* Créer une chaîne vide de taille identique */
  str_out = (VarChar *)palloc(VARSIZE(str_in));

  /* La structure résultante aura une longueur identique */
  SET_VARSIZE(str_out, VARSIZE(str_in));

  /* Vérifie que l'encodage de la chaîne en argument
   * concorde avec l'encodage de la BDD
   */
  pg_verifymbstr(VARDATA(str_in), len, false);

  /* Copie à l'envers de la chaîne */
  while (pos < len)
  {
    int charlen = pg_mblen(VARDATA(str_in) + pos);
    int i = charlen;
    // Copie un caractère.
    // !! Un caractère != un octet
    while (i--)
      *(VARDATA(str_out) + len - charlen + i - pos) = *(VARDATA(str_in) + i + pos);
    pos = pos + charlen;
    // incrémente le compteur
  }
  PG_FREE_IF_COPY(str_in, 0);

  /* Retourne la copie */
  PG_RETURN_VARCHAR_P(str_out);
}

Construction

La construction de l'extension PostgreSQL est réalisée en invoquant make

tom@clementina:~/src/reverse$ make cc -g -Wall -O2 -fPIC -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -g -fpic -I. -I/usr/include/postgresql/8.2/server -I/usr/include/postgresql/internal -D_GNU_SOURCE -I/usr/include/tcl8.4 -c -o reverse.o reverse.c cc -shared -o reverse.so reverse.o rm reverse.o

Si tout s'est bien passé, l'installation sera finalisée en exécutant la commande make install, éventuellement précédé de sudo en fonction de sa distribution et de son installation de PostgreSQL.

tom@clementina:~/src/reverse$ sudo make install Password: xxxx /bin/sh /usr/lib/postgresql/8.2/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./reverse.sql '/usr/share/postgresql/8.2/contrib' /bin/sh /usr/lib/postgresql/8.2/lib/pgxs/src/makefiles/../../config/install-sh -c -m 755 reverse.so '/usr/lib/postgresql/8.2/lib'

Les fichiers produits seront ainsi installés dans le répertoire d'installation de PostgreSQL. Il est toutefois possible de les positionner ailleurs, à condition d'adapter le fichier « reverse.sql » de façon à indiquer à PostgreSQL où se trouve la bibliothèque partagée (fichier « reverse.so » sous Linux).

Utilisation et performances

Vérification de bon fonctionnement

Dans un premier temps, on crée la fonction via l'outil psql :

test=# \i reverse.sql CREATE FUNCTION

On vérifie que la fonction répond correctement :

test=# SHOW client_encoding;

 client_encoding

-----------------

 UTF8

(1 ligne)

test=# SELECT reverse('Chaîne à renverser');

      reverse

--------------------

 resrevner à enîahC

(1 ligne)

Ok, ça marche, y compris avec les chaînes encodées en UTF-8 !

Petit test de performance

Ce test a été réalisé par depesz, qui m'a aimablement autorisé a le réutiliser dans le cadre de cet article.

Petit aperçu du jeu de test :

test=# SELECT count(*),

test-# min(length(filepath)),

test-# max(length(filepath)),

test-# sum(length(filepath))

test-# FROM test;

 count | min | max | sum

-------+-----+-----+----------

320136 | 7 | 174 | 18563865

(1 row)

Maintenant, voici une petite comparaison des trois implémentations, à savoir le prototype en PL/pgSQL, la version PL/perl de depesz et la version C. On oppose à ces trois tests un parcours de la table via la fonction d'agrégat count(), permettant ainsi de mesurer l'overhead due à chaque implémentation de la fonction reverse. À chaque fois, 3 exécutions permettent de vérifier les résultats.

Simple comptage (count)

Voici l'ordre SQL utilisé pour réaliser ce test :

test=# EXPLAIN ANALYZE

test-# SELECT count(filepath)

test-# FROM test;

Et voici les temps de réponse obtenus :
Exécution #1 : 1269.535 ms
Exécution #2 : 1268.421 ms
Exécution #3 : 1257.926 ms
Moyenne : 1265,29 ms

Prototype PL/pgSQL

test=# EXPLAIN ANALYZE

test-# SELECT count(reverse_plpgsql(filepath))

test-# FROM test;

Exécution #1 : 55269.941 ms
Exécution #2 : 56047.004 ms
Exécution #3 : 56149.888 ms
Moyenne : 55822,28 ms

Version PL/perl

test=# EXPLAIN ANALYZE

test-# SELECT count(text_reverse(filepath))

test-# FROM test;

Exécution #1 : 4088.625 ms
Exécution #2 : 4089.729 ms
Exécution #3 : 4020.500 ms
Moyenne : 4066,28 ms

Version C

test=# EXPLAIN ANALYZE

test-# SELECT count(reverse(filepath))

test-# FROM test;

Exécution #1 : 1596.176 ms
Exécution #2 : 1647.046 ms
Exécution #3 : 1657.531 ms
Moyenne : 1633,58 ms

Synthèse du test de performance

Voici un graphe faisant la synthèse des moyennes des temps de réponse :

Comparaison des temps de réponse

Le graphe suivant permet de mieux se rendre compte de l'overhead induie par l'implémentation PL/perl et l'implémentation C.

Comparaison des temps de réponse

Chose très intéressante : l'overhead pour renverser ~320000 enregistrements est de seulement 300ms, ce qui est bien entendu excellent et laisse présager de très bonnes performances quant au coût de la mise à jour d'un index fonctionnel.

Ainsi, comme cela pouvait être aisément imaginé, la version C est la plus rapide, suivie par la version PL/Perl. La version PL/pgSQL se traîne lamentablement derrière, ce qui justifie complètement la réécriture de la procédure stockée en C.

Notes

Cette fonction a été testé sur une base en PostgreSQL 8.0, 8.2 et 8.3devel (merci à depesz).

Je regrette de ne pas avoir pu aller un peu plus loin pour le précédent article, des impératifs de place m'ayant obligé à aller à l'essentiel sans montrer les différents plans d'exécution. Heureusement, l'article de hubert depesz lubaczewski montre tous les aspects que j'ai négligé, malheureusement c'est en anglais.

Références

De plus amples précisions sont également disponibles en langue anglaise sur les sites Internet suivant :

Remerciements

Je remercie vivement les personnes suivantes :

  • Alain Delorme pour sa contribution,
  • hubert depesz lubaczewski pour ses retours et tests préliminaires,
  • Guillaume Lelarge pour ses relectures et ses conseils avisés.
Article écrit par Thomas Reiss, publié sur postgresqlfr.org avec sa permission. Vous pouvez le retrouver sur son blog où il parle encore de PostgreSQL (et d'autres choses :-) ). Merci beaucoup.

Sécuriser votre base PostgreSQL

Article écrit par Hubert Lubaczewski et traduit par Damien Clochard, le 18 août 2007. La version originale est disponible sur le blog de l'auteur où se trouvent beaucoup d'autres articles intéressants.

Lire la suite...

dimanche 15 juillet 2007

Utiliser un index pour les recherches sur des motifs tels que « colonne LIKE '%chaîne' »

Lire la suite...

vendredi 29 septembre 2006

Debian Sarge, PostgreSQL 8.1, XID Wraparound et Single User Mode

Lire la suite...

vendredi 7 avril 2006

Problèmes de récupération après crash

Lire la suite...

mardi 28 mars 2006

Beginning Databases With PostgreSQL

Lire la suite...

- page 1 de 3

Propulsé par Dotclear