Programming w/ PostgreSQL ～ テーブル・レコード更新日時の記録

PostgreSQL のテーブルにレコード更新された日時を記録する実験

仕掛けづくりは下記の通り。

-- [初期設定] スキーマ作成
CREATE SCHEMA sandbox;

-- [初期設定] 観測対象テーブルのレコード最終更新日時を記録するテーブルの定義
CREATE TABLE IF NOT EXISTS sandbox.table_last_modified (
  table_name       TEXT      PRIMARY KEY,
  last_modified_at TIMESTAMP
);

-- [初期設定] レコード更新時に発動する関数の定義
CREATE OR REPLACE FUNCTION sandbox.update_table_last_modified() RETURNS trigger AS $$
BEGIN
  -- レコード更新されたテーブル名と現在時刻を挿入・更新する
  INSERT INTO sandbox.table_last_modified (table_name, last_modified_at) VALUES (TG_TABLE_NAME, CURRENT_TIMESTAMP)
  ON CONFLICT (table_name) DO UPDATE SET last_modified_at = CURRENT_TIMESTAMP;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- [テーブル作成都度] 観測対象テーブルの定義
CREATE TABLE IF NOT EXISTS sandbox.table_observable (
  id   INTEGER PRIMARY KEY,
  text TEXT
);

-- [テーブル作成都度] 観測対象テーブルに対するトリガーの設定
CREATE TRIGGER sandbox.test_last_modified
AFTER INSERT OR UPDATE OR DELETE ON sandbox.table_observable 
FOR EACH STATEMENT EXECUTE PROCEDURE update_table_last_modified();

実行結果

観測対象テーブルにも更新記録テーブルにも、初めは何も入っていない。

SELECT * FROM sandbox.table_observable;
--|id|text|
--|  |    |

SELECT * FROM sandbox.table_last_modified;
-- |table_name|last_modified_at|
-- |          |                |

観測対象テーブルに１行挿入してみる。

INSERT INTO sandbox.table_observable VALUES (1, 'test');

観測対象テーブルには１行挿入され、更新記録テーブルにもレコード更新の記録が残る。

SELECT * FROM sandbox.table_observable;
--|id|text|
--| 1|test|

SELECT * FROM sandbox.table_last_modified;
-- |table_name      |last_modified_at          |
-- |table_observable|2023-08-11-22:22:22.222222|

利用例

レコード最終更新日時も同時に取得したいとき、このように使ったらよいのではないか。
ただし、最終更新日時カラムの情報は各レコードごとの更新情報ではなく、テーブルのそれである。OLAP ではデータキューブの一括更新時刻を知りたいことがある *1 。

WITH qry AS (SELECT last_modified_at FROM sandbox.table_last_modified WHERE table_name = 'table_observable')
SELECT tbl.*, (SELECT qry.last_modified_at FROM qry) FROM sandbox.table_observable AS tbl;

-- |id|text|last_modified_at          |
-- | 1|test|2023-08-11-22:22:22.222222|

*1:レコードごとの最終更新日時を保持・参照したいときは観測対象テーブル側にそのようなカラムを用意すればよい。こちらは OLTP でよくやる通常の方法。