All we need is an easy explanation of the problem, so here it is.
I have a materialized view in Postgres, and want to know what locks (if any) are taken out when refreshing that view.
CREATE TABLE people ( name VARCHAR(30) ); INSERT INTO people VALUES ('Alice'), ('Bob'), ('Cher'); CREATE MATERIALIZED VIEW test AS SELECT * FROM people; REFRESH MATERIALIZED VIEW test;
Specifically, I’m trying to understand whether the
REFRESH MATERIALIZED VIEW command takes out an
ACCESS EXCLUSIVE lock.
I tried an explain with no success:
#> EXPLAIN REFRESH MATERIALIZED VIEW test; QUERY PLAN ------------------------------------------- Utility statements have no plan structure
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
REFRESH MATERIALIZED VIEW CONCURRENTLY takes an
EXCLUSIVE lock, so
SELECTs may still run. Per the manual.
There seems to be an oversight with
REFRESH MATERIALIZED VIEW (without
CONCURRENTLY) as it’s not listed there. A search of the source code shows that
ExecRefreshMatView take an
ACCESS EXCLUSIVE lock, as you might expect, so no other queries may run on the view.
I’ve submitted a docs patch to list it.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂