Subject: Re: Preventing duplicate vacuums?
From: Robert Treat <xzilla@users.sourceforge.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Josh Berkus <josh@agliodbs.com>, pgsql-hackers@postgresql.org
In-Reply-To: <17544.1076137674@sss.pgh.pa.us>
References: <200402051237.14615.josh@agliodbs.com>
	<11820.1076015269@sss.pgh.pa.us> <200402051351.49983.josh@agliodbs.com>
	<1076106724.30335.41.camel@camel>  <17544.1076137674@sss.pgh.pa.us>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 09 Feb 2004 13:55:09 -0500
Message-Id: <1076352909.30149.248.camel@camel>
Mime-Version: 1.0

On Sat, 2004-02-07 at 02:07, Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.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