Re: Timing out A Blocker Based on Time or Count of Waiters

> On Mar 22, 2024, at 09:25, Fred Habash <fmhabash@gmail.com> wrote:
>
> Facing an issue where sometimes humans login to a database and run DDL statements causing a long locking tree of over
1000waiters. As a workaround, we asked developers to always start their DDL sessions with 'SET lock_timeout = 'Xs'.  
>
> I reviewed the native lock timeout parameter in Postgres and found 7. None seem to be related to blocker timeouts
directly.

"Blocker" isn't totally clear to me, but assuming you mean, "Is there a way of terminating a transaction that is
holdinga lock on which other sessions are waiting after a certain amount of time, even if the session is actively
runningqueries?", the answer is no, there's no specific setting in PostgreSQL that does that automatically. 

The most apropos setting is `idle_in_transaction_session_timeout`, but that will not terminate a session that is
activelyrunning a query.  A combination of `idle_in_transaction_session_timeout` and `statement_timeout` will get you
veryclose to it, however.  That won't catch a session that is running queries that are less take less than
`statement_timeout`to complete, and don't wait more than `idle_in_transaction_session_timeout` to issue a new query.
Itdoes not also discriminate between transactions that are holding locks on which other sessions are waiting, and ones
thataren't. 

You could write a polling script that checks pg_stat_activity and pg_locks, and terminates transactions that have been
runninglonger than x seconds, and which are holding locks that other sessions are waiting on.  I'm not sure that's
reallya recommended course of action, though, as scripts like that can often kill things you didn't really mean them
to.