# PMM - Postgres Alarm creation for row lock contention

**URL:** <https://forums.percona.com/t/pmm-postgres-alarm-creation-for-row-lock-contention/35169>\
**Category:** Percona Monitoring and Management (PMM)\
**Tags:** postgres\
**Created:** [November 21, 2024, 10:25am UTC](https://forums.percona.com/t/pmm-postgres-alarm-creation-for-row-lock-contention/35169 "2024-11-21T10:25:00Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Mata\_pg](https://avatars.discourse-cdn.com/v4/letter/m/f07891/32.png) [@Mata\_pg](https://forums.percona.com/u/Mata_pg)\
**Post date:** [November 21, 2024, 10:25am UTC](https://forums.percona.com/t/pmm-postgres-alarm-creation-for-row-lock-contention/35169/1 "2024-11-21T10:25:00Z")

</div>

Hi, I would need to implement an alter so that it is notified when a contention lasts more than a certain number of minutes, and therefore probably will not resolve itself, but could block the table indefinitely.

I tried to look in the various metrics, but I did not find anything specific, by chance has anyone of you already faced and resolved a similar situation?

Thanks so much

Have a nice day  
Ivan

---

<div class="post-metadata">

**Author:** ![Agustin\_G](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/agustin_g/32/14111_2.png) [@Agustin\_G](https://forums.percona.com/u/Agustin_G)\
**Post date:** [November 21, 2024, 10:19pm UTC](https://forums.percona.com/t/pmm-postgres-alarm-creation-for-row-lock-contention/35169/2 "2024-11-21T22:19:56Z")

</div>

Hi Ivan,

It will involve several customization steps, but it can definitely be done.

First, you need to add a custom query exporter to collect data on locks. You can get an idea on what query to use from:  
[https://wiki.postgresql.org/wiki/Lock\_Monitoring](https://wiki.postgresql.org/wiki/Lock_Monitoring)

From a PMM standpoint, seconds will be better than “age”, so just use EXTRACT(EPOCH …) instead. An example query could be:

```auto
SELECT a.datname,
         l.relation::regclass,
         l.mode,
         extract(epoch from age(now(), a.query_start)) AS "seconds"
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
JOIN pg_class c ON c.oid = l.relation
WHERE c.relkind = 'r';

```

And for custom queries (this is for MySQL, but the same applies for Postgres) check the following blog:

> **[Running Custom MySQL Queries in Percona Monitoring and Management](https://www.percona.com/blog/running-custom-queries-in-percona-monitoring-and-management/)**
>
> Percona Monitoring and Management comes with a lot of metrics out of the box, but sometimes we need to extend the them by running custom queries.

Then, once you have the metric you want, you can create a new custom template alert using it:

> **[Percona Monitoring and Management - About Percona Alerting](https://docs.percona.com/percona-monitoring-and-management/get-started/alerting.html#template-example)**
>
> Alerting notifies of important or unusual activity in your database environments so that you can identify and resolve problems quickly. When something needs your attention, Percona Alerting can be configured to automatically send you a notification...

After you have all this in place, you can easily create a new alert.

Note that I haven’t tested any of this, but I think it’s a good starting point for what you should check/investigate further. Hope it helps 🙂

---

<div class="post-metadata">

**Author:** ![Mata\_pg](https://avatars.discourse-cdn.com/v4/letter/m/f07891/32.png) [@Mata\_pg](https://forums.percona.com/u/Mata_pg)\
**Post date:** [November 22, 2024, 11:37am UTC](https://forums.percona.com/t/pmm-postgres-alarm-creation-for-row-lock-contention/35169/3 "2024-11-22T11:37:31Z")

</div>

Hi Agustin

thank you very much for your interest and for providing me with this solution.

I thought it was possible to retrieve this information directly from the default metrics, but apparently it is not so.  
I will continue to analyze the solution and will try to follow your lines.

I hope to be able to give you updates as soon as possible.

If others in the Forum have any other indications, I would be very interested in learning more.

Thanks  
Have a nice day

Ivan