Hikaricp: Failed to create a Non-Pooling DataSource from PostgreSQL JDBC Driver 42.1.1

[ngigiwaithaka]

Environment

HikariCP version: 2.6.1
JDK version     : 1.8.0_111
Database        : PostgreSQL
Driver version  : 42.1.1

---

Hello,

I have an issue that has completely stumped me when trying to use HikariCP with Postgresql 9.6 on Ubuntu 16 Linux.

Without using HikariCP and using the default Connection without Connection Pooling, it works as expected.

However, when using HikariCP and Ireuse the connection for the 5th time (e.g Refresh 5 times), I get the following error:

Failed to create a Non-Pooling DataSource from PostgreSQL JDBC Driver 42.1.1 for postgres at jdbc:postgresql://localhost/db_ke_elections?loginTimeout=30: org.postgresql.util.PSQLException: FATAL: sorry, too many clients already
Connection error:

org.postgresql.util.PSQLException: FATAL: sorry, too many clients already
at org.postgresql.Driver$ConnectThread.getResult(Driver.java:401)
at org.postgresql.Driver.connect(Driver.java:259)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at org.postgresql.ds.common.BaseDataSource.getConnection(BaseDataSource.java:94)
at org.postgresql.ds.common.BaseDataSource.getConnection(BaseDataSource.java:79)
at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:356)
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:199)
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:444)
at com.zaxxer.hikari.pool.HikariPool.access$200(HikariPool.java:71)
at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:631)
at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:617)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:748)

The code that is executing this:

private static HikariDataSource getHikariDataSource(Application myApplication, final boolean useSimplePostgreDriver){   

    Properties props = new Properties();


    if (useSimplePostgreDriver){
        props.setProperty("dataSourceClassName", "org.postgresql.ds.PGSimpleDataSource");           
        props.setProperty("dataSource.user", "postgres");
        props.setProperty("dataSource.password", "password");
        props.setProperty("dataSource.databaseName", myApplication.getDatabaseName());

        props.put("dataSource.logWriter", new PrintWriter(System.out));

    } else{
        props.setProperty("dataSourceClassName", "com.impossibl.postgres.jdbc.PGDataSource");
        props.setProperty("dataSource.user", "postgres");
        props.setProperty("dataSource.password", "password");
        props.setProperty("dataSource.host", "localhost");
        props.setProperty("dataSource.database", myApplication.getDatabaseURL());
    }
    HikariConfig config = new HikariConfig(props);

    /*if (useSimplePostgreDriver){
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
    }*/
    HikariDataSource ds = new HikariDataSource(config);     
    ds.setMaximumPoolSize(10);
    ds.setConnectionTimeout(34000);
    ds.setIdleTimeout(28740000);
    ds.setMaxLifetime(28740000);
    ds.addDataSourceProperty("ssl.mode", "disable");

    return ds;
}

This is how I am closing all the resources in the Finally section. I have confirmed this is executing everytime

public static void closeQuietly(ResultSet rs, PreparedStatement ps, Connection conn){
   try { if (rs !=null); rs.close(); rs =null;} catch (Exception e) { /* ignored */ }
   try { if (ps!=null); ps.close(); ps=null;} catch (Exception e) { /* ignored */ }
   try { if(conn!=null); conn.close();conn=null;/*ds.close()*/;} catch (Exception e) { /* ignored */ }
}

What could be the problem?

[brettwooldridge]

@ngigiwaithaka I suspect that you are creating many pools, instead of one. If you are calling getHikariDataSource() multiple times, you are creating a new pool instance each time. The pool should be an application-wide singleton.

[ngigiwaithaka]

Hi Brett,

I get it now... Somehow it's not obvious from the documentation that the
Datasource should be a singleton.

That has fixed the problem.

Thanks a lot...

On 24 Jun 2017 7:36 p.m., "Brett Wooldridge" [e-mail]
wrote:

@ngigiwaithaka https://github.com/ngigiwaithaka I suspect that you are
creating many pools, instead of one. If you are calling
getHikariDataSource() multiple times, you are creating a new pool
instance each time. The pool should be an application-wide singleton
https://en.wikipedia.org/wiki/Singleton_pattern#Lazy_initialization.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/brettwooldridge/HikariCP/issues/924#issuecomment-310849285,
or mute the thread
https://github.com/notifications/unsubscribe-auth/ARqlNEhDeiqRE39m-j9buacxBXBTEfgDks5sHTsSgaJpZM4OEUHT
.