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?
From a PMM standpoint, seconds will be better than “age”, so just use EXTRACT(EPOCH …) instead. An example query could be:
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:
Then, once you have the metric you want, you can create a new custom template alert using it:
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
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.