Re: CREATE OR REPLACE MATERIALIZED VIEW

On 2024-07-12 16:49 +0200, Said Assemlal wrote:
> > 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?
>
> I believe the expectation is to get materialized views updated whenever it
> gets replaced so likely to confuse users ?

I agree, that could be confusing -- unless it's well documented. The
attached 0003 implements WITH OLD DATA and states in the docs that this
is intended to be used before a concurrent refresh.

Patch 0001 now covers all matview cases in psql's tab completion. I
missed some of them with v1.

--
Erik