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

David Wall wrote:

>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).
>
They will eventually... but the point is that your JDBC connection, that
caused the crash will receive a SQL Exception from the backend, not the
socket error.

> 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!).
>
You don't need to restart the app - if the backend crashes, it will
normally restart itself automatically, so if your connection pool is
smart enough to detect and restore connections that went bad, it will be
able to recover without having to restart the app.

>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.
>
It is not about the connection being useful that it'd get upset about.
It's about the connection suddenly getting closed on its own.
If the network error occurs, or backend crashes, or whetever else
happens when I execute a query, I expect to get an exception that tells
me what happened, if instead I would just get a message that the
connection is closed, I just fail to see how this makes it any more
useful... Useful for what???
You'll have the same exact situation (a useless connection, that needs
to be recreated) with just less diagnostics available to you...

>
>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.
>
It *does* let you know that your connection is broken - by throwing an
exception.

>  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's a shame they don't have error codes in postgres, I agree...
You do have to parse error messages if you want to detect and handle
particular kinds or errors - like duplicates, FK violations, deadlocks
etc...
It's been on the TODO list for a while to have error codes added to the
backend responses...

But I don't see what it has with the topic we started with - just to
test the connection validity, you don't need to parse any error
messages, and there is nothing database-specific about that logic either
- you just send a query that always succeedes, and, if you get an error,
your connection is bad.

>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.
>
You don't need to test the connection after every query, I hope :-)

Just when you return it to the pool...
If you want to be *really* conservative about it, you can only test it
after you get a failure from your 'normal' query...
This way, you'll need way lest than 20K of those 'dummies'.

>
>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.
>
Well... good luck. Here are some predictions:

First, you'll get bitten by it because you'll leave out some of the errors.
Then you'll add those, and will get bitten again, becuase you've left
out some more.
Then, you'll add those, and will be bitten again, because you've added
some extra ones this time.
You'll remove those, and will be bitten again because a the new database
version you upgrade to adds some more errors, you haven't seen before.
You'll add those, and will be bitten again, because the next version
changes the format of error reporting completely,  and you'll have to
rewrite the whole thing.

And, if, God forbid, you send your application to customers (as opposed
to using it as a hosted service), you'll be bitten once again, because
they will be in a different locale.

Eventually, you'll give up, and just do "select 1" to see if the
connection is valid :-)

Dima