Re: Add statistics refresh materialized view

On Thu, 4 Jul 2024 at 21:13, Said Assemlal <sassemlal(at)neurorx(dot)com> 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/6fe02a8ab3b2b0801933b9cabfab62cf(at)oss(dot)nttdata(dot)com)
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