Обсуждение: REFRESH MATERIALIZED VIEW blocks pgAdmin III login

If you try to connect to a database while REFRESH MATERIALIZE VIEW for a
materialized view in the database is in progress, the interface will
"hang" (the window remains blank and does not react to the mouse).

This is probably caused by the ACCESS EXCLUSIVE locks that are held
during REFRESH MATERIALIZED VIEW.  When the command finishes, pgAdmin III
continues working as usual.

I'd say that this is a bug, because it is not unusual for REFRESH
MATERIALIZED VIEW to take a very long time, and in that time pgAdmin III
is not working.

I tried with versions 1.20.0 and 1.18.1.

Here is a sample materialized view useful for testing:  CREATE MATERIALIZED VIEW x AS     SELECT CASE WHEN pg_sleep(1)
ISNULL THEN i ELSE 0 END     FROM generate_series(1, 100000) i     WITH NO DATA;
 

Enter "REFRESH MATERIALIZED VIEW x" and try to connect with pgAdmin III!

Yours,
Laurenz Albe

I wrote:
> If you try to connect to a database while REFRESH MATERIALIZE VIEW for a
> materialized view in the database is in progress, the interface will
> "hang" (the window remains blank and does not react to the mouse).
> 
> This is probably caused by the ACCESS EXCLUSIVE locks that are held
> during REFRESH MATERIALIZED VIEW.  When the command finishes, pgAdmin III
> continues working as usual.
> 
> I'd say that this is a bug, because it is not unusual for REFRESH
> MATERIALIZED VIEW to take a very long time, and in that time pgAdmin III
> is not working.
> 
> I tried with versions 1.20.0 and 1.18.1.

I dug a bit into that, and the problem is in pgViewFactory::CreateObjects in pgView.cpp.
The query that is constructed there selects pg_get_viewdef(c.oid) for all views,
and this function takes out an ACCESS SHARE LOCK on each view.

This lock conflicts with the ACCESS EXCLUSIVE lock from REFRESH MATERIALIZE VIEW.

What about joining with pg_locks and calling pg_get_viewdef(c.oid) only for the views
on which there is no ACCESS EXCLUSIVE lock?  There might be a race condition, but only
a very small one.

Yours,
Laurenz Albe