PostgreSQL 9.5 disponible en téléchargement
Le SGBDRO embarque la fonction UPSERT et mise sur le Big Data et la sécurité

Le , par Michael Guilloux, Chroniqueur Actualités
Un an après la sortie de PostgreSQL 9.4, les utilisateurs du système populaire de gestion de bases de données relationnelles et objet (SGBDRO) PostgreSQL peuvent bénéficier d’une nouvelle version majeure. Le PostgreSQL Global Development Group vient en effet d’annoncer la sortie de PostgreSQL 9.5 en mettant en avant la fonction UPSERT, les politiques de Row Level Security, ainsi que des fonctionnalités Big Data.

À la demande des développeurs, la fonction UPSERT (diminutif d’INSERT, ON CONFLICT UPDATE) est enfin implémentée dans PostgreSQL. D’après l’annonce officielle, cette fonction vise à « simplifier le développement d’applications web et mobiles en déléguant à la base de données la gestion des éventuels conflits lors de modifications concurrentes ». « La nouvelle clause ON CONFLICT permet d’ignorer certaines données ou de mettre à jour d’autres colonnes ou tables, de manière à supporter les traitements complexes lors de chargement de données avec un ETL. »

Cette nouvelle version met encore l’accent sur la sécurité de vos données avec une nouvelle fonctionnalité appelée Row Level Security (RLS). Comme son nom peut l’indiquer, RLS permet une gestion des droits des utilisateurs par ligne et par colonne. Cela pourrait permettre d’offrir une meilleure résistance contre les attaques par injection SQL et les failles de sécurité au niveau applicatif.

Côté Big Data, il faut retenir que PostgreSQL 9.5 est également conçu pour les grands volumes de données. Le SGBDRO intègre de nombreuses fonctionnalités Big Data. Entre autres, on peut noter par exemple la commande SQL TABLESAMPLE qui permet de renvoyer rapidement un échantillon des données d’une table. On retrouve encore trois nouvelles clauses issues de la norme SQL (CUBE, ROLLUP et GROUPING SETS) qui permettent aux utilisateurs de créer des rapports avec plusieurs niveaux d’agrégation de données en utilisant une seule requête SQL.

PostgreSQL peut désormais effectuer les tris de données textuelles et NUMERIC plus rapidement sur de grands ensembles de données. Les requêtes qui font des tris peuvent être 2 à 12 fois plus rapides selon l’annonce officielle du PostgreSQL Global Development Group. La création d’index, quant à elle, se fera jusqu’à 20 fois plus vite. Cela a été rendu possible grâce à un algorithme appelé « abbreviated keys ». Comme autre nouveauté pour le traitement des grands volumes de données, on note aussi les index BRIN qui offrent une nouvelle méthode pour créer des index plus légers, mais plus efficaces sur les tables volumineuses et « naturellement ordonnées ».

Déjà présents dans les versions précédentes de PostgreSQL, les Foreign Data Wrappers (FDW) s’améliorent dans cette nouvelle version. Les FDW permettent d’utiliser PostgreSQL comme un moteur de recherche pour des systèmes Big Data comme Hadoop et Cassandra. Dans la version 9.5, les développeurs du SGBDRO ajoutent la commande IMPORT FOREIGN SCHEMA et le transfert des ordres JOIN sur les serveurs distants. Cela permet de simplifier et optimiser l’accès aux sources de données externes.

Plus de détails sur les nouveautés dans PostgreSQL 9.5 sont disponibles sur le site officiel. La nouvelle version est également disponible en téléchargement.

Télécharger PostgreSQL 9.5

Source : Blog PostgreSQL

Et vous ?

Utilisez-vous PostgreSQL ? Que pensez-vous de cette nouvelle version ?

Voir aussi

Forum PostgreSQL


Vous avez aimé cette actualité ? Alors partagez-la avec vos amis en cliquant sur les boutons ci-dessous :


 Poster une réponse

Avatar de MichaelREMY MichaelREMY - Membre confirmé https://www.developpez.com
le 08/01/2016 à 16:26
UPSERT sert-elle à mettre à jour un enregistrement dans le cas où l'insertion serait un échec dû à un duplicat ?

ou est-ce l'inverse ? c-a-d UPSERT permet de créer un tuple si on a essayé avec échec de mettre à jour un enregistrement dont l’id n'existe pas-plus ?

Dans les deux, n'est-ce pas de la responsabilité du "bon développeur responsable" de s'assurer de l'existence ou de la non-existence d'un tuple (ou de son id) avant sa mise-à-jour/création.
Avatar de Shepard Shepard - Membre éprouvé https://www.developpez.com
le 09/01/2016 à 20:49
La commande en PostGreSQL ne s'appelle pas UPSERT mais "ON CONFLICT UPDATE", qui est bien plus explicite je pense :-)

Le cas d'utilisation est simplement lorsqu'on veut s'assurer que la donnée se trouvera dans la base de données, peu importe si elle était déjà présente ou pas. C'est parfois pratique, mais ça ne reste "que" du sucre syntaxique .
Avatar de dasdeb dasdeb - Membre habitué https://www.developpez.com
le 14/01/2016 à 1:50
Pour moi ça ressemble au INSERT... ON DUPLICATE UPDATE de MySQL, qui existe depuis longtemps
Avatar de T`lash T`lash - Membre confirmé https://www.developpez.com
le 14/01/2016 à 10:45
Avec l'instruction ON CONFLICT, on pourra remplacer ceci :

Code : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE FUNCTION upsert_user(u_name text, u_fullname text, u_email text) RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
    userid users.id_user%TYPE;
BEGIN
    LOOP
        UPDATE users SET "fullname" = u_fullname, "email" = u_email WHERE "name" = u_name RETURNING "id_user" INTO userid;
        IF FOUND THEN
            RETURN userid;
        END IF;
        BEGIN
            INSERT INTO users ("name", "fullname", "email") VALUES (u_name, u_fullname, u_email) RETURNING "id_user" INTO userid;
            RETURN userid;
            EXCEPTION WHEN unique_violation THEN
                
        END;
    END LOOP;
END;
$$;

SELECT * FROM upsert_user(`jdoe`, `John Doe`, `john.doe@developpez.com`);
Par ceci :

Code : Sélectionner tout
1
2
3
4
INSERT INTO users ("name", "fullname", "email")
VALUES (`jdoe`, `John Doe`, `john.doe@developpez.com`)
ON CONFLICT DO UPDATE SET "fullname" = `John Doe`, "email" = `john.doe@developpez.com`
RETURNING "id_user";
L'avantage est de ne pas être obligé de passer par une procédure stockée.
Intéressant pour l'avenir, mais je ne vais pas pour autant modifier mes anciens projets.
Avatar de MichaelREMY MichaelREMY - Membre confirmé https://www.developpez.com
le 14/01/2016 à 11:01
quelqu'un peut-il me dire dans quel cas fonctionnel, quelle situation (dans la vie réelle, dans une vraie application développée sérieusement), on arrive à un cas où l'on met à jour un enregistrement qui n'est pas existant (qui n'a pas d'id) ?

ça semble être un moyen de dissimuler/amortir un bug produit dans l'application (qui met à disposition des enregistrements sans ID alors qu'elle ne devrait pas) ?
Quelle situation de la vie d'un processus autoriserait un tel process : créer un ID quand une identité n'existe pas ... c'est bancal non ?

Si on met à jour un enregistrement et que l'id n'existe pas, la moindre des choses est de retourner une erreur pour enquêter de la disparition de cet ID (ou du filtrage écran trop strict) plutôt que de recréer une identité.
Avatar de T`lash T`lash - Membre confirmé https://www.developpez.com
le 14/01/2016 à 12:33
Tu utilises généralement l'UPSERT pour intégrer un objet d'une base de données externe à ta base.
Je l'utilise par exemple dans le cas de l'authentification Active Directory à l'IntraWeb PHP de la société : si l'utilisateur se connecte pour la première fois au système son nom de compte n'existe pas au sein de la base de données, mais tu cherches à obtenir la clé primaire dans la table users. Dans ce cas l'UPSERT insert une ligne et te renvoie l'id de la ligne insérée. Lorsqu'il se connectera pour la seconde fois il te renverra l'id de la ligne existante. Il en va de même pour les groupes auxquels cet utilisateur appartient, qui doivent exister localement pour servir au système de gestion d'accès de l'appli alors que leur création, modification et suppression ne se fait que dans Active Directory.

Certains dans ce cas utiliseront deux requêtes distinctes et diront que l'UPSERT ne sert à rien, mais imagine un cas pratique très simple : le verrouillage du formulaire de login est défaillant et la requête est envoyée à deux reprises dans un intervalle très court (double clic sur le bouton d'envoi). Nous aurons donc 2 accès concurrents qui vont vérifier l'existence du nom d'utilisateur et chercher à le créer. Le premier réussira, mais le second va lever une exception. Pour faciliter cela on peut, grâce à l'implémentation du concept UPSERT (ON CONFLICT DO), gérer tout cela en une seule opération : insérer, sinon mettre à jour ou simplement récupérer.
Avatar de al1_24 al1_24 - Modérateur https://www.developpez.com
le 14/01/2016 à 12:46
Il me semble que la commande MERGE apparue dans la norme SQL-2003 opère de manière semblable.
Code : Sélectionner tout
1
2
3
4
5
MERGE INTO ma_table [AS] tgt
USING ( requete ) [AS] src
ON ( src.cle = tgt.cle )
WHEN MATCHED THEN UPDATE SET tgt.col = src.col [, ...]
WHEN NOT MATCHED THEN INSERT (cle, col [, ...]) VALUES (src.cle, src.col [, ...]);
Je trouve dommage d'avoir créé une commande spécifique plutôt que se conformer à la norme.
Avatar de T`lash T`lash - Membre confirmé https://www.developpez.com
le 14/01/2016 à 13:30
Il existe plusieurs raisons à cela, mais la principale est que PostgreSQL se veut un SGBDR collant parfaitement à la norme SQL, ce qui fait qu'ils n'implémentent une instruction que lorsqu'ils sont sûrs de le faire de la bonne manière.
Oracle et MSSQL utilisent la syntaxe du MERGE pour l'implémentation de l'UPSERT, mais la plupart des SGBDR ont préféré (comme c'est le cas pour PostgreSQL) de choisir un autre mot réservé car la norme SQL-2003 ne définit pas de manière précise le comportement de cette instruction.
Si ce comportement devait être détaillé dans une future norme, ils ne veulent pas que leur implémentation du MERGE n'entre en conflit avec cette norme.

Source : https://wiki.postgresql.org/wiki/UPS...tax_discussion

MERGE disadvantages

The SQL standard doesn't specify concurrency behaviour for MERGE any more than it does for INSERT, UPDATE, or DELETE; so (like other DML) our behavior with concurrent access may not be the same as other database products. In particular, the SQL standard does not require that MERGE be atomic in the sense of atomically providing either an INSERT or UPDATE, and other products do not provide any such guarantees with their MERGE statement.

The SQL-standard MERGE doesn't provide for choosing an index, so use of a unique index would need to be conditioned on equality quals against indexed columns in order to provide the behavior being discussed for UPSERT.

Implementing an extended subset of MERGE support for UPSERT will impact a future implementation of full MERGE support:
Different concurrency and locking semantics will be needed for a MERGE without equality quals matching a unique index of the target table.
Users may be very surprised when failure to compare for equality on the columns of a unique index results in slower execution or less graceful interaction with other transactions, such as deadlocks.
The design for MERGE when the conditions don't allow joining using a unique index has not yet been done. It may require different locking, allow deadlocks or other serialization failures, or allow the same anomalies which can occur with other DML statements, and which will not be possible when the unique index is used.

If a subquery (rather than a direct table reference) is used for the second relation reference, there is no restriction on what that subquery may join on. Delineating the cases where we must use a dirty snapshot, and where we must use an MVCC snapshot seems very difficult and subtle - Peter Geoghegan [14] [15].

The ON expression will need to be evaluated to see whether it properly compares to a unique index on the target table. Initially this will need to be done to determine whether the MERGE is allowed at all; later it will determine which sort of plan is allowed. It would be easier to match an index name, provided the index name is known and stable.

MERGE will probably need to fire before row insert triggers after deciding to insert, since in general it cannot reverse the triggers having been fired - the implementation should probably have decided on insertion by then. In contrast, UPSERT will have to fire before row triggers before deciding to INSERT or UPDATE (by value locking the value extracted from the affected tuple) [16]. With UPSERT, we are deciding to take the alternative path based on the modified tuple. With MERGE, we may prefer not to.
Avatar de MichaelREMY MichaelREMY - Membre confirmé https://www.developpez.com
le 14/01/2016 à 17:01
Citation Envoyé par T`lash Voir le message
Je l'utilise par exemple dans le cas de l'authentification Active Directory à l'IntraWeb PHP de la société : si l'utilisateur se connecte pour la première fois au système son nom de compte n'existe pas au sein de la base de données, mais tu cherches à obtenir la clé primaire dans la table users. Dans ce cas l'UPSERT insert une ligne et te renvoie l'id de la ligne insérée. Lorsqu'il se connectera pour la seconde fois il te renverra l'id de la ligne existante. Il en va de même pour les groupes auxquels cet utilisateur appartient, qui doivent exister localement pour servir au système de gestion d'accès de l'appli alors que leur création, modification et suppression ne se fait que dans Active Directory.
donc tu as deux tables d'utilisateurs, c'est répétitif et dangereux sur le point de la sécurité. pourquoi ne pas avoir ajouter à ton intraweb la possibilité d'aller lire l'active directory plutôt que de le redondancer comme ça ? ou de créer une VUE ?

quand une nouvelle personne arrive dans l'entreprise, vous l'inscrivez dans l'activedirectory, mais pas dans votre intranet ? pourquoi ?
Avatar de T`lash T`lash - Membre confirmé https://www.developpez.com
le 14/01/2016 à 18:17
L'intraweb interroge l'Active Directory à chaque connexion d'un utilisateur et récupère les informations de l'utilisateur.
La base de données Postgres ne contient pas plus d'informations que ce que l'intraweb a besoin pour fonctionner (username, displayname et email).
Il faut cependant qu'une ligne existe pour chaque utilisateur afin de conserver une intégrité référentielle par rapport aux autres tables de la base.
Et puisque le but est de conserver une cohérence au sein de tout l'intranet, il est logique que les informations soient conserver par Active Directory puisque c'est cet annuaire qui gère les connexions aux postes de travail.
Il n'est pas possible de créer, modifier ou supprimer directement un utilisateur ou un groupe en base de données puisque les infos sont lus à chaque connexion depuis l'AD et qu'aucun mot de passe n'est conservé par Postgres.

Je pense que c'est un cas d'utilisation de l'UPSERT tout ce qu'il y a de plus logique.
Contacter le responsable de la rubrique Accueil