Re: 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:

> 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