Bruce Momjian wrote:
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.
Assuming I can monitor the logs in this way, how would I cancel the queries (or lack thereof, in the case of a client that sits doing nothing
with a held lock)?
2) Is there any hostility about the notion of implementing this feature into Postgres?Probabably --- it seems like a narrow use case.
I'll consider this to be the definite answer unless I hear a dissenting opinion in the next few days. Thanks for your time, Laurent Birtz
