PostgreSQL 9.3

75
10
sept.
2013
Base de données

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.

PostgreSQL

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

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.

  • # zut

    Posté par . Évalué à 2.

    Il y a 2 fois 9.3 dans la première phrase

    • [^] # Re: zut

      Posté par (page perso) . Évalué à 3.

      Corrigé.

      « Rappelez-vous toujours que si la Gestapo avait les moyens de vous faire parler, les politiciens ont, eux, les moyens de vous faire taire. » Coluche

  • # Une tuerie

    Posté par . Évalué à 9.

    Ça fait tellement plaisir de voir des fonctionnalités aussi avancées que LATERAL, JSON, les tables externes !

    On dirait que la chute de MySQL a donné un coup de fouet à PgSQL !

  • # ATTENTION !!!

    Posté par . Évalué à 10.

    Je suis un grand fan de PostgreSQL et il n'y a jamais eu aucun problème de mise à jour avec PostgreSQL MAIS ce coup ci le modèle de mappage mémoire a été modifié. C'est une excellente nouvelle parceque ca va éviter à tous les admins de régler leur shmax aux petits oignons pendant des jours, mais ca implique aussi qu'il y a potentiellement des bugs vicieux qui peuvent se rpoduire sur certaines configs.

    A garder un bon moment en test avant de migrer donc.(Plus que d'habitude en tout cas).

    Ceci dit ça n'enlève rien à ce qui est le meilleur framework de données libre au monde.

    • [^] # Re: ATTENTION !!!

      Posté par (page perso) . Évalué à 2.

      Pas d'accord: Il y en a d'autre mais pas forcément dans le monde SQL ;)

      • [^] # Re: ATTENTION !!!

        Posté par . Évalué à 4.

        Pas d'accord: Il y en a d'autre mais pas forcément dans le monde SQL ;)

        PostgreSQL est aussi une excellente base de données hiérarchiques et un très bon datastore clef/valeur.

  • # Traitement en arrière plan et module

    Posté par (page perso) . Évalué à 3.

    Trop tard maintenant que c'est en ligne, mais la section:

    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.

    ne ferait pas référence aux Custom Background Workers ?

    Une fonctionnalité qui permet de créer ses propres "plugins" gérés par le processus PostgreSQL et permettant un accès complet à toutes les données du cluster ouvrant par exemple la porte aux développements d'outils tiers pour le monitoring, le logging, etc.

    • [^] # Re: Traitement en arrière plan et module

      Posté par . Évalué à 1.

      Tout à fait.
      Il s'agit en fait de processus gérés par les serveur de manière à ne pas pénaliser la performance. Ils peuvent avoir un accès étendu au données du serveur : mémoire partagée, connexions directe à la base ou connexions via la lib.

  • # support de Python3

    Posté par (page perso) . Évalué à 1.

    Je suis avec beaucoup d'intérêt le développement de PostgreSQL mais je crois que son support par Python3 est encore balbutiant. Quelqu'un pourrait-il m'en dire plus ? Merci !

    Trust the Python !

  • # Reconstruction d’un serveur secondaire uniquement via streaming

    Posté par . Évalué à 1.

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

    Est-ce que quelqu'un a un pointeur là dessus ?
    Dans le wiki je lis également qu'on pourra faire du streaming sur des OSes différents. Ca signifie du 64bits vers une machine 32bits par ex ?

  • # Hints ?

    Posté par . Évalué à 2.

    Bonjour,

    je redécouvre PostgreSQL.

    => Est-ce que des hints à l'Oracle sont possibles ?
    Très utiles pour forcer l'optimiseur sur une décision, lorsque les stats ne sont pas à jour, et ne peuvent l'être pour x raisons.

    => Existe t-il un équivalent au hint parallel sur un select ?

    Exemple, un select sur 4 cpus :

    SELECT /*+ parallel(c,4) */ *
    FROM sh.customers c
    ORDER BY cust_first_name, cust_last_name, cust_year_of_birth

    • [^] # Re: Hints ?

      Posté par . Évalué à 7.

      Le 1er et le 3ème lien google "postgresql hint" répondent assez bien à "pourquoi il y en a pas?" et "comment qu'on fait alors?":
      * http://wiki.postgresql.org/wiki/OptimizerHintsDiscussion
      * http://blog.2ndquadrant.com/hinting_at_postgresql/

      • [^] # Re: Hints ?

        Posté par . Évalué à -1.

        Merci pour les liens, mais tu es obligé d'être aussi agréable ?
        Je connaissais le premier, et les raisons présentées me laisse dubitatif.

        Mon post était plus pour avoir un retour d'expérience sur une migration Oracle => PostgreSQL. L'existence d'hints étant pour moi un prérequis, et je me demandais comment on pouvait s'en passer.

        • [^] # Re: Hints ?

          Posté par (page perso) . Évalué à 3.

          Mon post était plus pour avoir un retour d'expérience sur une migration Oracle => PostgreSQL. L'existence d'hints étant pour moi un prérequis, et je me demandais comment on pouvait s'en passer.

          Quand on migre une base d’une version d’Oracle à une autre, on commence déjà par virer les hints des requêtes, parce que beaucoup de choses ayant évolué par ailleurs, il y a peu de chances qu’ils soient encore pertinents. C’est en rejouant les requêtes les plus fréquentes qu’on va voir s’il y en a qui ont besoin d’être particulièrement optimisées, et ça ne sera pas forcément les mêmes qu’avec la version précédente du SGBD, et, dans ce cas, la solution ne passe pas nécessairement par un hint.

          Et quand on migre d’Oracle à un autre SGBD, comme ici PostgreSQL, c’est pareil : il faut prévoir une phase d’optimisation des requêtes, les index pertinents ne seront plus forcément les mêmes, etc.

        • [^] # Re: Hints ?

          Posté par . Évalué à 3.

    • [^] # Re: Hints ?

      Posté par (page perso) . Évalué à 4.

      Une même requête ne peut malheureusement pas tourner sur plusieurs CPUs en parallèle dans Postgresql. De ce que j'en comprends, ce serait assez difficile à mettre en oeuvre, en particulier parce que Postgresql n'utilise pas de threads, mais de multiples processus, mais les développeurs y travaillent (voir par exemple http://www.databasesoup.com/2013/05/postgresql-new-development-priorities-4_20.html)

Suivre le flux des commentaires

Note : les commentaires appartiennent à ceux qui les ont postés. Nous n'en sommes pas responsables.