[解決済み] ルールや通知を使用して、マテリアライズド・ビューを自動的にリフレッシュする

質問

PostgreSQL 9.3のデータベースで、めったに変更されない（1日に2回程度）マテリアライズド・ビューを持っています。しかし、変更があった場合、私はそのデータを迅速に更新したいと思います。

以下は、私がこれまで考えていたことです。

マテリアライズド・ビューがあり mat_view テーブルからデータを取得する table1 と table2 をjoinステートメントで指定します。

の中に何かがあるときはいつでも table1 または table2 が変更された場合、私はすでに小さな設定テーブルを更新するトリガーを持っています。 config で構成されています。

table_name | mat_view_name | need_update
-----------+---------------+------------
table1     | mat_view      | TRUE/FALSE
table2     | mat_view      | TRUE/FALSE

ですから、もし table1 が変更されると (すべてのステートメントに対して UPDATE と DELETE のトリガーがあります)、フィールド need_update が設定され、最初の行の TRUE . 同様に table2 と2行目の

明らかに、もし need_update が TRUE の場合、マテリアライズド・ビューはリフレッシュされなければなりません。

UPDATE : マテリアライズド・ビューはルールをサポートしないので（下のコメントで@pozsが言及しているように）、私ならもう一歩踏み込みます。私はダミーのビューを作成します v_mat_view という定義でダミーのビューを作成します。 SELECT * FROM mat_view という定義があります。ユーザーがこのビューでSELECTを実行すると、次のことを行うON SELECTルールを作成する必要があります。

• 以下をチェックします。 mat_view が更新されているかどうか ( SELECT 1 FROM config WHERE mat_view_name='mat_view' AND need_update=TRUE )
• をリセットします。 need_update フラグを UPDATE config SET need_update=FALSE where mat_view_name='mat_view'
• REFRESH MATERIALIZED VIEW mat_view
• で、最後に元の SELECT ステートメントを実行します。 mat_view をターゲットとします。

UPDATE2 : 上記の手順で作成してみました。

上記の4点を処理する関数を作成します。

CREATE OR REPLACE FUNCTION mat_view_selector()
RETURNS SETOF mat_view AS $body$
BEGIN
  -- here is checking whether to refresh the mat_view
  -- then return the select:
  RETURN QUERY SELECT * FROM mat_view;
END;
$body$ LANGUAGE plpgsql;

ビューを作成する v_mat_view を作成し、そこから本当に選択する関数 mat_view_selector :

CREATE TABLE v_mat_view AS SELECT * from mat_view LIMIT 1;
DELETE FROM v_mat_view;

CREATE RULE "_RETURN" AS
    ON SELECT TO v_mat_view
    DO INSTEAD 
        SELECT * FROM mat_view_selector();
    -- this also converts the empty table 'v_mat_view' into a view.

不満足な結果です。

# explain analyze select field1 from v_mat_view where field2 = 44;
QUERY PLAN
Function Scan on mat_view_selector (cost=0.25..12.75 rows=5 width=4)
(actual time=15.457..18.048 rows=1 loops=1)
Filter: (field2 = 44)
Rows Removed by Filter: 20021
Total runtime: 31.753 ms

mat_view 自体から選択するのと比較して。

# explain analyze select field1 from mat_view where field2 = 44;
QUERY PLAN
Index Scan using mat_view_field2 on mat_view (cost=0.29..8.30 rows=1 width=4)
  (actual time=0.015..0.016 rows=1 loops=1)
Index Cond: (field2 = 44)
Total runtime: 0.036 ms

というわけで、基本的には動作しますが、パフォーマンスが問題になるかもしれません。

どなたか良いアイデアをお持ちですか? そうでなければ、アプリケーション ロジックに何らかの形で実装するか、最悪、1 分かそこらごとに実行される単純な cronjob を実行する必要があります。

どのように解決するのですか?

PostgreSQL 9.4 を追加しました。 REFRESH CONCURRENTLY をマテリアライズド・ビューに追加しました。

これは、マテリアライズド・ビューの非同期更新を設定しようとしていることを説明するときに探しているものであるかもしれません。

マテリアライズド・ビューから選択するユーザーは、更新が終了するまで不正なデータを見ることになりますが、マテリアライズド・ビューを使用する多くのシナリオでは、これは許容できるトレードオフとなります。

あらゆる変更のために基礎となるテーブルを監視し、次にマテリアライズされたビューを同時にリフレッシュするステートメントレベルのトリガーを使用します。