前提
環境はPostgreSQL 9.4
で、autocommit=off
にしている
やりたいこと
CREATE TABLE hoge (id INTEGER, head TEXT);
CREATE TABLE fuga (id INTEGER, hoge_id INTEGER, body TEXT);
CREATE MATERIALIZED VIEW piyo AS (
SELECT h.head, f.body FROM hoge h LEFT JOIN fuga f ON h.id = f.hoge_id
);
のようなテーブルとマテリアライズドビューがあったとき、hoge
やfuga
の更新時には自動的にpiyo
も更新したいと思うのが人情だと思う
そのとき、単純に
CREATE FUNCTION refresh_piyo() RETURNS TRIGGER AS $$
REFRESH MATERIALIZED VIEW CONCURRENTLY piyo;
RETURN NULL;
$$ LANGUAGE plpgsql;
CREATE TRIGGER hoge_modify_trigger AFTER INSERT OR UPDATE OR DELETE ON hoge FOR EACH SATEMENT EXECUTE PROCEDURE refresh_piyo();
CREATE TRIGGER fuga_modify_trigger AFTER INSERT OR UPDATE OR DELETE ON fuga FOR EACH SATEMENT EXECUTE PROCEDURE refresh_piyo();
などとやってしまうと、hoge
やfuga
を大量に更新する際に凄まじく遅くなってしまう
トランザクションの最後に1回だけリフレッシュするようにしたい
やったこと
一時テーブルと遅延制約トリガーを使い、処理を間引いた
CREATE FUNCTION refresh_piyo() RETURNS TRIGGER AS $$
CREATE TEMP TABLE IF NOT EXISTS debouncer (called BOOLEAN) ON COMMIT DROP;
IF (SELECT COUNT(*) > 0 FROM debouncer) THEN
RETURN NULL;
END IF;
INSERT INTO debouncer VALUES (TRUE);
REFRESH MATERIALIZED VIEW CONCURRENTLY piyo;
RETURN NULL;
$$ LANGUAGE plpgsql;
CREATE CONSTRAINT TRIGGER hoge_modify_trigger AFTER INSERT OR UPDATE OR DELETE ON hoge
DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE refresh_piyo();
CREATE CONSTRAINT TRIGGER fuga_modify_trigger AFTER INSERT OR UPDATE OR DELETE ON fuga
DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE refresh_piyo();
トリガー内でコミット時にドロップされる一時テーブルを作ることで、重たい処理が実行済みかどうかのフラグとし、一番最初の実行時にのみ処理を走らせる
複数件更新時で最初のものを更新した後だけリフレッシュが走ると困るので、リフレッシュ処理をすべての処理が終わった後まで遅延するよう、遅延制約トリガーとした
制約トリガーで更新処理を行うなど気持ち悪いことはしているが、パフォーマンス上問題になることは無くなったと思う
もうちょっと素直に実現できる方法はないだろうか・・・