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