Re: refresh materialized view concurrently

On Fri, Jun 21, 2013 at 5:20 AM, Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> wrote:
> If I don't miss something, the requirement for the CONCURRENTLY option is to
> allow simple SELECT reader to read the matview concurrently while the view
> is populating the new data, and INSERT/UPDATE/DELETE and SELECT FOR
> UPDATE/SHARE are still blocked. So, I wonder why it is not possible just to
> acquire ExclusiveLock on the matview while populating the data and swap the
> relfile by taking small AccessExclusiveLock. This lock escalation is no
> dead lock hazard, I suppose, because concurrent operation would block the
> other at the point ExclusiveLock is acquired, and ExclusiveLock conflicts
> AccessExclusiveLock. Then you don't need the complicated SPI logic or
> unique key index dependency.

This is no good. One, all lock upgrades are deadlock hazards. In
this case, that plays out as follows: suppose that the session running
REFRESH MATERIALIZED VIEW CONCURRENTLY also holds a lock on something
else. Some other process takes an AccessShareLock on the materialized
view and then tries to take a conflicting lock on the other object.
Kaboom, deadlock. Granted, the chances of that happening in practice
are small, but it IS the reason why we typically try to having
long-running operations perform lock upgrades. Users get really
annoyed when their DDL runs for an hour and then rolls back.

Two, until we get MVCC catalog scans, it's not safe to update any
system catalog tuple without an AccessExclusiveLock on some locktag
that will prevent concurrent catalog scans for that tuple. Under
SnapshotNow semantics, concurrent readers can fail to see that the
object is present at all, leading to mysterious failures - especially
if some of the object's catalog scans are seen and others are missed.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company