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
Best Answer
REFRESH MATERIALIZED VIEW CONCURRENTLY
takes anEXCLUSIVE
lock, soSELECT
s may still run. Per the manual.There seems to be an oversight with
REFRESH MATERIALIZED VIEW
(withoutCONCURRENTLY
) as it's not listed there. A search of the source code shows thatExecRefreshMatView
take anACCESS EXCLUSIVE
lock, as you might expect, so no other queries may run on the view.I've submitted a docs patch to list it.