Re: Avoid endless futile table locks in vacuuming.

<p dir="ltr">On Mon, Dec 28, 2015 at 2:12 PM, Tom Lane <<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>>
wrote:<br/> > I wrote:<br /> >> Jeff Janes <<a
href="mailto:jeff.janes@gmail.com">jeff.janes@gmail.com</a>>writes:<br /> >>> If a partially-active table
developsa slug of stable all-visible,<br /> >>> non-empty pages at the end of it, then every autovacuum of
thattable<br /> >>> will skip the end pages on the forward scan, think they might be<br /> >>>
truncatable,and take the access exclusive lock to do the truncation.<br /> >>> And then immediately fail when
itsees the last page is not empty.<br /> >>> This can persist for an indefinite number of autovac rounds.<br
/>>>> The simple solution is to always scan the last page of a table, so it<br /> >>> can be noticed
thatit is not empty and avoid the truncation attempt.<br /> ><br /> >> This seems like a reasonable proposal,
butI find your implementation<br /> >> unconvincing: there are two places in lazy_scan_heap() that pay
attention<br/> >> to scan_all, and you touched only one of them.<br /> ><br /> > After further
investigation,there is another pre-existing bug: the short<br /> > circuit path for pages not requiring freezing
doesn'tbother to update<br /> > vacrelstats->nonempty_pages, causing the later logic to think that the<br /> >
pageis potentially truncatable even if we fix the second check of<br /> > scan_all! So this is pretty broken, and I
almostthink we should treat it<br /> > as a back-patchable bug fix.<br /> ><br /> > In the attached proposed
patch,I added another refinement, which is to<br /> > not bother with forcing the last page to be scanned if we
alreadyknow<br /> > that we're not going to attempt truncation, because we already found a<br /> > nonempty page
tooclose to the end of the rel. I'm not quite sure this<br /> > is worthwhile, but it takes very little added logic,
andsaving an I/O<br /> > is probably worth the trouble.<p dir="ltr">If we are not doing a scan_all and we fail to
acquirea clean-up lock on the last block, and the last block reports that it needs freezing, then we continue on to
waitfor the clean-up lock. But there is no need, we don't really need to freeze the block, and we already know whether
ithas tuples or not without the clean up lock.  Couldn't we just set the flag based on hastup, then 'continue'?<p
dir="ltr">Cheers,<pdir="ltr">Jeff