Re: Deadlock

I think what is being said is that parsing the logs or looking for specific
events such as a deadlock is the responsibility of the monitoring or the
application interacting with the database, not the database server. The
database server is simply tasked with logging / providing notice to the
client in this instance. Ideally, as previously stated, the application
should be determining how to address the issue.

If memory serves, pg_stat_database
<https://www.postgresql.org/docs/16/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW>
and pg_stat_database_conflicts
<https://www.postgresql.org/docs/16/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-CONFLICTS-VIEW>
views
have some statistics and information regarding deadlocks which can be
checked.

As for monitoring: Percona Monitoring and Management
<https://www.percona.com/software/database-tools/percona-monitoring-and-management/postgresql-monitoring>,
pganalyze
<https://pganalyze.com/blog/postgresql-log-monitoring-101-deadlocks-checkpoints-blocked-queries>,
pgbadger <http://pgbadger.darold.net/> or any similar tool might prove to
be helpful. Ultimately, whatever alerting you configure from monitoring is
what should be sending emails about potential issues vs the database server
directly. There are probably extensions or walkthroughs on using Postgres
to trigger sending of emails in a manner similar to SQL Server but this
does not seem aligned with the database server's purpose (manage access,
storage, and retrieval of data). Hope this information helps you find a
suitable solution for your use case.

Best regards,

Dan Smith

On Tue, Apr 2, 2024 at 3:01 PM Rajesh Kumar <rajeshkumar(dot)dba09(at)gmail(dot)com>
wrote:

> Ok, do u mean to say that we do not have to do anything if I see an error
> in log for deadlock lock?
>
>
>
> On Tue, 2 Apr 2024, 23:12 Holger Jakobs, <holger(at)jakobs(dot)com> wrote:
>
>>
>> Am 02.04.24 um 19:09 schrieb Rajesh Kumar:
>>
>> Yes, I am trying to find out what message i should reply if I receive
>> email with deadlock notification.
>>
>> I understand how deadlock occurs, hot to find them in logs, prevention of
>> deadlock(ex: oqueries in order) etc.
>>
>> But since the deadlock_timeout is 1s, they don't probably hang around. So
>> post getting an alert, not sure what to respond to them
>>
>> On Tue, 2 Apr 2024, 22:15 Holger Jakobs, <holger(at)jakobs(dot)com> wrote:
>>
>>> Am 02.04.24 um 18:40 schrieb Rajesh Kumar:
>>>
>>> Hi
>>>
>>> If I receive an alert for deadlock, what should I do? Like what analysis
>>> should I do? No way to find a root cause?
>>> Under pg_locks, what I can analyse?
>>>
>>> If you receive an error with a deadlock, the current transaction is
>>> dead. You have to decide whether to try again.
>>>
>>> Within the error message you find a hint which other (not aborted)
>>> transaction was involved in the deadlock. So you have all the information
>>> you need to find the culprit.
>>>
>>> Regards,
>>>
>>> Holger
>>> --
>>>
>>> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
>>>
>>> chat with me on Signal messenger
>>> <https://signal.me/#eu/GpIP259bBne_JiBYJFvbZCLYJELhB-92nJfncACL1LUeBOphfXFFgLYYVXv_AGhg>
>>>
>> I wonder what you are writing about. A database doesn't send email
>> notifications. That must be a feature of some application.
>>
>> So you will have to deal the the application code or its developers.
>>
>> Or is it a customer sending you a message that some deadlock happened on
>> their system? Still, you have to deal with the application code or the log
>> files of the database system.
>>
>> Regards,
>> Holger
>>
>