HikariCP: Postgres timestamptz column timezone disappears in resultset of PreparedStatement after 5 queries

Question

my slick mapping in play2.4 wasn't working, and I boiled down the problem to this: If I do a simple select from a table with a timestamp with time zone column, the timezone disappears from the results after a while. The example at the end of this message produces the following output: 2015-10-27 20:45:13.459+01 2015-10-27 20:45:13.459+01 2015-10-27 20:45:13.459+01 2015-10-27 20:45:13.459+01 2015-10-27 20:45:13.459+01 // from now on, the timezone is never returned (even after 1000 queries) 2015-10-27 20:45:13.459 2015-10-27 20:45:13.459 2015-10-27 20:45:13.459 2015-10-27 20:45:13.459 ...

• If I create the connection directly without using HikariCP, it works.
• If I don't close the connection at the end of every query in the loop (i.e. I leak the connection), it works
• If I use the same connection without creating/closing a new one (i.e. getting one from the pool and releasing it) each time, it works
• If I create and close a new connection each time using standard DriverManager.getConnection with the same URL, it works.
• If I don't prepare the statement, it works
• If I prepare the statement twice (even without using the second one), it works
• If I prepare a second statement different from the first one, it doesn't work

I tried both normal and java6 versions of hikaricp. I'm using postgres 9.4. I work in scala but I created the example in java to broaden the audience ;)

I opened an issue but I'm in a bit of a rush so if anyone knows what to do... If anyone can point me to how to use bonecp or anything else in play 2.4, I'd be grateful as well.

You can reproduce the issue with a project with just the HikariCP and the postgres dependencies:

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class TestHikari {
    public static void main(String[] args) throws Exception {
        HikariConfig conf = new HikariConfig();
        conf.setJdbcUrl("jdbc:postgresql://localhost/postgres");
        conf.setDriverClassName("org.postgresql.Driver");

        HikariDataSource ds = new HikariDataSource(conf);
        Connection ddlconn = ds.getConnection();
        try { ddlconn.createStatement().execute("DROP TABLE TEST"); } catch(Exception ignored) {}
        ddlconn.createStatement().execute("CREATE TABLE TEST (ts TIMESTAMP with time zone)");
        ddlconn.createStatement().execute("insert into test(ts) values('2015-10-27 20:45:13.459+01')");
        ddlconn.close();

        for (int i = 0; i< 10; i++) {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement("select ts from TEST");
            //if I uncomment the next line, it works!
            //conn.prepareStatement("select ts from TEST");
            ResultSet rs = stmt.executeQuery();
            rs.next();

            System.out.println(rs.getString(1));

            conn.close();
        }
    }
}

Answer 1

This question has been answered here: https://github.com/brettwooldridge/HikariCP/issues/473

There were some issues with dates / timezones when the JDBC driver switches to server-side prepared statements. By default this happens after 5 prepared statement executions (see prepareThreshold parameter of postgresql JDBC).

So this is a bug in the postgres jdbc driver. it seems the only workaround for now is setting prepareThreshold=0 or switching to timestamp without time zone.