Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit2955f1e

Browse files
committed
Add n_live_tuples and n_dead_tuples to pg_stat_all_tables.
The purpose is to allow autovacuum-esq conditional vacuuming andclustering using SQL to discover the required stats.No documentation updates required. Catalog version updated.Glen Parker
1 parent7bb1cc5 commit2955f1e

File tree

5 files changed

+49
-9
lines changed

5 files changed

+49
-9
lines changed

‎src/backend/catalog/system_views.sql

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
*
44
* Copyright (c) 1996-2006, PostgreSQL Global Development Group
55
*
6-
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.33 2006/12/06 18:06:47 neilc Exp $
6+
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.34 2007/01/02 20:59:31 momjian Exp $
77
*/
88

99
CREATEVIEWpg_rolesAS
@@ -203,10 +203,12 @@ CREATE VIEW pg_stat_all_tables AS
203203
pg_stat_get_tuples_returned(C.oid)AS seq_tup_read,
204204
sum(pg_stat_get_numscans(I.indexrelid))::bigintAS idx_scan,
205205
sum(pg_stat_get_tuples_fetched(I.indexrelid))::bigint+
206-
pg_stat_get_tuples_fetched(C.oid)AS idx_tup_fetch,
206+
pg_stat_get_tuples_fetched(C.oid)AS idx_tup_fetch,
207207
pg_stat_get_tuples_inserted(C.oid)AS n_tup_ins,
208208
pg_stat_get_tuples_updated(C.oid)AS n_tup_upd,
209209
pg_stat_get_tuples_deleted(C.oid)AS n_tup_del,
210+
pg_stat_get_live_tuples(C.oid)AS n_live_tup,
211+
pg_stat_get_dead_tuples(C.oid)AS n_dead_tup,
210212
pg_stat_get_last_vacuum_time(C.oid)as last_vacuum,
211213
pg_stat_get_last_autovacuum_time(C.oid)as last_autovacuum,
212214
pg_stat_get_last_analyze_time(C.oid)as last_analyze,

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

Lines changed: 35 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.35 2006/12/06 18:06:47 neilc Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/pgstatfuncs.c,v 1.36 2007/01/02 20:59:31 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -28,6 +28,8 @@ extern Datum pg_stat_get_tuples_fetched(PG_FUNCTION_ARGS);
2828
externDatumpg_stat_get_tuples_inserted(PG_FUNCTION_ARGS);
2929
externDatumpg_stat_get_tuples_updated(PG_FUNCTION_ARGS);
3030
externDatumpg_stat_get_tuples_deleted(PG_FUNCTION_ARGS);
31+
externDatumpg_stat_get_live_tuples(PG_FUNCTION_ARGS);
32+
externDatumpg_stat_get_dead_tuples(PG_FUNCTION_ARGS);
3133
externDatumpg_stat_get_blocks_fetched(PG_FUNCTION_ARGS);
3234
externDatumpg_stat_get_blocks_hit(PG_FUNCTION_ARGS);
3335
externDatumpg_stat_get_last_vacuum_time(PG_FUNCTION_ARGS);
@@ -152,6 +154,38 @@ pg_stat_get_tuples_deleted(PG_FUNCTION_ARGS)
152154
}
153155

154156

157+
Datum
158+
pg_stat_get_live_tuples(PG_FUNCTION_ARGS)
159+
{
160+
Oidrelid=PG_GETARG_OID(0);
161+
int64result;
162+
PgStat_StatTabEntry*tabentry;
163+
164+
if ((tabentry=pgstat_fetch_stat_tabentry(relid))==NULL)
165+
result=0;
166+
else
167+
result= (int64) (tabentry->n_live_tuples);
168+
169+
PG_RETURN_INT64(result);
170+
}
171+
172+
173+
Datum
174+
pg_stat_get_dead_tuples(PG_FUNCTION_ARGS)
175+
{
176+
Oidrelid=PG_GETARG_OID(0);
177+
int64result;
178+
PgStat_StatTabEntry*tabentry;
179+
180+
if ((tabentry=pgstat_fetch_stat_tabentry(relid))==NULL)
181+
result=0;
182+
else
183+
result= (int64) (tabentry->n_dead_tuples);
184+
185+
PG_RETURN_INT64(result);
186+
}
187+
188+
155189
Datum
156190
pg_stat_get_blocks_fetched(PG_FUNCTION_ARGS)
157191
{

‎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-2006, 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.368 2006/12/30 21:21:55 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.369 2007/01/02 20:59:32 momjian Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO200612291
56+
#defineCATALOG_VERSION_NO200701021
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2006, 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.434 2006/12/30 21:21:55 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.435 2007/01/02 20:59:32 momjian Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -2912,6 +2912,10 @@ DATA(insert OID = 1932 ( pg_stat_get_tuples_updatedPGNSP PGUID 12 f f t f s 1
29122912
DESCR("Statistics: Number of tuples updated");
29132913
DATA(insertOID=1933 (pg_stat_get_tuples_deletedPGNSPPGUID12fftfs120"26"_null__null__null_pg_stat_get_tuples_deleted-_null_ ));
29142914
DESCR("Statistics: Number of tuples deleted");
2915+
DATA(insertOID=2878 (pg_stat_get_live_tuplesPGNSPPGUID12fftfs120"26"_null__null__null_pg_stat_get_live_tuples-_null_ ));
2916+
DESCR("Statistics: Number of live tuples");
2917+
DATA(insertOID=2879 (pg_stat_get_dead_tuplesPGNSPPGUID12fftfs120"26"_null__null__null_pg_stat_get_dead_tuples-_null_ ));
2918+
DESCR("Statistics: Number of dead tuples");
29152919
DATA(insertOID=1934 (pg_stat_get_blocks_fetchedPGNSPPGUID12fftfs120"26"_null__null__null_pg_stat_get_blocks_fetched-_null_ ));
29162920
DESCR("Statistics: Number of blocks fetched");
29172921
DATA(insertOID=1935 (pg_stat_get_blocks_hitPGNSPPGUID12fftfs120"26"_null__null__null_pg_stat_get_blocks_hit-_null_ ));

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

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1289,12 +1289,12 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
12891289
pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstime AS valuntil, pg_authid.rolconfig AS useconfig FROM pg_authid WHERE pg_authid.rolcanlogin;
12901290
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));
12911291
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"]));
1292-
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_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;
1292+
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;
12931293
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;
12941294
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"]));
1295-
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.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"]));
1295+
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"]));
12961296
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"]));
1297-
pg_stat_user_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.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 <> ALL (ARRAY['pg_catalog'::"name", 'pg_toast'::"name", 'information_schema'::"name"]));
1297+
pg_stat_user_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 <> ALL (ARRAY['pg_catalog'::"name", 'pg_toast'::"name", 'information_schema'::"name"]));
12981298
pg_statio_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, (pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read, pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit 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"]));
12991299
pg_statio_all_sequences | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS blks_read, pg_stat_get_blocks_hit(c.oid) AS blks_hit FROM (pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = 'S'::"char");
13001300
pg_statio_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS heap_blks_read, pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit, (sum((pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))))::bigint AS idx_blks_read, (sum(pg_stat_get_blocks_hit(i.indexrelid)))::bigint AS idx_blks_hit, (pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read, pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit, (pg_stat_get_blocks_fetched(x.oid) - pg_stat_get_blocks_hit(x.oid)) AS tidx_blks_read, pg_stat_get_blocks_hit(x.oid) AS tidx_blks_hit FROM ((((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid))) LEFT JOIN pg_class x ON ((t.reltoastidxid = x.oid))) 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, t.oid, x.oid;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp