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

Lock tree: All PID's waiting on a lock held by/blocked by single blocker
PID. Similar to what you see in the output of this script:
https://github.com/dataegret/pg-utils/blob/master/sql/locktree.sql . It
uses the dot connotation to draw a tree.

Waiters: The PID (first column) returned by this query, for example

SELECT
activity.pid,
activity.usename,
activity.query,
blocking.pid AS blocking_id,
blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid =
ANY(pg_blocking_pids(activity.pid));

DDL example: An 'alter table ... alter column ...' would cause all DML and
SELECT statements to wait/block.

Hope this answers your question. Thanks for your interest.

On Fri, Mar 22, 2024 at 12:32 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 3/22/24 09:25, Fred Habash 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
>
> The above needs more explanation:
>
> 1) Define locking tree.
>
> 2) Define waiters.
>
> 3) Provide examples of the DDL.
>
>
> > 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.
> >
> > idle_in_transaction_session_timeout
> > idle_session_timeout
> > lock_timeout: How long a session waits for a lock
> > statement_timeout
> > authentication_timeout
> > deadlock_timeout
> > log_lock_waits
> >
> > Instead, I put together a quick procedure that counts waiter sessions
> > for a given blocker and terminates it if waiter count exceeds a
> threshold.
> >
> > Is there not a native way to ...
> > 1. Automatically time out a blocker
> > 2. A metric that shows how many waiters for a blocker?
> >
> > Thanks
> > --
> >
> > ----------------------------------------
> > Thank you
> >
> >
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
>

--

----------------------------------------
Thank you