Création d'extensions TLE pour Aurora PostgreSQL

Vous pouvez installer toutes les extensions que vous créez avec TLE dans n'importe quel cluster de bases de données Aurora PostgreSQL disposant de l'extension pg_tle. L'extension pg_tle s'applique à la base de données PostgreSQL dans laquelle elle est installée. Les extensions que vous créez à l'aide de TLE sont appliquées à la même base de données.

Utilisez les différentes fonctions pgtle pour installer le code qui constitue votre extension TLE. Les fonctions Trusted Language Extensions suivantes nécessitent toutes le rôle pgtle_admin.

• pgtle.install_extension

• pgtle.install_update_path

• pgtle.register_feature

• pgtle.register_feature_if_not_exists

• pgtle.set_default_version

• pgtle.uninstall_extension(name)

• pgtle.uninstall_extension(name, version)

• pgtle.uninstall_extension_if_exists

• pgtle.uninstall_update_path

• pgtle.uninstall_update_path_if_exists

• pgtle.unregister_feature

• pgtle.unregister_feature_if_exists

Exemple : création d'une extension de langage approuvé utilisant SQL

L'exemple suivant montre comment créer une extension TLE nommée pg_distance et contenant quelques fonctions SQL permettant de calculer des distances à l'aide de différentes formules. Dans la liste, vous trouverez la fonction de calcul de la distance de Manhattan et la fonction de calcul de la distance euclidienne. Pour plus d'informations sur la différence entre ces formules, consultez Taxicab geometry (Géométrie de Manhattan) et Euclidean geometry (Géométrie euclidienne) sur Wikipedia.

Vous pouvez utiliser cet exemple dans votre propre cluster de bases de données Aurora PostgreSQL si vous disposez de l'extension pg_tle configurée comme indiqué dans Configuration de Trusted Language Extensions dans votre cluster de bases de données Aurora PostgreSQL.

Note

Vous devez disposer des privilèges du rôle pgtle_admin pour suivre cette procédure.

Pour créer l'exemple d'extension TLE

Les étapes suivantes utilisent un exemple de base de données nommé labdb. Cette base de données appartient à l'utilisateur principal postgres. Le rôle postgres possède également les autorisations du rôle pgtle_admin.

1. Utilisez psql pour vous connecter à l'instance d'écriture de votre cluster de bases de données Aurora PostgreSQL.

   psql --host=db-instance-123456789012.aws-region.rds.amazonaws.com
   --port=5432 --username=postgres --password --dbname=labdb

2. Créez une extension TLE nommée pg_distance en copiant le code suivant et en le collant dans votre console de session psql.

   SELECT pgtle.install_extension
   (
    'pg_distance',
    '0.1',
     'Distance functions for two points',
   $_pg_tle_$
       CREATE FUNCTION dist(x1 float8, y1 float8, x2 float8, y2 float8, norm int)
       RETURNS float8
       AS $$
         SELECT (abs(x2 - x1) ^ norm + abs(y2 - y1) ^ norm) ^ (1::float8 / norm);
       $$ LANGUAGE SQL;
   
       CREATE FUNCTION manhattan_dist(x1 float8, y1 float8, x2 float8, y2 float8)
       RETURNS float8
       AS $$
         SELECT dist(x1, y1, x2, y2, 1);
       $$ LANGUAGE SQL;
   
       CREATE FUNCTION euclidean_dist(x1 float8, y1 float8, x2 float8, y2 float8)
       RETURNS float8
       AS $$
         SELECT dist(x1, y1, x2, y2, 2);
       $$ LANGUAGE SQL;
   $_pg_tle_$
   );

   Vous devez voir la sortie suivante.

   install_extension
   ---------------
    t
   (1 row)

   Les artefacts qui constituent l'extension pg_distance sont désormais installés dans votre base de données. Ces artefacts incluent le fichier de contrôle et le code de l'extension, qui sont des éléments qui doivent être présents pour que l'extension puisse être créée à l'aide de la commande CREATE EXTENSION. En d'autres termes, il vous reste à créer l'extension pour mettre ses fonctions à la disposition des utilisateurs de la base de données.

3. Pour créer cette extension, utilisez la commande CREATE EXTENSION comme vous le feriez pour toute autre extension. Comme pour les autres extensions, l'utilisateur de la base de données doit disposer des autorisations CREATE dans la base de données.

   CREATE EXTENSION pg_distance;

4. Pour tester l'extension TLE pg_distance, vous pouvez l'utiliser pour calculer la distance de Manhattan entre quatre points.

   labdb=> SELECT manhattan_dist(1, 1, 5, 5);
   8

   Pour calculer la distance euclidienne entre le même ensemble de points, vous pouvez utiliser ce qui suit.

   labdb=> SELECT euclidean_dist(1, 1, 5, 5);
   5.656854249492381

L'extension pg_distance charge les fonctions dans la base de données et les met à la disposition de tous les utilisateurs dotés d'autorisations sur la base de données.

Modification de votre extension TLE

Pour améliorer les performances des requêtes pour les fonctions incluses dans cette extension TLE, ajoutez les deux attributs PostgreSQL suivants à leurs spécifications.

• IMMUTABLE : l'attribut IMMUTABLE garantit que l'optimiseur de requêtes peut utiliser des optimisations pour améliorer les temps de réponse aux requêtes. Pour plus d'informations, consultez Function Volatility Categories (Catégories de volatilité des fonctions) dans la documentation PostgreSQL.

• PARALLEL SAFE : l'attribut PARALLEL SAFE est un autre attribut qui permet à PostgreSQL d'exécuter la fonction en mode parallèle. Pour plus d'informations, consultez CREATE FUNCTION dans la documentation PostgreSQL.

Dans l'exemple suivant, vous pouvez voir comment la fonction pgtle.install_update_path est utilisée pour ajouter ces attributs à chaque fonction afin de créer une version 0.2 de l'extension TLE pg_distance. Pour de plus amples informations sur cette fonction, veuillez consulter pgtle.install_update_path. Vous devez avoir le rôle pgtle_admin pour effectuer cette tâche.

Pour mettre à jour une extension TLE existante et spécifier la version par défaut

1. Connectez-vous à l'instance d'écriture de votre cluster de bases de données Aurora PostgreSQL à l'aide de psql ou d'un autre outil client, tel que pgAdmin.

   psql --host=db-instance-123456789012.aws-region.rds.amazonaws.com
   --port=5432 --username=postgres --password --dbname=labdb

2. Modifiez l'extension TLE existante en copiant le code suivant et en le collant dans votre console de session psql.

   SELECT pgtle.install_update_path
   (
    'pg_distance',
    '0.1',
    '0.2',
   $_pg_tle_$
       CREATE OR REPLACE FUNCTION dist(x1 float8, y1 float8, x2 float8, y2 float8, norm int)
       RETURNS float8
       AS $$
         SELECT (abs(x2 - x1) ^ norm + abs(y2 - y1) ^ norm) ^ (1::float8 / norm);
       $$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
   
       CREATE OR REPLACE FUNCTION manhattan_dist(x1 float8, y1 float8, x2 float8, y2 float8)
       RETURNS float8
       AS $$
         SELECT dist(x1, y1, x2, y2, 1);
       $$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
   
       CREATE OR REPLACE FUNCTION euclidean_dist(x1 float8, y1 float8, x2 float8, y2 float8)
       RETURNS float8
       AS $$
         SELECT dist(x1, y1, x2, y2, 2);
       $$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
   $_pg_tle_$
   );

   Vous voyez une réponse similaire à la suivante.

   install_update_path
   ---------------------
    t
   (1 row)

   Vous pouvez faire de cette version de l'extension la version par défaut, afin que les utilisateurs de base de données n'aient pas à spécifier de version lorsqu'ils créent ou mettent à jour l'extension dans leur base de données.

3. Pour spécifier que la version modifiée (version 0.2) de votre extension TLE est la version par défaut, utilisez la fonction pgtle.set_default_version comme indiqué dans l'exemple suivant.

   SELECT pgtle.set_default_version('pg_distance', '0.2');

   Pour de plus amples informations sur cette fonction, veuillez consulter pgtle.set_default_version.

4. Une fois le code en place, vous pouvez mettre à jour l'extension TLE installée de la manière habituelle, en utilisant la commande ALTER EXTENSION ... UPDATE, comme indiqué ici :

   ALTER EXTENSION pg_distance UPDATE;