Thread: Forcibly vacating locks
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
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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
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
Laurent Birtz wrote: > > 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)? Use log_line_prefix to get the process id in the log line, then use pg_cancel_backend() on the process id. > >> 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. Yea, I might be wrong. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Fri, 2008-06-20 at 18:38 -0400, Bruce Momjian wrote: > Laurent Birtz wrote: > > > 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)? > > Use log_line_prefix to get the process id in the log line, then use > pg_cancel_backend() on the process id. > > > >> 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. > > Yea, I might be wrong. I think you're very right. Don't ever want to see locks disappearing because of a timeout, sorry Laurent. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support
>> > >> 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. >> >> Yea, I might be wrong. > > I think you're very right. Don't ever want to see locks disappearing > because of a timeout, sorry Laurent. > OK, thanks all for the replies. Laurent Birtz