postgresql - 如何确保物化 View 始终是最新的？

Question

我需要调用 REFRESH MATERIALIZED VIEW每次更改所涉及的表格，对吗？我很惊讶在网上没有找到太多关于这个的讨论。

我该怎么做呢？

我认为答案的上半部分是我正在寻找的:https://stackoverflow.com/a/23963969/168143

这有什么危险吗？如果更新 View 失败，调用更新、插入等的事务是否会被回滚？ (这就是我想要的……我想)

Answer 1

I'll need to invoke REFRESH MATERIALIZED VIEW on each change to the tables involved, right?

是的，PostgreSQL 本身永远不会自动调用它，您需要以某种方式进行调用。

How should I go about doing this?

实现这一目标的方法很多。在给出一些例子之前，请记住 REFRESH MATERIALIZED VIEW command 确实在 AccessExclusive 模式下阻塞了 View ，所以当它工作时，你甚至不能在 table 上做 SELECT。

不过，如果您使用的是 9.4 或更高版本，您可以为其指定 CONCURRENTLY 选项:

REFRESH MATERIALIZED VIEW CONCURRENTLY my_mv;

这将获得一个 ExclusiveLock，并且不会阻塞 SELECT 查询，但可能会产生更大的开销(取决于更改的数据量，如果更改的行数很少，那么它可能会更快)。尽管您仍然无法同时运行两个 REFRESH 命令。

手动刷新

这是一个可以考虑的选项。特别是在数据加载或批量更新的情况下(例如，在很长一段时间后才加载大量信息/数据的系统)，通常会在最后进行操作来修改或处理数据，因此您可以简单地包含一个 REFRESH 操作最后。

调度 REFRESH 操作

第一个也是广泛使用的选项是使用一些调度系统来调用刷新，例如，您可以在 cron 作业中配置类似的内容:

*/30 * * * * psql -d your_database -c "REFRESH MATERIALIZED VIEW CONCURRENTLY my_mv"

然后您的物化 View 将每 30 分钟刷新一次。

注意事项

这个选项非常好，特别是使用 CONCURRENTLY 选项，但前提是您可以接受并非始终 100% 最新的数据。请记住，即使有或没有 CONCURRENTLY ， REFRESH 命令确实需要运行整个查询，因此您必须花时间运行内部查询，然后再考虑安排 REFRESH 的时间。

用触发器刷新

另一种选择是在触发函数中调用 REFRESH MATERIALIZED VIEW，如下所示:

CREATE OR REPLACE FUNCTION tg_refresh_my_mv()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY my_mv;
    RETURN NULL;
END;
$$;

然后，在涉及 View 更改的任何表中，您执行以下操作:

CREATE TRIGGER tg_refresh_my_mv AFTER INSERT OR UPDATE OR DELETE
ON table_name
FOR EACH STATEMENT EXECUTE PROCEDURE tg_refresh_my_mv();

注意事项

它在性能和并发方面存在一些关键缺陷:

任何 INSERT/UPDATE/DELETE 操作都必须执行查询(如果您正在考虑 MV，这可能会很慢)；

即使使用 CONCURRENTLY ，一个 REFRESH 仍然会阻塞另一个，因此相关表上的任何 INSERT/UPDATE/DELETE 都将被序列化。

唯一我认为这是一个好主意的情况是，如果更改真的很少见。

使用 LISTEN/NOTIFY 刷新

前一个选项的问题在于它是同步的，并且在每个操作中都会产生很大的开销。为了改善这种情况，您可以像以前一样使用触发器，但这只会调用 NOTIFY operation :

CREATE OR REPLACE FUNCTION tg_refresh_my_mv()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NOTIFY refresh_mv, 'my_mv';
    RETURN NULL;
END;
$$;

因此，您可以构建一个保持连接并使用 LISTEN operation 来确定调用 REFRESH 的需要的应用程序。一个可以用来测试的好项目是 pgsidekick ，在这个项目中你可以使用 shell 脚本来做 LISTEN ，所以你可以安排 REFRESH 为:

pglisten --listen=refresh_mv --print0 | xargs -0 -n1 -I? psql -d your_database -c "REFRESH MATERIALIZED VIEW CONCURRENTLY ?;"

或者使用 pglater (也在 pgsidekick 内部)来确保你不会经常调用 REFRESH 。例如，您可以使用以下触发器使其变为 REFRESH ，但在 1 分钟(60 秒)内:

CREATE OR REPLACE FUNCTION tg_refresh_my_mv()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NOTIFY refresh_mv, '60 REFRESH MATERIALIZED VIEW CONCURRENLTY my_mv';
    RETURN NULL;
END;
$$;

所以它不会在 60 秒内调用 REFRESH，如果你在 60 秒内多次调用 NOTIFY， REFRESH 只会被触发一次。

注意事项

作为 cron 选项，仅当您可以使用少量陈旧数据时，此选项也很好，但这具有仅在真正需要时才调用 REFRESH 的优点，因此您的开销更少，并且数据更新得更紧密到需要的时候。

OBS:我还没有真正尝试过代码和示例，所以如果有人发现错误、拼写错误或尝试过并且有效(或无效)，请告诉我。