1

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?

4
  • 1
    How much time it takes normally to refresh that view ? Maybe it only seems to freeze but its only waiting for ending the query. – Sahap Asci May 31 '16 at 7:45
  • It can take many hours to refresh some matviews. pgAdmin shouldn't freeze when queries are running in the background. – thibautg May 31 '16 at 8:08
  • I've edited the question to show an extract from SELECT * FROM pg_stat_activity – thibautg May 31 '16 at 9:26
  • It seems that it's a known bug. I hope it will be resolved in a future release. – thibautg Jun 3 '16 at 12:21
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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