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 refreshing
to 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?