Re: idle in transaction - Mailing list pgsql-jdbc

Hi.<br /> In a more generic way, you need to garantee 2 things:<br /> 1) You always release your connections back to
thepool.<br /> 2) Any connection released must not have a valid transaction with resources allocated.<br /><br /> Its
okto have a connection in the pool with setautocommit(false), that leads to "idle in connection", if the transaction is
anew one and no resources or lock have been made at the db.<br /><br /> If you want now what is blocking your vaccum
trythis:<br /><br /> SELECT pg_class.relname AS table, pg_database.datname AS database, transaction, pid, mode, granted
FROMpg_locks, pg_class, pg_database WHERE pg_locks.relation = pg_class.oid AND pg_locks.database = pg_database.oid
orderby pg_class.relname, pid;<br /><br /> You will see in wich table the vaccum is waiting to acquire the lock and
wichprocess is holding it at the moment.<br /><br /> Best regards.<br /> João Paulo Ribeiro<br /><br /> Sriram
Dandapaniwrote: <blockquote cite="mid6992E470F12A444BB787B5C937B9D4DF05DF1A7F@ca-mail1.cis.local" type="cite"><style>
 
<!--/* Font Definitions */@font-face{font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;}/* Style Definitions
*/p.MsoNormal,li.MsoNormal, div.MsoNormal{margin:0in;margin-bottom:.0001pt;font-size:12.0pt;font-family:"Times New
Roman";color:black;}
a:link, span.MsoHyperlink{color:blue;text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed{color:purple;text-decoration:underline;}
pre{margin:0in;margin-bottom:.0001pt;font-size:10.0pt;font-family:"Courier New";color:black;}
span.EmailStyle17{mso-style-type:personal;font-family:Arial;color:windowtext;}
span.EmailStyle19{mso-style-type:personal-reply;font-family:Arial;color:navy;}
@page Section1{size:8.5in 11.0in;margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1{page:Section1;}
--> </style><div class="Section1"><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size:
10pt;font-family: Arial; color: navy;">Hi</span></font><p class="MsoNormal"><font color="navy" face="Arial"
size="2"><spanstyle="font-size: 10pt; font-family: Arial; color: navy;"> </span></font><p class="MsoNormal"><font
color="navy"face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;">I use JBOSS 4.0.3 to
managemy connection pooling. Is there a way to configure / modify code to take care of this</span></font><p
class="MsoNormal"><fontcolor="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color:
navy;"> </span></font><pclass="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10pt;
font-family:Arial; color: navy;">Thanks for your input</span></font><p class="MsoNormal"><font color="navy"
face="Arial"size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;"> </span></font><p
class="MsoNormal"><fontcolor="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color:
navy;">Sriram</span></font><pclass="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10pt;
font-family:Arial; color: navy;"> </span></font><div><div align="center" class="MsoNormal" style="text-align:
center;"><fontcolor="black" face="Times New Roman" size="3"><span style="font-size: 12pt; color: windowtext;"><hr
align="center"size="3" tabindex="-1" width="100%" /></span></font></div><p class="MsoNormal"><b><font color="black"
face="Tahoma"size="2"><span style="font-size: 10pt; font-family: Tahoma; color: windowtext; font-weight:
bold;">From:</span></font></b><fontcolor="black" face="Tahoma" size="2"><span style="font-size: 10pt; font-family:
Tahoma;color: windowtext;"> João Paulo Ribeiro [<a class="moz-txt-link-freetext"
href="mailto:jp@mobicomp.com">mailto:jp@mobicomp.com</a>]<br /><b><span style="font-weight: bold;">Sent:</span></b>
Monday,September 18, 2006 2:15 AM<br /><b><span style="font-weight: bold;">To:</span></b> Sriram Dandapani<br
/><b><spanstyle="font-weight: bold;">Cc:</span></b> <a class="moz-txt-link-abbreviated"
href="mailto:pgsql-jdbc@postgresql.org">pgsql-jdbc@postgresql.org</a><br/><b><span style="font-weight:
bold;">Subject:</span></b>Re: [JDBC] idle in transaction</span></font><font color="black"><span style="color:
windowtext;"></span></font></div><pclass="MsoNormal"><font color="black" face="Times New Roman" size="3"><span
style="font-size:12pt;"> </span></font><p class="MsoNormal"><font color="black" face="Times New Roman" size="3"><span
style="font-size:12pt;">Hi.<br /><br /> Are you using DODS/Enhydra to manage your database connection pool?<br /> If
yes,see my email in the Enhydra mailing list:<br /><a
href="http://mail-archive.objectweb.org/dods/2006-05/msg00000.html">http://mail-archive.objectweb.org/dods/2006-05/msg00000.html</a><br
/><a
href="http://mail-archive.objectweb.org/dods/2006-06/msg00000.html">http://mail-archive.objectweb.org/dods/2006-06/msg00000.html</a><br
/><br/> The latest DODS dont have this issue anymore. You just use "RollbackOnReset" option.<br /><br /> Bests
regards.<br/> João Paulo Ribeiro<br /><br /> Sriram Dandapani wrote: </span></font><p class="MsoNormal"><font
color="black"face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">I use postgres 8.1.2 database and
postgres8.1.404 jdbc driver .</span></font><p class="MsoNormal"><font color="black" face="Arial" size="2"><span
style="font-size:10pt; font-family: Arial;"> </span></font><p class="MsoNormal"><font color="black" face="Arial"
size="2"><spanstyle="font-size: 10pt; font-family: Arial;">I keep seeing “idle in transaction” connections on the
postgresbox all the time. The application logic closes all connections , so it makes me wonder if the driver is issuing
abegin statement after every commit to leave a floating transaction open.</span></font><p class="MsoNormal"><font
color="black"face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font><p
class="MsoNormal"><fontcolor="black" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">This is
causingautovacuum to not finish (and hence I have to frequently issue database-wide vacuums to prevent the transaction
idwraparound problem)</span></font><p class="MsoNormal"><font color="black" face="Arial" size="2"><span
style="font-size:10pt; font-family: Arial;"> </span></font><p class="MsoNormal"><font color="black" face="Arial"
size="2"><spanstyle="font-size: 10pt; font-family: Arial;">Any help here is appreciated</span></font><p
class="MsoNormal"><fontcolor="black" face="Arial" size="2"><span style="font-size: 10pt; font-family:
Arial;"> </span></font><pclass="MsoNormal"><font color="black" face="Arial" size="2"><span style="font-size: 10pt;
font-family:Arial;">Thanks</span></font><p class="MsoNormal"><font color="black" face="Arial" size="2"><span
style="font-size:10pt; font-family: Arial;"> </span></font><p class="MsoNormal"><font color="black" face="Arial"
size="2"><spanstyle="font-size: 10pt; font-family: Arial;">Sriram</span></font><p class="MsoNormal"><font color="black"
face="TimesNew Roman" size="3"><span style="font-size: 12pt;"><br /><br /><br /></span></font><pre><font color="black"
face="CourierNew" size="2"><span style="font-size: 10pt;">-- </span></font></pre> <pre><font color="black"
face="CourierNew" size="2"><span style="font-size: 10pt;">João Paulo Ribeiro | Senior Software
Engineer</span></font></pre><pre><font color="black" face="Courier New" size="2"><span style="font-size: 10pt;"><a
href="mailto:jp@mobicomp.com">jp@mobicomp.com</a></span></font></pre><pre><font color="black" face="Courier New"
size="2"><spanstyle="font-size: 10pt;"> </span></font></pre> <pre><font color="black" face="Courier New" size="2"><span
style="font-size:10pt;">PHONE: + 351 253 305 250</span></font></pre> <pre><font color="black" face="Courier New"
size="2"><spanstyle="font-size: 10pt;">FAX  : + 351 253 305 250</span></font></pre> <pre><font color="black"
face="CourierNew" size="2"><span style="font-size: 10pt;"><a
href="http://www.mobicomp.com">www.mobicomp.com</a></span></font></pre><pre><font color="black" face="Courier New"
size="2"><spanstyle="font-size: 10pt;"> </span></font></pre> <pre><font color="black" face="Courier New" size="2"><span
style="font-size:10pt;">________________________________________________________________</span></font></pre> <pre><font
color="black"face="Courier New" size="2"><span style="font-size: 10pt;"> </span></font></pre> <pre><font color="black"
face="CourierNew" size="2"><span style="font-size: 10pt;">About Solutions | Wireless World</span></font></pre>
<pre><fontcolor="black" face="Courier New" size="2"><span style="font-size: 10pt;"> </span></font></pre> <pre><font
color="black"face="Courier New" size="2"><span style="font-size: 10pt;">CONFIDENTIALITY NOTICE: This message, as well
asexisting attached files, is confidential and intended exclusively for the individual(s) named as addressees. If you
arenot the intended recipient, you are kindly requested not to make any use whatsoever of its contents and to proceed
tothe destruction of the message, thereby notifying the sender.</span></font></pre> <pre><font color="black"
face="CourierNew" size="2"><span style="font-size: 10pt;">DISCLAIMER: The sender of this message can not ensure the
securityof its electronic transmission and consequently does not accept liability for any fact which may interfere with
theintegrity of its content.</span></font></pre></div></blockquote><br /><br /><pre class="moz-signature" cols="72">--

João Paulo Ribeiro | Senior Software Engineer
<a class="moz-txt-link-abbreviated" href="mailto:jp@mobicomp.com">jp@mobicomp.com</a>

PHONE: + 351 253 305 250
FAX  : + 351 253 305 250
<a class="moz-txt-link-abbreviated" href="http://www.mobicomp.com">www.mobicomp.com</a>

________________________________________________________________

About Solutions | Wireless World

CONFIDENTIALITY NOTICE: This message, as well as existing attached files, is confidential and intended exclusively for
theindividual(s) named as addressees. If you are not the intended recipient, you are kindly requested not to make any
usewhatsoever 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
notaccept liability for any fact which may interfere with the integrity of its content.</pre>