履歴テーブルとRETURNING句

postgres DB 技術

3日連続Postgres小ネタ。別にひとりアドベントカレンダーをやっているわけではない。

TL;DR

  • 論理削除は本質的にリレーショナルモデルと相性が悪い
  • 論理削除の一実現手法として削除済テーブル（＝履歴テーブル）がある
  • PostgresならRETURNING+CTEでできる

履歴テーブル

リレーショナルデータベースにおいて、「データが削除」を表現する方法として、物理削除と論理削除と呼ばれる2つの方法がある。物理削除は、文字どおり「レコード自体を物理的に削除する」ことを指し、論理削除は、「実際には削除しないが、削除されたということをデータを使って表現する」こと。例えば、is_deleted という削除フラグをカラムとして持たせ、これが true になっているレコードは削除済とみなす。

長い歴史を積み重ね成熟してきたリレーショナルデータベースにおいて、いまなお論理削除が議論の種になる。なぜか。論理削除とは、言い換えると、削除されたレコードの履歴を残すことである。そもそも「リレーショナルモデル」における「リレーション」とは「集合」であり、要素間の順序関係は存在しない。一方の履歴には「新しい」「古い」という順序関係が存在する。これらの事実から、履歴をリレーションとして表現することに本質的な不整合が発生するためである。

履歴の代表的な表現方法は前述の削除フラグだが、これは一般にアンチパターンと言われている*1。

論理削除の実現方法を考える際に、「一度削除された要素が元に戻る可能性があるのか」は大きな判断要因になる。戻る可能性があるのならば、それは「履歴」ではなくその要素自体の「状態」であるため、削除フラグ（と呼ぶかは別として）でその要素の状態を表現する方法は妥当になりうる。

戻り得ないのであれば履歴として扱ってもよく，別途「履歴テーブル」を作ってそっちに保持しなおす、という設計が優位になるケースのほうが多そう。「削除されていない要素」と「削除された要素」は同一のリレーション（＝集合）に存在し得ないと考えられるためである。

履歴テーブルの実例

具体例として、「ユーザの永久退会」をリレーショナルデータベースにおける論理削除で表現する。

-- ユーザを管理するテーブル
CREATE TABLE test_user
(
    id int primary key,
    name text
);

-- 退会済ユーザを管理するテーブル（履歴テーブル）
CREATE TABLE test_user_log
(
    id int primary key,
    name text
);

-- テスト用のユーザを追加
INSERT INTO test_user
(id, name)
values
(1, 'yamada'),
(2, 'suzuki'),
(3, 'tanaka');

これで、3名のデータを持つtest_userテーブルと、空のtest_user_logテーブルができる。

  • test_user
id name
1 yamada
2 suzuki
3 tanaka
  • test_user_log
id name

SELECT + DELETE + INSERT を用いた履歴テーブルへの記録

ここで、ユーザ「suzuki」が退会したとすると、以下のSQLが実行すればよい。

-- DELETE後にINSERTするためアプリケーションで一時保持しておく
SELECT * FROM test_user
WHERE id = 2; 

DELETE FROM test_user
WHERE id = 2;

-- SELECTしておいたデータをINSERTする
INSERT INTO test_user_log
(id, name)
values (2, 'suzuki');

結果、両テーブルは以下のようになる。

  • test_user
id name
1 yamada
3 tanaka
  • test_user_log
id name
2 suzuki

Userテーブルから削除されたsuzukiユーザがUserLogテーブルに記録され、論理削除が実現されている。

ただしこの方法だとテーブル間の整合性をアプリケーションに委ねることになってしまうため、トリガを使う方法もある。やることは同じ。

RETURNINGを用いた履歴テーブルへの記録

ここからが本題。やっとPostgresの話。前置きが長すぎる。

PostgresにはRETURNINGという便利な拡張がある。

DELETE FROM test_user
WHERE id = 2
RETURNING id, name;

このようにすると、DELETEされたレコードの、RETURNINGに指定したカラムが返る*2。

id name
2 suzuki

この結果を利用して以下のようにしたくなる。しかしこれはエラーになる。

INSERT INTO test_user_log
     DELETE FROM test_user
     WHERE id = 2
     RETURNING id, name;

ERROR: syntax error at or near "DELETE"

なぜならば、文法上、INSERT INTOの VALUES に指定できるのは query、つまりSELECTのみだから*3。
実験した環境はバージョンが9.6ですが、10でもこの仕様は変わっていない模様*4。

試しにDELETEのかわりにSELECTをやってみると、普通に実行できる。

INSERT INTO test_user_log
    SELECT id, name
    FROM test_user
    WHERE id = 2;

RETURNING使えへんやん！と思いきや、Postgres 9.1以降なら、以下のようにCTEを挟むと実行できる。9.1以前ならFUNCTIONとかでがんばってください。

WITH deleted AS (DELETE FROM test_user where id = 2 RETURNING id, name)
INSERT INTO test_user_log
SELECT * FROM deleted;

無駄な抵抗で、以下のようにSELECTを挟んでみると、もちろんエラーになる。

INSERT INTO test_user_log
    SELECT * FROM (
         DELETE FROM test_user
         WHERE id = 2
         RETURNING id, name
    );

ERROR: syntax error at or near "DELETE"

なぜならば、SELECTのFROMに指定できるのはfrom_item 、つまりテーブルとかファンクションとかだから*5。

最初の例だとDELETEする前にSELECTしておき、それをINSERTする必要があるため、クエリ数が3だったのに対し、RETURNINGとCTEを使えば一発OK！なんということでしょう！

参考

*1:https://www.slideshare.net/t_wada/ronsakucasual

*2:ワイルドカード(*)も指定可能

*3:https://www.postgresql.jp/document/9.6/html/sql-insert.html

*4:https://www.postgresql.org/docs/10/static/sql-insert.html

*5:https://www.postgresql.jp/document/9.6/html/sql-select.html