Via www.loxodata.com : Les plages de valeurs (Partie 1)

Dans la suite des articles présentant des fonctionnalités du langage SQL disponible dans PostgreSQL (pas forcément présentes dans la norme SQL), nous allons parler d’une fonctionnalité SQL intéressante, les plages de valeurs (RANGE), je vous conseille de lire, si vous ne l’avez pas déjà fait, l’article sur les contraintes d’exclusion

Qu’est-ce qu’une plage de valeurs ?

Une plage de valeurs représente un nombre de valeurs plus ou moins grand en une seule valeur intervalle. Il existe plusieurs types de données d’intervalles dérivés de types scalaires.

• integer : int4range
• bigint : int8range
• numeric : numrange
• timestamp without time zone : tsrange
• timestamp with time zone : tstzrange
• date : daterange

Un intervalle de valeurs a deux bornes : une borne inférieure et une borne supérieure. Les valeurs peuvent être incluses ou non, selon les bornes utilisées.

La borne inclusive est représentée par les caractères [ (borne inférieure) et ] (borne supérieure) et les bornes exclusives par ( (borne inférieure) et ) (borne supérieure).

Construction d’une plage de valeurs

Afin de construire une donnée de l’un de ces types, il est possible d’utiliser un litéral avec un typage explicite, par exemple :

SELECT '[1,10)'::int4range;
┌───────────┐
│ int4range │
├───────────┤
│ [1,10)    │
└───────────┘

ou :

SELECT '[2020-06-26,2020-06-28)'::daterange;
┌─────────────────────────┐
│        daterange        │
├─────────────────────────┤
│ [2020-06-26,2020-06-28) │
└─────────────────────────┘

Il est possible de ne pas renseigner une des deux bornes, incluant dans la plage de valeurs toutes les valeurs plus petite ou plus grande que la borne indiquée, par exemple :

SELECT '[1,)'::int4range;
┌───────────┐
│ int4range │
├───────────┤
│ [1,)      │
└───────────┘

ou :

SELECT '[,2020-06-26)'::daterange;
┌───────────────┐
│   daterange   │
├───────────────┤
│ (,2020-06-26) │
└───────────────┘

Par ailleurs, la construction d’une plage de valeurs peut se faire à l’aide d’une fonction spécifique à chaque type, le constructeur, qui accepte 2 ou 3 paramètres.

Les deux premiers paramètres sont respectivement la borne inférieure et supérieure et le troisième paramètre, optionnel, représente le mode de bornage : par défaut elle est inclusive pour la borne inférieure et exclusive pour la borne supérieure.

Dans l’exemple ci-après, l’intervalle va de la valeur 10 incluse à la valeur 20 exclue :

SELECT int4range(10, 20);
┌───────────┐
│ int4range │
├───────────┤
│ [10,20)   │
└───────────┘

Si on souhaite modifier le bornage par défaut, on utilisera le troisième paramètre, dans ce cas les bornes sont inclusives :

SELECT numrange(10.5, 20.2, '[]');
┌─────────────┐
│  numrange   │
├─────────────┤
│ [10.5,20.2] │
└─────────────┘

Cependant, il y a des exceptions :

SELECT daterange('2020-06-25','2020-06-27', '(]');
        daterange
┌─────────────────────────┐
│        daterange        │
├─────────────────────────┤
│ [2020-06-26,2020-06-28) │
└─────────────────────────┘

Comme le montre l’exemple précédent, l’affichage de la valeur ne correspond pas au paramètre '(]', la raison est que int4range, int8range, daterange sont des types intervalles de valeurs discrètes, la valeur est donc convertie sous la forme canonique '[)'.

Les opérateurs

En plus des opérateurs classiques (=,<> …), d’autres sont disponibles, en particulier l’opérateur permettant de tester le chevauchement de deux plages.

Voici quelques opérateurs utiles dans la gestion des plages de temps :

• Égalité entre 2 intervalles (=):

SELECT tsrange('2020-06-26 18:00','2020-06-26 22:00')
    =  tsrange('2020-06-26 18:00','2020-06-26 22:00')
┌────┐
│ eq │
├────┤
│ t  │
└────┘

ou :

SELECT tsrange('2020-06-26 18:00','2020-06-26 22:00')
    =  tsrange('2020-06-26 18:01','2020-06-26 22:00') eq
┌────┐
│ eq │
├────┤
│ f  │
└────┘

• Supériorité / infériorité avec > et <. Les comparaisons s’effectuent sur la borne inférieure de l’intervalle :

SELECT tsrange('2020-06-26 18:01','2020-06-26 18:02')
    >  tsrange('2020-06-26 17:59','2020-06-26 22:00') as sup
┌─────┐
│ sup │
├─────┤
│ t   │
└─────┘

ou :

SELECT tsrange('2020-06-26 18:01','2020-06-26 18:02')
    <  tsrange('2020-06-26 17:59','2020-06-26 22:00') as inf
┌─────┐
│ inf │
├─────┤
│ f   │
└─────┘

• Chevauchement &&. Les plages testées ont-elles des valeurs communes :

SELECT tsrange('2020-06-26 18:00','2020-06-26 22:00')
    && tsrange('2020-06-26 20:00','2020-06-26 22:00') as overlap;
┌─────────┐
│ overlap │
├─────────┤
│ t       │
└─────────┘

• Contient @>. La plage de gauche contient-elle la plage de droite :

SELECT tsrange('2020-06-26 12:00','2020-06-26 22:00')
    @> tsrange('2020-06-26 16:00','2020-06-26 18:00') as contains_range;
┌────────────────┐
│ contains_range │
├────────────────┤
│ t              │
└────────────────┘

ou :

SELECT tsrange('2020-06-26 12:00','2020-06-26 22:00')
    @> '2020-06-26 16:00'::timestamp as contains_element;
┌──────────────────┐
│ contains_element │
├──────────────────┤
│ t                │
└──────────────────┘

• Contenu par <@. La plage de gauche est-elle contenue dans la plage de droite :

SELECT tsrange('2020-06-26 18:00','2020-06-26 19:00')
    <@ tsrange('2020-06-26 16:00','2020-06-26 20:00') as range_is_contained_by
┌───────────────────────┐
│ range_is_contained_by │
├───────────────────────┤
│ t                     │
└───────────────────────┘

ou :

SELECT '2020-06-26 18:00'::timestamp
    <@ tsrange('2020-06-26 16:00','2020-06-26 20:00') as element_is_contained_by;
┌─────────────────────────┐
│ element_is_contained_by │
├─────────────────────────┤
│ t                       │
└─────────────────────────┘

Vous trouverez l’ensemble des informations complémentaires dans la documentation sur les opérateurs.

Les fonctions

Après les opérateurs, voyons à présent les fonctions disponibles pour ces types de données.

• Limite basse de l’intervalle : lower() :

SELECT lower(tsrange('2020-06-26 18:01','2020-06-26 18:02'));
┌─────────────────────┐
│        lower        │
├─────────────────────┤
│ 2020-06-26 18:01:00 │
└─────────────────────┘

• Limite haute de l’intervalle : upper() :

SELECT upper(tsrange('2020-06-26 18:01','2020-06-26 18:02'));
┌─────────────────────┐
│        upper        │
├─────────────────────┤
│ 2020-06-26 18:02:00 │
└─────────────────────┘

• La limite inférieure de l’intervalle est-elle incluse : lower_inc() ?

SELECT lower_inc(tsrange('2020-06-26 18:01','2020-06-26 18:02'));
┌───────────┐
│ lower_inc │
├───────────┤
│ t         │
└───────────┘

ou :

SELECT lower_inc(tsrange('2020-06-26 18:01','2020-06-26 18:02', '()'));
┌───────────┐
│ lower_inc │
├───────────┤
│ f         │
└───────────┘

• La limite supérieure de l’intervalle est-elle incluse : upper_inc() ? Par défaut la borne supérieure n’est pas incluse :

SELECT upper_inc(tsrange('2020-06-26 18:01','2020-06-26 18:02'));
┌───────────┐
│ upper_inc │
├───────────┤
│ f         │
└───────────┘

Donc pour forcer la limite supérieure incluse, il faut le préciser par le troisième paramètre du constructeur qui représente le mode de bornage :

SELECT upper_inc(tsrange('2020-06-26 18:01','2020-06-26 18:02', '[]'));
┌───────────┐
│ upper_inc │
├───────────┤
│ t         │
└───────────┘

• La limite inférieure est-elle infinie (sans borne) : lower_inf() ?

SELECT lower_inf('(,2020-06-26 18:02)'::TSTZRANGE);
┌───────────┐
│ lower_inf │
├───────────┤
│ t         │
└───────────┘

• La limite supérieure est infinie (sans borne) : upper_inf() ?

 SELECT upper_inf('(2020-06-26 18:01,)'::TSTZRANGE);
┌───────────┐
│ upper_inf │
├───────────┤
│ t         │
└───────────┘

• Fusion de plage : range_merge( p1, p2) : renvoie un intervalle incluant la plus petite et la plus grande valeur des deux intervalles :

SELECT range_merge('[2020-06-26 18:01,2020-06-26 18:02)'::TSTZRANGE
                   , '(2020-06-26 18:06,2020-06-26 20:00)'::TSTZRANGE);
┌─────────────────────────────────────────────────────┐
│                     range_merge                     │
├─────────────────────────────────────────────────────┤
│ ["2020-06-26 18:01:00+02","2020-06-26 20:00:00+02") │
└─────────────────────────────────────────────────────┘

Vous trouverez l’ensemble des informations complémentaires dans la documentation sur les fonctions et les types.

Pourquoi l’utiliser ?

Lors de la conception d’une application gérant les réservations pour la location de vélo par exemple, on sera amené à se poser la question: comment stockons-nous l’information des plages de locations ?

On peut imaginer le stockage de cette information par 2 colonnes start_time, end_time et la gestion de l’intégrité des données par déclencheur ou dans le code de l’application.

Une autre solution, l’utilisation d’un type range qui intègre le concept de chevauchement grâce à l’opérateur &&, avec l’extension btree_gist qui fournit des classes d’opérateurs utilisables par l’index GiST, il est facile de garantir l’unicité de l’intervalle avec une contrainte d’exclusion.

De cette manière le contrôle est directement fait par le type de données et de façon transactionnelle.

Conclusion

Cette première partie vous ayant présenté les plages de valeurs, pour illustrer l’intérêt de ce type, nous allons dans un second article passer à la pratique en prenant l’exemple d’une application dont la fonction est de gérer les réservations de la location de vélos.

Nous comparerons la mise en oeuvre et les performances d’une plage de dates et de la gestion d’intervalle avec l’utilisation de date de début et de fin utilisant les types scalaires traditionnels.

Lire la suite sur www.loxodata.com...

Nouvelles hebdomadaires de PostgreSQL - 5 juillet 2020

Foss4G a été annulée. La prochaine conférence FOSS4G est prévue pour août 2021 à Buenos Aires (Argentine) : https://2020.foss4g.org/cancellation/

Personne de la semaine : https://postgresql.life/post/andrew_dunstan/

Les nouveautés des produits dérivés

• ORC Foreign Data Wrapper 1.0.0, un FDW pour les fichiers au format ORC : https://github.com/HighgoSoftware/orc_fdw/releases/tag/ORC_FDW-REL-1-0
• pgAdmin4 4.23, un centre de contrôle pour PostgreSQL en web & GUI native : https://www.pgadmin.org/docs/pgadmin4/dev/release_notes_4_23.html
• pg_probackup 2.4.1, un utilitaire pour gérer les sauvegardes et les restaurations de clusters PostgreSQL : https://github.com/postgrespro/pg_probackup/releases/tag/2.4.1
• CYPEX 1.0, une boîte à outils permettant de construire des applications orientées base de données avec PostgreSQL : https://www.cybertec-postgresql.com/en/products/cypex/

Lire la suite...

Via sebastien.lardiere.net : PostgreSQL Hebdo #66

Lu cette semaine :

• system_stats extension for PostgreSQL
• Join strategies and performance in PostgreSQL
• Types of Indexes in PostgreSQL
• Global Indexes
• SQL Injection Double Uppercut :: How to Achieve Remote Code Execution Against PostgreSQL Note: ceci ne fonctionne qu'en tant que superuser. Ne faites jamais fonctionner vos applications en superuser, et ne laissez jamais les utilisateurs se connecter en tant que superuser ! jamais !

À venir dans PostgreSQL 13 :

• PostgreSQL 13 en bêta

Agenda :

• Pgconf.eu a lieu du 20 au 23 octobre à Berlin : l'appel à orateur est publié : https://2020.pgconf.eu/call-for-pap...

Lire la suite sur sebastien.lardiere.net...

Via www.loxodata.com : PostgreSQL 13 en bêta

PostgreSQL 13 Bêta 2 publiée

Le PostgreSQL Global Development Group annonce la disponibilité de la seconde bêta de PostgreSQL 13. Cette publication contient un aperçu de toutes les fonctionnalités qui seront disponibles dans la version finale de PostgreSQL 13. Il est, toutefois, possible que quelques détails changent avant cette publication.

Vous trouverez des informations sur toutes les fonctionnalités et modifications à PostgreSQL dans les notes de publication : https://www.postgresql.org/docs/13/release-13.html.

Dans l’esprit de la communauté open source PostgreSQL, nous vous encourageons fortement à tester les nouvelles fonctionnalités dans votre contexte. Ceci afin de nous aider à éliminer les bogues et autres problèmes qui pourraient exister. Si nous ne conseillons pas d’utiliser PostgreSQL 13 Bêta 2 dans vos environnements de production, nous vous encourageons à tester votre charge applicative typique avec cette publication bêta.

Vos tests et commentaires aideront la communauté à s’assurer que PostgreSQL 13 respecte les standards de stabilité et de fiabilité.

Le processus de bêta test et les possibilités de contribution sont décrits à l’adresse suivante : https://www.postgresql.org/developer/beta/.

Changements depuis la Bêta 1

Plusieurs bogues ont été corrigés depuis la publication de la Bêta 1 :

• le paramètre de configuration effective_io_concurrency est maintenant plus simple à utiliser. Pour convertir l’ancienne valeur vers la nouvelle, vous pouvez utiliser cette requête : SELECT round(sum(OLD / n::float)) from generate_series(1, OLD) s(n); ;
• le paramètre de configuration enable_hashagg_disk est renommé en hashagg_avoid_disk_plan et sa valeur par défaut est off;
• le paramètre de configuration enable_groupingsets_hash_disk est retiré;
• la sortie de EXPLAIN ANALYZE pour les plans d’exécution des requêtes parallèles ayant des agrégats de hachage sur disque montre maintenant la quantité maximum de mémoire et le volume utilisé sur le disque par tâche parallélisée;
• plusieurs correctifs et améliorations concernant les agrégats de hachage sur disque;
• la correction d’un crash dans le processus « WAL sender » au démarrage de la réplication physique.

Fonctionnalités

De nombreuses nouvelles fonctionnalités dans PostgreSQL 13 améliorent les performances tout en facilitant le développement des applications.

L’index B-tree, standard de PostgreSQL, reçoit plusieurs améliorations pour la gestion des données dupliquées. Ces améliorations aident à diminuer la taille des index et la vitesse de parcours, en particulier pour les index contenants des valeurs répétées.

PostgreSQL 13 ajoute le tri incrémental, accélérant le tri de données préalablement triées dans une autre partie de la requête. De plus, les requêtes ayant des clauses OR ou des clauses IN/ANY avec des listes de constantes peuvent utiliser les statistiques étendues (créé par CREATE STATISTICS). Cela peut améliorer les plans d’exécution et les performances. PostgreSQL 13 peut maintenant utiliser un fichier sur disque pour les agrégations de hachage (utilisées en tant que partie de requêtes agrégées) avec de grands ensembles d’agrégations.

D’autres améliorations sont ajoutées aux fonctionnalités de partitionnement de PostgreSQL dans cette publication, incluant un plus grand nombre de cas où une jointure directe entre des partitions peut être utilisée. Cela améliore le temps d’exécution de la requête. Les tables partitionnées supportent désormais les déclencheurs « niveau ligne » avec la clause BEFORE, et une table partitionnée peut être répliquée via la réplication logique sans avoir à publier chacune des partitions.

PostgreSQL 13 ajoute la fonctionnalité FETCH FIRST WITH TIES, qui retourne les lignes liées à la dernière ligne.

La fonction .datetime() ajoutée aux requêtes jsonpath convertit automatiquement une chaîne contenant une date ou un horodatage vers le type de données approprié.

Il est plus facile de générer des UUIDs aléatoires avec la fonction gen_random_uuid() utilisable sans activer d’extensions.

Administration

Une des fonctionnalités les plus attendues de PostgreSQL 13 est la capacité de la commande VACUUM à traiter des index en parallèle. Cette fonctionnalité est accessible en utilisant l’option PARALLEL de la commande VACUUM (ou --parallel sur vacuumdb), ce qui permet d’indiquer le nombre de tâches parallèles à utiliser pour traiter les index. Notez que cela ne fonctionne pas avec l’option FULL.

La commande reindexdb peut aussi paralléliser les tâches avec la nouvelle option --jobs, qui permet de spécifier le nombre de sessions concurrentes à utiliser pour réindexer une base de données.

PostgreSQL 13 introduit la notion de « trusted extension », qui permet à un SUPERUSER de spécifier les extensions qu’un utilisateur peut installer dans sa base de données en ayant le privilège CREATE.

Cette publication inclut de nouvelles méthodes de supervision dans une instance PostgreSQL : PostgreSQL 13 permet de suivre les statistiques d’utilisation des WAL, la progression d’une sauvegarde physique en flux et la progression d’une commande ANALYZE. pg_basebackup peut produire un manifeste pouvant être utilisé pour vérifier l’intégrité d’une sauvegarde avec un nouvel outil appelé pg_verifybackup. Il est aussi possible de limiter le volume de WAL réservé par les slots de réplication.

Une nouvelle option de pg_dump, --include-foreign-data, inclut les données des serveurs distants dans la sauvegarde.

La commande pg_rewind reçoit aussi quelques améliorations dans PostgreSQL 13. En plus de la capacité à déclencher une restauration après le crash, il est possible de configurer l’instance secondaire avec l’option --write-recovery-conf. pg_rewind peut utiliser le paramètre restore_command de l’instance cible pour récupérer les WAL requis.

Sécurité

PostgreSQL continue d’améliorer la sécurité avec cette dernière publication. L’introduction de plusieurs fonctionnalités aident au déploiement en toute sécurité de PostgreSQL.

libpq, la bibliothèque de connexions utilisées par psql et de nombreux pilotes de connexions inclut plusieurs nouveaux paramètres de sécurisation des connexions. PostgreSQL 13 introduit le paramètre de connexion channel_binding, permettant à un client de préciser qu’il veut reacquérir la fonctionnalité de «&nsp;channel binding » dans SCRAM. De plus, un client utilisant des certificats TLS protégés par mot de passe peut utiliser le paramètre sslpassword pour l’indiquer. PostgreSQL 13 ajoute aussi le support des certificats chiffrés DER.

Le pilote de données distantes postgres_fdw augmente la sécurité des connexions, en incluant la possibilité d’utiliser une authentification basée sur des certificats pour se connecter à d’autres instances PostgreSQL. De plus, des comptes non privilégiés peuvent maintenant se connecter à d’autres bases de données PostgreSQL via postgres_fdw sans mot de passe.

Autres sujets

PostgreSQL continue d’améliorer l’utilisabilité sur Windows. Il est désormais possible d’utiliser les sockets UNIX pour se connecter à PostgreSQL sous Windows.

La documentation de PostgreSQL ajoute un glossaire pour aider les utilisateurs à se familiariser avec les concepts généraux des bases de données et de PostgreSQL. Cela coïncide avec un travail sur l’affichage des tableaux de fonctions et opérateurs, améliorant la lisibilité sur le web et dans la documentation PDF.

L’utilitaire pgbench utilisé pour les tests de performance offre maintenant la possibilité de partitionner la table “accounts”, facilitant ainsi les tests de charge sur des scénarios contenant des partitions.

psql inclut maintenant la commande \warn, similaire à la commande \echo en termes de sortie. La différence réside dans le fait que \warn envoie le message sur la sortie d’erreur (stderr). De plus, afin d’améliorer l’utilisation des commandes SQL, l’aide \help inclut maintenant un lien vers l’aide en ligne.

Fonctionnalités supplémentaires

De nombreuses autres fonctionnalités et améliorations ont été ajoutées à PostgreSQL. En fonction des cas d’usages, leur importance peut paraître plus ou moins grande que celles mentionnées ci-dessus.

Vous pouvez consulter les notes de publication pour la liste complète des nouveautés et changements.

Tests pour le débogage et la compatibilité

La stabilité de chaque publication de PostgreSQL dépend de vous, la communauté. En testant la version à venir avec votre charge et vos outils de tests, vous pourrez nous aider à trouver les bogues et régressions avant la publication de PostgreSQL 13.

Étant donné qu’il s’agit d’une version bêta, des changements mineurs dans le comportement de la base de données, des détails et des APIs sont toujours possibles. Vos retours et tests aideront à déterminer les ajustements finaux des nouvelles fonctionnalités.

La qualité des tests aide à déterminer le moment de la publication finale.

Une liste des problèmes ouverts est publiquement disponible dans le wiki de PostgreSQL. Vous pouvez rapporter des bogues en utilisant le formulaire présent sur le site web de PostgreSQL : https://www.postgresql.org/account/submitbug/

Planning Bêta

Il s’agit de la deuxième publication bêta de la version 13. Le projet PostgreSQL publiera autant de bêtas que nécessaire pour tester. Celles-ci seront suivies par une ou plusieurs publications de versions candidates, jusqu'à la publication de la version finale à la fin de l’année 2020.

Pour plus d’information, veuillez consulter la page Beta Testing.

Liens

• Téléchargement
• Information sur le Bêta Testing
• Note de publication de PostgreSQL 13 Bêta
• Problèmes connus de PostgreSQL 13
• Soumettre un bogue

Lire la suite sur www.loxodata.com...

Nouvelles hebdomadaires de PostgreSQL - 28 juin 2020

PostgreSQL 13 Beta 2 disponible. À vos tests ! https://www.postgresql.org/about/news/2047/

Personne de la semaine : https://postgresql.life/post/simon_riggs/

Lire la suite...