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.

  • You can add a column 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. – thibautg May 31 '16 at 9:47
  • 1
    Hello thibautg. If performance were not an issue, that is what I would have done. If I were to add such a column then (a) the view would become 50% larger, and it is already big, and (b) when doing 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. – Max Murphy May 31 '16 at 13:49
  • 1
    Here is a related post: dba.stackexchange.com/questions/58214/… – Max Murphy May 31 '16 at 13:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.