Re: REFRESH MATERIALIZED VIEW blocks pgAdmin III login

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