Greg Smith wrote: > The biggest downside of [MVCC] is that if you have an old client > lingering around, things that happened in the database after it started > can't be cleaned up. Just to clarify for readers: Idle clients aren't generally an issue. It's only clients that are idle with an open transaction that tend to cause issues. > In 8.4 this situation is improved for some common use cases. In the 8.3 > you're using, an old transaction will block any VACUUM attempt from > moving past that point in time forever. You have to figure out how to > get Hibernate to close the transaction it's leaving open for VACUUM to > work. Hibernate is pretty well behaved with transaction management. In fact, it's downright nuts about keeping transactions open for as short a period of time as possible. It even implements its own row-versioning based optimistic locking scheme (oplock) rather than relying on holding a transaction open with row locks in the database. If you have connections left idle in transaction by a Hibernate-based Java app, the problem is probably: 1) Unclosed sessions / EntityManagers or explicit transactions in your own app code. Check particularly for places where the app may open a transaction without a finally clause on a try block to ensure the transaction (and the Session / EntityManager) are closed when the block is exited. 2) Connections being returned to the connection pool with open transactions ( probably due to #1 ). The connection pool should take care of that, but reports suggest that some don't. 3) Autocommit being disabled. At least when using Hibernate via JPA, that'll cause a major mess and would easily explain the issues you're seeing. Hibernate manages transactions explicitly when required, and expects autocommit to be off. 3) Your connection pool software doing something crazy like intentionally keeping idle connections with transactions open. The connection pool (c3p0 or whatever) that you use is separate from Hibernate. I'd be surprised to see this except if autocommit was disabled and the pooling software expected/assumed it'd be enabled. -- Craig Ringe -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
