javadatabasegoogle-cloud-sqlhikaricp

"FATAL: remaining connection slots are reserved for non-replication superuser connections" triggers despite closing connections


I've created a simple program that repeatedly creates a connection to my database then closes the connection. The program produces an error on the sixth or seventh iteration. If I pause the program between each iteration for five seconds, the program produces an error on the third iteration. Printing out the connection status reveals that they are being closed successfully, so no connection leak should be occurring. My connections are being made with HikariCP and Google Cloud's IAM authentication.

package Tester;

import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import InstanceCreator.InstanceCreator;

public class Tester {

    public static void main(String[] args) throws SQLException, InterruptedException {

        int i = 1;
        while (true) {
            System.out.println("Starting loop: " + i);
            i++;
            Thread.sleep(5000);
            DataSource dataSource = InstanceCreator.createConnectionPool();
            Connection connection = null;
            try {
                connection = dataSource.getConnection();

            } catch (SQLException e) {
                e.printStackTrace();
            } 
            finally {
                connection.close();
            }
        }
    }
}


package InstanceCreator;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;

public class InstanceCreator {

      private static final String INSTANCE_CONNECTION_NAME = "mycreds";
      private static final String DB_IAM_USER = "mycreds";
      private static final String DB_NAME = "mycreds";

      public static DataSource createConnectionPool() {

        HikariConfig config = new HikariConfig();

        config.setJdbcUrl(String.format("jdbc:postgresql:///%s", DB_NAME));

        config.addDataSourceProperty("socketFactory", "com.google.cloud.sql.postgres.SocketFactory");
        config.addDataSourceProperty("cloudSqlInstance", INSTANCE_CONNECTION_NAME);
        config.addDataSourceProperty("enableIamAuth", "true");
        config.addDataSourceProperty("user", DB_IAM_USER);
        config.addDataSourceProperty("password", "password");
        config.addDataSourceProperty("sslmode", "disable");

        return new HikariDataSource(config);
      }
    }


Starting loop: 1
SLF4J(W): No SLF4J providers were found.
SLF4J(W): Defaulting to no-operation (NOP) logger implementation
SLF4J(W): See https://www.slf4j.org/codes.html#noProviders for further details.
Starting loop: 2
Starting loop: 3
Starting loop: 4
Starting loop: 5
Starting loop: 6
Starting loop: 7
Exception in thread "main" com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: FATAL: remaining connection slots are reserved for non-replication superuser connections
    at com.zaxxer.hikari.pool.HikariPool.throwPoolInitializationException(HikariPool.java:596)
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:582)
    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)
    at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:81)
    at InstanceCreator.InstanceCreator.createConnectionPool(InstanceCreator.java:27)
    at Tester.Tester.main(Tester.java:17)
Caused by: org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
    at org.postgresql.core.v3.QueryExecutorImpl.readStartupMessages(QueryExecutorImpl.java:2788)
    at org.postgresql.core.v3.QueryExecutorImpl.<init>(QueryExecutorImpl.java:174)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:313)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:54)
    at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:253)
    at org.postgresql.Driver.makeConnection(Driver.java:434)
    at org.postgresql.Driver.connect(Driver.java:291)
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:121)
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364)
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206)
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:476)
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561)
    ... 4 more


Solution

  • Each time in the loop, you create not one connection, but a pool of connections. By default, one HikariCP pool opens 10 connections to the database, therefore at the sixth iteration you have 60 active connections, and at the seventh there are already 70. Any DBMS has a limit on the maximum number of connection slots; in PostgreSQL it is 100 by default. Having created a lot of connections, you quickly exhausted this limit and received the error org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections. PostgreSQL reserves three connection slots for administrative purposes so that the supervisor can connect to the DBMS even when limit is exhausted. Do not do this, use connections sparingly.