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
、COPY
、TRUNCATE
を使用してリモートテーブルを操作する事もできます。
(もちろん、ユーザマッピングで指定されたリモートユーザは、これらの操作を実行する権限を有している必要があります)
リモートテーブルをアクセスあるいは変更する際、SELECT
、UPDATE
、DELETE
、TRUNCATE
に対してONLY
オプションを指定しても効果はありません。
postgres_fdw
は今のところ、ON CONFLICT DO UPDATE
句のあるINSERT
文をサポートしていないことに注意して下さい。
しかし、一意インデックスの推定の指定を省略しているならば、ON CONFLICT DO NOTHING
句はサポートされます。
postgres_fdw
は、パーティションテーブルで実行されたUPDATE
文により引き起こされる行の移動をサポートしますが、移動した行を挿入するよう選択されたリモートパーティションが同じコマンド内で別の場所で更新されるUPDATE
対象のパーティションでもある場合は、今のところ扱わないことにも注意してください。
一般的な推奨事項として、可能であれば外部テーブルのカラムを、被参照側のリモートテーブル側のカラムと全く同一のデータ型および照合順序によって定義してください。
postgres_fdw
は必要に応じてデータ型の変換を行いますが、リモートサーバがローカルサーバとは異なる問い合わせ条件の解釈を行うため、データ型や照合順序が一致していないと、時には予期しない意味論的に異常な結果を得る事があるかもしれません。
リモートテーブルより少ないカラム数で、あるいは異なった順序であっても外部テーブルを定義できる事に留意してください。 リモートテーブル側のカラムとの対応付けは、その位置ではなく、名前によって行われます。
postgres_fdw
外部データラッパーを使用する外部サーバは、以下に記す許されていないものや特別な取り扱いのものを除き、34.1.2に記載されているlibpqが接続文字列としてサポートするものと同一のオプションを使用する事ができます。
user
、password
およびsslpassword
(これらは代わりにユーザマッピングのオプションの中で指定するか、サービスファイルを使用します)
client_encoding
(これはローカルサーバのエンコーディングが自動的にセットされます)
application_name
-これは接続とpostgres_fdw.application_nameのいずれかまたは両方に現れる可能性があります。
両方が存在する場合、postgres_fdw.application_name
は接続設定を上書きします。
libpqとは異なり、postgres_fdw
はapplication_name
に「エスケープシーケンス」を含めることを許可します。
詳細はpostgres_fdw.application_nameを参照してください。
fallback_application_name
(自動的にpostgres_fdw
とセットされます)
sslkey
とsslcert
- これは、接続とユーザマッピングのどちらか一方、または両方に現れます。
両方に存在する場合、ユーザマッピングの設定が接続設定に優先します。
スーパーユーザのみがsslcert
やsslkey
の設定のあるユーザマッピングを作成したり修正したりできます。
非スーパーユーザはパスワード認証またはGSSAPI委任認証情報を使用して外部サーバに接続するため、パスワード認証が必要な非スーパーユーザに属するユーザマッピングにはpassword
オプションを指定します。
ユーザマッピングオプションpassword_required 'false'
を設定することで、スーパーユーザはこのユーザマッピング単位の検査を無効にできます。例えば以下の通りです。
ALTER USER MAPPING FOR some_non_superuser SERVER loopback_nopw OPTIONS (ADD password_required 'false');
非特権ユーザが、postgresサーバが動作しているunixユーザの認証権限を悪用して、スーパーユーザ権限へ昇格するのを防ぐため、スーパーユーザのみがユーザーマッピングでこのオプションを設定できます。
CVE-2007-3278やCVE-2007-6601により、マップされたユーザがマップされたデータベースにスーパーユーザとして接続するのを許可しないことを確実にするよう注意が必要です。
public
ロールではpassword_required=false
を設定しないでください。
postgresサーバが動作しているシステムユーザのunixのホームディレクトリにある、任意のクライアント証明書や.pgpass
、.pg_service.conf
などをマップされたユーザは潜在的には利用可能なことを心に留めておいてください。
peer
やident
認証のような認証モードで付与された信頼関係を使うこともできます。
これらのオプションによりリモートのPostgreSQLサーバに送出されるSQL文で使用される名前を制御する事ができます。 外部テーブルがリモートテーブルとは異なった名前で定義されている場合、これらのオプションは必須です。
schema_name
(string
)外部テーブルに対して指定できるこのオプションは、リモートサーバ上のリモートテーブルのスキーマ名を与えます。 省略された場合、外部テーブルのスキーマ名が使用されます。
table_name
(string
)外部テーブルに対して指定できるこのオプションは、リモートサーバ上のリモートテーブル名を与えます。 省略された場合、外部テーブルのテーブル名が使用されます。
column_name
(string
)外部テーブルのカラムに対して指定できるこのオプションは、リモートサーバ上のカラム名を与えます。 省略された場合、外部テーブルのカラム名が使用されます。
postgres_fdw
はリモートサーバに対するクエリを実行しリモートのデータを受信します。したがって、理想的には外部テーブルをスキャンする推定コストは、それをリモートサーバで実行するコストと通信オーバーヘッドの和となります。
この推定を行うための最も信頼できる方法は、リモートサーバに問い合わせを行い、その結果にオーバーヘッド分を加算する事ですが、小さいクエリではコスト推定を得るための追加的な問い合わせに要するコストに見合わないかもしれません。
そこで、どのようにコスト推定を行うかを制御するため、postgres_fdw
は以下のようなオプションを提供します。
use_remote_estimate
(boolean
)
外部テーブルまたは外部サーバに指定できるこのオプションは、コスト推定を得るためにpostgres_fdw
がリモートのEXPLAIN
コマンドを発行するかどうかを制御します。
外部テーブルに対する設定は、関連付けられた外部サーバに対する設定を上書きしますが、その効果は当該外部テーブルに限定されます。
デフォルト値はfalse
です。
fdw_startup_cost
(floating point
)
外部テーブルまたは外部サーバに指定できるこのオプションは、当該外部サーバに関連付けられた全ての外部テーブルスキャンの推定開始コストに加算される浮動小数点値です。
これは、接続の確立、リモート側でのクエリのパース・最適化など、追加的なオーバーヘッドを表現します。
デフォルト値は100
です。
fdw_tuple_cost
(floating point
)
外部サーバに指定できるこのオプションは、このサーバでの外部テーブルのスキャンにおいて、各タプル毎に発生する追加的なコストとして使用される浮動小数点値です。
これは、サーバ間のデータ転送における追加的なオーバーヘッドを表現し、リモートサーバへのネットワーク遅延の高低を反映するためにこの数値を増減することができます。
デフォルト値は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を実行します。これはリモートテーブルに対するスキャンを実行し、あたかもローカルなテーブルであるかのように統計情報の計算と保存を行います。
ローカルな統計情報を保存する事で、問い合わせの度にリモートテーブルの実行計画を作成するオーバーヘッドを削減する事ができます。
しかしながら、リモートテーブルの更新頻度が高ければローカルの統計情報はすぐに実態を反映しなくなるでしょう。
以下のオプションは、このようなANALYZE
操作の動作を制御します。
analyze_sampling
(string
)
このオプションは、外部テーブルまたはリモートサーバに対して指定できます。外部テーブルでのANALYZE
によるサンプリングがリモート側で行われるか、すべてのデータを読み取ってローカルでサンプリングするかを決定します。
サポートされている値は、off
、random
、system
、bernoulli
、auto
です。
off
はリモートサンプリングを無効にし、すべてのデータが転送されてローカルでサンプリングされます。
random
はrandom()
関数を使用してリモートサンプリングを実行し、返された行を選択します。一方、system
とbernoulli
はそれらの名前の組込みTABLESAMPLE
メソッドに依存します。
random
はすべてのリモートサーババージョンで動作し、TABLESAMPLE
は9.5以降でのみサポートされます。
auto
(デフォルト)は推奨されるサンプリング方法を自動的に選択します。現在はリモートサーバのバージョンに応じてbernoulli
またはrandom
のいずれかを意味します。
デフォルトでは、組み込みの演算子および関数を使ったWHERE
句のみがリモートサーバでの実行を考慮されます。
組み込みでない関数を含む句は、行が取得された後、ローカルで検査されます。
そのような関数がリモートサーバで利用でき、かつローカルで実行するのと同じ結果を生成すると信頼できるときは、そのようなWHERE
句をリモートでの実行のために送出することでパフォーマンスを向上することができます。
この動作は以下のオプションを使うことで制御できます。
extensions
(string
)このオプションは、PostgreSQLの拡張で、ローカルとリモートの両方に、互換のバージョンがインストールされているものの名前のリストです。 IMMUTABLEで、列挙された拡張に属する関数と演算子は、リモートサーバに送出可能とみなされます。 このオプションは外部サーバについてのみ指定可能で、テーブル毎の指定ではありません。
extensions
オプションを使用する場合、列挙する拡張が存在し、かつローカルとリモートのサーバで同一の動作をするようにすることはユーザの責任です。
そうでない場合、リモートの問い合わせは失敗したり、期待と異なる動作をするかもしれません。
fetch_size
(integer
)
このオプションは、postgres_fdw
が1回のフェッチの動作で何行のデータを取得するかを指定します。
これは外部テーブルあるいは外部サーバに対して指定できます。
テーブルに対して指定されたオプションは、サーバに対して指定されたオプションよりも優先します。
デフォルトは100
です。
batch_size
(integer
)
このオプションは、postgres_fdw
が個々のINSERT操作で挿入する行数を指定します。
外部テーブルあるいは外部サーバに対して指定することができます。
テーブルに対してこのオプションを指定すると、サーバに対して指定されたオプションを上書きします。
デフォルトは1
です。
postgres_fdw
が実際に一度に挿入する行数は、列の数と指定されたbatch_size
値に依存することに注意してください。
一つのバッチは単一の問い合わせとして実行され、libpqプロトコル(postgres_fdw
がリモートサーバに接続するために使用します)は単一の問い合わせにおけるパラメータ数を65535に制限していることに注意してください。
列数 * batch_size
がその上限を超えると、エラーを回避するためにbatch_size
が調整されます。
このオプションは、外部テーブルにコピーする場合にも適用されます。
その場合、postgres_fdw
が一度にコピーする実際の行数は、挿入する場合と類似した方法で決定されますが、COPY
コマンドの実装上の制限により1000以下に制限されます。
postgres_fdw
は非同期実行をサポートします。
これは性能を向上するために、複数のAppend
ノードの部分を順番にではなく、並行して実行します。
この実行は以下のオプションで制御できます。
async_capable
(boolean
)
このオプションは、postgres_fdw
が非同期実行の際に外部テーブルの並列走査を許すかどうかを制御します。
外部テーブルあるいは外部サーバに対して指定できます。
テーブルレベルのオプションはサーバレベルのオプションを上書きします。
デフォルトはfalse
です。
外部サーバから返却されるデータの一貫性を保証するために、postgres_fdw
は一つの外部サーバに対して一つの接続だけを開きます。
そして、テーブルが異なるユーザマッピングの対象でない限り、複数の外部テーブルが存在してもすべての問い合わせをサーバに対して順番に実行します。
この場合、問い合わせを非同期に実行することによるオーバーヘッドをなくすためにこのオプションを使用しないほうが性能が良くなるかもしれません。
Append
ノードに順次実行されるサブプランが含まれていても、あるいは非同期実行されるサブプランが含まれていても、非同期実行は適用されます。
このような場合では、非同期実行のサブプランがpostgres_fdw
を用いて処理されると、外部サーバに送信される非同期問い合わせの結果を非同期サブプランが待っている間に順次実行サブプランが実行されるため、少なくとも1つの順次実行サブプランがすべてのタプルを返すまでは、非同期実行サブプランによるタプルは返りません。
このふるまいは将来のリリースでは変更されるかもしれません。
「トランザクション制御」節で説明されているように、postgres_fdw
では、トランザクションは対応するリモートトランザクションを作成することで制御され、サブトランザクションは対応するリモートサブトランザクションを作成することで制御されます。
現在のローカルトランザクションに複数のリモートトランザクションが含まれている場合、デフォルトではpostgres_fdw
はローカルトランザクションがコミットまたは中断されたときに、これらのリモートトランザクションを順番にコミットまたは中断します。
現在のローカルサブトランザクションに複数のリモートサブトランザクションが含まれている場合、デフォルトではpostgres_fdw
はローカルサブトランザクションがコミットまたは中断されたときに、これらのリモートサブトランザクションを順番にコミットまたは中断します。
次のオプションでパフォーマンスを向上させることができます。
parallel_commit
(boolean
)
このオプションは、ローカルトランザクションがコミットされたときに、ローカルトランザクション内の外部サーバで開かれたリモートトランザクションをpostgres_fdw
が並行してコミットするかどうかを制御します。
この設定は、リモートサブトランザクションとローカルサブトランザクションにも適用されます。
このオプションは外部サーバに対してのみ指定でき、テーブル単位では指定できません。
デフォルトはfalse
です。
parallel_abort
(boolean
)
このオプションは、ローカルトランザクションが中断されたときに、ローカルトランザクション内の外部サーバで開かれたリモートトランザクションをpostgres_fdw
が並行して中断するかどうかを制御します。
この設定は、リモートサブトランザクションとローカルサブトランザクションにも適用されます。
このオプションは外部サーバに対してのみ指定でき、テーブル単位では指定できません。
デフォルトはfalse
です。
このオプションが有効になっている複数の外部サーバがローカルトランザクションに関与している場合、これらの外部サーバ上の複数のリモートトランザクションは、ローカルトランザクションがコミットまたは中断されるときに、これらの外部サーバ間で並列にコミットまたは中断されます。
このオプションを有効にすると、多数のリモートトランザクションを持つ外部サーバは、ローカルトランザクションがコミットまたは中断されたときにパフォーマンスが悪影響を受ける可能性があります。
デフォルトではpostgres_fdw
を使用する全ての外部テーブルは更新可能であると想定されます。以下のオプションにより、この挙動を上書きする事ができます。
updatable
(boolean
)
このオプションは、postgres_fdw
がINSERT
、UPDATE
あるいはDELETE
コマンドを使用して外部テーブルを操作する事を許可するかどうかを規定します。
外部テーブルで指定されたオプションは、外部サーバにおいて指定されたオプションを上書きします。
デフォルト値はtrue
です。
もちろん、リモートテーブルが実際には更新可能ではなかった場合、いずれにしてもエラーが発生するでしょう。このオプションを使用することで、リモートサーバへの問い合わせを行う事なくローカルでエラーを発生させることができます。
また、information_schema
ビューは、このオプションの値に従ってpostgres_fdw
管理下の外部テーブルを更新可能(あるいは不可能)であるとレポートする事に留意してください。
リモートサーバ側のチェックは一切行われません。
デフォルトではpostgres_fdw
を使用する外部テーブルは切り詰め可能であると見なされます。
これは以下のオプションで変更が可能です。
truncatable
(boolean
)
このオプションはpostgres_fdw
が外部テーブルをTRUNCATE
を使って切り詰めることができるかどうかを制御します。
外部テーブルあるいは外部サーバに対して指定できます。
外部テーブルで指定されたオプションは、外部サーバにおいて指定されたオプションを上書きします。
デフォルトはtrue
です。
もちろん外部テーブルが切り詰め不可能なら、結局エラーが生じます。 このオプションを使用することにより、リモートサーバに問い合わせることなくエラーをローカルで起こすことができるのが主な用途です。
postgres_fdw
はIMPORT FOREIGN SCHEMAを使って、外部テーブルの定義をインポートすることができます。
このコマンドは、リモートのサーバ上に存在するテーブルあるいはビューとマッチする外部テーブルの定義をローカルサーバ上に作成します。
インポートするリモートのテーブルにユーザ定義のデータ型の列がある場合、ローカルサーバにも同じ名前の互換性のある型がなければなりません。
インポートの動作は以下のオプションでカスタマイズできます(IMPORT FOREIGN SCHEMA
コマンドで指定します)。
import_collate
(boolean
)
このオプションは、列のCOLLATE
オプションが、外部サーバからインポートする外部テーブルの定義に含まれているかどうかを制御します。
デフォルトはtrue
です。
リモートサーバとローカルサーバで照合順序の名前の集合が異なる場合は、この設定を無効にする必要があるでしょう。
リモートサーバが異なるOSで動作しているなら、そういうことがありそうです。
しかし、そうした時には、インポートしたテーブルの列の照合順序が実際のデータと一致せず、問い合わせの振る舞いが結果として異常になる大きなリスクがあります。
このパラメータがtrue
のときでさえ、照合順序がリモートサーバのデフォルトである列をインポートするのは危険性があります。
これらの列はCOLLATE "default"
としてインポートされますが、ローカルサーバのデフォルトの照合順序は異なるかもしれません。
import_default
(boolean
)
このオプションは、列のDEFAULT
式が外部サーバからインポートされる外部テーブルの定義に含まれているかどうかを制御します。
デフォルトはfalse
です。
このオプションを有効にする場合は、ローカルサーバとリモートサーバで異なる計算をされるデフォルトに注意して下さい。
nextval()
はよくある問題の一つです。
インポートされるデフォルト式がローカルには存在しない関数または演算子を使っていた場合、IMPORT
は失敗します。
import_generated
(boolean
)
このオプションは、外部サーバからインポートされた外部テーブルの定義にGENERATED
列式が含まれるかどうかを制御します。
デフォルトはtrue
です。
インポートされる生成式がローカルには存在しない関数あるいは演算子を使っていた場合、IMPORT
は失敗します。
import_not_null
(boolean
)
このオプションは、列のNOT NULL
制約が、外部サーバからインポートされる外部テーブルの定義に含まれているかどうかを制御します。
デフォルトはtrue
です。
NOT NULL
以外の制約は決してリモートのテーブルからインポートされないことに注意して下さい。
PostgreSQLは外部テーブルのcheck制約をサポートしていますが、それを自動的にインポートする予定はありません。
なぜなら、制約の式はローカルとリモートのサーバで異なる評価をされる危険があるからです。
check制約でそのような一貫しない動作があると、問い合わせの最適化で検知するのが難しい誤りが発生するかもしれません。
そのため、check制約をインポートしたい場合は、それを手作業で実行する必要があり、またその一つ一つの意味を注意深く確認するべきです。
外部テーブルのcheck制約の取扱いについて、詳しくはCREATE FOREIGN TABLEを参照して下さい。
他のテーブルのパーティションであるテーブルや外部テーブルは、明示的なLIMIT TO
句が指定されている場合にのみインポートされます。
そうでなければそれらは、IMPORT FOREIGN SCHEMAから自動的に除外されます。
パーティショニング化階層のルートであるパーティションテーブルを介してすべてのデータにアクセスできるため、パーティション化テーブルのみをインポートすることで余分なオブジェクトを作成せずにすべてのデータにアクセスできます。
デフォルトではpostgres_fdw
が外部サーバに確立した接続は、再利用のためにローカルセッションにおいて開いたまま維持されます。
keep_connections
(boolean
)
このオプションは、後の再利用のためにpostgres_fdw
が外部サーバに対する接続を保持したままにしておくかどうかを制御します。
外部サーバに対してのみ指定ができます。
デフォルトはon
です。
off
に設定すると、この外部サーバに対するすべての接続は個々のトランザクションの終了時に破棄されます。
postgres_fdw_get_connections(OUT server_name text, OUT valid boolean) returns setof record
この関数は、postgres_fdw
によってローカルセッションから外部サーバに確立されたすべての開いている接続の外部サーバ名を返します。
個々の接続が有効かどうかも返します。
外部サーバの接続が現在のローカルトランザクションで使用されていても、外部サーバあるいはユーザマッピングが変更あるいは削除されるとfalse
が返り(無効な接続のサーバ名は、サーバが削除されるとNULL
になることに注意してください)、そうした無効な接続はトランザクションの終了時に閉じられます。
そうでなければtrue
が返ります。
開いた接続がなければレコードは返りません。
関数の使用例を示します。
postgres=# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1; server_name | valid -------------+------- loopback1 | t loopback2 | f
postgres_fdw_disconnect(server_name text) returns boolean
この関数は、postgres_fdw
によってローカルセッションから指定された名前の外部サーバに確立された開いている接続を破棄します。
異なるユーザマッピングを使用することにより、指定されたサーバに複数の接続が存在する可能性があることに注意してください。
現在のローカルトランザクションで接続が使われている場合は、接続は切断されず、警告メッセージが報告されます。
少なくとも1つの接続が切断されると、この関数はtrue
を返し、そうでない場合はfalse
を返します。
指定した名前の外部サーバが存在しなければ、エラーが報告されます。
この関数の使用例を示します。
postgres=# SELECT postgres_fdw_disconnect('loopback1'); postgres_fdw_disconnect ------------------------- t
postgres_fdw_disconnect_all() returns boolean
この関数は、postgres_fdw
によってローカルセッションから外部サーバに確立されたすべての開いている接続を破棄します。
現在のローカルトランザクションで接続が使われている場合は、接続は切断されず、警告メッセージが報告されます。
少なくとも1つの接続が切断されると、この関数はtrue
を返します。
さもなければfalse
が返ります。
この関数の使用例を示します。
postgres=# SELECT postgres_fdw_disconnect_all(); postgres_fdw_disconnect_all ----------------------------- t
postgres_fdw
は、外部サーバに関連付けられた外部テーブルを参照するクエリを最初に実行する際に、外部サーバへの接続を確立します。
デフォルトではこの接続は保持され、同じセッションで以降の問い合わせのために再利用されます。
この振る舞いは外部サーバのkeep_connections
オプションを使って制御できます。
しかし、外部サーバへのアクセスに対して複数のユーザ識別子(ユーザマッピング)が使用される場合には、接続はユーザマッピング毎に確立される事になります。
外部サーバあるいはユーザマッピングの定義を変更ないし削除している場合は、関連する接続は閉じられます。 しかし、現在のローカルトランザクションが接続を使っていると、接続はトランザクションが終了するまでは維持されることに注意してください。 このあと外部テーブルを使用する問い合わせで必要になれば、閉じた接続は再び確立されます。
ひとたび外部サーバに接続が確立されると、デフォルトではローカルあるいは関連するリモートセッションが終了するまで接続は維持されます。
明示的に接続を切断するには外部サーバのkeep_connections
オプションを無効にするか、postgres_fdw_disconnect
関数あるいはpostgres_fdw_disconnect_all
関数を使用します。
たとえば、必要がなくなった接続を切断し、それによって外部サーバの接続を開放するのにこれらの関数が役に立ちます。
外部サーバ上のリモートテーブルを参照する際に、まだトランザクションが開始されていなければpostgres_fdw
はリモートサーバ上でトランザクションを開始します。
ローカルのトランザクションがコミット、あるいはアボートした時、リモートのトランザクションも同様にコミット、あるいはアボートします。
セーブポイントも同様に管理され、リモート側に関連付けられたセーブポイントが作成されます。
ローカルトランザクションがSERIALIZABLE
分離レベルを用いている時、リモートトランザクションもSERIALIZABLE
分離レベルを使用します。
それ以外の場合にはREPEATABLE READ
分離レベルを使用します。
これは、あるクエリが複数のテーブルスキャンをリモート側で行う際に、確実に全てのスキャンにおいて一貫したスナップショットで結果を取り出すためです。
その結果、別の要求によってリモートサーバ側で競合する更新が発生したとしても、あるトランザクション内の問い合わせはリモートサーバからの一貫したデータを参照する事となります。
ローカルのトランザクションがSERIALIZABLE
あるいはREPEATABLE READ
分離レベルを用いている場合、この動作は期待通りのものでしょう。
一方、ローカルのトランザクションがREAD COMMITTED
分離レベルを使用している場合には、予想外の動作かもしれません。
将来のPostgreSQLリリースではこれらのルールに変更が加えられるかもしれません。
postgres_fdw
は今のところ、二相コミットのためのリモートトランザクションの準備をサポートしていないことに注意して下さい。
外部サーバからのデータ転送量を削減するため、postgres_fdw
はリモート問い合わせを最適化しようと試みます。
これは問い合わせのWHERE
句をリモートサーバに送出する事、およびクエリで必要とされていないカラムを取得しない事により行われます。
問い合わせの誤作動のリスクを下げるため、組み込みあるいは外部サーバのextensions
オプションに列挙されている拡張に属するデータ型、演算子、関数だけを用いたものでない限り、リモートサーバにWHERE
句は送出されません。
また、そのようなWHERE
句で使われる演算子と関数はIMMUTABLE
でなければなりません。
UPDATE
あるいはDELETE
の問い合わせについては、リモートサーバに送出できないWHERE
句がなく、問い合わせにローカルな結合がなく、対象のテーブルにローカルな行レベルのBEFORE
あるいはAFTER
トリガーや格納された生成列がなく、親ビューからのCHECK OPTION
制約がないのであれば、postgres_fdw
は問い合わせ全体をリモートサーバに送出することで、問い合わせの実行の最適化を図ります。
UPDATE
では、問い合わせの誤った実行のリスクを低減するため、対象列への代入式では組み込みのデータ型、IMMUTABLE
演算子、IMMUTABLE
関数のみを使わなければなりません。
同一の外部サーバ上の外部テーブルの間の結合がある場合、postgres_fdw
はその結合全体を外部サーバに送出します。
ただし、何らかの理由で各テーブルから個別に行を取得する方が効率的だと思われる場合、あるいは結合に含まれるテーブルの参照が異なるユーザマッピングに従う場合を除きます。
JOIN
句を送出するにあたり、WHERE
句に関して上で説明したことと同じ注意が払われます。
リモートサーバでの実行のために実際に送出される問い合わせはEXPLAIN VERBOSE
を用いて調べる事ができます。
postgres_fdw
が開いたリモートセッションでは、search_pathパラメータはpg_catalog
にだけ設定されますので、スキーマで修飾しなければ組み込みオブジェクトだけが可視です。
postgres_fdw
自身が生成した問い合わせでは、常にそのような修飾を行ないますので、これは問題になりません。
しかし、リモートテーブルのトリガやルールによってリモートサーバ上で実行された関数にとっては問題の原因となり得ます。
例えば、リモートテーブルが実際にはビューであれば、そのビューで使われている関数はすべて制限された検索パスで実行されるでしょう。
期待される検索パス環境を確立できるよう、そのような関数では名前はすべてスキーマ修飾するか、そのような関数にSET search_path
オプション(CREATE FUNCTION参照)を付けることをお薦めします。
postgres_fdw
は、同様に様々なパラメータでリモートセッション設定を確立します。
TimeZoneはUTC
に設定されます。
DateStyleはISO
に設定されます。
IntervalStyleはpostgres
に設定されます。
extra_float_digitsはリモートサーバが9.0以上では3
に設定され、それより古いバージョンでは2
に設定されます。
これはsearch_path
ほど問題にはならないでしょうが、もし必要になったら関数のSET
オプションで処理してください。
上のパラメータのセッションレベルの設定を変更することで、この振舞いを覆すことはお薦めしません。
postgres_fdw
が正常に動作しない原因となるでしょう。
postgres_fdw
のリモートサーバにはPostgreSQL 8.3以降のバージョンを使用する事ができます。
読み取り専用であれば、8.1以降のバージョンまで可能です。
一方、postgres_fdw
はIMMUTABLE
属性を持った組み込みの演算子と関数が外部テーブルのWHERE
句に含まれる場合、リモート側で実行しても安全であると仮定します。そのため、リモートサーバのリリース後に追加された関数が実行のために送出されるかもしれず、結果として「関数が見つかりません」あるいは類するエラーを発生させる事になります。
この種の問題は問い合わせの書き換えによって対処する事ができます。
例えば、最適化を妨げるため、外部テーブルへの参照をOFFSET 0
を付けてsub-SELECT
に埋め込み、問題のある関数や演算子をsub-SELECT
の外に配置するなどの方法があります。
postgres_fdw.application_name
(string
)
#
postgres_fdw
が外部サーバへの接続を確立する際に使用されるapplication_name設定パラメータの値を指定します。
これにより、サーバオブジェクトのapplication_name
オプションが上書きされます。
このパラメータを変更しても、再確立されるまで既存の接続には影響しません。
postgres_fdw.application_name
は任意の長さの文字列で、非ASCII文字も含むことさえできます。
しかし、外部サーバでapplication_name
として渡されて使用される場合、NAMEDATALEN
文字未満に切り捨てられることに注意してください。
印字可能なASCII文字以外の文字はC言語形式の16進数エスケープ文字に置き換えられます。
詳細はapplication_nameを参照してください。
%
文字は「エスケープシーケンス」を開始し、以下で説明するようにステータス情報に置き換えられます。
認識されないエスケープは無視されます。
他の文字はアプリケーション名に直接コピーされます。
位置合わせとパディングのために、%
の後とオプションの前にプラス/マイナス記号または数値リテラルを指定することはできないことに注意してください。
エスケープ | 効果 |
---|---|
%a | ローカルサーバ上のアプリケーション名 |
%c | ローカルサーバ上のセッションID(詳細はlog_line_prefixを参照) |
%C | ローカルサーバ上のクラスタ名(詳細はcluster_nameを参照) |
%u | ローカルサーバ上のユーザ名 |
%d | ローカルサーバ上のデータベース名 |
%p | ローカルサーバ上のバックエンドのプロセスID |
%% | 文字 % |
たとえば、ユーザーlocal_user
がデータベースlocal_db
からforeign_db
への接続をユーザーforeign_user
として確立するとします。
設定'db=%d,user=%u'
は'db=local_db,user=local_user'
に置き換えられます。
これは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>