BUG #14452: Unexpected transaction behaviour

The following bug has been logged on the website:

Bug reference: 14452
Logged by: Rodrigo Garcia
Email address: rjsdg(at)sapo(dot)pt
PostgreSQL version: 9.6.1
Operating system: Windows 7 64bit
Description:

Hello,

I have a OLTP system supported by a Java standalone app using JDBC 42 Driver
postgresql-jdbc-9.4-1212 and a backend DB running in "PostgreSQL 9.6.1,
compiled by Visual C++ build 1800, 64-bit”. The Os is Windows 7 64 bit.

The system normal throughput is about 110 transactions/min constantly
without degradation. Recently a new Java module of the application was
installed and when the app startups the system throughput starts to degrade
to the point that reaches 50 transactions/min, it’s not an immediate
downfall in the throughput but it looses like 5 to 10 transactions per
minute until it reaches 50 transactions per minute. At this point if we
terminate the only existing backend pid of the new Java module (that is in
“idle in transaction” with a backend_xmin value) immediatly in the next
minute the throughput rises again to 110 transactions/min.

We’re not being able to identify the root cause for this behaviour, but we
found out that this occurs only when we use JDBC Driver via Java and is
reproducible whenever we use JDBC driver with a small piece of Java code
(simulating the new Java module). At POINT 4 below we stay with a “idle in
transaction” session and a backend_xmin value corresponding to the snapshot
of the moment when the “select * from x” query was fired. This doesn’t
happen when we connect via psql and reproduce the similar actions the issue
is not occuring, this is, with psql we stay at POINT 4 with a “idle in
transaction” session but without a backend_xmin value.

Reading PG documentation this behaviour around backend_xmin depends on the
transaction isolation level, the snapshot collection is diferent. We suspect
that the loss of throughput resides in the fact that the “idle in
transaction” with a backend_xmin avoids or causes delays in the MVCC
versioning core system of Postgres. Nevertheless we don’t understang why it
happens only when using JDBC.

The new Java module does the following code:

Class.forName("org.postgresql.Driver");
String url = "jdbc:postgresql://127.0.0.1:5433/postgres";
Properties props = new Properties();
props.setProperty("user”,"postgres");
props.setProperty("password”,”**********");
Connection conn = DriverManager.getConnection(url,props);
conn.setAutoCommit(true);
PreparedStatement st = conn.prepareStatement("BEGIN");
st.execute();
st = conn.prepareStatement("select * from x");
st.execute();
//POINT 4 => At this point pg_stat_activity shows "idle in transaction” and
a backend_xmin value filled
Thread.sleep(300000);
conn.close();

The same actions via PSQL (after connect) are:

BEGIN;
select * from x;
=> At this point pg_stat_activity shows "idle in transaction” and
backend_xmin is null

Why the differente behaviour? We understand that PSQL and JDBC use different
protocols but in terms of transaction behaviour should be the same, the Java
code and PSQL are equivalent in terms of transaction behaviour. Can someone
help?