oracle_fdw 1.0.0


  1. oracle_fdwとは?
  2. 機能概要
  3. インストール
    1. 動作環境
    2. ビルド
    3. 環境変数の設定
    4. エクステンションの作成
  4. 使い方
    1. 外部サーバの作成
    2. ユーザマッピングの作成
    3. 外部テーブルの作成
    4. 検索の実行
    5. FDWオプションの操作
  5. FDWオプション
  6. WHERE句のpush down
  7. アンインストール
  8. 使用上の注意と制約
  9. 詳細情報
    1. バリデータ関数とハンドラ関数
    2. max_value_len
    3. 接続管理
    4. 内部で設定する接続オプション
  10. 関連項目

oracle_fdwとは?

oracle_fdwは、外部のOracleサーバに対して検索を実行するための外部データラッパです。

機能概要

oracle_fdwを用いると、外部のOracleサーバにあるテーブルに対応する外部テーブルを作成できます。また、その外部テーブルを用いた通常のSELECT文を実行することで、通常のテーブルと同様に外部データを扱うことができます。

oracle_fdwでは、Oracleに発行してもSELECT結果に影響しないWHERE句の条件を、Oracleに発行するSELECT文のWHERE句に含めます。これをWHERE句push downと呼びます。

PostgreSQL 9.1では外部テーブルは読み取り専用のため、oracle_fdwで定義した外部テーブルに対する更新(INSERT/UPDATE/DELETE)はエラーとなり実行できません。

インストール

oracle_fdwのインストール方法について説明します。

動作環境

PostgreSQL
バージョン 9.1
動作検証済みOracle Database
10g Release 2
動作検証済みOracle Instant Client
10.2.0.4
動作検証済みOCIライブラリ
バージョン 10.2.0.4
動作検証済みOS
RHEL 5.6

ビルド

oracle_fdwをソースコードからビルドするには、PostgreSQL 9.1 のソースツリーか、インストール済みのpgxsが必要です。また、OracleクライアントやOracle Instalt ClientによってOCIライブラリをインストールしたうえで、ビルドするOSユーザに以下の環境変数を設定する必要があります。Oracle製品のインストール手順についてはOracleが提供するドキュメントを参照してください。

ソースツリーを用いたビルド

PostgreSQLのソースツリーを使用する場合は、PostgreSQL本体のconfigureおよびビルドを済ませてから、contrib配下にoracle_fdwのソースを展開し、makeを実施します。

$ cd postgresql-9.1.x
$ ./configure
$ make
$ cd contrib
$ tar zxvf oracle_fdw-1.0.0.tar.gz
$ cd oracle_fdw-1.0.0
$ make
$ make install 

pgxsを用いたビルド

pgxsを用いる場合は、USE_PGXS変数を設定してからビルドおよびインストールを実施します。

$ tar xzvf oracle_fdw-1.0.0.tar.gz
$ cd oracle_fdw
$ make USE_PGXS=1
$ su
# make USE_PGXS=1 install

環境変数の設定

oracle_fdwを実行するには、OracleクライアントやOracle Instalt ClientによってOCIライブラリをインストールしたうえで、postgresを起動するOSユーザに以下の環境変数を設定する必要があります。Oracle製品のインストール手順についてはOracleが提供するドキュメントを参照してください。

エクステンションの作成

oracle_fdwはPostgreSQL 9.1で導入されたエクステンション形式に対応しているため、psqlなどでCREATE EXTENSIONコマンドを実行することで、関数などの構成要素とともに自動的に外部データラッパが作成されます。従来のcontribモジュールのように手動でSQLスクリプトを実行する必要はありません。なお、この作業にはスーパーユーザ権限が必要です。

$ psql
postgres=# CREATE EXTENSION oracle_fdw;
CREATE EXTENSION
postgres=# \dew
                   List of foreign-data wrappers
    Name    |  Owner   |      Handler       |      Validator
------------+----------+--------------------+----------------------
 oracle_fdw | postgres | oracle_fdw_handler | oracle_fdw_validator
(1 row)

postgres=#

使い方

oracle_fdwを用いて外部のOracleサーバにあるテーブルを検索する手順について説明します。なお、接続情報はサンプルですので、実際の環境に合わせて適宜読み替えてください。

外部サーバの作成

スーパーユーザ権限のあるユーザでCREATE SERVERコマンドを実行して、検索対象のデータベースに対応する外部サーバを作成します。外部サーバのFDWオプションとして、ネット・サービス名を指定します。

postgres=# CREATE SERVER remote_db FOREIGN DATA WRAPPER oracle_fdw
postgres-# OPTIONS (dbname 'orcl');
CREATE SERVER
postgres=#

外部サーバ作成後に、スーパーユーザ権限のあるユーザで以下のINSERTコマンドを実行して、ローカルの関数とリモートの関数をひもづけるルーチンマッピングを作成します。詳細はルーチンマッピングを参照してください。

postgres=# INSERT INTO oracle_fdw.pg_routine_mapping
postgres-# SELECT t.rmproc, s.oid, t.rmoptions
postgres-#   FROM pg_foreign_server s, (
postgres(#        SELECT *
postgres(#          FROM oracle_fdw.pg_routine_mapping r, pg_foreign_server s
postgres(#         WHERE srvname = 'oracle_fdw_template_server'
postgres(#           AND r.rmserver = s.oid) t
postgres-#  WHERE s.srvname = 'remote_db';

ユーザマッピングの作成

外部サーバの所有者でCREATE USER MAPPINGコマンドを実行して、ローカルユーザとリモートユーザをひもづけるユーザマッピングを作成します。ユーザマッピングのFDWオプションとして、リモートユーザのユーザ名やパスワードを指定します。

postgres=# CREATE USER MAPPING FOR postgres SERVER remote_db
postgres-# OPTIONS (user 'scott', password 'tiger');
CREATE USER MAPPING
postgres=#

任意のローカルユーザが外部テーブルを検索しても良いケースでは、ローカルユーザ名の代わりにpublicを指定することで、ユーザマッピングを持たないローカルユーザ用のマッピングを作成することもできます。

postgres=# CREATE USER MAPPING FOR public SERVER remote_db
postgres-# OPTIONS (user 'app_user', password 'secret');
CREATE USER MAPPING
postgres=#

外部テーブルの作成

外部サーバの所有者でCREATE FOREIGN TABLEコマンドを実行して、検索対象のリモートテーブルに対応する外部テーブルを作成します。基本的な構文はCREATE TABLE文と同じですが、列リストの後に外部サーバを指定する必要があります。また、リモートテーブルのスキーマ名やテーブル名が、外部テーブルを作成するスキーマ名や外部テーブル名と異なる場合は、外部テーブルのFDWオプションで指定できます。スキーマ名やテーブル名が同じ場合は不要です。以下に例を示します。

Oracleに以下のテーブルが定義されているものとします。

SQL> DESC pgbench_accounts
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 AID                                       NOT NULL NUMBER(10)
 BID                                                NUMBER(10)
 ABALANCE                                           NUMBER(10)
 FILLER                                             CHAR(84)

SQL>

CREATE FOREIGN TABLEコマンドを実行して、上記のテーブルに関連付けられた外部テーブルを作成します。列名はリモートテーブルと同じ名前にする必要があります。また、引用符付きでない識別子は小文字の識別子として扱われますので注意してください。外部テーブルのFDWオプションとして、スキーマ名とOracleのテーブル名を指定します。

postgres=# CREATE FOREIGN TABLE remote_accounts (
postgres(# "AID" integer,
postgres(# "BID" integer,
postgres(# "ABALANCE" integer,
postgres(# "FILLER" char(84))
postgres(# SERVER remote_db
postgres(# OPTIONS (nspname 'scott', relname 'PGBENCH_ACCOUNTS');
CREATE FOREIGN TABLE
postgres=#

検索の実行

外部テーブルが作成できたら、外部テーブルを使った通常のSELECT文を実行するだけで外部テーブルからデータを取得できます。ユーザマッピングで指定したリモートユーザには、OracleのテーブルのSELECT権限が必要です。実行するSELECT文に制限はありませんが、外部テーブルに対する更新はできません。外部テーブルを作成したユーザ以外にも検索を許可したい場合は、通常のテーブルと同様にGRANT文でSELECT権限を付与します。

postgres=# SELECT "AID", "BID", "ABALANCE" FROM remote_accounts WHERE "AID" = 1;
 AID | BID | ABALANCE
-----+-----+----------
   1 |   1 |        0
(1 row)

postgres=#

リモートに発行するクエリを確認するには、EXPLAINコマンドを使用します。リモートに発行するクエリは、「Remote SQL:」に続けて出力されます。以下の例では「"AID" = 1」の条件がpush downされていることが確認できます。

postgres=# EXPLAIN SELECT "AID", "BID", "ABALANCE" FROM remote_accounts WHERE "AID" = 1;
                                        QUERY PLAN

-------------------------------------------------------------------------------------------
 Foreign Scan on remote_accounts  (cost=10.00..10.00 rows=1000 width=352)
   Remote SQL: SELECT "AID", "BID", "ABALANCE" FROM "scott"."PGBENCH_ACCOUNTS" WHERE ("AID" = 1)
(2 行)

postgres=#

FDWオプションの操作

リモートユーザのパスワードなどの接続情報が変更された場合は、該当するFDWオプションを定義したオブジェクトに対してALTER文を実行して、FDWオプションの値を設定します。以下の例ではリモートユーザのパスワードを変更しています。CREATE文と異なり、既存FDWオプションの変更にはSET指定が必要ですので注意してください。FDWオプションは、SET指定による変更の他に、ADD指定による追加やDROP指定による削除もできます。

postgres=# ALTER USER MAPPING FOR postgres SERVER remote_db
postgres-# OPTIONS (SET password 'more secret');
ALTER USER MAPPING
postgres=#

FDWオプション

oracle_fdwでは、Oracleに対して検索を実行するのに必要な情報をいくつかのオブジェクトに分割して設定します。無効なFDWオプションを指定した場合はFDWオプション設定時にエラーが発生しますが、必須項目が省略されていたり設定した値が誤っていた場合は、検索実行時にエラーが発生するので注意してください。

有効なFDWオプションは以下のとおりです。

指定対象
オブジェクト
FDWオプション名説明デフォルト値
外部サーバdbnameOracleへの接続に使用する接続識別子です。tnsnames.oraファイルで設定したネット・サービス名や簡易接続ネーミング・メソッドなどが指定できます。環境変数のORACLE_SIDに指定した接続識別子
max_value_len 検索処理を高速化するための、列データを保持するバッファのサイズです。整数で指定してください。指定できる最大サイズは(1G-2)バイトです。 OracleのテーブルにLONG型、およびLONG RAW型を含む場合は必ず指定してください。指定しない場合は、検索実行時にエラーとなります。 Oracleのテーブルに格納されているデータの中で、最大の列のサイズを指定してください。このとき、ラージオブジェクトの列は除外してください。 0以下の整数を指定した場合は、バッファを自動拡張しながら検索を行います。 max_value_lenの詳細については、検索結果のバッファリングを参照してください。

なし(自動拡張)
fetchsize一度にフェッチする件数を整数で設定します。0以下の整数を指定するとデフォルトの100が使用されます。100
ユーザマッピングuserリモートユーザのユーザ名です。なし(必須)
passwordリモートユーザのパスワードです。なし(必須)
外部テーブル nspnameOracle上のテーブルのスキーマ名です。大文字と小文字を区別します。Oracleとローカルでスキーマが異なる場合に使用します。外部テーブルのスキーマ名
relnameOracle上のテーブルのテーブル名です。大文字と小文字を区別します。リモートとローカルでテーブル名が異なる場合に使用します。外部テーブルのテーブル名
max_value_len外部サーバの同名FDWオプションと同じ意味で、両方に設定した場合は外部テーブルの設定値が優先されます。指定しない場合は、外部サーバの設定値が使用されます。なし(外部サーバの設定)
fetchsize外部サーバの同名FDWオプションと同じ意味で、両方に設定した場合は外部テーブルの設定値が優先されます。指定しない場合は、外部サーバの設定値が使用されます。なし(外部サーバの設定)

WHERE句のpush down

oracle_fdwでは、外部テーブルに指定されたWHERE句の条件のうち、Oracleに発行してもSELECT結果が変わらない条件をOracleに発行するSELECT文のWHERE句に含めます。 WHERE句に指定した条件を外部サーバで評価することで得られるメリットは、CPUリソースの分散使用、転送データ量の削減、SELECT実行時間の高速化などがあげられます。 これをWHERE句push downと呼びます。

WHERE句push downの対象は、SELECT結果が変わらない条件のうち、よく使われる演算や関数で構成されたものに限られます。 WHERE句push downの対象となる具体的な演算はWHERE句のpush downを、関数はWHERE句の関数のpush downを参照してください。 また、関数の場合は、Oracle側の関数への対応付けを定義するためのルーチンマッピングという仕組みを使うことで、WHERE句push downの対象を増やすことができます。詳細はルーチンマッピングを参照してください。

ただし、ある限られたWHERE句の条件の内容によっては、PostgreSQLとOracleで異なる結果を返すため、注意してください。 例えば、PostgreSQLでは空文字列を有効な値として扱うのに対して、OracleではNULLとして扱われてしまうため、col = '' の演算結果はOracleではUNKNOWNとなります。 この相異による問題はCTEを使用することで回避できます。 PostgreSQLとOracleの仕様の相異については、PostgreSQLとOracleの仕様の相異およびPostgreSQLとOracleの関数の仕様の相異を参照してください。

アンインストール

oracle_fdwをアンインストールするには、DROP EXTENSION文を実行します。oracle_fdwに関連するオブジェクトが存在する場合は、先にそれらを削除しておくか、CASCADEオプションが必要です。

以下の例では一つの外部サーバとユーザマッピング、四つの外部テーブルをまとめて削除しています。

postgres=# drop EXTENSION oracle_fdw CASCADE;
NOTICE:  drop cascades to 6 other objects
DETAIL:  drop cascades to server remote_db
drop cascades to user mapping for public
drop cascades to foreign table remote_accounts
drop cascades to foreign table remote_branches
drop cascades to foreign table remote_tellers
drop cascades to foreign table remote_history
DROP EXTENSION
postgres=#

使用上の注意と制約

oracle_fdwを使用する際には、以下の使用上の注意と制約があります。

データ型マッピング
oracle_fdw用の外部テーブルを作成する際は、Oracle側のデータ型に応じて、PostgreSQL側で定義可能なデータ型に制限があります。 oracle_fdwがサポートするデータ型マッピングの詳細については、データ型マッピングを参照してください。
PostgreSQLとOracleの仕様の相異
oracle_fdwでは、Oracleに発行してもSELECT結果に影響しないWHERE句の条件を、Oracleに発行するSELECT文のWHERE句に含めます。 しかし、PostgreSQLとOracleの仕様の相異から、外部テーブルに対するSELECTの結果が意図しない結果になる場合があります。 PostgreSQLとOracleの仕様の相異については、PostgreSQLとOracleの仕様の相異およびPostgreSQLとOracleの関数の仕様の相異を参照してください。
外部テーブルは読み取り専用
PostgreSQLの仕様で外部テーブルは読み取り専用です。このため、更新系のDMLはエラーとなります。また、管理用のSQLコマンド(VACUUMやANALYZE)は外部テーブルを処理対象に含めません。
接続とトランザクションの管理
oracle_fdwでは、一つのクエリに複数の外部テーブルが含まれていた場合、それらが同じ外部サーバで定義されていればリモートデータベースへの接続を共有します。ただし、このときのトランザクション分離レベルはOracleの設定値に従うため、読み取り結果に一貫性がない場合があります。
メモリ消費の問題
1件のサイズが大きいテーブルを検索した場合や、1つのSELECT文に複数の外部テーブルを指定した場合などは、ローカルセッションのメモリ使用量が非常に多くなります。 特に、FDWオプションでmax_value_lenを指定した場合は、極端にサイズが大きい列が1つでもあると、列数に比例してメモリ使用量が増加するため、メモリ確保エラーでSQLが異常終了する場合があります。 max_value_lenを指定した場合の、1テーブルあたりのメモリ使用量の算出方法については、検索結果のバッファリングを参照してください。 メモリ確保エラーが発生する場合は、SELECT句に指定する列やFROM句に指定する外部テーブルを、複数のSEELCT文に分割して発行してください。 また、SELECT文をキャンセルすると、メモリの解放が完全に行われないため、メモリリークの原因になるので、注意してください。

詳細情報

より高度な利用方法や、内部構成について説明します。

バリデータ関数とハンドラ関数

oracle_fdwでは、バリデータ関数として oracle_fdw_validatorを、ハンドラ関数としてoracle_fdw_handler関数をそれぞれ定義します。これらはCREATE EXTENSION文で自動的に作成されます。

検索結果のバッファリング

oracle_fdwでは、検索結果をバッファリングするためのメモリ領域を、実際にテーブルへ格納されているデータのサイズに合わせて自動拡張します。 必要最低限のメモリ使用量で検索が可能ですが、自動拡張のオーバーヘッドのため、検索処理は低速になります。 検索時間を短縮したい場合は、FDWオプションのmax_value_lenに、検索結果を保持するバッファのサイズを指定してください。 max_value_lenに設定する値は、Oracleのテーブルに格納されているデータの中で、最大の列のサイズです。このとき、ラージオブジェクトの列は除外してください。 max_value_lenに指定したサイズを超えるサイズの値を検索した場合は、検索実行時にエラーとなります。 実際に使用されるバッファのサイズは、((max_value_len * 列数) + ラージオブジェクトの最大サイズ)です。

接続管理

oracle_fdwでは、同一のローカルセッション内では、一つの接続で全ての外部テーブルスキャンを実行します。 Oracleへの接続は、セッション終了時に切断されます。また、接続識別子、接続ユーザ、または接続に使用するパスワードを、ALTER文を使用して変更した場合は、一度切断したうえで変更後の接続情報を使用して再接続を行う場合があります。

内部で設定する接続オプション

oracle_fdwでは、PostgreSQLとOracleの相異を吸収するために、リモートデータベースに接続する際に、内部で接続オプションを設定します。 設定する接続オプションは以下のとおりです。

NLS_CHARACTERSET
ローカルのデータベースエンコーディングを設定します。ローカルのデータベースエンコーディングがOracleでサポートされていない場合は、環境変数のNLS_LANGの設定に従います。NLS_LANGの設定がない場合はOracleのデフォルトの設定を使用します。
NLS_DATE_FORMAT
'YYYY-MM-DD BC HH24:MI:SS'を設定します。
NLS_TIMESTAMP_FORMAT
'YYYY-MM-DD BC HH24:MI:SS.FF6'を設定します。
NLS_TIMESTAMP_TZ_FORMAT
'YYYY-MM-DD BC HH24:MI:SS.FF6 TZR'を設定します。
NLS_TIME_FORMAT
'HH24:MI:SS.FF6'を設定します。
NLS_TIME_TZ_FORMAT
'HH24:MI:SS.FF6 TZR'を設定します。
NLS_DATE_LANGUAGE
'AMERICAN'を設定します。
NLS_NUMERIC_CHARACTERS
'.,'を設定します。
TIME_ZONE
ローカルのタイムゾーンを設定します。

関連項目

PostgreSQLドキュメント

外部データ, CREATE FOREIGN DATA WRAPPER, CREATE SERVER, CREATE USER MAPPING, CREATE FOREIGN TABLE