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