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








Hung backends


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
Thu, 16 Nov 2000 20:21:57 -0800
"Schmidt, Peter" <peter.schmidt@prismedia.com>
Hung backends