Database pool with PGPoolingDataSource?

Question

I am having problems creating a database pool with the PGPoolingDataSource class, after a while the pool leaves when many users are working, and does not show errors.

The code for the class that creates the pool:

public class PgConexion { private static PgConexion _instancia = new PgConexion(); // instancia de la clase private Configuracion config; private PGPoolingDataSource source; /** * instancia la clase y carga las opciones de configuracion */ public PgConexion() { final URL archivo = Constantes.RUTA_CONFIG; if(archivo != null){ config = new Configuracion(archivo); } } /** * regresa la instancia del pool de conexiones * @return */ public static PgConexion getInstance() { return _instancia; } /** * crear la conexion la conexion * @return * @throws SQLException */ public void crearConexion() throws SQLException{ source = new PGPoolingDataSource(); // configuracion del pool source.setDataSourceName("Logistica"); source.setServerName(config.get("servidor_sql")); source.setPortNumber(Integer.parseInt(config.get("puerto_sql"))); source.setDatabaseName(config.get("bd_sql")); source.setUser(config.get("usuario_sql")); source.setPassword(config.get("contrasena_sql")); source.setMaxConnections(30); } /** * devuelve la conecion a utilizar * @return * @throws SQLException */ public Connection nuevaConexion() throws SQLException{ if(source == null){ crearConexion(); } // genero la conexion de la lista del pool return source.getConnection(); } /** * Cierra las conexiones y libera los recursos */ public void cerrarConexion(){ source.close(); } } 

How to fix it?

Answer 1

It's not a good idea to use PGPoolingDataSource , as the JDBC documentation explains .

The main problem is that the call to getConnection () will be blocked until the connection is closed when the connection limit is reached.

You set 30 as the maximum number of concurrent connections, so if the 31st is to be opened, it will block the thread making the call.

Possible solutions:

• Increase maxConnections if you are sure of the actual size of the upper limit of concurrent connections. You should also check the server side restriction in postgresql.conf .
• Use PGSimpleDataSource . Depending on the type of application, not using the connection pool (thus creating a connection every time) will not be a problem.
• If you really need a connection pool, just implement your own at the Java level.

EDIT: you can check the number of open connections by simply doing:

 SELECT * FROM pg_stat_activity 

Each line is a connection (including one of pgAdmin and a query analyzer). If you are sure that the number of connections should not go up to the upper limit (but nevertheless it is), you may have some kind of connection leakage problem.

Answer 2

If my experience matters, the problem is probably not here, but rather that your clients do not always close their connections for one reason or another - often forgetting to do this in error handling scripts.

The best way to handle this depends on the scenario. If the client is your own code, it is very different from when the client is code written by an unknown person in an unknown organization.