Thread: No automatic reconnect after network error
Hi all,
JDBC Version: 8.1-405 (Type 3)
Postgre SQL Version: 8.1.3
OS: Windows XP
I have the PSQL database running on one server, my java application on
another server. The java application is a server application that opens a
database connection once at startup time.
The server application uses PreparedStatements (threshold=0) that are also
created at startup time.
After a network error (to test I removed the network cable from the server
where my java application is running for a few seconds), I get the following
error message when using on of the PreparedStatements:
org.postgresql.util.PSQLException: An I/O error occured while sending to the
backend.
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:214)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
ava:437)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2St
atement.java:353)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statem
ent.java:257)
...
Using MySQL or SAPDB, the JDBC driver is automatically reconnecting to the
database when the connection is up again. Is there a way to use the PSQL
driver the same way?
How is this problem handled within Connection Pools (used within a servlet
environment)? Is the pool deleting "dead" connections automatically and is
creating new ones?
Thanks in advance
Markus
Hi, Markus,
Markus Riehl wrote:
> Using MySQL or SAPDB, the JDBC driver is automatically reconnecting to the
> database when the connection is up again. Is there a way to use the PSQL
> driver the same way?
Currently, the backend cannot keep the current transaction if the
connection breaks, so it will be rolled back.
> How is this problem handled within Connection Pools (used within a servlet
> environment)? Is the pool deleting "dead" connections automatically and is
> creating new ones?
At least JBoss, you can use the following approach: In the Datasource
definition, put the following:
<datasources>
<no-tx-datasource>
<jndi-name>boo</jndi-name>
<connection-url>jdbc:postgresql://localhost:5432/bar</connection-url>
[ ... other attributes ... ]
<new-connection-sql>SELECT version()</new-connection-sql>
<check-valid-connection-sql>SELECT
version()</check-valid-connection-sql>
</no-tx-datasource>
</datasources>
This validates the connection before it is handed out on the
application, and throws away invalid connections. Be aware that the SQL
is case sensitive, we had a problem with earlier versions of OpenMDX/CRX
that had upper case letters in the tags so they were ignored by jboss.
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
Hi Markus, thanks for your answer. > Currently, the backend cannot keep the current transaction if the > connection breaks, so it will be rolled back. That's ok, I just want the JDBC driver to reconnect as soon as the connection is available again. > At least JBoss, you can use the following approach: In the Datasource > definition, put the following: > > <datasources> > <no-tx-datasource> > <jndi-name>boo</jndi-name> > <connection-url>jdbc:postgresql://localhost:5432/bar</connection-url> > [ ... other attributes ... ] > <new-connection-sql>SELECT version()</new-connection-sql> > <check-valid-connection-sql>SELECT > version()</check-valid-connection-sql> > </no-tx-datasource> > </datasources> > > This validates the connection before it is handed out on the > application, and throws away invalid connections. Be aware that the SQL > is case sensitive, we had a problem with earlier versions of OpenMDX/CRX > that had upper case letters in the tags so they were ignored by jboss. I'm using Resin, not JBoss. The above configuration means a lot of overhead, just to verify that a connection is still valid. Is this the recommended way in a production environment? I think this means that the PostgreSQL JDBC driver doesn't support automatic reconnection. And I don't see this feature on the Roadmap. Is it unusual to use PostgreSQL in a multi server environment? Without this feature I don't think PostgreSQL is the right choice for me and I keep using SAPDB. I could implement it in my server application, but within the servlet engine it is almost impossible to do outside the JDBC driver. Thanks, Markus
Markus Riehl wrote: > Hi Markus, > > thanks for your answer. > >> Currently, the backend cannot keep the current transaction if the >> connection breaks, so it will be rolled back. > > That's ok, I just want the JDBC driver to reconnect as soon as the > connection is available again. > >> At least JBoss, you can use the following approach: In the Datasource >> definition, put the following: >> >> <datasources> >> <no-tx-datasource> >> <jndi-name>boo</jndi-name> >> >> >> >> >> <connection-url>jdbc:postgresql://localhost:5432/bar</connection-url> >> [ ... other attributes ... ] <new-connection-sql>SELECT >> version()</new-connection-sql> <check-valid-connection-sql>SELECT >> version()</check-valid-connection-sql> </no-tx-datasource> >> </datasources> >> >> This validates the connection before it is handed out on the >> application, and throws away invalid connections. Be aware that the >> SQL is case sensitive, we had a problem with earlier versions of >> OpenMDX/CRX that had upper case letters in the tags so they were >> ignored by jboss. > > I'm using Resin, not JBoss. The above configuration means a lot of > overhead, just to verify that a connection is still valid. Is this > the recommended way in a production environment? > > I think this means that the PostgreSQL JDBC driver doesn't support > automatic reconnection. And I don't see this feature on the Roadmap. > Is it unusual to use PostgreSQL in a multi server environment? Most JDBC drivers that I'm aware of don't do automatic reconnections. We primarily use Oracle at work (sigh - I'm trying to change that - long, slow effort) and it does not automatically reconnect. I don't use Resin, but I just checked the Resin site here http://www.caucho.com/resin-3.0/db/config.xtp#DataSource and it has the same kind of connection validation that JBoss does. See the section titled "Reliability Configuration." With that, Resin's DBPool will get you a valid connection when they become available again. > > Without this feature I don't think PostgreSQL is the right choice for > me and I keep using SAPDB. I could implement it in my server > application, but within the servlet engine it is almost impossible to > do outside the JDBC driver. That does not appear to be the case. -- Guy Rouillier
Markus Riehl wrote: > I think this means that the PostgreSQL JDBC driver doesn't support automatic > reconnection. And I don't see this feature on the Roadmap. Is it unusual to > use PostgreSQL in a multi server environment? It's unusual to have JDBC drivers automatically reconnect. I could see an argument for it if you could guarantee that the reconnection was invisible to the user .. but that's not the case here. The JDBC spec's Connection lifecycle seems to be aimed at a 1:1 mapping to real physical connections, anyway. Once it's closed for whatever reason, a Connection stays closed. If you want to do more work, you need to get a new Connection. -O