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.
now() AS last_refresh
to your materialized view in your query definition. This way it will hold the time when the view has been refreshed.refresh materialized view concurrently
every 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.