Re: autovacuum next steps, take 3

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > Tom Lane wrote:
> >> 1. Grab the AutovacSchedule LWLock exclusively.
> >> 2. Check to see if another worker is currently processing
> >> that table; if so drop LWLock and go to next list entry.
> >> 3. Recompute whether table needs vacuuming; if not,
> >> drop LWLock and go to next entry. (This test covers the
> >> case where someone vacuumed the table since you made your
> >> list.)
> >> 4. Put table OID into shared memory, drop LWLock, then
> >> vacuum table.
> >> 5. Clear current-table OID from shared memory, then
> >> repeat for next list entry.
>
> > The point I'm not very sure about is that this proposal means we need to
> > do I/O with the AutovacSchedule LWLock grabbed, to obtain up-to-date
> > stats.
>
> True. You could probably drop the lock while rechecking stats, at the
> cost of having to recheck for collision (repeat step 2) afterwards.
> Or recheck stats before you start, but if collisions are likely then
> that's a waste of time. But on the third hand, does it matter?
> Rechecking the stats should be much cheaper than a vacuum operation,
> so I'm not seeing that there's going to be a problem. It's not like
> there are going to be hundreds of workers contending for that lock...

Turns out that it does matter, because not only we need to read pgstats,
but we also need to fetch the pg_autovacuum and pg_class rows again for
the table. So we must release the AutovacuumSchedule lock before trying
to open pg_class etc.

Unless we are prepared to "cache" (keep a private copy of) the contents
of said tuples between the first check (i.e. when building the initial
table list) and the recheck? This is possible as well, but it gives me
an uneasy feeling.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.