What's new in PostgreSQL 9.1/fr

From PostgreSQL wiki
Jump to navigationJump to search

Quoi de neuf dans PostgreSQL 9.1

Ce document présente, si possible par l'exemple, un grand nombre des nouveautés de PostgreSQL 9.1, comparé à la version majeure précédente - PostgreSQL 9.0. Il y a de nombreuses nouveautés dans cette version, cette page de wiki ne couvre donc que les changements les plus importants en détail. La liste complète des modifications se trouve dans le chapitre Notes de version de la documentation officielle.

Nouveautés majeures

Réplication synchrone et autres fonctionnalités de réplication

Il y a un certain nombre de nouvelles fonctionnalités autour de la réplication en 9.1:


  • En 9.0, l'utilisateur servant à la réplication devait être superutilisateur. Ce n'est plus le cas, il y a un nouvel attribut appelé 'replication'.
 CREATE ROLE replication_role REPLICATION LOGIN PASSWORD 'pwd_replication'.

Ce rôle peut alors être ajouté au pg_hba.conf, et être utilisé pour la streaming replication. C'est évidemment préférable, d'un point de vue sécurité, que d'avoir un rôle superutilisateur dédié à cela.

Maintenant que nous avons une instance créée, ainsi qu'un utilisateur de réplication, nous pouvons mettre en place la streaming replication. Il ne s'agit que d'ajouter la permission de se connecter à la base virtuelle 'replication' dans "pg_hba.conf", positionner wal_level, l'archivage (archive_mode et archive_command) et max_wal_senders, ce qui est déjà traité dans le billet sur les nouveautés de la 9.0.

Quand l'instance est prête pour le streaming, nous pouvons montrer la seconde nouveauté.

  • pg_basebackup.

Ce nouvel outil permet de cloner une base, ou en faire une sauvegarde, en n'utilisant que le protocole réseau PostgreSQL. Il n'y a pas besoin d'appeler "pg_start_backup()", puis réaliser une copie manuelle et enfin appeler "pg_stop_backup()". pg_basebackup effectue tout ce travail en une seule commande. Pour la démonstration, nous allons cloner l'instance en cours de fonctionnement vers /tmp/newcluster.

 > pg_basebackup -D /tmp/newcluster -U replication -v
 Password: 
 NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_basebackup: base backup completed

Cette nouvelle instance est prête à démarrer: ajoutez simplement un fichier "recovery.conf" avec une "restore_command" pour récupérer les fichiers archivés, et démarrez la nouvelle instance. pg_basebackup peut aussi fabriquer un tar, ou inclure tous les fichiers xlog requis (pour avoir une sauvegarde totalement autonome).

Comme nous allons maintenant montrer la réplication synchrone, préparons un "recovery.conf" pour se connecter à la base maître et récupérer les enregistrements au fil de l'eau.

Le fichier va ressembler à ceci

 restore_command = 'cp /tmp/%f %p'
 standby_mode = on
 primary_conninfo = 'host=localhost port=59121 user=replication password=replication application_name=newcluster'
 trigger_file = '/tmp/trig_f_newcluster'

Puis nous démarrons la nouvelle instance:

 pg_ctl -D /tmp/newcluster start
 
 LOG:  database system was interrupted; last known up at 2011-05-22 17:15:45 CEST
 LOG:  entering standby mode
 LOG:  restored log file "00000001000000010000002F" from archive
 LOG:  redo starts at 1/2F000020
 LOG:  consistent recovery state reached at 1/30000000
 LOG:  database system is ready to accept read only connections
 cp: cannot stat « /tmp/000000010000000100000030 »: No such file or directory
 LOG:  streaming replication successfully connected to primary

Nous avons notre esclave, et il récupère les données provenant du maître par le mode «streaming», mais nous sommes toujours en asynchrone. Notez que nous avons positionné un paramètre "application_name" dans la chaîne de connexion du "recovery.conf".

  • Réplication synchrone

Pour que la réplication devienne synchrone, c'est très simple, il suffit de positionner ceci dans le postgresql.conf du maître:

 synchronous_standby_names = 'newcluster'

C'est bien sûr l'"application_name" provenant du "primary_conninfo" de l'esclave. Un «pg_ctl_reload», et le nouveau paramètre est pris en compte. Maintenant, tout «COMMIT» sur le maître ne sera considéré comme terminé que quand l'esclave l'aura écrit sur son propre journal, et l'aura notifié au maître.

Un petit avertissement: les transactions sont considérées comme validées quand elles sont écrites dans le journal de l'esclave, pas quand elles sont visibles sur l'esclave. Cela veut dire qu'il y a toujours un délai entre le moment où une transaction est validée sur le maître, et le moment où elle est visible sur l'esclave. La réplication est tout de même synchrone: vous ne perdrez pas de données dans le cas du crash d'un maître.

La réplication synchrone peut être réglée assez finement: elle est contrôlable par session. Le paramètre "synchronous_commit" peut être désactivé (il est évidemment actif par défaut) par session, si celle-ci n'a pas besoin de cette garantie de réplication synchrone. Si, dans votre transaction, vous n'avez pas besoin de la réplication synchrone, faites simplement

 SET synchronous_commit TO off

et vous ne paierez pas la pénalité due à l'attente de l'esclave.

Il y a quelques autres nouveautés à mentionner pour la réplication:

  • Les esclaves peuvent maintenant demander au maître de ne pas nettoyer par VACUUM les enregistrements dont ils pourraient encore avoir besoin.

C'était une des principales difficultés du paramétrage de la réplication en 9.0, si on souhaitait utiliser l'esclave: un VACUUM pouvait détruire des enregistrements qui étaient encore nécessaires à l'exécution des requêtes de l'esclave, engendrant des conflits de réplication. L'esclave avait alors à faire un choix: soit tuer la requête en cours d'exécution, soit accepter de retarder l'application des modifications générées par le VACUUM (et toutes celles qui le suivent bien sûr), et donc prendre du retard. On pouvait contourner le problème, en positionnant "vacuum_defer_cleanup_age" à une valeur non nulle, mais c'était difficile de trouver une bonne valeur. La nouvelle fonctionnalité est activée en positionnant "hot_standby_feedback", sur les bases de standby. Bien sûr, cela entraîne que la base de standby va pouvoir empêcher VACUUM de faire son travail de maintenance sur le maître, s'il y a des requêtes très longues qui s'exécutent sur l'esclave.

  • pg_stat_replication est une nouvelle vue système.

Elle affiche, sur le maître, l'état de tous les esclaves: combien de WAL ils ont reçu, s'ils sont connectés, synchrones, où ils en sont de l'application des modifications:

 =# SELECT * from pg_stat_replication ;
  procpid | usesysid |   usename   | application_name | client_addr | client_hostname | client_port |        backend_start         |   state   | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state 
 ---------+----------+-------------+------------------+-------------+-----------------+-------------+------------------------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
    17135 |    16671 | replication | newcluster       | 127.0.0.1   |                 |       43745 | 2011-05-22 18:13:04.19283+02 | streaming | 1/30008750    | 1/30008750     | 1/30008750     | 1/30008750      |             1 | sync

Il n'est dont plus nécessaire d'exécuter des requêtes sur les esclaves pour connaître leur état par rapport au maître.

  • pg_stat_database_conflicts est une autre vue système.

Celle ci est sur la base de standby, et montre combien de requêtes ont été annulées, et pour quelles raisons:

 =# SELECT * from pg_stat_database_conflicts ;
  datid |  datname  | confl_tablespace | confl_lock | confl_snapshot | confl_bufferpin | confl_deadlock 
 -------+-----------+------------------+------------+----------------+-----------------+----------------
      1 | template1 |                0 |          0 |              0 |               0 |              0
  11979 | template0 |                0 |          0 |              0 |               0 |              0
  11987 | postgres  |                0 |          0 |              0 |               0 |              0
  16384 | marc      |                0 |          0 |              1 |               0 |              0
  • la réplication peut maintenant être mise en pause sur un esclave.

Appelez tout simplement pg_xlog_replay_pause() pour mettre en pause, et pg_xlog_replay_resume() pour reprendre. Cela gèlera la base, ce qui en fait un excellent outil pour réaliser des sauvegardes cohérentes.

pg_is_xlog_replay_paused() permet de connaître l'état actuel.

On peut aussi demander à PostgreSQL de mettre l'application des journaux en pause à la fin de la récupération d'instance, sans passer la base en production, pour permettre à l'administrateur d'exécuter des requêtes sur la base. L'administrateur peut alors vérifier si le point de récupération atteint est correct, avant de mettre fin à la réplication. Ce nouveau paramètre est "pause_at_recovery_target", et se positionne dans le recovery.conf.

  • On peut créer des points de récupération (Restore Points)

Ce ne sont rien de plus que des points nommés dans le journal de transactions.

Il peuvent être utilisés en spécifiant un "recovery_target_name" à la place d'un "recovery_target_time" ou un "recovery_target_xid" dans le fichier recovery.conf.

Ils sont créés en appelant "pg_create_restore_point()".

Collations par colonne

L'ordre de collation n'est plus unique dans une base de données.

Imaginons que vous utilisiez une base en 9.0, avec un encodage UTF8, et une collation de_DE.utf8 (tri alphabétique), parce que la plupart de vos utilisateurs parlent allemand. Si vous avez des données françaises à stocker aussi, et que vous avez besoin de les trier, les utilisateurs français les plus pointilleux ne seraient pas satisfaits:

 SELECT * from (values ('élève'),('élevé'),('élever'),('Élève')) as tmp order by column1;
  column1 
 ---------
  élevé
  élève
  Élève
  élever

Pour être honnête, ce n'est pas si mal. Mais ce n'est pas l'ordre alphabétique français: les caractères accentués sont considérés comme non accentués durant une première passe de tri. Ensuite, on effectue une seconde passe, où on considère que les caractères accentués sont après les non accentués (dans un ordre bien précis). Mais, pour que la chose soit plus amusante, le tri est fait du dernier au premier caractère dans cette seconde passe, et non plus du premier au dernier. Évidemment, la règle n'est pas la même en allemand.

En 9.1, vous disposez de deux nouvelles fonctionnalités:

  • Vous pouvez spécifier la collation dans une requête:
 SELECT * FROM (VALUES ('élève'),('élevé'),('élever'),('Élève')) AS tmp ORDER BY column1 COLLATE "fr_FR.utf8";
  column1 
 ---------
  élève
  Élève
  élevé
  élever
  • Vous pouvez définir la collation au moment de la déclaration de la table:
 CREATE TABLE french_messages (message TEXT COLLATE "fr_FR.utf8");
 INSERT INTO french_messages VALUES ('élève'),('élevé'),('élever'),('Élève');
 SELECT * FROM french_messages ORDER BY message;
  message 
 ---------
  élève
  Élève
  élevé
  élever

Et bien sûr, vous pouvez créer un index sur la colonne message, qui pourra être utilisé pour trier rapidement en français. Par exemple, avec une table plus grande et sans collation précisée:

 CREATE TABLE french_messages2 (message TEXT);
 INSERT INTO french_messages2 SELECT * FROM french_messages, generate_series(1,100000); -- 400k lignes
 CREATE INDEX idx_french_ctype ON french_messages2 (message COLLATE "fr_FR.utf8");
 EXPLAIN SELECT * FROM french_messages2 ORDER BY message;
                                   QUERY PLAN                                   
 -------------------------------------------------------------------------------
  Sort  (cost=62134.28..63134.28 rows=400000 width=32)
    Sort Key: message
    ->  Seq Scan on french_messages2  (cost=0.00..5770.00 rows=400000 width=32)
 
 EXPLAIN SELECT * FROM french_messages2 ORDER BY message COLLATE "fr_FR.utf8";
                                             QUERY PLAN                                            
 --------------------------------------------------------------------------------------------------
  Index Scan using idx_french_ctype on french_messages2  (cost=0.00..17139.15 rows=400000 width=8)

Unlogged Tables/Tables non journalisées

Ces tables peuvent être utilisées pour stocker des données éphémères. Une table non journalisée est bien plus rapide à écrire, mais elle ne survivra pas à un crash (elle sera tronquée au redémarrage de l'instance en cas de crash).

Elles n'ont pas pas le coût de maintenance associé à la journalisation, elles sont donc bien plus rapides à écrire.

Voici un exemple (idiot, l'exemple):

 # CREATE TABLE test (a int);
 CREATE TABLE
 # CREATE UNLOGGED table testu (a int);
 CREATE TABLE
 # CREATE INDEX idx_test on test (a);
 CREATE INDEX
 # CREATE INDEX idx_testu on testu (a);
 CREATE INDEX
 =# \timing 
 Timing is on.
 =# INSERT INTO test SELECT generate_series(1,1000000);
 INSERT 0 1000000
 Time: 17601,201 ms
 =# INSERT INTO testu SELECT generate_series(1,1000000);
 INSERT 0 1000000
 Time: 3439,982 ms

Elles sont donc très efficace pour des données de cache, ou pour n'importe quoi qui puisse être reconstruit après un crash.

Extensions

Ce point et le suivant sont l'occasion de présenter plusieurs fonctionnalités d'un coup. Nous allons commencer par installer pg_trgm, et c'est maintenant une extension.

Installons donc pg_trgm. Jusqu'à la 9.0, nous devions lancer un script manuellement. La commande ressemblait à ceci:

 \i /usr/local/pgsql/share/contrib/pg_trgm.sql

Cela entraînait des problèmes de maintenance: les fonctions créées allaient par défaut dans le schéma public, elles étaient envoyées telles quelles dans les fichiers pg_dump, ne se restauraient souvent pas bien, puisqu'elles dépendaient souvent d'objets binaires externes, ou pouvaient changer de définition entre les différentes versions de PostgreSQL.

Avec la 9.1, vous pouvez utiliser la commande CREATE EXTENSION:

 CREATE EXTENSION [ IF NOT EXISTS ] extension_name
     [ WITH ] [ SCHEMA schema ]
              [ VERSION version ]
              [ FROM old_version ]

Les options les plus importantes sont "extension_name", bien sûr, et "schema": les extensions peuvent être stockées dans un schéma.

Installons donc pg_trgm, pour l'exemple qui va suivre:

 =# CREATE schema extensions;
 CREATE SCHEMA
 =# CREATE EXTENSION pg_trgm WITH SCHEMA extensions;
 CREATE EXTENSION

Maintenant, pg_trgm est installé dans un schéma "extensions". Il sera inclus dans les exports de base correctement, avec la syntaxe CREATE EXTENSION. Par conséquent, si quelque chose change dans l'extension, elle sera restaurée avec la nouvelle définition.

La liste des extensions peut être obtenue comme suit dans psql:

 \dx
                                     List of installed extensions
    Name   | Version |   Schema   |                            Description                            
 ----------+---------+------------+-------------------------------------------------------------------
  pg_trgm  | 1.0     | extensions | text similarity measurement and index searching based on trigrams
  plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
 (2 rows)


K-Nearest-Neighbor Indexing/Indexation des k plus proches voisins

Les index GIST peuvent maintenant être utilisés pour retourner des enregistrements triés, si la notion de distance a une signification pour ces données, et qu'on peut en fournir une définition. Pour le moment, ce travail a été effectué pour le type 'point', l'extension 'pg_trgm' et plusieurs types de données btree_gist. Cette fonctionnalité est fournie à tous les types de données, il y en aura donc probablement d'autres qui l'implémenteront dans un futur proche.

Pour l'heure, voici donc un exemple avec pg_trgm. pg_trgm utilise des trigrammes pour comparer des chaînes. Voici les trigrammes pour la chaîne 'hello':

 SELECT show_trgm('hello');
             show_trgm            
 ---------------------------------
  {"  h"," he",ell,hel,llo,"lo "}

Les trigrammes sont utilisés pour évaluer la similarité (entre 0 et 1) entre des chaînes. Il y a donc une notion de distance, et on peut la définir par '1-similarité'.

Voici un exemple. La table contient 5 millions d'enregistrements et pèse 750Mo.

 CREATE TABLE test_trgm ( text_data text);
 CREATE INDEX test_trgm_idx on test_trgm using gist (text_data extensions.gist_trgm_ops);

Jusqu'à la 9.0, si nous voulons les deux text_data les plus proches de 'hello' dans la table, la requête était celle-ci:

 SELECT text_data, similarity(text_data, 'hello')
 FROM test_trgm 
 WHERE text_data % 'hello'
 ORDER BY similarity(text_data, 'hello')
 LIMIT 2;

Sur cette base de test, il faut environ 2 secondes pour obtenir le résultat.

Avec 9.1 et la nouvelle fonctionnalité KNN, on peut l'écrire comme ceci:

 SELECT text_data, text_data <-> 'hello'
 FROM test_trgm 
 ORDER BY text_data <-> 'hello'
 LIMIT 2;

L'opérateur <-> est l'opérateur de distance. La requête dure 20 millisecondes, et passe par l'index pour récupérer directement les deux meilleurs enregistrements.

Tant que nous parlons de pg_trgm, une autre fonctionnalité apparaissant en 9.1 est que les opérateurs LIKE et ILIKE peuvent maintenant utiliser automatiquement un index trgm. Toujours sur la même table:

 SELECT text_data
 FROM test_trgm
 WHERE text_data like '%hello%';

utilise l'index test_trgm_idx (au lieu de parcourir la table entière).

Attention tout de même: les index trgm sont très volumineux, et coûteux à maintenir.

Serializable Snapshot Isolation/Isolation par Instantanés Sérialisable/SSI

Cette fonctionnalité est très utile si vous avez besoin que toutes vos transactions se comportent comme si elles s'exécutaient les unes après les autres, sans trop sacrifier les performances, comme c'est le cas pour la plupart des implémentations d'isolation «sérialisable» actuelles (elles s'appuient habituellement sur le verrouillage de tous les enregistrements accédés).

Comme cette fonctionnalité est complexe à montrer et à expliquer, voici un lien vers l'explication complète de cette fonctionnalité: http://wiki.postgresql.org/wiki/SSI/fr

Writeable Common Table Expressions/Expression de Table Commune en Écriture

Cela étend la syntaxe WITH introduite en 8.4. Dorénavant, des requêtes de modification de données peuvent être utilisées dans la partie WITH de la requête, et les données retournées par cet ordre utilisées.

Imaginons que nous voulons archiver tous les enregistrements correspondant à %hello% de la table test_trgm:

 CREATE TABLE old_text_data (text_data text);
 
 WITH deleted AS (DELETE FROM test_trgm WHERE text_data like '%hello%' RETURNING text_data)
 INSERT INTO old_text_data SELECT * FROM deleted;

Tout en une seule requête (donc en une seule passe sur test_trgm).

Voici un exemple plus ambitieux. Cette requête met à jour une base de données pgbench, en enlevant un groupe de transactions erronées et en mettant à jour les totaux de teller, branch et account, en un seul ordre:

 WITH deleted_xtns AS (
 DELETE FROM pgbench_history
 WHERE bid = 4 and tid = 9
 RETURNING *
 ),
 deleted_per_account as (
   SELECT aid, sum(delta) as baldiff
   FROM deleted_xtns
   GROUP BY 1
 ),
   accounts_rebalanced as (
     UPDATE pgbench_accounts
     SET abalance = abalance - baldiff
     FROM deleted_per_account
     WHERE deleted_per_account.aid = pgbench_accounts.aid
     RETURNING deleted_per_account.aid, pgbench_accounts.bid,
       baldiff
   ),
     branch_adjustment as (
       SELECT bid, SUM(baldiff) as branchdiff
       FROM accounts_rebalanced
       GROUP BY bid
     )
 UPDATE pgbench_branches
 SET bbalance = bbalance - branchdiff
 FROM branch_adjustment
 WHERE branch_adjustment.bid = pgbench_branches.bid
 RETURNING branch_adjustment.bid,branchdiff,bbalance;


SE-Postgres

PostgreSQL est la seule base qui propose une intégration complète avec le framework de sécurisation SELinux. Sécurité de niveau militaire pour votre base de données. TODO

PGXN

PGXN est le PostgreSQL Extension Network (le réseau d'extensions PostgreSQL), un système de distribution centralisée pour les bibliothèques d'extension PostgreSQL open-source. Les auteurs d'extensions peuvent soumettre leur travail en même temps que les métadonnées le décrivant: les packages et leur documentation sont indexés et distribués sur plusieurs serveurs. Le système peut être utilisé au travers d'une interface web ou en utilisant des clients en ligne de commande grâce à une API simple.

Un client PGXN complet est en cours de développement. Il peut être installé avec:

$ easy_install pgxnclient
Searching for pgxnclient
...
Best match: pgxnclient 0.2.1
Processing pgxnclient-0.2.1-py2.6.egg
...
Installed pgxnclient-0.2.1-py2.6.egg

Il permet entre autres de rechercher des extensions sur le site web:

$ pgxn search pair
pair 0.1.3
    ... Usage There are two ways to construct key/value *pairs*: Via the
    *pair*() function: % SELECT *pair*('foo', 'bar'); *pair* ------------
    (foo,bar) Or by using the ~> operator: % SELECT 'foo' ~> 'bar';
    *pair*...

semver 0.2.2
    *pair* │ 0.1.0 │ Key/value *pair* data type Note that "0.35.0b1" is less
    than "0.35.0", as required by the specification. Use ORDER BY to get
    more of a feel for semantic version ordering rules: SELECT...

Pour compiler et installer sur le système:

$ pgxn install pair
INFO: best version: pair 0.1.3
INFO: saving /tmp/tmpezwyEO/pair-0.1.3.zip
INFO: unpacking: /tmp/tmpezwyEO/pair-0.1.3.zip
INFO: building extension
...
INFO: installing extension
[sudo] password for piro: 
/bin/mkdir -p '/usr/local/pg91b1/share/postgresql/extension'
...

Et pour les charger en tant qu'extension de base de données:

$ pgxn load -d mydb pair
INFO: best version: pair 0.1.3
CREATE EXTENSION

SQL/MED

Le support de SQL/MED (Management of External Data ou Gestion de Données Externes) a été démarré en 8.4. Maintenant, PostgreSQL peut définir des tables externes, ce qui est le but principal de SQL/MED: accéder à des données externes. Voici un exemple, s'appuyant sur l'extension file_fdw.

Nous allons accéder à un fichier CSV au travers d'une table.

 CREATE EXTENSION file_fdw WITH SCHEMA extensions;
 \dx+ file_fdw
           Objects in extension "file_fdw"
                  Object Description                 
 ----------------------------------------------------
  foreign-data wrapper file_fdw
  function extensions.file_fdw_handler()
  function extensions.file_fdw_validator(text[],oid)

L'étape suivante est optionnelle. Elle est là juste pour montrer la syntaxe de 'CREATE FOREIGN DATA WRAPPER' (le foreign data wrapper étant en quelque sorte le connecteur pour un type de données externes):

 =# CREATE FOREIGN DATA WRAPPER file_data_wrapper HANDLER extensions.file_fdw_handler;
 CREATE FOREIGN DATA WRAPPER

L'extension crée déjà un «foreign data wrapper» appelé file_fdw. Nous allons l'utiliser à partir de maintenant.

Nous avons besoin de créer un 'server'. Comme les données que nous allons récupérer ne proviennent que d'un fichier, cela semble un peu inutile, mais SQL/MED est aussi capable de gérer des bases de données distantes.

 CREATE SERVER file FOREIGN DATA WRAPPER file_fdw ;
 CREATE SERVER

Maintenant, attachons un fichier statistical_data.csv à une table statistical_data:

 CREATE FOREIGN TABLE statistical_data (field1 numeric, field2 numeric) server file options (filename '/tmp/statistical_data.csv', format 'csv', delimiter ';') ;
 CREATE FOREIGN TABLE
 marc=# SELECT * from statistical_data ;
  field1 | field2 
 --------+--------
     0.1 |    0.2
     0.2 |    0.4
     0.3 |    0.9
     0.4 |    1.6

Pour le moment, les foreign tables ne sont accessibles qu'en SELECT.

Voyez la liste des extensions Foreign Data Wrapper existantes, qui inclut Oracle, MySQL, CouchDB, Redis, Twitter, et autres.

Modifications pouvant entraîner des régressions

Les points suivants doivent être vérifiés lors d'une migration vers la version 9.1.

  • La valeur par défaut de standard_conforming_strings est devenue on

Traditionnellement, PostgreSQL ne traitait pas les littéraux de type chaîne ('..') comme le spécifie le standard SQL: les anti-slashs ('\') étaient considérés comme des caractères d'échappement, ce qui entraînait que le caractère suivant un '\' était interprété. Par exemple, '\n' est un caractère newline, '\\' est le caractère '\' lui-même. Cela s'apparentait davantage à la syntaxe du C.

En 9.1, standard_conforming_strings est maintenant par défaut à on, ce qui signifie que les littéraux de type chaîne sont maintenant traités comme spécifié par le standard SQL. Ce qui signifie que les caractères apostrophe doivent maintenant être protégés avec une deuxième apostrophe plutôt qu'un anti-slash, et que les anti-slashs ne sont plus des caractères d'échappement.

Par exemple, quand précédemment on écrivait 'l\'heure', on doit maintenant écrire 'l''heure'.

Certaines subtilités sont à connaître, même si elles ne sont pas apparues en 9.1:

  • L'ancienne syntaxe est toujours disponible. Mettez simplement un E devant le guillemet de départ: E'l\'heure'
  • standard_conforming_strings peut toujours être remis à off
  • Beaucoup de langages de programmation font déjà ce qu'il faut, si vous leur demandez de faire le travail d'échappement pour vous. Par exemple, la fonction PQescapeLiteral de la libpq détecte automatiquement la valeur de standard_conforming_strings et s'y adapte.

Toutefois, vérifiez bien que votre programme est prêt à supporter ce changement de comportement.

  • les conversions de type de données de style 'fonction' ou 'attribut' ne sont plus autorisés pour les types composites

Depuis la version 8.4, il est possible de convertir à peu près n'importe quoi vers son format texte. Essayons cela avec la foreign table définie précédemment:

 =# SELECT cast(statistical_data as text) from statistical_data ;
  statistical_data 
 ------------------
  (0.1,0.2)
  (0.2,0.4)
  (0.3,0.9)
  (0.4,1.6)
 (4 rows)

Le problème c'est que les versions 8.4 et 9.0 nous donnent 4 syntaxes différentes pour effectuer cela:

  • SELECT cast(statistical_data as text) from statistical_data ;
  • SELECT statistical_data::text from statistical_data;
  • SELECT statistical_data.text from statistical_data;
  • SELECT text(statistical_data) from statistical_data;

les deux dernières syntaxes ne sont plus autorisées pour les types composites (comme un enregistrement de table): ils étaient bien trop faciles à utiliser accidentellement.

  • Les vérifications de conversion sur les domaines définis à partir de tableaux ont été renforcées

Maintenant, PostgreSQL vérifie quand vous faites une mise à jour d'un élément d'une contrainte définie sur un tableau.

Voici ce qui se passait en 9.0:

 =#CREATE DOMAIN test_dom as int[] check (value[1] > 0);
 CREATE DOMAIN
 =#SELECT '{-1,0,0,0,0}'::test_dom;
 ERROR:  value for domain test_dom violates check constraint "test_dom_check"

Jusque là, tout va bien.

 =#CREATE TABLE test_dom_table (test test_dom);
 CREATE TABLE
 =# INSERT INTO test_dom_table values ('{1,0,0,0,0}');
 INSERT 0 1
 =# UPDATE test_dom_table SET test[1]=-1;
 UPDATE 1

Par contre, là, c'est anormal… la contrainte check nous interdit de le faire. C'est maintenant impossible en 9.1, la vérification est faite correctement.

  • string_to_array() retourne maintenant un tableau vide pour une chaîne d'entrée de longueur zéro. Précédemment, cela retournait NULL.
 =# SELECT string_to_array(,'whatever');
  string_to_array 
 -----------------
  {}
  • string_to_array() découpe maintenant une chaîne en ses caractères si le séparateur est NULL. Précédemment, cela retournait NULL:
 =# SELECT string_to_array('foo',NULL);
  string_to_array 
 -----------------
  {f,o,o}
  • PL/pgSQL's RAISE sans paramètre a changé de comportement.

C'est un cas assez rare, mais qui piégeait les utilisateurs habitués au comportement d'Oracle sur ce point.

Voici un exemple:

 CREATE OR REPLACE FUNCTION raise_demo () returns void language plpgsql as $$
 BEGIN
   RAISE NOTICE 'Main body';
   BEGIN
     RAISE NOTICE 'Sub-block';
     RAISE EXCEPTION serialization_failure; -- Simulate a problem
   EXCEPTION WHEN serialization_failure THEN
     BEGIN
       -- Maybe we had a serialization error
       -- Won't happen here of course
       RAISE DEBUG 'There was probably a serialization failure. It could be because of...';
       -- ..
       -- If I get there let's pretend I couldn't find a solution to the error
       RAISE; -- Let's forward the error
     EXCEPTION WHEN OTHERS THEN
         -- This should capture everything
         RAISE EXCEPTION 'Couldn t figure what to do with the error';
     END;
   END;
 END;
 $$
 ;

CREATE FUNCTION

En 9.0, vous aurez ce résultat (avec client_min_messages à debug):

 =# SELECT raise_demo();
 NOTICE:  Main body
 NOTICE:  Sub-block
 DEBUG:  There was probably a serialization failure. It could be because of...
 ERROR:  serialization_failure


En 9.1:

 =# SELECT raise_demo();
 NOTICE:  Main body
 NOTICE:  Sub-block
 DEBUG:  There was probably a serialization failure. It could be because of...
 ERROR:  Couldn t figure what to do with the error

La différence est que RAISE sans paramètres, en 9.0, ramène le déroulement du code à l'endroit où l'EXCEPTION s'est déclenchée. En 9.1, le RAISE continue dans le bloc dans lequel il se produit, le bloc BEGIN intérieur n'est pas quitté quand le RAISE se déclenche. Son bloc d'exception est exécuté.

Améliorations liées aux performances

  • Les écritures synchrones ont été optimisées pour moins charger le système de fichiers.

Ce point est difficile à mettre en évidence dans ce document. Mais la performance et les temps de réponse (la latence) ont été fortement améliorés quand la charge en écriture est élevée.

  • Les tables filles (par héritage) dans les requêtes peuvent maintenant retourner des résultats triés de façon utile, ce qui permet des optimisations de MIN/MAX pour l'héritage (et donc le partitionnement).

Si vous utilisez beaucoup d'héritage, dans un contexte de partitionnement en particulier, vous allez adorer cette optimisation.

Le planificateur de requête est devenu bien plus intelligent dans le cas suivant.

Créons un schéma factice:

 =# CREATE TABLE parent (a int);
 CREATE TABLE
 =# CREATE TABLE children_1 ( check (a between 1 and 10000000)) inherits (parent);
 CREATE TABLE
 =# CREATE TABLE children_2 ( check (a between 10000001 and 20000000)) inherits (parent);
 CREATE TABLE
 =# INSERT INTO children_1 select generate_series(1,10000000);
 INSERT 0 10000000
 =# INSERT INTO children_2 select generate_series(10000001,20000000);
 INSERT 0 10000000
 =# CREATE INDEX test_1 ON children_1 (a);
 CREATE INDEX;
 =# CREATE INDEX test_2 ON children_2 (a);
 CREATE INDEX;

Et demandons les 50 plus grandes valeurs de a.

 SELECT * from parent order by a desc limit 50;

Cela prend, sur une petite machine de test, 13 secondes sur une base en 9.0, et 0.8 millisecondes sur une base en 9.1.

Le plan en 9.0 est:

  Limit  (cost=952993.36..952993.48 rows=50 width=4)
    ->  Sort  (cost=952993.36..1002999.24 rows=20002354 width=4)
          Sort Key: public.parent.a
          ->  Result  (cost=0.00..288529.54 rows=20002354 width=4)
                ->  Append  (cost=0.00..288529.54 rows=20002354 width=4)
                      ->  Seq Scan on parent  (cost=0.00..34.00 rows=2400 width=4)
                      ->  Seq Scan on children_1 parent  (cost=0.00..144247.77 rows=9999977 width=4)
                      ->  Seq Scan on children_2 parent  (cost=0.00..144247.77 rows=9999977 width=4)

Le plan en 9.1 est:

  Limit  (cost=113.75..116.19 rows=50 width=4)
    ->  Result  (cost=113.75..975036.98 rows=20002400 width=4)
          ->  Merge Append  (cost=113.75..975036.98 rows=20002400 width=4)
                Sort Key: public.parent.a
                ->  Sort  (cost=113.73..119.73 rows=2400 width=4)
                      Sort Key: public.parent.a
                      ->  Seq Scan on parent  (cost=0.00..34.00 rows=2400 width=4)
                ->  Index Scan Backward using test_1 on children_1 parent  (cost=0.00..303940.35 rows=10000000 width=4)
                ->  Index Scan Backward using test_2 on children_2 parent  (cost=0.00..303940.35 rows=10000000 width=4)

Le plan en 9.0 signifie: je vais prendre tous les enregistrements de toutes les tables, les trier, et ensuite retourner les 50 plus grands.

Le plan en 9.1 signifie: je vais prendre les enregistrements de chaque table dans l'ordre trié, en utilisant leurs index s'il y en a, les fusionner comme ils arrivent, et retourner les 50 premiers.

C'était un piège très fréquent, ce genre de requête devenait extrêmement lent quand on partitionnait une table. Et il était un peu compliqué de le contourner par réécriture de requête.

  • Les algorithmes de hachage peuvent maintenant être utilisés pour les full outer join, et pour les tableaux.

Il est très simple de démontrer ce point (pour les full outer join):

 CREATE TABLE test1 (a int);
 CREATE TABLE test2 (a int);
 INSERT INTO test1 SELECT generate_series(1,100000);
 INSERT INTO test2 SELECT generate_series(100,1000);

Nous avons donc une grosse table test1 et une petite table test2.

En 9.0, la requête est faite avec ce plan:

 EXPLAIN ANALYZE SELECT * FROM test1 FULL OUTER JOIN test2 USING (a);
                                                         QUERY PLAN                                                        
 --------------------------------------------------------------------------------------------------------------------------
  Merge Full Join  (cost=11285.07..11821.07 rows=100000 width=8) (actual time=330.092..651.618 rows=100000 loops=1)
    Merge Cond: (test1.a = test2.a)
    ->  Sort  (cost=11116.32..11366.32 rows=100000 width=4) (actual time=327.926..446.814 rows=100000 loops=1)
          Sort Key: test1.a
          Sort Method:  external sort  Disk: 1368kB
          ->  Seq Scan on test1  (cost=0.00..1443.00 rows=100000 width=4) (actual time=0.011..119.246 rows=100000 loops=1)
    ->  Sort  (cost=168.75..174.75 rows=2400 width=4) (actual time=2.156..3.208 rows=901 loops=1)
          Sort Key: test2.a
          Sort Method:  quicksort  Memory: 67kB
          ->  Seq Scan on test2  (cost=0.00..34.00 rows=2400 width=4) (actual time=0.009..1.066 rows=901 loops=1
  Total runtime: 733.368 ms

Voici le nouveau plan, en 9.1 cette fois-ci:

 --------------------------------------------------------------------------------------------------------------------
  Hash Full Join  (cost=24.27..1851.28 rows=100000 width=8) (actual time=2.536..331.547 rows=100000 loops=1)
    Hash Cond: (test1.a = test2.a)
    ->  Seq Scan on test1  (cost=0.00..1443.00 rows=100000 width=4) (actual time=0.014..119.884 rows=100000 loops=1)
    ->  Hash  (cost=13.01..13.01 rows=901 width=4) (actual time=2.505..2.505 rows=901 loops=1)
          Buckets: 1024  Batches: 1  Memory Usage: 32kB
          ->  Seq Scan on test2  (cost=0.00..13.01 rows=901 width=4) (actual time=0.017..1.186 rows=901 loops=1)
  Total runtime: 412.735 ms

Le plan en 9.0 effectue 2 tris. Celui en 9.1 n'a besoin que d'un hachage de la plus petite table.

Le temps d'exécution est divisé par presque 2. Une autre propriété intéressante est que le nouveau plan a un coût de démarrage bien plus faible: le premier enregistrement est retourné après 2 millisecondes, alors qu'il en faut 330 à l'ancien plan.

 SELECT * from test1 full outer join test2 using (a) LIMIT 10

prend 330ms en 9.0, et 3ms en 9.1.


Administration

  • Paramétrage automatique de wal_buffers.

Le paramètre wal_buffers est maintenant positionné automatiquement quand sa valeur est -1, sa nouvelle valeur par défaut. Il est positionné automatiquement à 1/32ème de shared_buffers, avec un maximum à 16Mo. Un paramètre de moins à gérer…

  • Enregistrement des dernières remises à zéro dans les vues de statistiques de base de données et de background writer.

Vous pouvez maintentant savoir quand les statistiques ont été réinitialisées. Pour une base de données, par exemple:

 SELECT datname, stats_reset FROM pg_stat_database;
   datname  |          stats_reset          
 -----------+-------------------------------
  template1 | 
  template0 | 
  postgres  | 2011-05-11 19:22:05.946641+02
  marc      | 2011-05-11 19:22:09.133483+02
  • Nouvelles colonnes montrant le nombre d'opérations de vacuum et d'analyze dans les vues pg_stat_*_tables.

C'est maintenant bien plus facile de savoir quelle table attire l'attention d'autovacuum:

 SELECT relname, last_vacuum, vacuum_count, last_autovacuum, autovacuum_count, last_analyze, analyze_count, last_autoanalyze, autoanalyze_count
 FROM pg_stat_user_tables 
 WHERE relname in ('test1','test2');
  relname | last_vacuum | vacuum_count | last_autovacuum | autovacuum_count | last_analyze | analyze_count |       last_autoanalyze        | autoanalyze_count 
 ---------+-------------+--------------+-----------------+------------------+--------------+---------------+-------------------------------+-------------------
  test1   |             |            0 |                 |                0 |              |             0 | 2011-05-22 15:51:50.48562+02  |                 1
  test2   |             |            0 |                 |                0 |              |             0 | 2011-05-22 15:52:50.325494+02 |                 2


Fonctionnalités SQL et PL/PgSQL

  • Group by peut deviner des colonnes manquantes
 CREATE TABLE entities (entity_name text primary key, entity_address text);
 CREATE TABLE employees (employee_name text primary key, entity_name text references entities (entity_name));
 INSERT INTO entities VALUES ('HR', 'address1');
 INSERT INTO entities VALUES ('SALES', 'address2');
 INSERT INTO employees VALUES ('Smith', 'HR');
 INSERT INTO employees VALUES ('Jones', 'HR');
 INSERT INTO employees VALUES ('Taylor', 'SALES');
 INSERT INTO employees VALUES ('Brown', 'SALES');

On peut maintenant écrire:

 SELECT count(*), entity_name, address
 FROM entities JOIN employees using (entity_name)
 GROUP BY entity_name;
  count | entity_name | address  
 -------+-------------+----------
      2 | HR          | address1
      2 | SALES       | address2

En 9.0, il aurait fallu grouper aussi sur address. Comme entity_name est la clé primaire d'entities, address est fonctionnellement dépendant d'entity_name, il est donc évident que PostgreSQL doit aussi regrouper sur address.

  • De nouvelles valeurs peuvent être ajoutées à un type enum par ALTER TYPE.
 =# CREATE TYPE package_status AS ENUM ('RECEIVED', 'DELIVERED');  ;
 CREATE TYPE
 =# ALTER TYPE package_status ADD VALUE 'READY FOR DELIVERY' AFTER 'RECEIVED';
 ALTER TYPE

Jusqu'à la 9.0, il était nécessaire de détruire le type et en créer un nouveau. Cela impliquait de détruire toutes les colonnes utilisant ce type. C'était une des principales raisons pour lesquelles les enum étaient peu utilisés.

  • Les types composites peuvent être modifiés par ALTER TYPE ... ADD/DROP/ALTER/RENAME ATTRIBUTE.

Créons un type composite simple:

 =#CREATE TYPE package AS (destination text);

Créons une fonction vide utilisant ce type:

 =#CREATE FUNCTION package_exists (pack package) RETURNS boolean LANGUAGE plpgsql AS $$
 BEGIN
   RETURN true;
 END
 $$
 ;

Testons cette fonction:

 =#SELECT package_exists(row('test'));
  package_exists 
 ----------------
  t

Cela fonctionne.

Il est maintenant possible de modifier le type 'package':

 =#ALTER TYPE package ADD ATTRIBUTE received boolean;

le type a changé:

 =#SELECT package_exists(row('test'));
 ERROR:  cannot cast type record to package
 LINE 1: SELECT package_exists(row('test'));
                               ^
 DETAIL:  Input has too few columns.
 =# SELECT package_exists(row('test',true));
  package_exists 
 ----------------
  t
  • ALTER TABLE ... ADD UNIQUE/PRIMARY KEY USING INDEX

Cela sera certainement utilisé principalement pour créer une clé unique ou primaire sans verrouiller une table pendant trop longtemps:

 =# CREATE UNIQUE INDEX CONCURRENTLY idx_pk ON test_pk (a);
 CREATE INDEX
 =# ALTER TABLE test_pk ADD primary key using index idx_pk;
 ALTER TABLE

La table test_pk ne sera verrouillée en écriture que pendant la durée de l'ALTER TABLE. Le reste du travail sera fait sans bloquer le travail des utilisateurs.

On peut bien sûr utiliser cela pour reconstruire l'index d'une clé primaire sans verrouiller la table pendant toute l'opération:

 =# CREATE UNIQUE INDEX CONCURRENTLY idx_pk2 ON test_pk (a);
 =# BEGIN ;
 =# ALTER TABLE test_pk DROP CONSTRAINT idx_pk;
 =# ALTER TABLE test_pk ADD primary key using index idx_pk2;
 =# COMMIT ;
  • ALTER TABLE ... SET DATA TYPE peut éviter la ré-écriture de toute la table pour les cas les plus appropriés.

Par exemple, convertir une colonne varchar en texte ne demande plus de réécrire la table.

Par contre, augmenter la taille d'une colonne varchar nécessite toujours une réécriture de la table

Il reste encore un certain nombre de cas non gérés, qui déclenchent une réécriture. Il y aura probablement des améliorations dans les prochaines versions de PostgreSQL, ce travail se poursuivant.

  • New CREATE TABLE IF NOT EXISTS syntax.

Vous n'aurez pas d'erreur si une table existe déjà, seulement un NOTICE.

Attention au fait qu'il ne vérifiera pas que la définition de votre CREATE TABLE et la définition de la table sont identiques.

  • Nouvelle option ENCODING à COPY TO/FROM. Cela permet de spécifier un encodage à COPY qui soit indépendant du client_encoding.
 COPY test1 TO stdout ENCODING 'latin9'

convertira l'encodage directement. Il n'est donc pas nécessaire de changer le client_encoding avant le COPY.

  • triggers INSTEAD OF sur vues.

Cette fonctionnalité peut être utilisée pour implémenter des vues en mise à jour. Voici un exemple:

Continuons sur l'exemple employees/entities.

 =#CREATE VIEW emp_entity AS SELECT employee_name, entity_name, address
 FROM entities JOIN employees USING (entity_name);

Pour rendre cette vue modifiable en 9.0, il fallait utiliser des RULES (règles). Cela pouvait vite tourner au cauchemar, les rules sont assez complexes à écrire, et encore pire à déboguer. Voici comment on procédait: mises à jour par règles

On peut maintenant faire tout cela avec un trigger. Voici un exemple, en PL/PgSQL (il n'y a que la partie INSERT ici):

 =#CREATE OR REPLACE FUNCTION dml_emp_entity () RETURNS trigger LANGUAGE plpgsql AS $$
 DECLARE
   vrecord RECORD;
 BEGIN
   IF TG_OP = 'INSERT' THEN
     -- Does the record exist in entity ?
     SELECT entity_name,address INTO vrecord FROM entities WHERE entity_name=NEW.entity_name;
     IF NOT FOUND THEN
       INSERT INTO entities (entity_name,address) VALUES (NEW.entity_name, NEW.address);
     ELSE
       IF vrecord.address != NEW.address THEN
       RAISE EXCEPTION 'There already is a record for % in entities. Its address is %. It conflics with your address %',
                       NEW.entity_name, vrecord.address, NEW.address USING ERRCODE = 'unique_violation';
       END IF;
     END IF; -- Nothing more to do, the entity already exists and is OK
     -- We now try to insert the employee data. Let's directly try an INSERT
     BEGIN
       INSERT INTO employees (employee_name, entity_name) VALUES (NEW.employee_name, NEW.entity_name);
       EXCEPTION WHEN unique_violation THEN
         RAISE EXCEPTION 'There is already an employee with this name %', NEW.employee_name USING ERRCODE = 'unique_violation';
     END;
   RETURN NEW; -- The trigger succeeded
   END IF;
 END
 $$
 ;

Il ne reste plus qu'à déclarer notre trigger maintenant:

 =#CREATE TRIGGER trig_dml_emp_entity INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_entity FOR EACH ROW EXECUTE PROCEDURE dml_emp_entity ();

Il y a d'autres avantages: une rule ne fait que réécrire la requête. Avec le trigger, nous avons rajouté de la logique, et nous pouvons retourner des messages d'erreur pertinents. Cela permet bien plus facilement de comprendre ce qui a échoué. On peut aussi gérer des exceptions. Nous avons tous les avantages des triggers sur les rules.

  • PL/PgSQL FOREACH IN ARRAY.

C'est devenu bien plus simple de faire une boucle sur un tableau en PL/PgSQL. Jusqu'à maintenant, le mot clé FOR ne fonctionnait que pour boucler sur des recordsets (résultat de requête).

On peut maintenant s'en servir pour boucler sur un tableau.

Avant la 9.1, il aurait fallu écrire quelque chose de ce genre:

 =# CREATE OR REPLACE FUNCTION test_array (parray int[]) RETURNS int LANGUAGE plpgsql AS $$
 DECLARE
   vcounter int :=0;
   velement int;
 BEGIN
   FOR velement IN SELECT unnest (parray)
   LOOP
     vcounter:=vcounter+velement;
   END LOOP;
   RETURN vcounter;
 END
 $$
 ;

Maintenant:

 =# CREATE OR REPLACE FUNCTION test_array (parray int[]) RETURNS int LANGUAGE plpgsql AS $$
 DECLARE
   vcounter int :=0;
   velement int;
 BEGIN
   FOREACH velement IN ARRAY parray
   LOOP
     vcounter:=vcounter+velement;
   END LOOP;
   RETURN vcounter;
 END
 $$
 ;

C'est bien plus facile à lire, et plus performant à l'exécution.

Il y a un autre avantage: nous pouvons découper le tableau quand il est multi-dimensionnel. Voici un exemple, tiré directement de la documentation:

 =#CREATE FUNCTION scan_rows(int[]) RETURNS void AS $$
 DECLARE
   x int[];
 BEGIN
   FOREACH x SLICE 1 IN ARRAY $1
   LOOP
     RAISE NOTICE 'row = %', x;
   END LOOP;
 END;
 $$ LANGUAGE plpgsql;
 
 =#SELECT scan_rows(ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]);
 NOTICE:  row = {1,2,3}
 NOTICE:  row = {4,5,6}
 NOTICE:  row = {7,8,9}
 NOTICE:  row = {10,11,12}