PostgreSQL イベントトリガーの設定

はじめに

この実験 (Lab) では、PostgreSQL でイベントトリガーを設定する方法を学びます。イベントトリガーは、データベースオブジェクトの作成、変更、削除などのデータ定義言語 (DDL) イベントをキャプチャします。

これらのイベントを記録するための専用のログテーブルと特別な関数を作成します。次に、CREATE TABLE コマンドの後に発火するイベントトリガーを作成し、新しいテーブルの詳細を記録します。この実践的な演習では、必要なコンポーネントの作成、トリガー機能のテスト、および後処理としてのデータベースオブジェクトのクリーンアップについて説明します。

ログテーブルとトリガー関数の作成

このステップでは、DDL イベントのログ記録に必要なデータベースオブジェクトを作成します。ログを格納するテーブルと、トリガーが実行する関数です。

まず、ターミナルを開き、psql インタラクティブシェルを使用して PostgreSQL データベースに接続します。以降の SQL 操作はこのシェル内で行います。

sudo -u postgres psql

これで、postgres=# のような PostgreSQL プロンプトが表示されるはずです。

次に、各 DDL イベントの詳細を格納する event_log という名前のテーブルを作成します。このテーブルには、イベント時刻、タイプ、スキーマ、オブジェクト名、および実行されたコマンドの列が含まれます。

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

CREATE TABLE event_log (
    event_time timestamp with time zone,
    event_type text,
    schema_name text,
    object_name text,
    command_tag text
);

正常に実行されると、PostgreSQL は次のように応答します。

CREATE TABLE

次に、DDL イベントによってトリガーされる関数を作成します。この関数 log_ddl_event は、event_log テーブルに新しいレコードを挿入します。

CREATE OR REPLACE FUNCTION log_ddl_event()
RETURNS event_trigger
AS $$
BEGIN
    INSERT INTO event_log (event_time, event_type, schema_name, object_name, command_tag)
    SELECT now(), tg_event, ddl_command.schema_name, ddl_command.objid::regclass, tg_tag
    FROM pg_event_trigger_ddl_commands() AS ddl_command;
END;
$$ LANGUAGE plpgsql;

この関数について詳しく見てみましょう。

• RETURNS event_trigger: これは、イベントトリガーで使用するために設計された特別な種類の関数であることを指定します。
• AS $$ ... $$ LANGUAGE plpgsql: plpgsql プロシージャル言語を使用して関数本体を定義します。
• INSERT INTO event_log ...: これはコアアクションであり、ログテーブルに新しい行を挿入します。
• now(): 現在のタイムスタンプを返す標準関数です。
• tg_event, tg_tag: トリガー関数内で利用可能な特別な変数で、ddl_command_end や CREATE TABLE のようなイベントに関するコンテキストを提供します。
• pg_event_trigger_ddl_commands(): 実行された DDL コマンドを記述する行のセットを返す関数です。これを使用して、作成中のオブジェクトの schema_name と objid (オブジェクト識別子) を取得します。

コマンドを実行すると、次の確認が表示されます。

CREATE FUNCTION

これで、イベントトリガーの基盤となるコンポーネントを正常に設定できました。

DDL イベントトリガーの作成

ログテーブルと関数が準備できたので、イベントトリガー自体を作成します。このトリガーは特定の DDL コマンドを監視し、それらが発生したときにログ記録関数を実行します。

同じ psql シェルで、log_table_creation という名前のイベントトリガーを作成するために次のコマンドを実行します。

CREATE EVENT TRIGGER log_table_creation
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION log_ddl_event();

このステートメントのコンポーネントを見てみましょう。

• CREATE EVENT TRIGGER log_table_creation: これは新しいイベントトリガーの名前です。
• ON ddl_command_end: これはトリガーのタイミングを指定します。DDL コマンドの実行が完了した後に発火します。
• WHEN TAG IN ('CREATE TABLE'): これはフィルター条件です。コマンドタグが CREATE TABLE と一致する場合にのみトリガーがアクティブになります。ここには ALTER TABLE や DROP TABLE のような他のタグを追加することもできます。
• EXECUTE FUNCTION log_ddl_event(): これは、トリガーの条件が満たされたときに実行する関数を指定します。

コマンドを実行すると、PostgreSQL はその作成を確認します。

CREATE EVENT TRIGGER

これでイベントトリガーがアクティブになり、新しいテーブルが作成されるたびにログが記録されるようになります。

イベントトリガーのテスト

いよいよ、イベントトリガーが期待どおりに機能するかどうかをテストします。新しいテーブルを作成し、次に event_log を確認して、作成イベントが記録されたかどうかを確認します。

まず、employees という名前の簡単なテーブルを作成します。

CREATE TABLE employees (
    id serial PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE コマンドは通常どおり実行され、標準の確認メッセージが表示されます。

CREATE TABLE

このアクションにより、バックグラウンドで log_table_creation トリガーが発火したはずです。これを検証するために、event_log テーブルをクエリしてその内容を確認します。

SELECT schema_name, object_name, command_tag FROM event_log;

出力には複数のレコードが表示されます。これは、serial PRIMARY KEY を持つテーブルを作成すると、シーケンスと主キー制約も暗黙的に作成されるためです。イベントトリガーは関連するすべての DDL コマンドをキャプチャし、ログに複数のエントリが作成されます。

 schema_name |   object_name    | command_tag
-------------+------------------+--------------
 public      | employees_id_seq | CREATE TABLE
 public      | employees        | CREATE TABLE
 public      | employees_pkey   | CREATE TABLE
 public      | employees_id_seq | CREATE TABLE
(4 rows)

この結果は、イベントトリガーが正しく機能しており、テーブル作成とその依存オブジェクトのログを記録していることを確認します。

データベースオブジェクトのクリーンアップ

不要になったデータベースオブジェクトを削除することは、良い習慣です。この最後のステップでは、この実験中に作成したイベントトリガー、テーブル、および関数を削除します。

まず、イベントトリガーを削除します。

DROP EVENT TRIGGER log_table_creation;

確認メッセージが表示されます。

DROP EVENT TRIGGER

次に、作成した employees および event_log の 2 つのテーブルを削除します。

DROP TABLE employees;
DROP TABLE event_log;

PostgreSQL は各アクションを確認します。

DROP TABLE
DROP TABLE

最後に、トリガー関数を削除します。

DROP FUNCTION log_ddl_event();

最終確認が表示されます。

DROP FUNCTION

これで、この実験で作成されたすべてのオブジェクトを正常にクリーンアップしました。psql シェルを終了して通常のターミナルプロンプトに戻るには、\q と入力して Enter キーを押します。

\q

まとめ

この実験では、PostgreSQL でイベントトリガーを実装して DDL アクティビティを監視する方法を学びました。イベントデータを格納するためのログテーブルと、それを設定するための PL/pgSQL 関数を作成しました。次に、CREATE TABLE イベントを specifically にキャプチャし、それをログ関数にリンクするイベントトリガーを定義しました。トリガーをテストし、ログエントリを検証することで、これらのコンポーネントがどのように連携して機能するかについての実践的な経験を得ました。最後に、作成したトリガー、テーブル、関数をクリーンアップすることで、適切なデータベース管理を実践しました。