Re: Forcibly vacating locks

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