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

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

はじめに

こんにちは！
エンジニア２年目の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,
old_data JSON,
new_data JSON,
query TEXT,
changed_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

• create_trigger.sql

CREATE OR REPLACE FUNCTION log_table_changes() RETURNS TRIGGER AS BEGINIFTGOP=′INSERT′THENINSERTINTOauditlog(operation,newdata,query)VALUES(′INSERT′,rowtojson(NEW),currentquery());ELSIFTGOP=′UPDATE′THENINSERTINTOauditlog(operation,olddata,newdata,query)VALUES(′UPDATE′,rowtojson(OLD),rowtojson(NEW),currentquery());ELSIFTGOP=′DELETE′THENINSERTINTOauditlog(operation,olddata,query)VALUES(′DELETE′,rowtojson(OLD),currentquery());ENDIF;RETURNNEW;END;BEGIN IF TG_OP = 'INSERT' THEN

    INSERT INTO audit_log (operation, new_data, query)
    VALUES ('INSERT', row_to_json(NEW), current_query());
ELSIF TG_OP = 'UPDATE' THEN
    
    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
    
    INSERT INTO audit_log (operation, old_data, query)
    VALUES ('DELETE', row_to_json(OLD), current_query());
END IF;
RETURN NEW;

END;BEGINIFTGO​P=′INSERT′THENINSERTINTOauditl​og(operation,newd​ata,query)VALUES(′INSERT′,rowt​oj​son(NEW),currentq​uery());ELSIFTGO​P=′UPDATE′THENINSERTINTOauditl​og(operation,oldd​ata,newd​ata,query)VALUES(′UPDATE′,rowt​oj​son(OLD),rowt​oj​son(NEW),currentq​uery());ELSIFTGO​P=′DELETE′THENINSERTINTOauditl​og(operation,oldd​ata,query)VALUES(′DELETE′,rowt​oj​son(OLD),currentq​uery());ENDIF;RETURNNEW;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を使用する方法もあるみたいなのでいずれ調べてみたいです。
ここまで読んでいただきありがとうございました！