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

Question

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?

score 18 · Accepted Answer · edited Mar 13 '18 at 04:59

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.

score 5 · Answer 2 · edited Mar 13 '18 at 04:57

As noted by mustaccio, this question overlaps significantly with Postgres Refresh Materialized View Locks.

However, while the accepted answer to that question has a link that answers this one, the answer to this question isn't directly included in that one.

So, to be specific: According to the PostgreSQL manual page on explicit locking (Link is to the current version page, for PostGres 10), REFRESH MATERIALIZED VIEW CONCURRENTLY takes a EXCLUSIVE lock. The EXCLUSIVE lock appears to block all other locks except ACCESS SHARE - that includes other EXCLUSIVE locks.

So a second REFRESH MATERIALIZED VIEW CONCURRENTLY request on the same view will wait for the lock obtained by the first one to be released.

score 0 · Answer 3 · edited Mar 13 '18 at 11:07

Thanks to the answers by mustaccio and RDFozz, I finally understood that REFRESH ... CONCURRENTLY taking an exclusive lock is the reason for which PostgreSQL documentation says:

Even with this option only one REFRESH at a time may run against any one materialized view.

I was afraid that this meant any attempt to make a simultaneous refresh would throw an error, but in the light of their answers, there isn't any special error involved. It's just a matter of locks that will enqueue simultaneous attempts. So the documentation could instead be interpreted as:

The lock acquired during this operation will prevent any operation other than reading from the MATERIALIZED VIEW. Further attempts to refresh the Materialized View while a REFRESH ... CONCURRENTLY is running will queue up until the first lock is released.

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

3 Answers3