dblinkで他のデータベースのテーブルを更新する

PostgreSQLでは、トリガを使うと、テーブルが更新されたら、別のテーブルを自動的に更新する事が出来ます。
では、データベースが異なる場合はどうするか？
トリガの中でdblinkを実行する事で対応可能です。

環境

PostgreSQL 8.3
CentOS 5.4

参考にしたページ

インストール

インストール後、データベースへの登録SQLを実行する事で利用可能になります。

CentOSの場合は、Yumでインストール出来ます。

sudo yum install postgresql-contrib

postgresにsuしてから登録用SQLを実行します。

$ su -
# su postgres
bash-3.2$ psql pro9_production -f /usr/share/pgsql/contrib/dblink.sql

dblinkの実行方法

基本は

コネクションを開いて（dblink_connect | dblink_connect_u）
SQLを実行して（dblink | dblink_exec）
コネクションを閉じる（dblink_disconnect）

です。

dblink関数に引き渡すSQLは文字列として渡す必要がある所が、この関数の面倒なところです。
何が面倒かって、シングルコーテーション（'）を適切にエスケープしなきゃならない所です。

トリガーでdblinkを実行する例

CREATE OR REPLACE FUNCTION トリガー関数名()
  RETURNS trigger AS
$BODY$DECLARE
  remote_query text;
  local_query text;
BEGIN
  /* create connection */
  perform dblink_connect('dbname=<データベース名> user=<ユーザー名> password=<パスワード>');

  /* create query text */
  IF (TG_OP = 'UPDATE') THEN
    remote_query := 'UPDATE daily_machine_datas SET ('
      || 'company_id,center_id,model_id,machine_id,rec_date,rec_time,'
      || 'machine_no,created_at,updated_at) = ('
      || NEW.company_id || ','
      || NEW.center_id || ','
      || NEW.model_id || ','
      || NEW.machine_id || ','
      || '''''' || NEW.rec_date || ''''','
      || '''''' || NEW.rec_time || ''''','
      || NEW.machine_no || ','
      || '''''' || NEW.created_at || ''''','
      || '''''' || NEW.updated_at || ''''')'
      || ' WHERE id = ' || NEW.id;
  ELSIF (TG_OP = 'INSERT') THEN
    remote_query := 'INSERT INTO daily_machine_datas(' 
      || 'id,company_id,center_id,model_id,machine_id,rec_date,rec_time,'
      || 'machine_no,created_at,updated_at) VALUES ('
      || NEW.id || ','
      || NEW.company_id || ','
      || NEW.center_id || ','
      || NEW.model_id || ','
      || NEW.machine_id || ','
      || '''''' || NEW.rec_date || ''''','
      || '''''' || NEW.rec_time || ''''','
      || NEW.machine_no || ','
      || '''''' || NEW.created_at || ''''','
      || '''''' || NEW.updated_at || ''''')';
  END IF;
  
  local_query := 'select dblink_exec(''' || remote_query || ''')';

  /* execute query */
  EXECUTE local_query;
  
  /* delete connection */
  perform dblink_disconnect();

  RETURN NULL;

END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

ポイント

日付、時刻はシングルコーテーションで囲む必要があり、エスケープする必要がある

dblink関数に渡すSQL文は文字列なので、シングルコーテーションはエスケープする必要があります。
日付、時刻はシングルコーテーションで囲む必要があるため、'が6回も連続する事になります。

リモートのテーブルをSELECTした結果を、ローカルに引っ張ってくる。逆は出来ない

取ってくる事は出来ても、送り出す事が出来ません。
どうしてもリモートにデータを送りたい場合は、回りくどいですが、リモート側にdblinkを導入して、
ローカル側のテーブルを読み取る関数を用意し、それをローカルからdblinkで実行すればOKです。

エラーへの対処

実行例は、開発マシンでは動くのに、実行マシンでは動きませんでした。
ログを見てみると認証エラーが発生していました。

ERROR:  password is required
DETAIL:  Non-superuser cannot connect if the server does not request a password.
HINT:  Target server's authentication method must be changed.

pg_hba.confでローカル接続をtrustにしているせいで、パスワード無しで接続しようとしてはねられているようです。

今回は、稼働中のシステムの認証方法を変更するのは気乗りがしなかったので、dblink_connectを使わず、dblink_connect_uを使う事で、対処しました。

dblink_connect_uは、初期値ではpotgres以外は実行できないようになっているので、potgresでログインし、実行権限をユーザーに付与する必要があります。

GRANT EXECUTE ON FUNCTION dblink_connect_u(text) TO <ユーザー名>;

コードは以下のように書き直します。

  /* create connection */
  perform dblink_connect_u('dbname=<接続先データベース名>');

以上で、無事に動くようになりました。