PostgreSQL 익스텐션 함수

PostgreSQL 익스텐션 함수 (PostgreSQL Extension Functions)

PostgreSQL 익스텐션이 제공하는 함수들을 정리한 목록이에요. 원격 Postgres 인스턴스에 연결하거나 쿼리하고, 바이너리 덤프를 읽는 등의 작업을 SQL에서 바로 할 수 있어요.

출처: 문서

본문

pg_clear_cache

pg_clear_cache() -> TABLE

연결된 모든 PostgreSQL 카탈로그에 대해 캐시된 스키마 항목(컬럼 목록이 있는 테이블 이름 등)을 지워요. 연결된 스키마는 다음 접근 시 다시 읽혀요.

파라미터 (Parameters)

없음.

반환 (Returns)

다음 컬럼의 테이블:

  • Success (BOOLEAN): 캐시 지우기가 성공했는지 여부

현재 테이블 결과는 항상 0행으로 반환되므로 플래그 값은 확인할 수 없어요.

예시 (Example)

CALL pg_clear_cache()

postgres_attach

경고 (Warning) 이 함수는 더 이상 사용되지 않으며(deprecated) 향후 버전에서 제거될 예정이에요. 대신 ATTACH 문을 사용하세요.

postgres_attach(connection_string VARCHAR [, ⟨optional named parameters⟩]) -> TABLE

postgres_configure_pool

FROM postgres_configure_pool([⟨optional named parameters⟩]) -> TABLE

PostgreSQL 데이터베이스가 연결되면 이 데이터베이스에 대한 연결 풀(connection pool)이 만들어져요. 이 함수는 지정된 연결된 데이터베이스의 연결 풀 구성 옵션을 변경할 수 있게 해줘요. 또한 연결 풀의 현재 유효 구성 옵션과 수집된 런타임 통계를 나열할 수도 있어요.

파라미터 (Parameters)

  • catalog_name (VARCHAR): 구성 변경이 적용되고 세부 정보가 반환되는 연결된 Postgres 데이터베이스의 이름(별칭). NULL(기본)이면 구성은 변경하지 않고 모든 연결된 카탈로그의 풀 현재 상태를 반환해요. 다른 옵션이 지정될 때는 지정되고 non-NULL이어야 해요.
  • acquire_mode (VARCHAR, 기본: 'force'): 풀에서 연결을 얻는 방법: 'force'(항상 연결, 풀 제한 무시), 'wait'(사용 가능할 때까지 차단), 'try'(사용 불가하면 즉시 실패)
  • max_connections (UBIGINT): 각 연결된 Postgres 데이터베이스에 대해 연결 풀에 캐시할 수 있는 최대 연결 수. 병렬 스캔을 사용할 때는 이 수를 일시적으로 초과할 수 있어요.
  • wait_timeout_millis (UBIGINT): 사용 가능한 연결이 모두 사용 중인 풀에서 연결을 얻을 때 기다리는 최대 밀리초 수.
  • enable_thread_local_cache (BOOLEAN): 스레드 로컬 캐시에서 연결 캐싱을 활성화할지 여부. 이러한 연결은 스레드에 고정되고 다른 스레드에 제공되지 않지만 풀에서 자리는 차지해요.
  • max_lifetime_millis (UBIGINT): 연결을 열어 둘 수 있는 최대 밀리초 수. 이 값은 연결을 풀에서 가져오고 풀로 돌려보낼 때 확인돼요. 연결 풀 리퍼(reaper) 스레드가 활성화되면('enable_reaper_thread' 인자), 이 값은 백그라운드에서 주기적으로 확인돼요.
  • idle_timeout_millis (UBIGINT): 연결을 풀에서 유휴 상태로 유지할 수 있는 최대 밀리초 수. 이 값은 연결을 풀에서 가져올 때 확인돼요. 연결 풀 리퍼 스레드가 활성화되면('enable_reaper_thread' 옵션), 이 값은 백그라운드에서 주기적으로 확인돼요.
  • enable_reaper_thread (BOOLEAN): 연결 풀 리퍼 스레드를 활성화할지 여부. 이 스레드는 주기적으로 풀을 스캔해 'max_lifetime_millis'와 'idle_timeout_millis'를 확인하고 지정된 값을 초과하는 연결을 닫아요. 이 옵션이 효과를 내려면 'max_lifetime_millis' 또는 'idle_timeout_millis' 중 하나가 0이 아닌 값으로 설정되어야 해요.
  • health_check_query (VARCHAR): 연결이 정상인지 확인하는 데 사용되는 쿼리. 이 옵션을 빈 문자열로 설정하면 헬스 체크가 비활성화돼요.

반환 (Returns)

다음 컬럼의 테이블:

  • catalog_name (VARCHAR): 연결된 Postgres 데이터베이스의 이름(별칭)
  • acquire_mode (VARCHAR): 풀에서 연결을 얻는 방법: 'force'(항상 연결, 풀 제한 무시), 'wait'(사용 가능할 때까지 차단), 'try'(사용 불가하면 즉시 실패)
  • available_connections (UBIGINT): 현재 풀에서 사용 가능한 유휴 연결 수
  • max_connections (UBIGINT): 풀에 캐시할 수 있는 최대 연결 수.
  • wait_timeout_millis (UBIGINT): 사용 가능한 연결이 모두 사용 중인 풀에서 연결을 얻을 때 기다리는 최대 밀리초 수; wait 획득 모드에만 적용
  • cache_hits (UBIGINT): 캐시된 연결이 풀에서 성공적으로 반환된 횟수
  • cache_misses (UBIGINT): 풀이 새 연결을 만든 횟수
  • try_failures (UBIGINT): 풀이 try 획득 모드에서 연결을 요청했고 그 시점에 사용 가능한 연결이 없어서 풀이 연결을 제공하지 못한 횟수; Postgres 익스텐션이 병렬 스캔을 수행할 때 워커 스레드는 항상 try 획득 모드를 사용한다는 점을 참고 — SET threads = ⟨number_higher_then_pool_size⟩{:.language-sql .highlight}를 사용하면 일부 워커 스레드는 연결을 얻지 못해 에러를 던지지 않고 작업 없이 반환해요
  • thread_local_cache_enabled (BOOLEAN): 스레드 로컬 캐시에서 연결 캐싱을 활성화했는지 여부; 스레드 로컬 연결은 리퍼 스레드가 아니 지워요
  • thread_local_cache_hits (UBIGINT): 메인 풀로 가지 않고 스레드 로컬 캐시에서 연결을 성공적으로 얻은 횟수
  • thread_local_cache_misses (UBIGINT): 스레드 로컬 캐시에서 사용할 수 없어 대신 메인 풀에서 연결을 가져온 횟수
  • max_lifetime_millis (UBIGINT): 연결을 열어 둘 수 있는 최대 밀리초 수
  • idle_timeout_millis (UBIGINT): 연결을 풀에서 유휴 상태로 유지할 수 있는 최대 밀리초 수
  • reaper_thread_running (BOOLEAN): 풀 리퍼 스레드가 실행 중인지 여부; 이 스레드는 주기적으로 풀을 스캔해 'max_lifetime_millis'와 'idle_timeout_millis'를 확인하고 지정된 값을 초과하는 연결을 닫아요
  • reaper_thread_period_millis (UBIGINT): 리퍼 스레드가 검사를 수행하는 기간
  • health_check_query (VARCHAR): 연결이 정상인지 확인하는 데 사용되는 쿼리

예시 (Examples)

모든 연결 풀(모든 연결된 데이터베이스)의 현재 유효 구성 옵션과 수집된 런타임 통계 나열:

FROM postgres_configure_pool()

지정된 연결된 데이터베이스의 연결 풀에 대한 하나 이상의 구성 옵션 변경:

FROM postgres_configure_pool(catalog_name = 'db1', acquire_mode = 'wait', max_connections = 42)

postgres_execute

postgres_execute(attached_db_name VARCHAR, sql_query VARCHAR[, ⟨optional named parameters⟩]) -> TABLE

이전에 ATTACH ... AS ⟨attached_db_name⟩{:.language-sql .highlight}으로 연결한 지정된 원격 Postgres 인스턴스에서 ⟨sql_query⟩{:.language-sql .highlight}을 실행해요. 이 함수는 빈 결과를 반환해요.

파라미터 (Parameters)

  • attached_db_name (VARCHAR): 연결된 PostgreSQL 데이터베이스의 이름
  • sql_query (VARCHAR): 실행을 위해 PostgreSQL로 전달되는 쿼리; DuckDB는 이 쿼리에 대해 변환이나 분석을 수행하지 않아요

선택적 명명된 파라미터:

  • use_transaction (BOOLEAN, 기본: TRUE): 이전에 트랜잭션이 시작되지 않았다면 PostgreSQL 트랜잭션을 시작할지 여부.

반환 (Returns)

다음 컬럼의 테이블:

  • Success (BOOLEAN): 캐시 지우기가 성공했는지 여부

현재 테이블 결과는 항상 0행으로 반환되므로 플래그 값은 확인할 수 없어요.

예시 (Example)

CALL postgres_execute('db1', 'VACUUM ANALYZE', use_transaction = false)

postgres_hstore_get

postgres_hstore_get(hstore_string VARCHAR, hstore_key VARCHAR) -> VARCHAR

PostgreSQL hstore 컬럼의 외부 표현을 파싱하고 지정된 hstore 키의 값을 반환해요.

파라미터 (Parameters)

  • hstore_string (VARCHAR): key => value 형태의 PostgreSQL hstore 값.
  • hstore_key (VARCHAR): 값을 반환할 키 이름.

반환 (Returns)

지정된 키의 값. 키가 없으면 NULL.

예시 (Example)

SELECT postgres_hstore_get('a=>b, c=>d', 'a')

postgres_hstore_to_json

postgres_hstore_to_json(hstore_string VARCHAR) -> JSON

PostgreSQL hstore 컬럼의 외부 표현을 JSON으로 변환해요.

파라미터 (Parameters)

  • hstore_string (VARCHAR): key => value 형태의 PostgreSQL hstore 값.

반환 (Returns)

입력 hstore 문자열과 같은 키/값 쌍을 가진 JSON 딕셔너리. 모든 값은 문자열로 반환돼요.

예시 (Example)

SELECT postgres_hstore_to_json('z=>1, a=>2, m=>3')

postgres_query

postgres_query(attached_db_name VARCHAR, sql_query VARCHAR[, ⟨optional named parameters⟩]) -> TABLE

이전에 ATTACH .. AS ⟨attached_db_name⟩{:.language-sql .highlight}으로 연결한 지정된 원격 DB에서 쿼리를 실행하고 쿼리 결과를 테이블로 반환해요.

파라미터 (Parameters)

  • attached_db_name (VARCHAR): 연결된 PostgreSQL 데이터베이스의 이름
  • sql_query (VARCHAR): 실행을 위해 PostgreSQL로 전달되는 쿼리; DuckDB는 이 쿼리에 대해 변환이나 분석을 수행하지 않아요

선택적 명명된 파라미터:

  • use_transaction (BOOLEAN, 기본: TRUE): 이전에 트랜잭션이 시작되지 않았다면 PostgreSQL 트랜잭션을 시작할지 여부.
  • params (STRUCT): PostgreSQL 서버로 전달되는 쿼리 파라미터; 텍스트 프로토콜을 사용할 때만 지원

반환 (Returns)

쿼리 결과가 담긴 테이블.

예시 (Example)

FROM postgres_query('db11', 'SELECT $1::INTEGER, $2::TEXT', params=row(42::INTEGER, 'foo'::VARCHAR))

postgres_scan

경고 (Warning) 이 함수는 더 이상 사용되지 않으며 향후 버전에서 제거될 예정이에요. 대신 연결된 PostgreSQL 데이터베이스에 대한 직접 SQL 쿼리를 사용하세요.

postgres_scan(connection_string VARCHAR, schema_name VARCHAR, table_name VARCHAR) -> TABLE

postgres_scan_pushdown

경고 (Warning) 이 함수는 더 이상 사용되지 않으며 향후 버전에서 제거될 예정이에요. 대신 연결된 PostgreSQL 데이터베이스에 대한 직접 SQL 쿼리를 사용하세요.

postgres_scan_pushdown(connection_string VARCHAR, schema_name VARCHAR, table_name VARCHAR) -> TABLE

read_postgres_binary

FROM read_postgres_binary(file_path VARCHAR[, ⟨optional named parameters⟩]) -> TABLE

파일 시스템에서 PostgreSQL 바이너리 덤프 파일을 읽어요.

파라미터 (Parameters)

  • file_path (VARCHAR): PostgreSQL 형식의 바이너리 덤프 파일의 FS 경로.

선택적 명명된 파라미터:

  • columns (STRUCT): column_name -> column_type 구조 형태의 타입 매핑.
  • buffer_size (UBIGINT, 기본: 32KB): 읽기 버퍼 크기(바이트).

반환 (Returns)

바이너리 덤프 파일의 내용을 테이블로.

예시 (Example)

COPY (SELECT 42::INTEGER AS a, 'foo'::VARCHAR AS b) TO 'path/to/test.bin' (FORMAT postgres_binary);

FROM read_postgres_binary('path/to/test.bin', columns = {a: 'INTEGER', b: 'VARCHAR'});

더 알아보기 (Learn more)