Based on this discussion, I worked on the first version of this patch. Please find the attached file. Looking forward to your feedback.
On Thu, 30 Jul 2026 at 08:32, Rafia Sabih <[email protected]> wrote: > > > On Wed, 29 Jul 2026 at 14:17, Michael Banck <[email protected]> wrote: > >> 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. >> > > Thank you Michael for your input and giving another good reason to not go > this route. > >> >> 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. >> > I like the idea, so as I understand it would also be populated by other > commands like VACUUM, CLUSTER, ALTER TABLE, so maybe this could be a good > solution in that way. But in regards to materialised view I am unsure how > to handle REFRESH MATERIALISED VIEW CONCURRENTLY, since in that path there > is no real rewrite happening, rather it makes a new heap. Particularly, in > all the other cases we are calling finish_heap_swap so we can get the > timestamp there for our purpose but not for REFRESH with CONCURRENTLY case. > >> >> > 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. >> >> +1 > >> > 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. >> >> I meant we need to ensure that it is not included in pg_dump, etc. But > you are right there shouldn't be any special handling for this case. > >> > 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. >> > Yes and an interesting one to know the answer to before starting > implementation. > >> >> 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? >> >> I thought a little about it based on the extension pg_ivm, but it doesn't > look like anything changes here. The ivm is working based on triggers so it > doesn't conflict with the path which we are covering here. Also, looks like > even when ivm is in core it is likely to have its own catalog table. > > Michael >> >> >> > > -- > Regards, > Rafia Sabih > CYBERTEC PostgreSQL International GmbH > -- Regards, Rafia Sabih CYBERTEC PostgreSQL International GmbH
v1-0001-Track-last-refresh-time-and-count-for-materialize.patch
Description: Binary data
