Vacuum Delayed by Idle Transactions

your location: home page ··· client area ··· faqs ··· postgresql database ··· vacuum delayed by idle transactions

Article Summary

Title:		Vacuum Delayed by Idle Transactions
Description:		Idle transactions may cause vacuums to hang
Key Words:		postgresql, vacuum, delay, incomplete, waiting, idle, transaction
Type:		FAQs
Category:		PostgreSQL Database
Last Updated:		2004-01-08 05:01:11


	Vacuum Delayed by Idle Transactions When vacuuming my PostgreSQL database, the vacuum process hangs and never seems to complete. What is the most likely cause of this problem? When a JDBC transaction is committed, but the `Connection.setAutoCommit(true);` is not called, the JDBC driver leaves the postmaster backend in a "idle in transaction" state. The transaction holds locks on tables and prevents the vacuum from running. This is probably an oversight in the PostgreSQL JDBC drivers, as a call to `Connection.commit();` should logically end the transaction. Unfortunately, that is not currently the case. You may find out if this is the problem using the following shell command: `ps wwax \| grep postgres` This is the cause of the vacuum deadlock if the output looks like this: 22796 ? S 0:00 postgres: username db x.x.x.x idle in transaction 26201 ? S 0:00 postgres: username db x.x.x.x VACUUM waiting To avoid this problem, please see the Recommended JDBC Pool and Recommended JDBC Transaction.