I just upgraded from version 2.5.2 to 2.6.0 and encountered, that GeoServer 2.6.0 now leaves pending/open transactions in the PostGIS/PostgreSQL database between service requests.
I'm using a JNDI PostGIS data source (configured in Apache Tomcat) and did not test whether this happens with non-JNDI data sources too (however, I'm sure it has nothing to do with JNDI or non-JNDI sources).
GeoServer/GeoTools seems to start transaction even if it's only reading data (that is, not performing a WFS-T operation, but processing a WMS request). Maybe you start the transaction "manually" or it's the SET extra_float_digits = 3 statement, that implicitly starts a transaction.
However, before the connection is given back to the pool (aka closed), there is no longer a ROLLBACK statement issued. After that, a pending transaction is left back, making it impossible to do any maintenance tasks (ANALYZE, VACUUM for example) on the corresponding table. pgAdmin III Server Status reports for Status: idle in transaction
Actually, GeoServer 2.5.2 issued a ROLLBACK statement as it's last operation on the connection before returning it to the pool. That seems to be missing with the new version.
Maybe a ROLLBACK statement in the Session close-up SQL could help as a workaround (it actually does for read-only requests like WMS requests), but I do not know what problems arise with WFS-T transactions like insert, update or delete.
CodeHaus Comment From: cklein05 - Time: Tue, 7 Oct 2014 07:23:20 -0500
<p>after some more research and digging it turned out, that GeoServer/GeoTools seems to rely on the class(es) of JDBC connections pools for its decision, when to issue <tt>ROLLBACK</tt> and when not. Actually, the problem is not specific to 2.6.0 but also occurs with 2.5.2 and likely many other previous versions.</p>
<p>The problem seems to be Tomcat 7's new JDBC connection pool <tt>org.apache.tomcat.jdbc.pool</tt> (see <a href="http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html" class="external-link" rel="nofollow">The Tomcat JDBC Connection Pool</a>). Using this pool seems GeoServer to cause problems by issuing <tt>BEGIN</tt> but no <tt>ROLLBACK</tt> statements for some of the SQL statements (typically, the last statement(s) needed for the request).</p>
<p>So, actually, this works fine with both the original Apache Commons DBCP pool (1.4 and 1.3 which is used by GeoServer/GeoTools internally when not using a JNDI-based data source) and the repackaged Tomcat JDBC Pool in tomcat-dbcp.jar.</p>
<p>The latter JAR is not distributed by Debian/Ubuntu, so they've always used the original Apache Commons DBCP, which was installed as a dependency by apt. Now, since Tomcat 7 has its own pool (which created from scratch), Debian/Ubuntu decided to make that pool the default by patching Tomcat's Java source files (that's great, isn't it?).</p>
<p>So, at least on current Ubuntu Trusty, you have to hack a bit to get the old 1.4 DBCP. By default, you'll get the new Tomcat JDPC pool. Actually, this does no <tt>ROLLBACK</tt> when a connection is returned to the pool (at least not by default). Likely this could be configured.</p>
<p>However, there is a problem with these out-of-the-box configured Ubuntu machines. I'm really not sure whether its a good idea to start a transaction explicitly and expect the pool to roll it back. However, at current, I don't see any solution to that.</p>
<p>Maybe it is best to configure the new Tomcat pool to issue a <tt>ROLLBACK</tt> as well. However, this should be stated by the docs.</p>
CodeHaus Comment From: aaime - Time: Tue, 7 Oct 2014 07:26:48 -0500
<p><a href="http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html" class="external-link" rel="nofollow">http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html</a>
You have to set rollback on return to true, it's false by default in this pool</p>
CodeHaus Comment From: dcalde - Time: Thu, 23 Oct 2014 19:25:17 -0500
<p>We are having the same problem. I don't believe that geoserver/geotools should make the assumption that a certain option on the jndi datasource has been configured or is even available. If you are deploying geoserver in a shared JVM with global JNDI resources, you may not have the option to enable auto-rollback. Further, BoneCP for example has a provision for this feature but it is documented as "debugging only" and the configuration option is named "detectUnresolvedTransactions", indicating that this is not normal or recommended behaviour. </p>
CodeHaus Comment From: aaime - Time: Fri, 24 Oct 2014 01:11:14 -0500
<p>Indeed the ticket was left open. Patches welcomed.</p>
CodeHaus Comment From: smalchev - Time: Tue, 28 Oct 2014 06:15:47 -0500
<p>Hi everyone, </p>
<p>I can see that the overall opinion is in direction the transaction's ROLLBACK statement to be issued by the GeoServer application itself but not by the used JDBC connection pool, and I just wanted to add one more vote for this. I'm the main author of Vibur DBCP which is another JDBC connection pool with functionality similar to the pools mentioned above, and I can clearly remember that at the time we decided against implementing an option like "commit or rollback forgotten transactions" as we felt that such option may help to hide an application bug. If in future we decide to include similar option it will definitely be implemented for "application debugging" purposes only, similarly to how it is done by BoneCP.</p>