Re: Connection terminated by the server causes deadlock in jdbc client side connection

Donald,

These are good points all. Let me provide more context for my scenario on
each item
> First I'd like to say that even though we are dealing with TCP one must
> treat it almost like UDP. TCP
> packets can be consumed by firewalls and proxy servers with no response
> given to the sender. So with
> that in mind to create a robust fault tolerant driver, timeout on all TCP
> communications is a must.

True in the general case, but I don't think it would have affected the
specific scenario under issue. For context on the scenario, note that there
was no firewall or proxy. We were using local connections
(jdbc:postgresql://localhost...)

> 1) So in the first case where an IOException is thrown, the original code
> that is closing the connection
> via "close" cannot have a timeout feature enabled and that will still
> exist in the code with the potential
> to fail...

As the 'graceful' close() method will write on the socket, would it be
possible to put a timeout on write? I am no expert but have not been able to
find out how.


> 2) When an IOException is thrown as the origin of an exception this should
> not be lost and should
> always be attached to the final exception. I don't see this cascaded in
> the updated driver using the
> "abort" code...

At least in some of the cases it appears that the original IO error is
consumed and converted to an error SQL state, and not re-thrown.
In other cases there is actually a message from the server advising of the
termination and this is somehow interpreted by the client, thus no IO
exception in those cases.

Here is a more detailed log capture (including some Hibernate entries) -
again, this is after the fix:

(SqlExceptionHelper.java:145) - SQL Error: 0, SQLState: 40001
(SqlExceptionHelper.java:147) - FATAL: terminating connection due to
conflict with recovery
  Detail: User query might have needed to see row versions that must be
removed.
  Hint: In a moment you should be able to reconnect to the database and
repeat your command.
org.hibernate.exception.LockAcquisitionException: could not extract
ResultSet
        at
org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:140)
        at
org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
        at
org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
        at
org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
        at
org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:61)
        at org.hibernate.loader.Loader.getResultSet(Loader.java:2040)
        at
org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1837)
        at
org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1816)
        at org.hibernate.loader.Loader.doQuery(Loader.java:900)
        at
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:342)
        at org.hibernate.loader.Loader.doList(Loader.java:2526)
        at org.hibernate.loader.Loader.doList(Loader.java:2512)
        at
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2342)
        at org.hibernate.loader.Loader.list(Loader.java:2337)
        at
org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:124)
        at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1662)
        at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:374)
...
Caused by: org.postgresql.util.PSQLException: FATAL: terminating connection
due to conflict with recovery
  Detail: User query might have needed to see row versions that must be
removed.
  Hint: In a moment you should be able to reconnect to the database and
repeat your command.
        at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)
        at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927)
        at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:562)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:305)
        ... 46 more

Let me know if there is any additional detail that I could provide.

Regards,

Leonardo




--
View this message in context:
http://postgresql.nabble.com/Connection-terminated-by-the-server-causes-deadlock-in-jdbc-client-side-connection-tp5869131p5870008.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.