Thread: 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.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
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
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
>
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
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
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
>
>
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
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
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) > >
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
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 > >
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
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 > > >
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 >
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 > >