diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index a19e3f0..af7b235 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -328,7 +328,8 @@ postgres: user database host FULL vacuumed manually, the last time it was vacuumed by the autovacuum daemon, the last time it was analyzed manually, @@ -764,6 +765,14 @@ postgres: user database host + + + pg_stat_get_unremovable_tuples(oid) + bigint + + Number of dead rows not removed in the table's last vacuum + + pg_stat_get_blocks_fetched(oid) diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 2253ca8..9c18dc7 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -353,6 +353,7 @@ CREATE VIEW pg_stat_all_tables AS pg_stat_get_tuples_hot_updated(C.oid) AS n_tup_hot_upd, 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_unremovable_tuples(C.oid) AS n_unremovable_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, diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c index cf8337b..140fe92 100644 --- a/src/backend/commands/vacuumlazy.c +++ b/src/backend/commands/vacuumlazy.c @@ -91,6 +91,7 @@ typedef struct LVRelStats double scanned_tuples; /* counts only tuples on scanned pages */ double old_rel_tuples; /* previous value of pg_class.reltuples */ double new_rel_tuples; /* new estimated total # of tuples */ + double unremovable_tuples; /* count of dead tuples not yet removable */ BlockNumber pages_removed; double tuples_deleted; BlockNumber nonempty_pages; /* actually, last nonempty page + 1 */ @@ -245,7 +246,8 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt, /* report results to the stats collector, too */ pgstat_report_vacuum(RelationGetRelid(onerel), onerel->rd_rel->relisshared, - new_rel_tuples); + new_rel_tuples, + vacrelstats->unremovable_tuples); /* and log the action if appropriate */ if (IsAutoVacuumWorkerProcess() && Log_autovacuum_min_duration >= 0) @@ -829,6 +831,7 @@ lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats, /* save stats for use later */ vacrelstats->scanned_tuples = num_tuples; vacrelstats->tuples_deleted = tups_vacuumed; + vacrelstats->unremovable_tuples = nkeep; /* now we can compute the new value for pg_class.reltuples */ vacrelstats->new_rel_tuples = vac_estimate_reltuples(onerel, false, diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c index 9132db7..40d1107 100644 --- a/src/backend/postmaster/pgstat.c +++ b/src/backend/postmaster/pgstat.c @@ -1251,7 +1251,7 @@ pgstat_report_autovac(Oid dboid) * --------- */ void -pgstat_report_vacuum(Oid tableoid, bool shared, PgStat_Counter tuples) +pgstat_report_vacuum(Oid tableoid, bool shared, PgStat_Counter tuples, PgStat_Counter m_unremovable_tuples) { PgStat_MsgVacuum msg; @@ -1264,6 +1264,7 @@ pgstat_report_vacuum(Oid tableoid, bool shared, PgStat_Counter tuples) msg.m_autovacuum = IsAutoVacuumWorkerProcess(); msg.m_vacuumtime = GetCurrentTimestamp(); msg.m_tuples = tuples; + msg.m_unremovable_tuples = m_unremovable_tuples; pgstat_send(&msg, sizeof(msg)); } @@ -4202,6 +4203,7 @@ pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len) tabentry = pgstat_get_tab_entry(dbentry, msg->m_tableoid, true); tabentry->n_live_tuples = msg->m_tuples; + tabentry->n_unremovable_tuples = msg->m_unremovable_tuples; /* Resetting dead_tuples to 0 is an approximation ... */ tabentry->n_dead_tuples = 0; diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 7792b33..fb60fc5 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -33,6 +33,7 @@ extern Datum pg_stat_get_tuples_deleted(PG_FUNCTION_ARGS); extern Datum pg_stat_get_tuples_hot_updated(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_unremovable_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); @@ -256,6 +257,22 @@ pg_stat_get_dead_tuples(PG_FUNCTION_ARGS) PG_RETURN_INT64(result); } +Datum +pg_stat_get_unremovable_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_unremovable_tuples); + + PG_RETURN_INT64(result); +} + + Datum pg_stat_get_blocks_fetched(PG_FUNCTION_ARGS) diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index f3c8bb4..950f1f2 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201109071 +#define CATALOG_VERSION_NO 201110031 #endif diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 96f43fe..69f6415 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -2526,6 +2526,8 @@ DATA(insert OID = 2878 ( pg_stat_get_live_tuples PGNSP PGUID 12 1 0 0 0 f f f t DESCR("statistics: number of live tuples"); DATA(insert OID = 2879 ( pg_stat_get_dead_tuples PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_dead_tuples _null_ _null_ _null_ )); DESCR("statistics: number of dead tuples"); +DATA(insert OID = 3122 ( pg_stat_get_unremovable_tuples PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_unremovable_tuples _null_ _null_ _null_ )); +DESCR("statistics: number of dead tuples not yet removable"); DATA(insert OID = 1934 ( pg_stat_get_blocks_fetched PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_blocks_fetched _null_ _null_ _null_ )); DESCR("statistics: number of blocks fetched"); DATA(insert OID = 1935 ( pg_stat_get_blocks_hit PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_blocks_hit _null_ _null_ _null_ )); diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 20c4d43..e1b082e 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -326,6 +326,7 @@ typedef struct PgStat_MsgVacuum bool m_autovacuum; TimestampTz m_vacuumtime; PgStat_Counter m_tuples; + PgStat_Counter m_unremovable_tuples; } PgStat_MsgVacuum; @@ -539,6 +540,7 @@ typedef struct PgStat_StatTabEntry PgStat_Counter n_live_tuples; PgStat_Counter n_dead_tuples; + PgStat_Counter n_unremovable_tuples; PgStat_Counter changes_since_analyze; PgStat_Counter blocks_fetched; @@ -706,7 +708,7 @@ extern void pgstat_reset_single_counter(Oid objectid, PgStat_Single_Reset_Type t extern void pgstat_report_autovac(Oid dboid); extern void pgstat_report_vacuum(Oid tableoid, bool shared, - PgStat_Counter tuples); + PgStat_Counter tuples, PgStat_Counter unremovable_tuples); extern void pgstat_report_analyze(Relation rel, PgStat_Counter livetuples, PgStat_Counter deadtuples);