Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I would like to keep a record of when various Postgres materialised views are refreshed. As far as I can see there are no built in logs. The next obvious choice is to create a trigger, however it seems that triggers cannot be set on postgres materialized views. I'm running out of options. Can you recommend a way to do this without reaching up into the application layer?
Many thanks in advance. Max.
Possible solution in 2021:
There is an extension mv_stats that captures MV (Materialized View) statistics. It adds triggers for "CREATE MATERIALIZED VIEW", "ALTER MATERIALIZED VIEW", "REFRESH MATERIALIZED VIEW" and then records timestamps when they are called for a MV.
Tracking the refresh Performance of Materialized Views with mv_stat in PostgreSQL
now() AS last_refreshto your materialized view in your query definition. This way it will hold the time when the view has been refreshed.refresh materialized view concurrentlyevery single row would be changed, so instead of updating just a handful of rows every time it would have to update all of them. At the moment it looks as if my best choice is to look at the last modified time of the file on disk, although that is far from accurate. I'm looking at commit_timestamps now.