stat-time-reset-3.diff

*** a/doc/src/sgml/monitoring.sgml
--- b/doc/src/sgml/monitoring.sgml
***************
*** 663,668 **** postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
--- 663,677 ----
       </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 reset of statistics for this database (as a result of executing
+        <function>pg_stat_reset</function> function) or any object within it (table or index).
+       </entry>
+      </row>
+ 
+      <row>
        <entry><literal><function>pg_stat_get_numscans</function>(<type>oid</type>)</literal></entry>
        <entry><type>bigint</type></entry>
        <entry>
***************
*** 1125,1130 **** postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
--- 1134,1148 ----
         <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 reset of statistics for the background writer (as a result of executing
+         <function>pg_stat_reset_shared('bgwriter')</function>)
+       </entry>
+      </row>
  
       <row>
        <entry><literal><function>pg_stat_get_buf_written_backend()</function></literal></entry>
*** a/src/backend/catalog/system_views.sql
--- b/src/backend/catalog/system_views.sql
***************
*** 523,529 **** CREATE VIEW pg_stat_database AS
              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
***************
*** 570,576 **** CREATE VIEW pg_stat_bgwriter AS
          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
*** a/src/backend/postmaster/pgstat.c
--- b/src/backend/postmaster/pgstat.c
***************
*** 3160,3165 **** pgstat_get_db_entry(Oid databaseid, bool create)
--- 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);
***************
*** 3438,3443 **** pgstat_read_statsfile(Oid onlydb, bool permanent)
--- 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
***************
*** 4052,4057 **** pgstat_recv_resetcounter(PgStat_MsgResetcounter *msg, int len)
--- 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);
***************
*** 4083,4088 **** pgstat_recv_resetsharedcounter(PgStat_MsgResetsharedcounter *msg, int len)
--- 4093,4099 ----
  	{
  		/* Reset the global background writer statistics for the cluster. */
  		memset(&globalStats, 0, sizeof(globalStats));
+ 		globalStats.stat_reset_timestamp = GetCurrentTimestamp();
  	}
  
  	/*
***************
*** 4107,4112 **** pgstat_recv_resetsinglecounter(PgStat_MsgResetsinglecounter *msg, int len)
--- 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)
*** a/src/backend/utils/adt/pgstatfuncs.c
--- b/src/backend/utils/adt/pgstatfuncs.c
***************
*** 77,88 **** extern Datum pg_stat_get_db_conflict_snapshot(PG_FUNCTION_ARGS);
--- 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);
***************
*** 1134,1139 **** pg_stat_get_db_tuples_deleted(PG_FUNCTION_ARGS)
--- 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)
***************
*** 1261,1266 **** pg_stat_get_bgwriter_maxwritten_clean(PG_FUNCTION_ARGS)
--- 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);
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 3131,3136 **** DATA(insert OID = 3069 (  pg_stat_get_db_conflict_startup_deadlock PGNSP PGUID 1
--- 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_ ));
***************
*** 3141,3146 **** DATA(insert OID = 2772 ( pg_stat_get_bgwriter_buf_written_clean PGNSP PGUID 12 1
--- 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 = 3118 ( 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_ ));
*** a/src/include/pgstat.h
--- b/src/include/pgstat.h
***************
*** 503,514 **** typedef struct PgStat_StatDBEntry
--- 503,516 ----
  	PgStat_Counter n_tuples_updated;
  	PgStat_Counter n_tuples_deleted;
  	TimestampTz last_autovac_time;
+ 
  	PgStat_Counter n_conflict_tablespace;
  	PgStat_Counter n_conflict_lock;
  	PgStat_Counter n_conflict_snapshot;
  	PgStat_Counter n_conflict_bufferpin;
  	PgStat_Counter n_conflict_startup_deadlock;
  
+ 	TimestampTz stat_reset_timestamp;
  
  	/*
  	 * tables and functions must be last in the struct, because we don't write
***************
*** 584,589 **** typedef struct PgStat_GlobalStats
--- 586,594 ----
  	PgStat_Counter buf_written_backend;
  	PgStat_Counter buf_fsync_backend;
  	PgStat_Counter buf_alloc;
+ 	
+ 	TimestampTz stat_reset_timestamp;
+ 
  } PgStat_GlobalStats;