Thread: Problem with JTA/JTS

Problem with JTA/JTS

From
Johan Svensson
Date:
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


Re: Problem with JTA/JTS

From
"David Hooker"
Date:
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



Re: Problem with JTA/JTS

From
Barry Lind
Date:
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
>



Re: Problem with JTA/JTS

From
Tom Lane
Date:
Johan Svensson <johan.svensson@windh.net> writes:
> 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?

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.

            regards, tom lane

Re: Problem with JTA/JTS

From
Johan Svensson
Date:
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


Re: Problem with JTA/JTS

From
Dave Cramer
Date:
Johan,

Can you send me your test code. I think there are some issues with the
XAConnection, I think it's time to fix them

Dave

On Fri, 2002-06-14 at 06:14, Johan Svensson wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>




Re: Problem with JTA/JTS

From
Johan Svensson
Date:
On Fri, 2002-06-14 at 12:49, Dave Cramer wrote:
> Johan,
>
> Can you send me your test code. I think there are some issues with the
> XAConnection, I think it's time to fix them
>
> Dave
>
> On Fri, 2002-06-14 at 06:14, Johan Svensson wrote:
> > On Thu, 2002-06-13 at 22:32, Tom Lane wrote:

Dave,

Sending some code that simulates JTA interaction with the data source
adapter according to JTA specification (I hope).

You need to create a table named "some_table" with the "int_value"
column of type INT and change the database, username, password etc. Add
the PostgreSQL jdbc jar to classpath and then it should run.

/Johan

Attachment

Re: Problem with JTA/JTS

From
Tom Lane
Date:
Johan Svensson <johan.svensson@windh.net> writes:
> 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 unexpected-EOF messages indicate the client side cutting the
connection (and not bothering to send a terminate message first).
I agree with Dave that this must be a problem inside the
pooled-connection code --- somehow it's closing rather than recycling
connections.

            regards, tom lane

Re: Problem with JTA/JTS

From
Dave Cramer
Date:
Tom, Johan,

Ok, the problem is that PostgresDataSource does NOT pool connections.

I will work on this, but it won't happen overnight.

So in the meantime if you can work around it....

Dave

On Fri, 2002-06-14 at 09:27, Tom Lane wrote:
> Johan Svensson <johan.svensson@windh.net> writes:
> > 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 unexpected-EOF messages indicate the client side cutting the
> connection (and not bothering to send a terminate message first).
> I agree with Dave that this must be a problem inside the
> pooled-connection code --- somehow it's closing rather than recycling
> connections.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>




Re: Problem with JTA/JTS

From
Tom Lane
Date:
Dave Cramer <Dave@micro-automation.net> writes:
> Ok, the problem is that PostgresDataSource does NOT pool connections.

Well, that answers that ...

On reflection though, the behavior Johan reports seems curious.  Is it
really possible that new connections could be launched faster than old
ones shut down?  The backend startup time is not trivial, and he is
executing a query on each connection too.  It's hard to believe that the
backend shutdown time exceeds startup + query time.

Can you think of any reason why the client-side connection closure might
not get signaled to the backend immediately?  If, say, there were some
kind of TCP timeout involved, then the report would make a whole lot
more sense.

            regards, tom lane

Re: Problem with JTA/JTS

From
Dave Cramer
Date:
Tom,

I'm embarassed to say this but looking at the code it appears that the
backend connection isn't really closed at all. I think putting in a
Pooled Connection will help this greatly.

So this probably fails after you get to maxConnections. Is that correct
Johan?

Dave
On Fri, 2002-06-14 at 10:46, Tom Lane wrote:
> Dave Cramer <Dave@micro-automation.net> writes:
> > Ok, the problem is that PostgresDataSource does NOT pool connections.
>
> Well, that answers that ...
>
> On reflection though, the behavior Johan reports seems curious.  Is it
> really possible that new connections could be launched faster than old
> ones shut down?  The backend startup time is not trivial, and he is
> executing a query on each connection too.  It's hard to believe that the
> backend shutdown time exceeds startup + query time.
>
> Can you think of any reason why the client-side connection closure might
> not get signaled to the backend immediately?  If, say, there were some
> kind of TCP timeout involved, then the report would make a whole lot
> more sense.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>



Re: Problem with JTA/JTS

From
Johan Svensson
Date:
On Fri, 2002-06-14 at 17:07, Dave Cramer wrote:
> Tom,
>
> I'm embarassed to say this but looking at the code it appears that the
> backend connection isn't really closed at all. I think putting in a
> Pooled Connection will help this greatly.
>
> So this probably fails after you get to maxConnections. Is that correct
> Johan?
>
> Dave

Seems to be the case. When maxConnections are set to 32 I get 32
iterations in my test before exception. When I changed maxConnections to
8 I got 8 iterations.

I run the Test and just as I get the exception the message "FATAL 1:
Sorry, too many clients already" is printed to console. About half a
second or so later I get <maxConnection> number of "DEBUG:  pq_recvbuf:
unexpected EOF on client connection" to console.

It looks like a new connection is created for every transaction and when
the transaction is complete the connection isn't released at once but
about 1 second later.

As you say, pooling connections would probably do the trick.

/Johan



Re: Problem with JTA/JTS

From
Dave Cramer
Date:
Johan,

Ok, I will try to incrementally patch this so that it at least pools
connections.

Dave
On Fri, 2002-06-14 at 12:44, Johan Svensson wrote:
> On Fri, 2002-06-14 at 17:07, Dave Cramer wrote:
> > Tom,
> >
> > I'm embarassed to say this but looking at the code it appears that the
> > backend connection isn't really closed at all. I think putting in a
> > Pooled Connection will help this greatly.
> >
> > So this probably fails after you get to maxConnections. Is that correct
> > Johan?
> >
> > Dave
>
> Seems to be the case. When maxConnections are set to 32 I get 32
> iterations in my test before exception. When I changed maxConnections to
> 8 I got 8 iterations.
>
> I run the Test and just as I get the exception the message "FATAL 1:
> Sorry, too many clients already" is printed to console. About half a
> second or so later I get <maxConnection> number of "DEBUG:  pq_recvbuf:
> unexpected EOF on client connection" to console.
>
> It looks like a new connection is created for every transaction and when
> the transaction is complete the connection isn't released at once but
> about 1 second later.
>
> As you say, pooling connections would probably do the trick.
>
> /Johan
>
>
>



Re: Problem with JTA/JTS

From
"Robert J. Sanford, Jr."
Date:
Is dropping in the pooling code from PoolMan
(http://sourceforge.net/projects/poolman/) something that would make sense
or are you going to implement your own?

rjsjr

> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer
> Sent: Friday, June 14, 2002 12:30 PM
> To: Johan Svensson
> Cc: Tom Lane; pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] Problem with JTA/JTS
>
>
> Johan,
>
> Ok, I will try to incrementally patch this so that it at least pools
> connections.
>
> Dave
> On Fri, 2002-06-14 at 12:44, Johan Svensson wrote:
> > On Fri, 2002-06-14 at 17:07, Dave Cramer wrote:
> > > Tom,
> > >
> > > I'm embarassed to say this but looking at the code it appears that the
> > > backend connection isn't really closed at all. I think putting in a
> > > Pooled Connection will help this greatly.
> > >
> > > So this probably fails after you get to maxConnections. Is
> that correct
> > > Johan?
> > >
> > > Dave
> >
> > Seems to be the case. When maxConnections are set to 32 I get 32
> > iterations in my test before exception. When I changed maxConnections to
> > 8 I got 8 iterations.
> >
> > I run the Test and just as I get the exception the message "FATAL 1:
> > Sorry, too many clients already" is printed to console. About half a
> > second or so later I get <maxConnection> number of "DEBUG:  pq_recvbuf:
> > unexpected EOF on client connection" to console.
> >
> > It looks like a new connection is created for every transaction and when
> > the transaction is complete the connection isn't released at once but
> > about 1 second later.
> >
> > As you say, pooling connections would probably do the trick.
> >
> > /Johan
> >
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>


Re: Problem with JTA/JTS

From
Dave Cramer
Date:
Robert,

DataSources pool slightly differently than most pools, one thing is that
the DataSource represents a specific database on a server, but can
return connections for different users. I'll have a look at it, but the
actual pooling isn't really the tough part.

dave
On Fri, 2002-06-14 at 14:56, Robert J. Sanford, Jr. wrote:
> Is dropping in the pooling code from PoolMan
> (http://sourceforge.net/projects/poolman/) something that would make sense
> or are you going to implement your own?
>
> rjsjr
>
> > -----Original Message-----
> > From: pgsql-jdbc-owner@postgresql.org
> > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer
> > Sent: Friday, June 14, 2002 12:30 PM
> > To: Johan Svensson
> > Cc: Tom Lane; pgsql-jdbc@postgresql.org
> > Subject: Re: [JDBC] Problem with JTA/JTS
> >
> >
> > Johan,
> >
> > Ok, I will try to incrementally patch this so that it at least pools
> > connections.
> >
> > Dave
> > On Fri, 2002-06-14 at 12:44, Johan Svensson wrote:
> > > On Fri, 2002-06-14 at 17:07, Dave Cramer wrote:
> > > > Tom,
> > > >
> > > > I'm embarassed to say this but looking at the code it appears that the
> > > > backend connection isn't really closed at all. I think putting in a
> > > > Pooled Connection will help this greatly.
> > > >
> > > > So this probably fails after you get to maxConnections. Is
> > that correct
> > > > Johan?
> > > >
> > > > Dave
> > >
> > > Seems to be the case. When maxConnections are set to 32 I get 32
> > > iterations in my test before exception. When I changed maxConnections to
> > > 8 I got 8 iterations.
> > >
> > > I run the Test and just as I get the exception the message "FATAL 1:
> > > Sorry, too many clients already" is printed to console. About half a
> > > second or so later I get <maxConnection> number of "DEBUG:  pq_recvbuf:
> > > unexpected EOF on client connection" to console.
> > >
> > > It looks like a new connection is created for every transaction and when
> > > the transaction is complete the connection isn't released at once but
> > > about 1 second later.
> > >
> > > As you say, pooling connections would probably do the trick.
> > >
> > > /Johan
> > >
> > >
> > >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>