Apache Cayenne / PostgreSQL: “too many clients alr

Question

I'm using Apache Cayenne 4 Milestone 5 and I'm getting this error. The application is not officially deployed but it is being tested within the IntelliJ IDEA.

Apr 03, 2017 10:19:58 PM org.postgresql.Driver connect
SEVERE: Connection error: 
org.postgresql.util.PSQLException: FATAL: sorry, too many clients already
    at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:438)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:222)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
    at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:194)
    at org.postgresql.Driver.makeConnection(Driver.java:431)
    at org.postgresql.Driver.connect(Driver.java:247)
    at org.apache.cayenne.datasource.DriverDataSource.getConnection(DriverDataSource.java:159)
    at org.apache.cayenne.datasource.DriverDataSource.getConnection(DriverDataSource.java:133)
    at org.apache.cayenne.datasource.UnmanagedPoolingDataSource.createUnwrapped(UnmanagedPoolingDataSource.java:300)
    at org.apache.cayenne.datasource.UnmanagedPoolingDataSource.createWrapped(UnmanagedPoolingDataSource.java:293)
    at org.apache.cayenne.datasource.UnmanagedPoolingDataSource.createUnchecked(UnmanagedPoolingDataSource.java:273)
    at org.apache.cayenne.datasource.UnmanagedPoolingDataSource.<init>(UnmanagedPoolingDataSource.java:142)
    at org.apache.cayenne.datasource.PoolingDataSourceBuilder.buildPooling(PoolingDataSourceBuilder.java:92)
    at org.apache.cayenne.datasource.PoolingDataSourceBuilder.build(PoolingDataSourceBuilder.java:88)
    at org.apache.cayenne.configuration.server.XMLPoolingDataSourceFactory.getDataSource(XMLPoolingDataSourceFactory.java:75)
    at org.apache.cayenne.configuration.server.DelegatingDataSourceFactory.getDataSource(DelegatingDataSourceFactory.java:68)
    at org.apache.cayenne.configuration.server.DefaultDataNodeFactory.createDataNode(DefaultDataNodeFactory.java:79)
    at org.apache.cayenne.configuration.server.DataDomainProvider.addDataNode(DataDomainProvider.java:175)
    at org.apache.cayenne.configuration.server.DataDomainProvider.createAndInitDataDomain(DataDomainProvider.java:127)
    at org.apache.cayenne.configuration.server.DataDomainProvider.get(DataDomainProvider.java:91)
    at org.apache.cayenne.configuration.server.DataDomainProvider.get(DataDomainProvider.java:56)
    at org.apache.cayenne.di.spi.CustomProvidersProvider.get(CustomProvidersProvider.java:39)
    at org.apache.cayenne.di.spi.FieldInjectingProvider.get(FieldInjectingProvider.java:43)
    at org.apache.cayenne.di.spi.DefaultScopeProvider.get(DefaultScopeProvider.java:50)
    at org.apache.cayenne.di.spi.DefaultInjector.getInstance(DefaultInjector.java:139)
    at org.apache.cayenne.di.spi.FieldInjectingProvider.value(FieldInjectingProvider.java:105)
    at org.apache.cayenne.di.spi.FieldInjectingProvider.injectMember(FieldInjectingProvider.java:68)
    at org.apache.cayenne.di.spi.FieldInjectingProvider.injectMembers(FieldInjectingProvider.java:59)
    at org.apache.cayenne.di.spi.FieldInjectingProvider.get(FieldInjectingProvider.java:44)
    at org.apache.cayenne.di.spi.DefaultScopeProvider.get(DefaultScopeProvider.java:50)
    at org.apache.cayenne.di.spi.DefaultInjector.getInstance(DefaultInjector.java:134)
    at org.apache.cayenne.configuration.CayenneRuntime.newContext(CayenneRuntime.java:124)
    at com.company.databases.postgresql.accountsdb.dataservices.AccountServices.loginAsEmailAddress(AccountServices.java:454)
    at com.company.databases.postgresql.accountsdb.dataservices.AccountServices.login(AccountServices.java:445)
    at com.company.actions.fan.account.AccountActions.login(AccountActions.java:44)
    at com.company.serverapps.fans.entrypoints.standard.AccountConnections.login(AccountConnections.java:95)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81)
    at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:144)
    at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:161)
    at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$ResponseOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:160)
    at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:99)
    at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:389)
    at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:347)
    at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:102)
    at org.glassfish.jersey.server.ServerRuntime$2.run(ServerRuntime.java:326)
    at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271)
    at org.glassfish.jersey.internal.Errors$1.call(Errors.java:267)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:315)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:297)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:267)
    at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:317)
    at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:305)
    at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:1154)
    at org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:473)
    at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:427)
    at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:388)
    at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:341)
    at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:228)
    at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:841)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1634)
    at org.eclipse.jetty.servlets.CrossOriginFilter.handle(CrossOriginFilter.java:308)
    at org.eclipse.jetty.servlets.CrossOriginFilter.doFilter(CrossOriginFilter.java:262)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1621)
    at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:541)
    at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:188)
    at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1592)
    at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:188)
    at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1239)
    at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:168)
    at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:481)
    at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1561)
    at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:166)
    at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1141)
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
    at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:213)
    at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
    at org.eclipse.jetty.server.Server.handle(Server.java:564)
    at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:320)
    at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:251)
    at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:279)
    at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:110)
    at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:124)
    at org.eclipse.jetty.util.thread.Invocable.invokePreferred(Invocable.java:122)
    at org.eclipse.jetty.util.thread.strategy.ExecutingExecutionStrategy.invoke(ExecutingExecutionStrategy.java:58)
    at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceConsume(ExecuteProduceConsume.java:201)
    at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:133)
    at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:672)
    at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:590)
    at java.lang.Thread.run(Thread.java:745)

Apr 03, 2017 10:21:58 PM org.postgresql.Driver connect
SEVERE: Connection error: 
org.postgresql.util.PSQLException: FATAL: sorry, too many clients already
    at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:438)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:222)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
    at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:194)
    at org.postgresql.Driver.makeConnection(Driver.java:431)
    at org.postgresql.Driver.connect(Driver.java:247)
    at org.apache.cayenne.datasource.DriverDataSource.getConnection(DriverDataSource.java:159)
    at org.apache.cayenne.datasource.DriverDataSource.getConnection(DriverDataSource.java:133)
    at org.apache.cayenne.datasource.UnmanagedPoolingDataSource.createUnwrapped(UnmanagedPoolingDataSource.java:300)
    at org.apache.cayenne.datasource.UnmanagedPoolingDataSource.createWrapped(UnmanagedPoolingDataSource.java:293)
    at org.apache.cayenne.datasource.UnmanagedPoolingDataSource.createUnchecked(UnmanagedPoolingDataSource.java:273)
    at org.apache.cayenne.datasource.UnmanagedPoolingDataSource.managePool(UnmanagedPoolingDataSource.java:188)
    at org.apache.cayenne.datasource.PoolingDataSourceManager.run(PoolingDataSourceManager.java:71)

The applicaion is working normally, but it shows this error after some usage. What is the cause of this error?

I'm using the following method to obtain the runtime each time I need a transaction.

public final static ServerRuntime getAccountsDBPostgreSQLRuntime() {
        return ServerRuntime.builder().addConfig("cayenne/cayenne-AccountsDB.project.xml").build();
    }

Example:

public final static Long newFanAccount() {
        ServerRuntime accountsDBPostgresRuntime = AccountsDBRuntime.getAccountsDBPostgreSQLRuntime();
        ObjectContext context = accountsDBPostgresRuntime.newContext();
        Fan fan = context.newObject(Fan.class);
        context.commitChanges();
        Long fanID = (Long) Cayenne.pkForObject(fan);
        return fanID;
    }

Answer 1

From the stack trace and the code example it looks like you are not reusing the Cayenne stack (ServerRuntime object), creating a new one every time instead. Aside from being slow, this also introduces a resource leak. Each ServerRuntime holds its own connection pool that you do not shut down. So sooner or later PostreSQL server is overflown with open connections.

The solution is to turn ServerRuntime into an app-scoped singleton (e.g. make it a static var of AccountsDBRuntime), and reuse it whenever you need a new ObjectContext.