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.sgml7 Feb 2007 23:11:29 - 1.46
--- doc/src/sgml/monitoring.sgml15 Mar 2007 21:28:31 -
***
*** 265,272
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).
--- 265,273
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.
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.sql5 Jan 2007 22:19:25 -
1.35
--- src/backend/catalog/system_views.sql15 Mar 2007 21:28:31 -
***
*** 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 - 1.148
--- src/backend/postmaster/pgstat.c 15 Mar 2007 21:28:32 -
***
*** 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 - 1.39
--- src/backend/utils/adt/pgstatfuncs.c 15 Mar 2007 21:28:32 -
***
*** 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);
exte
