PostgreSQL 9.3

La version 9.3 de PostgreSQL est sortie le 9 septembre 2013.

Avec cette nouvelle version, la plus aboutie des bases de données libres s'enrichit encore de nouvelles fonctionnalités.

Principales nouveautés :

• tables externes modifiables ;
• le pilote pgsql_fdw permet l’agrégation de base de données PostgreSQL ;
• vues modifiables ;
• vue matérialisée ;
• jointure latérale ;
• fonctions JSON supplémentaires ;
• recherche indexée des expressions régulières ;
• checksums des pages disques ;
• utilisation de mmap pour réduire la consommation de mémoire partagée SysV.

Plus de détails dans la deuxième partie.

Sommaire

• Principales nouveautés :
  • Tables externes modifiables
  • Le pilote pgsql_fdw permet l’agrégation de base de données PostgreSQL
  • Vues modifiables
  • Vue matérialisée
  • Jointure latérale
  • Fonctions JSON supplémentaires
  • Recherche indexée des expressions régulières
  • Checksums des pages disques pour détecter les erreurs du système de fichiers
  • Utilisation de mmap pour réduire la consommation de mémoire partagée SysV
• Et aussi
  • Bascules d’urgence rapides (Failover) vers un serveur secondaire pour garantir la haute disponibilité de vos données.
  • Reconstruction d’un serveur secondaire uniquement via streaming
  • Performance et améliorations des verrous sur clefs étrangères
  • pg_dump parallèle pour des sauvegardes plus rapides
  • Des dossiers pour les fichiers de configuration
  • pg_isready : vérifier le statut de connexion d'un serveur PostgreSQL
  • Traitement en arrière plan et module.
  • Vues récursives
  • lock_timeout

Principales nouveautés :

Tables externes modifiables

La version 9.1 avait introduit le support des tables externes SQL/MED. En exemple, l'extension file_fdw permettait alors de définir une table externe basée sur un ficher CSV.
Avec cette nouvelle version, il est désormais possible, si le pilote implémente cette fonctionnalité, de modifier une table externe via les commandes SQL habituelles.
Cette fois-ci, le pilote en exemple est postgres_fdw.

Le pilote pgsql_fdw permet l’agrégation de base de données PostgreSQL

Pour compléter les fonctionnalités SQL/MED désormais accessibles en écriture, le pilote postgres_fdw est distribué officiellement dans le répertoire 'contrib'. Il vous permet d'accéder à partir d'une seule instance PgSGL à plusieurs autre instances distantes.
Exemple :

--Sur votre base distante, il existe une base appelée 'base_lointaine'
--qui possède une table appelée 'table_lointaine' qui contient un champ `TEXT`

--Configuration de l'accès:

--Chargement de l'extension
CREATE EXTENSION postgres_fdw;
--Création de la connexion
CREATE SERVER test_lointain FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'server_lointain', dbname 'base_lointaine');
CREATE USER MAPPING FOR PUBLIC SERVER test_lointain OPTIONS (password '');
CREATE FOREIGN TABLE table_lien(champ_text TEXT) SERVER test_lointain OPTIONS (table_name 'table_lointaine');

--Vous pouvez maintenant manipuler 'table_lointaine' comme une table locale via 'table_lien'
INSERT INTO table_lien VALUES('A ' || CURRENT_TIME || ' ago in a database far, far away');

Vues modifiables

Il est désormais possible de modifier la table affichée par la vue sans écrire de trigger. Il faut pour cela que la vue ne soit liée qu'à une seule table (ou une autre vue modifiable). Les commandes UPDATE et DELETE ne peuvent s'appliquer qu'aux lignes affichées par la vue (en cas d'utilisation d'une condition WHERE). Cependant, une instruction UPDATE peut rendre une ligne visible dans la vue invisible s'il ne satisfait plus aux conditions de la vue, de même, la commande INSERT peut insérer des lignes qui ne sont pas visibles dans la vue.

Vue matérialisée

Les vues matérialisées sont des vues dont le résultat est stocké dans une table physique. Cela permet d'éviter d'exécuter la requête de la vue à chaque accès. L'inconvénient est que cette vue n'est pas mise à jour automatiquement quand la ou les tables de référence sont modifiées. C'est donc très similaire à la commande CREATE TABLE AS mais comme la requête pour remplir la vue est stockée, la mise à jour est facilitée. Pour la mise à jour, il vous suffit d'exécuter la requête SQL suivante : REFRESH MATERIALIZED VIEW nom_de_votre_vue;

Jointure latérale

Lorsque vous utilisiez des requêtes contenant des sous-requêtes, il n'était pas possible de faire référence à une des tables précédemment mentionnées dans la cause FROM, chaque sous-requête étant évaluée indépendamment. La prise en charge du standard SQL LATERAL lève désormais cette limitation.

--Cette requête (plutôt idiote) ne fonctionne pas
select player_rank.name, rang.rank_name
from player_rank,(select rank, rank_name from rank where player_rank.rank= rank.rank) rang
--Erreur:
--ERROR:  invalid reference to FROM-clause entry for table "player_rank"
--LINE 2: ...om player_rank,(select rank, name from rank where player_ran...
--                                                           ^
--HINT:  There is an entry for table "player_rank", but it cannot be referenced from this part of the query.

--Avec LATERAL elle fonctionne
select player_rank.name, rang.rank_name
from player_rank, LATERAL (select rank, rank_name from rank where player_rank.rank= rank.rank) rang

Fonctions JSON supplémentaires

La précédente version n'offrait que deux fonctions pour convertir un tableau ou une ligne en JSON. Cette version introduit des opérateurs JSON comme -> pour obtenir l'objet contenu dans le champ spécifié. Mais aussi de nouvelles fonctions permettant de manipuler plus facilement les données JSON comme une fonction pour avoir le nombre d'éléments dans un tableau JSON (json_array_length(json)) ou json_each(json) qui permet de développer un objet en un ensemble de clefs/valeurs, permettant des requêtes du style : select * from json_each('{"a":"foo", "b":"bar"}')

Recherche indexée des expressions régulières

Il est possible de créer des index à l'aide de l'extension pg_trgm. La création d'un index entraîne alors la création de trigrammes qui peuvent être utilisés pour accélérer certaines recherches réalisées avec des expressions régulières.

Checksums des pages disques pour détecter les erreurs du système de fichiers

Le contrôle d'intégrité des pages peut désormais être effectué. Ce comportement est global, a un impact important sur les performances, mais est désactivé par défaut.

Utilisation de mmap pour réduire la consommation de mémoire partagée SysV

PostgreSQL utilise désormais la mémoire partagée de type Posix et mmap pour gérer la mémoire. Principale conséquence, les administrateurs ne devront plus avoir à modifier les réglages SysV pour obtenir de bonnes performances.

Et aussi

Bascules d’urgence rapides (Failover) vers un serveur secondaire pour garantir la haute disponibilité de vos données.

La promotion d'un serveur secondaire en serveur primaire se fait en moins d'une seconde.

Reconstruction d’un serveur secondaire uniquement via streaming

La reconstruction ne nécessite plus d'être faite à partir des fichiers WAL. Elle peut désormais se faire en flux.

Performance et améliorations des verrous sur clefs étrangères

Deux nouveaux types de verrous :
SELECT FOR KEY SHARE et SELECT FOR NO KEY UPDATE, apparaissent pour permettre un verrouillage plus fin des données. Les actions concernant les clés étrangères utilisent désormais ces deux types de verrous pour de meilleures performances, en évitant les verrouillages non nécessaires.

pg_dump parallèle pour des sauvegardes plus rapides

pg_dump accepte désormais un paramètre -j nbjobs (ou --jobs=nbjobs) qui permet de spécifier le nombre de tables qui vont être sauvegardées en parallèle. Il est évident que cela fonctionne uniquement avec la sauvegarde de type dossier (contrairement à sauvegarder toute la base de données dans un fichier).

Des dossiers pour les fichiers de configuration

Une nouvelle directive vous permet de spécifier un répertoire dans lequel tous les fichiers '.conf' seront lus comme fichiers de configuration supplémentaires.

pg_isready : vérifier le statut de connexion d'un serveur PostgreSQL

pg_isready est un outil qui vérifie le statut de connexion d'un serveur PostgreSQL.
Le code de sortie indique le résultat de la vérification :
0 : connexion acceptée
1 : connexion rejetée
2 : pas de réponse
3 : pas de tentative de connexion faite (problème de paramètre de connexion)

Traitement en arrière plan et module.

Les modules peuvent être créés en indiquant que leur traitement doit se faire en arrière-plan.

Vues récursives

Une sélection récursive en SQL ce n'est déjà pas clair… Maintenant c'est aussi disponible pour les vues !

lock_timeout

Vous permet de spécifier la durée d'attente avant de déclencher un timeout lors de l'acquisition d'un lock.

Aller plus loin

• What's_new_in_PostgreSQL_9.3 (272 clics)
• PostgreSQL_9.3_Blog_Posts (74 clics)