18

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?

0

3 Answers 3

18

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.

5
  • PS: do you think it makes any sense to keep this auxiliar table to tell concurrent (no pun intended) attempts that the MAT VIEW is busy and therefore should be left alone even if it seems it needs a refresh?
    – ffflabs
    Mar 13, 2018 at 10:52
  • It's a matter of opinion; if you think it helps you can of course keep your logic. Note, however, that your function is subject to race conditions and therefore is not 100% reliable.
    – mustaccio
    Mar 13, 2018 at 11:03
  • Do you think it's viable to check on pg_locks then to see if there's one referencing the mat view?
    – ffflabs
    Mar 13, 2018 at 11:10
  • Again, race condition possible: there's a chance that a lock will be placed between you check pg_locks and start your refresh. A proper way to address lock conflicts is to set timeout and handle the error.
    – mustaccio
    Mar 13, 2018 at 12:54
  • any changes to this in postgres 14.5?
    – PirateApp
    Sep 12, 2022 at 10:58
5

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.

1
  • Thank you. I still marked @mustaccio's answer as accepted since he edited his text to be more specific to my question.
    – ffflabs
    Mar 13, 2018 at 10:40
0

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.