This patch consists of two c functions to expose n_live_tuples and
n_dead_tuples, SQL functions to expose them to SQL land, and corresponding fields added to pg_stat_all_tables.


This has been discussed in general. The purpose is to allow autovacuum-esq conditional vacuuming and clustering using SQL to discover the required stats.

-Glen Parker
--- ./src/backend/utils/adt/pgstatfuncs.c.old   2006-12-20 17:01:30.585852856 
-0800
+++ ./src/backend/utils/adt/pgstatfuncs.c       2006-12-20 17:00:58.570719896 
-0800
@@ -28,6 +28,8 @@
 extern Datum pg_stat_get_tuples_inserted(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_tuples_updated(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_tuples_deleted(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_live_tuples(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_dead_tuples(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_blocks_fetched(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_blocks_hit(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_last_vacuum_time(PG_FUNCTION_ARGS);
@@ -153,6 +155,38 @@
 
 
 Datum
+pg_stat_get_live_tuples(PG_FUNCTION_ARGS)
+{ 
+       Oid             relid = PG_GETARG_OID(0);
+       int64   result;
+       PgStat_StatTabEntry     *tabentry;
+ 
+       if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
+               result = 0;
+       else
+               result = (int64) (tabentry->n_live_tuples);
+        
+       PG_RETURN_INT64(result);
+}
+
+        
+Datum
+pg_stat_get_dead_tuples(PG_FUNCTION_ARGS)
+{
+       Oid             relid = PG_GETARG_OID(0);
+       int64   result;
+       PgStat_StatTabEntry     *tabentry;
+
+       if ((tabentry = pgstat_fetch_stat_tabentry(relid)) == NULL)
+               result = 0;
+       else
+               result = (int64) (tabentry->n_dead_tuples);
+        
+       PG_RETURN_INT64(result);
+}
+
+
+Datum
 pg_stat_get_blocks_fetched(PG_FUNCTION_ARGS)
 {
        Oid                     relid = PG_GETARG_OID(0);


--- ./src/include/catalog/pg_proc.h.old 2006-12-06 10:06:47.000000000 -0800
+++ ./src/include/catalog/pg_proc.h     2006-12-20 17:09:32.874533832 -0800
@@ -2872,6 +2872,10 @@
 DESCR("Statistics: Number of tuples updated");
 DATA(insert OID = 1933 (  pg_stat_get_tuples_deleted   PGNSP PGUID 12 f f t f 
s 1 20 "26" _null_ _null_ _null_ pg_stat_get_tuples_deleted - _null_ ));
 DESCR("Statistics: Number of tuples deleted");
+DATA(insert OID = 2878 (  pg_stat_get_live_tuples      PGNSP PGUID 12 f f t f 
s 1 20 "26" _null_ _null_ _null_ pg_stat_get_live_tuples - _null_ ));
+DESCR("Statistics: Number of live tuples");
+DATA(insert OID = 2879 (  pg_stat_get_dead_tuples      PGNSP PGUID 12 f f t f 
s 1 20 "26" _null_ _null_ _null_ pg_stat_get_dead_tuples - _null_ ));
+DESCR("Statistics: Number of dead tuples");
 DATA(insert OID = 1934 (  pg_stat_get_blocks_fetched   PGNSP PGUID 12 f f t f 
s 1 20 "26" _null_ _null_ _null_ pg_stat_get_blocks_fetched - _null_ ));
 DESCR("Statistics: Number of blocks fetched");
 DATA(insert OID = 1935 (  pg_stat_get_blocks_hit               PGNSP PGUID 12 
f f t f s 1 20 "26" _null_ _null_ _null_ pg_stat_get_blocks_hit - _null_ ));


--- ./src/backend/catalog/system_views.sql.old  2006-12-06 10:06:47.000000000 
-0800
+++ ./src/backend/catalog/system_views.sql      2006-12-20 17:13:03.036584344 
-0800
@@ -203,10 +203,12 @@
             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_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,
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

Reply via email to