Re: CREATE OR REPLACE MATERIALIZED VIEW

On 2024-07-04 22:18 +0200, Said Assemlal wrote:
> +1 for this feature.

Thanks!

> I noticed replacing the materialized view is blocking all reads. Is that
> expected ? Even if there is a unique index ?

That is expected because AccessExclusiveLock is acquired on the existing
matview.  This is also the case for CREATE OR REPLACE VIEW.

My initial idea, while writing the patch, was that one could replace the
matview without populating it and then run the concurrent refresh, like
this:

    CREATE OR REPLACE MATERIALIZED VIEW foo AS ... WITH NO DATA;
    REFRESH MATERIALIZED VIEW CONCURRENTLY foo;

But that won't work because concurrent refresh requires an already
populated matview.

Right now the patch either populates the replaced matview or leaves it
in an unscannable state.  Technically, it's also possible to skip the
refresh and leave the old data in place, perhaps by specifying
WITH *OLD* DATA.  New columns would just be null.  Of course you can't
tell if you got stale data without knowing how the matview was replaced.
Thoughts?

-- 
Erik