2014年5月22日木曜日

PostgreSQLの他データベースにアクセスする仕組み(dblink編)

こんにちは、稲垣です。

PostgreSQLには他のデータベースにアクセスする仕組みが2種類用意されています。
一つはdblinkで他のPostgreSQLのデータベースにアクセスできます。もう一つはFDW(Foreign Data Wrapper)です。FDWはPostgreSQL 9.1から導入され、9.2からpostgres_fdwが利用できるようになりました。現在も様々な拡張が行われています。

今回は2回に分けて、dblink と FDW についてご案内します。

■ dblinkとは?

dblinkは別のPostgreSQLサーバーが管理するデータベースにアクセスする仕組みです。PostgreSQLのCクライアントライブラリであるlibpqを利用して、他のPostgreSQLデータベースにアクセスします。

dblinkは以下の書式を持っています。

dblink(text connname, text sql [, bool fail_on_error]) returns setof record
dblink(text connstr, text sql [, bool fail_on_error]) returns setof record
dblink(text sql [, bool fail_on_error]) returns setof record

http://www.postgresql.jp/document/9.3/html/contrib-dblink-function.html

実際に利用する場合、以下の様に利用します。

local=# SELECT * FROM dblink('dbname=remote user=postgres', 'SELECT version()') AS t(v text);

このクエリはdblinkで接続したデータベース名を返します。任意のクエリを実行できますが、record型を返さなければなりません。このため、この例ように、t(v text)と戻り値となるレコードの定義を明示しなければなりません。

何度も dblink を利用する場合、ビューを作成すると便利です。

CREATE VIEW myremote_pg_proc AS
SELECT *
FROM dblink('dbname=postgres', 'select proname, prosrc from pg_proc')
AS t1(proname name, prosrc text);

ビューを作成すると通常のテーブルのように扱えます。

SELECT * FROM myremote_pg_proc WHERE proname LIKE 'bytea%';

 

■ dblinkの制限

dblinkはリモートDBのデータをローカルデータベースのテーブルのように利用できるので、他のPostgreSQLサーバーのデータにアクセスしたい場合に便利です。しかし、制限もあります。

クエリの中で利用されると、結果全てをローカルデータベースに転送します。結果のレコード数が少ない場合は問題となりませんが、大きな場合にはクエリ実行効率が非常に悪くなります。

特にdblinkをビューとして作成した場合、クエリはローカルデータベースのテーブルを利用する場合と変わりません。ジョインを行った場合、ジョインに必要なレコードのみが選択されて送信されるのではなく、テーブル全てのデータがローカルデータベースに転送された後、ローカルでジョイン処理が行われます。この動作はほとんどの場合、この好ましくない動作でしょう。

この他にも整合性の問題があります。dblinkはリモートDBのテーブルや行を全くロックしません。この為、クエリの整合性が必要な場合は二層コミットを利用する必要があります。

 

■ dblink_openとdblink_close

クエリ内容を一度に全て送信されるとパフォーマンスに大きく影響します。必要なレコードのみを順次取得するにはカーソルを利用します。dblink_open関数はリモートデータベースでカーソルを開きます。

dblink_open(text cursorname, text sql [, bool fail_on_error]) returns text
dblink_open(text connname, text cursorname, text sql [, bool fail_on_error]) returns text

dblink_openで開いたカーソルはdblink_closeで閉じます。dblink_openとdblink_closeの間にdblink_execを使用してデータを変更した場合、エラーが発生します。 dblink_closeの前にdblink_disconnectを使用すると、トランザクションがアボートしますので変更が失われます。

dblink についてはここまでとなります。来週は「PostgreSQLの他データベースにアクセスする仕組み(FDW編)」 をご案内します。

0 件のコメント:

コメントを投稿