Aurora PostgreSQL용 TLE 확장 생성 - Amazon Aurora
예: SQL을 사용하여 TLE 확장 생성TLE 확장 수정

Aurora PostgreSQL용 TLE 확장 생성

pg_tle 확장이 설치된 Aurora PostgreSQL DB 클러스터 에서 TLE를 사용하여 생성한 확장을 설치할 수 있습니다. pg_tle 확장은 설치된 PostgreSQL 데이터베이스로 범위가 지정됩니다. TLE를 사용하여 생성한 확장은 동일한 데이터베이스로 범위가 지정됩니다.

다양한 pgtle 함수를 사용하여 TLE 확장을 구성하는 코드를 설치하세요. 다음과 같은 신뢰할 수 있는 언어 확장 함수는 모두 pgtle_admin 역할이 필요합니다.

예: SQL을 사용하여 신뢰할 수 있는 언어 확장 생성

다음 예제는 다양한 공식을 사용하여 거리를 계산하는 몇 가지 SQL 함수가 포함된 pg_distance라는 TLE 확장을 생성하는 방법을 보여 줍니다. 목록에서 맨해튼 거리를 계산하는 함수와 유클리드 거리를 계산하는 함수를 찾을 수 있습니다. 이러한 공식의 차이에 대한 자세한 내용은 Wikipedia의 Taxicab geometry와 Euclidean geometry를 참조하세요.

Aurora PostgreSQL DB 클러스터에서 신뢰할 수 있는 언어 확장 설정에 설명된 대로 pg_tle 확장을 설정한 경우 자체 Aurora PostgreSQL DB 클러스터 에서 이 예제를 사용할 수 있습니다.

참고

이 절차를 수행하려면 pgtle_admin 역할의 권한이 있어야 합니다.

예제 TLE 확장을 생성하는 방법

다음 단계에서는 labdb라는 예제 데이터베이스를 사용합니다. 이 데이터베이스는 postgres 기본 사용자가 소유합니다. postgres 역할에는 pgtle_admin 역할의 권한도 있습니다.

  1. psql을 사용하여 Aurora 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 확장을 테스트하려면 TLE 확장을 사용하여 네 점 사이의 맨해튼 거리를 계산하면 됩니다.

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

    동일한 점 집합 간의 유클리드 거리를 계산하려면 다음을 사용하면 됩니다.

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

pg_distance 확장은 데이터베이스에 함수를 로드하여 데이터베이스에서 권한이 있는 모든 사용자가 사용할 수 있도록 합니다.

TLE 확장 수정

이 TLE 확장에 패키징된 함수의 쿼리 성능을 향상시키려면 다음 두 PostgreSQL 속성을 해당 사양에 추가합니다.

  • IMMUTABLE - IMMUTABLE 속성은 쿼리 최적화 프로그램이 최적화를 통해 쿼리 응답 시간을 개선할 수 있도록 합니다. 자세한 내용은 PostgreSQL 설명서에서 Function Volatility Categories를 참조하세요.

  • PARALLEL SAFE - PARALLEL SAFE 속성은 PostgreSQL이 병렬 모드에서 함수를 실행할 수 있도록 하는 또 다른 속성입니다. 자세한 내용은 PostgreSQL 설명서에서 CREATE FUNCTION을 참조하십시오.

다음 예제에서는 pgtle.install_update_path 함수를 사용하여 각 함수에 이러한 속성을 추가하여 pg_distance TLE 확장의 0.2 버전을 생성하는 방법을 확인할 수 있습니다. 이 함수에 대한 자세한 내용은 pgtle.install_update_path 단원을 참조하세요. 이 작업을 수행하려면 pgtle_admin 역할이 있어야 합니다.

기존 TLE 확장을 업데이트하고 기본 버전을 지정하는 방법

  1. psql 또는 pgAdmin 같은 다른 클라이언트 도구를 사용하여 Aurora 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;