Re: Detecting 'socket errors' - closing the Connection object - Mailing list pgsql-jdbc

> But why do you only care about socket errors? What is so special about
them?
> What if you execute a query, and it crashes the backend? You'll get a
> SQLException from the backend (not a socket error), but the connection
> will still be invalid.

I care about socket errors because that's the type of error I'm
encountering.  It appears that when the SQL client (my code) is separated
from the database through a less than ideal network, network I/O exceptions
occur, causing the JDBC connections to become useless.  It appears that such
connection resets can occur because of the way the TCP/IP stacks are
configured (keep-alives, etc.), because firewalls may reset long standing
connections, or just because of network congestion or other errors that
cause an application to believe that the TCP connection is broken, and
therefore the underlying socket is of no further use.

If the backend crashes, the sockets would become invalid as they are tied to
file descriptors within the backend (unless the backend is a simple thread
within another process).  Also, if the database crashes, I think it's okay
for me to say that you need to restart my application that relies on the
database, just as it's not hard to blame a system if the OS crashes (unless
it's the cause of the crash!).

> I am sure some application code would get *very* surprised if
> connections started to get closed "automagically" under it :-)
> You don't want to preceed every statement executing a query with (if
> (!connection.isClosed ()) check, do you?

I can't imagine any application being surprised since the JDBC connection is
entirely useless after such an error, unless the JDBC library itself
re-opens the underlying socket that is correctly hidden from users of the
JDBC library.  You cannot do ANYTHING with a connection that's broken; you
can't even explicitly abort the transaction as that request will also fail.

Besides, if you get an SQLException during a transaction, transactions are
automatically rolled back as a failure to execute transactional code within
the database ought to do so.  I think an I/O error that's underlying the
JDBC should either reopen it transparently (fine by me!) or close it or
otherwise let me know that my connection is broken.  As it is right now, my
"portable JDBC" is being littered with db-specific code as it has to query
text and error codes that vary with each database to determine if an update
causes an duplicate key or now even more when I interrogate an SQLException
to determine if an underlying I/O exception occurred (no two databases will
report this in a portable way).

> It *is* very different... With this piece of code, the only overhead is,
> pretty much, just the cost of sending about 15 bytes over the wire...
> that's pretty negligeable...
> You are still saving on the expensive stuff like establishing a new TCP
> connection, creating file descriptors, multiple network round trips for
> authentication, constructing, and starting a new backend process,
> setting up memory structures etc, etc, etc...

Agreed.  But, considering that I may do 20,000 queries a day and never get
an I/O exception error, it seems a bit much to add another 20,000 "dummy
queries" -- which would also be part of a database transaction, with it's
extra overhead -- just to catch the odd cases of a network reset on the
underlying socket.

At this point, I think I'd prefer to write extra code that analyzes the
non-portable SQLException error text/code (for those databases that provide
a code) on those rare occassions than to formulate a query, send it over TCP
to the backend, have the backend process this query under a transaction,
formulate the result, send it back over TCP, and have JDBC store it into a
ResultSet.  I suppose if such exceptions were typical, this would be less
"intuitively bothersome" to me.  At the same, adding extra code wherever an
SQLException is handled is prone to errors (typically of omission).  In the
end, it would be nice if a JDBC Connection object either took care of it all
for me, or made it portably clear that the connection is broken until
re-established, as opposed to be a duplicate key, constraint violation,
incorrectly formulated query, too big of a value for the data type, etc.
Most SQLExceptions are related to SQL issues, but those that are related to
the "hidden" socket connection or anything else that makes a connection
unusable going forward, it would be nice to know which type it really was so
we could take proper action.

David