Here's a patch to add some more data to pg_stat_database. Turns out it
was a lot easier than I thought - especially after reading Alvaros mail.
Luckily I think he was wrong this time ;-)
Does this seem like a reasonable way to do it, or did I miss something
completely?
//Magnus
Index: doc/src/sgml/monitoring.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/monitoring.sgml,v
retrieving revision 1.46
diff -c -r1.46 monitoring.sgml
*** doc/src/sgml/monitoring.sgml 7 Feb 2007 23:11:29 -0000 1.46
--- doc/src/sgml/monitoring.sgml 15 Mar 2007 21:28:31 -0000
***************
*** 265,272 ****
<entry>One row per database, showing database OID, database name,
number of active server processes connected to that database,
number of transactions committed and rolled back in that database,
! total disk blocks read, and total buffer hits (i.e., block
! read requests avoided by finding the block already in buffer cache).
</entry>
</row>
--- 265,273 ----
<entry>One row per database, showing database OID, database name,
number of active server processes connected to that database,
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, inserted, updated and deleted.
</entry>
</row>
Index: src/backend/catalog/system_views.sql
===================================================================
RCS file: /cvsroot/pgsql/src/backend/catalog/system_views.sql,v
retrieving revision 1.35
diff -c -r1.35 system_views.sql
*** src/backend/catalog/system_views.sql 5 Jan 2007 22:19:25 -0000
1.35
--- src/backend/catalog/system_views.sql 15 Mar 2007 21:28:31 -0000
***************
*** 357,361 ****
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
FROM pg_database D;
--- 357,366 ----
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_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;
Index: src/backend/postmaster/pgstat.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/postmaster/pgstat.c,v
retrieving revision 1.148
diff -c -r1.148 pgstat.c
*** src/backend/postmaster/pgstat.c 1 Mar 2007 20:06:56 -0000 1.148
--- src/backend/postmaster/pgstat.c 15 Mar 2007 21:28:32 -0000
***************
*** 1970,1975 ****
--- 1970,1980 ----
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,2419 ****
--- 2419,2433 ----
}
/*
+ * 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;
Index: src/backend/utils/adt/pgstatfuncs.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/pgstatfuncs.c,v
retrieving revision 1.39
diff -c -r1.39 pgstatfuncs.c
*** src/backend/utils/adt/pgstatfuncs.c 27 Feb 2007 23:48:08 -0000 1.39
--- src/backend/utils/adt/pgstatfuncs.c 15 Mar 2007 21:28:32 -0000
***************
*** 55,60 ****
--- 55,65 ----
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,677 ****
--- 677,762 ----
}
+ 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)
Index: src/include/pgstat.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/pgstat.h,v
retrieving revision 1.54
diff -c -r1.54 pgstat.h
*** src/include/pgstat.h 9 Feb 2007 16:12:19 -0000 1.54
--- src/include/pgstat.h 15 Mar 2007 21:28:34 -0000
***************
*** 251,256 ****
--- 251,261 ----
PgStat_Counter n_xact_rollback;
PgStat_Counter n_blocks_fetched;
PgStat_Counter n_blocks_hit;
+ PgStat_Counter n_tuples_returned;
+ PgStat_Counter n_tuples_fetched;
+ PgStat_Counter n_tuples_inserted;
+ PgStat_Counter n_tuples_updated;
+ PgStat_Counter n_tuples_deleted;
TimestampTz last_autovac_time;
/*
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.447
diff -c -r1.447 pg_proc.h
*** src/include/catalog/pg_proc.h 3 Mar 2007 19:52:46 -0000 1.447
--- src/include/catalog/pg_proc.h 15 Mar 2007 21:28:35 -0000
***************
*** 2974,2979 ****
--- 2974,2989 ----
DESCR("Statistics: Blocks fetched for database");
DATA(insert OID = 1945 ( pg_stat_get_db_blocks_hit PGNSP PGUID 12
1 0 f f t f s 1 20 "26" _null_ _null_ _null_ pg_stat_get_db_blocks_hit - _null_
));
DESCR("Statistics: Blocks found in cache for database");
+ DATA(insert OID = 2758 ( pg_stat_get_db_tuples_returned PGNSP PGUID 12 1 0 f
f t f s 1 20 "26" _null_ _null_ _null_ pg_stat_get_db_tuples_returned - _null_
));
+ DESCR("Statistics: Tuples returned for database");
+ DATA(insert OID = 2759 ( pg_stat_get_db_tuples_fetched PGNSP PGUID 12 1 0 f
f t f s 1 20 "26" _null_ _null_ _null_ pg_stat_get_db_tuples_fetched - _null_
));
+ DESCR("Statistics: Tuples fetched for database");
+ DATA(insert OID = 2760 ( pg_stat_get_db_tuples_inserted PGNSP PGUID 12 1 0 f
f t f s 1 20 "26" _null_ _null_ _null_ pg_stat_get_db_tuples_inserted - _null_
));
+ DESCR("Statistics: Tuples inserted in database");
+ DATA(insert OID = 2761 ( pg_stat_get_db_tuples_updated PGNSP PGUID 12 1 0 f
f t f s 1 20 "26" _null_ _null_ _null_ pg_stat_get_db_tuples_updated - _null_
));
+ DESCR("Statistics: Tuples updated in database");
+ DATA(insert OID = 2762 ( pg_stat_get_db_tuples_deleted PGNSP PGUID 12 1 0 f
f t f s 1 20 "26" _null_ _null_ _null_ pg_stat_get_db_tuples_deleted - _null_
));
+ DESCR("Statistics: Tuples deleted indatabase");
DATA(insert OID = 2230 ( pg_stat_clear_snapshot PGNSP PGUID 12
1 0 f f f f v 0 2278 "" _null_ _null_ _null_ pg_stat_clear_snapshot -
_null_ ));
DESCR("Statistics: Discard current transaction's statistics snapshot");
DATA(insert OID = 2274 ( pg_stat_reset
PGNSP PGUID 12 1 0 f f f f v 0 2278 "" _null_ _null_ _null_ pg_stat_reset -
_null_ ));
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
