Re: Problem with JTA/JTS - Mailing list pgsql-jdbc

Just some ideas:

It could be that Jboss is doing some sort of connection pooling, and
that the pool could be growing beyond the connection limit of the
database.  Try increasing the max number of connections the database
will support.  Also see if Jboss has a way to put an upper limit on any
connection pools.

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Johan Svensson
Sent: Thursday, June 13, 2002 2:31 PM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] Problem with JTA/JTS


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.j
ava:266)
 at
org.postgresql.PostgresqlDataSource.getConnection(PostgresqlDataSource.j
ava:203)
 at
org.postgresql.xa.XADataSourceImpl.newConnection(XADataSourceImpl.java:2
93)
 at
org.postgresql.xa.XAConnectionImpl.getUnderlying(XAConnectionImpl.java:9
46)
 at
org.postgresql.xa.ClientConnection.getUnderlying(ClientConnection.java:5
54)
 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.j
ava:266)
 at
org.postgresql.PostgresqlDataSource.getConnection(PostgresqlDataSource.j
ava:203)
 at
org.postgresql.xa.XADataSourceImpl.newConnection(XADataSourceImpl.java:2
93)
 at
org.postgresql.xa.XAConnectionImpl.getUnderlying(XAConnectionImpl.java:9
46)
 at
org.postgresql.xa.ClientConnection.getUnderlying(ClientConnection.java:5
54)
 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