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のインストール方法について説明します。
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を用いる場合は、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オプションを定義したオブジェクトに対して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=#
oracle_fdwでは、Oracleに対して検索を実行するのに必要な情報をいくつかのオブジェクトに分割して設定します。無効なFDWオプションを指定した場合はFDWオプション設定時にエラーが発生しますが、必須項目が省略されていたり設定した値が誤っていた場合は、検索実行時にエラーが発生するので注意してください。
有効なFDWオプションは以下のとおりです。
| 指定対象 オブジェクト | FDWオプション名 | 説明 | デフォルト値 |
|---|---|---|---|
| 外部サーバ | dbname | Oracleへの接続に使用する接続識別子です。tnsnames.oraファイルで設定したネット・サービス名や簡易接続ネーミング・メソッドなどが指定できます。 | 環境変数のORACLE_SIDに指定した接続識別子 |
| max_value_len | 検索処理を高速化するための、列データを保持するバッファのサイズです。整数で指定してください。指定できる最大サイズは(1G-2)バイトです。 OracleのテーブルにLONG型、およびLONG RAW型を含む場合は必ず指定してください。指定しない場合は、検索実行時にエラーとなります。 Oracleのテーブルに格納されているデータの中で、最大の列のサイズを指定してください。このとき、ラージオブジェクトの列は除外してください。 0以下の整数を指定した場合は、バッファを自動拡張しながら検索を行います。 max_value_lenの詳細については、検索結果のバッファリングを参照してください。 | なし（自動拡張） | |
| fetchsize | 一度にフェッチする件数を整数で設定します。0以下の整数を指定するとデフォルトの100が使用されます。 | 100 | |
| ユーザマッピング | user | リモートユーザのユーザ名です。 | なし（必須） |
| password | リモートユーザのパスワードです。 | なし（必須） | |
| 外部テーブル | nspname | Oracle上のテーブルのスキーマ名です。大文字と小文字を区別します。Oracleとローカルでスキーマが異なる場合に使用します。 | 外部テーブルのスキーマ名 |
| relname | Oracle上のテーブルのテーブル名です。大文字と小文字を区別します。リモートとローカルでテーブル名が異なる場合に使用します。 | 外部テーブルのテーブル名 | |
| max_value_len | 外部サーバの同名FDWオプションと同じ意味で、両方に設定した場合は外部テーブルの設定値が優先されます。指定しない場合は、外部サーバの設定値が使用されます。 | なし（外部サーバの設定） | |
| fetchsize | 外部サーバの同名FDWオプションと同じ意味で、両方に設定した場合は外部テーブルの設定値が優先されます。指定しない場合は、外部サーバの設定値が使用されます。 | なし（外部サーバの設定） |
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_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の相異を吸収するために、リモートデータベースに接続する際に、内部で接続オプションを設定します。 設定する接続オプションは以下のとおりです。