Re: Autovacuum versus rolled-back transactions

[Alvaro Herrera <alvherre(at)commandprompt(dot)com>]

Tom Lane wrote:

> It may boil down to whether we would like the identity
> 	n_live_tup = n_tup_ins - n_tup_del
> to continue to hold, or the similar one for n_dead_tup.  The problem
> basically is that pgstats is computing n_live_tup and n_dead_tup
> using those identities rather than by tracking what really happens.

Thanks for fixing this.  For the record, I don't think I ever actually
*considered* the effect of rolled back transactions in the tuple counts;
at the time I wrote the code, I was just mirroring what the old autovac
code did, and I didn't stop to think whether the assumptions were
actually correct.

I think the committed fix was the most appropriate -- changing the
semantics of n_ins_tup etc would defeat the original purpose they were
written for, I think.


Regarding the idea of counting dead tuples left behind by vacuum to
update pgstats at the end, I think the idea of counting them
individually is good, but it doesn't account for dead tuples created in
areas that were scanned earlier.  So I think that Takahiro-san idea of
using the value accumulated in pgstats is better.

If we apply Heikki's idea of advancing OldestXmin, I think what we
should do is grab the value from pgstats when vacuum starts, and each
time we're going to advance OldestXmin, grab the value from pgstats
again; accumulate the differences from the various pgstat grabs.  At the
end we send the accumulated differences as the new dead tuple count.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support