Re: (auto)vacuum truncate exclusive lock

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Kevin Grittner <kgrittn(at)ymail(dot)com> writes:
>> OK, will review that to confirm;but assuming that's right, and
>> nobody else is already working on a fix, I propose to do the
>> following:
>
>> (1)  Restore the prior behavior of the VACUUM command.  This was
>> only ever intended to be a fix for a serious autovacuum problem
>> which caused many users serious performance problems -- in some
>> cases including unscheduled down time.  I also saw sites where,
>> having been bitten by this, they disabled autovacuum and later ran
>> into problems with bloat and/or xid wraparound.
>
>> (2)  If autovacuum decides to try to truncate but the lock cannot
>> be initially acquired, and analyze is requested, skip the
>> truncation and do the autoanalyze.  If the table is so hot that we
>> cannot get the lock, the space may get re-used soon, and if not
>> there is a good chance another autovacuum will trigger soon.  If
>> the user really wants the space released to the OS immediately,
>> they can run a manual vacuum to force the issue.
>
> I think that the minimum appropriate fix here is to revert the hunk
> I quoted, ie take out the suppression of stats reporting and analysis.

I'm not sure I understand -- are you proposing that is all we do
for both the VACUUM command and autovacuum?  (i.e., we don't try to
full restore the old VACUUM command behavior; just the troublesome
failure to generate statistics?)

> However, we're still thinking too small.  I've been wondering whether we
> couldn't entirely remove the dirty, awful kluges that were installed in
> the lock manager to kill autovacuum when somebody blocked behind it.
> This mechanism should ensure that AV never takes an exclusive lock
> for long enough to be a serious problem, so do we need that anymore?

Are you suggesting that just in master/HEAD or back to 9.0?  If the
latter, what existing problem does it cure (besides ugly code)?

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company