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

materialized-viewplpgsqlpostgresql

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?

Best Answer

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.

Related Solutions

Using NEXT clause to set periodic materilized view refresh in oracle and verifying refresh

I'd use DBMS_SCHEDULER instead, with a job for each refresh. It's then easier to manage, and view previous executions.

CREATE OR REPLACE PROCEDURE REFRESH_MY_VIEW
AS
BEGIN
    DBMS_MVIEW.REFRESH('MY_VIEW');
END;
/

Refresh at 8am every day:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB
    (
    job_name            => 'REFRESH_MY_VIEW',
    job_type            => 'PLSQL_BLOCK',
    job_action          => 'REFRESH_MY_VIEW',
    number_of_arguments => 0,
    start_date          => SYSTIMESTAMP, 
    repeat_interval => 'freq=daily; byhour=8; byminute=0; bysecond=0;',
    end_date            => NULL,
    enabled             => TRUE,
    auto_drop           => FALSE,
    comments            => 'Refreshes MY_VIEW at 8am'
    );
END;
/

Refresh at 8pm every day:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB
    (
    job_name            => 'REFRESH_MY_VIEW',
    job_type            => 'PLSQL_BLOCK',
    job_action          => 'REFRESH_MY_VIEW',
    number_of_arguments => 0,
    start_date          => SYSTIMESTAMP, 
    repeat_interval => 'freq=daily; byhour=20; byminute=0; bysecond=0;',
    end_date            => NULL,
    enabled             => TRUE,
    auto_drop           => FALSE,
    comments            => 'Refreshes MY_VIEW at 8pm'
    );
END;
/

The *_SCHEDULER_JOB_LOG and *_SCHEDULER_JOB_RUN_DETAILS data dictionary views provide all the information you need to track what executed, when, and if any errors occurred. Documentation link here.

As pointed out in a comment, you can add multiple by hour clauses, comma-separated, as follows: repeat_interval => 'freq=daily; byhour=8,20; byminute=0; bysecond=0;'

Postgresql – Refresh Materialized View impact on the DB

Your problem is likely the indexes.

IIRC, refreshing a materialized view drops the existing data and create a new "table" with the current data.

What this does for your indexes is re-index the entire subset of data, which based on your indexes send like a significant workload.

My recommendation would be to not use the built-in materialized views and to instead roll your own equivalent, incrementally updating data so your are only writing stale data/indexes.

The trade off is that this will be much more complex to build out than simply creating a materialized view, and you'll need to consider how you want to track invalidation of stale data.

Best Answer

Related Solutions

Using NEXT clause to set periodic materilized view refresh in oracle and verifying refresh

Postgresql – Refresh Materialized View impact on the DB

Related Question