Skip to content

Commit 51d7741

Browse files
mhagandermhagander
committed
Add new columns for tuple statistics on a database level to
pg_stat_database.
1 parent c4fdfb8 commit 51d7741

File tree

8 files changed

+172
-12
lines changed

8 files changed

+172
-12
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 44 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.46 2007/02/07 23:11:29 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.47 2007/03/16 17:57:35 mha Exp $ -->
22

33
<chapter id="monitoring">
44
<title>Monitoring Database Activity</title>
@@ -265,8 +265,9 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
265265
<entry>One row per database, showing database OID, database name,
266266
number of active server processes connected to that database,
267267
number of transactions committed and rolled back in that database,
268-
total disk blocks read, and total buffer hits (i.e., block
269-
read requests avoided by finding the block already in buffer cache).
268+
total disk blocks read, total buffer hits (i.e., block
269+
read requests avoided by finding the block already in buffer cache),
270+
number of rows returned, inserted, updated and deleted.
270271
</entry>
271272
</row>
272273

@@ -502,6 +503,46 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
502503
</entry>
503504
</row>
504505

506+
<row>
507+
<entry><literal><function>pg_stat_get_db_tuples_returned</function>(<type>oid</type>)</literal></entry>
508+
<entry><type>bigint</type></entry>
509+
<entry>
510+
Number of tuples returned for database
511+
</entry>
512+
</row>
513+
514+
<row>
515+
<entry><literal><function>pg_stat_get_db_tuples_fetched</function>(<type>oid</type>)</literal></entry>
516+
<entry><type>bigint</type></entry>
517+
<entry>
518+
Number of tuples fetched for database
519+
</entry>
520+
</row>
521+
522+
<row>
523+
<entry><literal><function>pg_stat_get_db_tuples_inserted</function>(<type>oid</type>)</literal></entry>
524+
<entry><type>bigint</type></entry>
525+
<entry>
526+
Number of tuples inserted in database
527+
</entry>
528+
</row>
529+
530+
<row>
531+
<entry><literal><function>pg_stat_get_db_tuples_updated</function>(<type>oid</type>)</literal></entry>
532+
<entry><type>bigint</type></entry>
533+
<entry>
534+
Number of tuples updated in database
535+
</entry>
536+
</row>
537+
538+
<row>
539+
<entry><literal><function>pg_stat_get_db_tuples_deleted</function>(<type>oid</type>)</literal></entry>
540+
<entry><type>bigint</type></entry>
541+
<entry>
542+
Number of tuples deleted in database
543+
</entry>
544+
</row>
545+
505546
<row>
506547
<entry><literal><function>pg_stat_get_numscans</function>(<type>oid</type>)</literal></entry>
507548
<entry><type>bigint</type></entry>

‎src/backend/catalog/system_views.sql

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
*
44
* Copyright (c) 1996-2007, PostgreSQL Global Development Group
55
*
6-
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.35 2007/01/05 22:19:25 momjian Exp $
6+
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.36 2007/03/16 17:57:36 mha Exp $
77
*/
88

99
CREATE VIEW pg_roles AS
@@ -357,5 +357,10 @@ CREATE VIEW pg_stat_database AS
357357
pg_stat_get_db_xact_rollback(D.oid) AS xact_rollback,
358358
pg_stat_get_db_blocks_fetched(D.oid) -
359359
pg_stat_get_db_blocks_hit(D.oid) AS blks_read,
360-
pg_stat_get_db_blocks_hit(D.oid) AS blks_hit
360+
pg_stat_get_db_blocks_hit(D.oid) AS blks_hit,
361+
pg_stat_get_db_tuples_returned(D.oid) AS tup_returned,
362+
pg_stat_get_db_tuples_fetched(D.oid) AS tup_fetched,
363+
pg_stat_get_db_tuples_inserted(D.oid) AS tup_inserted,
364+
pg_stat_get_db_tuples_updated(D.oid) AS tup_updated,
365+
pg_stat_get_db_tuples_deleted(D.oid) AS tup_deleted
361366
FROM pg_database D;

‎src/backend/postmaster/pgstat.c

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@
1313
*
1414
* Copyright (c) 2001-2007, PostgreSQL Global Development Group
1515
*
16-
* $PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.148 2007/03/01 20:06:56 tgl Exp $
16+
* $PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.149 2007/03/16 17:57:36 mha Exp $
1717
* ----------
1818
*/
1919
#include "postgres.h"
@@ -1970,6 +1970,11 @@ pgstat_get_db_entry(Oid databaseid, bool create)
19701970
result->n_xact_rollback = 0;
19711971
result->n_blocks_fetched = 0;
19721972
result->n_blocks_hit = 0;
1973+
result->n_tuples_returned = 0;
1974+
result->n_tuples_fetched = 0;
1975+
result->n_tuples_inserted = 0;
1976+
result->n_tuples_updated = 0;
1977+
result->n_tuples_deleted = 0;
19731978
result->last_autovac_time = 0;
19741979

19751980
memset(&hash_ctl, 0, sizeof(hash_ctl));
@@ -2413,6 +2418,15 @@ pgstat_recv_tabstat(PgStat_MsgTabstat *msg, int len)
24132418
tabentry->blocks_hit += tabmsg[i].t_blocks_hit;
24142419
}
24152420

2421+
/*
2422+
* Add table stats to the database entry.
2423+
*/
2424+
dbentry->n_tuples_returned += tabmsg[i].t_tuples_returned;
2425+
dbentry->n_tuples_fetched += tabmsg[i].t_tuples_fetched;
2426+
dbentry->n_tuples_inserted += tabmsg[i].t_tuples_inserted;
2427+
dbentry->n_tuples_updated += tabmsg[i].t_tuples_updated;
2428+
dbentry->n_tuples_deleted += tabmsg[i].t_tuples_deleted;
2429+
24162430
/*
24172431
* And add the block IO to the database entry.
24182432
*/

‎src/backend/utils/adt/pgstatfuncs.c

Lines changed: 86 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/pgstatfuncs.c,v 1.39 2007/02/27 23:48:08 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/pgstatfuncs.c,v 1.40 2007/03/16 17:57:36 mha Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -55,6 +55,11 @@ extern Datum pg_stat_get_db_xact_commit(PG_FUNCTION_ARGS);
5555
extern Datum pg_stat_get_db_xact_rollback(PG_FUNCTION_ARGS);
5656
extern Datum pg_stat_get_db_blocks_fetched(PG_FUNCTION_ARGS);
5757
extern Datum pg_stat_get_db_blocks_hit(PG_FUNCTION_ARGS);
58+
extern Datum pg_stat_get_db_tuples_returned(PG_FUNCTION_ARGS);
59+
extern Datum pg_stat_get_db_tuples_fetched(PG_FUNCTION_ARGS);
60+
extern Datum pg_stat_get_db_tuples_inserted(PG_FUNCTION_ARGS);
61+
extern Datum pg_stat_get_db_tuples_updated(PG_FUNCTION_ARGS);
62+
extern Datum pg_stat_get_db_tuples_deleted(PG_FUNCTION_ARGS);
5863

5964
extern Datum pg_stat_clear_snapshot(PG_FUNCTION_ARGS);
6065
extern Datum pg_stat_reset(PG_FUNCTION_ARGS);
@@ -672,6 +677,86 @@ pg_stat_get_db_blocks_hit(PG_FUNCTION_ARGS)
672677
}
673678

674679

680+
Datum
681+
pg_stat_get_db_tuples_returned(PG_FUNCTION_ARGS)
682+
{
683+
Oid dbid = PG_GETARG_OID(0);
684+
int64 result;
685+
PgStat_StatDBEntry *dbentry;
686+
687+
if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
688+
result = 0;
689+
else
690+
result = (int64) (dbentry->n_tuples_returned);
691+
692+
PG_RETURN_INT64(result);
693+
}
694+
695+
696+
Datum
697+
pg_stat_get_db_tuples_fetched(PG_FUNCTION_ARGS)
698+
{
699+
Oid dbid = PG_GETARG_OID(0);
700+
int64 result;
701+
PgStat_StatDBEntry *dbentry;
702+
703+
if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
704+
result = 0;
705+
else
706+
result = (int64) (dbentry->n_tuples_fetched);
707+
708+
PG_RETURN_INT64(result);
709+
}
710+
711+
712+
Datum
713+
pg_stat_get_db_tuples_inserted(PG_FUNCTION_ARGS)
714+
{
715+
Oid dbid = PG_GETARG_OID(0);
716+
int64 result;
717+
PgStat_StatDBEntry *dbentry;
718+
719+
if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
720+
result = 0;
721+
else
722+
result = (int64) (dbentry->n_tuples_inserted);
723+
724+
PG_RETURN_INT64(result);
725+
}
726+
727+
728+
Datum
729+
pg_stat_get_db_tuples_updated(PG_FUNCTION_ARGS)
730+
{
731+
Oid dbid = PG_GETARG_OID(0);
732+
int64 result;
733+
PgStat_StatDBEntry *dbentry;
734+
735+
if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
736+
result = 0;
737+
else
738+
result = (int64) (dbentry->n_tuples_updated);
739+
740+
PG_RETURN_INT64(result);
741+
}
742+
743+
744+
Datum
745+
pg_stat_get_db_tuples_deleted(PG_FUNCTION_ARGS)
746+
{
747+
Oid dbid = PG_GETARG_OID(0);
748+
int64 result;
749+
PgStat_StatDBEntry *dbentry;
750+
751+
if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
752+
result = 0;
753+
else
754+
result = (int64) (dbentry->n_tuples_deleted);
755+
756+
PG_RETURN_INT64(result);
757+
}
758+
759+
675760
/* Discard the active statistics snapshot */
676761
Datum
677762
pg_stat_clear_snapshot(PG_FUNCTION_ARGS)

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.389 2007/03/02 00:48:44 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.390 2007/03/16 17:57:36 mha Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 200703011
56+
#define CATALOG_VERSION_NO 200703161
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 11 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.447 2007/03/03 19:52:46 momjian Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.448 2007/03/16 17:57:36 mha Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -2974,6 +2974,16 @@ DATA(insert OID = 1944 ( pg_stat_get_db_blocks_fetched PGNSP PGUID 12 1 0 f f t
29742974
DESCR("Statistics: Blocks fetched for database");
29752975
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_ ));
29762976
DESCR("Statistics: Blocks found in cache for database");
2977+
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_ ));
2978+
DESCR("Statistics: Tuples returned for database");
2979+
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_ ));
2980+
DESCR("Statistics: Tuples fetched for database");
2981+
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_ ));
2982+
DESCR("Statistics: Tuples inserted in database");
2983+
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_ ));
2984+
DESCR("Statistics: Tuples updated in database");
2985+
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_ ));
2986+
DESCR("Statistics: Tuples deleted in database");
29772987
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_ ));
29782988
DESCR("Statistics: Discard current transaction's statistics snapshot");
29792989
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_ ));

‎src/include/pgstat.h

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -5,7 +5,7 @@
55
*
66
* Copyright (c) 2001-2007, PostgreSQL Global Development Group
77
*
8-
* $PostgreSQL: pgsql/src/include/pgstat.h,v 1.54 2007/02/09 16:12:19 tgl Exp $
8+
* $PostgreSQL: pgsql/src/include/pgstat.h,v 1.55 2007/03/16 17:57:36 mha Exp $
99
* ----------
1010
*/
1111
#ifndef PGSTAT_H
@@ -251,6 +251,11 @@ typedef struct PgStat_StatDBEntry
251251
PgStat_Counter n_xact_rollback;
252252
PgStat_Counter n_blocks_fetched;
253253
PgStat_Counter n_blocks_hit;
254+
PgStat_Counter n_tuples_returned;
255+
PgStat_Counter n_tuples_fetched;
256+
PgStat_Counter n_tuples_inserted;
257+
PgStat_Counter n_tuples_updated;
258+
PgStat_Counter n_tuples_deleted;
254259
TimestampTz last_autovac_time;
255260

256261
/*

‎src/test/regress/expected/rules.out

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1292,7 +1292,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
12921292
pg_stat_activity | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid) AS usesysid, u.rolname AS usename, pg_stat_get_backend_activity(s.backendid) AS current_query, pg_stat_get_backend_waiting(s.backendid) AS waiting, pg_stat_get_backend_txn_start(s.backendid) AS txn_start, pg_stat_get_backend_activity_start(s.backendid) AS query_start, pg_stat_get_backend_start(s.backendid) AS backend_start, pg_stat_get_backend_client_addr(s.backendid) AS client_addr, pg_stat_get_backend_client_port(s.backendid) AS client_port FROM pg_database d, (SELECT pg_stat_get_backend_idset() AS backendid) s, pg_authid u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid) = u.oid));
12931293
pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"]));
12941294
pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname;
1295-
pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, 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;
1295+
pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, 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;
12961296
pg_stat_sys_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE (pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::"name", 'pg_toast'::"name", 'information_schema'::"name"]));
12971297
pg_stat_sys_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.schemaname, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del, pg_stat_all_tables.n_live_tup, pg_stat_all_tables.n_dead_tup, pg_stat_all_tables.last_vacuum, pg_stat_all_tables.last_autovacuum, pg_stat_all_tables.last_analyze, pg_stat_all_tables.last_autoanalyze FROM pg_stat_all_tables WHERE (pg_stat_all_tables.schemaname = ANY (ARRAY['pg_catalog'::"name", 'pg_toast'::"name", 'information_schema'::"name"]));
12981298
pg_stat_user_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE (pg_stat_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::"name", 'pg_toast'::"name", 'information_schema'::"name"]));

0 commit comments

Comments
 (0)