| | | | | | | | | | | | | | 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.
| | | | |
| | | | | Copyright © 2000-2024 AO Industries, Inc. |
|