Introduction

Cette version est disponible en version beta depuis le 15 avril 2009 et est une version majeure. Des fonctionnalités extrêmement importantes ont été ajoutées, tant au niveau du langage SQL qu'au niveau de l'administration. Contrairement au passage de 8.2 vers 8.3 (suppression de nombreuses conversions implicites), il y a peu de risques de régression entre 8.3 et 8.4.

Par ailleurs, si vous voulez un résumé des fonctionnalités ajoutées au fur et à mesure des versions, cette page (en anglais) fournit un excellent résumé : http://www.postgresql.org/about/featurematrix

Pour permettre à chaque audience de savoir ce qui la concerne, chaque fonctionnalité se voit affecter une couleur :

Jaune pour ce qui concerne les améliorations pour les développeurs.

Bleu pour ce qui concerne les améliorations pour les administrateurs.

Vert pour ce qui concerne tout le monde.

Window Functions (fonctions de fenêtrage)

Le but d'une fonction de fenêtrage est de fournir des fonctions de regroupement 'glissantes' et de ne pas regrouper les enregistrements dans la sortie de la requête : une fonction de regroupement classique (SUM par exemple) donne un seul résultat pour tout un groupe (la somme des salaires des employés d'une division par exemple). Les fonctions de fenêtrage permettront d'obtenir la somme des salaires des employés d'une division, tout en ne faisant pas le regroupement. Cela permettrait en une seule requête de comparer le salaire d'un employé avec la moyenne de son département.

La doc officielle : http://docs.postgresql.fr/8.4/tutorial-window.html et http://docs.postgresql.fr/8.4/sql-expressions.html#syntax-window-functions.

On peut grâce à ces fonctions calculer le rang d'un enregistrement, éclater une liste suivant l'appartenance d'une valeur à un centile, etc ...

Elles sont très pratiques en contexte d'infocentre.

Voici un exemple simple (celui du paragraphe explicatif) :

Soit une table employe :

\d employe
Table "public.employe"
Column   | Type              | Modifiers
---------+-------------------+----------
nom      | character varying |
division | character varying |
salaire  | numeric           |


SELECT * from employe ;
nom   | division | salaire
------+----------+--------
marc  | dept a   | 100
jean  | dept a   | 110
luc   | dept a   | 90
brian | dept b   | 100
pete  | dept b   | 150

Pour obtenir le résultat demandé (donner, pour chaque utilisateur,le salaire moyen de sa division), on peut écrire cette requête :

SELECT nom,division,salaire,salaire_moyen
FROM employe
JOIN (
      SELECT AVG(salaire) AS salaire_moyen,division
      FROM employe
      GROUP BY division)
      AS temp
    USING (division);

nom   | division | salaire | salaire_moyen
------+----------+---------+---------------------
marc  | dept a   | 100     | 100.0000000000000000
jean  | dept a   | 110     | 100.0000000000000000
luc   | dept a   | 90      | 100.0000000000000000
brian | dept b   | 100     | 125.0000000000000000
pete  | dept b   | 150     | 125.0000000000000000

Dans ce cas simple, c'est encore lisible. Évidemment, si on veut en plus le salaire maximum, le classement dans la division, la requête devient vite lourde, et demande en plus plusieurs passes sur la table.

Avec les nouvelles fonctions de fenêtrage, on l'écrit comme suit :

SELECT nom,
       division,
       salaire,
       AVG(salaire) OVER (PARTITION BY division) AS salaire_moyen
FROM employe;

nom  | division | salaire | salaire_moyen
-----+----------+---------+---------------
marc | dept a   | 100     | 100.0000000000000000
jean | dept a   | 110     | 100.0000000000000000
luc  | dept a   | 90      | 100.0000000000000000
brian| dept b   | 100     | 125.0000000000000000
pete | dept b   | 150     | 125.0000000000000000

On indique à PostgreSQL sur quelle partition effectuer le fenêtrage avec le mot clé partition.

Si on veut en plus le classement de chaque employé dans sa division :

SELECT nom,
       division,
       salaire,
       AVG(salaire) OVER (PARTITION BY division) AS salaire_moyen,
       RANK() OVER (PARTITION BY division ORDER BY salaire DESC) AS rang_division
FROM employe;

nom  | division | salaire | salaire_moyen        | rang_division
-----+----------+---------+----------------------+-----------
jean | dept a   | 110     | 100.0000000000000000 | 1
marc | dept a   | 100     | 100.0000000000000000 | 2
luc  | dept a   | 90      | 100.0000000000000000 | 3
pete | dept b   | 150     | 125.0000000000000000 | 1
brian| dept b   | 100     | 125.0000000000000000 | 2

On peut donc donner un ordre avec un order by dans la clause OVER. On remarque aussi que RANK ne prend pas de paramètre (ce qui est logique).

On peut continuer à complexifier : on veut le rang global du salaire dans la table (on ne partitionne plus sur une valeur, mais on continue le tri):

SELECT nom,
       division,
       salaire,
       AVG(salaire) OVER (PARTITION BY division) AS salaire_moyen,
       RANK() OVER (PARTITION BY division ORDER BY salaire DESC) AS rang_division,
       RANK() OVER (ORDER BY salaire DESC) AS rang_global
FROM employe;

nom  | division | salaire | salaire_moyen        | rang_division | rang_global
-----+----------+---------+----------------------+---------------+------------
pete | dept b   | 150     | 125.0000000000000000 | 1             | 1
jean | dept a   | 110     | 100.0000000000000000 | 1             | 2
marc | dept a   | 100     | 100.0000000000000000 | 2             | 3
brian| dept b   | 100     | 125.0000000000000000 | 2             | 3
luc  | dept a   | 90      | 100.0000000000000000 | 3             | 5

Ce genre de requête est très pénible à écrire sans les fonctions de fenêtrage ...

Attention tout de même aux performances : les fonctions de fenêtrage travaillent sur des données triées (les partitions), ce qui fait que les données de la requête subiront des tris (qui peuvent être nombreux si il y a de nombreuses méthodes de partitionnement différentes utilisées dans la même requête). Par comparaison, la requête initiale (avec la jointure) peut faire des aggregats par hash, qui dans certains contextes seront plus performants. Ces fonctions de fenêtrage ont donc tendance à restreindre les choix de l'optimiseur SQL. Il faut donc être prudent sur l'utilisation de ces fonctions sur un gros volume de données.

Au niveau syntaxique, on peut déclarer une clause de partition de façon globale pour pouvoir ensuite l'utiliser plusieurs fois par son nom dans la requête :

SELECT nom,
       division,
       salaire,
       AVG(salaire) OVER (w) AS salaire_moyen,
       RANK() OVER (w) AS rang
FROM employe
WINDOW w AS (PARTITION BY division ORDER BY salaire DESC);

CTE, Common Table Expressions (Expressions de tables communes)

Le but des CTE est de fournir des déclarations communes de tables au début d'une requête, avant son corps proprement dit.

Nb: les exemples ci-dessous sont tirés de la documentation PostgreSQL.

Ceci est utilisé dans 2 cas :

  • Pour définir de la 'récursion' (l'équivalent des 'CONNECT BY' d'Oracle) selon la nomenclature SQL. Il s'agit en fait d'un processus itératif (voir la documentation).
WITH RECURSIVE parties_incluses(sous_partie, partie, quantite) AS (
   SELECT sous_partie, partie, quantite FROM parties WHERE partie = 'notre_produit'
 UNION ALL
   SELECT p.sous_partie, p.partie, p.quantite
   FROM parties_incluses pr, parties p
   WHERE p.partie = pr.sous_partie
)
SELECT sous_partie, SUM(quantite) as quantite_totale
FROM parties_incluses
GROUP BY sous_partie

Ceci est assez complexe à utiliser, et potentiellement dangereux : il est très facile d'écrire une requête qui ne s'arrête pas. Il est donc important de bien étudier la documentation avant de se lancer dans des requêtes récursives : http://docs.postgresql.fr/8.4/queries-with.html

  • Pour déclarer une requête qui sera réutilisée plusieurs fois par la suite dans la requête principale, et limiter ainsi le nombre de ses exécutions (pour raison de performance et de lisibilité donc).
WITH ventes_regionales AS (
   SELECT region, SUM(montant) AS ventes_totales
   FROM commandes
   GROUP BY region
),
     meilleures_regions AS (
   SELECT region
   FROM ventes_regionales
   WHERE ventes_totales > (SELECT SUM(ventes_totales)/10 FROM ventes_regionales)
)
SELECT region,
       produit,
       SUM(quantite) AS unites_produit,
       SUM(montant) AS ventes_produit
FROM commandes
WHERE region IN (SELECT region FROM meilleures_regions)
GROUP BY region, produit;

Les CTE sont déclarées avec le mot clé WITH : http://docs.postgresql.fr/8.4/queries-with.html

Free Space Map automatique

La Free Space Map est un mécanisme permettant à PostgreSQL de mémoriser la liste des zones réutilisables d'un fichier de données (équivalent des freelists d'Oracle). Jusque là, cette free space map était une zone mémoire partagée, donc de taille fixe, ce qui pouvait poser des problèmes de dimensionnement de celle ci : dès que la free space map était pleine, il devenait impossible de stocker de nouveaux blocs dans celle ci, et des tables pouvaient grossir de façon incontrôlable.

En 8.4, la Free Space Map n'est plus une zone mémoire mais une extension logique de chaque table (un « fork » dans la nomenclature PostgreSQL), chaque fork pouvant croître de façon autonome (il a son propre fichier géré comme les fichiers des tables et index). Les deux paramètres max_fsm_pages et max_fsm_relation disparaissent, supprimant avec eux une des principales sources complexité et d'erreur de l'administration de PostgreSQL.

Les fichiers de FSM sont stockés avec les fichiers des tables, avec le même identifiant numérique, mais terminé par le suffixe FSM. Par exemple, la table de relfilenode 2610 aura un fichier FSM 2610_fsm.

Visibility Map

L'apparition du concept de fork (cf paragraphe précédent) dans PostgreSQL a permis de rajouter de nouveaux bitmaps d'attributs sur les tables. Le premier est la visibility map. Elle fournit la liste des pages dont TOUS les enregistrements sont visibles. Vacuum active le bit d'une page quand il a fini de la traiter et que tous les enregistrements qui sont dedans sont validés (cela sera de loin le cas le plus courant dans une table en production). Toute transaction qui invalide un enregistrement dans une page désactive le bit. Ceci comporte plusieurs intérêts:
  • Le premier est de permettre un VACUUM partiel : il n'est plus nécessaire de passer sur toute la table à chaque vacuum, puisqu'on a la liste des pages qui contiennent potentiellement des enregistrements à nettoyer (celles dont le bit n'est plus actif). Il n'est plus nécessaire de nettoyer les autres. Cela réduit la durée d'un VACUUM et la lie à la quantité réelle de travail à effectuer. On pourra donc par la même occasion faire des VACUUM plus rapprochés, et gérer beaucoup plus facilement les tables de grande taille (vacuum ne sera probablement plus une raison pour la partitionner). Attention,même en cas de vacuum partiel, il reste nécessaire d'inspecter en entier tous les index.
  • Le second n'est pas encore implémenté mais le sera probablement assez rapidement.
    Les entrées d'index ne contiennent pas d'information sur la visibilité des enregistrements qu'elles pointent. Ceci oblige PostgreSQL, lors de parcours d'index, à aller consulter la page de la table pour vérifier la visibilité de l'enregistrement qu'il vient de trouver. Ceci empêche quelques optimisations, que font Oracle et SQL Server par exemple qui peuvent se contenter de parcourir un index sans aller voir la table si seules les colonnes indexées sont utilisées (pour un count(*) sur une table entière par exemple).
    Avec ce bitmap de visibilité, il sera possible de confirmer la plupart du temps la visibilité d'un enregistrement d'index simplement par la consultation du bit associé à la page de l'enregistrement qu'il pointe : si l'ensemble de la page est visible, l'enregistrement est visible, et il n'est donc pas la peine d'aller regarder le contenu de la page. Sachant que 99% des pages d'une table seront probablement totalement visibles à chaque instant (surtout que le vacuum partiel permettra d'augmenter la fréquence des vacuum), le gain sera probablement très net sur ces requêtes particulières, et permettra de se débarrasser d'un point faible de PostgreSQL.

Les fichiers de Visibility Map sont stockés avec les fichiers des tables, avec le même identifiant numérique, mais terminé par VM. Par exemple, la table de relfilenode 2610 aura un fork 2610_vm.

Locale par base

On peut maintenant créer des bases d'encodage et de collation/catégorisation (ordre de tri dans les index, le lc_collate, et classification/casse avec le lc_ctype) différentes.

infocentre_dte=# CREATE DATABASE test1 ENCODING='LATIN9' LC_COLLATE='fr_FR@euro' LC_CTYPE='fr_FR@euro' TEMPLATE=template0;
CREATE DATABASE
infocentre_dte=# CREATE DATABASE test2
ENCODING='UTF8' LC_COLLATE='fr_FR.UTF8' LC_CTYPE='fr_FR.UTF8' TEMPLATE=template0;
CREATE DATABASE

'Nb:' quand on crée une base qui n'a pas les mêmes collation/ctype que la base template1, il faut repartir du template0 (et donc recréer dans la nouvelle base tout ce qu'on a ajouté à la template1). Sur un cluster, il est peu probable qu'on ait plus de 2 ou 3 encodages différents. Il est donc intéressant de créer des templates pour ces quelques combinaisons, et utiliser ensuite ces templates pour la création des bases réelles.

List of databases
Name      | Owner | Encoding | Collation   | Ctype       | Access privileges
----------+-------+----------+-------------+-------------+------------------
postgres  | marc  | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 |
template0 | marc  | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 | =c/marc
                                                         : marc=CTc/marc
template1 | marc  | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 | =c/marc
                                                         : marc=CTc/marc
test1     | marc  | LATIN9   | fr_FR@euro  | fr_FR@euro  |
test2     | marc  | UTF8     | fr_FR.UTF8  | fr_FR.UTF8  |

Fonctions de monitoring

  • Dans le cas d'un deadlock, PostgreSQL retourne les textes de toutes les requêtes en cours durant le blocage dans les traces.
ERROR: deadlock detected
DETAIL: Process 13686 waits for ShareLock on transaction 8710; blocked by process 13692.
        Process 13692 waits for ShareLock on transaction 8709; blocked by process 13686.
        Process 13686: DELETE FROM test1 where a=2;
        Process 13692: DELETE FROM test1 where a=1;
HINT: See server log for query details.
STATEMENT: DELETE FROM test1 where a=2;
  • pg_conf_load_time() permet de connaître la dernière date de rechargement du fichier de configuration
  • pg_terminate_backend() permet de tuer une session proprement (SIGTERM le permet aussi maintenant). Avant, aucune fonction supportée ne permettait de le faire, même si SIGTERM fonctionnait quasiment à chaque fois. Cette fonction est à distinguer de pg_cancel_backend() qui arrête la requête en cours d'une session.
  • Traçage des nombres d'appels et temps moyens d'exécution des procédures stockées, via la vue pg_stat_user_functions. Pour cela, il faut aussi activer track_functions dans la configuration, qui peut valoir all, pl ou none. All correspond à tout tracer (C, PL, SQL), pl uniquement les langages procéduraux, none rien du tout. Nb : une fonction SQL suffisamment simple pour être inlinée dans la requête appelante ne sera pas comptabilisée.
  • On peut maintenant spécifier la taille maximum d'une requête affichée dans pg_stat_activity via track_activity_query_size. Ce paramêtre n'est positionnable qu'au démarrage du serveur, puisqu'il réserve de la mémoire. La valeur par défaut est 1024 caractères. C'est cette taille qui est aussi utilisée (voir plus bas) pour pg_stat_statements.

Voici un extrait des traces quand cette fonctionnalité est activée :

LOG: statement: BEGIN
LOG: statement: DECLARE _psql_cursor NO SCROLL CURSOR FOR
        SELECT * from isilog.actions limit (select 100);
LOG: statement: FETCH FORWARD 100 FROM _psql_cursor
LOG: statement: FETCH FORWARD 100 FROM _psql_cursor
LOG: statement: CLOSE _psql_cursor
LOG: duration: 0.047 ms plan:
      Limit (cost=0.01..17013.93 rows=416152 width=347)
        InitPlan 1 (returns $0)
          -> Result (cost=0.00..0.01 rows=1 width=0)
        -> Seq Scan on actions (cost=0.00..170139.21 rows=4161521 width=347)
STATEMENT: CLOSE _psql_cursor
LOG: statement: COMMIT

Evidemment, pour les besoins de l'exemple, la durée minimum d'une requête déclenchant les traces du plan a été abaissée à une valeur ridiculement faible : on ne va pas tracer les plans de requêtes durant 50 microsecondes, le coût serait trop élevé...

  • Contrib : pg_stat_statements
    Suivre cette doc : http://docs.postgresql.fr/8.4/pgstatstatements.html
    Ce module permet d'avoir des statistiques similaires à celles d'Oracle (dans V$SQLAREA, perfstat ou rapports AWR). Il consomme de la mémoire (pour stocker ses statistiques) et un peu de ressources processeur (l'impact n'est pas mesurable).
SELECT * from pg_stat_statements order by total_time desc;
-RECORD 1-
userid     | 10
dbid       | 16384
query      | DECLARE _psql_cursor NO SCROLL CURSOR FOR
           : SELECT * from isilog.actions limit 100000;
calls      | 2
total_time | 0.106574
rows       | 0
-RECORD 2-
userid     | 10
dbid       | 16384
query      | SELECT n.nspname as "Schema",
           : c.relname as "Name",
           : CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S'
           : THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
           : pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
           : FROM pg_catalog.pg_class c
           : LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
           : WHERE c.relkind IN ('r','v','S','')
           : AND n.nspname <> 'pg_catalog'
           : AND n.nspname <> 'information_schema'
           : AND n.nspname !~ '^pg_toast'
           : AND pg_catalog.pg_table_is_visible(c.oid)
           : ORDER BY 1,2;
calls      | 1
total_time | 0.023864
rows       | 8
-RECORD 3--
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      | 1
total_time | 0.019265
rows       | 22

Pour un article plus détaillé (en anglais) sur le sujet : http://www.depesz.com/index.php/2009/01/13/waiting-for-84-pg_stat_statements/

Attention, il vaut mieux augmenter track_activity_query_size, si on veut les requêtes en entier dans cette vue. Par ailleurs, comme pour Oracle, cette fonctionnalité ne sera vraiment utile que dans le cas où le nombre de requêtes différentes est faible (requêtes préparées).

Archivage et sauvegardes

  • Améliorations de la sauvegarde en ligne et du Warm Standby (réplication à chaud par fichiers xlog)
    • pg_stop_backup ne rend la main à l'appelant qu'une fois que les fichiers d'archive correspondant à la période du backup sont correctement archivés. Cela simplifie la sauvegarde en ligne et en garantit le bon fonctionnement.
    • pg_start_backup dispose d'un paramètre optionnel booléen qui permet de déterminer si on veut que le checkpoint se fasse à vitesse normale (false), c'est-à-dire à la vitesse paramétrée par checkpoint_completion_target, ou à vitesse maximale (true). La valeur par défaut est false.
  • pg_dump --data-only réordonne les imports pour respecter les contraintes d'intégrité (intégrer les données de la clé étrangère avant les données de la table), dans la mesure du possible. S'il y a des dépendances circulaires, un message NOTICE est affiché.
  • pg_restore dispose maintenant d'une option -j/--jobs (comme make par exemple), qui permet d'augmenter le parallélisme des restaurations (dans la limite du raisonnable).
    Il faut préciser un nombre après le j (-j8 par exemple pour 8 sessions de restauration en parallèle).
    Ceci parallélise la restauration des données de plusieurs tables, ainsi que les créations d'index/contraintes. Si la restauration est limitée par le processeur en temps normal (c'est souvent le cas), on peut paralléliser sur plus d'un processeur le processus de restauration grâce à cette option.

Diverses améliorations de performance

L'optimiseur SQL a été amélioré pour pouvoir :

  • Utiliser des hachages pour SELECT DISTINCT, UNION, INTERSECT, EXCEPT. Attention, cela entraîne que leur résultat ne sera plus forcément trié (utiliser un ORDER BY) comme c'était le cas auparavant (mais c'était un effet de bord de l'algorithme).
  • Si c'est rentable, transformer les EXISTS/NOT EXISTS/IN/NOT IN dans la forme la plus efficace (c'est à dire convertir un EXIST en IN équivalent et vice versa suivant les statistiques)
  • Supporter des statistiques plus fines : la valeur par défaut de default_statistics_target est passée de 10 à 100, avec une valeur maxi passée de 1000 à 10000. Il ne sera par défaut plus nécessaire d'y toucher, la valeur 100 étant très raisonnable (contrairement à 10, qui générait des histogrammes trop imprécis).
  • Faire les exclusion de contrainte (constraint_exclusion) par défaut pour les partitions.
  • Faire de la lecture anticipée sur disque pour certains plans d'exécution : les 'bitmap index scans' déclencheront une lecture de nombreux blocs à la fois sur le disque afin que le système d'exploitation optimise les accès disques : paramètre effective_io_concurrency
  • Substituer les fonctions SQL simples (Inline) dans les requêtes les utilisant par leur code, afin qu'elles ne soient plus des boîtes noires et que l'optimiseur puisse faire un travail plus précis.
  • Prendre en compte un paramètre du type 'FIRST_ROWS' d'Oracle : on peut indiquer quel pourcentage d'un curseur sera probablement récupéré, d'une façon globale, et par session, afin qu'il optimise le plan d'exécution. Il s'agit de cursor_tuple_fraction, paramétré par défaut à 0.1.
  • Optimisation des performances d'insertion dans les index GIN (utilisés entre autres pour la recherche FULL TEXT) http://docs.postgresql.fr/8.4/gin-implementation.html

Module Optionnel : champs texte insensible à la casse

Le module citext permet de disposer d'un type texte insensible à la casse (pour les recherches de chaînes de caractères).

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

Une fois le module citext activé dans la base cible, on l'utilise comme suit :

infocentre_dte=# CREATE TABLE test2 (a citext);
CREATE TABLE
infocentre_dte=# ALTER TABLE test2 ADD primary key (a);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "test2_pkey" for table "test2"
ALTER TABLE
infocentre_dte=# INSERT INTO test2 values ('a');
INSERT 0 1
infocentre_dte=# INSERT INTO test2 values ('A');
ERROR: duplicate key value violates unique constraint "test2_pkey"
infocentre_dte=# SELECT * from test2 where a='A';
a
---
a
(1 row)

On a donc un nouveau type citext (Case Insensitive Text), insensible à la casse pour les comparaisons (mais qui conserve la casse saisie).

Le mot clé AS devient optionnel

Le mot clé AS devient optionnel pour les alias de colonnes, sauf si le nom de l'alias est un mot clé réservé. Au passage, il faut signaler la fonction pg_get_keywords(), qui permet d'avoir la liste de ces mots réservés (il y en a environ 400), qui sont aussi listés ici : http://docs.postgresql.fr/8.4/sql-keywords-appendix.html.

Amélioration des langages PL

  • Fonctions SETOF et requêtes
    Le résultat de procédures PL stockées retournant plusieurs enregistrements (des setof via RETURN NEXT) pourra être utilisé dans des jointures. Jusque là, ce n'était possible qu'avec les fonctions purement SQL.
  • Amélioration de la gestion des erreurs en PL
    Voir cette doc : http://docs.postgresql.fr/8.4/plpgsql-errors-and-messages.html
    A retenir:
    On peut maintenant dans un RAISE définir des codes d'erreur personnalisés ou mettre en place les codes retours standard (SQLSTATE,ERRCODE), et des indices pour la résolution des problèmes (HINT). On peut avec ces options remonter proprement et précisément les erreurs d'une fonction à la fonction ou la requête appelante.

Trigger sur l'évènement TRUNCATE

  • On peut maintenant écrire des triggers sur évènement TRUNCATE. Il s'agit bien sûr de triggers 'FOR EACH STATEMENT'.
  • On peut aussi fournir un argument RESTART/CONTINUE IDENTITY à la commande TRUNCATE, afin de réinitialiser les séquences qu'utilise la table, et une nouvelle permission TRUNCATE spécifique est maintenant disponible (pour la séparer de DELETE).

Améliorations de EXPLAIN

Explain verbose affiche maintenant les colonnes de sorties de chaque noeud du plan. Explain quant à lui affiche les sous-plans et plans initiaux avec des labels individuels pour améliorer la lisibilité de l'ensemble.

Un exemple d'explain verbose :

explain VERBOSE SELECT id_service,octets,date,id_sonde from liste_releves natural join detail_releve;
QUERY PLAN
----------------------------------------------------------------------------------------
Hash Join (cost=1824.09..800688.50 rows=16606047 width=24)
  Output: detail_releve.id_service, detail_releve.octets, liste_releves.date, liste_releves.id_sonde
  Hash Cond: (detail_releve.id_releve = liste_releves.id_releve)
  -> Seq Scan on detail_releve (cost=0.00..271831.47 rows=16606047 width=20)
       Output: detail_releve.id_service, detail_releve.octets, detail_releve.id_releve
  -> Hash (cost=859.93..859.93 rows=52493 width=20)
       Output: liste_releves.date, liste_releves.id_sonde, liste_releves.id_releve
       -> Seq Scan on liste_releves (cost=0.00..859.93 rows=52493 width=20)
            Output: liste_releves.date, liste_releves.id_sonde, liste_releves.id_releve

Droits par colonnes

On peut maintenant donner des droits d'accès aux tables et vues par colonne :

CREATE USER user_test;

CREATE TABLE test3 (a int, b varchar);

GRANT SELECT (a) ON test3 to user_test;

Puis on se connecte en tant que user_test :

SELECT * from test3;
ERROR: permission denied for relation test3
infocentre_dte=> SELECT a from test3;
a
---
1
2
(2 rows)

pg_hba.conf

Améliorations du traitement du fichier pg_hba.conf :
  • Si le fichier est erroné, il n'est pas chargé et la version précédente est gardée (un message d'erreur est évidemment affiché dans les traces)
  • Toutes les erreurs d'analyse du fichier sont affichées, et pas seulement la première, ce qui permet de gagner du temps dans la correction de celles-ci.

CREATE OR REPLACE VIEW et les colonnes

On peut maintenant rajouter des colonnes à une vue par la commande CREATE OR REPLACE VIEW. Elles sont rajoutées à la fin de la vue. Jusqu'alors, il fallait détruire puis recréer la vue, ce qui était très problématique dans le cas de vues empilées (il fallait détruire toutes les vues dépendant de la vue à modifier, puis les recréer).

Rétrocompatibilité psql

Jusqu'alors, il fallait utiliser la version du client psql compatible avec la base à laquelle on se connectait, sous peine d'avoir des problèmes avec les commandes internes.

A partir de la version 8.4, le client psql identifie la version de la base et exécute les interrogations de dictionnaire \d appropriées à la base. psql est donc compatible avec les bases de versions 7.4 à 8.4.

PS : Merci  à Sigma Informatique (mon employeur) pour m'avoir autorisé à travailler sur ce document et à le publier...