On Thu, 4 Jul 2024 at 21:13, Said Assemlal <[email protected]> wrote:
> 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. > > > Hi all, 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. 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. That's the second option: a dedicated catalog, e.g. pg_matview_meta(mvrelid oid, mvlastrefresh timestamptz), one tuple per materialized view. 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 - but it leaves 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. Looking forward to your inputs, particularly: - whether the dedicated-catalog approach addresses the 2021 concerns, or there's still a preference for the pg_class route - naming (pg_matview_meta / mvrelid / mvlastrefresh) - handling of REFRESH ... WITH NO DATA should it clear the recorded timestamp or leave the last real refresh time visible -- Regards, Rafia Sabih CYBERTEC PostgreSQL International GmbH
