0

I am using a PostGreSQL database to store data. (I use hikariCP btw) I have many methods that collect connections from a Hikari Datasource:

public Connection getConnection() {
        try {
            return dataSource.getConnection();
        }
        catch (SQLException ex)
        {
            return null;
        }
    }

And use them to carry out various functions. Recently, I have run into an error that happens when the database receives a lot of connections:

com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: FATAL: sorry, too many clients already
Caused by: org.postgresql.util.PSQLException: FATAL: sorry, too many clients already

I have already tried to edit my configuration, search google, and run tests, but they didn't fix it. Could someone here help me fix it, or provide information as to how? Here is my configuration in my database class constructor:

PGSimpleDataSource ds = new PGSimpleDataSource();
        ds.setDatabaseName("BGWW Bot");
        ds.setServerNames(new String[] {"localhost"});
        ds.setUser(dbUser);
        ds.setPassword(dbPass);
        // Configure the HikariDatesource; add PGDatasource
        config.setJdbcUrl("jdbc:postgresql://localhost/BGWW Bot");
        config.setMinimumIdle(5);
        config.setMaximumPoolSize(100);
        config.setMaxLifetime(30000);
        config.setIdleTimeout(30000);
        config.setConnectionTimeout(60000);
        config.setLeakDetectionThreshold(10000);
        config.addDataSourceProperty("max_connections", "100");
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        config.setDataSource(ds);

Here is a method that gets a connection:

public String[] getMember(String uuid, TextChannel channel)
    {
        try (Connection conn = getConnection())
        {
            String getUserSTR = "SELECT * FROM guild_members WHERE uuid = ?";

            PreparedStatement stmt = conn.prepareStatement(getUserSTR);

            stmt.setString(1, uuid);

            ResultSet rs = stmt.executeQuery();

            while(rs.next())
            {
                if(rs.getString(1).equals(uuid))
                {
                    String[] array = new String[]{rs.getString(1), rs.getString(2), rs.getString(3)};
                    conn.close();
                    return array;
                }
            }
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
            return null;
        }
        return null;
    }

I also found something interesting, in which I opened and then closed new connections rapidly, 150 times, but they didn't produce any error.

What could be causing this error? How can I fix it? Thanks!

GouramEats
  • 51
  • 8
  • You're using more connections than your PostgreSQL server is configured to allow. – Mark Rotteveel Mar 10 '21 at 08:15
  • Ok, but the thing is, everytime i request a connection, and there are a bunch of idle connections, it still creates a new connection. And then, when I close it, the connection doesn't disappear from the table. Am I doing something wrong? – GouramEats Mar 10 '21 at 22:30
  • Closing connections returns them to the pool, so of course they don't disappear, because the connection is still open. You are probably not closing connections everywhere, leading to more connections being allocated (BTW: setting the maximum pool size to 100 is overkill for most applications, and may in some cases even actively hurt performance, did you do proper performance testing to arrive at this number?). – Mark Rotteveel Mar 11 '21 at 09:20
  • No not really. I'll look through my code and see if there are some connections, statements, or Resultsets I forgot to close. – GouramEats Mar 11 '21 at 16:58

0 Answers0