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.
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" 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.
Also, that method requires the high cost of "pg_stat_statements".
This patch will add statistics(count, last time) about "REFRESH
MATERIALIZED VIEW"
to pg_stat_all_tables(pg_stat_user_tables, [pg_stat_sys_tables]).
What do you think?
Regards,
Seino Yukidiff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 643e1ad49f..884585af0c 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4285,6 +4285,26 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
daemon
+
+
+
+ last_refresh_matview_time timestamp with time zone
+
+
+ Last time at which this materialized view was refreshed
+ (If this record is materialized view)
+
+
+
+
+
+ refresh_matview_count bigint
+
+
+ Number of times this materialized view has been refreshed
+ (If this record is materialized view)
+
+
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 999d984068..70375c7e19 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -656,7 +656,9 @@ CREATE VIEW pg_stat_all_tables AS
pg_stat_get_vacuum_count(C.oid) AS vacuum_count,
pg_stat_get_autovacuum_count(C.oid) AS autovacuum_count,
pg_stat_get_analyze_count(C.oid) AS analyze_count,
-pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count
+pg_stat_get_autoanalyze_count(C.oid) AS autoanalyze_count,
+pg_stat_get_last_refresh_matview_time(C.oid) AS last_refresh_matview_time,
+pg_stat_get_refresh_matview_count(C.oid) AS refresh_matview_count
FROM pg_class C LEFT JOIN
pg_index I ON C.oid = I.indrelid
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index 25bbd8a5c1..20733b04de 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -356,6 +356,9 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
ObjectAddressSet(address, RelationRelationId, matviewOid);
+ /* Report results to the stats collector */
+ pgstat_report_refresh_matview(matviewOid);
+
/*
* Save the rowcount so that pg_stat_statements can track the total number
* of rows processed by REFRESH MATERIALIZED VIEW command. Note that we
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 11702f2a80..6a378831a9 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -358,6 +358,7 @@ static void pgstat_recv_checksum_failure(PgStat_MsgChecksumFailure *msg, int len
static void pgstat_recv_connstat(PgStat_MsgConn *msg, int len);
static void pgstat_recv_replslot(PgStat_MsgReplSlot *msg, int len);
static void pgstat_recv_tempfile(PgStat_MsgTempFile *msg, int len);
+static void pgstat_recv_refresh_matview(PgStat_MsgRefreshMatview *msg, int len);
/*
* Public functions called from postmaster follow
@@ -1863,6 +1864,28 @@ pgstat_report_replslot_drop(const char *slotname)
pgstat_send(, sizeof(PgStat_MsgReplSlot));
}
+/* -
+ * pgstat_report_refresh_matview() -
+ *
+ * Tell the collector about the matview we just refreshed.
+ * -
+ */
+void
+pgstat_report_refresh_matview(Oid tableoid)
+{
+ PgStat_MsgRefreshMatview msg;
+
+ if (pgStatSock == PGINVALID_SOCKET || !pgstat_track_counts)
+ return;
+
+ pgstat_setheader(_hdr, PGSTAT_MTYPE_REFRESHEDMATVIEW);
+ msg.m_databaseid = MyDatabaseId;
+ msg.m_tableoid = tableoid;
+ msg.refreshmatview_time = GetCurrentTimestamp();
+ pgstat_send(, sizeof(msg));
+}
+
+
/* --
* pgstat_ping() -
*
@@ -3424,6 +3447,10 @@ PgstatCollectorMain(int argc, char *argv[])
pgstat_recv_connstat(_conn, len);
break;
+case PGSTAT_MTYPE_REFRESHEDMATVIEW:
+ pgstat_recv_refresh_matview(_refreshmatview, len);
+ break;
+
default:
break;
}
@@ -3600,6 +3627,8 @@ pgstat_get_tab_entry(PgStat_StatDBEntry *dbentry, Oid tableoid, bool create)
result->analyze_count = 0;
result->autovac_analyze_timestamp = 0;
result->autovac_analyze_count = 0;
+ result->matview_refresh_timestamp = 0;
+ result->matview_refresh_count = 0;
}
return result;
@@ -4883,6 +4912,8 @@ pgstat_recv_tabstat(PgStat_MsgTabstat *msg, int len)
tabentry->analyze_count =
