Hi,
I'm new to PostgreSQL and have been asked to determine the cause of what appear to be hung processes on FreeBSD after one or more frontend apps crash. I did alot of searching through the msg lists and found a few discussions that seem related, but I was unable to find a resolution in the msg archives. I noticed the last item in changes for PostgreSQL v7.0.3:
Fix for crash of backend, on abort (Tom)
Is this related? Our scenario is, a frontend java program creates multiple connections to PostgreSQL v7.0.2 attempting to exceed MAXBACKENDS. If the program crashes(unhandled exception) we're left with hung (or waiting processes) on FreeBSD equal to the number of successful connections (ps log below). Subsequent connection attempts are eventually rejected (when MAXBACKENDS is reached) with "Sorry, too many clients already". I've waited for over an hour to see if these processes get cleaned up, but they don't.
The only msgs I could dig up that seem like they _could_ be related are a discussion between Dirk Niggemann and Tom Lane in Oct/1999 ("timeouts in libpq- can libpq requests block forever/a very long time?" - PGTIMEOUT and PGCONNTIMEOUT) - I could be way off the mark on this one though...
Thanks for any and all advice.
Peter Schmidt
postgres@dev-postgres:~ > ps -cl -U postgres
UID PID PPID CPU PRI NI VSZ RSS WCHAN STAT TT TIME COMMAND
500 1395 1 0 2 0 4040 2380 select Ss ?? 0:01.17 postgres
500 2255 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2256 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2257 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2258 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2259 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2260 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2261 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2262 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2263 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2264 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2265 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2266 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2267 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2268 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2269 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2270 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2271 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2272 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2273 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2274 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2275 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2317 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2318 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2319 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2320 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2321 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2322 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2323 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2324 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2325 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2326 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 2327 1395 0 2 0 4384 2984 sbwait I ?? 0:00.01 postgres
500 892 890 0 10 0 1636 1412 wait S p2 0:00.37 bash
500 979 892 0 28 0 1672 1368 - T p2 0:00.02 psql
500 2385 892 0 28 0 440 264 - R+ p2 0:00.00 ps
