Re: Lock problem with autovacuum truncating heap

On Sun, Mar 27, 2011 at 8:25 PM, Jan Wieck <JanWieck(at)yahoo(dot)com> wrote:
>
> Since we are talking about stable releases, I think just releasing and
> reacquiring the exclusive lock is enough. We can then try to further improve
> things for future releases.

I like all of:

1) move the truncating to a new transaction just like we currently do
toast tables in a separate transaction from the main vacuum. I'm not
sure why we do it for toast tables but it makes sense here. If we get
killed by autovacuum detecting a lock conflict we want to commit the
changes to pg_class entry so that autovacuum doesn't invoke us again.

2) Don't bother trying to truncate if we've been called from
autovacuum at all. This doesn't help people who run vacuum from a cron
job but it does help anyone who doesn't know what's going on and is
just randomly having their table exclusive-locked at arbitrary times
in the middle of peak production hours. I doubt the truncation really
helps much in normal operation anyways and if you've deleted all the
rows in your table it's not a bad recommendation to say you should run
vacuum manually and not rely on autovacuum in that instance.

3) Scanning backwards 8MB at a time scanning each 8MB forwards instead
of just going back by block backwards.

--
greg