PostgreSQL エラー処理 | PL/pgSQL 例外処理入門

はじめに

この実験 (Lab) では、PostgreSQL の手続き型言語である PL/pgSQL を使用したエラー処理技術を探求します。クラッシュする代わりに、潜在的なエラーを適切に処理できる関数を作成する方法を学びます。この実験では、情報メッセージの発生、ゼロ除算のような特定のエラーの捕捉、および後で確認するためにエラー詳細をデータベーステーブルに記録する方法を扱います。最終的には、より堅牢で信頼性の高いデータベース関数を構築する方法を理解できるようになります。

通知と警告の発行

このステップでは、PL/pgSQL 関数で RAISE ステートメントを使用する方法を学びます。RAISE ステートメントは、関数の実行を停止することなく、情報メッセージ、警告、またはデバッグ出力を提供するのに役立ちます。

まず、postgres ユーザーとして PostgreSQL の対話型ターミナル psql に接続します。この実験の以降のすべての SQL コマンドは、このターミナル内で実行されます。

sudo -u postgres psql

これで、postgres=# のような psql プロンプトが表示されます。

次に、名前を入力として受け取り、NOTICE を発生させる greet という名前の関数を作成しましょう。Notice は優先度の低いメッセージです。

psql シェルで次の SQL コマンドを実行します。

CREATE OR REPLACE FUNCTION greet(name TEXT)
RETURNS TEXT AS $$
BEGIN
  RAISE NOTICE 'Greeting function called with name: %', name;
  RETURN 'Hello, ' || name;
END;
$$ LANGUAGE plpgsql;

この関数は name 引数を受け取ります。RAISE NOTICE '...', name; の行は、通知メッセージを生成します。% はプレースホルダーであり、name 変数の値に置き換えられます。

次に、関数を呼び出して動作を確認しましょう。

SELECT greet('LabEx');

出力には、通知メッセージと関数の戻り値の両方が表示されます。

NOTICE:  Greeting function called with name: LabEx
   greet
-----------
 Hello, LabEx
(1 row)

より高い重要度レベルのメッセージ、例えば WARNING を発生させることもできます。入力名が空の場合に警告を発生させるように関数を変更しましょう。

CREATE OR REPLACE FUNCTION greet(name TEXT)
RETURNS TEXT AS $$
BEGIN
  IF name IS NULL OR name = '' THEN
    RAISE WARNING 'Input name is empty or NULL.';
    RETURN 'Hello, stranger';
  ELSE
    RAISE NOTICE 'Greeting function called with name: %', name;
    RETURN 'Hello, ' || name;
  END IF;
END;
$$ LANGUAGE plpgsql;

次に、空の文字列で関数を呼び出して警告をトリガーしましょう。

SELECT greet('');

今回は、NOTICE の代わりに WARNING メッセージが表示されます。

WARNING:  Input name is empty or NULL.
      greet
------------------
 Hello, stranger
(1 row)

関数内からさまざまな種類のメッセージを生成するために RAISE を正常に使用しました。

例外のトリガーとキャッチ

このステップでは、例外として知られる実行時エラーを処理する方法を学びます。捕捉されない例外は、関数の実行を直ちに終了させます。まず、失敗する関数を作成し、次に例外を適切に捕捉するように変更します。

整数除算を実行する simple_divide 関数を作成しましょう。この関数には潜在的な欠陥があります。ゼロ除算を試みるとクラッシュします。

CREATE OR REPLACE FUNCTION simple_divide(numerator INTEGER, denominator INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN numerator / denominator;
END;
$$ LANGUAGE plpgsql;

まず、有効な入力で関数を呼び出して、正しく動作することを確認します。

SELECT simple_divide(10, 2);

出力は除算の結果になります。

 simple_divide
---------------
             5
(1 row)

次に、ゼロ除算子で関数を呼び出して例外をトリガーします。

SELECT simple_divide(10, 0);

このコマンドは失敗し、エラーメッセージを返します。関数の実行は中止されます。

ERROR:  division by zero
CONTEXT:  PL/pgSQL function simple_divide(integer,integer) line 3 at RETURN

これは捕捉されない例外です。関数がクラッシュするのを防ぐために、EXCEPTION ブロックを使用できます。エラー処理を含む新しい関数 safe_divide を作成しましょう。

CREATE OR REPLACE FUNCTION safe_divide(numerator INTEGER, denominator INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN numerator / denominator;
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'Error: Cannot divide by zero.';
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

EXCEPTION ブロックの仕組みは次のとおりです。

BEGIN...END;: コードブロックを定義します。
EXCEPTION: このキーワードは例外処理セクションを開始します。
WHEN division_by_zero THEN: division_by_zero エラーが発生した場合にのみ、それに続くコードが実行されることを指定します。
ハンドラー内のコードは、通知を発生させ、クラッシュする代わりに NULL を返します。

次に、新しい safe_divide 関数をゼロ除算子で呼び出します。

SELECT safe_divide(10, 0);

今回は、関数はクラッシュしません。例外を捕捉し、カスタム通知を表示し、NULL を返します。

NOTICE:  Error: Cannot divide by zero.
 safe_divide
-------------

(1 row)

特定の例外を捕捉し、関数の出力を制御することに成功しました。

テーブルへのエラーログ記録

例外を捕捉することは良いことですが、監査やデバッグのためには、エラーをテーブルのような永続的な場所にログに記録する方が良い場合が多くあります。このステップでは、safe_divide 関数を変更して、実験のセットアップ中に作成された error_log テーブルにエラーの詳細を書き込みます。

まず、psql の \d コマンドを使用して error_log テーブルの構造を確認しましょう。

\d error_log

テーブルの列が表示され、自動インクリメントされる id、timestamp、エラーの message、および function_name が含まれます。

                                              Table "public.error_log"
    Column     |            Type             | Collation | Nullable |                     Default
---------------+-----------------------------+-----------+----------+----------------------------------------------------
 id            | integer                     |           | not null | nextval('error_log_id_seq'::regclass)
 timestamp     | timestamp without time zone |           |          | (now() AT TIME ZONE 'utc'::text)
 message       | text                        |           |          |
 function_name | text                        |           |          |
Indexes:
    "error_log_pkey" PRIMARY KEY, btree (id)

次に、safe_divide 関数を変更しましょう。EXCEPTION ブロック内で、返す前にエラーをログに記録する INSERT ステートメントを追加します。

CREATE OR REPLACE FUNCTION safe_divide(numerator INTEGER, denominator INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN numerator / denominator;
EXCEPTION
  WHEN division_by_zero THEN
    INSERT INTO error_log (message, function_name)
    VALUES ('Division by zero occurred!', 'safe_divide');

    RAISE NOTICE 'Error: Cannot divide by zero. Details logged.';
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

再度関数を呼び出して、エラーと新しいログ記録ロジックをトリガーしましょう。

SELECT safe_divide(10, 0);

関数は以前と同様に NULL を返しますが、データベースへの書き込みも実行されました。これを検証するために、error_log テーブルをクエリして新しいエントリを確認します。

SELECT function_name, message FROM error_log;

出力にはログに記録されたエラーが表示されるはずです。

 function_name |          message
---------------+----------------------------
 safe_divide   | Division by zero occurred!
(1 row)

これにより、エラー処理ロジックが、後で分析するためにエラー情報を永続化できるようになっていることが確認できました。

OTHERS による一般的な例外の処理

これまでは、division_by_zero という非常に具体的なエラーを処理してきました。では、予期しないその他のエラーについてはどうでしょうか？PL/pgSQL は、より具体的な WHEN 句で捕捉されなかったあらゆるエラーを捕捉するための汎用的な OTHERS 例外ハンドラを提供します。

このステップでは、あらゆる例外を処理するように safe_divide 関数を強化します。また、SQLSTATE（SQL エラーコード）と SQLERRM（SQL エラーメッセージ）という 2 つの特殊な変数を使用して、より詳細な情報をログに記録します。

safe_divide 関数を最後に一度変更しましょう。

CREATE OR REPLACE FUNCTION safe_divide(numerator INTEGER, denominator INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN numerator / denominator;
EXCEPTION
  WHEN division_by_zero THEN
    INSERT INTO error_log (message, function_name)
    VALUES ('Division by zero occurred!', 'safe_divide');

    RAISE NOTICE 'Error: Cannot divide by zero. Details logged.';
    RETURN NULL;

  WHEN OTHERS THEN
    INSERT INTO error_log (message, function_name)
    VALUES ('An unexpected error occurred: ' || SQLSTATE || ' - ' || SQLERRM, 'safe_divide');

    RAISE NOTICE 'An unexpected error occurred. Details logged.';
    RETURN -1;
END;
$$ LANGUAGE plpgsql;

新しい WHEN OTHERS ブロックは、その他のあらゆるエラーを捕捉します。SQLSTATE および SQLERRM 変数を使用して詳細なメッセージを構築し、ログに挿入します。その後、汎用的なエラーが発生したことを示すために -1 を返します。

現在の関数は division_by_zero エラーしかトリガーできませんが、この構造により、将来の変更や予期しない入力に対してはるかに堅牢になります。たとえば、入力型がオーバーフローする可能性のある型に変更された場合、OTHERS ブロックは numeric_value_out_of_range エラーを捕捉します。

期待どおりに動作することを確認するために、もう一度 division_by_zero パスをテストしましょう。まず、クリーンなテストのためにログテーブルをクリアします。

TRUNCATE error_log;

次に、関数を呼び出します。

SELECT safe_divide(10, 0);

最後に、ログを確認します。最初の WHEN ブロックが正しく選択されたことを確認する、特定の「Division by zero」メッセージが含まれているはずです。

SELECT message FROM error_log;

          message
----------------------------
 Division by zero occurred!
(1 row)

これで、特定された予期されるエラーと一般的な予期しないエラーの両方を処理できる、回復力のある関数が構築されました。

まとめ

この実験では、PostgreSQL の PL/pgSQL におけるエラー処理の基本を学びました。まず、デバッグや情報提供のために RAISE ステートメントを使用して NOTICE および WARNING メッセージを発行しました。次に、失敗する可能性のある関数を作成し、BEGIN...EXCEPTION...END ブロックを実装して特定の division_by_zero エラーを捕捉することで、実行時例外の処理に進みました。これを基盤として、エラー詳細をデータベーステーブルに記録する関数を強化し、監査のための永続的な記録を提供しました。最後に、汎用的な WHEN OTHERS ハンドラを追加して予期しないエラーを捕捉することで、関数をより堅牢にし、さまざまな条件下で関数が適切に失敗することを保証しました。