dblink_plus
名前
dblink_plus -- 外部データベース・サーバに接続し、SQLを実行します。
API一覧
dblink.connect(server, use_xa DEFAULT true) : boolean
dblink.connect(name, server, use_xa DEFAULT true) : boolean
dblink.disconnect(name) : boolean
dblink.query(name, sql, fetchsize DEFAULT 0, max_value_len DEFAULT -1) : SETOF record
dblink.exec(name, sql) : bigint
dblink.open(name, sql, fetchsize DEFAULT 100, max_value_len DEFAULT -1) : dblink.cursor
dblink.fetch(dblink.cursor, howmany DEFAULT 1) : SETOF record
dblink.close(dblink.cursor) : boolean
dblink.call(name, sql, fetchsize DEFAULT 0, max_value_len DEFAULT -1) : SETOF record
dblink.connections() : SETOF record
概要
データベースセッション内から他のデータベースへ接続するためのモジュールです。PostgreSQL 以外のデータベース・サーバへの接続をサポートしています。dblink_plus を使うことにより商用データベースからのデータ移行が外部ファイルを介さずに行えるほか、異種RDBMS間のオンラインでのテーブル結合等が実現可能となります。
dblink_plus は contrib/dblink と異なり、リモートのトランザクションを含めた2相コミットが可能です。
即ち、ローカルのトランザクションがコミット / ロールバックした場合、リモートのトランザクションも同様にコミット / ロールバックします。
特に更新処理にて、一貫性のあるデータベース操作を容易に実現できます。
外部データベース・コネクタにはプラグイン方式を採用しており、PostgreSQL, Oracle Database, MySQL, sqlite3 に接続できます。
自動トランザクション管理について
dblink_plus は自動トランザクション管理をサポートしています。これによりユーザーはローカルのトランザクションだけを意識すればよく、リモート側のトランザクションについて意識しなくても、dblink_plus が自動で2相コミットを行ってくれます。
=# BEGIN;
=# INSERT INTO postgres_table values (...); -- ローカルテーブルの挿入
=# SELECT dblink.exec('oracle_conn', 'insert into oracle_table ...'); -- リモート（Oracle）への挿入
=# COMMIT; -- ローカルのコミットですが、リモートのOracleも自動的にコミットされます
自動トランザクション管理の有効／無効は接続毎に設定でき、 dblink.connect() の引数 use_xa として true/false で指定できます。
また、use_xa は GUC パラメータとして、 postgresql.conf にも設定できます。この値は接続を指定せずに dblink.query(), dblink.exec() などを実行した際に使用されます。詳細は、設定パラメータを参照ください。
コネクタ・プラグインについて
dblink_plus は外部データベースとの接続コネクタに、プラグイン方式を採用しています。
現在サポートされているコネクタは PostgreSQL, Oracle です。これらのコネクタは、dblink_plus モジュールのビルド時に指定可能です。
dblink.postgres - PostgreSQLコネクタ
PostgreSQL用のコネクタです。ビルドおよび実行にはlibpqライブラリが必要です。
dblink.oracle - Oracleコネクタ
Oracle用のコネクタです。ビルドおよび実行にはOracleクライアントおよびOCIライブラリがインストールされている必要があります。またOracle用コネクタはOracleクライアントとして実行されるため、PostgreSQLを起動するユーザーでOracle用環境変数の設定が必要となります。
- ORACLE_HOME - （必須）
- LD_LIBRARY_PATH - （必須）
- ORACLE_SID - （ローカル接続の場合のみ必要）
- NLS_LANG - （オプション）
- NLS_DATE_FORMAT - （オプション）
- NLS_TIMESTAMP_FORMAT - （オプション）
- NLS_TIMESTAMP_TZ_FORMAT - （オプション）
- NLS_TIME_FORMAT - （オプション）
- NLS_TIME_TZ_FORMAT - （オプション）
クライアントキャラクタセットはPostgreSQLデータベースセッションのエンコーディングがOracleに対しても適用されます。取得されたPostgreSQLのキャラクタセットがOracleコネクタで認識できない場合、NLS_LANGの設定に従います。NLS_LANGの設定がない場合はOracleのデフォルトとなります。
日付/時刻型の変換は、環境変数NLS_xxxx_FORMATの指定に従います。
dblink.mysql - MYSQLコネクタ
MYSQL用のコネクタです。ビルドおよび実行にはMYSQLクライアントパッケージがインストールされている必要があります。
dblink.sqlite3 - sqlite3コネクタ
sqlite3用のコネクタです。ビルドおよび実行にはsqlite3ライブラリが必要です。
使用例
dblink_plus を使用する前に、外部データラッパ、外部サーバ、ユーザマッピングを作成する必要があります。
=# CREATE FOREIGN DATA WRAPPER postgres VALIDATOR dblink.postgres;
=# CREATE SERVER my_server FOREIGN DATA WRAPPER postgres OPTIONS (dbname 'mydb');
=# CREATE USER MAPPING FOR local_user SERVER my_server OPTIONS (user 'remote_user');
接続する先のDBに応じて、CREATE FOREIGN DATA WRAPPER にて VALIDATOR に dblink_plus が提供するコネクタを指定してください。
OracleコネクタでのOPTIONSの指定
CREATE SERVERでは dbname と max_value_len を、CREATE USER MAPPINGでは user と password を指定します。
- dbname
- TNS接続文字列です。
- max_value_len
- 取り扱う列データの最大長（文字列に変換後のバイト長）を指定します。
この外部サーバ定義を利用して検索される行データの内、最大長となる列データの長さ（文字列に変換後のバイト長）が事前に分かっている場合は、その値を設定することにより検索処理が高速になります（ただし、指定した値より列のデータ長が大きくなる場合はエラーとなります）。このオプションを省略した場合は、内部的に列バッファを自動調整しながら検索処理が行なわれますので、低速になることがあります。
- user
- Oracleデータベースユーザ名です。
- password
- Oracleデータベースユーザのパスワードです。
=# CREATE FOREIGN DATA WRAPPER oracle VALIDATOR dblink.oracle;
=# CREATE SERVER server_oracle FOREIGN DATA WRAPPER oracle OPTIONS (dbname 'DBT', max_value_len '200');
=# CREATE USER MAPPING FOR local_user SERVER server_oracle OPTIONS (user 'scott', password 'tiger');
最も簡単なSQLの実行
サーバ名を指定して、dblink.exec()を用い更新系SQLを実行します。実行結果に影響行数が返ります。
=# SELECT dblink.exec('my_server','insert into tbl values(500, ''remote'')');
exec
------
1
(1 row)
サーバ名を指定して、dblink.query()を用い参照系SQLを実行します。実行結果に検索された全ての行が返ります。
=# SELECT * FROM dblink.query('my_server','select * from tbl') as t(c1 integer, c2 text);
c1 | c2
-----+--------
100 | host
500 | remote
(2 rows)
接続を指定した実行
接続をユーザが管理する場合には、dblink.connect(), dblink.disconnect() を使います。
=# SELECT dblink.connect('my_conn', 'my_server');
=# SELECT dblink.exec('my_conn', 'UPDATE tbl SET col = 999 WHERE id = 1');
=# SELECT dblink.disconnect('my_conn');
明示的に接続せずにサーバ名を指定した場合には、トランザクション終了時に自動的に切断されます。
=# BEGIN;
=# SELECT dblink.exec('my_server', 'UPDATE tbl SET col = 999 WHERE id = 1');
=# COMMIT; -- 切断される
open, fetch, closeの実行
カーソルをオープンし、このカーソルを使いテーブルを検索します。カーソルはトランザクション内で有効です。
=# BEGIN;
=# select dblink.open('my_server', 'select * from tbl');
open
------
1
(1 row)
=# select * from dblink.fetch(1) as t(c1 integer, c2 text);
c1 | c2
-----+--------
100 | host
(1 row)
=# select * from dblink.fetch(1, 2) as t(c1 integer, c2 text);
c1 | c2
-----+--------
500 | remote
10 | dddddd
(2 rows)
=# select dblink.close(1);
close
-------
t
(1 row)
=# COMMIT;
DDL文の実行
分散トランザクション中にDDLの実行を許していないDBMSでは、dblink.connect()で use_xa を false で接続し、DDL文を dblink.exec()で実行します。
=# SELECT dblink.connect('conn_ora', 'server_oracle', false);
connect
---------
t
(1 row)
=# SELECT dblink.exec('conn_ora', 'CREATE TABLE dblink_tbl (id NUMBER(4), value VARCHAR2(100))');
exec
------
0
(1 row)
=# SELECT dblink.disconnect('conn_ora');
disconnect
------------
t
(1 row)
関数
dblink_plus は下記の関数を含みます。
dblink.connect(server, use_xa DEFAULT true) : boolean
外部サーバ 'server' に接続します。接続の名前は 'server' になります。
use_xa が true の場合、自動トランザクション管理が有効になります。
use_xa を指定しなかった場合は、true として動作します。これは GUC パラメータで use_xa を off にしていても変わりません。
use_xa を明示的に引数に指定した場合、GUC パラメータで指定した値を上書きして動作します。
接続は、セッション終了時または dblink.disconnect() を呼ぶまで維持されます。
- server
- 外部サーバ名です。
- use_xa
- 自動トランザクションの有効／無効を true/false で指定します。
返値:
新規の接続の場合は true が返却されます。
既に同じ名前の接続があった場合は false が返却されます。
dblink.connect(name, server, use_xa DEFAULT true) : boolean
外部サーバ 'server' に接続します。接続の名前は 'name' になります。
それ以外は dblink.connect(server, use_xa) と同じです。
dblink.disconnect(name) : boolean
名前 'name' の接続を切断します。
ただし、この関数はすぐには切断しません。
トランザクションの終了時に実際に切断します。
- name
- 切断する接続名です。
返値:
指定した名前の接続が存在した場合は true が返却されます。
見つからなければ false が返却されます。(エラーにはなりません)
dblink.query(name, sql, fetchsize DEFAULT 0, max_value_len DEFAULT -1) : SETOF record
参照SQLを実行します。'name'に外部サーバー名を指定した場合、SQLの実行前に該当サーバーへ接続を行います。またSQL実行後に該当サーバーから切断を行います。
- name
- 接続名または外部サーバー名を指定します。
- sql
- 実行するSQL文です。
- fetchsize
- 内部バッファの行数です。0を指定すると外部データベース・コネクタで最適な値となります。
- max_value_len
- 取り扱う列データの最大長（文字列に変換後のバイト長）を指定します。
Oracle外部サーバ定義の 'max_value_len' オプションと同様の効果があり、dblink.query()の実行範囲内で有効となります。0を指定すると外部サーバ定義の 'max_value_len' オプション指定は無視され、内部的に列バッファを自動調整しながら検索処理が行なわれます。引数を省略した場合は、外部サーバ定義の 'max_value_len' オプション指定に従い動作します。この引数はOracleコネクタのみで有効となります。
返値:
結果の行セットです。
dblink.exec(name, sql) : bigint
更新SQLを実行します。
- name
- 接続名または外部サーバー名を指定します。
- sql
- 実行するSQL文です。
返値:
SQLが影響した行数です。
dblink.cursor [TYPE]
カーソルハンドルです。
dblink.open(name, sql, fetchsize DEFAULT 100, max_value_len DEFAULT -1) : dblink.cursor
参照SQLを実行し、結果を分割して取得するためのカーソルを作成します。その後カーソルをdblink.fetch()とdblink.close()で操作することができます。
- name
- 接続名または外部サーバー名を指定します。
- sql
- 実行するSQL文です。
- fetchsize
- 内部バッファの行数です。0を指定すると外部データベース・コネクタで最適な値となります。
- max_value_len
- 取り扱う列データの最大長（文字列に変換後のバイト長）を指定します。
Oracle外部サーバ定義の 'max_value_len' オプションと同様の効果があり、dblink.open()で作成したカーソルの範囲内で有効となります。0を指定すると外部サーバ定義の 'max_value_len' オプション指定は無視され、内部的に列バッファを自動調整しながら検索処理が行なわれます。引数を省略した場合は、外部サーバ定義の 'max_value_len' オプション指定に従い動作します。この引数はOracleコネクタのみで有効となります。
返値:
カーソルハンドルです。
PostgreSQLコネクタ利用時において、引数 fetchsize に1以上が指定され、かつクエリの結果が0件となる場合は、カーソルが作成されずにカーソルハンドルは常に0値で返却されます。
dblink.fetch(dblink.cursor, howmany DEFAULT 1)
カーソルから結果を分割して取得します。オープンされていない無効なカーソルハンドル値を引数 dblink.cursor に指定した場合は、エラーとせずに0件の結果が返却されます。
- dblink.cursor
- カーソルハンドルです。
- howmany
- フェッチする行数です。
返値:
結果の行セットです。
dblink.close(dblink.cursor)
カーソルを閉じます。オープンされていない無効なカーソルハンドル値を引数 dblink.cursor に指定した場合でもエラーとしません。
- dblink.cursor
- カーソルハンドルです。
dblink.call(name, sql, fetchsize DEFAULT 0, max_value_len DEFAULT -1) : SETOF record
ストアドファンクションを実行します。
- name
- 接続名または外部サーバー名を指定します。
- sql
- 実行するストアドファンクション名です。
- fetchsize
- 内部バッファの行数です。0を指定すると外部データベース・コネクタで最適な値となります。
- max_value_len
- 取り扱う列データの最大長（文字列に変換後のバイト長）を指定します。
Oracle外部サーバ定義の 'max_value_len' オプションと同様の効果があり、dblink.call()の実行範囲内で有効となります。0を指定すると外部サーバ定義の 'max_value_len' オプション指定は無視され、内部的に列バッファを自動調整しながら検索処理が行なわれます。引数を省略した場合は、外部サーバ定義の 'max_value_len' オプション指定に従い動作します。この引数はOracleコネクタのみで有効となります。
返値:
結果の行セットです。
dblink.connections [VIEW]
現在使用中の接続の一覧を返すビューです。
| カラム名 |
データ型 |
説明 |
| name |
text |
接続に付けた名前です。明示的に指定していない場合、外部サーバ名と同じです。 |
| server |
oid |
接続先の外部サーバの oid です。外部サーバの oid は pg_foreign_server カタログで確認できます。 |
| status |
text |
接続の使用状態です。
- idle : 接続されて何も行なっていない状態
- used : 2相コミットのトランザクション中の状態
- prepared : 2相コミットでのPREPARE状態 |
| use_xa |
boolean |
接続が2相コミットを利用しているかどうかのフラグです。use_xa にて指定されます。 |
| keep |
boolean |
接続が dblink.connect() 経由で行われたものであるかどうかのフラグです。
dblink.connect() が開始した接続は dblink.disconnect() されるまで接続が維持されます。 |
dblink.postgres(text[], oid) RETURNS boolean
PostgreSQL に接続するためのコネクタです。
外部データラッパ検証関数として実装されており、以下の形式で使用します。
CREATE FOREIGN DATA WRAPPER postgres VALIDATOR dblink.postgres;
dblink.mysql(text[], oid) RETURNS boolean
MySQL に接続するための外部データラッパ検証関数です。
それ以外は dblink.postgres() と同じです。
dblink.oracle(text[], oid) RETURNS boolean
Oracle に接続するための外部データラッパ検証関数です。
それ以外は dblink.postgres() と同じです。
設定パラメータ
- dblink_plus.use_xa
-
GUC パラメータとして設定した use_xa の値は、dblink.query() や dblink.exec() などを接続を指定せずに実行した場合に使用されます。
dblink.connect() 時に use_xa を明示的に指定した場合はそちらの値が優先されます。
特に、dblink.connect() において明示的に use_xa を指定しなかった場合は GUC で設定した値は使用されず、 自動トランザクション有効で接続が開始されます。
このパラメータは postgresql.conf に設定します。
custom_variable_classes = 'dblink_plus' # PostgreSQL9.2以降ではこの項目は不要
dblink_plus.use_xa = off # 記載しなかった場合は on (true)として動作する
なお、shared_preloaded_librariesへの登録は必要ありません。
使用上の注意と制約
dblink_plus を使用する際には、以下の使用上の注意と制約があります。
Oracleコネクタで制限となる型
Oracleのデータ型 LONG, LONG RAW, NCLOB, BLOB, BFILE, RAW の検索についてはサポートしていません。
これらのデータ型を検索した場合、"Not supported data type" のエラー、もしくはOracleのエラー（ORA-XXXXX）が発生します。
メモリ消費の問題
大量のデータを検索した場合、ローカルセッションのメモリを大量に消費する恐れがあります。なるべくサーバーサイドで絞込みを行ったSQLを発行してください。
特にOracleのCLOB型のについては4Gバイトまでのテキストデータを格納できますが、Oracleコネクタは読み込みに必要なバッファを一度に取得するため、メモリ確保エラーでSQLが異常終了する場合があります。
Oracleコネクタの外部サーバ定義や dblink.query()、dblink.open()、dblink.call()関数において 'max_value_len' の列データ長を指定した場合、フェッチ用のバッファとして消費されるメモリ量は、おおよそ「(max_value_len+1) × 列数 × フェッチ行数」となります。
また、処理を中断するとメモリの解放が完全に行われないため、メモリリークの原因になるので、注意してください。
PostgreSQLの設定
PostgreSQLコネクタを使って接続する場合、自動トランザクション管理による2相コミットを有効にするため、リモートデータベースのGUCパラメータ max_prepared_transactions を1以上に設定してください。
ユーザに必要なオブジェクト権限
dblink_plus を使用する非スーパーユーザには、以下の3種類の権限を付与してください。
- dblink スキーマの USAGE 権限
- dblink スキーマ内に存在する関数の EXECUTE 権限
- dblink スキーマ内に存在するテーブルの DELETE 権限
自動トランザクション管理有効時に使用できないコマンド
自動トランザクションが有効の場合、トランザクションブロックを使用するため、トランザクションブロック内で実行できないコマンドは使用できません。
例として以下のコマンドがあります。
- PostgreSQL では、VACUUM 文
- ORACLE では、CREATE TABLE 文や TRUNCATE 文等の DDL
log_statement の設定
dblink_plus は、SELECT 文で実行されるため、ローカルのGUCパラメータ log_statement を mod に設定して更新系SQLを実行してもログには残りません。そのため、dblink_plus を使用したログを残すためには、ローカル側の log_statement を all に設定してください。
ホットスタンバイサーバでのdblink_plus
自動トランザクション管理が有効の場合、接続先の DB サーバとの間で2相コミットを行うために内部で DELETE トリガを使用します。そのため、レプリケーション構成でのレプリカ側サーバやホットスタンバイサーバ等の参照専用のDBでは、自動トランザクション管理を有効にして動作させることができません。
実行しようとした場合、以下のエラーが発生します。
postgres=# SELECT * FROM dblink.query('server_oracle', 'SELECT * FROM tbl') AS t(c1 int, c2 text);
ERROR: cannot execute DELETE in a read-only transaction
CONTEXT: SQL statement "DELETE FROM dblink.atcommit"
これらのサーバで dblink_plus を使用する場合、自動トランザクション管理を無効にして外部サーバに接続する必要があります。
use_xa を false にした接続を作成して使用するか、postgresql.conf にて use_xa を off に設定してください。
以下は、use_xa を false にした接続を明示的に作成する例です。
postgres=# SELECT dblink.connect('my_conn', 'server_oracle', false);
connect
---------
t
(1 行)
postgres=# SELECT * FROM dblink.connections ;
name | server | status | use_xa | keep
---------+--------+--------+--------+------
my_conn | 16594 | idle | f | t
(1 行)
postgres=# SELECT * FROM dblink.query('my_conn', 'SELECT * FROM tbl') AS t(c1 int, c2 text);
c1 | c2
-----+--------
204 | remote
403 | remote
104 | remote
203 | remote
(4 行)
postgresql.conf に use_xa を off で設定した場合、明示的に use_xa を false で開いた接続を用意しなくても各 API が実行できるようになります。ただし、 use_xa を引数として指定せずに dblink.connect() を実行すると、その接続は use_xa が true になります。API 実行時に接続を指定する場合は上記の様に use_xa を false として明示的に指定した接続を使用してください。
なお、自動トランザクション管理を無効にすることで dblink_plus は接続先のDBサーバとの間で2相コミットを行わなくなるため、DBサーバ間でデータの一貫性を保たれるようにクライアント側で考慮する必要があります。
詳細
全体構成
dblink.call()で呼出し可能ファンクションの型式
呼出し可能のファンクションは、各DBMSによって作成方法に制約があります。
Oracleのストアドプロシージャ
カーソルとの連携のため、パッケージの作成とカーソル型の定義、およびレコードを出力するプロシージャを作成する必要があります。
下の例は、dblinkというパッケージを作成し、そのパッケージ内プロシージャとしてf_test()を作成しています。
CREATE OR REPLACE PACKAGE dblink AS
TYPE RT1 IS RECORD ( ret NUMBER );
TYPE RCT1 IS REF CURSOR RETURN RT1;
PROCEDURE f_test(RC IN OUT RCT1, num IN NUMBER);
END;
CREATE OR REPLACE PACKAGE BODY dblink AS
PROCEDURE f_test(RC IN OUT RCT1, num IN NUMBER)
AS
BEGIN
OPEN RC FOR
select c1 + num from test_tbl;
END f_test;
END;
このプロシージャは、以下のようにcall関数を使って呼び出します。
=# select * from dblink.call('sv_ora', 'dblink.f_test(100)') as t(id integer);
PostgreSQLのストアドファンクション
出力パラメータを指定していない SETOF RECORD を戻すファンクションは呼び出すことができません。これは、PostgreSQL用call関数が、'select * from function()' の型式で関数を呼び出すためです。
なお、'function()'の後ろに別のSQL文を続けて指定すると、後者のSQL文も実行されるので、注意してください。
インストール方法
dblink_plus のインストールは、標準のcontribモジュールと同様です。
ビルド
pgxs を使ってビルドできます。
$ cd dblink_plus
$ make
$ make install
デフォルトでは全てのコネクタがビルド対象となります。特定のコネクタを除外するにはコネクタフラグに0を指定します。
$ make MYSQL=0 SQLITE3=0
$ make MYSQL=0 SQLITE3=0 install
コネクタフラグには ORACLE, MYSQL, SQLITE3 があります。またコネクタとして PostgreSQL は除外することはできません。
データベースへの登録
dblink_plus を利用する場合には、対象のデータベースに対して $PGHOME/share/contrib/dblink_plus.sql を実行してください。
$ psql -d dbname -f $PGHOME/share/contrib/dblink_plus.sql
PostgreSQL 9.1以降であれば、上記の代わりに CREATE EXTENSION コマンドでも登録可能です。
postgres=# CREATE EXTENSION dblink_plus;
なお、dblink_plus を使って外部サーバへのアクセスを行うためには、ここからさらに接続先のDBサーバに応じて、外部データラッパ、外部サーバ、ユーザマッピングを作成する必要があります。
使用例を参照してください。
データベースからの登録解除
dblink_plus が提供するコネクタを使用する全ての外部データラッパを削除下の後に、アンインストールスクリプトを実行してください。
$ psql -d dbname -f $PGHOME/share/contrib/uninstall_dblink_plus.sql
PostgreSQL 9.1以降であれば、上記の代わりに DROP EXTENSION コマンドでも削除可能です。
postgres=# DROP EXTENSION dblink_plus CASCADE;
dblink_plus のために作成した外部サーバなどを削除していない場合は、DROP EXTENSION と同時に削除するように CASCADE 句を付ける必要があります。
動作環境
- PostgreSQLバージョン
- PostgreSQL 9.2, 9.3, 9.4, 9.5, 9.6, 10, 11, 12, 13, 14, 15, 16, 17, 18
- Oracleバージョン
- Oracle 11g, 12c, 18c, 19c, 21c
- OS
- RHEL 6/7/8/9/10
関連項目
dblink,
CREATE FOREIGN DATA WRAPPER,
CREATE SERVER,
CREATE USER MAPPING