Re: keeping a timestamp of the last stats reset (for a db, table and function)

Tomas Vondra wrote:
> Because when I create a database, the field is
> NULL - that's true. But once I connect to the database, the stats are
> updated and the field is set (thanks to the logic in pgstat.c).
>

OK--so it does what I was hoping for, I just didn't test it the right
way.  Let's call that a documentation issue and move on.

Attached is an updated patch that fixes the docs and some other random
bits.  Looks ready for committer to me now.  Make sure to adjust
PGSTAT_FILE_FORMAT_ID, do a cat version bump, and set final OIDs for the
new functions.

Below is what changed since the last posted version, mainly as feedback
for Tomas:

-Explained more clearly that pg_stat_reset and
pg_stat_reset_single_counters will both touch the database reset time,
and that it's initialized upon first connection to the database.

-Added the reset time to the list of fields in pg_stat_database and
pg_stat_bgwriter.

-Fixed some tab/whitespace issues.  It looks like you had tab stops set
at 8 characters during some points when you were editing non-code
files.  Also, there were a couple of spot where you used a tab while
text in the area used spaces.  You can normally see both types of errors
if you read a patch, they showed up as misaligned things in the context
diff.

-Removed some extra blank lines that didn't fit the style of the
surrounding code.

Basically, all the formatting bits I'm nitpicking about I found just by
reading the patch itself; they all stuck right out.  I'd recommend a
pass of that before submitting things if you want to try and avoid those
in the future.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index ca83421..100f938 100644
*** a/doc/src/sgml/monitoring.sgml
--- b/doc/src/sgml/monitoring.sgml
*************** postgres: <replaceable>user</> <replacea
*** 267,273 ****
        by backends (that is, not by the background writer), how many times
        those backends had to execute their own fsync calls (normally the
        background writer handles those even when the backend does its own
!       write), and total buffers allocated.
       </entry>
       </row>

--- 267,273 ----
        by backends (that is, not by the background writer), how many times
        those backends had to execute their own fsync calls (normally the
        background writer handles those even when the backend does its own
!       write), total buffers allocated, and time of last statistics reset.
       </entry>
       </row>

*************** postgres: <replaceable>user</> <replacea
*** 278,286 ****
        number of transactions committed and rolled back in that database,
        total disk blocks read, total buffer hits (i.e., block
        read requests avoided by finding the block already in buffer cache),
!       number of rows returned, fetched, inserted, updated and deleted, and
        total number of queries cancelled due to conflict with recovery (on
!       standby servers).
       </entry>
       </row>

--- 278,286 ----
        number of transactions committed and rolled back in that database,
        total disk blocks read, total buffer hits (i.e., block
        read requests avoided by finding the block already in buffer cache),
!       number of rows returned, fetched, inserted, updated and deleted, the
        total number of queries cancelled due to conflict with recovery (on
!       standby servers), and time of last statistics reset.
       </entry>
       </row>

*************** postgres: <replaceable>user</> <replacea
*** 663,668 ****
--- 663,681 ----
       </row>

       <row>
+       <entry><literal><function>pg_stat_get_db_stat_reset_time</function>(<type>oid</type>)</literal></entry>
+       <entry><type>timestamptz</type></entry>
+       <entry>
+        Time of the last statistics reset for the database.  Initialized to the
+        system time during the first connection to each database.  The reset time
+        is updated when you call <function>pg_stat_reset</function> on the
+        database, as well as upon execution of
+        <function>pg_stat_reset_single_table_counters</function> against any
+        table or index in it.
+       </entry>
+      </row>
+
+      <row>
        <entry><literal><function>pg_stat_get_numscans</function>(<type>oid</type>)</literal></entry>
        <entry><type>bigint</type></entry>
        <entry>
*************** postgres: <replaceable>user</> <replacea
*** 1125,1130 ****
--- 1138,1153 ----
         <varname>bgwriter_lru_maxpages</varname> parameter
        </entry>
       </row>
+
+      <row>
+       <entry><literal><function>pg_stat_get_bgwriter_stat_reset_time()</function></literal></entry>
+       <entry><type>timestamptz</type></entry>
+       <entry>
+         Time of the last statistics reset for the background writer, updated
+         when executing <function>pg_stat_reset_shared('bgwriter')</function>
+         on the database cluster.
+       </entry>
+      </row>

       <row>
        <entry><literal><function>pg_stat_get_buf_written_backend()</function></literal></entry>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 718e996..904714f 100644
*** a/src/backend/catalog/system_views.sql
--- b/src/backend/catalog/system_views.sql
*************** CREATE VIEW pg_stat_database AS
*** 523,529 ****
              pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted,
              pg_stat_get_db_tuples_updated(D.oid) AS tup_updated,
              pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted,
!             pg_stat_get_db_conflict_all(D.oid) AS conflicts
      FROM pg_database D;

  CREATE VIEW pg_stat_database_conflicts AS
--- 523,530 ----
              pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted,
              pg_stat_get_db_tuples_updated(D.oid) AS tup_updated,
              pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted,
!             pg_stat_get_db_conflict_all(D.oid) AS conflicts,
!             pg_stat_get_db_stat_reset_time(D.oid) AS stats_reset
      FROM pg_database D;

  CREATE VIEW pg_stat_database_conflicts AS
*************** CREATE VIEW pg_stat_bgwriter AS
*** 570,576 ****
          pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
          pg_stat_get_buf_written_backend() AS buffers_backend,
          pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync,
!         pg_stat_get_buf_alloc() AS buffers_alloc;

  CREATE VIEW pg_user_mappings AS
      SELECT
--- 571,578 ----
          pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
          pg_stat_get_buf_written_backend() AS buffers_backend,
          pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync,
!         pg_stat_get_buf_alloc() AS buffers_alloc,
!         pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;

  CREATE VIEW pg_user_mappings AS
      SELECT
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 301568f..9529ed9 100644
*** a/src/backend/postmaster/pgstat.c
--- b/src/backend/postmaster/pgstat.c
*************** pgstat_get_db_entry(Oid databaseid, bool
*** 3160,3165 ****
--- 3160,3167 ----
          result->n_conflict_bufferpin = 0;
          result->n_conflict_startup_deadlock = 0;

+         result->stat_reset_timestamp = GetCurrentTimestamp();
+
          memset(&hash_ctl, 0, sizeof(hash_ctl));
          hash_ctl.keysize = sizeof(Oid);
          hash_ctl.entrysize = sizeof(PgStat_StatTabEntry);
*************** pgstat_read_statsfile(Oid onlydb, bool p
*** 3438,3443 ****
--- 3440,3451 ----
       * load an existing statsfile.
       */
      memset(&globalStats, 0, sizeof(globalStats));
+
+     /*
+      * Set the current timestamp (will be kept only in case we can't load an
+      * existing statsfile.
+      */
+     globalStats.stat_reset_timestamp = GetCurrentTimestamp();

      /*
       * Try to open the status file. If it doesn't exist, the backends simply
*************** pgstat_recv_resetcounter(PgStat_MsgReset
*** 4052,4057 ****
--- 4060,4067 ----
      dbentry->n_tuples_deleted = 0;
      dbentry->last_autovac_time = 0;

+     dbentry->stat_reset_timestamp = GetCurrentTimestamp();
+
      memset(&hash_ctl, 0, sizeof(hash_ctl));
      hash_ctl.keysize = sizeof(Oid);
      hash_ctl.entrysize = sizeof(PgStat_StatTabEntry);
*************** pgstat_recv_resetsharedcounter(PgStat_Ms
*** 4083,4088 ****
--- 4093,4099 ----
      {
          /* Reset the global background writer statistics for the cluster. */
          memset(&globalStats, 0, sizeof(globalStats));
+         globalStats.stat_reset_timestamp = GetCurrentTimestamp();
      }

      /*
*************** pgstat_recv_resetsinglecounter(PgStat_Ms
*** 4107,4112 ****
--- 4118,4125 ----
      if (!dbentry)
          return;

+     /* Set the reset timestamp for the whole database */
+     dbentry->stat_reset_timestamp = GetCurrentTimestamp();

      /* Remove object if it exists, ignore it if not */
      if (msg->m_resettype == RESET_TABLE)
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index a95ba8b..f0d9393 100644
*** a/src/backend/utils/adt/pgstatfuncs.c
--- b/src/backend/utils/adt/pgstatfuncs.c
*************** extern Datum pg_stat_get_db_conflict_sna
*** 77,88 ****
--- 77,90 ----
  extern Datum pg_stat_get_db_conflict_bufferpin(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_db_conflict_startup_deadlock(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_db_conflict_all(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_get_db_stat_reset_time(PG_FUNCTION_ARGS);

  extern Datum pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_bgwriter_buf_written_checkpoints(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_bgwriter_buf_written_clean(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_bgwriter_maxwritten_clean(PG_FUNCTION_ARGS);
+ extern Datum pg_stat_get_bgwriter_stat_reset_time(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_buf_written_backend(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_buf_fsync_backend(PG_FUNCTION_ARGS);
  extern Datum pg_stat_get_buf_alloc(PG_FUNCTION_ARGS);
*************** pg_stat_get_db_tuples_deleted(PG_FUNCTIO
*** 1134,1139 ****
--- 1136,1159 ----

      PG_RETURN_INT64(result);
  }
+
+ Datum
+ pg_stat_get_db_stat_reset_time(PG_FUNCTION_ARGS)
+ {
+     Oid            dbid = PG_GETARG_OID(0);
+     TimestampTz result;
+     PgStat_StatDBEntry *dbentry;
+
+     if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+         result = 0;
+     else
+         result = dbentry->stat_reset_timestamp;
+
+     if (result == 0)
+         PG_RETURN_NULL();
+     else
+         PG_RETURN_TIMESTAMPTZ(result);
+ }

  Datum
  pg_stat_get_db_conflict_tablespace(PG_FUNCTION_ARGS)
*************** pg_stat_get_bgwriter_maxwritten_clean(PG
*** 1261,1266 ****
--- 1281,1292 ----
  }

  Datum
+ pg_stat_get_bgwriter_stat_reset_time(PG_FUNCTION_ARGS)
+ {
+     PG_RETURN_TIMESTAMPTZ(pgstat_fetch_global()->stat_reset_timestamp);
+ }
+
+ Datum
  pg_stat_get_buf_written_backend(PG_FUNCTION_ARGS)
  {
      PG_RETURN_INT64(pgstat_fetch_global()->buf_written_backend);
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index f8b5d4d..eb7cc86 100644
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*************** DATA(insert OID = 3069 (  pg_stat_get_db
*** 3131,3136 ****
--- 3131,3138 ----
  DESCR("statistics: recovery conflicts in database caused by buffer deadlock");
  DATA(insert OID = 3070 (  pg_stat_get_db_conflict_all PGNSP PGUID 12 1 0 0 f f f t f s 1 0 20 "26" _null_ _null_
_null__null_ pg_stat_get_db_conflict_all _null_ _null_ _null_ )); 
  DESCR("statistics: recovery conflicts in database");
+ DATA(insert OID = 3116 (  pg_stat_get_db_stat_reset_time PGNSP PGUID 12 1 0 0 f f f t f s 1 0 1184 "26" _null_ _null_
_null__null_    pg_stat_get_db_stat_reset_time _null_ _null_ _null_ )); 
+ DESCR("statistics: last reset for a database");
  DATA(insert OID = 2769 ( pg_stat_get_bgwriter_timed_checkpoints PGNSP PGUID 12 1 0 0 f f f t f s 0 0 20 "" _null_
_null__null_ _null_ pg_stat_get_bgwriter_timed_checkpoints _null_ _null_ _null_ )); 
  DESCR("statistics: number of timed checkpoints started by the bgwriter");
  DATA(insert OID = 2770 ( pg_stat_get_bgwriter_requested_checkpoints PGNSP PGUID 12 1 0 0 f f f t f s 0 0 20 "" _null_
_null__null_ _null_ pg_stat_get_bgwriter_requested_checkpoints _null_ _null_ _null_ )); 
*************** DATA(insert OID = 2772 ( pg_stat_get_bgw
*** 3141,3146 ****
--- 3143,3150 ----
  DESCR("statistics: number of buffers written by the bgwriter for cleaning dirty buffers");
  DATA(insert OID = 2773 ( pg_stat_get_bgwriter_maxwritten_clean PGNSP PGUID 12 1 0 0 f f f t f s 0 0 20 "" _null_
_null__null_ _null_ pg_stat_get_bgwriter_maxwritten_clean _null_ _null_ _null_ )); 
  DESCR("statistics: number of times the bgwriter stopped processing when it had written too many buffers while
cleaning");
+ DATA(insert OID = 3117 ( pg_stat_get_bgwriter_stat_reset_time PGNSP PGUID 12 1 0 0 f f f t f s 0 0 1184 "" _null_
_null__null_ _null_    pg_stat_get_bgwriter_stat_reset_time _null_ _null_ _null_ )); 
+ DESCR("statistics: last reset for the bgwriter");
  DATA(insert OID = 2775 ( pg_stat_get_buf_written_backend PGNSP PGUID 12 1 0 0 f f f t f s 0 0 20 "" _null_ _null_
_null__null_ pg_stat_get_buf_written_backend _null_ _null_ _null_ )); 
  DESCR("statistics: number of buffers written by backends");
  DATA(insert OID = 3063 ( pg_stat_get_buf_fsync_backend PGNSP PGUID 12 1 0 0 f f f t f s 0 0 20 "" _null_ _null_
_null__null_ pg_stat_get_buf_fsync_backend _null_ _null_ _null_ )); 
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 9f4e0ca..342f459 100644
*** a/src/include/pgstat.h
--- b/src/include/pgstat.h
*************** typedef struct PgStat_StatDBEntry
*** 508,513 ****
--- 508,514 ----
      PgStat_Counter n_conflict_snapshot;
      PgStat_Counter n_conflict_bufferpin;
      PgStat_Counter n_conflict_startup_deadlock;
+     TimestampTz stat_reset_timestamp;


      /*
*************** typedef struct PgStat_GlobalStats
*** 584,589 ****
--- 585,591 ----
      PgStat_Counter buf_written_backend;
      PgStat_Counter buf_fsync_backend;
      PgStat_Counter buf_alloc;
+     TimestampTz stat_reset_timestamp;
  } PgStat_GlobalStats;