Feedback-ID: i76984098:Fastmail
Message-ID: <8a8aef5b-5239-4617-8b4c-ec54de02d4bb@aklaver.com>
Date: Fri, 22 Mar 2024 15:24:04 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Timing out A Blocker Based on Time or Count of Waiters
To: Fred Habash <fmhabash@gmail.com>
Cc: pgsql-general@lists.postgresql.org
References: 
 <CADpeV5yrSVGjKoK_cLwyf3KaQXkbTan8MjtmaZgACrbmWmc8Pg@mail.gmail.com>
 <4d234943-b4bf-4e59-aa89-e5d69e18a228@aklaver.com>
 <CADpeV5wu6DXx+zUkhiX2NmwePiukY5aiZMP+rp4Wg92h8D=Hmg@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: 
 <CADpeV5wu6DXx+zUkhiX2NmwePiukY5aiZMP+rp4Wg92h8D=Hmg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/8a8aef5b-5239-4617-8b4c-ec54de02d4bb%40aklaver.com>
Precedence: bulk

On 3/22/24 12:41, Fred Habash wrote:
> 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 
> <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.

Yes.

To me the issue is,

"Facing an issue where sometimes humans login to a database and run DDL 
statements causing a long locking tree of over 1000 waiters."

where the problem is people running disruptive statements without regard 
to planning or what else is happening on the database. I am not sure 
that dropping a statement just based on a count is progress. If the DDL 
is important then it needs to be run at some point and you are 
conceivably back at the same blocking issue. This then leads to two 
possibilities either the DDL is not important and shouldn't be run or it 
is and some thought and timing needs to be applied before it is run.

> 
> On Fri, Mar 22, 2024 at 12:32 PM Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 
> 
> 
> -- 
> 
> ----------------------------------------
> Thank you
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com