No automatic reconnect after network error
Markus Riehl
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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Markus Schaber
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
Oliver Jowett
> 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?