What happens if two process try to REFRESH MATERIALIZED VIEW CONCURRENTLY at the same time?

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?

As mentioned in this answer, "REFRESH MATERIALIZED VIEW CONCURRENTLY takes an EXCLUSIVE lock" on the table. Following the crumb trail to documentation we can read that an EXCLUSIVE lock on a table "allows only concurrent ACCESS SHARE locks, i.e., only reads from the table can proceed". In the same paragraph we can see that "EXCLUSIVE conflicts with ... EXCLUSIVE", meaning that another REFRESH MATERIALIZED VIEW CONCURRENTLY statement, which requests the same EXCLUSIVE lock, will have to wait until the earlier EXCLUSIVE lock is released.

If you want to avoid waiting for this lock for an undefined period, you may want to set the session variable lock_timeout to a sensible value.

StackExchange Database Administrators Q#199994, answer score: 18