not responding, server locking up.

several of you reading these lists may remember my server locking up 
problem -- we have 20 users on a linux rh kernel 2.2.2, postgres 6.4.2, 
odbc Version='06.40.0005', front end all msaccess97, and the server would 
lock up continually.  actually postgres would lock up.  the server was 
perfectly stable.  we ultimately found that by turning off "use 
declare/fetch", no more locking up, but occasionally users would get access 
to freeze (evidenced by ctrl alt del and seeing that access was not 
responding).  i have set all queries timeout to 180 seconds.  by the way, 
each user has the exact same front-end access file, an MDE file so they can 
make no changes.

well, i still need to understand something that is going on -- maybe you 
have some ideas for me to check??  our controller must run financial 
reports weekly.  these reports usually are larger and sift through many 
records (and return many records too!).  he MUST have declare/fetch on to 
run the reports -- it will send access into "not responding" territory 
every time if he doesn't.  we have the postgres server running on a dual 
pentium 233, 128m ram, dual 4.3g hd's (ide though) -- i'd expect it to be 
very fast.  in most cases it is.  but when the controller runs the 
financial reports you can nearly guarantee the server will be locked again. it'll still serve him, and complete his
report. and it'll serve others 
 
too, even at the same time.  but if i, from another virtual terminal on the 
server, look at processes (ps axfwww) i can see SOME of the users in 
WAITING status, and others idle.  the idle ones are actually doing stuff, 
just idle at the time i ran the ps axfwww.  why did it pick on just SOME of 
the users?  also, because they were thrown into WAITING status, access 
immediately went into that "not responding" territory -- so after the 
server freed up and tried to process their request it found a dead process.

as i try to watch in the debug window (i am running debug level 3), when it 
locks up i cannot use shift pgup to see what happened a few screens ago.

do you think this is an odbc issue?  an access configuration issue 
(although i don't know where or how to change how access is configured!)? a windows issue??

i'd be happy to get it stable enough to run for a year or so until i can 
get the front-end done in cgi/perl/html or apache (is that php+??).  surely 
i shouldn't expect users to get kicked out whenever someone runs a larger 
report or query??!!??

i'd appreciate any suggestions you may have.  have a great holiday!

jt kirkpatrick / www.mpsllc.com