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.kla...@aklaver.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.kla...@aklaver.com > > -- ---------------------------------------- Thank you