Re: CREATE OR REPLACE MATERIALIZED VIEW

On 2024-09-05 22:33 +0200, Erik Wienhold wrote:
> 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.

rebased

--
Erik