Обсуждение: keeping Connection alive

Hi,

I'm using a PostgreSQL as backend for a Java application, which may idle for quite a while (i.e. hours) and now and
thenissue a query on the backend. 

The problem is the following exception I get when connection is apparently broken:

-- snip --
org.postgresql.util.PSQLException: An I/O error occured while sending to the backend.
-- snip --

What is best practice to prevent the connection from breaking, i.e. keeping the Conection alive?

I'm using an instance of PGPoolingDataSource and would expect to retrieve a _valid_ Connection when calling
DataSource.getConnection()(which I don't get). I even did call PGPoolingDataSource.setTcpKeepAlive(true) - same result. 

My first approach was to use a scheduled task and regularly check the Connection for validity ("ping") - unfortunately
Connection.isValid(inttimeout) has not been implemented yet. 

My current workaround is a scheduled task issueing a "SELECT 1" on a regularly basis. Which I feel is not the best
solution,though. 

I read of using a connection pool like dbcp, which I'd like to avoid: I don't need connection pooling at all, just need
tohave a valid Connection even after hours of no-operation (a reconnect is ok, so no performance issue here). 

So, what's the best practice using the postgresql jdbc driver?

I'm using PostgreSQL 8.3.8 and postgresql-8.4-701.jdbc4.jar for JDBC.

Thanks alot!

Best regards,
Andreas

On Dec 12, 2009, at 6:43 PM, Andreas Brandl wrote:
> I'm using a PostgreSQL as backend for a Java application, which may idle for quite a while (i.e. hours) and now and
thenissue a query on the backend. 

Is this a standard Java application or is it running in a Java EE or servlet container?

> -- snip --
> org.postgresql.util.PSQLException: An I/O error occured while sending to the backend.
> -- snip --
>
> What is best practice to prevent the connection from breaking, i.e. keeping the Conection alive?
>
> I'm using an instance of PGPoolingDataSource and would expect to retrieve a _valid_ Connection when calling
DataSource.getConnection()(which I don't get). I even did call PGPoolingDataSource.setTcpKeepAlive(true) - same result. 

If you're not using an application server, and/or don't need pooling, perhaps it would be better to use the regular
jdbcapi something like this: 
Class.forName("org.postgresql.Driver");
Connection conn = DriverManager.getConnection(jdbcConnUrl, username, password);
// then conn.createStatement , etc.

Then you can call conn.close() and create a new connection when necessary.

> My first approach was to use a scheduled task and regularly check the Connection for validity ("ping") -
unfortunatelyConnection.isValid(int timeout) has not been implemented yet. 
> My current workaround is a scheduled task issueing a "SELECT 1" on a regularly basis. Which I feel is not the best
solution,though. 
>
> I read of using a connection pool like dbcp, which I'd like to avoid: I don't need connection pooling at all, just
needto have a valid Connection even after hours of no-operation (a reconnect is ok, so no performance issue here). 

I've only had issues with pooled connections when I've misconfigured a setting in the app server (it should manage the
connectionsand automatically close and reconnect every so often) or when I've had an application bug that was not
releasingconnections because of unintended long running transactions. 

Hope that helps.

-Jovan Jester


> I'm using PostgreSQL 8.3.8 and postgresql-8.4-701.jdbc4.jar for JDBC.
>
> Thanks alot!
>
> Best regards,
> Andreas
>

Hi Jovan,

thanks for your reply!

> On Dec 12, 2009, at 6:43 PM, Andreas Brandl wrote:
> > I'm using a PostgreSQL as backend for a Java application, which may
> idle for quite a while (i.e. hours) and now and then issue a query on
> the backend.
>
> Is this a standard Java application or is it running in a Java EE or
> servlet container?

This is a standard Java application.

> If you're not using an application server, and/or don't need pooling,
> perhaps it would be better to use the regular jdbc api something like
> this:
> Class.forName("org.postgresql.Driver");
> Connection conn = DriverManager.getConnection(jdbcConnUrl, username,
> password);
> // then conn.createStatement , etc.
>
> Then you can call conn.close() and create a new connection when
> necessary.

I did work with a pure Connection before, until I realized things go wrong when application is idle for long time.

In either case, the problem is how to detect whether a Connection is valid or not. As far as I know there is no native
mechanismfor checking the state. So one approach may be to implement a DataSource which does check (i.e. "SELECT 1")
validitybefore returning the Connection (and reconnect if its not valid, i.e. throwing a SQLException?). 

Is there any better way to achieve this? After all this might mean a lot of overhead (checking every time
DataSource.getConnection()is called)... 

Thanks,
Andreas

On 13/12/2009 9:37 AM, Andreas wrote:
> Hi Jovan,
>
> thanks for your reply!
>
>> On Dec 12, 2009, at 6:43 PM, Andreas Brandl wrote:
>>> I'm using a PostgreSQL as backend for a Java application, which may
>> idle for quite a while (i.e. hours) and now and then issue a query on
>> the backend.
>>
>> Is this a standard Java application or is it running in a Java EE or
>> servlet container?
>
> This is a standard Java application.

Is its connection to the server diect via a local Ethernet segment? Or
is it going through routers - possibly routers doing NAT, such as most
home user modem/routers?

NAT is the most common cause of connection drop-outs. You can use TCP
keepalives to address this. See the archives and documentation.

(Hmm, we need a FAQ entry for this - added to my todo)

> I did work with a pure Connection before, until I realized things go wrong when application is idle for long time.
>
> In either case, the problem is how to detect whether a Connection is valid or not.

Don't. Even if the connection is ok when you test it, it might not be
when you then try to use it. Instead of trying to ensure that the
connection is perfect, make your application tolerant of failure. Batch
things into transactions, catch SQLExceptions, and re-try operations
that fail.

After all, database operations may fail due to serialization errors (in
SERIALIZABLE mode), locking deadlocks if you make mistakes with lock
ordering, etc. While you want to try to avoid these things, you should
also handle them in the app if they come up.

If you try to ensure that your connection is ok and then rely on it
staying OK, you *will* have problems. A user might lose wifi/cellular
coverage, a network might drop out, etc. You're setting yourself up for
a race condition you will inevitably lose some of the time.

> As far as I know there is no native mechanism for checking the state.

Trying something and catching the SQLException if it fails. That
reflects the design of the JDBC interface and of transactional databases
in general that you should try to do something and cope if it fails,
rather than ensuring it cannot fail.

 > So one approach may be to implement a DataSource which does check
(i.e. "SELECT 1") validity before returning the Connection (and
reconnect if its not valid, i.e. throwing a SQLException?).

Nononono....

Have the DataSource _consumer_ deal with it.


int retries = MAX_RETRIES;
do {
   try {
     try {
       Connection conn = myprovider.getConnection();
       // do work
       break;
     } finally {
       try {
         stmt.close();
       } catch (SQLException e) {
         // log to complain about statement close failure
       }
     }
   } catch (SQLException e) {
     myProvider.invalidateConnection(conn, e);
     retries--;
   }
} while (retries > 0);


... where "invalidateConnection(Connection, Throwable)" tells the
provider/pool that the connection is broken.

If you're using Connection directly, you'll just close() one you think
is broken and set the connection var to null so you know you need to
create a new one next time around.

In practice, you'll also generally test e.getSQLSTate() to see if the
exception might be a transient once tha doesn't imply the connection is
broken, and re-try with the same connection if it is. There's no point
dropping and re-creating the connection if it's a deadlock,
serialization failure, or the like after all.

Here's some code I use to check for SQLStates that may mean an operation
is worth retrying:

    private static final List<String> transientStates = Arrays.asList(
             "08",   // Connection exceptions - refused, broken, etc
             "53",   // Insufficient resources - disk full, etc
             "57P0", // Db server shutdown/restart
             "40001",// Serialization failure
             "40P01"// Deadlock detected
             );

    /**
      * Attempt to figure out whether a given database error could be
      * transient
      * and might be worth a retry. Detects things like network timeouts,
      * transaction deadlocks, serialization failures, connection drops,
      * etc.
      *
      * @param e Exception thrown by persistence provider
      * @return True if the error might be transient
      */
     public static boolean isDbErrorTransient(Throwable e) {
         final SQLException se = getSQLException(e);
         if (se == null)
             return false;
         final String sqlState = se.getSQLState();
         for (String s : transientStates) {
             Logger.getLogger(TheNameClass.class).warn(
                "Got possibly transient SQL error " + sqlState, e);
             if (sqlState.startsWith(s))
                 return true;
         }
         return false;
     }


--
Craig Ringer

On 13/12/2009 10:52 AM, Craig Ringer wrote:

> int retries = MAX_RETRIES;
> do {
> try {
> try {
> Connection conn = myprovider.getConnection();
 > Statement stmt = conn.createStatement();
> // do work
> break;
> } finally {
> try {
> stmt.close();
> } catch (SQLException e) {
> // log to complain about statement close failure
> }
> }
> } catch (SQLException e) {
> myProvider.invalidateConnection(conn, e);
> retries--;
> }
> } while (retries > 0);

Oh, also: if that seems ghastly and rather undesirable to repeat all
over the place - good.

You should probably wrap your database work up in an implementation of
an interface that you can pass to a database work executor. Your
interface has a single method that does the actual work with the
connection, plus hooks called for success, failure, etc.

You pass a (probably anonymous inner class) instance of the interface to
a database worker that's responsible for the ugly error handling and
connection management, and let it take care of giving it a connection
and running it. The database worker can take care of the nasty bits,
leaving the rest of your code free to just handle real failures.

You don't have to bother to catch SQLExceptions thrown within your work
code, since it should all be running in a single transaction so it'll
get automatically rolled back when the exception is thrown. Instead, you
can let the exception bubble up for the database worker to handle. It'll
decide whether to retry, to give up and call your failure hook, etc.

As a bonus, if you specify that the on success and on failure methods
must be run on the EDT but require the database work code to be safe to
run on any thread, you can trivially move your database work off the EDT
and into a background worker thread.

--
Craig Ringer

Hi Craig,

thanks a lot for your detailed answer! I learned a lot and hopefully are going to do a better design next time. :)

> Is its connection to the server diect via a local Ethernet segment? Or
> is it going through routers - possibly routers doing NAT, such as most
> home user modem/routers?

Neither local nor NAT/home routers.

>  > So one approach may be to implement a DataSource which does check
> (i.e. "SELECT 1") validity before returning the Connection (and
> reconnect if its not valid, i.e. throwing a SQLException?).
>
> Nononono....
>
> Have the DataSource _consumer_ deal with it.
>
>
> int retries = MAX_RETRIES;
> do {
>    try {
>      try {
>        Connection conn = myprovider.getConnection();
>        // do work
>        break;
>      } finally {
>        try {
>          stmt.close();
>        } catch (SQLException e) {
>          // log to complain about statement close failure
>        }
>      }
>    } catch (SQLException e) {
>      myProvider.invalidateConnection(conn, e);
>      retries--;
>    }
> } while (retries > 0);
>
>
> ... where "invalidateConnection(Connection, Throwable)" tells the
> provider/pool that the connection is broken.

I'm not sure how to do this, because AFAIK a DataSource implementation does not have a method for invalidating the
Connection.How to do this when implementing against standard interfaces? 

Thanks again for your detailed answer!

Best regards,
Andreas

On 14/12/2009 10:53 PM, Andreas wrote:

>> ... where "invalidateConnection(Connection, Throwable)" tells the
>> provider/pool that the connection is broken.
>
> I'm not sure how to do this, because AFAIK a DataSource implementation does not have a method for invalidating the
Connection.How to do this when implementing against standard interfaces? 

If you're using JNDI to get a DataSource and calling getConnection(...)
on it to obtain a JDBC connection, then there's no connection pooling
going on. You close your connectoins when you're done with them. So
there's no need to tell the DataSource the connection is broken; you
just close it and request a new one from the DataSource.

I don't currently use JNDI in my own app (though it looks like I
should), but I'm pretty sure that's how to handle it.

--
Craig Ringer

On 14/12/2009 10:53 PM, Andreas wrote:

> I'm not sure how to do this, because AFAIK a DataSource implementation does not have a method for invalidating the
Connection.How to do this when implementing against standard interfaces? 

OK, after a little more reading it's clear that if you're using the
basic javax.sql.DataSource there's no pooling and you just close dead
connections and open new ones.

If you're using javax.sql.ConnectionPoolDataSource, you'll need to
inform the connection pool that the connection is dead. How to do so
depends on the connection pool you're using, so you'd need to provide
more information. In general, it seems to end up with the connection
pool calling close() on the PooledConnection associated with the problem
connection.

See:
   http://java.sun.com/javase/6/docs/api/javax/sql/PooledConnection.html

--
Craig Ringer

Hi Craig, folks,

I'm coming back to this topic, because I now do have an implementation which is more robust in respect to connection
failuresand the like and which follows Craig's idea. 

> > As far as I know there is no native mechanism for checking the
> state.
>
> Trying something and catching the SQLException if it fails. That
> reflects the design of the JDBC interface and of transactional
> databases
> in general that you should try to do something and cope if it fails,
> rather than ensuring it cannot fail.
>
>  > So one approach may be to implement a DataSource which does check
> (i.e. "SELECT 1") validity before returning the Connection (and
> reconnect if its not valid, i.e. throwing a SQLException?).
>
> Nononono....
>
> Have the DataSource _consumer_ deal with it.
>
> [...]
>
> ... where "invalidateConnection(Connection, Throwable)" tells the
> provider/pool that the connection is broken.
>
> [...]
>
> In practice, you'll also generally test e.getSQLSTate() to see if the
> exception might be a transient once tha doesn't imply the connection
> is
> broken, and re-try with the same connection if it is. There's no point
> dropping and re-creating the connection if it's a deadlock,
> serialization failure, or the like after all.
>
> Here's some code I use to check for SQLStates that may mean an
> operation
> is worth retrying:
>
>     private static final List<String> transientStates =
> Arrays.asList(
>              "08",   // Connection exceptions - refused, broken, etc
>              "53",   // Insufficient resources - disk full, etc
>              "57P0", // Db server shutdown/restart
>              "40001",// Serialization failure
>              "40P01"// Deadlock detected
>              );
>

In the course of implementing I discovered, that in JDBC 4 there are subtypes of SQLException designed for
distinguishingtransient and non-transient failures. These are SQLTransientException, SQLNonTransientException and
SQLNonTransientConnectionException.Quite an old source is [1]. 

My understanding is that in general distinguishing by the type of SQLException is sufficient for this purpose. Though I
wonderif the postgresql-jdbc does implement this? 

Another question arising with implementing a more robust connection handling is the following. Suppose there is a
'WorkUnit'containing the concrete jdbc-code which gets executed in a single transaction which may get rolled back and
retriedsome more times. 

The execution of the 'WorkUnit' generates some Statement- and ResultSet-instances which have to be closed after
execution. 

My question is if it is advisable to do this closing of resources on a background worker thread. This would have the
advantageof returning faster after executing the relevant JDBC code because resources are closed on the separate
backgroundthread. 

I.e. in pseudo code:

void execute(WorkUnit unit) {

  do {
     unit.execute();
  } while (failed and retry-count is not reached};

  backgroundThread.close(unit); // a background thread will close the units ressources

}

Thanks a lot, I really appreciate this (and the other pgsql-*) mailinglist(s)!

Andreas

[1] http://www.artima.com/lejava/articles/jdbc_four3.html

On 21/12/2009 10:24 AM, Andreas Brandl wrote:

> In the course of implementing I discovered, that in JDBC 4 there are subtypes of SQLException designed for
distinguishingtransient and non-transient failures. These are SQLTransientException, SQLNonTransientException and
SQLNonTransientConnectionException.Quite an old source is [1]. 

Hmm, I'd never noticed the subtypes of SQLException.

> My understanding is that in general distinguishing by the type of SQLException is sufficient for this purpose. Though
Iwonder if the postgresql-jdbc does implement this? 

It does not appear to - at least, those exception names don't appear
anywhere in the sources. It only seems to throw PSQLException, which is
a subtype of SQLException.

The full heirarchy is here, by the way:

   http://java.sun.com/javase/5/docs/api/java/sql/SQLException.html


Unfortunately, the exception descriptions don't seem to be as clear as
they could be regarding what exactly "transient" means.

SQLRecoverableException describes something the app can recover from by
at minimum re-connecting and re-trying. That's clear enough.

SQLTransactionRollbackException would be a good one to be able to use
for deadlock-rollback. It's similarly clear.

SQLTransientException is for when "a previoulsy failed operation might
be able to succeed when the operation is retried without any
intervention by application-level functionality." This isn't very clear.
Does that mean "retrying *this* *statement* may work, you don't have to
re-try the whole transaction" ? That's how I read it. If it refers to
just the one JDBC operation as it seems to, it doesn't really apply to Pg.

In that case, is there even a suitable class for the most important case
in Pg - "if you re-try the whole transaction this will probably succeed,
but re-trying this particular statement is pointless. You don't have to
reconnect." ?



Anyway: To implement the use of SQLTransientException etc might be
interesting given that the Pg driver supports JDBC2, JDBC3 _and_ JDBC4
drivers in one source package. It'd need an exception factory that was
part of the JDBC-version-specific code, which took an exception type
param as well as the usual msg, sqlstate and cause. The factory for
JDBC2 / JDBC3 would return a normal PSQLException, and the JDBC4 factory
would override it to return a PSQLException subclass derived from one of
the JDBC SQLException subtypes (for JDBC4).

The JDBC4 factory wouldn't even get built when building the JDBC2/3
drivers, so the exceptions being missing from older JDKs wouldn't matter.



> Another question arising with implementing a more robust connection handling is the following. Suppose there is a
'WorkUnit'containing the concrete jdbc-code which gets executed in a single transaction which may get rolled back and
retriedsome more times. 
>
> The execution of the 'WorkUnit' generates some Statement- and ResultSet-instances which have to be closed after
execution.
>
> My question is if it is advisable to do this closing of resources on a background worker thread. This would have the
advantageof returning faster after executing the relevant JDBC code because resources are closed on the separate
backgroundthread. 

Closing the statement and associated result set shouldn't take long. I'd
be reluctant to add the additional complexity, myself. Also, while the
PgJDBC driver _is_ thread safe, this still seems like it's asking for
threading issues. I'm no JDBC expert, though.

--
Craig Ringer

On 21/12/2009 11:05 AM, Craig Ringer wrote:

> http://java.sun.com/javase/5/docs/api/java/sql/SQLException.html

Er, sorry:

   http://java.sun.com/javase/5/docs/api/java/sql/SQLException.html

--
Craig Ringer

Craig Ringer wrote:
> On 21/12/2009 11:05 AM, Craig Ringer wrote:
>
>> http://java.sun.com/javase/5/docs/api/java/sql/SQLException.html
>
> Er, sorry:
>
>   http://java.sun.com/javase/5/docs/api/java/sql/SQLException.html

same broken link, but this
http://java.sun.com/j2se/1.5.0/docs/api/java/sql/SQLException.html

seems to work for me.