【Postgres】DELETEフラグによる論理削除からTRIGGERでのレコード管理へ切り替える

今日はデータベースの論理削除の実装について。
最近はまっている「fukabori.fm」にて論理削除に関するパートがあったので視聴してみました。
なるほど。論理削除のアンチパターンについて、納得できる部分が多々あり。
盲目的になんでも論理削除で設計していたことに気づかされました。
アンチパターンの解法の1つに

「論理削除フラグ設計から、別途archiveテーブルなどを設け、DELETEしたレコードをRDBの機能'TRIGGER'を使って移す」

というものがあったので、今回それを試してみようかと。

---

実際にトリガーを作ってみる

---

今回実装する内容は以下の通り。
前提）
・productテーブルとproduct_deletedテーブルを用意
実装）
・productテーブルのレコードがDELETEされた場合、product_deletedテーブルにINSERTする
参考)
・PostgreSQL 8.1.9文書
https://www.postgresql.jp/document/8.1/html/plpgsql-trigger.html

①ストアドプロシージャの実装
process_backupというストアドプロシージャを作成します。

CREATE OR REPLACE FUNCTION process_backup()
 RETURNS trigger
 LANGUAGE plpgsql
AS 
'
BEGIN 
  INSERT INTO product_deleted SELECT OLD.*;
  RETURN OLD;
END
'

読めば大体わかりそうな感じのプロシージャですね。 OLDというのは、削除前のレコードのことです。 'OLD.*'で削除前レコードの全カラムのデータを取り出しています。

<参考>
作成したストアドプロシージャを見てみるとこのようなコードに変換されています。

CREATE OR REPLACE FUNCTION public.process_backup()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN 
    INSERT INTO product_deleted SELECT OLD.*;
    RETURN OLD;
END
$function$

a5でプロシージャをCREATEした際に、以下のようなエラーが出たため、%function%を ' にして実行しました。 (原因はわかっていません)

a5エラー

②トリガーの実装

CREATE TRIGGER backup_recode 
AFTER DELETE ON product 
 FOR EACH ROW EXECUTE PROCEDURE process_backup();

トリガーの宣言はシンプルですね。
productテーブルのDELETEのタイミングで、process_backupプロシージャが呼び出されます。

---

試してみよう

---

productテーブルはシンプルに準備。

productテーブル

1レコード消してみます

delete from product where product_name = '大根';

productテーブル　DELETE後

当然のことながら、productテーブルから消えています。
product_deletedではどうでしょう。

product_deletedテーブル

ちゃんといました。TRIGGER成功です。

---

全レコード消してみる

---

全レコード消したときの挙動も見てみます。

delete from product;

productテーブル 全レコード削除

productは空ですね。
product_deletedテーブルを見てみましょう。

product_deletedテーブル 全レコード削除後

全部移っていますね。複数レコードの挙動もばっちりです。

---

まとめ

---

今回は「fukabori.fm」で紹介されていたTRIGGERの実装を試してみました。
こうしたRDBの機能を活用することでコード実装の負担が減るかもしれませんね。

今回参考にした「fukabori.fm」、面白いので是非聞いてみてください。 https://fukabori.fm/episode/27

Qittaにも投稿しました。 qiita.com