I seems that it's a known bug.
I'm answering to my own question with this workaround.
If you use PostgreSQL 9.4+, one workaround is to use REFRESH MATERIALIZED VIEW CONCURRENTLY
.
From PostgreSQL's manual:
CONCURRENTLY
Refresh the materialized view without locking out concurrent selects on the materialized view. Without this option a refresh which
affects a lot of rows will tend to use fewer resources and complete
more quickly, but could block other connections which are trying to
read from the materialized view. This option may be faster in cases
where a small number of rows are affected.
This option is only allowed if there is at least one UNIQUE index on the materialized view which uses only column names and includes all
rows; that is, it must not index on any expressions nor include a
WHERE clause.
You just need to add an UNIQUE INDEX
to your materialized view and to refresh it CONCURRENTLY
:
-- add index (do it only once per matview)
CREATE UNIQUE INDEX ON very_big_matview (id);
-- refresh concurrently
REFRESH MATERIALIZED VIEW CONCURRENTLY very_big_matview;
But it does not prevent other users from running long refreshes which hangs my pgAdmin.
SELECT * FROM pg_stat_activity