Re: Heap truncation without AccessExclusiveLock (9.4)

On 16.05.2013 04:15, Andres Freund wrote:
> On 2013-05-15 18:35:35 +0300, Heikki Linnakangas wrote:
>> Truncating a heap at the end of vacuum, to release unused space back to
>> the OS, currently requires taking an AccessExclusiveLock. Although it's only
>> held for a short duration, it can be enough to cause a hiccup in query
>> processing while it's held. Also, if there is a continuous stream of queries
>> on the table, autovacuum never succeeds in acquiring the lock, and thus the
>> table never gets truncated.
>>
>> I'd like to eliminate the need for AccessExclusiveLock while truncating.
>
> Couldn't we "just" take the extension lock and then walk backwards from
> the rechecked end of relation ConditionalLockBufferForCleanup() the
> buffers?
> For every such locked page we check whether its still empty. If we find
> a page that we couldn't lock, isn't empty or we already locked a
> sufficient number of pages we truncate.

You need an AccessExclusiveLock on the relation to make sure that after
you have checked that pages 10-15 are empty, and truncated them away, a
backend doesn't come along a few seconds later and try to read page 10
again. There might be an old sequential scan in progress, for example,
that thinks that the pages are still there.

- Heikki