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

On Thu, 2002-06-13 at 22:32, Tom Lane wrote:
>
> It does take a finite (and not small) amount of time for a backend to
> clean up and exit after it detects client closure of the connection.
>
> It looks like you're managing to spawn connections fast enough that
> the not-yet-exited backends are filling all your available backend
> slots.
>
>
> If you are concerned about performance, why in the world are you
> starting a new connection for every transaction anyway?  Talk about
> self-inflicted damage ... set up a connection pool, man.
>

Seems like I made a mistake in the dummy code simulating the behavior.
As you said it looks like new connections are spawned faster than the
"not-yet-exited backends are filling in the available slots". However, I
couldn't understand why a new connection was created since I thought I
had pooling. Now I see something that may cause this behavior. The line:

   Connection conn = xaDs.getXAConnection().getConnection();

should be replaced with:

   Connection conn = xaCon.getConnection();

where xaCon is the "pooled connection". Doing that I still get error if
I don't have the sleep() call between transactions. The PostgreSQL
console still prints a lot of "DEBUG:  pq_recvbuf: unexpected EOF on
client connection" and one "FATAL 1:  Sorry, too many clients already".
The exception I get now looks like this:

javax.transaction.xa.XAException
 at org.postgresql.xa.XAConnectionImpl.start(XAConnectionImpl.java:476)
 at Test.main(Test.java:144)

To me the following code snippet is the same as the previous one minus
J2EE stuff.

--- begin code snippet ---

   // this is the pooled connection
   Connection conn = DriverManager.getConnection(
      "jdbc:postgresql:<db>", "<user>", "<pwd>" );
   for ( int i = 0; i < 999; i++ )
   {
      // begin transaction
      conn.setAutoCommit( false );

      Statement stmnt = conn.createStatement();
      stmnt.executeUpdate( "INSERT INTO some_table (some_value) " +
         "VALUES (" + i + ");" );

      conn.commit();
      // end transaction
   }

--- end code snippet ---

That code works fine but using XAConnection won't work, why?
XAConnection is the only thing I can pool, there is no way for me to
manage the underlying java.sql.Connection when using/implementing JTA.
It is up to the XAConnection implementation to manage the real
connection(s) and I may or may not benefit from pooling the XAConnection
depending on the implementation. An enlisted resource should be able to
reuse the connections that participated in the transaction from the
moment the transaction is completed, or?

Basically this comes down to three questions:

 1. Is the org.postgresql.xa.XAConnectionImpl pooling "real"
    connections?
 2. Will I benefit from pooling XAConnections?
 3. May the underlying connection be reused as soon as it is free?

Another thing that crossed my mind is the message "DEBUG:  pq_recvbuf:
unexpected EOF on client connection" that is printed some time after the
connection is closed. Couldn't that indicate that the connection isn't
closed properly forcing us to create a new connection instead of reusing
the existing one? Hope this clarified my problem some.

regards,

Johan Svensson [johan.svensson@windh.net]
Kernel Developer, .windh AB