Re: CREATE OR REPLACE MATERIALIZED VIEW

On 2026-05-01 17:59 +0200, Soumya S Murali wrote:
> 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.

Thanks for testing!

> 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

Please share your test script. It looks like you're operating on an
existing mv_test with additional columns, probably from earlier test
cases. And as you've noticed in your first paragraph, dropping columns
is not supported via CREATE OR REPLACE. So your third point isn't even
valid. Unless you mean "changing column values" when writing "modify
existing columns". Changing the SELECT values should of course work if
the types are compatible with any existing column type.

--
Erik Wienhold