Re: Forcibly vacating locks

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +