У меня есть материализованное представление о базе данных 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_update
имеет значение ИСТИНА, то материализованное представление должно быть обновлено.
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
) - reset флаг
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, которая запускается каждую минуту или около того.: - (