postgres_fdwモジュールは、外部のPostgreSQLサーバに格納されたデータをアクセスするために使用する、postgres_fdw外部データラッパを提供します。
実質上、本モジュールの提供する機能は以前のdblinkモジュールが提供する機能と重複していますが、postgres_fdwはリモートのテーブルにアクセスするためにより透過的で標準に準拠した構文を利用できるほか、多くの場合においてより良い性能を得る事ができます。
postgres_fdwを使用したリモートアクセスを準備するには:
CREATE EXTENSIONを使用してpostgres_fdw拡張をインストールしてください。
CREATE SERVERを使用して、接続しようとする各リモートデータベースを定義する外部サーバオブジェクトを作成してください。
userおよびpasswordを除く接続パラメータを、外部サーバオブジェクトのオプションとして指定します。
CREATE USER MAPPINGを使用して、外部サーバへのアクセスを許可するデータベースユーザごとにユーザマッピングを作成します。
ユーザマッピングのuserおよびpasswordオプションを使用してリモートユーザのためのユーザ名とパスワードを指定します。
CREATE FOREIGN TABLEもしくはIMPORT FOREIGN SCHEMAを使用して、アクセスしたいリモートテーブルごとに外部テーブルを作成します。 外部テーブルのカラム定義は被参照側のリモートテーブルに一致していなければなりません。 しかしながら、外部テーブルのオプションとして正しいリモートの名前を外部テーブルのオプションに指定すれば、テーブルおよびカラム名はリモートのものと異なった名前を付ける事ができます。
今のところ、リモートテーブルに格納されているデータにアクセスするには少なくとも外部テーブルに対するSELECT権限が必要です。
また、INSERTやUPDATE、DELETEを使用してリモートテーブルを操作する事もできます。
（もちろん、ユーザマッピングで指定されたリモートユーザは、これらの操作を実行する権限を有している必要があります）
postgres_fdwは今のところ、ON CONFLICT DO UPDATE句のあるINSERT文をサポートしていないことに注意して下さい。
しかし、一意インデックスの推定の指定を省略しているならば、ON CONFLICT DO NOTHING句はサポートされます。
一般的な推奨事項として、可能であれば外部テーブルのカラムを、被参照側のリモートテーブル側のカラムと全く同一のデータ型および照合順序によって定義してください。
postgres_fdwは必要に応じてデータ型の変換を行いますが、リモートサーバがローカルサーバとは少々違ったWHERE句の解釈を行うため、データ型や照合順序が一致していないと、時には予期しない結果を得る事があるかもしれません。
リモートテーブルより少ないカラム数で、あるいは異なった順序であっても外部テーブルを定義できる事に留意してください。 リモートテーブル側のカラムとの対応付けは、その位置ではなく、名前によって行われます。
postgres_fdw外部データラッパを使用する外部サーバは、以下に記すものを除き、31.1.2. パラメータキーワードに記載されているlibpqが接続文字列としてサポートするものと同一のオプションを使用する事ができます。
user および password
（これらは代わりにユーザマッピングのオプションの中で指定します）
client_encoding
（これはローカルサーバのエンコーディングが自動的にセットされます）
fallback_application_name
（自動的にpostgres_fdwとセットされます）
特権ユーザのみが外部サーバに対してパスワードなしの認証で接続できます。
したがって、非特権ユーザのユーザマッピングにはpasswordを必ず指定するようにして下さい。
これらのオプションによりリモートのPostgreSQLサーバに送出されるSQL文で使用される名前を制御する事ができます。 外部テーブルがリモートテーブルとは異なった名前で定義されている場合、これらのオプションは必須です。
schema_name外部テーブルに対して指定できるこのオプションは、リモートサーバ上のリモートテーブルのスキーマ名を与えます。 省略された場合、外部テーブルのスキーマ名が使用されます。
table_name外部テーブルに対して指定できるこのオプションは、リモートサーバ上のリモートテーブル名を与えます。 省略された場合、外部テーブルのテーブル名が使用されます。
column_name外部テーブルのカラムに対して指定できるこのオプションは、リモートサーバ上のカラム名を与えます。 省略された場合、外部テーブルのカラム名が使用されます。
postgres_fdwはリモートサーバに対するクエリを実行しリモートのデータを受信します。したがって、理想的には外部テーブルをスキャンする推定コストは、それをリモートサーバで実行するコストと通信オーバーヘッドの和となります。
この推定を行うための最も信頼できる方法は、リモートサーバに問い合わせを行い、その結果にオーバーヘッド分を加算する事ですが、小さいクエリではコスト推定を得るための追加的な問い合わせに要するコストに見合わないかもしれません。
そこで、どのようにコスト推定を行うかを制御するため、postgres_fdwは以下のようなオプションを提供します。
use_remote_estimate外部テーブルまたは外部サーバに指定できるこのオプションは、コスト推定を得るためにpostgres_fdwがリモートのEXPLAINコマンドを発行するかどうかを制御します。
外部テーブルに対する設定は、関連付けられた外部サーバに対する設定を上書きしますが、その効果は当該外部テーブルに限定されます。
デフォルト値はfalseです。
fdw_startup_cost外部テーブルまたは外部サーバに指定できるこのオプションは、当該外部サーバに関連付けられた全ての外部テーブルスキャンの推定開始コストに加算される数値です。
これは、接続の確立、リモート側でのクエリのパース・最適化など、追加的なオーバーヘッドを表現します。
デフォルト値は100です。
fdw_tuple_cost外部サーバに指定できるこのオプションは、このサーバでの外部テーブルのスキャンにおいて、各タプル毎に発生する追加的なコストとして使用される数値です。
これは、サーバ間のデータ転送における追加的なオーバーヘッドを表現し、リモートサーバへのネットワーク遅延の高低を反映するためにこの数値を増減することができます。
デフォルト値は0.01です。
use_remote_estimateがtrueの時、postgres_fdwはリモートサーバから行数とコスト推定値を取得し、それをfdw_startup_costとfdw_tuple_costに加算します。
一方、use_remote_estimateがfalseの時、postgres_fdwはローカルの行数とコスト推定値を取得しfdw_startup_costとfdw_tuple_costをコスト推定値に加算します。
このローカルな推定は、リモートテーブルの統計情報のローカルコピーが利用可能でないと、正確である見込みはほとんどありません。
ローカルな統計情報を更新するには外部テーブルに対するANALYZEを実行します。これはリモートテーブルに対するスキャンを実行し、あたかもローカルなテーブルであるかのように統計情報の計算と保存を行います。
ローカルな統計情報を保存する事で、クエリの度にリモートテーブルの実行計画を作成するオーバヘッドを削減する事ができます。
しかしながら、リモートテーブルの更新頻度が高ければローカルの統計情報はすぐに実態を反映しなくなるでしょう。
デフォルトではpostgres_fdwを使用する全ての外部テーブルは更新可能であると想定されます。以下のオプションにより、この挙動を上書きする事ができます。
updatableこのオプションは、postgres_fdwがINSERT、UPDATEあるいはDELETEコマンドを使用して外部テーブルを操作する事を許可するかどうかを規定します。
外部テーブルで指定されたオプションは、外部サーバにおいて指定されたオプションを上書きします。
デフォルト値はtrueです。
もちろん、リモートテーブルが実際には更新可能ではなかった場合、いずれにしてもエラーが発生するでしょう。このオプションを使用することで、リモートサーバへの問い合わせを行う事なくローカルでエラーを発生させることができます。
また、information_schemaビューは、このオプションの値に従ってpostgres_fdw管理下の外部テーブルを更新可能（あるいは不可能）であるとレポートする事に留意してください。
リモートサーバ側のチェックは一切行われません。
postgres_fdwはIMPORT FOREIGN SCHEMAを使って、外部テーブルの定義をインポートすることができます。
このコマンドは、リモートのサーバ上に存在するテーブルあるいはビューとマッチする外部テーブルの定義をローカルサーバ上に作成します。
インポートするリモートのテーブルにユーザ定義のデータ型の列がある場合、ローカルサーバにも同じ名前の互換性のある型がなければなりません。
インポートの動作は以下のオプションでカスタマイズできます（IMPORT FOREIGN SCHEMAコマンドで指定します）。
import_collateこのオプションは、列のCOLLATEオプションが、外部サーバからインポートする外部テーブルの定義に含まれているかどうかを制御します。
デフォルトはtrueです。
リモートサーバとローカルサーバで照合順序の名前の集合が異なる場合は、この設定を無効にする必要があるでしょう。
リモートサーバが異なるOSで動作しているなら、そういうことがありそうです。
import_defaultこのオプションは、列のDEFAULT式が外部サーバからインポートされる外部テーブルの定義に含まれているかどうかを制御します。
デフォルトはfalseです。
このオプションを有効にする場合は、ローカルサーバとリモートサーバで異なる計算をされるデフォルトに注意して下さい。
nextval()はよくある問題の一つです。
インポートされるデフォルト式がローカルには存在しない関数または演算子を使っていた場合、IMPORTは失敗します。
import_not_nullこのオプションは、列のNOT NULL制約が、外部サーバからインポートされる外部テーブルの定義に含まれているかどうかを制御します。
デフォルトはtrueです。
NOT NULL以外の制約は決してリモートのテーブルからインポートされないことに注意して下さい。
PostgreSQLは外部テーブルのCHECK制約をサポートしていますが、それを自動的にインポートする予定はありません。
なぜなら、制約の式はローカルとリモートのサーバで異なる評価をされる危険があるからです。
CHECK制約でそのような一貫しない動作があると、問い合わせの最適化で検知するのが難しい誤りが発生するかもしれません。
そのため、CHECK制約をインポートしたい場合は、それを手作業で実行する必要があり、またその一つ一つの意味を注意深く確認するべきです。
外部テーブルのCHECK制約の取扱いについて、詳しくはCREATE FOREIGN TABLEを参照して下さい。
postgres_fdwは、外部サーバに関連付けられた外部テーブルを参照するクエリを最初に実行する際に、外部サーバへの接続を確立します。
この接続は保持され、同じセッションで以降の問い合わせのために再利用されます。
しかし、外部サーバへのアクセスに対して複数のユーザ識別子（ユーザマッピング）が使用される場合には、接続はユーザマッピング毎に確立される事になります。
外部サーバ上のリモートテーブルを参照する際に、まだトランザクションが開始されていなければpostgres_fdwはリモートサーバ上でトランザクションを開始します。
ローカルのトランザクションがコミット、あるいはアボートした時、リモートのトランザクションも同様にコミット、あるいはアボートします。
セーブポイントも同様に管理され、リモート側に関連付けられたセーブポイントが作成されます。
ローカルトランザクションがSERIALIZABLE隔離レベルを用いている時、リモートトランザクションもSERIALIZABLE隔離レベルを使用します。
それ以外の場合にはREPEATABLE READ隔離レベルを使用します。
これは、あるクエリが複数のテーブルスキャンをリモート側で行う際に、確実に全てのスキャンにおいて一貫したスナップショットで結果を取り出すためです。
その結果、別の要求によってリモートサーバ側で競合する更新が発生したとしても、あるトランザクション内の問い合わせはリモートサーバからの一貫したデータを参照する事となります。
ローカルのトランザクションがSERIALIZABLEあるいはREPEATABLE READ隔離レベルを用いている場合、この動作は期待通りのものでしょう。
一方、ローカルのトランザクションがREAD COMMITTED隔離レベルを使用している場合には、予想外の動作かもしれません。
将来のPostgreSQLリリースではこれらのルールに変更が加えられるかもしれません。
外部サーバからのデータ転送量を削減するため、postgres_fdwはリモート問い合わせを最適化しようと試みます。
これは問い合わせのWHERE句をリモートサーバに送出する事、およびクエリで必要とされていないカラムを取得しない事により行われます。
問い合わせの誤作動のリスクを下げるため、ビルトインのデータ型、演算子、関数だけを用いたものでない限り、リモートサーバにWHERE句は送出されません。また、WHERE句で使われる演算子と関数はIMMUTABLEでなければなりません。
リモートサーバでの実行のために実際に送出される問い合わせはEXPLAIN VERBOSEを用いて調べる事ができます。
postgres_fdwが開いたリモートセッションでは、search_pathパラメータはpg_catalogにだけ設定されますので、スキーマで修飾しなければビルトインオブジェクトだけが可視です。
postgres_fdw自身が生成した問い合わせでは、常にそのような修飾を行ないますので、これは問題になりません。
しかし、リモートテーブルのトリガやルールによってリモートサーバ上で実行された関数にとっては問題の原因となり得ます。
例えば、リモートテーブルが実際にはビューであれば、そのビューで使われている関数はすべて制限された検索パスで実行されるでしょう。
期待される検索パス環境を確立できるよう、そのような関数では名前はすべてスキーマ修飾するか、そのような関数にSET search_pathオプション(CREATE FUNCTION参照)を付けることをお薦めします。
postgres_fdwは、同様に、パラメータTimeZone、DateStyle、IntervalStyle、extra_float_digitsでリモートセッション設定を確立します。
これはsearch_pathほど問題にはならないでしょうが、もし必要になったら関数のSETオプションで処理してください。
上のパラメータのセッションレベルの設定を変更することで、この振舞いを覆すことはお薦めしません。postgres_fdwが正常に動作しない原因となるでしょう。
postgres_fdwのリモートサーバにはPostgreSQL 8.3以降のバージョンを使用する事ができます。
読み取り専用であれば、8.1以降のバージョンまで可能です。
一方、postgres_fdwはIMMUTABLE属性を持ったビルトインの演算子と関数が外部テーブルのWHERE句に含まれる場合、リモート側で実行しても安全であると仮定します。そのため、リモートサーバのリリース後に追加された関数が実行のために送出されるかもしれず、結果として「関数が見つかりません」あるいは類するエラーを発生させる事になります。
この種の問題は問い合わせの書き換えによって対処する事ができます。
例えば、最適化を妨げるため、外部テーブルへの参照をOFFSET 0を付けて副問い合わせに埋め込み、問題のある関数や演算子を副問い合わせの外に配置するなどの方法があります。
これはpostgres_fdwで外部テーブルを作成する例です。
まず、拡張をインストールします。
CREATE EXTENSION postgres_fdw;
次に、CREATE SERVERを使って外部サーバを作成します。
この例では、ホスト192.83.123.89でポート5432を監視しているPostgreSQLサーバに接続します。
接続されるデータベースはリモートサーバ上でforeign_dbという名前です。
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');
リモートサーバで使われるロールを特定するためにユーザマッピングも必要です。ユーザマッピングはCREATE USER MAPPINGで定義されます。
CREATE USER MAPPING FOR local_user
SERVER foreign_server
OPTIONS (user 'foreign_user', password 'password');
これでCREATE FOREIGN TABLEにより外部テーブルが作成できるようになりました。
この例では、リモートサーバのsome_schema.some_tableという名前のテーブルにアクセスします。
対応するローカルの名前はforeign_tableです。
CREATE FOREIGN TABLE foreign_table (
id integer NOT NULL,
data text
)
SERVER foreign_server
OPTIONS (schema_name 'some_schema', table_name 'some_table');
CREATE FOREIGN TABLEで宣言した列のデータ型やその他の属性は、実際のリモートテーブルと一致していることが必須です。
リモートテーブルでどのような名前なのかを個々の列に対してcolumn_nameオプションで指定しない限り、列名も一致していなければなりません。
多くの場合、外部テーブルの定義を手作業で作成するよりも、IMPORT FOREIGN SCHEMAを使用する方が望ましいです。
花田 茂 <shigeru.hanada@gmail.com>