Re: Lock problem with autovacuum truncating heap

On Mar 27, 2011, at 9:43 PM, Tom Lane wrote:
>> 1) move the truncating to a new transaction just like we currently do
>> toast tables in a separate transaction from the main vacuum.
>
> +1 if we are going to continue the behavior of allowing other
> transactions to kick autovac off the exclusive lock. However, if we can
> find a way to avoid the need of that, then breaking it into multiple
> transactions would just be useless complication (and extra cycles).
>
> In particular, I thought the direction Jan was headed was to release and
> reacquire the lock between truncating off limited-size chunks of the
> file. If we do that, we probably *don't* want or need to allow autovac
> to be booted off the lock more quickly.
<snip>
>> 3) Scanning backwards 8MB at a time scanning each 8MB forwards instead
>> of just going back by block backwards.
>
> Maybe. I'd want to see some experimental evidence justifying the choice
> of chunk size; I'm pretty sure this will become counterproductive once
> the chunk size is too large.

I have an idea that might be better than protecting truncation by exclusive-locking the table: a "virtual end of relation" pointer we can lock that makes the truncation fast and easy? The idea is that vacuum would grab the lock protecting that pointer, see what the last valid page actually is, and then truncate everything else. Incrementing that pointer is one more thing we'd have to do when extending a relation, but it's just an increment (and AFAIK we already have a lock for extending a rel).

The challenge would come in actually moving that pointer backwards. My thought is that vacuum can simply set it back to the last known page with data and set a bit indicating that the pointer *might* be ahead of the actual valid end of the relation. Anything that wants to extend the relation when that bit is set would need to

- See if the next page actually exists
- See if it actually contains data (vacuum could mark completely empty pages as invalid to speed that up)

Additionally, we'd need to make sure that nothing else could write to a page > virtual end of relation.

Once that's set, vacuum could start at virtual_end_of_relation and scan forward, ensuring no later pages have data. After that's done it can truncate anything > virtual_end (which might have moved forward in the meantime).
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net