Laurent Birtz wrote: > Hello, > > I am using Postgres in a high-availability environment and I'd like to > know whether Postgres has provisions to kick off a misbehaving client > that has obtained an advisory lock on the database and won't release it > in a timely fashion. I am not worried about malicious clients, however I > am concerned that a client may hang for a very long time in the middle of > a transaction due to a programming error, an overloaded machine or > another bizarre set of circumstances. TCP keepalive packets can improve > the situation, but they won't prevent some problems from occurring. > > For this reason, it is the policy of my company to avoid using explicit > locks in Postgres altogether. However, as you can imagine, it is hard at > times to avoid race conditions with this programming model. > > Thus, I'd like Postgres to offer a function like set_watchdog(int nb_ms). > I would call set_watchdog(10000) to enable the watchdog just before I > obtained the lock, then I would call set_watchdog(0) to disable the > watchdog after I released the lock. If a client froze, the watchdog would > eventually trigger and drop the connection to the client, thereby > preventing the whole system from freezing. > > I have three specific questions: > > 1) Does Postgres offer something like this already? I'm aware of > statement_timeout, but it doesn't do exactly what I need. A possible > kludge would be to parse the 'pg_locks' table and kill the offending > Postgres backend, but I'd rather avoid doing this. No. The closest thing we have is log_lock_waits in 8.3. I wonder if you could hack up something to monitor the server logs for such messages and cancel the queries. > 2) Is there any hostility about the notion of implementing this feature > into Postgres? Probabably --- it seems like a narrow use case. > 3) Would it be hard to implement it? After a brief code review, I think > it would make sense to reuse the SIGALARM signal used by > statement_timeout to forcibly close the Postgres connection when > the watchdog triggers. Not too hard. -- Bruce Momjian <bruce@xxxxxxxxxx> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
