Refresh materialized views: Concurrency, transacti

问题:

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

A quote from this blog:

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:

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. 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

回答1:

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.

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

回答2:

Take the answer with a grain of salt, since I've yet to play around with mat views, but based on this:

http://www.postgresql.org/docs/current/static/sql-creatematerializedview.html

The philosophy behind them is to treat them like smarter variations of create table as ...:

CREATE MATERIALIZED VIEW is similar to CREATE TABLE AS, except that it also remembers the query used to initialize the view, so that it can be refreshed later upon demand. A materialized view has many of the same properties as a table, but there is no support for temporary materialized views or automatic generation of OIDs.

Insofar as I read the refresh materialized view command or the docs I've found on them, they don't get updated automatically, and I understand the flow the same way you do.

The exclusive lock, I imagine, comes from the fact that you can't easily know (except in trivial cases) which rows are dirty and which aren't. Had the devs identified an efficient way of doing so, the materialized view would probably be updating automatically and concurrently.