On Mon, 29 Jun 2026 at 00:02, Erik Wienhold <[email protected]> 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
