株式会社ラクスの技術ブログ

全941件

2024/09/12

はじめに事前準備トリガーを使用する方法補足：トリガーと関数のみ消す方法まとめはじめにこんにちは！エンジニア２年目の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を使用する方法もあるみたいなのでいずれ調べてみたいです。ここまで読んでいただきありがとうございました！

株式会社ラクス の技術ブログ

コンテンツ

株式会社ラクスの技術ブログ