Thread

Re: CREATE OR REPLACE MATERIALIZED VIEW

Erik Wienhold <ewie@ewie.name> — 2024-10-31T00:22:17Z

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