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

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

我该怎么做呢？

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

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

最佳答案

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

实现这一目标的方法很多。在举一些例子之前，请记住 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:我还没有真正尝试过代码和示例，所以如果有人发现错误、打字错误或尝试并成功(或失败)，请告诉我。

关于postgresql - 如何确保物化 View 始终是最新的？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/29437650/