Clearing global statistics - Mailing list pgsql-hackers

Since the pg_stat_bgwriter structure was introduced in 8.3, there's
never been any way to reset its statistics back to 0.  A week of
analyzing data from that every day drove me crazy enough to finally fix
this with the attached patch.  This implements the TODO item "Allow the
clearing of cluster-level statistics", based on previous discussion
ending at http://archives.postgresql.org/pgsql-hackers/2009-03/msg00920.php

Here's the patch in action:

gsmith=# select checkpoints_req,buffers_alloc from pg_stat_bgwriter;
 checkpoints_req | buffers_alloc
-----------------+---------------
               1 |             5

gsmith=# select pg_stat_reset_global();

gsmith=# select checkpoints_req,buffers_alloc from pg_stat_bgwriter;
 checkpoints_req | buffers_alloc
-----------------+---------------
               0 |             0

Patch is complete including docs, it's basically just pg_stat_reset with
a different clearing mechanism at the very end.  My list of potential
questions here are:

-Not sure if this should be named pg_stat_rest_global (to match the way
these are called "global stats" in the source) or
pg_stat_reset_cluster.  Picked the former for V1, not attached to that
decision at all.  Might even make sense to use a name that makes it
obvious the pg_stat_bgwriter data is what's targeted.

-I create a new stats message type for this, but just reuse the same
message payload structure as pg_stats_reset rather than add a new
payload structure for no good reason.  That's marked with two XXX s in
the code as a questionable design decision.  I can implement that too if
there's some reason it's a good idea I don't know yet.

-I grabbed what looked like an appropriate unused OID.  I'm never sure
if I did that right or not though, it may need to be renumbered.

Since this whole patch is basically a cut and paste job of code that was
already there, I don't really expect it to need much discussion beyond
these minor points; wouldn't have sent it in the middle of an active
CommitFest if that weren't the case.

--
Greg Smith    2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com  www.2ndQuadrant.com

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 1f70fd4..2af5696 100644
*** a/doc/src/sgml/monitoring.sgml
--- b/doc/src/sgml/monitoring.sgml
*************** postgres: <replaceable>user</> <replacea
*** 918,923 ****
--- 918,932 ----
         (requires superuser privileges)
        </entry>
       </row>
+
+      <row>
+       <entry><literal><function>pg_stat_reset_global</function>()</literal></entry>
+       <entry><type>void</type></entry>
+       <entry>
+        Reset the global statistics counters for the database cluster to
+        zero (requires superuser privileges)
+       </entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 2d4fb77..b124a3e 100644
*** a/src/backend/postmaster/pgstat.c
--- b/src/backend/postmaster/pgstat.c
*************** static void pgstat_recv_tabstat(PgStat_M
*** 269,274 ****
--- 269,275 ----
  static void pgstat_recv_tabpurge(PgStat_MsgTabpurge *msg, int len);
  static void pgstat_recv_dropdb(PgStat_MsgDropdb *msg, int len);
  static void pgstat_recv_resetcounter(PgStat_MsgResetcounter *msg, int len);
+ static void pgstat_recv_resetglobalcounter(PgStat_MsgResetcounter *msg, int len);
  static void pgstat_recv_autovac(PgStat_MsgAutovacStart *msg, int len);
  static void pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len);
  static void pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len);
*************** pgstat_reset_counters(void)
*** 1150,1155 ****
--- 1151,1182 ----
      pgstat_send(&msg, sizeof(msg));
  }

+ /* ----------
+  * pgstat_reset_global_counters() -
+  *
+  *    Tell the statistics collector to reset counters for cluster-wide globals.
+  * ----------
+  */
+ void
+ pgstat_reset_global_counters(void)
+ {
+     /* XXX Since there's no payload needed, reusing the existing reset counter
+        structure rather than defining a new one.  Is that OK? */
+     PgStat_MsgResetcounter msg;
+
+     if (pgStatSock < 0)
+         return;
+
+     if (!superuser())
+         ereport(ERROR,
+                 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+                  errmsg("must be superuser to reset statistics counters")));
+
+     pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_RESETGLOBALCOUNTER);
+     /* XXX Not actually using this */
+     msg.m_databaseid = MyDatabaseId;
+     pgstat_send(&msg, sizeof(msg));
+ }

  /* ----------
   * pgstat_report_autovac() -
*************** PgstatCollectorMain(int argc, char *argv
*** 2888,2893 ****
--- 2915,2926 ----
                                               len);
                      break;

+                 case PGSTAT_MTYPE_RESETGLOBALCOUNTER:
+                     pgstat_recv_resetglobalcounter(
+                                              (PgStat_MsgResetcounter *) &msg,
+                                              len);
+                     break;
+
                  case PGSTAT_MTYPE_AUTOVAC_START:
                      pgstat_recv_autovac((PgStat_MsgAutovacStart *) &msg, len);
                      break;
*************** pgstat_recv_resetcounter(PgStat_MsgReset
*** 3840,3845 ****
--- 3873,3890 ----
  }

  /* ----------
+  * pgstat_recv_resetglobalcounter() -
+  *
+  *    Reset the statistics for the specified cluster.
+  * ----------
+  */
+ static void
+ pgstat_recv_resetglobalcounter(PgStat_MsgResetcounter *msg, int len)
+ {
+     memset(&globalStats, 0, sizeof(globalStats));
+ }
+
+ /* ----------
   * pgstat_recv_autovac() -
   *
   *    Process an autovacuum signalling message.
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index fc245d0..70d2bc9 100644
*** a/src/backend/utils/adt/pgstatfuncs.c
--- b/src/backend/utils/adt/pgstatfuncs.c
*************** extern Datum pg_stat_get_buf_alloc(PG_FU
*** 78,83 ****
--- 78,84 ----

  extern Datum pg_stat_clear_snapshot(PG_FUNCTION_ARGS);
  extern Datum pg_stat_reset(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_reset_global(PG_FUNCTION_ARGS);

  /* Global bgwriter statistics, from bgwriter.c */
  extern PgStat_MsgBgWriter bgwriterStats;
*************** pg_stat_reset(PG_FUNCTION_ARGS)
*** 1108,1110 ****
--- 1109,1120 ----

      PG_RETURN_VOID();
  }
+
+ /* Reset all the cluster-wide global counters */
+ Datum
+ pg_stat_reset_global(PG_FUNCTION_ARGS)
+ {
+     pgstat_reset_global_counters();
+
+     PG_RETURN_VOID();
+ }
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index bcb20aa..e04fd00 100644
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*************** DATA(insert OID = 2230 (  pg_stat_clear_
*** 3069,3074 ****
--- 3069,3076 ----
  DESCR("statistics: discard current transaction's statistics snapshot");
  DATA(insert OID = 2274 (  pg_stat_reset                    PGNSP PGUID 12 1 0 0 f f f f f v 0 0 2278 "" _null_ _null_
_null__null_    pg_stat_reset _null_ _null_ _null_ )); 
  DESCR("statistics: reset collected statistics for current database");
+ DATA(insert OID = 2275 (  pg_stat_reset_global            PGNSP PGUID 12 1 0 0 f f f f f v 0 0 2278 "" _null_ _null_
_null__null_    pg_stat_reset_global _null_ _null_ _null_ )); 
+ DESCR("statistics: reset global collected statistics across cluster");

  DATA(insert OID = 1946 (  encode                        PGNSP PGUID 12 1 0 0 f f f t f i 2 0 25 "17 25" _null_ _null_
_null__null_ binary_encode _null_ _null_ _null_ )); 
  DESCR("convert bytea value into some ascii-only text string");
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 102507d..c372f36 100644
*** a/src/include/pgstat.h
--- b/src/include/pgstat.h
*************** typedef enum StatMsgType
*** 38,43 ****
--- 38,44 ----
      PGSTAT_MTYPE_TABPURGE,
      PGSTAT_MTYPE_DROPDB,
      PGSTAT_MTYPE_RESETCOUNTER,
+     PGSTAT_MTYPE_RESETGLOBALCOUNTER,
      PGSTAT_MTYPE_AUTOVAC_START,
      PGSTAT_MTYPE_VACUUM,
      PGSTAT_MTYPE_ANALYZE,
*************** extern void pgstat_drop_database(Oid dat
*** 633,638 ****
--- 634,640 ----

  extern void pgstat_clear_snapshot(void);
  extern void pgstat_reset_counters(void);
+ extern void pgstat_reset_global_counters(void);

  extern void pgstat_report_autovac(Oid dboid);
  extern void pgstat_report_vacuum(Oid tableoid, bool shared, bool scanned_all,