Thread: Fix for receiving empty query errors.
Changed to receive the 4 bytes. Cheers, Kim
Attachment
I have a connection pool that opens several connections and passes them out to individual threads as needed. Normally this works just fine. However, if the postgresql database is restarted, or if the database is on another network segment, hiccups on the network will cause socket errors on the underlying JDBC connection. When a connection is returned to the pool, I'm calling the 'isClosed()' method to see if the underlying connection has been closed, but apparently when there is a socket error, the JDBC connection is not automatically being closed, despite the fact that such an error will always be unrecoverable. I've also seen the same problem occur on the Oracle JDBC drivers (oracle sql exception errors 17002 and 17008). Is there a reason why the JDBC implementations don't close the Connection object when they get an I/O error on the socket? Or am I doing something else wrong in trying to detect that the connection has failed and must be closed and reopened before being used again? Thanks, David
It is not that trivial for JDBC to be able to detect and handle all the
cases when the connection can be considered "closed" as opposed
legitimate error conditions...
Besides, if isClosed () returned true in such situations, it would be
lying :-)
You can use other means to figure out if the connection is still valid
when it is returned to the pool, instead of relying on isClosed ().
I do something like this in my connection pool:
try
{
c.createStatement ().execute ("select 1;");
idlingConnections.add (c);
}
catch (Exception e)
{
log ("OOPS: the connection seems dead: ", e);
try
{
c.close (); //Just in case...
}
catch (Exception e)
{
}
numOpenConnections--;
}
I hope it helps...
Dima.
David Wall wrote:
>I have a connection pool that opens several connections and passes them out
>to individual threads as needed. Normally this works just fine. However,
>if the postgresql database is restarted, or if the database is on another
>network segment, hiccups on the network will cause socket errors on the
>underlying JDBC connection.
>
>When a connection is returned to the pool, I'm calling the 'isClosed()'
>method to see if the underlying connection has been closed, but apparently
>when there is a socket error, the JDBC connection is not automatically being
>closed, despite the fact that such an error will always be unrecoverable.
>
>I've also seen the same problem occur on the Oracle JDBC drivers (oracle sql
>exception errors 17002 and 17008).
>
>Is there a reason why the JDBC implementations don't close the Connection
>object when they get an I/O error on the socket? Or am I doing something
>else wrong in trying to detect that the connection has failed and must be
>closed and reopened before being used again?
>
>Thanks,
>David
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
>
> It is not that trivial for JDBC to be able to detect and handle all the
> cases when the connection can be considered "closed" as opposed
> legitimate error conditions...
Why is that the case? I mean, if the library gets a socket error, rather
than error from the database, it's an I/O error that probably means a TCP
socket won't really be okay anymore, and it could then close it.
> Besides, if isClosed () returned true in such situations, it would be
> lying :-)
Well, not if it closed it after it got an error. I mean, I don't open the
socket either, but the JDBC library should be able to figure it out, no?
> try
> {
> c.createStatement ().execute ("select 1;");
> idlingConnections.add (c);
> }
> catch (Exception e)
> {
> log ("OOPS: the connection seems dead: ", e);
> try
> {
> c.close (); //Just in case...
> }
> catch (Exception e)
> {
> }
>
> numOpenConnections--;
> }
That will work, but doesn't adding an entire call to the backend database on
each use a connection from the pool seem excessive? I mean, if I'm going to
all that trouble, I might as well not pool the connection at all -- the
overhead can't be that much different.
David
David Wall wrote: >> It is not that trivial for JDBC to be able to detect and handle all the >>cases when the connection can be considered "closed" as opposed >>legitimate error conditions... >> >> > >Why is that the case? I mean, if the library gets a socket error, rather >than error from the database, it's an I/O error that probably means a TCP >socket won't really be okay anymore, and it could then close it. > > 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. >>Besides, if isClosed () returned true in such situations, it would be >>lying :-) >> >> > >Well, not if it closed it after it got an error. I mean, I don't open the >socket either, but the JDBC library should be able to figure it out, no? > > 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? >That will work, but doesn't adding an entire call to the backend database on >each use a connection from the pool seem excessive? I mean, if I'm going to >all that trouble, I might as well not pool the connection at all -- the >overhead can't be that much different. > > > 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... Dima
> 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
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
It was suggested that doing a simple "dummy" SQL query each time a JDBC
Connection is returned to the pool is a valid way to determine if a given
JDBC Connection object is still usable for subsequent SQL queries/updates.
I'd be interested in hearing other feedback on whether they think this is
suitable or not. Clearly, the advantage is that I can put this code inside
the pool code so there's only one place that needs to worry about it, and
it's the code that already worries about creating the connections, so it's
nice. But whether it has issues surrounding transactions might arise (would
the 'select 1;' query fail because it's not inside a transaction -- or do I
need to rollback the transaction that was involved in the SQLException
first?)
But, is this how most people's pools work? Or do most people simply suffer
restarts when such errors occur so that the connections are created fresh
again?
Clearly, if the dummy call occurs each time a connection is returned to the
pool, this would be wasteful, albeit Dmitry says it's probably very little
overhead.
My current take would be to use such a dummy call only when handling an
SQLException, so that I could pass along the connection object that may be
in trouble (most SQLExceptions won't imply the database socket is broken
unless your network is really in trouble) back to the pool and have the pool
figure out if the connection itself is dead or not. This would certainly
only use the overhead when an SQLException occurs, which is typically a low
percentage of SQL calls, but it does mean instrumenting the code further so
that whenever an unexpected SQLException occurs, a new bit of code has to be
executed.
I suppose another way would be to simply assume all SQLExceptions that are
not handled by the business logic (like working around a duplicate key
problem perhaps) will require that the Connection be re-opened. Assuming
that your code has been properly debugged so that invalid SQL won't occur,
this would only cause unnecessary re-opening of a Connection for duplicate
keys, contraint violations, etc.
Anyway, I'd love to hear other thoughts on this before committing to code
changes throughout all of the JDBC calls we do.
David
----- Original Message -----
From: "Dmitry Tkach" <dmitry@openratings.com>
To: "David Wall" <d.wall@computer.org>
Cc: "pgsql-jdbc-list" <pgsql-jdbc@postgresql.org>
Sent: Monday, July 21, 2003 10:52 AM
Subject: Re: [JDBC] Detecting 'socket errors' - closing the Connection
object
> You can use other means to figure out if the connection is still valid
> when it is returned to the pool, instead of relying on isClosed ().
> I do something like this in my connection pool:
>
> try
> {
> c.createStatement ().execute ("select 1;");
> idlingConnections.add (c);
> }
> catch (Exception e)
> {
> log ("OOPS: the connection seems dead: ", e);
> try
> {
> c.close (); //Just in case...
> }
> catch (Exception e)
> {
> }
>
> numOpenConnections--;
> }
>
>
> I hope it helps...
>
> Dima.
On Mon, Jul 21, 2003 at 10:38:42AM -0700, David Wall wrote: > I have a connection pool that opens several connections and passes them out > to individual threads as needed. Normally this works just fine. However, > if the postgresql database is restarted, or if the database is on another > network segment, hiccups on the network will cause socket errors on the > underlying JDBC connection. > > When a connection is returned to the pool, I'm calling the 'isClosed()' > method to see if the underlying connection has been closed, but apparently > when there is a socket error, the JDBC connection is not automatically being > closed, despite the fact that such an error will always be unrecoverable. Unfortunately the isClosed() javadoc is fairly explicit in saying you can't use it for this: Retrieves whether this Connection object has been closed. A connection is closed if the method close has been called on it or if certain fatal errors have occurred. This method is guaranteed to return true only when it is called after the method Connection.close has been called. This method generally cannot be called to determine whether a connection to a database is valid or invalid. A typical client can determine that a connection is invalid by catching any exceptions that might be thrown when an operation is attempted. There is javax.sql.ConnectionEventListener.connectionErrorOccurred(). However, in my experience the current postgresql driver never calls this (in fact, I'm yet to find a jdbc driver that does). My current implementation wraps a DataSource in a custom ConnectionPoolDataSource / PooledConnection pair that calls connectionErrorOccurred() when any SQLException is thrown. This is a bit gross, but works for our app which in general does not expect to see exceptions under normal operation. The final bit of the puzzle is that you usually need to *do* something on the connection before you will notice connection errors, as at least the postgres driver does not have a separate read thread running. So if you want timely detection of errors, you need to periodically "select 1;" or something similar on connections in your idle pool. -O
> > >Unfortunately the isClosed() javadoc is fairly explicit in saying you can't >use it for this: > > Retrieves whether this Connection object has been closed. A connection is > closed if the method close has been called on it or if certain fatal errors > have occurred. > Hmmm.... To me this phrase sounds like exactly what he was asking for... Perhaps, it would then make sense to make isClosed() return false if the socket goes south... I think that situation very well qualifies as a 'certain fatal error' after all... >This method is guaranteed to return true only when it is > called after the method Connection.close has been called. > > This is weird.... direct contradiction with the previous sentense isn't it? :-) Dima
On Tue, Jul 22, 2003 at 10:18:28AM -0400, Dmitry Tkach wrote: > > > > > >Unfortunately the isClosed() javadoc is fairly explicit in saying you can't > >use it for this: > > > > Retrieves whether this Connection object has been closed. A connection is > > closed if the method close has been called on it or if certain fatal > > errors > > have occurred. > > > Hmmm.... To me this phrase sounds like exactly what he was asking for... > Perhaps, it would then make sense to make isClosed() return false if the > socket goes south... > I think that situation very well qualifies as a 'certain fatal error' > after all... > > >This method is guaranteed to return true only when it is > > called after the method Connection.close has been called. > > > > > This is weird.... direct contradiction with the previous sentense isn't > it? :-) No, it's just saying you can't rely on detecting errors via isClosed(). You might see a spontaneous close after an error, but it's not *guaranteed* -- only close() is guaranteed to cause isClosed() to return true. The next paragraph, which you trimmed, clarifies that: This method generally cannot be called to determine whether a connection to a database is valid or invalid. A typical client can determine that a connection is invalid by catching any exceptions that might be thrown when an operation is attempted. -O
> > >No, it's just saying you can't rely on detecting errors via isClosed(). You >might see a spontaneous close after an error, but it's not *guaranteed* -- >only close() is guaranteed to cause isClosed() to return true. > >The next paragraph, which you trimmed, clarifies that: > Yes, that was the *next* paragraph abotu not relying on detecting errors. This one explicitly said that it is *guaranteed* that isClosed() will return true *only* after close() has been called. And the previous one was - if the close() has been called *or* certain fatal errors occured... Dima
> I still don't understand why do you believe it is Connection's job. > Why aren't you suggesting that the Socket should reopen itself if it > gets an error from the network? > Or why don't you expect the java VM to restart itself in case an app > exists on some kind of an unexpected error condition? :-) I don't think that's a fair comparison at all. For one thing, an I/O error on a socket is not an unexpected condition. The underlying socket using in a JDBC Connection object is an internal variable. The Connection opens it, closes it, etc. and never gives it out. In fact, there is probably nothing that requires that the Connection use a socket per se, if another communications channel would work. It's just that TCP is the common mechanism, but I'm sure shared memory, UNIX sockets, pipes, or a myriad of non-TCP protocols (sna, osi, sonet, token rings whatever) could be used just fine. The entire purpose of object orientation is to hide such details. Since I never closed the Connection object, and the JDBC library itself never closed it, and since the underlying communications channel is broken and will never recover itself, it would be fine for the Connection object to note that it's last request was a fatal I/O error, cause the SQLException to be thrown, reset it's file pointer to indicate no underlying socket is open, and on the next use of the connection, it could try to re-open it. Again, I'm okay with closing it myself if there were an obvious way to detect that a given SQLException was in fact related to an unrecoverable I/O error. It's just that simple. David > With Oracle, you can certainly look at the exception code to find out > the exact cause of the problem. True, but it's Oracle specific, which is rather non-JDBC like. Also, as you pointed out, I'd have to have a complete understanding of all of the possible error codes and which ones were related to unrecoverable connections that would require a new connection to be established. After all, a socket error is an obvious form, but perhaps there would be other internal JDBC errors or the like that would also mean a connection object must be recreated since the current object is effectively dead for all future queries. Then we'd have to test against each of those codes, and as new codes are added, I'd have to change my code again. This is not the job of the app programmer, it's the job of the JDBC programmer as the JDBC library owns the Connection object and only it can tell the app programmer if the Connection object is no longer functional. (Or I have to do a "never can fail" query and see if that fails or not, as you suggested). > It doesn't matter. If it is an I/O exception, it could be caused by a > bunch of reasons. The database can be down, the computer could have > crashed, the network could be inaccessible etc, etc... In all of those > cases it is pointless to try and restore the connection. You are just being argumentative now. Whether a connection can be established at all IS up to the app code. When I try to create a Connection object, if it fails, then I can do whatever I think is reasonable. For most applications, an inability to get a connection the database would be highly fatal as few db-related apps can do anything without the db available. But that's up to me. What happens now is that there is such an error, the socket could be re-established, but the Connection object and SQLException give no clear, common indication that the Connection is broken and must be re-established. After all, nobody throws out a Connection just because an SQLException occurred, which shows that most such exceptions are not fatal. > And the other way around - if you get a SQL exception, it doesn't mean > that the database is up, it could have crashed the next millisecond > after sending a response to you. For all intents and purposes it would > be the same situation as getting a SocketError to begin with. So, this would be true after your "cannot fail" query. You think you just put a valid Connection back in your pool because it passed it, but by your own statement here, you point out that this is not so. > The point is that the only way to find out if your connection is good > (meaning that the server is willing to talk to it) at this exact point > in time is to ask the server (by sending a query). Yes, and this is exactly what my code does. It sends a query, gets an SQLException, but then has no clear, common way to determine if that was because of a fatal problem with the Connection object, or whether it was just a typical SQL type of error. > Besides, even if it wasn't that hard, it would still be pretty pointless > - as I explained above - if the error you are getting is "soft" (or even > if you are not getting any error at all), it doesn't mean that your > connection is good be the time you are finished looking at that error. > It only means it *was* good at the moment that response was sent... but > what good does it do to you? Since when has a programmer attempted to detect errors before doing an operation? That's just silly now. I don't care about some future transaction; I care about the transaction I just did, and it failed. Without some knowledge about the type of failure, though, I will either think I can reuse the Connection object or not. If I reuse it and it was typical SQLException, it will likely work just fine. If it was an I/O exception, it will never work, no matter what I do. So, it's quite obvious that I'd like to know the difference since in the latter case I'll know that I should attempt to recreate the connection before I submit another query. > It is not "defensive" - if you acknowledge that network/database errors > are the reality of life, you have to keep them in mind when writing your > code. We do. That's why we catch SQLException on every db call. When I write socket code, I catch IOException, and with the amazing future insights, I am able to close such a socket and reopen it when the time comes. I'm just amazing that way; I don't just keep retrying reading a file after EOF, too. But I suppose in your mind such indications shouldn't be given to the programmer, and I should just keep on trying reading, and if the read fails, I could write extra hack code just to see if that IOException was real or not. The point is, the Connection does report the exception just fine. The Connection KNOWS there was an I/O Exception. The Connection KNOWS that the underlying socket is useless. It's just keeping that info to itself so my app cannot take remedial action when such an error occurs, instead of using your defensive programming method that assumes even a perfectly successful query should be checked just to make sure something bad hasn't happened since that successful query. That's absurd for 99.99999999% of all coding. > No, it was not a hack. Not at all. > It is indeed has a purpose to test the connection... > But you are not trying to say that any piece of code, that has a purpose > is a hack, are you? ;-) Nope, only a hack when it's a work around for a problem that we could easily know. If you add an extra query to the database just to see if a previous query was successful or not, that's a hack. The previous query's results should be all that's necessary to know if the query was okay or not. Additional queries only tell me if that query failed or not, and it's a hack because you are doing an SQL query, using your computer's TCP stack and OS code, using network resouces, using database resources, using extra memory, using extra processing and wall clock time. And yet if you did not get an SQLException, the test has no purpose whatsoever, and if you did get an SQLException, then it would be nice for that exception to have given me the info that you are submitting the dummy query to determine. > Because the output of the first query did not give you enough > information. You need additional information to make a decision. What's > wrong with it? Because for the most part, the Connection object KNOWS that information, it's just not letting the programmer know. David
David Wall wrote:
>
> Again, I'm okay with closing it myself if there were an obvious way to
> detect that a given SQLException was in fact related to an unrecoverable I/O
> error. It's just that simple.
>
When we get the SQLSTATE patch in you'll be able to do:
if ((e.getSQLState() != null) && (e.getSQLState().equals("08S01")))
throw new ExplainException("Communication link error: " + e.getMessage());
--
Fernando Nasser
Red Hat Canada Ltd. E-Mail: fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario M4P 2C9
> > > Retrieves whether this Connection object has been closed. A connection is > > > closed if the method close has been called on it or if certain fatal > > > errors have occurred. Precisely. This makes PERFECT sense to me. It's those fatal errors that should allow us to detect something has gone wrong and therefore should not be retried. If the JDBC Connection gets an I/O exception, it could easily close itself. I suspect I could add that code to the Postgresql JDBC library myself quite easily. The unfortunate situation, though, is that other JDBC libraries may or may not do it, so therefore it's an unreliable bit of code, and Dmitry's hack comes back into play as perhaps the only reliable solution with the current state of affairs among JDBC implementations. I know for sure that Oracle does not close itself. > No, it's just saying you can't rely on detecting errors via isClosed(). You > might see a spontaneous close after an error, but it's not *guaranteed* -- > only close() is guaranteed to cause isClosed() to return true. True, but it's easy for the Connection to call close() when it gets a fatal error, so I don't think the second paragraph really means you can't close it. The Connection object is surely allowed to call any and all of its methods as necessary. > The next paragraph, which you trimmed, clarifies that: > > This method generally cannot be called to determine whether a > connection to a database is valid or invalid. A typical client can determine > that a connection is invalid by catching any exceptions that might be thrown > when an operation is attempted. We certainly can tell that. I think this is simply saying, though, what Dmitry was implying earlier, and that's simply the fact that a "valid" Connection object can fail at any time, since a database can crash, a network can drop, whatever. Just because you are holding a Connection object, doesn't mean the underlying connection is valid. However, once the underlying connection IS FOUND to be invalid, it would be quite nice to mark it as such so that we can take the appropriate action. The last line says I can tell by catching the exception that might be thrown. Well, let me tell you, I catch the exception just fine. I just cannot tell if that means the underlying connection is fatally down or not. I don't know whether the Connection object I'm holding can ever be used again or not. This is obvious to anybody because I can catch SQLExceptions and reuse the Connection object just fine, but at other times the SQLException thrown means the Connection object is fatally broken and must be discarded for a new one. And there's no method in the SQLException to let me know which type it was, and the Connection object itself (which does know!) won't tell us! ACK! I think the Dmitry hack is my best option right now, though I will probably do it only after receiving an SQLException rather than just because a perfectly good Connection object is being returned to the pool (after all, in our code, success occurs hundreds of thousands of times before an exception is thrown). David
> When we get the SQLSTATE patch in you'll be able to do:
>
> if ((e.getSQLState() != null) && (e.getSQLState().equals("08S01")))
> throw new ExplainException("Communication link error: " + e.getMessage());
Thanks, Fernando. Unfortunately, this will be a postgresql-ism, not a JDBC
library-ism, and it will likely require that the user be on a particular
version of the database, too (or is this bit going to work even on 7.3 and
pre 7.3 versions too?). I'm looking for something that doesn't require a
special patch for each of the myriad databases our customers use (MSFT SQL
Server, Oracle, PostgreSQL MySQL and DB2/UDB to name the current list).
David
On Tue, Jul 22, 2003 at 10:15:25AM -0700, David Wall wrote: > > > > Retrieves whether this Connection object has been closed. A connection > is > > > > closed if the method close has been called on it or if certain fatal > > > > errors have occurred. > > Precisely. This makes PERFECT sense to me. It's those fatal errors that > should allow us to detect something has gone wrong and therefore should not > be retried. If the JDBC Connection gets an I/O exception, it could easily > close itself. I suspect I could add that code to the Postgresql JDBC > library myself quite easily. The unfortunate situation, though, is that > other JDBC libraries may or may not do it, so therefore it's an unreliable > bit of code, and Dmitry's hack comes back into play as perhaps the only > reliable solution with the current state of affairs among JDBC > implementations. I know for sure that Oracle does not close itself. Have you looked at the ConnectionEventListener approach? I know that it's not currently implemented in postgresql, but it should be easy enough to add, and in theory this is the standard way of doing it. -O
> Have you looked at the ConnectionEventListener approach? I know that it's > not currently implemented in postgresql, but it should be easy enough to > add, and in theory this is the standard way of doing it. No, we're still using JDK 1.3.1. It's amazing how slowly our customers migrate to new JDKs, especially the Fortune 500 players. When 1.5 comes out, they'll no doubt let us look into 1.4. Also, this is an entire new set of javax.sql classes that are not used in our application, so there are more issues for us when that time comes... Thanks, David
On Tue, Jul 22, 2003 at 05:25:36PM -0700, David Wall wrote: > > Have you looked at the ConnectionEventListener approach? I know that it's > > not currently implemented in postgresql, but it should be easy enough to > > add, and in theory this is the standard way of doing it. > > No, we're still using JDK 1.3.1. It's amazing how slowly our customers > migrate to new JDKs, especially the Fortune 500 players. When 1.5 comes > out, they'll no doubt let us look into 1.4. > > Also, this is an entire new set of javax.sql classes that are not used in > our application, so there are more issues for us when that time comes... You can get at javax.sql under 1.3 (or probably even 1.2), it's just not there as a standard package; pre-1.4 it was part of the "EE" sql addons. Look for a "JDBC2 optional extensions" download somewhere on java.sun.com. (and then you get to rewrite all your app code to use Datasources! so much for that plan.) -O
> >>When we get the SQLSTATE patch in you'll be able to do:
> >>if ((e.getSQLState() != null) && (e.getSQLState().equals("08S01")))
> >>throw new ExplainException("Communication link error: " +
e.getMessage());
....
> First of all, this is plain SQL + JDBC specs.
Really! Wow, now that's cool. When will the SQLSTATE patch be available?
And it will really work with Oracle and DB2, too? That's precisely the kind
of solution I'm looking for. Perhaps I'll write my code to work with or
without it for now, unless that patch will be available shortly. I need my
solution to be in place in a week or so, and I think my current coding
workaround will be compatible (sending the Connection and SQLException to a
pool handler), though this may save the 'select 1' hack.
David
David Wall wrote:
>>When we get the SQLSTATE patch in you'll be able to do:
>>
>>if ((e.getSQLState() != null) && (e.getSQLState().equals("08S01")))
>>throw new ExplainException("Communication link error: " + e.getMessage());
>
>
> Thanks, Fernando. Unfortunately, this will be a postgresql-ism, not a JDBC
> library-ism, and it will likely require that the user be on a particular
> version of the database, too (or is this bit going to work even on 7.3 and
> pre 7.3 versions too?). I'm looking for something that doesn't require a
> special patch for each of the myriad databases our customers use (MSFT SQL
> Server, Oracle, PostgreSQL MySQL and DB2/UDB to name the current list).
>
First of all, this is plain SQL + JDBC specs.
The code falls into the "implementation defined" range, but ODBC, DB2
and Oracle use the same and my patch followed that convention (a sort of
de facto standard for some of these values not pre-defined in the SQL
standard itself). I don't know if all vendors did the same though -- I
just checked these three. It is a limitation of the standards that they
did not come out with a more compreheensive set of pre-defined numbers.
I guess they do spect the applications to cope with the error code
differences somehow.
My patch was originally against 7.3, but it only includes the conditions
detected by the driver. The backend will only spit SQLSTATEs from 7.4
on (i.e., V# protocol).
--
Fernando Nasser
Red Hat Canada Ltd. E-Mail: fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario M4P 2C9
Patch applied. thanks, --Barry Kim Ho wrote: > Changed to receive the 4 bytes. > > Cheers, > > Kim > > > ------------------------------------------------------------------------ > > ? temp.diff > Index: org/postgresql/core/QueryExecutor.java > =================================================================== > RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/core/QueryExecutor.java,v > retrieving revision 1.22 > diff -c -p -r1.22 QueryExecutor.java > *** org/postgresql/core/QueryExecutor.java 29 May 2003 03:21:32 -0000 1.22 > --- org/postgresql/core/QueryExecutor.java 21 Jul 2003 17:11:17 -0000 > *************** public class QueryExecutor > *** 156,162 **** > // keep processing > break; > case 'I': // Empty Query > ! int t = pgStream.ReceiveChar(); > break; > case 'N': // Error Notification > int l_nlen = pgStream.ReceiveIntegerR(4); > --- 156,162 ---- > // keep processing > break; > case 'I': // Empty Query > ! int t = pgStream.ReceiveIntegerR(4); > break; > case 'N': // Error Notification > int l_nlen = pgStream.ReceiveIntegerR(4); > *************** public class QueryExecutor > *** 260,266 **** > // keep processing > break; > case 'I': // Empty Query > ! int t = pgStream.ReceiveChar(); > break; > case 'N': // Error Notification > statement.addWarning(pgStream.ReceiveString(connection.getEncoding())); > --- 260,266 ---- > // keep processing > break; > case 'I': // Empty Query > ! int t = pgStream.ReceiveIntegerR(4); > break; > case 'N': // Error Notification > statement.addWarning(pgStream.ReceiveString(connection.getEncoding())); > > > ------------------------------------------------------------------------ > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly