Re: CREATE OR REPLACE MATERIALIZED VIEW

On Mon, 29 Jun 2026 at 00:02, Erik Wienhold <ewie(at)ewie(dot)name> wrote:

> 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
>
>
> I looked into the latest version of this patch and there is an issue I am
seeing,
in CreateTableAsRelReplaceable when we are calling
checkMembershipInCurrentExtension() it is based on oldrelid, however
further in the code when we are finally calling create_ctas_replace it is
with matviewoid which is new oid which is is never checked against
checkMembershipInCurrentExtension, so couldn't there be a possibility that
in between these two calls a concurrent session drops and recreates the
matview of same name. Also, since there is no lock on the relation. Maybe
this is a pathological case, but wouldn't it be better to have something
such that check and replace are close together?

Another thing, there is a branch if (!into->replace)
in CreateTableAsRelReplaceable, however CreateTableAsRelReplaceable is
called only under if (is_matview && into->replace) in ExecCreateTableAs, so
that is dead code precisely.
--
Regards,
Rafia Sabih
CYBERTEC PostgreSQL International GmbH