PostgreSQL 8.2
Table of Contents
Le but de ce document est de couvrir en détails les fonctionnalités majeures de la nouvelle version 8.2 de PostgreSQL.
SQL
Support de SQL:2003
Clause RETURNING
Supposons que vous vouliez insérer une ligne dans une table disposant d'un champ de type serial (donc ayant comme valeur par défaut la prochaine valeur d'une séquence) et que vous souhaitiez récupérer cette valeur. Avec les versions antérieures, vous le faites avec deux requêtes : un INSERT, et un SELECT récupérant la valeur de la séquence. Par exemple :
amarok=# INSERT INTO album (name) VALUES ('Half the perfect world'); INSERT 55333 1 amarok=# SELECT currval('album_seq'); currval --------- 273 (1 ligne)
Voici la preuve que la valeur renvoyée est bien l'identifiant attendu :
amarok=# SELECT * FROM album WHERE name='Half the perfect world'; id | name -----+------------------------ 273 | Half the perfect world (1 ligne)
Il serait intéressant que la requête d'insertion renvoie directement la valeur de l'identifiant. C'est ce que propose la clause RETURNING. Dans ce cas, vous procéderiez ainsi :
amarok=# INSERT INTO album (name) VALUES ('From this moment on') RETURNING id; id ----- 274 (1 ligne) INSERT 55334 1
Et voilà, j'insère une ligne et je récupère mon information en même temps. La fonctionnalité RETURNING ne s'arrête pas là. Cette clause peut renvoyer tout champ et expression que vous souhaitez. Elle s'applique aux trois instructions de modification : INSERT, UPDATE et DELETE. C'est donc une nouvelle fonctionnalité très intéressante. En diminuant le nombre de requêtes envoyées au serveur, elle participe à l'amélioration des performances.
Si vous utilisez RETURNING avec une des trois instructions ci-dessus, vous devez avoir le droit SELECT sur cette table en plus du droit INSERT, UPDATE ou DELETE.
Vous pouvez aussi utiliser cette clause dans une procédure stockée en PL/pgsql. Il y a deux possibilités :
- vous ne récupérez qu'une ligne : un simple « INSERT ... RETURNING expressions INTO [STRICT] cible; » suffira (si plus d'une ligne est renvoyée, une erreur sera levée) ;
- vous récupérez plus d'une ligne : la construction FOR peut s'en charger.
Enfin, vous pouvez aussi l'utiliser dans une règle. La règle ainsi créée supportera les requêtes avec et sans clause RETURNING.
Alias de table cible dans les intructions UPDATE/DELETE
Depuis la version 8.1, un UPDATE, comme un DELETE peut faire appel à une deuxième table grâce à la clause USING. Si vous vouliez renommer temporairement les tables avec la clause AS, vous ne pouviez le faire que dans la clause USING. Autrement dit, vous ne pouviez pas ajouter un alias à la table dont des lignes allaient être mises à jour ou supprimées.
C'est maintenant possible avec la 8.2. La requête suivante est donc valide :
UPDATE tags AS cible SET cible.artist=autre.artist USING tags AS autre, album WHERE cible.album=album.id AND autre.album=album.id AND cible.artist<>autre.artist
Clause VALUES
Une instruction INSERT se contruit sous cette forme :
INSERT INTO table (champ1, champ2, ...) VALUES (valeur1, valeur2, ...);
L'expression « VALUES (valeur1, valeur2, ...) » est tout simplement la valeur d'une ligne. Avant PostgreSQL 8.2, il est possible d'utiliser cette expression avec la seule instruction INSERT. À partir de la version 8.2, VALUES devient une instruction à part entière. Voici un exemple :
amarok=# VALUES ('valeur1', 'valeur2'); column1 | column2 ---------+--------- valeur1 | valeur2 (1 ligne)
L'intérêt de cette modification est d'autoriser la saisie de plusieurs lignes, en séparant chaque ligne par une virgule. Nouvel exemple :
amarok=# VALUES ('valeur1, ligne 1', 'valeur2, ligne 1'), ('valeur1, ligne 2', 'valeur2, ligne 2'); column1 | column2 ------------------+------------------ valeur1, ligne 1 | valeur2, ligne 1 valeur1, ligne 2 | valeur2, ligne 2 (2 lignes)
VALUES est très utile dans un cas précis. Tout d'abord, il permet d'insérer plusieurs lignes à la fois avec l'instruction INSERT. Si je prends l'exemple de la section précédente, je peux ajouter les deux lignes en une seule requête :
amarok=# INSERT INTO album (name) VALUES ('Half the perfect world'), ('From this moment on'); INSERT 0 2
Si on veut reprendre l'exemple complet de la section précédente :
amarok=# INSERT INTO album (name) VALUES ('Half the perfect world'), ('From this moment on') RETURNING *; id | name -----+------------------------ 280 | Half the perfect world 281 | From this moment on (2 lignes) INSERT 0 2
J'insère deux lignes et je récupère leurs identifiants respectifs en une seule commande SQL... impressionnant, non ?
On peut aussi utiliser cette construction en tant que table temporaire en dur sur la requête... je manque d'exemple intéressant pour ce cas.
Extensions SQL
Clause IF EXISTS
IF EXISTS s'emploie avec les instructions DROP (par exemple DROP TABLE). Auparavant, pour supprimer un objet, il fallait d'abord vérifier qu'il existait bien. Maintenant, en ajoutant IF EXISTS, aucune erreur ne sera retournée si l'objet n'existe pas.
amarok=# DROP TABLE toto; ERREUR: La table « toto » n existe pas amarok=# DROP TABLE IF EXISTS toto; INFO: table "toto" does not exist, skipping DROP TABLE
Comme le montre l'exemple, seul un message d'avertissement est affiché mais l'action est un succès. Cela permet d'éviter d'exécuter une requête pour vérifier que l'objet existe bien avant de lancer l'ordre de suppression. Cela permet d'éviter l'annulation d'une transaction commencée. Enfin, bien que cela ne fasse pas partie du standard SQL, cette variante est très utilisée dans les bases MySQL. Le support de cette fonctionnalité facilite le portage des applications MySQL vers PostgreSQL.
COPY TO à partir d'un SELECT
La commande COPY permet d'extraire les données complètes d'une table. Pour la version 8.2, certains ont voulu ajouter le support des vues, d'autres préféraient ajouter celui d'une requête. Cette dernière proposition a été choisie. Maintenant, il est donc possible de réaliser l'opération COPY à partir d'une requête avec la formulation suivante : COPY (requete) TO...
Les parenthèses sont absolument nécessaires. Voici un exemple :
amarok=# COPY (SELECT * FROM album WHERE name LIKE 'A%') TO STDOUT; 14 Autobiography 30 A Camp 54 All For You - A Decication To The Nat King Cole Trio 68 A Day without Rain 69 Amarantine 75 Always & Forever 88 A State of Wonder: the Complete Goldberg Variations - disc 1 92 A State of Wonder: the Complete Goldberg Variations - disc 2 98 Artist Portrait 104 A Funk Odyssey 151 Anamorphosee 167 A Fortnight in France (live) 209 A force de vivre 212 Au sourire de l âme 223 Alice & June / CD1 224 Alice & June / CD2 228 A la faveur de l automne 265 Au Lit Les Mômes !
Remarquez que cela permet aussi de supporter la première demande des utilisateurs, à savoir le support des vues, comme dans l'exemple suivant :
amarok=# \dv
Liste des relations
Schéma | Nom | Type | Propriétaire
--------+------+------+--------------
public | vart | vue | guillaume
(1 ligne)
amarok=# COPY (SELECT * FROM vart) TO STDOUT;
243 10000 Maniacs
244 Ace of Base
245 Alanis Morissette
246 Alberto Iglesias
247 Alice Deejay
248 Alicia Keys
249 Alizée
250 Karrin Allyson
[... coupé parce que beaucoup trop long ...]
Enfin, notez que vous pouvez aussi utiliser une liste VALUES comme dans cet exemple :
amarok=# COPY (VALUES (1, 2), (3, 4), (5, 6)) TO STDOUT; 1 2 3 4 5 6
Création d'index en parallèle
Lors de la création d'un index, PostgreSQL pose un verrou SHARE sur la table en cours d'indexage. Ce verrou bloque toute modification sur la table en question. Sur de grosses tables ou sur des tables très fréquemment modifiées, cela peut créer un goulot d'étranglement néfaste aux performances du système, voire même à son utilisation.
La version 8.2 ajoute donc la possibilité de créer des index tout en ne bloquant pas l'accès en modification à la table. Malgré tout, un verrou SHARE UPDATE EXCLUSIVE est posé mais ce dernier ne bloque que les modifications sur la structure même de la table et empêche l'exécution d'un VACUUM. Du coup, la modification des données se révèle possible à condition d'accepter quelques inconvénients inhérents à l'opération.
- Le moteur doit attendre la fin des transactions existantes avant de commencer la création de l'index.
- Si un problème survient, l'index résultant sera invalide. Il ne sera pas utilisé pour l'optimisation des requêtes mais les mises à jour de l'index seront quand même effectuées. Si une contrainte d'unicité a été demandé, cette contrainte sera toujours active. Il est donc nécessaire de supprimer l'index et de le recréer si vous souhaitez toujours créer l'index sans blocage des écritures. Dans le cas contraire, vous pouvez utiliser REINDEX qui ne tient pas compte de l'option CONCURRENTLY.
- Si vous créez un index UNIQUE, la contrainte d'unicité est posée avant la création de l'index (d'où le fait qu'en cas de problème de création de l'index, l'unicité est toujours contrainte). Mais comme elle est valide avant l'index et que des modifications sont possibles sur les données, cela impose que les requêtes exécutées pendant la création de l'index peuvent échouer si elles ne respectent pas l'unicité de la colonne indexée.
- Vous pouvez créer d'autres index sur une table pendant la création d'un index sans blocage mais seule une construction d'index en parallèle peut survenir sur une table à un même moment.
- Enfin, contrairement à CREATE INDEX, il n'est pas possible d'utiliser CREATE INDEX CONCURRENTLY dans une transaction explicite.
Tout ceci montre les inconvénients à l'utilisation de cette nouvelle option. Mais cela ne doit pas occulter son intérêt principal : créer un index tout en ne bloquant pas les opérations d'écritures indispensables à tout système en production.
Pour plus d'informations, voir la page man de CREATE INDEX et surtout la section « Construire des index en parallèle ».
Valeurs NULL dans les tableaux
Dans les versions antérieures à la 8.2, il était impossible d'initialiser un élément du tableau à NULL. Une nouvelle fonctionnalité marquante de cette 8.2 est justement de le permettre. En voici une preuve rapide :
amarok=# CREATE TABLE foobarray (tab int[]); CREATE TABLE amarok=# INSERT INTO foobarray VALUES (ARRAY[1,2,3]), (ARRAY[4,5,6]), (ARRAY[1,NULL]), (ARRAY[1,NULL,3]); INSERT 0 4 amarok=# SELECT * FROM foobarray; tab ------------ {1,2,3} {4,5,6} {1,NULL} {1,NULL,3} (4 lignes)
(Et vous pouvez remarquer l'utilisation du VALUES à plusieurs lignes.)
Il est donc possible de saisir une valeur NULL dans un tableau. Auparavant, le mot clé NULL était pris pour la chaîne 'NULL'. Ce n'est plus le cas comme le montre l'exemple ci-dessous :
amarok=# CREATE TABLE foobarray2 (tab varchar(10)[]); CREATE TABLE amarok=# INSERT INTO foobarray2 VALUES ('{4,NULL,6}'); INSERT 0 1 amarok=# SELECT * FROM foobarray2; tab -------------- {4,NULL,6} (1 ligne)
Si vous voulez insérer le mot clé NULL en tant que chaîne, vous devez ajouter des guillemets doubles (vous ne pouvez pas utiliser les guillemets simples, cela provoquerait l'affichage d'une erreur).
amarok=# INSERT INTO foobarray2 VALUES ('{4,"NULL",6}'); INSERT 0 1 amarok=# SELECT * FROM foobarray2; tab -------------- {4,NULL,6} {4,"NULL",6} (2 lignes)
Vous pouvez revenir à l'ancien comportement en modifiant une nouvelle variable de configuration nommée array_nulls :
amarok=# SET array_nulls TO off; SET amarok=# INSERT INTO foobarray2 VALUES ('{7,NULL,9}'); INSERT 0 1 amarok=# INSERT INTO foobarray2 VALUES ('{7,"NULL",9}'); INSERT 0 1 amarok=# SELECT * FROM foobarray2; tab -------------- {4,NULL,6} {4,"NULL",6} {7,"NULL",9} {7,"NULL",9} (4 lignes)
Dernier point, si vous tentez de modifier une valeur en dehors des indices actuels du tableau, ce dernier sera agrandi en plaçant des valeurs NULL pour les éléments non spécifiés :
amarok=# UPDATE foobarray SET tab[5]='4' WHERE tab='{1,2,3}'; UPDATE 1 amarok=# SELECT * FROM foobarray; tab ---------------- {4,5,6} {1,NULL} {1,NULL,3} {4,NULL,6} {1,2,3,NULL,4} (5 lignes)
Option FILLFACTOR sur les relations
À chaque fois que vous mettez à jour une ligne, il y a de fortes chances pour que le moteur ajoute une info d'obsolescence sur l'ancienne ligne et ajoute une nouvelle ligne contenant les infos mises à jour. Ce qui veut dire que, plus vous mettez à jour et insérez, plus votre table grossira. Une ligne ne se trouvera plus sur la même page qu'avant la mise à jour, ce qui pose des problèmes de performances. Les développeurs ont donc ajouté la notion de facteur de remplissage. Vous pouvez maintenant indiquer le pourcentage d'occupation de la page. Avec un facteur de 100, vous obtiendrez le comportement normal. Avec un facteur de 50, vous aurez des fichiers à moitié vide mais deux fois plus gros.
Voyons cela avec un exemple :
amarok=# CREATE TABLE tags_100 WITH (FILLFACTOR=100) AS SELECT * FROM tags; SELECT amarok=# CREATE TABLE tags_50 WITH (FILLFACTOR=50) AS SELECT * FROM tags; SELECT amarok=# CREATE TABLE tags_10 WITH (FILLFACTOR=10) AS SELECT * FROM tags; SELECT amarok=# \x Affichage étendu activé. amarok=# select * from pgstattuple('tags'); -[ RECORD 1 ]------+-------- table_len | 1122304 tuple_count | 3976 tuple_len | 1083156 tuple_percent | 96.51 dead_tuple_count | 0 dead_tuple_len | 0 dead_tuple_percent | 0 free_space | 19968 free_percent | 1.78 amarok=# select * from pgstattuple('tags_100'); -[ RECORD 1 ]------+-------- table_len | 1105920 tuple_count | 3976 tuple_len | 1067252 tuple_percent | 96.5 dead_tuple_count | 0 dead_tuple_len | 0 dead_tuple_percent | 0 free_space | 19536 free_percent | 1.77 amarok=# select * from pgstattuple('tags_50'); -[ RECORD 1 ]------+-------- table_len | 2252800 tuple_count | 3976 tuple_len | 1067252 tuple_percent | 47.37 dead_tuple_count | 0 dead_tuple_len | 0 dead_tuple_percent | 0 free_space | 1163044 free_percent | 51.63 amarok=# select * from pgstattuple('tags_10'); -[ RECORD 1 ]------+--------- table_len | 13369344 tuple_count | 3976 tuple_len | 1067252 tuple_percent | 7.98 dead_tuple_count | 0 dead_tuple_len | 0 dead_tuple_percent | 0 free_space | 12247020 free_percent | 91.61
Dans cet exemple, j'ai créé trois tables identiques au niveau des données mais différentes au niveau du FILLFACTOR. À 100% ou sans indication, la table fait 1122304 octets pour 3976 pages. J'ai le même résultat avec un FILLFACTOR à 100%. Par contre, avec celui à 50%, mon fichier double de taille et le pourcentage des lignes vivantes fait à peu près 50%... au détriment de la place nécessaire sur l'espace disque.
Vous pouvez récupérer les FILLFACTOR de chaque table en requêtant sur pg_class :
amarok=# SELECT relname, reloptions FROM pg_class WHERE relname like 'tags%' ORDER BY relname; relname | reloptions ----------+------------------ tags | tags_10 | {fillfactor=10} tags_100 | {fillfactor=100} tags_50 | {fillfactor=50} (4 lignes)
Cette option existe aussi sur les index. De plus, contrairement aux tables, vous pouvez modifier le FILLFACTOR d'un index existant via la commande http://docs.postgresqlfr.org/8.2/sql-alterindex.html ALTER INDEX] mais vous devrez exécuter un REINDEX pour le recréer.
Dans le cas d'une table statique, il est préférable de conserver un FILLFACTOR de 100%. Vous y gagnerez en place disque sans y perdre en performance. Dans le cas d'une table mise à jour fréquemment, cette option vous permettra de gagner en performance au prix d'un espace disque occupé plus important.
Les valeurs min et max dépendent de l'objet sur lequel l'option est utilisée. Un point commun, la valeur max est toujours la valeur par défaut.
| Objet | Min | Max |
| Table | 10 | 100 |
| Index Btree | 10 | 90 |
| Index Hash | 10 | 75 |
| Index GiST | 10 | 90 |
| Index GIN | 10 | 100 |
De plus, pour les index Btree, les éléments qui ne sont pas de feuilles ont un FILLFACTOR fixe (70).
Du coup, lors d'une mise à jour, la taille de la table ne change pas et l'élément mis à jour sera sur la même page. Du coup, les SELECT seront plus rapides (car on lit page par page)
Quelques liens supplémentaires :
- Frequent Update Project: Design Overview of HOT Updates, thread commencé par Simon Riggs.
- A couple thoughts about btree fillfactor, thread commencé par Tom Lane.
- fillfactor using WITH syntax, thread commencé par ITAGAKI Takahiro.
Fonctions d'agrégats pour des statistiques SQL:2003
La version 8.2 inclut de nombreuses fonctions d'agrégats spécifiques aux statistiques et réclamées par le standard SQL:2003. Je n'irais pas en détail sur ce sujet, mes connaissances en statistiques étant relativement ridicules. Bref, voici un lien vers la documentation tellement succincte qu'elle se réduit à ce tableau.
Spécificités PostgreSQL
Index GIN
pg_advisory_locks
Anciennement connu sous le nom de userlocks, ce module contrib a été intégré au coeur de PostgreSQL 8.2. Cette nouvelle fonctionnalité est maintenant désigné en tant qu'« Advisory Locks », autrement dit des verrous informatifs.
Le point très particulier de ces verrous, c'est qu'ils ne respectent pas MVCC. Autrement dit, ils ne sont pas relâchés à la fin d'une transaction. Ce sont des verrous sur le long terme. Autre point particulier, ils demandent une coopération des utilisateurs. Si un utilisateur décide de ne pas prendre en compte ce verrou, il peut très bien le faire. Enfin, dernier point, le verrou s'établit sur un nombre. Ce nombre a la signification que vous souhaitez. Cela peut-être un OID. Donc vous pouvez verrouiller des objets comme les tables, les vues, les fonctions... mais aussi les lignes et c'est là le gros apport de ces verrous. Ce nombre peut avoir une signification toute autre, spécifique à votre application. Ils sont stockés dans le shared_buffers, donc pas d'écriture disque lors de la mise en place d'un verrou informatif.
Par exemple, si je veux verrouiller un album de ma table album à partir de mon client 1 :
amarok (client1)=# SELECT pg_try_advisory_lock(id), * FROM album WHERE id=281; pg_try_advisory_lock | id | name ----------------------+-----+----------------- t | 281 | Grains de sable (1 ligne)
Le verrou est accepté pour le client 1.
amarok (client2)=# SELECT pg_try_advisory_lock(id), * FROM album WHERE id=281; pg_try_advisory_lock | id | name ----------------------+-----+----------------- f | 281 | Grains de sable (1 ligne)
Le client 2 récupère aussi les infos mais voit qu'il n'a pas obtenu le verrou.
amarok (client1)=# SELECT pg_advisory_unlock(281);
pg_advisory_unlock
--------------------
t
(1 ligne)
Le verrou est relâché par le client 1.
amarok (client2)=# SELECT pg_try_advisory_lock(id), * FROM album WHERE id=281; pg_try_advisory_lock | id | name ----------------------+-----+----------------- 2 | 281 | Grains de sable (1 ligne)
Le client récupère les infos et s'aperçoit qu'il a obtenu le verrou.
Il est donc possible pour une application de savoir simplement si une fiche est en cours de modification par un autre client. Il peut ainsi activer ou non la modification de la fiche.
Quelques documents supplémentaires :
Autre format pour UPDATE sur plusieurs colonnes
Auparavant, pour mettre à jour plusieurs colonnes, vous deviez passer par un UPDATE de cette façon :
UPDATE album SET id=500, name='haha' WHERE id=200;
Maintenant, vous pouvez aussi le saisir ainsi :
UPDATE album SET (id,name)=(500,'haha') WHERE id=200;
Le gros avantage de cette nouvelle méthode est qu'elle ressemble comme deux gouttes d'eau à un INSERT. L'apprentissage du SQL en est plus rapide mais surtout le codage d'une application est plus simple. En effet, auparavant, la requête exécutée changeait du tout au tout. Voici un petit exemple en PHP :
if ($id == 0) {
$sql = "INSERT INTO tags (url, dir, createdate, modifydate, album, artist, ...) VALUES ($url, $dir, $createdate, $modifydate, $album, $artist, ...)";
} else {
$sql = "UPDATE album SET url=$url, dir=$dir, createdate=$createdate, modifydate=$modifydate, album=$album, artist=$artist WHERE id=$id ";
}
...
Avec la 8.2, un développeur pourra avoir plus d'éléments en commun :
$liste_colonnes = "url, dir, createdate, modifydate, album, artist, ...";
$liste_valeurs = "$url, $dir, $createdate, $modifydate, $album, $artist, ...";
if ($id == 0) {
$sql = "INSERT INTO tags ($liste_colonnes) VALUES ($liste_valeurs)";
} else {
$sql = "UPDATE album SET ($liste_colonnes)=($liste_valeurs) WHERE id=$id ";
}
...
La liste des colonnes n'est pas saisie deux fois, ce qui permet d'éviter un oubli. La liste des valeurs n'est pas, elle non plus, saisie deux fois, ce qui permet d'éviter d'oublier qu'une fonction, style pg_escape-string, ne soit oubliée pour une valeur (oublié sur l'UPDATE mais pas sur l'INSERT par exemple).
Divers
Nouvelles instructions SQL : DROP OWNED et REASSIGN OWNED
Si vous voulez supprimer un utilisateur, vous devez tout d'abord vous assurer qu'il n'y a plus d'objets qui dépendent de lui. Cela demandait beaucoup de tests. Et si vous trouviez un objet qui lui appartenait, il vous fallait d'abord changer le propriétaire de cet objet. Bref, beaucoup de travail.
La version 8.2 vous propose deux solutions :
- supprimer tous les objets qui appartiennent à un utilisateur : DROP OWNED ;
- réaffecter tous les objets qui appartiennet à un utilisateur à un autre utilisateur : REASSIGN OWNED.
On peut donc imaginer que tous les objets de Marc seront maintenant la propriété de Thierry :
REASSIGN OWNED BY marc TO thierry;
Il ne vous reste plus qu'à détruire le rôle marc.
Nouvelles options pour CREATE TABLE AS
Cette instruction dispose de deux nouvelles options.
La clause TABLESPACE permet de préciser le tablespace où doit être créée la table. Sans cette indication, le tablespace par défaut est utilisé.
La clause ON COMMIT permet de préciser le comportement des tables temporaires à la fin d'un bloc de transactions. Les trois possibilités sont :
- PRESERVE ROWS, comportement par défaut, aucune action réalisée.
- DELETE ROWS, toutes les lignes sont supprimées.
- DROP, la table temporaire est tout simplement supprimée.
Simplification du partitionnement
Une nouvelle option de CREATE TABLE permet de simplifier le partitionnement. Cette option permet d'inclure automatiquement toutes les contraintes de la table parent (option INCLUDING CONSTRAINTS).
De plus, ALTER TABLE peut maintenant supprimer une notion d'héritage, ou l'ajouter (option [NO] INHERIT).
TG_relname obsolète
En PL/pgsql, pour les procédures de trigger, la variable TG_relname indiquait le nom de la relation ayant déclenché le trigger. Cette variable est remplacée par le couple TG_table_name et TG_table_schema, respectivement le nom de la table et le nom du schéma auquel appartient la table.
Administration
Surveillance de l'activité du démon autovacuum
L'utilisation de pg_autovacuum est fortement conseillé depuis déjà quelques temps. Malheureusement, les traces générées ne sont pas très bavardes. En fait, par défaut, nous pouvons seulement savoir si un processus autovacuum est exécuté et quand il l'est. Mais nous ne savons pas s'il a exécuté un VACUUM ou un ANALYZE sur la table, voire sur la base. En gros, nous savons qu'il travaille mais nous ne savons pas ce qu'il fait réellement.
La version 8.2 nous fournit ces informations de deux façons : des colonnes supplémentaires dans pg_stat_*_tables et des nouvelles fonctions.
amarok=# SELECT * FROM pg_stat_user_tables WHERE relname='album'; -[ RECORD 1 ]----+------- relid | 45604 schemaname | public relname | album last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | seq_scan | 0 seq_tup_read | 0 idx_scan | 0 idx_tup_fetch | 0 n_tup_ins | 0 n_tup_upd | 0 n_tup_del | 0
Voici les quatre nouvelles colonnes : last_vacuum, last_autovacuum, last_analyze, last_autoanalyze. Leur intérêt est de nous indiquer l'heure du dernier VACUUM, VACUUM par autovacuum, ANALYZE et ANALYZE par autovacuum. En voici la preuve :
amarok=# VACUUM ANALYZE album; VACUUM amarok=# SELECT * FROM pg_stat_user_tables WHERE relname='album'; -[ RECORD 1 ]----+----------------------------- relid | 45604 schemaname | public relname | album last_vacuum | 2006-11-06 07:31:43.19613+01 last_autovacuum | last_analyze | 2006-11-06 07:31:43.19613+01 last_autoanalyze | seq_scan | 0 seq_tup_read | 0 idx_scan | 0 idx_tup_fetch | 0 n_tup_ins | 0 n_tup_upd | 0 n_tup_del | 0
Il existe aussi des fonctions pour accéder à ces informations :
amarok=# SELECT pg_stat_get_last_vacuum_time((SELECT oid FROM pg_class WHERE relname='album')); pg_stat_get_last_vacuum_time ------------------------------ 2006-11-06 07:31:43.19613+01 (1 ligne)
Surveillance des processus en attente
pg_stat_activity indique l'état des processus connectés au cluster mais il nous manquait une information : le processus est_il en attente ? La nouvelle colonne waiting est là pour ça
amarok=# SELECT * FROM pg_stat_activity; -[ RECORD 1 ]-+-------------------------------- datid | 45596 datname | amarok procpid | 5841 usesysid | 10 usename | guillaume current_query | select * from pg_stat_activity; waiting | f query_start | 2006-11-06 07:27:13.769082+01 backend_start | 2006-11-06 07:26:32.725066+01 client_addr | client_port | -1
Cette information était déjà disponible dans pg_locks en précisant le PID mais il est intéressant d'avoir une vue complète des processus serveur avec pg_stat_activity.
Nom du processus dans ps
La modification du nom du processus affiché par la commande Unix ps était un grand consommateur de temps. Des améliorations ont été intégrées pour diminuer cette occupation du processeur mais il est aussi possible de désactiver complètement cette fonctionnalité grâce à la nouvelle variable de configuration, update_process_title. Elle est activée par défaut pour conserver le comportement par défaut des anciennes versions.
WAL
- added forced switch to a new transaction log file
- WAL informational functions
- improve recovery from a crash during WAL replay
- add archive_timeout
- add native ldap authentication
Serveurs « warm standby »
Il ne s'agit pas à proprement parler d'une nouvelle fonctionnalité. Plutôt d'une nouvelle utilisation d'une ancienne fonctionnalité. Il s'agit de réplication « un maître, plusieurs esclaves » ne réclamant pas de logiciels supplémentaires, ce qui est un gros avantage. L'inconvénient principal est que la réplication se fait sur la totalité du cluster étant donné qu'elle fonctionne par recopie de fichiers WAL (et ceux-ci sont valables pour la totalité du cluster).
L'idée est la suivante. Les modifications réalisées sur le serveur maître créent des fichiers WAL sur ce serveur. Ces fichiers sont copiés un par un sur le serveur cible, ou serveur en attente. Ce serveur les rejoue immédiatement. En fait, ce serveur attend chaque fichier WAL pour le rejouer dès sa réception et être ainsi disponible dès que la serveur maître se trouve indisponible.
Évidemment, le deuxième point faible de cette réplication est que cela se fait fichier WAL par fichier WAL. Donc, si une opération est validée par un COMMIT, elle ne se trouve pas forcément sur les serveurs cibles. Elle ne sera intégrée qu'à la fin du fichier WAL courant. Donc, vous aurez très souvent une différence entre le maître et les esclaves.
Bref, encore une fois, tout dépend de ce que vous voulez faire. Si vous pouvez survivre à une petite perte de données, ce type de réplication sera certainement le mieux pour vous étant donné qu'il ne demande la maîtrise d'aucun logiciel supplémentaire : ni installation, ni configuration, ni administration...
postgresql.conf
postgresql.conf est le fichier de configuration de PostgreSQL. Au fil des ans, il a pris un certain embonpoint. La version 8.2 permet donc de le diviser en plusieurs fichiers qui seront inclus dans le fichier principal grâce à une directive d'inclusion.
Mais la grosse modification de ce fichier est ailleurs. Il est possible maintenant de saisir des unités pour les valeurs ayant un lien avec la mémoire ou le temps. Voici la liste des unités autorisées :
- unités mémoire
- kB pour Ko ;
- MB pour Mo ;
- GB pour Go.
- unités temps
- ms pour millisecondes ;
- s pour seconde ;
- min pour minute ;
- h pour heure ;
- d pour jour.
Voici un extrait de mon postgresql.conf :
...
shared_buffers = 24MB # min 128kB or max_connections*16kB
# (change requires restart)
#temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 5 # can be 0 or more
# (change requires restart)
# Note: increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
#work_mem = 1MB # min 64kB
#maintenance_work_mem = 16MB # min 1MB
#max_stack_depth = 2MB # min 100kB
...
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min # range 30s-1h
#checkpoint_warning = 30s # 0 is off
...
À noter d'ailleurs que les valeurs par défaut de shared_buffers et de max_fsm_pages ont été augmentées.
Nouvelles vues système
pg_prepared_statements contient la liste des instructions préparées pour la session en cours alors que pg_cursors contient la liste des curseurs ouverts.
pg_dump
Enfin, pg_dump est capable de gérer la sélection de plusieurs tables et/ou de plusieurs schémas. Les options -t et -n, déjà connues des anciennes versions, peuvent être saisies plusieurs fois pour ajouter le dump de plusieurs autres tables et schémas.
Deux nouvelles options permettent de faire l'opération inverse : -T exclue la sauvegarde de la table citée après, et -N exclue celui du schéma cité après.
pg_restore
Deux comportements faisaient défaut à pg_restore. Les utilisateurs les réclamaient depuis bien longtemps.
Notamment le fait que la restauration se fasse dans une transaction. Ceci est maintenant possible avec l'option -1 (--single-transaction pour l'option longue).
Il est aussi possible de ne pas faire l'ajout de données si la création de la table a échoué. Pour cela, il vous suffit d'ajouter l'option --no-data-for-failed-tables.
Contrib
Amélioration des anciens modules
TSearch2
- support de jeux de caractères multibyte
- support ooo+myspell
- support gin
pgstattuple
pgstattuple affiche des statistiques sur l'état des lignes d'une table. La fonction pgstattuple accepte en argument soit le nom de la table soit son numéro d'OID. Par exemple :
amarok=# \x Affichage étendu activé. amarok=# SELECT * FROM pgstattuple('tags'); -[ RECORD 1 ]------+-------- table_len | 1122304 tuple_count | 3976 tuple_len | 1083156 tuple_percent | 96.51 dead_tuple_count | 0 dead_tuple_len | 0 dead_tuple_percent | 0 free_space | 19968 free_percent | 1.78
On voit donc très bien que le VACUUM n'a pas de travail à faire sur cette table, qu'elle n'est pas fragmentée, etc. À partir de la version 8.2, ce module propose une nouvelle fonction : pgstatindex. Par exemple :
amarok=# SELECT * FROM pgstatindex('artist_tag'); -[ RECORD 1 ]------+------ version | 2 tree_level | 1 index_size | 81920 root_block_no | 3 internal_pages | 0 leaf_pages | 9 empty_pages | 0 deleted_pages | 0 avg_leaf_density | 86.89 leaf_fragmentation | 0
Là aussi, les informations sont particulièrement utiles pour savoir si l'index est fragmentée... auquel cas un REINDEX pourra être nécessaire.
Nouveaux modules
adminpack pour pgAdmin
pgAdmin dispose d'un module contrib permettant une administration plus simple. Notamment, ce module permet d'accèder en lecture/écriture aux fichiers de configuration. Auparavant, ce module était disponible sur le site web de pgAdmin. À partir de la 8.2, ce module fait directement partie du répertoire contrib des sources officiels, ce qui permettra une installation plus simple.
pgrowlocks
amarok=# select * from pgrowlocks('album'); locked_row | lock_type | locker | multi | xids | pids ------------+-----------+--------+-------+------+------ (0 lignes)
Trouver un exemple intéressant...
pg_freespacemap
Ce nouveau module propose deux fonctions : pg_freespacemap_pages et pg_freespacemap_relations. La première donne une liste des pages inclues dans la structure FSM. La seconde liste les relations qui y sont inclues.
amarok=# select * from pg_freespacemap_pages; reltablespace | reldatabase | relfilenode | relblocknumber | bytes ---------------+-------------+-------------+----------------+------- 1664 | 0 | 1262 | 0 | 7536 1663 | 1 | 2619 | 5 | 432 1663 | 1 | 2619 | 8 | 6072 1663 | 1 | 10737 | 5 | 2692 1663 | 1 | 2838 | 0 | 1516 ... amarok=# select * from pg_freespacemap_relations; reltablespace | reldatabase | relfilenode | avgrequest | interestingpages | storedpages | nextpage ---------------+-------------+-------------+------------+------------------+-------------+---------- 1663 | 16384 | 2608 | 219 | 0 | 0 | 0 1663 | 16384 | 2618 | 353 | 0 | 0 | 0 1663 | 16384 | 1249 | 208 | 0 | 0 | 0 1663 | 16384 | 1259 | 241 | 0 | 0 | 0 1663 | 16384 | 1247 | 243 | 0 | 0 | 0 ...
Scripts de désinstallation
Installer un module contrib dans une base est généralement assez simple. Il existe un script SQL qui ajoute les fonctions dans la base souhaitée (je dis fonctions, mais d'autres objets peuvent être créés comme des types ou des tables). Malheureusement, il n'existait pas encore de moyens simples pour les désinstaller. Dès cette version, un script de désinstallation est disponible pour chaque module contrib.
Par exemple, pour installer le module contrib adminpack, vous avez le script adminpack.sql. Pour le désinstaller, vous avez uninstall_adminpack.sql.
Divers
À comprendre
- HOT
- reordering outer joins
- multiple input aggregates
Liens importants
- Documentation française
- Notes de la version (en anglais actuellement).
