Re: Question about Idle in TX

David Kerr wrote:
> I know that "Idle in TXs" can interfere with Vaccums for example, but
> I'm not sure if that's due to them usually having some form of lock on a
> table.
>

Locks aren't the issue. When you have a transaction open, the database
makes sure it can deliver a consistent view of the database for the
lifetime of that transaction, using MVCC:
http://wiki.postgresql.org/wiki/MVCC

What this means in practice is that VACUUM may stop cleaning up old data
because your open transaction might still need to look at it. Table
maintenance can grind to a halt when you have one of these long running
transactions. Dead rows (ones left behind by DELETE or UPDATE) will
stop being recycled, tables will grow, queries will slow down.

If you're running 8.4 or later, there is a significant improvement to
how pessimistic that gets in a typical case. To quote Alvaro, the
author of that patch:

"I expect to be able to remove dead rows created by transactions that
are no longer in progress, but which started more recently than some
currently-open long-running transaction."

It's still something to be wary of.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us