Re: Add statistics refresh materialized view

On 2021-09-01 23:15, Fujii Masao wrote:
> Why do you want to treat only REFRESH MATERIALIZED VIEW command
> special?
> What about other utility commands like TRUNCATE, CLUSTER, etc?

First of all, knowing the update date and time of the MATVIEW is
essential for actual operation.
Without that information, users will not be able to trust the MATVIEW.

In terms of the reliability of the information in the table,
I think the priority of the REFRESHED MATVIEW is higher than that of
TRUNCATE and CLUSTER.

> It's not good design to add new columns per utility command into
> pg_stat_all_tables. Otherwise pg_stat_all_tables will have to have lots
> of
> columns to expose the stats of many utility commands at last. Which is
> ugly and very user-unfriendly.

> Most entries in pg_stat_all_tables are basically for tables. So the
> columns
> about REFRESH MATERIALIZED VIEW are useless for those most entries.
> This is another reason why I think the design is not good.

I agree with this opinion.
Initially, I thought about storing this information in pg_matviews,
but decided against it because of the overhead of adding it to the
system catalog.

> pg_stat_statements reports different records for REFRESH MATERIALIZED
> VIEW
> commands on different views. So ISTM that we can aggregate the
> information
> per view, from pg_stat_statements. No?

I made this suggestion based on the premise that the last update date
and time of the Mateview should always be retained.
I think the same concept applies to Oracle Database.
https://docs.oracle.com/cd/F19136_01/refrn/ALL_MVIEWS.html#GUID-8B9432B5-6B66-411A-936E-590D9D7671E9
I thought it would be useless to enable pg_stat_statements and
log_statement to see this information.

However, as you said, for most use cases, pg_stat_statements and
log_statement may be sufficient.
I would like to withdraw this proposal.

Regards,