Deadlock detection

As we know from recent versions, the JDBC driver can deadlock. We also
know that the deadlock detector as exists does not detect all types of
deadlock and isn't foolproof at that either. Not criticism, just
summarising.

We've recently tried to diagnose some sporadic hang situations and would
love to be able to rule out the JDBC driver. This has not yet been
possible. (Substantial work has already been done server side, so this
is not finger pointing, just working backwards). Currently we can take
stack traces, but knowing when to take them is key and since in problem
situations the priority is often on cure rather than prevention it has
not so far been possible to acquire useful diagnostic information.

It is possible that the cases under investigation are linked to the
occasional reports we get on various lists about idle-in-transaction. On
server side, I'm expecting to implement SIGINT to cancel
idle-in-transaction sessions. This may just further bury any problems in
the driver.

The best solution would be to have a logging mode that would advise us
of the presence of a deadlock and log details for later analysis. If
this worked in a objective way that could be sure of detecting the
general case we would be in a much better situation. We may discover
some unknown cases, though if not we can at least rule out the driver
from these situations and we know how important that can be in complex
failure analysis.

With me so far? Perhaps, perhaps not.

I'm thinking of some simple bookkeeping, so that before any session
issues a blockable call it takes note that it is doing so in a shared
array, and clears that state again once released. A single watcher
thread can then regularly scan the state array and see if a session is
marked as waiting on both send and receive. Side threads are already
used for login timeout handling, so that mechanism is already in use.
Some loss of performance would be acceptable.

Would you agree this would be worthwhile? Any practical blockers? If it
is unhelpful in this specific case, would that feature be generally
usable?

If not, other suggestions and viewpoints welcome. Thanks,

You might say the best way is to fix the underlying architecture. May
be, but we are a long way from that and even if we did, some second and
third order effects would still exist for which we need new log modes to
diagnose correctly.

Is there a deadline for the JDBC driver for 8.4? (When was it?)

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support