PostgreSQL Deep Dive: PL/pgSQLデバッガを使ってみよう

PostgreSQLのPL/pgSQLはプログラムロジックをデータベース側で実行させる非常に強力な機能です。今回は、PL/pgSQLとそのデバッガについてご紹介します。

■PostgreSQLの「PL/pgSQL」とは

PostgreSQLのPL/pgSQLは、データベース側にプログラムのロジックを埋め込むための仕組みで、通常のSQL（DDL、DML等）に制御のための構文が追加されてたような言語仕様になっています。

PL/pgSQL - SQL手続き言語
http://www.postgresql.jp/document/9.0/html/plpgsql.html

私自身、このPL/pgSQLをよく使うのですが、PL/pgSQLのひとつの難点は、ロジックが複雑になってくるとデバッグが難しい、ということでした。

そのため、伝統的なデバッグの方法、いわゆる「printfデバッグ」に頼ることになります。PL/pgSQLの場合、printfの代わりに（ログを出力する）RAISE NOTICEを使います。

エラーとメッセージ
http://www.postgresql.jp/document/9.0/html/plpgsql-errors-and-messages.html

「printfデバッグ」はどこでも使えて非常に便利なのですが、手間がかかるのが難点です。

■PL/pgSQLデバッガ by EnterpriseDB

PostgreSQLサポート企業の一社であるEnterpriseDB社は、PostgreSQLを独自に拡張したり、便利なツール類を開発して、そのコンサルティングやサポートを提供していますが、そのEnterpriseDB社の提供するツールの中に「PL/pgSQLデバッガ」があります。

このPL/pgSQLデバッガを使うことによって、単なるprintfデバッグを超えて、プロシージャ（関数）にブレークポイントを設定してステップ実行しながら変数の状態を確認したり、関数の実行中に変数の値を書き換えたりすることができるようになります。

今回は、このPL/pgSQLのセットアップ方法および使い方を簡単に紹介します。

なお、EnterpriseDB社の提供しているPostgreSQL製品は、（１）Oracle互換機能など独自の拡張を盛り込んだ有償の「Postgres Plus Advanced Server」と、（２）PostgreSQLのインストールを容易にするためにコミュニティで配布しているソースをビルドしてパッケージ化した無償の「One-click Installer」と呼ばれる製品に大別できます。

PL/pgSQLデバッガは後者の無償版にもバンドルされていますので、今回はこのOne-Click InstallerのWindows版を使います。

なお、今回試した環境は以下の通りです。

Windows7 Home Premium 64bit
PostgreSQL 9.2.3 64bit

■PostgreSQL for Windowsのインストール

まず始めに、EnterpriseDB社の配布しているOne-Click Installerを使ってPostgreSQLをインストールします。

詳しい手順は以下にまとめてありますので、こちらを参照してください。（今回はApache/PHPをインストールする必要はありません）

PostgreSQL/Apache/PHP for Windowsインストールガイド（9.2対応版）
http://www.uptime.jp/ja/resources/techdocs/2012/09/installing_postgresql_9_2_on_windows/

■PL/pgSQLデバッガのセットアップ

PostgreSQLのインストールが完了したら、続いてPL/pgSQLデバッガのセットアップを行います。

必要な作業は以下の2つです。

postgresql.confのshared_preload_librariesの設定
CREATE EXTENSIONでエクステンションのセットアップ

まず、postgresql.confの設定を行います。

C:\Program Files\PostgreSQL\9.2\lib ディレクトリ（PATHはインストール環境に合わせて適宜読み替えてください）をエクスプローラで開き、plugin_debugger.dllファイルが存在していることを確認します。これが、PL/pgSQLのデバッガ用の拡張モジュールです。

次に管理者としてメモ帳を起動し、C:\Program Files\PostgreSQL\9.2\dataディレクトリにあるpostgresql.confを開きます。shared_preload_librariesというパラメータを探し、先ほど確認したplugin_debuggerを指定します（拡張子を記載する必要はありません）。

shared_preload_libraries = 'plugin_debugger'

postgresql.confを設定したら、PostgreSQLサーバを再起動し、showコマンドでshared_preload_librariesが設定されていることを確認します。

testdb=# show shared_preload_libraries;
      shared_preload_libraries
-------------------------------------
 pg_stat_statements, plugin_debugger
(1 行)

testdb=# show shared_buffers;

ここでは別のモジュール（pg_stat_statements）もロードしているので、それも一緒に表示されています。

モジュールのロードができたら、データベースにEXTENSIONをインストールします。

pgAdminIIIのオブジェクトブラウザペインで、データベース内の「Extensions」を選択、右クリックして出てきたコンテキストメニューから「New Extension...」を選択します。

出てきたダイアログで「pldbgapi」を選択します。

オブジェクトブラウザペインで、Extensionsの中に「pldbgapi」が表示されれば、セットアップは完了です。

■プロシージャ（関数）へのブレークポイントの設定

それでは、実際にデバッガを使ってみましょう。まずは、デバッガの使い方の王道であるブレークポイントの設定を行ってみます。

今回は、以下のような「2つの値を引数として与えて、両方がNULLでなければその平均を計算し、片方がNULLであれば有効な値を返却するavg2関数」をデバッガを介して実行してみます。

CREATE OR REPLACE FUNCTION avg2(numeric, numeric)
  RETURNS numeric AS
'
DECLARE
  _a ALIAS FOR $1;
  _b ALIAS FOR $2;
BEGIN
  IF _a IS NOT NULL AND _b IS NOT NULL THEN
    RETURN (_a+_b)/2;
  ELSIF _a IS NOT NULL THEN
    RETURN _a;
  ELSIF _b IS NOT NULL THEN
    RETURN _b;
  END IF;

  RETURN null;
END
' LANGUAGE 'plpgsql';

まず、上記のavg2関数をデータベースに登録します。

avg2関数を作成したら、オブジェクトブラウザペインからavg2関数を探し、右クリックのコンテキストメニューから「デバッグ→ブレークポイントの設定」を選択します。

すると、デバッガの画面が現れ、ブレークポイントへの到達を待機している状態となります。

この状態で、通常のクエリウィンドウに切り替えて、avg2関数を実行します。

avg2関数が実行されると、ブレークポイントに到達し、処理がデバッガに切り替わります。

このようにして、プロシージャへのブレークポイントを設定することができます。

■プロシージャ（関数）のステップ実行

ブレークポイントに到達して処理がデバッガに切り替わると、そこからプロシージャの内部をステップ実行することができるようになります。

デバッガの左上に、「ステップイン」、「ステップオーバー」、「続ける（continue）」などのアイコンが並んでいますので、そちらを使ってデバッガによる実行を制御することができます。

■実行中のプロシージャ（関数）の変数の変更

デバッガに処理が渡っている間に、プロシージャ内部の変数の値をデバッガから直接書き換えることができます。

デバッガ下部にある「出力ビュー」の中の「値」というカラムを変更することによって、プロシージャ内部の変数を適宜変更できます。

上記は、avg2(3,6)として呼び出したものの、実行中に「6」を「10」に書き換えたために、結果として（3と10の平均である）「6.5」が返却されている様子です。

■まとめ

以上、非常に簡単ではありましたが、PostgreSQLのPL/pgSQLとそのデバッガについてご紹介しました。

私自身、今まではPL/pgSQLのデバッグはユニットテストとprintfデバッグに頼ることが多く、あまり真剣にデバッガの活用について考慮していませんでした。

しかし、見てきたようにPL/pgSQLデバッガはデバッガとしての必要な機能は押さえていますし、プロシージャが大きく、複雑になれば当然ながらこのようなツールが必要になってくると思います。

Windowsなど、開発環境で気軽に使えるのも大きなメリットであると思いますので、PL/pgSQLを使って開発されている方は、これを機会にぜひ試してみていただければと思います。

では、また。

PostgreSQL Deep Dive

2013年5月4日

PL/pgSQLデバッガを使ってみよう