Hi all,

I spent a bit of today diagnosing a problem where long held transactions were 
preventing auto vacuum from doing its job.  Eventually I had set 
log_autovacuum_min_duration to 0 to see what was going on.  Unfortunately it 
wasn't until I tried a VACUUM VERBOSE that I found there were dead tuples not 
being removed.  Had the unremoveable tuple count been included in the 
autovacuum log message life would have been a tiny bit easier.

I've been meaning for a while to dabble in postgres, so I thought this might be 
a good trivial thing for me to improve.  The attached patch adds extra detail 
the the existing autovacuum log message that is emitted when the 
log_autovacuum_min_duration threshold is met, exposing the unremovable dead 
tuple count similar to what you get from VACUUM VERBOSE.

Sample log output (my addition in bold):

LOG:  automatic vacuum of table "test.public.test": index scans: 0
        pages: 0 removed, 5 remain
        tuples: 0 removed, 1000 remain, 999 dead but not removable
        system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec

My patch adds another member to the LVRelStats struct named 
undeleteable_dead_tuples.  lazy_scan_heap() sets undeleteable_dead_tuples to 
the value of lazy_scan_heap()'s local variable "nkeep", which is the same 
variable that is used to emit the VACUUM VERBOSE unremoveable dead row count.

As this is my first patch to postgresql, I'm expecting I've done something 
wrong.  Please if you want me to fix something up, or just go away please say 
so ;)  I appreciate that this is a trivial patch, and perhaps doesn't add value 
except for my very specific use caseā€¦ feel free to ignore it =)


diff --git a/src/backend/commands/vacuumlazy.c 
index cf8337b..12f03d7 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          undeleteable_dead_tuples; /* count of dead tuples not 
yet removeable */
        BlockNumber pages_removed;
        double          tuples_deleted;
        BlockNumber nonempty_pages; /* actually, last nonempty page + 1 */
@@ -256,7 +257,7 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
                                        (errmsg("automatic vacuum of table 
\"%s.%s.%s\": index scans: %d\n"
                                                        "pages: %d removed, %d 
-                                                       "tuples: %.0f removed, 
%.0f remain\n"
+                                                       "tuples: %.0f removed, 
%.0f remain, %.0f dead but not removable\n"
                                                        "system usage: %s",
@@ -266,6 +267,7 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt,
@@ -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->undeleteable_dead_tuples = nkeep;
        /* now we can compute the new value for pg_class.reltuples */
        vacrelstats->new_rel_tuples = vac_estimate_reltuples(onerel, false,

Reply via email to