1

I have a Postgres database and a family of materialised views defined as clones of tables, used for testing and analysis of data so the data won't change during the testing/analysis process like:

create materialized view cloneschema.records_table as 
select * from dataschema.records_table;

I would like to force the view definition to retain the select * columns, so that if the underlying schema of dataschema.records_table changes to gain additional columns then those columns also appear in cloneschema.records_table when the materialised view is refreshed.

The current state of affairs is that pg_matviews lists the SQL with only the columns present at time of materialised view creation, so if the materialised view is refreshed it holds only data for those columns.

The main goal is to ensure that additional columns are picked up in testing and analysis depending on these materialised views so they remain full copies of the tables when refreshed. A side advantage would be not needing to consciously DROP CASCADE and rebuild as in this answer.

My Postgres version is PostgreSQL 11.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit. I have consulted all questions tagged postgresql and materialized-view on Stack Overflow and DBA and haven't found a related question. I don't in fact find much discussion on the wider Internet of select * constructions in materialised view definitions in Postgres.

Cong Chen
  • 46
  • 4
  • I hate saying that, but PostgreSQL's matviews are an unusable technology. It's not only because of the way you're supposed to DROP CASCADE whenever changing a matview that has dependencies, but also because there is no fast-refresh capability and during the refresh your table gets exclusively locked. Bottom line: Use regular tables and refresh them according to queries stored in source control files. – Jonathan Jacobson Aug 24 '20 at 20:44
  • I noticed one contradiction in your case description. You claim that the materialized views are clones of tables. If that is the case, how come there are dependencies between the materialized views? If there are no dependencies, why would you need to DROP CASCADE. – Jonathan Jacobson Aug 24 '20 at 20:46
  • It's not clear what your question is. If you are asking if Postgres can modify the structure of the table backing the mview upon refresh, then the answer is no. If you are asking something else, you may want to clarify what that is. – mustaccio Aug 24 '20 at 20:47
  • @JonathanJacobson The basic matviews I am asking about in this question are clones of tables and there are more complex ones built on top of them, sorry for the confusion.

    @mustaccio I think that's what I want to ask, whether I can - is the best solution to replace the REFRESH with DROP and CREATE then? I see there's also no CREATE OR REPLACE option for matviews. Due to the dependent matviews this requires some care around the dependencies.

     – Cong Chen Aug 24 '20 at 21:59

0 Answers0