~/sebl[og]

Ce premier billet revient donc sur les ajouts au langage SQL de la version 9.5 de PostgreSQL.

Ces ajouts portent sur de nombreux champs de fonctionnalités, de la sécurité aux gestions de performances en passant par la gestion des transactions.

UPSERT

Ce mot clé désigne en réalité la possibilité d'intercepter une erreur de clé primaire sur un ordre INSERT, et de faire un UPDATE à la place.

Ce principe était déjà faisable, côté client ou avec une procédure stockée, mais l'intégration dans le langage SQL permet une gestion plus simple et plus efficace.

L'exemple suivant montre l'insertion dans une table d'enregistrement de statistiques.

Lorsque la clé primaire id existe déjà ("ON CONFLICT (id)"), alors une ordre UPDATE est exécuté en ajoutant la valeur insérée à la valeur existante :

INSERT INTO user_stats (id, connect)
VALUES (12345,1),(87654,1)
ON CONFLICT (id)
DO UPDATE SET connect = user_stats.connect + EXCLUDED.connect;

La fonctionnalité est explicitement utilisée par le développeur, aisément compréhensible, l'adoption dans les projets est donc tout à fait bénéfique.

Row-Level Security Policies

Cette nouvelle fonctionnalité permet de contrôler l'accès aux données, non pas en fonction des privilèges apposés aux tables et aux colonnes, mais aux tuples dans les tables. Dans la table de l'exemple précédent, lorsque l'administrateur souhaite qu'un utilisateur de puisse voir que les statistiques le concernant, il est possible d'appliquer une politique de sécurité, associant les tuples enregistrés à l'utilisateur connecté :

CREATE POLICY policy_user ON user_stats
   FOR ALL
   TO PUBLIC
   USING (id = usertoid(current_user));

La fonction usertoid() traduit un nom d'utilisateur en id. Pour des questions de performances, il est conseillé de la rendre IMMUTABLE. Une fois la politique suivante appliquée :

ALTER TABLE user_stats ENABLE ROW LEVEL SECURITY;

L'utilisateur ne peut alors voir que les tuples le concernant dans la table user_stats.

GROUPING SETS, CUBE and ROLLUP

Ces fonctions étendent les capacités de regroupement des tuples, fonctionnalité déjà connue par l'utilisation de la clause GROUP BY.

Avec la clause GROUP BY, il est possible de regrouper sur une seule dimension, même plusieurs attributs sont utilisés. Lorsque plusieurs regroupements sont nécessaires, il est alors nécessaire d'écrire plusieurs requêtes, quitte à les concaténer avec UNION ALL.

Ces nouvelles clauses de regroupements permettent d'exprimer plusieurs regroupements dans une même requête. L'exemple suivant requête la base de données employees afin de compter les employées par titre de poste et par genre :

select gender, title, count(*)
    from employees join titles using (emp_no)
  group by
    grouping sets ( ( gender, title ), (title), (gender), () ) ;

Le résultat montre le comptage des quatre regroupements exprimés.

Les trois fonctions ont chacune leur rôle :

• GROUPING SETS ( (attr1, attr2), (attr2), (attr3, attr1) ) : regroupe les résultats selon toutes les combinaisons d'attributs exprimées.
• CUBE( attr1, attr2, attr3 ) : regroupe selon toutes les combinaisons des attributs utilisés
• ROLLUP( attr1, attr2, attr3 ) : regroupe selon toutes les combinaisons séquentielles (de gauche à droite). Très proche de CUBE(), cette fonction ignore les combinaisons des attributs que ne se suivent pas dans la séquence indiquée. Contrairement à CUBE(), il n'y aura pas la combinaison (attr1, attr3) dans le résultat.

Ces fonctions sont très utiles dans le cadre de bases de données analytiques (OLAP)

TABLESAMPLE

Cette fonctionnalité permet d'extraire des échantillons de données d'une table, afin de calculer une approximation d'un résultat, beaucoup plus rapidement que le calcul réel. Par exemple, le calcul du salaire moyen des salaires de la base de données employes peut prendre plusieurs secondes, alors qu'un calcul approximatif est beaucoup plus rapide :

employees=$ 
select avg(salary) from salaries ;
-[ RECORD 1 ]-----------
avg | 63810.744836143706
Time: 318.682 ms
employees=$ select avg(salary) from salaries TABLESAMPLE system ( 0.1 );
-[ RECORD 1 ]-----------
avg | 62681.119426751592
Time: 2.552 ms
employees=$ select avg(salary) from salaries TABLESAMPLE system ( 0.01 );
-[ RECORD 1 ]-----------
avg | 62807.193205944798
Time: 0.584 ms

Dans ce cas, l'algorithme de sélection repose sur le système d'exploitation. Un algorithme plus réaliste, BERNOULLI, est utilisable, mais plus coûteux.

IMPORT FOREIGN SCHEMA

Cette nouvelle instruction permet d'importer automatiquement des tables étrangères, en utilisant les fonctionnalités SQL/MED de PostgreSQL. Auparavant, il était possible de créer une table étrangère en précisant les attributs et les types de données.

Cette fonctionnalité déduit automatiquement le modèle de la table distante, et crée automatiquement la « table étrangère » avec les attributs et les types de données ainsi trouvés. Par défaut, toutes les tables d'un espace de nom sont importées, mais il est possible d'exclure des tables, ou, au contraire, de lister les tables à inclure.

Il est cependant nécessaire que le pilote SQL/MED supporte la fonctionnalité, ce qui n'est le cas que pour le pilote postgres_fdw, fourni avec PostgreSQL, pour le moment.

Opérateurs et Fonctions JSONB

Le type de données jsonb est une des grandes nouveautés de la version majeure précédente, la 9.4, ce qui permet à PostgreSQL de figurer parmi les bases de données orientées "document", la puissance du SQL en plus.

Cette version 9.5 se voit enrichie de fonctions permettant de modifier ce type de données directement, sans devoir passer par une extraction des données :

• l'opérateur || permet de concaténer des données jsonb
• l'opérateur - permet de supprimer une clé d'une donnée jsonb
• la fonction jsonb_set permet de mettre à jour la valeur d'une clé d'une donnée jsonb
• la fonction jsonb_pretty permet de créer un rendu texte lisible depuis une donnée jsonb.

ALTER TABLE ... SET LOGGED / UNLOGGED

La version 9.1 de PostgreSQL apportait la possibilité de ne pas enregistrer une table dans les WAL (journaux de transactions), ce qui accélérait grandement les imports de données en masse. Mais les données n'étaient pas sauvegardées ni repliquées, il était donc nécessaire de copier la table ainsi créée dans une table « régulière » afin d'assurer la durabilité des données.

Il est maintenant possible de modifier cette propriété avec une simple commande, ce qui simplifie les opérations de la part du DBA :

ALTER TABLE user_stats SET LOGGED ;

SKIP LOCKED

Cette clause modifie le comportement de la clause FOR UPDATE d'un ordre SELECT.

Lorsqu'une ligne est modifiée dans une transaction concurrente, l'utilisation d'un SELECT FOR UPDATE sur cette même ligne est bloquante.

L'ajout de la clause SKIP LOCKED ignore simplement la ligne verrouillée, et ne prend alors en compte que les lignes non verrouillées.

CREATE INDEX IF NOT EXISTS

Une nouvelle clause enrichi l'ordre CREATE INDEX. Lorsqu'un index exsiste déjà, cette clause permet de ne pas provoquer d'erreur, afin de ne pas interrompre l'exécution d'une transaction.

Cette clause n'est utilisable que indiquant explicitement le nom, et non pas lorsque le nom est omis et implicitement généré par le moteur. De plus, cela ne protège pas de la création d'un index indentique mais avec un nom différent. Exemple :

employees=# create unique index departments_dept_name_idx ON departments (dept_name) ; 
ERROR:  relation "departments_dept_name_idx" already exists
employees=# create unique index IF NOT EXISTS departments_dept_name_idx ON departments (dept_name) ; 
NOTICE:  relation "departments_dept_name_idx" already exists, skipping
CREATE INDEX
employees=# create unique index IF NOT EXISTS departments_dept_name_idx_fake ON departments (dept_name) ; 
CREATE INDEX
employees=# drop index departments_dept_name_idx_fake;
DROP INDEX

Le prochain article présentera les nouveautés internes au moteur PostgreSQL