Add statistics refresh materialized view

On 2021/07/09 1:39, Seino Yuki wrote:

Hi.

This is a proposal for a new feature in statistics collector.
I think we need to add statistics about refresh matview to pg_stat_all_tables view.

Why do you want to treat only REFRESH MATERIALIZED VIEW command special?
What about other utility commands like TRUNCATE, CLUSTER, etc?

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.

When the "REFRESH MATERIALIZED VIEW" was executed, the number of times it was executed
and date it took were not recorded anywhere.

pg_stat_statements and log_statement would help?

"pg_stat_statements" can be used to get the number of executions and the date and time of execution,
but this information is statement-based, not view-based.

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?

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

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,

On Tue, Sep 07, 2021 at 06:11:14PM +0900, Seino Yuki wrote:

I would like to withdraw this proposal.

This was registered in the CF, so marked as RwF.
--
Michael

Hi,

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

Well, they either require extensions or parameters to be set properly.
One advantage I see to store those kind of information is that it can be
queried by application developers (users are reporting old data for
example).

We currently have to rely on other ways to figure out if materialized
views were properly refreshed.