刷新物化视图：并发，交易行为 [英] Refresh materialized views: Concurrency, transactional behaviour

问题描述

刷新材料的官方PostgreSQL 9.3文档VIEW 尚未详细描述。

The official PostgreSQL 9.3 documentation on REFRESH MATERIALIZED VIEW does not yet describe it in detail.

此博客：

物化视图具有严重的局限性，包括在刷新时使用排他锁。基本上，这会阻止在通过其父级关系中的新数据刷新实体化视图时读取该实例化视图的任何尝试

materialized views in Postgres 9.3 have a severe limitation consisting in using an exclusive lock when refreshing it. This basically blocks any attempts to read a materialized view while it is being refreshed with new data from its parent relations

发布中的另一条报价在邮件列表：

Another quote from a posting in the mailing list:

如果我正确理解了内容，即使视图已经
包含数据，刷新材料视图也会使用AccessExclusiveLock锁定
物化视图。

if I understand things correctly REFRESH MATERIALIZED VIEW locks the materialized view with an AccessExclusiveLock even if the view already contains data.

我的问题：以下顺序是否正确：

My question: Is the following sequence correct:

1. 查询正在访问实例化视图


2. 作业执行了刷新材料视图。它将锁定视图，并等待直到所有使用matview的运行查询都已完成


3. matview开始刷新；如果在matview上有一个索引，它会在同一时间进行更新（因此一次事务中将进行完全刷新）


4. 使用matview的查询正在等待，直到刷新完成为止。完成。如果花费的时间太长，则会出现等待锁超时错误之类的东西。


5. 刷新完成后，锁将被删除


6. 查询哪些内容一直在等待matview继续

1. A query is accessing a materialized view
2. A job executes REFRESH MATERIALIZED VIEW. It puts a lock on the view, and waits until all running queries using the matview have been completed
3. The matview is starting the refresh; if there is an index on the matview, it is updated at the same time (so the complete refresh is taking place in one transaction)
4. Queries using the matview are waiting until the refresh has been completed. If this takes too long, there is something like a "waiting for lock timeout error".
5. Refresh completes, the lock is removed
6. Queries which have been waiting for the matview continue

推荐答案

从Postgres 9.4版本开始，这是'完全是这样。现在，您可以使用同时刷新材料视图命令同时刷新实例化视图。从功能上讲，这会刷新视图，但没有读取锁定。就计算而言，这是一个更昂贵的操作，但是如果锁定对您来说是个问题（就像对我而言，导致我走这条路），那么这并不是一个坏方法。

As of the release of Postgres 9.4 this isn't entirely the case. You can now refresh a materialized view concurrently using the REFRESH MATERIALIZED VIEW CONCURRENTLY command. Functionally this refreshes the view, but does so without the read lock. It is a more expensive operation in terms of computation, but if the lock is a problem for you (as it was for me, which lead me down this path), then this isn't a bad way to go.

以下是发行说明中的​​更多信息： https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.4#REFRESH_MATERIALIZED_VIEW_CONCURRENTLY

Here's some more info from the release notes: https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.4#REFRESH_MATERIALIZED_VIEW_CONCURRENTLY

这篇关于刷新物化视图：并发，交易行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！