Обсуждение: 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