Skip site navigation (1) Skip section navigation (2)

Re: Patch(es) to expose n_live_tuples and

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Glen Parker <glenebob(at)nwlink(dot)com>
Cc: Postgresql Patches <pgsql-patches(at)postgresql(dot)org>
Subject: Re: Patch(es) to expose n_live_tuples and
Date: 2007-01-02 20:59:34
Message-ID: 200701022059.l02KxYF23245@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
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))::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

-- 
  Bruce Momjian   bruce(at)momjian(dot)us
  EnterpriseDB    http://www.enterprisedb.com

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

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2007-01-02 21:11:37
Subject: Re: [HACKERS] Recent SIGSEGV failures in buildfarm HEAD
Previous:From: Andrew DunstanDate: 2007-01-02 20:54:59
Subject: Re: [HACKERS] Recent SIGSEGV failures in buildfarm HEAD

pgsql-patches by date

Next:From: Tom LaneDate: 2007-01-02 21:11:37
Subject: Re: [HACKERS] Recent SIGSEGV failures in buildfarm HEAD
Previous:From: Andrew DunstanDate: 2007-01-02 20:54:59
Subject: Re: [HACKERS] Recent SIGSEGV failures in buildfarm HEAD

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group