Thread: [GENERAL] does postgres log the create/refresh of a materialized view anywhere?

Is there a way to find out when a materialized view was created/refreshed?  I couldn't find this information anywhere
inthe docs. 

the use-case is that I wish to update a materialized view a few times a day in a clustered environment.  i'd like to
makesure one of the redundant nodes doesn't refresh if needed.  I can log this manually in postgresql if needed, but
washoping there was some "timestamp" on the view in a system table. 

On Tue, Dec 13, 2016 at 5:57 PM, Jonathan Vanasco <postgres@2xlp.com> wrote:

> Is there a way to find out when a materialized view was
> created/refreshed?

> I can log this manually in postgresql if needed, but was hoping
> there was some "timestamp" on the view in a system table.

This is not currently tracked in the system catalogs.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Dec 13, 2016 at 7:37 PM, Melvin Davidson <melvin6925@gmail.com> wrote:
> On Tue, Dec 13, 2016 at 7:36 PM, Kevin Grittner <kgrittn@gmail.com> wrote:
>> On Tue, Dec 13, 2016 at 5:57 PM, Jonathan Vanasco <postgres@2xlp.com> wrote:
>>
>>> Is there a way to find out when a materialized view was
>>> created/refreshed?
>>
>>> I can log this manually in postgresql if needed, but was hoping
>>> there was some "timestamp" on the view in a system table.
>>
>> This is not currently tracked in the system catalogs.

> This goes back to a discussion of my request to add relcreated
> column to pg_class.
> https://www.postgresql.org/message-id/CANu8FiyiRPGZ+gB=1JAYryX3HXCuQnFLSFgdzfXSvLMJ-jTc5w@mail.gmail.com
> Apparently the naysayers do not feel it is worthwhile.

Do you see relcreated as being something to set anew whenever the
data contents of a materialized view change due to the REFRESH
command?  I wouldn't have thought so, but I guess the problem with
that proposal is that everyone has a different idea of what the
semantics of the column would be.  Suggesting that field as the
solution here seems to reinforce that perception, anyway.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Wed, Dec 14, 2016 at 11:19 AM, Melvin Davidson <melvin6925@gmail.com> wrote:
> Originally, all I wanted was a column to record the creation date/time of an object. One reason it was debunked was
thatit would lead 
> to a request for an additional column to record changes in objects. I maintain that both can be done, but others
disagree,

Event triggers could be used to track the creation or drop timestamp
of objects. Even if it is not supported for REFRESH, it may make sense
to support it in the firing matrix.
--
Michael