Thread: RE: Postgresql 7.0 JDBC exceptions - broken connecti ons ?
As usual when replying from here, replies prefixed with PM: -- Peter Mount Enterprise Support Maidstone Borough Council Any views stated are my own, and not those of Maidstone Borough Council. -----Original Message----- From: Gunnar R|nning [mailto:gunnar@candleweb.no] Sent: Thursday, May 25, 2000 12:38 PM To: pgsql-interfaces@postgresql.org Subject: [INTERFACES] Postgresql 7.0 JDBC exceptions - broken connections ? Hello, As I told you in an former mail I'm trying to migrate an application to use PostgreSQL 7.0. The application now seems to be working pretty well and I have 5 users that have been testing our web application with the PostgreSQL database for the past 24 hours. The application runs with a connection pool, but after some time some of these connections seems to be broken. Ie. only some of the queries work - I will change the connection pool code to handle this, but I would like to know if anybody know why the connections gets into an unusable state. Could it be back crashes or similar things ? I'm turning on debugging for the database server to see if can find anything there, but anyway here is the exception I get : PM: How long is it before the problem starts? I'm wondering if the problem is because the backend is sitting there for a long period. select distinct entity.*,location.loc_id,location.loc_name from entity,locationmap,location,entityindex2 as e0 where locationmap.ent_id=entity.ent_id and locationmap.loc_id=location.loc_id and e0.ei_word='kjøttbørsen' and e0.ent_id=entity.ent_id and ENT_STATUS=4 order by ent_title,location.loc_name,location.loc_id Unknown Response Type u PM: Does anyone [on Hackers] know what the u code is for? The fact it's in lower case tells me that the protocol/connection got broken somehow.
Peter Mount <petermount@it.maidstone.gov.uk> writes: > PM: How long is it before the problem starts? I'm wondering if the > problem is because the backend is sitting there for a long period. The problem started after the connections had been for about 16 hours or so. So this could be the problem. However yesterday I restarted my database with some new options to do more logging and also print some debug information. I haven't seen any of the exceptions I reported yesterday in the past 20 hours after the restart. > Unknown Response Type u > > PM: Does anyone [on Hackers] know what the u code is for? The fact it's > in lower case tells me that the protocol/connection got broken somehow. I got a lot of these errors and the response type varied between different characters, so your theory seems plausible. Regards, Gunnar
Peter Mount <petermount@it.maidstone.gov.uk> writes:
> Unknown Response Type u
> PM: Does anyone [on Hackers] know what the u code is for? The fact it's
> in lower case tells me that the protocol/connection got broken somehow.
There is no 'u' message code. Looks to me like the client got out of
sync with the backend and is trying to interpret data as the start of
a message.
I think that this and the "Tuple received before MetaData" issue could
have a common cause, namely running out of memory on the client side
and not recovering well. libpq is known to emit its equivalent of
"Tuple received before MetaData" when the backend hasn't violated the
protocol at all. What happens is that libpq runs out of memory while
trying to accumulate a large query result, "recovers" by resetting
itself to no-query-active state, and then is surprised when the next
message is another tuple. (Obviously this error recovery plan needs
work, but no one's got round to it yet.) I wonder whether the JDBC
driver has a similar problem, and whether these queries could have
been retrieving enough data to trigger it?
Another possibility is that the client app is failing to release
query results when done with them, which would eventually lead to
an out-of-memory condition even with not-so-large queries.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > I think that this and the "Tuple received before MetaData" issue could > have a common cause, namely running out of memory on the client side > and not recovering well. libpq is known to emit its equivalent of > "Tuple received before MetaData" when the backend hasn't violated the > protocol at all. What happens is that libpq runs out of memory while > trying to accumulate a large query result, "recovers" by resetting > itself to no-query-active state, and then is surprised when the next > message is another tuple. (Obviously this error recovery plan needs > work, but no one's got round to it yet.) I wonder whether the JDBC > driver has a similar problem, and whether these queries could have > been retrieving enough data to trigger it? > This could be a possible explanation, as some of the queries may indeed retrieve large amounts of data. I have also noticed a couple of "Out of Memory" exceptions that could be related(This seem to be "temporary" out of memory exceptions, and not permanent memory leaks; so I guess these could be caused by queries returning huge amounts of data). > Another possibility is that the client app is failing to release > query results when done with them, which would eventually lead to > an out-of-memory condition even with not-so-large queries. I don't think this is the case. I've been running the application through OptimizeIT to profile memory and CPU usage and I haven't been able to spot any memory leakages in the driver; The quality of the JDBC driver is actually our main reason to migrate our application to PostgreSQL. regards, Gunnar