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. 2) Is there any hostility about the notion of implementing this feature into Postgres? 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. Thanks a lot for any response! Laurent Birtz
