Re: Preventing duplicate vacuums?

On Sat, 2004-02-07 at 02:07, Tom Lane wrote:
> Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> writes:
> > Don't know if I would agree for sure, but i the second vacuum could see
> > that it is being blocked by the current vacuum, exiting out would be a
> > bonus, since in most scenarios you don't need to run that second vacuum
> > so it just ends up wasting resources (or clogging other things up with
> > it lock)
>
> This would be reasonable if we could do it, but the present lock manager
> doesn't provide any way to tell what sort of lock is blocking you.
> There are some cases in which it isn't obvious anyway. For instance,
> suppose an ALTER TABLE (which wants an exclusive lock) is queued up
> waiting for the currently-running VACUUM. An incoming new VACUUM
> request will queue behind the ALTER. Which lock would you say is
> blocking it ... and does an honest answer to that question jibe with
> your preference about whether the second VACUUM should give up?
>

ISTM that both sides have trouble, since you could just as easily have
vacuum queued up behind an alter we your second vacuum comes in...

> A chintzy way out would be for VACUUM to just exit if it can't
> immediately acquire lock, regardless of the cause. This wouldn't be
> too useful for VACUUM FULL, but plain VACUUM is not blocked by very many
> common operations other than another VACUUM, so most of the time it
> would do what you want. I could possibly be talked into supporting an
> option to do that.
>

This seems pretty useful to me. I thought about doing things like
setting statement_timeout to some low number but that would generally
cause the vacuum to timeout as well. Looking through postgresql.conf
nothing else seems to apply... ISTR people asking for a general
"lock_timeout" param that would cancel queries if they wait for a lock
longer than x milliseconds... this seems like very similar
functionality...

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL