Re: [PATCHES] Patch(es) to expose n_live_tuples and
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.0
> -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_tuplesPGNSP 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_tuplesPGNSP 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.old2006-12-06
> 10:06:47.0 -0800
> +++ ./src/backend/catalog/system_views.sql2006-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 [EMAIL PROTECTED]
EnterpriseDBhttp://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
Re: [PATCHES] Patch(es) to expose n_live_tuples and
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.0
> -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_tuplesPGNSP 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_tuplesPGNSP 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.old2006-12-06
> 10:06:47.0 -0800
> +++ ./src/backend/catalog/system_views.sql2006-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 [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---
Re: [HACKERS] [PATCHES] Patch(es) to expose n_live_tuples and
Joshua D. Drake wrote: > > > The current terminology of live and dead is already used in many places in > > the > > documentation and in userspace; mostly around the need for maintainance of > > dead tuples within tables, reindex cleaning up dead pages, and even in the > > vacuum commands output (n dead tuples cannot be removed yet). Given this > > patch came from userland, istm people are comfortable enough with this > > terminology there is no need to change it. > > +1 OK. I will adjust any places that still use expired, and put the patch into the queue. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Patch(es) to expose n_live_tuples and
> The current terminology of live and dead is already used in many places in > the > documentation and in userspace; mostly around the need for maintainance of > dead tuples within tables, reindex cleaning up dead pages, and even in the > vacuum commands output (n dead tuples cannot be removed yet). Given this > patch came from userland, istm people are comfortable enough with this > terminology there is no need to change it. +1 > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [PATCHES] Patch(es) to expose n_live_tuples and
On Tuesday 26 December 2006 23:12, Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <[EMAIL PROTECTED]> writes:
> > > Alvaro Herrera wrote:
> > >> I'm not really convinced that Bruce's proposed names seem any better
> > >> to me. What's wrong with "dead" and "live"?
> > >
> > > In my mind, visible really means "visible to anyone", and expired means
> > > visible to no one.
> >
> > Um ... surely, visibility is in the eye of the beholder (no smiley).
> >
> > I don't have an immediate suggestion for better terminology, but IMHO
> > the whole point of visible/invisible terminology is that it depends on
> > who's looking. Dead and live seem to convey a more appropriate air
> > of finality.
> >
> > "Expired" is OK as a synonym for "dead", but there is no thesaurus
> > anywhere in the world that will suggest it as an antonym for "visible".
>
> OK, so we need new terminology and we need it to be used consistenly in
> our documentation, whatever we choose.
The current terminology of live and dead is already used in many places in the
documentation and in userspace; mostly around the need for maintainance of
dead tuples within tables, reindex cleaning up dead pages, and even in the
vacuum commands output (n dead tuples cannot be removed yet). Given this
patch came from userland, istm people are comfortable enough with this
terminology there is no need to change it.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] [PATCHES] Patch(es) to expose n_live_tuples and
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Alvaro Herrera wrote: > >> I'm not really convinced that Bruce's proposed names seem any better to > >> me. What's wrong with "dead" and "live"? > > > In my mind, visible really means "visible to anyone", and expired means > > visible to no one. > > Um ... surely, visibility is in the eye of the beholder (no smiley). > > I don't have an immediate suggestion for better terminology, but IMHO > the whole point of visible/invisible terminology is that it depends on > who's looking. Dead and live seem to convey a more appropriate air > of finality. > > "Expired" is OK as a synonym for "dead", but there is no thesaurus > anywhere in the world that will suggest it as an antonym for "visible". OK, so we need new terminology and we need it to be used consistenly in our documentation, whatever we choose. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Patch(es) to expose n_live_tuples and
Bruce Momjian <[EMAIL PROTECTED]> writes: > Alvaro Herrera wrote: >> I'm not really convinced that Bruce's proposed names seem any better to >> me. What's wrong with "dead" and "live"? > In my mind, visible really means "visible to anyone", and expired means > visible to no one. Um ... surely, visibility is in the eye of the beholder (no smiley). I don't have an immediate suggestion for better terminology, but IMHO the whole point of visible/invisible terminology is that it depends on who's looking. Dead and live seem to convey a more appropriate air of finality. "Expired" is OK as a synonym for "dead", but there is no thesaurus anywhere in the world that will suggest it as an antonym for "visible". regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Patch(es) to expose n_live_tuples and
Alvaro Herrera wrote: > Glen Parker wrote: > > [slightly reformatted for sanity] > > > Bruce Momjian wrote: > > >Is this something we want in 8.3? I am thinking visible/expired would > > >be clearer terms. > > > > I'd love to see this back patched into 8.2.1 if possible. > > > > Should I resubmit with new names? > > I'm not really convinced that Bruce's proposed names seem any better to > me. What's wrong with "dead" and "live"? With MVCC, my thought has always been that alive/dead is in the eye of the beholder/backend. For column names the user will see, I think we need to use terms that we have used in the past. If we want to move to alive/dead, fine, but we then need to make sure we use consistent terms in the documentation. In my mind, visible really means "visible to anyone", and expired means visible to no one. I don't think live/dead can be as clear as visible/expired, e.g. saying the tuple is "live to someone" seems more awkward. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Patch(es) to expose n_live_tuples and
Glen Parker wrote: > Alvaro Herrera wrote: >> As for backpatching, you already knew the answer :-) > > Nope, I had no idea this would require initdb... > > Regardless of this, our rule against backpatching new features is well founded. The stable branches are called stable for a good reason. cheers andrew ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] Patch(es) to expose n_live_tuples and
Alvaro Herrera wrote: As for backpatching, you already knew the answer :-) Nope, I had no idea this would require initdb... -Glen ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Patch(es) to expose n_live_tuples and
Glen Parker wrote: [slightly reformatted for sanity] > Bruce Momjian wrote: > >Is this something we want in 8.3? I am thinking visible/expired would > >be clearer terms. > > I'd love to see this back patched into 8.2.1 if possible. > > Should I resubmit with new names? I'm not really convinced that Bruce's proposed names seem any better to me. What's wrong with "dead" and "live"? As for backpatching, you already knew the answer :-) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Patch(es) to expose n_live_tuples and
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > On Tue, 2006-12-26 at 13:59 -0800, Glen Parker wrote: >> I'd love to see this back patched into 8.2.1 if possible. > Probably not. We typically do not introduce new features into back > releases. And since this one would require an initdb, there is exactly zero chance of it being back-patched ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Patch(es) to expose n_live_tuples and
On Tue, 2006-12-26 at 13:59 -0800, Glen Parker wrote: > I'd love to see this back patched into 8.2.1 if possible. Probably not. We typically do not introduce new features into back releases. Sincerely, Joshua D. Drake > > Should I resubmit with new names? > > -Glen > > Bruce Momjian wrote: > > Is this something we want in 8.3? I am thinking visible/expired would > > be clearer terms. > > > > --- > > > > 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. > > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Patch(es) to expose n_live_tuples and
I'd love to see this back patched into 8.2.1 if possible. Should I resubmit with new names? -Glen Bruce Momjian wrote: Is this something we want in 8.3? I am thinking visible/expired would be clearer terms. --- 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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Patch(es) to expose n_live_tuples and
Is this something we want in 8.3? I am thinking visible/expired would
be clearer terms.
---
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.0
> -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_tuplesPGNSP 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_tuplesPGNSP 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.old2006-12-06
> 10:06:47.0 -0800
> +++ ./src/backend/catalog/system_views.sql2006-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 [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
[PATCHES] Patch(es) to expose n_live_tuples and n_dead_tuples to SQL land
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.0 -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.0
-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
