You have a PSQL database utilizing a “MV” materialized view and want an automated process to determine if the MV should be refreshed because the underlying tables have changed. It should also wait a period of time between the last known update to a table and then execute the
REFRESH MATERIAL VIEW command.
This article will show you how to set up a couple of functions to handle that process on as many MVs as you need. Materialized Views are like actual tables in that their data is stored in a table rather than queried each time it is used, and they are like views in that you can’t modify the data in the MV… and so in order for a materialized view to properly reflect the data in the tables it gets the original data from there has to be an explicit instruction to refresh. Refreshing takes both time and resources, refreshing a materialized view may take long seconds or minutes to complete. In older versions of Postgresql this would even cause data to become temporarily inaccessible (and still will without the use of the
CONCURRENTLY parameter circa Postgresql >= 9.4).