Hi, On Wed, Jul 29, 2026 at 10:21:38AM +0200, Rafia Sabih wrote: > On Thu, 4 Jul 2024 at 21:13, Said Assemlal <[email protected]> wrote: > > > 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. > > I agree that there is no easy way currently to figure out the last time a > materialized view was refreshed. I want to work on this, but before > implementing anything I'd like to discuss the right approach.
Yeah. > Looking at the code, REFRESH MATERIALIZED VIEW and the populate step of > CREATE MATERIALIZED VIEW both go through RefreshMatViewByOid() in > matview.c, which already updates relispopulated on pg_class transactionally > once the refresh completes. Recording a timestamp would hook in right > there. Unlike VACUUM, though, REFRESH can run inside a transaction block > and get rolled back, so the timestamp needs to be a normal transactional > catalog update, not something written directly into shared stats memory > like last_vacuum_time. That part seems straightforward. The real question > is where this information should live. > > One option is a column on pg_class, say last_refresh timestamptz, set the > same way relispopulated is. Less implementation work, but it would stay > null for every non-matview row, and pg_class already carries every table, > index, sequence, etc., so this adds width with no benefit for the rest. > > This was actually discussed back in 2021 (Seino Yuki, > https://www.postgresql.org/message-id/flat/[email protected]), > proposing count and last-refresh-time columns on pg_stat_all_tables. I > gather, Fujii Masao objected on two grounds: it singles out REFRESH > MATERIALIZED VIEW when other utility commands (TRUNCATE, CLUSTER, etc.) > could make the same claim, and the columns would be dead weight for the > vast majority of pg_stat_all_tables entries, which are regular tables. I > think that objection is right, and it points at a way to avoid it entirely: > don't touch pg_stat_all_tables or pg_class at all. pg_class does not have any timestamps so far, so putting it there looks out-of-place to me. While being important information, it is also not essential information, so another strike against pg_class in my opinion. Why not circle back to pg_stat_all_tables and discuss two new columns there: last_rewrite and rewrite_count? Those would have to be maintained for regular table rewrites during DDL as well, but I think that would be (relatively) valuable information anyway. One could argue that "rewrite" is wrong terminology for a matview refresh but I guess most users would figure it out. > That's the second option: a dedicated catalog, e.g. pg_matview_meta(mvrelid > oid, mvlastrefresh timestamptz), one tuple per materialized view. As this is more-or-less performance data, I would suggest to go with the usual pg_stat_* naming schema and non-cryptic column names, as well as denormalization of schemaname/relation name similar to pg_stat_all_tables. So something like pg_stat_matviews with oid, schemaname, relname, last_refresh, refresh_count and what else. Regarding what else, when you are adding a new system catalog anyway, it might make sense to maintain the duration of the refresh as well, similar to pg_stat_statements. So something like total_refresh_times, min_refresh_time, max_refresh_time, mean_refresh_time, stddev_refresh_time. But as somebody mentioned upstream, those should be available from pg_stat_statements today if one enables it. > More work - it needs its own catalog OID, a unique index on mvrelid, > and exclusion from pg_dump / reset on upgrade, since this isn't user > data As an aside, why would it need special pg_dump work? I don't think we ever dump system catalogs, at least for regular dumps. > pg_class and pg_stat_all_tables untouched, sidestepping both of Fujii's > objections, and follows the same pattern as pg_partitioned_table or > pg_statistic_ext_data. I'm leaning this way, partly because it leaves room > to later add refresh duration, last error time, or refresh count. Again, those are all stats. > - handling of REFRESH ... WITH NO DATA should it clear the recorded > timestamp or leave the last real refresh time visible Good question. I think the other important question is: if we ever get incremental materialized views (IVM), would that change anything here and possibly make that new system catalog redundant? Would that be a problem in that case? Michael
