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 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 > >