> On Mar 22, 2024, at 09:25, Fred Habash <fmhab...@gmail.com> wrote:
>
> Facing an issue where sometimes humans login to a database and run DDL
> statements causing a long locking tree of over 1000 waiters. 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 holding a lock on which other sessions are
waiting after a certain amount of time, even if the session is actively running
queries?", 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 actively running a query. A combination
of `idle_in_transaction_session_timeout` and `statement_timeout` will get you
very close 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. It does
not also discriminate between transactions that are holding locks on which
other sessions are waiting, and ones that aren't.
You could write a polling script that checks pg_stat_activity and pg_locks, and
terminates transactions that have been running longer than x seconds, and which
are holding locks that other sessions are waiting on. I'm not sure that's
really a recommended course of action, though, as scripts like that can often
kill things you didn't really mean them to.
