patternsqlModerate
Postgres Refresh Materialized View Locks
Viewed 0 times
postgresmaterializedrefreshviewlocks
Problem
I have a materialized view in Postgres, and want to know what locks (if any) are taken out when refreshing that view.
Specifically, I'm trying to understand whether the
I tried an explain with no success:
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 structureSolution
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.
Context
StackExchange Database Administrators Q#119744, answer score: 15
Revisions (0)
No revisions yet.