Postgresql – pgAdmin crashes or freezes when big MATERIALIZED VIEWs are refreshing

materialized-viewpgadminpostgresql

I'm using pgAdmin III v1.22.1 on Windows 7 to connect to a PostgreSQL server version 9.4.8 on Red Hat.

We have scripts to REFRESH MATERIALIZED VIEWS during the night.
When they are refreshing, I can't connect to pgAdmin III. It just freezes. It seems that it tries to query some locked objects.

When the materialized views finishes refreshing, I am then able to log in via pgAdmin.

When the REFRESH MATERIALIZED VIEW my_view is done, the above query is run and pgAdmin unfreezes.

Is there a way to prevent pgAdmin from freezing when MATERIALIZED VIEWS are refreshing?

Best Answer

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.

Related Question