RDS for PostgreSQL の TLE 拡張機能の作成

TLE を使用して作成した拡張機能は、pg_tle 拡張機能がインストールされている任意の RDS for PostgreSQL DB インスタンスにインストールできます。pg_tle 拡張機能のスコープは、インストールされている PostgreSQL データベースに限定されます。TLE を使用して作成した拡張機能は、同じデータベースを対象としています。

さまざまな pgtle 関数を使用して、TLE 拡張機能を構成するコードをインストールします。以下の Trusted Language Extensions 関数には 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

例: SQL を使用した信頼できる言語拡張関数の作成

次の例は、さまざまな式を使用して距離を計算するためのいくつかの SQL 関数を含む pg_distance という名前の TLE 拡張機能を作成する方法を示しています。リストには、マンハッタン距離を計算する関数とユークリッド距離を計算する関数があります。これらの式の違いの詳細については、Wikipedia の「Taxicab geometry」と「Euclidean geometry」を参照してください。

RDS for PostgreSQL DB インスタンスに Trusted Language Extensions を設定する で説明されているように pg_tle 拡張機能をセットアップしていれば、この例を独自の RDS for PostgreSQL DB インスタンスで使用できます。

注記

この手順を実行するには、pgtle_admin ロールの権限が必要です。

サンプルの TLE 拡張機能を作成するには

以下の手順では、labdb という名前のサンプルデータベースを使用します。このデータベースは postgres プライマリユーザーが所有しています。postgres ロールには、pgtle_admin ロールのアクセス許可もあります。

1. psql を使用して、に接続します。RDS for PostgreSQL DB インスタンス。

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

2. 次のコードをコピーして psql セッションコンソールに貼り付けて、pg_distance という名前の TLE 拡張機能を作成します。

   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_$
   );

   次のような出力が表示されます。

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

   これで、pg_distance 拡張機能を構成するアーティファクトがデータベースにインストールされました。これらのアーティファクトには、コントロールファイルと拡張機能のコードが含まれます。これらは、CREATE EXTENSION コマンドを使用して拡張機能を作成するために必要となる項目です。つまり、データベースユーザーがその関数を利用できるようにするには、やはり拡張機能を作成する必要があります。

3. 拡張機能を作成するには、他の拡張機能と同じように CREATE EXTENSION コマンドを使用します。他の拡張機能と同様に、データベースユーザーにはデータベース内の CREATE アクセス許可が必要です。

   CREATE EXTENSION pg_distance;

4. pg_distance TLE 拡張機能をテストするには、これを使用して 4 点間のマンハッタン距離を計算できます。

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

   同じ点群間のユークリッド距離を計算するには、以下を使用できます。

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

pg_distance 拡張機能は関数をデータベースに読み込み、データベースに対するアクセス許可を持つすべてのユーザーがその関数を利用できるようにします。

TLE 拡張機能の変更

この TLE 拡張機能にパッケージされている関数のクエリパフォーマンスを向上させるには、次の 2 つの PostgreSQL 属性を仕様に追加してください。

• IMMUTABLE – IMMUTABLE 属性により、クエリオプティマイザが最適化を使用してクエリの応答時間を改善できるようになります。詳細については、PostgreSQL ドキュメントの「関数のボラティリティカテゴリ」を参照してください。

• PARALLEL SAFE – PARALLEL SAFE属性は、PostgreSQL が関数をパラレルモードで実行できるようにするもう 1 つの属性です。詳細については、PostgreSQL のドキュメントの「機能の作成」を参照してください。

次の例では、pgtle.install_update_path 関数を使用してこれらの属性を各関数に追加し、pg_distance TLE 拡張機能のバージョン 0.2 を作成する方法を確認できます。この関数の詳細については、「pgtle.install_update_path」を参照してください。このタスクを実行するには、pgtle_admin ロールが必要です。

既存の TLE 拡張機能を更新してデフォルトバージョンを指定するには

1. psql または pgAdmin などの別のクライアントツールを使用して、 RDS for PostgreSQL DB インスタンスのライターインスタンスに接続します。

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

2. 次のコードをコピーして psql セッションコンソールに貼り付けることで、既存の TLE 拡張機能を変更します。

   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_$
   );

   次のようなレスポンスが表示されます。

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

   このバージョンの拡張機能をデフォルトバージョンにすると、データベースユーザーがデータベースで拡張機能を作成または更新するときにバージョンを指定する必要がなくなります。

3. TLE 拡張機能の修正バージョン (バージョン 0.2) がデフォルトバージョンになるように指定するには、次の例に示す pgtle.set_default_version 関数を使用します。

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

   この関数の詳細については、「pgtle.set_default_version」を参照してください。

4. コードを配置したら、次に示すように、ALTER EXTENSION ... UPDATE コマンドを使用して、インストールされている TLE 拡張機能を通常の方法で更新できます。

   ALTER EXTENSION pg_distance UPDATE;