Add new columns for tuple statistics on a database level to

pg_stat_database.

3 files changed, 108 insertions, 4 deletions

diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 589f8ebc50a..5948db162b7 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -3,7 +3,7 @@
  *
  * Copyright (c) 1996-2007, PostgreSQL Global Development Group
  *
- * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.35 2007/01/05 22:19:25 momjian Exp $
+ * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.36 2007/03/16 17:57:36 mha Exp $
  */
 
 CREATE VIEW pg_roles AS 
@@ -357,5 +357,10 @@ CREATE VIEW pg_stat_database AS
             pg_stat_get_db_xact_rollback(D.oid) AS xact_rollback, 
             pg_stat_get_db_blocks_fetched(D.oid) - 
                     pg_stat_get_db_blocks_hit(D.oid) AS blks_read, 
-            pg_stat_get_db_blocks_hit(D.oid) AS blks_hit 
+            pg_stat_get_db_blocks_hit(D.oid) AS blks_hit,
+            pg_stat_get_db_tuples_returned(D.oid) AS tup_returned,
+            pg_stat_get_db_tuples_fetched(D.oid) AS tup_fetched,
+            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
     FROM pg_database D;
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 486dc6e48bf..378e165707f 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -13,7 +13,7 @@
  *
  *	Copyright (c) 2001-2007, PostgreSQL Global Development Group
  *
- *	$PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.148 2007/03/01 20:06:56 tgl Exp $
+ *	$PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.149 2007/03/16 17:57:36 mha Exp $
  * ----------
  */
 #include "postgres.h"
@@ -1970,6 +1970,11 @@ pgstat_get_db_entry(Oid databaseid, bool create)
 		result->n_xact_rollback = 0;
 		result->n_blocks_fetched = 0;
 		result->n_blocks_hit = 0;
+		result->n_tuples_returned = 0;
+		result->n_tuples_fetched = 0;
+		result->n_tuples_inserted = 0;
+		result->n_tuples_updated = 0;
+		result->n_tuples_deleted = 0;
 		result->last_autovac_time = 0;
 
 		memset(&hash_ctl, 0, sizeof(hash_ctl));
@@ -2414,6 +2419,15 @@ pgstat_recv_tabstat(PgStat_MsgTabstat *msg, int len)
 		}
 
 		/*
+		 * Add table stats to the database entry.
+		 */
+		dbentry->n_tuples_returned += tabmsg[i].t_tuples_returned;
+		dbentry->n_tuples_fetched += tabmsg[i].t_tuples_fetched;
+		dbentry->n_tuples_inserted += tabmsg[i].t_tuples_inserted;
+		dbentry->n_tuples_updated += tabmsg[i].t_tuples_updated;
+		dbentry->n_tuples_deleted += tabmsg[i].t_tuples_deleted;
+
+		/*
 		 * And add the block IO to the database entry.
 		 */
 		dbentry->n_blocks_fetched += tabmsg[i].t_blocks_fetched;
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 50624d6e728..831fe6b8899 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/utils/adt/pgstatfuncs.c,v 1.39 2007/02/27 23:48:08 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/adt/pgstatfuncs.c,v 1.40 2007/03/16 17:57:36 mha Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -55,6 +55,11 @@ extern Datum pg_stat_get_db_xact_commit(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_db_xact_rollback(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_db_blocks_fetched(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_db_blocks_hit(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_db_tuples_returned(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_db_tuples_fetched(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_db_tuples_inserted(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_db_tuples_updated(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_db_tuples_deleted(PG_FUNCTION_ARGS);
 
 extern Datum pg_stat_clear_snapshot(PG_FUNCTION_ARGS);
 extern Datum pg_stat_reset(PG_FUNCTION_ARGS);
@@ -672,6 +677,86 @@ pg_stat_get_db_blocks_hit(PG_FUNCTION_ARGS)
 }
 
 
+Datum
+pg_stat_get_db_tuples_returned(PG_FUNCTION_ARGS)
+{
+	Oid			dbid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatDBEntry *dbentry;
+
+	if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+		result = 0;
+	else
+		result = (int64) (dbentry->n_tuples_returned);
+
+	PG_RETURN_INT64(result);
+}
+
+
+Datum
+pg_stat_get_db_tuples_fetched(PG_FUNCTION_ARGS)
+{
+	Oid			dbid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatDBEntry *dbentry;
+
+	if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+		result = 0;
+	else
+		result = (int64) (dbentry->n_tuples_fetched);
+
+	PG_RETURN_INT64(result);
+}
+
+
+Datum
+pg_stat_get_db_tuples_inserted(PG_FUNCTION_ARGS)
+{
+	Oid			dbid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatDBEntry *dbentry;
+
+	if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+		result = 0;
+	else
+		result = (int64) (dbentry->n_tuples_inserted);
+
+	PG_RETURN_INT64(result);
+}
+
+
+Datum
+pg_stat_get_db_tuples_updated(PG_FUNCTION_ARGS)
+{
+	Oid			dbid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatDBEntry *dbentry;
+
+	if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+		result = 0;
+	else
+		result = (int64) (dbentry->n_tuples_updated);
+
+	PG_RETURN_INT64(result);
+}
+
+
+Datum
+pg_stat_get_db_tuples_deleted(PG_FUNCTION_ARGS)
+{
+	Oid			dbid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatDBEntry *dbentry;
+
+	if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+		result = 0;
+	else
+		result = (int64) (dbentry->n_tuples_deleted);
+
+	PG_RETURN_INT64(result);
+}
+
+
 /* Discard the active statistics snapshot */
 Datum
 pg_stat_clear_snapshot(PG_FUNCTION_ARGS)