Thread: Is there a problem running vacuum in the middle of a transaction?

    Is there a problem with running vacuum, or vacuum analyze in the
middle of making transactions? If there happens to be a transaction running
at the time I do a vacuum analyze, the transaction has problems and the
trigger doesn't get completed all the way, and the transaction fails.

Thanks for any pointers.

Mike Cianflone <mcianflone@littlefeet-inc.com> writes:

>     Is there a problem with running vacuum, or vacuum analyze in the
> middle of making transactions? If there happens to be a transaction running
> at the time I do a vacuum analyze, the transaction has problems and the
> trigger doesn't get completed all the way, and the transaction fails.

Hmmm--AFAIK, VACUUM is supposed to grab locks on the tables it
processes, which will block until all open transactions against that
table are finished.  So either VACUUM or your transactions will have
to wait, but they shouldn't interfere with each other. 

How about posting some log messages from when the problem occurs?

-Doug
-- 
Free Dmitry Sklyarov! 
http://www.freesklyarov.org/ 

We will return to our regularly scheduled signature shortly.

Doug McNaught <doug@wireboard.com> writes:
> Hmmm--AFAIK, VACUUM is supposed to grab locks on the tables it
> processes, which will block until all open transactions against that
> table are finished.  So either VACUUM or your transactions will have
> to wait, but they shouldn't interfere with each other. 

Well, it's uglier than that.  Normally, read and write locks are not
mutually exclusive, so if you have a client that is holding an open
transaction and not doing anything, it doesn't matter if it read or
wrote a table earlier in the transaction.  Other clients can proceed
to read or write that table despite the existence of a lock owned by
the open transaction.

But VACUUM wants an exclusive lock on the table, so it will block
until all clients holding read or write locks commit.  Once VACUUM has
blocked, subsequent read or write requests also block, because they
queue up behind the VACUUM exclusive-lock request.  (We could allow
them to go in front, but that would create the likelihood that VACUUM
could *never* get its lock, in the face of a steady stream of read 
or write lockers.)

Upshot: a client holding an open transaction, plus another client trying
to do VACUUM, can clog up the database for everyone else.

Restarting the whole database is severe overreaction; aborting the
transaction of either of the clients at fault would be sufficient to
clear the logjam.

7.2 will be less prone to this problem, since the default form of VACUUM
in 7.2 will not require exclusive lock.  But you'd still see it if you
have some clients that want to acquire exclusive table locks for some
reason.  Bottom line is that dawdling around with an open transaction is
bad form if you have a heavily concurrent application.  Once you've done
something, you should commit or roll back within a reasonably short
interval.
        regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Doug McNaught <doug@wireboard.com> writes:
> > Hmmm--AFAIK, VACUUM is supposed to grab locks on the tables it
> > processes, which will block until all open transactions against that
> > table are finished.  So either VACUUM or your transactions will have
> > to wait, but they shouldn't interfere with each other. 
> 
> Upshot: a client holding an open transaction, plus another client trying
> to do VACUUM, can clog up the database for everyone else.

Thanks for the clarification.  But the original poster's problem, that 
VACUUM caused his transactions to fail, theoretically shouldn't
happen--right?

-Doug
-- 
Free Dmitry Sklyarov! 
http://www.freesklyarov.org/ 

We will return to our regularly scheduled signature shortly.

Doug McNaught <doug@wireboard.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> Upshot: a client holding an open transaction, plus another client trying
>> to do VACUUM, can clog up the database for everyone else.

> Thanks for the clarification.  But the original poster's problem, that 
> VACUUM caused his transactions to fail, theoretically shouldn't
> happen--right?

It wasn't clear to me that anything was actually failing, as opposed to
just getting blocked for a long time.  Mike?
        regards, tom lane