According to the docs:
CONCURRENTLY Refresh the materialized view without locking out concurrent selects on the materialized view. (...)
... OTHER CONTENTS ...
Even with this option only one REFRESH at a time may run against any one materialized view.
I had a function that checked the last refresh time for a MATERIALIZED VIEW and, if more than 60 seconds had passed, it would to refresh it.
However, what would happen if I try to refresh a materialized view from two separate processes at the same time? would they queue or would they raise an error?
Is there a way to detect when a MATERIALIZED VIEW is being refreshed and therefore avoid touching it?
Currently, I have resorted to populate a table record before refreshing (setting
true) and then setting it to
false when the process has finished.
EXECUTE 'INSERT INTO refresh_status (last_update, refreshing) VALUES (clock_timestamp(), true) RETURNING id') INTO refresh_id; EXECUTE 'REFRESH MATERIALIZED VIEW CONCURRENTLY my_mat_view'; EXECUTE 'UPDATE refresh_status SET refreshing=false WHERE id=$1' USING refresh_id;
Then, whenever I call this procedure, I check the most recent
last_update and its
refreshing value. If
refreshing is true, then don't try to refresh the materialized view.
EXECUTE 'SELECT extract(epoch FROM now() - (last_update))::integer, refreshing FROM refresh_status ORDER BY last_update DESC LIMIT 1' INTO update_seconds_ago, refreshing; IF(updated_seconds_ago > 60 AND refreshing = FALSE) THEN -- the refresh block above END IF;
However, I'm not sure the refreshing flag is being updated synchronously (I mean, it really waits for the refresh to actually be complete)
Is this approach rational or am I missing something here?