규칙을 사용하여 자동으로 구체화 된 뷰를 새로 고치거나 알림

규칙을 사용하여 자동으로 구체화 된 뷰를 새로 고치거나 알림

---

거의 변경되지 않는 PostgreSQL 9.3 데이터베이스에 대한 구체화 된 뷰가 있습니다 (하루에 약 두 번). 하지만 그럴 때 즉시 데이터를 업데이트하고 싶습니다.

지금까지 제가 생각했던 것은 다음과 같습니다.

구체화 된 뷰가 mat_view테이블에서 데이터를 취득 table1하고 table2일부는 문을 조인을 사용은.

무언가가 변경 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두 번째 행도 마찬가지 입니다.

경우에 분명히, need_updateTRUE이며, 다음 구체화 된 뷰는 갱신해야합니다.

업데이트 : 구체화 된 뷰는 규칙을 지원하지 않기 때문에 (@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 : 위의 단계를 만들려고 시도했습니다.

위에서 언급 한 네 가지 사항을 처리하는 함수를 만듭니다.

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

따라서 본질적으로 작동하지만 성능이 문제가 될 수 있습니다.

누구든지 더 나은 아이디어가 있습니까? 그렇지 않다면 응용 프로그램 논리에서 어떻게 든 구현해야하거나 더 나쁜 경우에는 매분마다 실행되는 간단한 cronjob을 실행해야합니다. :-(

---

REFRESH CONCURRENTLY구체화 된 뷰에 PostgreSQL 9.4가 추가되었습니다 .

이것은 구체화 된 뷰의 비동기 업데이트 설정을 설명 할 때 찾고있는 것일 수 있습니다.

구체화 된 뷰에서 선택하는 사용자는 새로 고침이 완료 될 때까지 잘못된 데이터를 보게되지만 구체화 된 뷰를 사용하는 많은 시나리오에서 이는 허용 가능한 절충안입니다.

기본 테이블에서 변경 사항을 감시 한 다음 구체화 된 뷰를 동시에 새로 고치는 문 수준 트리거를 사용합니다.

---

You should refresh the view in triggers after insert/update/delete/truncate for each statement on table1 and table2.

create or replace function refresh_mat_view()
returns trigger language plpgsql
as $$
begin
    refresh materialized view mat_view;
    return null;
end $$;

create trigger refresh_mat_view
after insert or update or delete or truncate
on table1 for each statement 
execute procedure refresh_mat_view();

create trigger refresh_mat_view
after insert or update or delete or truncate
on table2 for each statement 
execute procedure refresh_mat_view();

In this way your materialized view is always up to date. This simple solution might be hard to accept with frequent inserts/updates and sporadic selects. In your case (seldom changes about twice a day) it ideally fits your needs.

---

To realize deferred refresh of a materialized view you need one of the following features:

• asynchronous trigger
• trigger before select
• rule on select before

Postgres has none of them, so it seems that there is no clear postgres solution.

Taking this into account I would consider a wrapper function for selects on mat_view, e.g.

CREATE OR REPLACE FUNCTION select_from_mat_view(where_clause text)
RETURNS SETOF mat_view AS $body$
BEGIN
  -- here is checking whether to refresh the mat_view
  -- then return the select:
  RETURN QUERY EXECUTE FORMAT ('SELECT * FROM mat_view %s', where_clause);
END;
$body$ LANGUAGE plpgsql;

If it is acceptable in practice depends on particulars I do not know about.

참고URL : https://stackoverflow.com/questions/23906977/refresh-a-materialized-view-automatically-using-a-rule-or-notify