Re: CREATE OR REPLACE MATERIALIZED VIEW

On 2024-07-27 02:45 +0200, Erik Wienhold wrote:
> 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.

Here's a rebased version due to conflicts with f683d3a4ca and
1e35951e71. No other changes since v2.

--
Erik