Re: This connection has been closed

I am using a cdo server (org.eclipse.emf.cdo) which uses hibernate which
uses the postgresql jdbc driver. (9.0.0.801)
Between two transactions the tcp connection between the cdo server and
postgresql went down (my laptop hibernated).
I got an exception with this cause:

Caused by: org.postgresql.util.PSQLException: This connection has been
closed.
at
org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:714)
at
org.postgresql.jdbc2.AbstractJdbc2Connection.getAutoCommit(AbstractJdbc2Connection.java:680)
at
org.hibernate.connection.DriverManagerConnectionProvider.getConnection(DriverManagerConnectionProvider.java:127)
at
org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)

This might be related to this issue:
https://forum.hibernate.org/viewtopic.php?f=1&t=957369

Do we have a hibernate, a postgresql or a CDO issue here?

On 12/05/11 10:10 PM, Magosányi Árpád wrote:
> I am using a cdo server (org.eclipse.emf.cdo) which uses hibernate
> which uses the postgresql jdbc driver. (9.0.0.801)
> Between two transactions the tcp connection between the cdo server and
> postgresql went down (my laptop hibernated).
> I got an exception with this cause:
>
> Caused by: org.postgresql.util.PSQLException: This connection has been
> closed.
> at
> org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:714)
> at
> org.postgresql.jdbc2.AbstractJdbc2Connection.getAutoCommit(AbstractJdbc2Connection.java:680)
> at
> org.hibernate.connection.DriverManagerConnectionProvider.getConnection(DriverManagerConnectionProvider.java:127)
> at
> org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
>
> This might be related to this issue:
> https://forum.hibernate.org/viewtopic.php?f=1&t=957369
>
> Do we have a hibernate, a postgresql or a CDO issue here?

offhand, I'd say you need to catch that exception, reinitialize the
connection, and then recover.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

On 6 December 2011 19:10, Magosányi Árpád <mag(at)magwas(dot)rulez(dot)org> wrote:
> I am using a cdo server (org.eclipse.emf.cdo) which uses hibernate which
> uses the postgresql jdbc driver. (9.0.0.801)
> Between two transactions the tcp connection between the cdo server and
> postgresql went down (my laptop hibernated).
> I got an exception with this cause:
>
> Caused by: org.postgresql.util.PSQLException: This connection has been
> closed.
>    at
> org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:714)
>    at
> org.postgresql.jdbc2.AbstractJdbc2Connection.getAutoCommit(AbstractJdbc2Connection.java:680)
>    at
> org.hibernate.connection.DriverManagerConnectionProvider.getConnection(DriverManagerConnectionProvider.java:127)
>    at
> org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
>
> This might be related to this issue:
> https://forum.hibernate.org/viewtopic.php?f=1&t=957369
>
> Do we have a hibernate, a postgresql or a CDO issue here?

In general you'll only get that exception in two cases:

1) You call Connection.close(); then you try to use the connection again
2) You try to do something on a Connection that requires I/O, there
is an I/O error (e.g. TCP connection failure), the driver throws a
suitable SQLException and implicitly closes the connection; then you
try to use the connection again.

I would guess that it is case (2) happening here. Presumably something
isn't treating the first exception as fatal, and then you get the
"connection is closed" exception when you later try to reuse the
connection.

You can detect this by looking for an 08-class SQLState error code in
the thrown exception; or if you're using the driver's
ConnectionPoolDataSource implementation, it'll fire a
connectionErrorOccurred callback when the driver decides the
connection is dead.

(Ideally we'd implement the exception subclasses present in 1.6 i.e..
SQLNonTransientConnectionException, but backwards compatibility is a
bit messy there)

Oliver

>
> On 6 December 2011 19:10, Magosányi Árpád <mag(at)magwas(dot)rulez(dot)org> wrote:
> > I am using a cdo server (org.eclipse.emf.cdo) which uses hibernate which
> > uses the postgresql jdbc driver. (9.0.0.801)
> > Between two transactions the tcp connection between the cdo server and
> > postgresql went down (my laptop hibernated).
> > I got an exception with this cause:
> >
> > Caused by: org.postgresql.util.PSQLException: This connection has been
> > closed.
> > at
> >
> org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:714)
> > at
> >
> org.postgresql.jdbc2.AbstractJdbc2Connection.getAutoCommit(AbstractJdbc2Connection.java:680)
> > at
> >
> org.hibernate.connection.DriverManagerConnectionProvider.getConnection(DriverManagerConnectionProvider.java:127)
> > at
> >
> org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
> >
> > This might be related to this issue:
> > https://forum.hibernate.org/viewtopic.php?f=1&t=957369
> >
> > Do we have a hibernate, a postgresql or a CDO issue here?
>
>
If you're using a 3rd party connection pool, I don't believe there is a
contract that they will necessarily validate that a connection is still
open before returning it. Most pools can be configured to issue a 'select
1' type of query on a periodic basis to keep connections from timing out,
or just to validate that a connection is still alive before returning it
from the pool.

On 06/12/11 15:55, Samuel Gendler wrote:
>
> If you're using a 3rd party connection pool, I don't believe there is
> a contract that they will necessarily validate that a connection is
> still open before returning it. Most pools can be configured to issue
> a 'select 1' type of query on a periodic basis to keep connections
> from timing out, or just to validate that a connection is still alive
> before returning it from the pool.
>

Relying on that alone is madness, because there's an inevitable gap
between the `SELECT 1;' succeeding and the connection being passed to
the app, and that's time the connection can become invalid during. It's
a race, bascially. Because that race can't be eliminated the app still
has to be coded to be fault-tolerant and cope with failed transactions.

Validating connections is still useful if your app's idea of
fault-tolerance is telling the user "something broke, please try again".
You want to minimise how often that happens, so you try to make sure
your pool notices a database restart promptly and flushes its connections.

If your app has better fault tolerance than that, where it remembers
what it tried to do and does it again if a transaction fails the first
time, then there may be no point validating connections at all.

--
Craig Ringer