トリガーを用いたPostgreSQLのデータ変更検知方法

はじめに
事前準備
トリガーを使用する方法
- 補足：トリガーと関数のみ消す方法
まとめ

はじめに

こんにちは！
エンジニア２年目のTKDSです！
PostgreSQLでのテーブル変更検知方法について調べました。
今回はトリガーを使用する方法について説明します。

事前準備

DBの準備(compose.yaml)

services:
  db:
    image: postgres:16.4-bullseye
    container_name: db
    environment:
      POSTGRES_USER: postgres
      POSTGRES_DB: postgres
      POSTGRES_PASSWORD: postgres
    ports:
      - "127.0.0.1:5432:5432"
    volumes:
      - db_data:/var/lib/postgresql/data  
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql

    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres -d postgres"]
      interval: 30s
      timeout: 10s
      retries: 5
      start_period: 10s

volumes:
  db_data:

初期化SQL(init.sql)

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (name, email) VALUES
('user1', 'user1@example.com'),
('user2', 'user2@example.com'),
('user3', 'user3@example.com');

トリガーを使用する方法

PostgreSQLの機能であるトリガーを使用すると、特定のイベントが発生した時に指定した関数を実行することができます。
トリガーについての詳細はドキュメントをみてください。
下記の例では、トリガーを使用して、テーブルの操作をしたときのログを記録します。

create_table.sql

CREATE TABLE audit_log (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    operation TEXT,        -- 操作の種類（INSERT、UPDATE、DELETE）
    old_data JSON,         -- 変更前のデータ（UPDATEやDELETE時）
    new_data JSON,         -- 変更後のデータ（INSERTやUPDATE時）
    query TEXT,            -- 実行されたクエリ
    changed_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP  -- 変更時刻
);

create_trigger.sql

CREATE OR REPLACE FUNCTION log_table_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        -- INSERT操作の新しいデータを記録
        INSERT INTO audit_log (operation, new_data, query)
        VALUES ('INSERT', row_to_json(NEW), current_query());
    ELSIF TG_OP = 'UPDATE' THEN
        -- UPDATE操作の変更前と変更後のデータを記録
        INSERT INTO audit_log (operation, old_data, new_data, query)
        VALUES ('UPDATE', row_to_json(OLD), row_to_json(NEW), current_query());
    ELSIF TG_OP = 'DELETE' THEN
        -- DELETE操作の削除されたデータを記録
        INSERT INTO audit_log (operation, old_data, query)
        VALUES ('DELETE', row_to_json(OLD), current_query());
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION log_table_changes();

まずCREATE OR REPLACE FUNCTION log_table_changes()に続くSQLでトリガー起動時に実行する関数を定義します。
TG_OPは実行された操作を表す特殊な変数です。
NEWはINSERT/UPDATE操作によって更新された行を保持する変数です。
OLDはUPDATE/DELETE操作によって更新される前の行を保持する変数です。
これらについては詳しくはトリガプロシージャのページに載ってます。
current_query()は、現在実行中のSQLクエリを文字列として返すPostgreSQLの関数です。
詳細はシステム情報関数に載ってます。
この関数では操作を判別し、データを記録してます。
３つが分かれているのは、OLDがUPDATE/DELETEしか対応しておらず、NEWがINSERTとUPDATEしか対応してないのと、操作種別を固定値でいれるためです。
次にトリガー作成部分について説明します。
CREATE TRIGGER audit_triggerに続く部分です。
一行目でトリガー名、２行目で実行タイミングと対象操作、３行目でトリガーの起動単位（行）と実行される関数を指定してます。
下記に実行手順と結果を示します。

準備

# コンテナ起動
docker compose up
# 記録用テーブル作成
cat ./sql/create_table.sql | docker exec -i db psql -U postgres -d postgres
# トリガー作成
cat ./sql/create_trigger.sql | docker exec -i db psql -U postgres -d postgres

試す
コンテナに接続 docker exec -it db psql -U postgres -d postgres
動作確認用SQLを実行

postgres=# INSERT INTO users (name, email) VALUES ('auditlog1', 'aaa@example.com');
INSERT 0 1
postgres=# INSERT INTO users (name, email) VALUES ('auditlog2', 'sss@example.com');
INSERT 0 1
postgres=# SELECT * FROM users;
 id |   name    |       email       |          created_at           
----+-----------+-------------------+-------------------------------
  1 | user1     | user1@example.com | 2024-09-10 14:34:03.56919+00
  2 | user2     | user2@example.com | 2024-09-10 14:34:03.56919+00
  3 | user3     | user3@example.com | 2024-09-10 14:34:03.56919+00
  4 | auditlog1 | aaa@example.com   | 2024-09-10 14:35:49.888486+00
  5 | auditlog2 | sss@example.com   | 2024-09-10 14:36:02.663281+00
(5 rows)

postgres=# UPDATE users SET email = 'update@example.com' WHERE name = 'auditlog2';
UPDATE 1
postgres=# DELETE FROM users WHERE name = 'auditlog1';
DELETE 1
postgres=# SELECT * FROM users;
 id |   name    |       email        |          created_at           
----+-----------+--------------------+-------------------------------
  1 | user1     | user1@example.com  | 2024-09-10 14:34:03.56919+00
  2 | user2     | user2@example.com  | 2024-09-10 14:34:03.56919+00
  3 | user3     | user3@example.com  | 2024-09-10 14:34:03.56919+00
  5 | auditlog2 | update@example.com | 2024-09-10 14:36:02.663281+00
(4 rows)

postgres=# SELECT * FROM audit_log ORDER BY changed_at DESC;

結果は以下の通りです。
操作が記録されているのが確認できました。

補足：トリガーと関数のみ消す方法

以下のコマンドで消せます。

DROP TRIGGER IF EXISTS audit_trigger ON users;
DROP FUNCTION IF EXISTS log_table_changes();

DROP TRIGGER IF EXISTS トリガー名 ON テーブル名;
DROP FUNCTION IF EXISTS 関数名;

簡単に投入・削除ができるのでテスト時など記録がほしいときだけいれて、終わったら消すこともできますね、便利です。

まとめ

今回はPostgreSQLのデータ変更検知方法について調べました。
トリガーを使用する方法はDBへの負荷などデメリットもあるみたいですが、簡単で導入しやすそうです。
活用例として、テスト時にステートストアのレコードの内容が期待通りに遷移してるか確認する、ORMを通して実際に実行されたSQLを記録するなどに使えそうです。
ログで検知する方法やWALを使用する方法もあるみたいなのでいずれ調べてみたいです。
ここまで読んでいただきありがとうございました！