Sunday, 15 September 2013

Refresh materialized views: Concurrency, transactional behaviour

Refresh materialized views: Concurrency, transactional behaviour

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:
A query is accessing a materialized view
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
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)
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".
Refresh completes, the lock is removed
Queries which have been waiting for the matview continue

No comments:

Post a Comment