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))::big int +
- 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

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.

---------------------------------------------------------------------------

Glen Parker wrote:
> 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))::big int +
> - 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

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Patch applied. Thanks.

---------------------------------------------------------------------------

Glen Parker wrote:
> 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))::big int +
> - 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

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org