Re: Avoid endless futile table locks in vacuuming.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Avoid endless futile table locks in vacuuming.
Date: 2015-12-27 18:33:11
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> If a partially-active table develops a slug of stable all-visible,
> non-empty pages at the end of it, then every autovacuum of that table
> will skip the end pages on the forward scan, think they might be
> truncatable, and take the access exclusive lock to do the truncation.
> And then immediately fail when it sees the last page is not empty.
> This can persist for an indefinite number of autovac rounds.

> This is not generally a problem, as the lock is taken conditionally.
> However, the lock is also logged and passed over to any hot standbys,
> where it must be replayed unconditionally. This can cause query
> cancellations.

> The simple solution is to always scan the last page of a table, so it
> can be noticed that it is not empty and avoid the truncation attempt.

This seems like a reasonable proposal, but I find your implementation
unconvincing: there are two places in lazy_scan_heap() that pay attention
to scan_all, and you touched only one of them. Thus, if we fail to
acquire cleanup lock on the table's last page on the first try, the
change is for naught. Shouldn't we be insisting on getting that lock?
Or, if you intentionally didn't change that because it seems like too
high a price to pay, why doesn't the comment reflect that?

regards, tom lane

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Artur Zakirov 2015-12-27 19:17:26 Re: Fuzzy substring searching with the pg_trgm extension
Previous Message Grzegorz Sampolski 2015-12-27 16:31:56 Re: pam auth - add rhost item