Refresh Materialized View concurrently locks the view

Question

I am trying to refresh a Materialized View concurrently.

I created the view:

reate materialized VIEW mv_secondary_id AS
SELECT  id,
        array_to_tsvector(users_secondary_id(data)) as terms
from users;

Added a unique index based on id:

CREATE UNIQUE INDEX mv_secondary_id_unique_idx ON mv_secondary_id (id);

And refresh:

refresh materialized view concurrently mv_secondary_id;

As I understand from the documentation - concurrent queries on the view will be able to run on the view, but when I try to refresh and run a query - it waits for the refresh to end and only then completes.

I am using Postgres 11 on GCloud.

Any idea why the query waits?

@jjanes Stored procuedure that returns array of values based on the user data. I am transforming the array to tsvector in order to search on it later (on the view) — Amityo
Are you sure it is blocking, and not just being slow due to the high load imposed by the refresh? Might your stored procedure take locks of its own? I can't reproduce this. Is the blocked query selecting a single row by the unique key, or something more complicated? — jjanes

Amityo Amityo · Accepted Answer · 2019-10-03T08:37:52

Apparently I used a UI that is only able to run one query at a time - DBeaver 6.1.4 for linux.

I tried to run the same queries with pgcli and it worked fine.

Refresh Materialized View concurrently locks the view

1 Answers