Re: autovacuum running for a long time on a new table with 1 row

Lonni J Friedman <netllama(at)gmail(dot)com> writes:
> On Fri, Jun 1, 2012 at 10:34 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> This seems to have been noticed and fixed in HEAD:
>> http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=b4e0741727685443657b55932da0c06f028fbc00
>> I wonder whether that should've been back-patched.

> Thanks for your reply. I won't even pretend to understand what that
> fix does. Is this behavior something that is blatantly broken, or
> harmless, or somewhere in between? Should I expect autovacuum to
> eventually complete succesfully when it stumbles into this scenario?

Well, the problem with the original code was that it would recheck the
visibility map's file size anytime somebody tried to check a bit beyond
the end of the map. If the map isn't there (which is not an error case)
this would result in a useless open() attempt for each table page
scanned by vacuum. So ordinarily I would say that yes you could expect
autovac to complete eventually. However ...

> Before dropping & recreating the table, yes it had millions of rows,
> and millions of updates. But since then, all I did was insert a
> single row, and watched autovacuum wedge itself in that seemingly
> infinite loop. I ended up doing a 'kill -2' on the autovacuum PID
> that was misbehaving, disabled autovacuuming the table, and went about
> what I needed to get done as an interim solution.

... if you really did drop and recreate the table, then at this point
it should only have a single page, I would think. It might be worth
checking the actual file size. pg_relation_size('tablename') is
probably the quickest way.

regards, tom lane