Transition Tableを使ってみる

PostgreSQL Advent Calendar 2017の22日目の記事です。

Transition Tableとは

Transition TablerはPostgreSQL 10から導入された新機能で、テーブル内容の変更差分を内部的なテーブルとして扱うことができる機能です。PostgreSQLのTransition Tableでは、トリガと連携しトリガ関数からテーブル内容の変更差分を利用することができます。

タイムトラベルしてみる

Transition Tableを使って、テーブルの変更履歴を保存し任意の時間のテーブルの状態を取り出せるようにしてみます。Temporal Table的なものです。

2018/1/17 追記
Transition Tableは「Transition Tableとは」に記載したとおり、テーブルの変更差分を内部的なテーブルとして扱う機能であり、タイムトラベルの実現を目的とした機能ではありません。（誤解を与えてしまうかもという指摘を頂いたので補足です）
以下では、Transition Tableの一つの活用例として、タイムトラベル機能の作成を紹介しています。

1. 下準備

対象となるテーブル、履歴保存用のテーブルの作成します。

CREATE TABLE public.test (id int, c int);
CREATE SCHEMA timetravel;
CREATE TABLE timetravel.test (like public.test, b timestamp, e timestamp);

2. トリガ関数の作成

トリガ関数を作成して、public.testテーブルの変更をtimetravel.testテーブルに記録するようにします。

CREATE OR REPLACE FUNCTION timetravel() RETURNS trigger as $$
BEGIN
    -- INSERTのときは、新しいレコードを作成し、b(begin)に時刻を入れる
    IF (TG_OP = 'INSERT') THEN
        EXECUTE 'INSERT INTO timetravel.' || TG_TABLE_NAME || ' SELECT *, current_timestamp, NULL FROM new_table';
    -- DELETEのときは、マッチする行のe(end)に時刻を入れる
    ELSIF (TG_OP = 'DELETE') THEN
        EXECUTE 'UPDATE timetravel.' || TG_TABLE_NAME || ' SET e = current_timestamp FROM old_table as old WHERE ' || TG_TABLE_NAME || '.id = old.id and e is NULL';
    -- UPDATEのときは、DELETE、INSERTするのを同じ
    ELSIF (TG_OP = 'UPDATE') THEN
        EXECUTE 'UPDATE timetravel.' || TG_TABLE_NAME || ' SET e = current_timestamp FROM old_table as old WHERE ' || TG_TABLE_NAME || '.id = old.id and e is NULL';
        EXECUTE 'INSERT INTO timetravel.' || TG_TABLE_NAME || ' SELECT *, current_timestamp, NULL FROM new_table';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER tt_trigger_ins
AFTER INSERT ON test
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE PROCEDURE timetravel();

CREATE TRIGGER tt_trigger_del
AFTER DELETE ON test
REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT EXECUTE PROCEDURE timetravel();

CREATE TRIGGER tt_trigger_upd
AFTER UPDATE ON test
REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE PROCEDURE timetravel();

ポイントは、トリガ関数内に出てくるold_tableとnew_tableです。この2つの名前は、CREATE TRIGGER内に出てくるREFERENCING OLD TABLE asとREFERENCING NEW TABLE asに対応しており、変更情報が入ったテーブル’のようなもの）の名前を意味します。

PostgreSQL 9.6までは各タプルの変更情報をトリガ関数内で参照することができましたが、Transition Tableを使うとそれらをまとめてテーブルとして扱うことができます。それにより、（FOR EACH ROWで行毎にトリガを実行しているのに比べて）トリガの性能が上がったり、各タプル毎の処理では難しかった事（変更されたタプルのの平均を算出するなど）ができるようになります。

また、INSERT/UPDATE/DELETEで参照できるテーブルが異なる(例えばINSERTではOLD TABLEはない)ので、CREATE TRIGGERはそれぞれに対して作成する必要があると思います。これは、これまでの行レベルの変更城を元にトリガをつくっていた時と違う点です。

3.試してみる

まずはテーブルにいくつかタプルを入れてみます。

INSERT INTO test SELECT i, i * 100 FROM generate_series(1,5) i;
SELECT * FROM test;
 id |  c
----+------
  1 |  100
  2 |  200
  3 |  300
  4 |  400
  5 |  500
(5 rows)

-- timetravel.testテーブルには時刻情報が合わせて入っていることがわかる
SELECT * FROM timetravel.test;
 id |  c  |             b             | e 
----+-----+---------------------------+---
  1 | 100 | 2017-12-22 05:33:13.63322 | 
  2 | 200 | 2017-12-22 05:33:13.63322 | 
  3 | 300 | 2017-12-22 05:33:13.63322 | 
  4 | 400 | 2017-12-22 05:33:13.63322 | 
  5 | 500 | 2017-12-22 05:33:13.63322 | 
(5 rows)

次にいくつかタプルを更新・削除して、テーブルの状態を見てみます。

UPDATE test SET c = c + 1000 WHERE id = 3;
-- 元テーブルからはデータが削除された
SELECT * FROM test;
 id |  c
----+------
  1 |  100
  4 |  400
  5 |  500
  3 | 1300
(4 rows)

-- timetravel.testテーブルには変更履歴が入っていることがわかる
SELECT * FROM timetravel.test;
 id |  c   |             b              |             e              
----+------+----------------------------+----------------------------
  1 |  100 | 2017-12-22 05:33:13.63322  | 
  2 |  200 | 2017-12-22 05:33:13.63322  | 
  4 |  400 | 2017-12-22 05:33:13.63322  | 
  5 |  500 | 2017-12-22 05:33:13.63322  | 
  3 |  300 | 2017-12-22 05:33:13.63322  | 2017-12-22 05:39:03.072119
  3 | 1300 | 2017-12-22 05:39:03.072119 | 
(6 rows)

timetravel.testテーブルから特定時間帯の状態を取ってきたい場合は、以下のようにします。

-- 5:30 - 5:35 のテーブルの状態を取得
SELECT * 
FROM timetravel.test 
WHERE
    b BETWEEN '2017-12-22 05:30:00' AND '2017-12-22 05:35:00'
    AND (e IS NULL OR e > '2017-12-22 05:35:00') ;

 id |  c  |             b             |             e              
----+-----+---------------------------+----------------------------
  1 | 100 | 2017-12-22 05:33:13.63322 | 
  2 | 200 | 2017-12-22 05:33:13.63322 | 
  4 | 400 | 2017-12-22 05:33:13.63322 | 
  5 | 500 | 2017-12-22 05:33:13.63322 | 
  3 | 300 | 2017-12-22 05:33:13.63322 | 2017-12-22 05:39:03.072119
(5 rows)

-- 最新の状態を取得
SELECT * FROM timetravel.test WHERE e IS NULL;
 id |  c   |             b              | e 
----+------+----------------------------+---
  1 |  100 | 2017-12-22 05:33:13.63322  | 
  2 |  200 | 2017-12-22 05:33:13.63322  | 
  4 |  400 | 2017-12-22 05:33:13.63322  | 
  5 |  500 | 2017-12-22 05:33:13.63322  | 
  3 | 1300 | 2017-12-22 05:39:03.072119 | 
(5 rows)

おわりに

PostgreSQL 10の新機能であるTransition Tableの使い方について紹介しました。Transition Tableはそれ自体の機能としても使いどころがありますし、おそらくマテリアライズド・ビューの差分更新のインフラにもなる機能なので、将来も楽しみです！