Hikaricp: Connection behaving differently

[debasishdas01]

package XXX;

import org.postgresql.PGConnection;

import java.sql.*;

public class NotificationTest {

public static void main(String args[]) throws Exception {
    Class.forName("org.postgresql.Driver");
    String url = "jdbc:postgresql://localhost:5432/TAHITIAN_APP_DB";

//un-comment the next line and comment the line following next. Run to see 2 different results.

// Connection lConn = DriverManager.getConnection(url,"tahitianuser","tahitian");
Connection lConn = DaoUtil.getDBConnection().unwrap(Connection.class);

    System.out.println("lConn: " +lConn.toString());

    // Create two threads, one to issue notifications and
    // the other to receive them.
    Listener listener = new Listener(lConn);
    Notifier notifier = new Notifier(lConn);
    listener.start();
    notifier.start();
}

}

class Listener extends Thread {

private Connection conn;
private org.postgresql.PGConnection pgconn;

Listener(Connection conn) throws SQLException {
    this.conn = conn;
    this.pgconn = conn.unwrap(PGConnection.class);
    Statement stmt = conn.createStatement();
    stmt.execute("LISTEN new_facility_added");
    stmt.close();
}

public void run() {
    while (true) {
        try {
            // issue a dummy query to contact the backend
            // and receive any pending notifications.
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT 1");
            rs.close();
            stmt.close();

            org.postgresql.PGNotification notifications[] = pgconn.getNotifications();
            if (notifications == null) {
                System.out.println("Got no notification");
            } else {
                for (int i=0; i<notifications.length; i++) {
                    System.out.println("Got notification: " + notifications[i].getName());
                }
            }

            // wait a while before checking again for new
            // notifications
            Thread.sleep(1000);
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        } catch (InterruptedException ie) {
            ie.printStackTrace();
        }
    }
}

}

class Notifier extends Thread {

private Connection conn;

public Notifier(Connection conn) {
    this.conn = conn;
}

public void run() {
    while (true) {
        try {
            Statement stmt = conn.createStatement();
            stmt.execute("NOTIFY new_facility_added");
            stmt.close();
            Thread.sleep(2000);
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        } catch (Exception ie) {
            ie.printStackTrace();
        }
    }
}

}

---

DaoUtil.getDBConnection() basically returns the following

        Sql2o sql2o = new Sql2o(aDataSource, new PostgresQuirks());
        return (sql2o.open().getJdbcConnection());

and the DataSource is -----HikariDataSource aDataSource

---

Results using -- Connection lConn = DriverManager.getConnection(url,"tahitianuser","tahitian");

lConn: org.postgresql.jdbc4.Jdbc4Connection@6d21714c
Got notification: new_facility_added
Got no notification
Got notification: new_facility_added
Got no notification
Got notification: new_facility_added
Got no notification
Got notification: new_facility_added
Got no notification
Got notification: new_facility_added
Got no notification
Got notification: new_facility_added

---

Results using -- Connection lConn = DaoUtil.getDBConnection().unwrap(Connection.class);
or Connection lConn = DaoUtil.getDBConnection();

2017-02-22 16:09:50,198 [main] INFO com.zaxxer.hikari.HikariDataSource - TahitianConnectionPool_1 - Started.
2017-02-22 16:09:50,352 [main] INFO com.zaxxer.hikari.pool.PoolBase - TahitianConnectionPool_1 - Driver does not support get/set network timeout for connections. (Method org.postgresql.jdbc4.Jdbc4Connection.getNetworkTimeout() is not yet implemented.)
lConn: org.postgresql.jdbc4.Jdbc4Connection@71e7a66b
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification

---

Am using the following maven config

    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>9.4-1200-jdbc41</version>
    </dependency>
    <dependency>
        <groupId>com.zaxxer</groupId>
        <artifactId>HikariCP</artifactId>
        <version>2.5.1</version>

Appreciate your help!

DD

[debasishdas01]

    <dependency>
        <groupId>org.sql2o</groupId>
        <artifactId>sql2o</artifactId>
        <version>1.5.4</version>
    </dependency>

[brettwooldridge]

It works fine for me (without sql2o). I created a junit test here that verifies that it is working.

[brettwooldridge]

Note that I tested with HikarCP 2.6.0 (shouldn't matter), and pgsql driver version 9.4.1212.jre7.

[brettwooldridge]

Tested again with HikariCP v2.5.1 and pgsql driver 9.4-1200-jdbc41, same result. Working.

[debasishdas01]

Thanks Brett!
I tried with HikarCP 2.6.0 and pgsql driver version 9.4.1212.jre7 and get the same result as before (not working).
I already put the error in sql2o issue list.
Thanks again for your help!

[brettwooldridge]

It is strange that it is not working, given that both log having a reference to an actual pgsql connection object. I do suggest trying with the junit test I referenced. I will note that most drivers do not explicitly support simultaneous access to a single connection from two threads, but it in this case it seems to work and is not likely an issue.

[brettwooldridge]

It is possible there is an unintended side-effect from calling:

return (sql2o.open().getJdbcConnection());

In this case, a temporal sql2o connection object is being created (the open()), the underlying Connection obtained, and then the sql2o object is being garbage collected. If the sql2o connection object has a finalizer, it could somehow be closing the underlying connection. Though in that case, I would expect SQLExceptions when the statements are executed...

Either way, you may try hanging onto (maintaining a reference to) the sql2o connection object just to see what happens.

[debasishdas01]

Hi Brett,
The original code looks something like this with an additional, if check, for database vendor which I removed here to make it simple.

public Connection connect() throws SQLException
{
    Connection aDatabaseConnection = null;
    Sql2o sql2o = new Sql2o(aDataSource, new PostgresQuirks());
    aDatabaseConnection = sql2o.open().getJdbcConnection();

    if(Validator.isEmpty(aDatabaseConnection)) {
        tahitianLogger.fatal("Connection returned is null");
    }
    return aDatabaseConnection;
}

[debasishdas01]

Tried the test case you sent and it works fine. Here are the results

2017-02-22 18:26:32,090 [main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Starting...
2017-02-22 18:26:32,299 [main] INFO com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Driver does not support get/set network timeout for connections. (Method org.postgresql.jdbc4.Jdbc4Connection.getNetworkTimeout() is not yet implemented.)
2017-02-22 18:26:32,328 [main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Start completed.
lConn: org.postgresql.jdbc4.Jdbc4Connection@4cdbe50f
Got no notification
Got notification: new_facility_added
Got no notification
Got notification: new_facility_added
Got no notification
Got notification: new_facility_added
Got no notification

[brettwooldridge]

@debasishdas01 Can you try adding notifier.join() to your original test's main() method?

[debasishdas01]

Added notifier.join() in original test's main() - still not working. Here are the results

2017-02-22 19:41:44,054 [main] INFO com.zaxxer.hikari.HikariDataSource - TahitianConnectionPool_1 - Starting...
2017-02-22 19:41:44,207 [main] INFO com.zaxxer.hikari.pool.PoolBase - TahitianConnectionPool_1 - Driver does not support get/set network timeout for connections. (Method org.postgresql.jdbc4.Jdbc4Connection.getNetworkTimeout() is not yet implemented.)
2017-02-22 19:41:44,234 [main] INFO com.zaxxer.hikari.HikariDataSource - TahitianConnectionPool_1 - Start completed.
lConn: org.postgresql.jdbc4.Jdbc4Connection@27f674d
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification
Got no notification

[brettwooldridge]

@debasishdas01 Oh well, it was a thought. Maybe with the test cases (yours and mine) the sql2o guys can figure it out. Very strange...

Feel free to link to this issue from their issue tracker, and if you can put a link to the sql2o issue here so that in the future users can find whatever the final sql2o resolution is.

[debasishdas01]

Finally figured out the issue.
I have hikariConfig.setAutoCommit(false);