Re: CREATE OR REPLACE MATERIALIZED VIEW - Mailing list pgsql-hackers

Hi all,


On Tue, Apr 21, 2026 at 11:56 AM Erik Wienhold <ewie@ewie.name> wrote:
>
> Sorry for the late reply but I haven't had the time to dig into this.
> Here's v7 fixing the points below.
>
> On 2025-04-05 22:37 +0200, Tom Lane wrote:
> > * I think the proposal to deprecate IF NOT EXISTS is a nonstarter.
> > Yeah, I don't like it much, but the standard of proof to remove
> > features is amazingly high and I don't think it's been reached here.
> > We're unlikely to remove IF NOT EXISTS for tables, and to the extent
> > that matviews are like tables it's reasonable for them to have it too.
>
> Yeah, I got that gist from the replies upthread and dropped that patch.
>
> > * On the other hand, the semantics you've implemented for CREATE OR
> > REPLACE are not right.  The contract for any form of C.O.R. is that
> > it will either fail, or produce exactly the same object definition
> > that you would have gotten from plain CREATE with no conflicting
> > object.  The v6 code is visibly not doing that for properties such
> > as tablespace --- if the command doesn't mention that, you don't
> > get the default tablespace, you get whatever the old object had.
>
> Thanks a lot.  I added a test case for that and v7-0001 now restores the
> default options if none are specified.  Handling the default tablespace
> is a bit cumbersome IMO because its name must be passed to
> AlterTableInternal.  With v7-0002 I moved that to ATPrepSetTableSpace as
> an alternative using the empty string as stand-in for the default
> tablespace.  What do you think?
>
> > * BTW, I'm inclined to think that WITH OLD DATA ought to fail
> > if the command isn't replacing an existing matview.  It seems
> > inconsistent to silently reinterpret it as WITH DATA, just as
> > silently reinterpreting "no tablespace mentioned" as "use the
> > old tablespace" is inconsistent.  I'm not dead set on that
> > but it feels wrong.
>
> Yes that also felt iffy to me.  It just didn't occur to me to simply
> raise an error in ExecCreateTableAs.  Done so in v7-0003.


I tested the patches and the feature is working as expected, I am able
to create and replace a materialized view successfully and the data
updates correctly after replacement. Replacing multiple times also
works without any issues. Also I observed that adding new columns
during replacement works fine but removing existing columns is not
allowed, and it throws an error. This behavior seems correct and is
similar to how normal views work. In cases where dependencies exist,
the system prevents unsafe changes through these validations. Also,
REFRESH MATERIALIZED VIEW works properly after replacement.

But a few limitations (if I am not wrong) are noticed. I am attaching
the outputs here:-
1. CREATE OR REPLACE MATERIALIZED VIEW mv_test AS SELECT
generate_series(1,5) AS a WITH NO DATA;) is not currently supported
with the replace option and results in an error during validation.
postgres=# CREATE OR REPLACE MATERIALIZED VIEW mv_test AS
SELECT generate_series(1,5) AS a WITH NO DATA;
ERROR:  cannot drop columns from materialized view

2. When a dependent materialized view exists, replacing the base view
fails due to column validation errors.
postgres=# CREATE MATERIALIZED VIEW mv_table AS SELECT * FROM mv_test;
SELECT 1
postgres=# CREATE OR REPLACE MATERIALIZED VIEW mv_test AS SELECT 100 AS a;
ERROR:  cannot drop columns from materialized view
Here I think the system is not handling dependencies directly instead
blocking changes indirectly due to column mismatch.

3. Schema evolution is limited (cannot drop or modify existing columns).
postgres=# CREATE OR REPLACE MATERIALIZED VIEW mv_test AS SELECT 10 AS a;
ERROR:  cannot drop columns from materialized view

Overall, the implementation is stable and follows safe behavior.
Please let me know if I should explore handling the remaining
limitations or add more test cases.

Regards,
Soumya