Discussion :

[Malick]

PostgreSQL 9.5 est disponible en version bêta 1,
et intègre la correction de nombreux bogues signalés depuis la version alpha 2

Le PostgreSQL Global Development Group a annoncé la sortie de la première version bêta de PostgreSQL 9.5. Cette dernière, selon l'éditeur, contient toutes les fonctionnalités et les API qui seront intégrées dans la version finale. Peu de modifications devraient intervenir.

L'éditeur affirme qu’avec cette première version bêta de PostgreSQL 9.5, les utilisateurs peuvent maintenant tester leurs applications, cela en préparation de la version finale.

Le PostgreSQL Global Development Group souligne également que cette nouvelle version du célèbre système de gestion de bases de données relationnelles et objets intègre plusieurs corrections de bogues qui avaient été signalés depuis la sortie de la version Alpha 2. Parmi les améliorations apportées, nous pouvons citer :

• de nombreux ajustements à la sémantique du Row Level Security (RLS) ;
• des améliorations des deadlocks avec LWLock ;
• les problèmes de corruption d'index BRIN ;
• les difficultés relatives à la connexion avec PGSSLMODE=require sous Windows ;
• différents problèmes de traçage des timestamp de commit ;
• les fuites de mémoire des hash join ;
• le comportement incohérent de jsonb_set lors de l'ajout dans un tableau.

L'éditeur souligne également la modification de la sémantique du Row Level Security pour plus de cohérence avec le système de permissions de PostgreSQL basé sur GRANT. À titre d’exemple, la RLS applique désormais à la fois les politiques d'INSERT et SELECT lorsque l'INSERT RETURNING est utilisé. Ceci étant, les utilisateurs sont invités à tester l'application des règles TLS et à retester toute configuration RLS existante afin de s’assurer qu’il n’y a pas de régression dans leurs cas d'utilisation.

Le PostgreSQL Global Development Group ajoute que ceci est la première version bêta de la version 9.5 ; ce qui veut dire que très peu de changements visibles par l'utilisateur sont attendus avant la version finale. Le projet PostgreSQL publiera des bêtas supplémentaires requis pour les tests à venir, cela jusqu'à la sortie de la version finale prévue en fin de 2015.

Téléchargez PostgreSQL 9.5 bêta 1

Consultez la note de version

Source : annonce officielle

Et vous ?

Que pensez-vous de cette nouvelle version bêta ?
Allez-vous la tester ?

Voir aussi :

le Forum PostgreSQL

[Michael Guilloux]

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

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

[MichaelREMY]

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.

[Shepard]

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 .

[dasdeb]

Pour moi ça ressemble au INSERT... ON DUPLICATE UPDATE de MySQL, qui existe depuis longtemps

[T`lash]

Avec l'instruction ON CONFLICT, on pourra remplacer ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 - Visualiser dans une fenêtre à part

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.

[MichaelREMY]

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é.

[T`lash]

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.

[al1_24]

Il me semble que la commande MERGE apparue dans la norme SQL-2003 opère de manière semblable.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[T`lash]

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.

[MichaelREMY]

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 ?

[T`lash]

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.

[alassanediakite]

Salut

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) ?
...

Dans le cas particulier où vos données sont mises à jour à partir d'une source externe (CSV, ...) cette opération est nécessaire.
Aussi vous avez là une bonne solution pour implémenter une réplication partielle (très semblable au premier cas).
@+

[MichaelREMY]

Salut

Dans le cas particulier où vos données sont mises à jour à partir d'une source externe (CSV, ...) cette opération est nécessaire.
Aussi vous avez là une bonne solution pour implémenter une réplication partielle (très semblable au premier cas).
@+

je suis désolé d'insister, mais je ne vois pas l'utilité.
c'est très dangereux de mettre à jour des données sans se baser sur son identifiant car risque de doublon..etc.
par exemple dans mon cas, bcp de gens orthographent mal mon prénom (mettre un K au lieu du H), une fois que je leurs demande de corriger leurs bases, ça veut dire que dans votre cas (import csv), je vais apparaître deux fois dans la base de destination....

Si vous me répondez alors que ça ne sert qu'à mettre à jour/insérer des données exclusivement chiffrées/numériques , alors là je comprends mieux car le risque est moindre.

[T`lash]

De la même manière que dans un échange B2B, il faut avoir une confiance absolu dans sa source.
Dans mon cas avec Active Directory, il n'y a pas grand risque, surtout que la connexion LDAP est chiffrée.

[alassanediakite]

Salut

je suis désolé d'insister, mais je ne vois pas l'utilité.

Peut-être qu'on arrive pas à t'expliquer l'utilité de la chose, mais sache qu'on trouve "MERGE" dans SQL Server, ORACLE, DB2 MySQL, Firebird, PostgreSQL. Ce n'est pas pour rien que tout ces systèmes implémentent la même chose (chacun le faisant un peu en sa manière).
Dans ton cas(le changement de prénom), je reçoit la ligne (sur le csv) avec CLE et prénom: si la clé existe alors je mets à jour sinon j’insère CLE et prénom.
Voir ici.

[SQLpro]

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.

Ne voyez vous pas quelques contradictions dans vos propos :
1) PG colle à le norme et donc évite le normatif MERGE pour un autre mot !
2) "la plupart des SGBDR ... ont préférés choisir un autre mot réservé" Ha bon ! Lesquels ?????
3) "la norme SQL-2003 ne définit pas de manière précise le comportement de cette instruction" Vous croyez sincèrement ce que vous dites ????

Pour vous punir, je me permets de vous communiquer l'extrait consacré au MERGE dans la norme SQL 2003 (7 pages de description... !)... Au passage il aura juste fallut attendre 12 ans pour voir cela arriver partiellement avec une syntaxe anormative dans PosteGreSQL alors que SQL Server ou Oracle ont cela depuis 10 ans environ...

Norme SQL 2003-foundation-MERGE statement v.pdf

A +

[T`lash]

Ne voyez pas une supposition de ma part dans le message que vous avez cité, mais un résumé de la page de discussion du Wiki officiel PostgreSQL que j'ai référencé comme source.
C'est la justification avancée par l'équipe de PostgreSQL.

[Logan Mauzaize]

La commande en PostGreSQL ne s'appelle pas UPSERT mais "ON CONFLICT UPDATE", qui est bien plus explicite je pense :-)

Mouais un mot/une expression ou une autre, cela ne change pas grand chose.

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 .

J'ose expérer que c'est plus que du sucre syntaxique. Le principe d'envoyer en une instruction c'est de permettre au serveur d'optimiser les accès.

L'avantage est de ne pas être obligé de passer par une procédure stockée.

Pour moi la procédure stockée n'est pas strictement équivalente. Dans la procédure stockée, si l'update échoue il ne verrouille pas la clé. Il peut donc y avoir une attente de libération de verrou entre les deux instructions et donc l'insertion peut échouer. Ce qui ne devrait pas arriverer avec l'instruction "UPSERT".

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é.

Comme indiqué dans la news, c'est ultra-pratique voir indipensable, pour tout ce qui est ETL et de manière générale la synchronsation massive. Faire un update + insert pour chaque enregistrement est beaucoup trop pénalisant en termes de performance.

Toutes personnes ayant eu à gérer des référentiels externes ou des aggrégations relativement coûteuses à ce genre de problèmatique à traiter. Je pense que beaucoup de projets doivent avoir dans leurs spécifications le fameux "mot clé" : "annule et remplace". C'est ce que permet de faire ce genre d'opérateur dans une optique "sans interruption" de service.

Il me semble que la commande MERGE apparue dans la norme SQL-2003 opère de manière semblable.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

Pour autant que je me souvienne, la commande "MERGE" ne permet pas vraiment l'insertion d'un tuple donné mais l'injection d'une source de données (table, vue, requête) dans une table (ou une vue updatable). Sous Oracle, je l'ai toujours utilisé pour synchroniser une table "résultante" d'aggrégations.