Re: Problem with JTA/JTS

Johan,

You are running into the max number of allowed connections limit on the
server.  You should increase the value of max_connections in the
postgresql.conf file.

--Barry

Johan Svensson wrote:
> Hi,
>
> I've run in to some troubles using PostgreSQL 7.2 and JTA/JTS. We are
> developing a J2EE application using JBossTx as JTA implementation. The
> problem occurs when lots of small transactions are created and executed
> sequentially. Below is a code snippet that simulates JTA interaction
> with the PostgreSQL data source adapter and triggers the error.
>
> --- begin code snippet ---
>
>    org.postgresql.PostgresqlDataSource xaDs =
>    new org.postgresql.PostgresqlDataSource();
>    xaDs.setUser( "<user>" );
>    xaDs.setPassword( "<pwd>" );
>    xaDs.setDatabaseName( "<dbname>" );
>    xaDs.setServerName( "<host>" );
>
>    javax.sql.XAConnection xaCon = xaDs.getXAConnection();
>    for ( int i = 0; i < 999; i++ )
>    {
>       javax.transaction.xa.XAResource xaRs = xaCon.getXAResource();
>       // some xid implementation (org.jboss.tm.XidImpl)
>       javax.transaction.xa.Xid xid = new SomeXidImpl();
>       xaRs.start( xid, javax.transaction.xa.XAResource.TMNOFLAGS );
>
>       Connection conn = xaDs.getXAConnection().getConnection();
>       Statement stmnt = conn.createStatement();
>       stmnt.executeUpdate( "INSERT INTO some_table (some_value) " +
>          "VALUES (" + i + ");" );
>       conn.close();
>
>       xaRs.end( xid, javax.transaction.xa.XAResource.TMSUCCESS );
>       xaRs.commit( xid, true );
>
>       // if sleep time is removed we get exception after some iterations
>       Thread.currentThread().sleep( 1000 );
>    }
>
> --- end code snippet ---
>
> Removing the sleep will result in following exception after some
> iterations:
>
> --- begin stack trace ---
>
> Exception in thread "main" Something unusual has occured to cause the
> driver to fail. Please report this exception:
> Exception: java.sql.SQLException: FATAL 1:  Sorry, too many clients
> already
>
> Stack Trace:
>
> java.sql.SQLException: FATAL 1:  Sorry, too many clients already
>
>  at org.postgresql.Connection.openConnection(Connection.java:274)
>  at org.postgresql.Driver.connect(Driver.java:149)
>  at
> org.postgresql.PostgresqlDataSource.getConnection(PostgresqlDataSource.java:266)
>  at
> org.postgresql.PostgresqlDataSource.getConnection(PostgresqlDataSource.java:203)
>  at
> org.postgresql.xa.XADataSourceImpl.newConnection(XADataSourceImpl.java:293)
>  at
> org.postgresql.xa.XAConnectionImpl.getUnderlying(XAConnectionImpl.java:946)
>  at
> org.postgresql.xa.ClientConnection.getUnderlying(ClientConnection.java:554)
>  at
> org.postgresql.xa.ClientConnection.createStatement(ClientConnection.java:121)
>  at Test.main(Test.java:147)
> End of Stack Trace
>
>  at org.postgresql.Driver.connect(Driver.java:166)
>  at
> org.postgresql.PostgresqlDataSource.getConnection(PostgresqlDataSource.java:266)
>  at
> org.postgresql.PostgresqlDataSource.getConnection(PostgresqlDataSource.java:203)
>  at
> org.postgresql.xa.XADataSourceImpl.newConnection(XADataSourceImpl.java:293)
>  at
> org.postgresql.xa.XAConnectionImpl.getUnderlying(XAConnectionImpl.java:946)
>  at
> org.postgresql.xa.ClientConnection.getUnderlying(ClientConnection.java:554)
>  at
> org.postgresql.xa.ClientConnection.createStatement(ClientConnection.java:121)
>  at Test.main(Test.java:147)
>
> --- end stack trace ---
>
> The PostgreSQL console prints a lot of "DEBUG:  pq_recvbuf: unexpected
> EOF on client connection" and one "FATAL 1:  Sorry, too many clients
> already" causing the exception on our side.
>
> It works with a delay after each completed transaction, but we cannot
> have such a delay since it will decrease performance too much. Any ideas
> why a delay is needed? Almost as if the db server or the java stuff
> needs some time to make sure the connection is closed/free and there is
> no need to create a new one.
>
> Using java.sql.DriverManager getting a connection works fine but we need
> the J2EE stuff.
>
> Best regards,
>
> Johan Svensson [johan.svensson@windh.net]
> Kernel Developer, .windh AB
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>