Creazione di estensioni TLE per RDS per PostgreSQL

È possibile installare qualsiasi estensione creata con TLE in qualsiasi istanza database RDS per PostgreSQL in cui è installata l'estensione pg_tle. L'estensione pg_tle si riferisce al database PostgreSQL in cui è installata. Le estensioni create utilizzando TLE si riferiscono allo stesso database.

Usa le varie funzioni pgtle per installare il codice che costituisce la tua estensione TLE. Le seguenti funzioni di Trusted Language Extensions richiedono tutte il ruolo 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

Esempio: creazione di un'estensione Trusted Language Extensions utilizzando SQL

L'esempio seguente mostra come creare un'estensione TLE denominata pg_distance che contiene alcune funzioni SQL per il calcolo delle distanze utilizzando formule diverse. Nell'elenco, puoi trovare la funzione per il calcolo della distanza di Manhattan e la funzione per il calcolo della distanza euclidea. Per ulteriori informazioni sulla differenza tra queste formule, consulta Taxicab geometry (Geometria del taxi) e Euclidean geometry (Geometria euclidea) in Wikipedia.

È possibile utilizzare questo esempio nell'istanza database RDS per PostgreSQL se l'estensione pg_tle è impostata come descritto in dettaglio in Impostazione di Trusted Language Extensions nell'istanza database RDS per PostgreSQL.

Nota

È necessario disporre dei privilegi del ruolo pgtle_admin per seguire questa procedura.

Per creare l'estensione TLE di esempio

I passaggi seguenti utilizzano un database di esempio denominato labdb. Questo database è di proprietà dell'utente postgres principale. Il ruolo postgres dispone anche delle autorizzazioni del ruolo pgtle_admin.

1. Utilizza psql per connetterti l'istanza database RDS per PostgreSQL.

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

2. Crea un'estensione TLE denominata pg_distance copiando il seguente codice e incollandolo nella console della sessione 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_$
   );

   Viene visualizzato l'output riportato di seguito.

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

   Gli artefatti che costituiscono l'estensione pg_distance sono ora installati nel database. Questi artefatti includono il file di controllo e il codice dell'estensione, che devono essere presenti in modo che l'estensione possa essere creata utilizzando il comando CREATE EXTENSION. In altre parole, è comunque necessario creare l'estensione per rendere le funzioni disponibili agli utenti del database.

3. Per creare l'estensione, usa il comando CREATE EXTENSION come per qualsiasi altra estensione. Come per altre estensioni, l'utente del database deve disporre delle autorizzazioni CREATE nel database.

   CREATE EXTENSION pg_distance;

4. Per testare l'estensione TLE pg_distance, puoi usarla per calcolare la distanza di Manhattan tra quattro punti.

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

   Per calcolare la distanza euclidea tra lo stesso set di punti, puoi usare quanto segue.

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

L'estensione pg_distance carica le funzioni nel database e le rende disponibili a tutti gli utenti con le autorizzazioni per il database.

Modifica dell'estensione TLE

Per migliorare le prestazioni delle query per le funzioni contenute nell'estensione TLE, aggiungi i seguenti due attributi PostgreSQL alle specifiche.

• IMMUTABLE: l'attributo IMMUTABLE garantisce che l'ottimizzatore di query possa utilizzare le ottimizzazioni per migliorare i tempi di risposta delle query. Per ulteriori informazioni, consulta Function Volatility Categories (Categorie della volatilità delle funzioni) nella documentazione di PostgreSQL.

• PARALLEL SAFE: l'attributo PARALLEL SAFE è un altro attributo che consente a PostgreSQL di eseguire la funzione in modalità parallela. Per ulteriori informazioni, consultare CREATE FUNCTION nella documentazione di PostgreSQL.

Nell'esempio seguente, puoi vedere come viene utilizzata la funzione pgtle.install_update_path per aggiungere questi attributi a ogni funzione per creare la versione 0.2 dell'estensione TLE pg_distance. Per ulteriori informazioni su questa funzione, consulta pgtle.install_update_path. È necessario avere il ruolo pgtle_admin necessario per eseguire questa operazione.

Per aggiornare un'estensione TLE esistente e specificare la versione predefinita

1. Esegui la connessione all'istanza database RDS per PostgreSQL utilizzando psql o un altro strumento client, ad esempio pgAdmin.

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

2. Modifica l'estensione TLE esistente copiando il seguente codice e incollandolo nella console della sessione 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_$
   );

   Viene visualizzata una risposta simile alla seguente.

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

   È possibile impostare questa versione dell'estensione come versione predefinita, in modo che gli utenti del database non debbano specificare una versione quando creano o aggiornano l'estensione nel database.

3. Per specificare che la versione modificata (versione 0.2) dell'estensione TLE è la versione predefinita, usa la funzione pgtle.set_default_version come mostrato nell'esempio seguente.

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

   Per ulteriori informazioni su questa funzione, consulta pgtle.set_default_version.

4. Una volta inserito il codice, puoi aggiornare l'estensione TLE installata nel modo consueto, usando il comando ALTER EXTENSION ... UPDATE, come mostrato di seguito:

   ALTER EXTENSION pg_distance UPDATE;