Re: idle in transaction

Hi.
In a more generic way, you need to garantee 2 things:
1) You always release your connections back to the pool.
2) Any connection released must not have a valid transaction with resources allocated.

Its ok to have a connection in the pool with setautocommit(false), that leads to "idle in connection", if the transaction is a new one and no resources or lock have been made at the db.

If you want now what is blocking your vaccum try this:

SELECT pg_class.relname AS table, pg_database.datname AS database, transaction, pid, mode, granted FROM pg_locks, pg_class, pg_database WHERE pg_locks.relation = pg_class.oid AND pg_locks.database = pg_database.oid order by pg_class.relname, pid;

You will see in wich table the vaccum is waiting to acquire the lock and wich process is holding it at the moment.

Best regards.
João Paulo Ribeiro

Sriram Dandapani wrote:

Hi

 

I use JBOSS 4.0.3 to manage my connection pooling. Is there a way to configure / modify code to take care of this

 

Thanks for your input

 

Sriram

 

---

From: João Paulo Ribeiro [mailto:jp@mobicomp.com]
Sent: Monday, September 18, 2006 2:15 AM
To: Sriram Dandapani
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] idle in transaction

 

Hi.

Are you using DODS/Enhydra to manage your database connection pool?
If yes, see my email in the Enhydra mailing list:
http://mail-archive.objectweb.org/dods/2006-05/msg00000.html
http://mail-archive.objectweb.org/dods/2006-06/msg00000.html

The latest DODS dont have this issue anymore. You just use "RollbackOnReset" option.

Bests regards.
João Paulo Ribeiro

Sriram Dandapani wrote:

I use postgres 8.1.2 database and postgres 8.1.404 jdbc driver .

 

I keep seeing “idle in transaction” connections on the postgres box all the time. The application logic closes all connections , so it makes me wonder if the driver is issuing a begin statement after every commit to leave a floating transaction open.

 

This is causing autovacuum to not finish (and hence I have to frequently issue database-wide vacuums to prevent the transaction id wraparound problem)

 

Any help here is appreciated

 

Thanks

 

Sriram

-- 

João Paulo Ribeiro | Senior Software Engineer

jp@mobicomp.com

PHONE: + 351 253 305 250

FAX  : + 351 253 305 250

www.mobicomp.com

________________________________________________________________

About Solutions | Wireless World

CONFIDENTIALITY NOTICE: This message, as well as existing attached files, is confidential and intended exclusively for the individual(s) named as addressees. If you are not the intended recipient, you are kindly requested not to make any use whatsoever of its contents and to proceed to the destruction of the message, thereby notifying the sender.

DISCLAIMER: The sender of this message can not ensure the security of its electronic transmission and consequently does not accept liability for any fact which may interfere with the integrity of its content.

-- 
João Paulo Ribeiro | Senior Software Engineer
jp@mobicomp.com

PHONE: + 351 253 305 250
FAX  : + 351 253 305 250
www.mobicomp.com

________________________________________________________________

About Solutions | Wireless World

CONFIDENTIALITY NOTICE: This message, as well as existing attached files, is confidential and intended exclusively for the individual(s) named as addressees. If you are not the intended recipient, you are kindly requested not to make any use whatsoever of its contents and to proceed to the destruction of the message, thereby notifying the sender.
DISCLAIMER: The sender of this message can not ensure the security of its electronic transmission and consequently does not accept liability for any fact which may interfere with the integrity of its content.