On Thu, May 9, 2024 at 11:44:03PM +0000, Karoline Pauls wrote:
> As we know, the deadlock error message isn't the most friendly one. All the
> client gets back is process PIDs, transaction IDs, and lock types. You have to
> check the server log to retrieve lock details. This is tedious.
>
> In one of my apps I even added a deadlock exception handler on the app side to
> query pg_stat_activity for processes involved in the deadlock and include their
> application names and queries in the exception message. It is a little racy but
> works well enough.
>
> Ideally I'd like to see that data coming from Postgres upon detecting the
> deadlock. That's why I made this small change.
>
> The change makes the deadlock error look as follows - the new element is the
> application name or "<insufficient privilege>" in its place if the activity
> user doesn't match the current user (and the current use isn't a superuser):
>
> postgres=*> SELECT * FROM q WHERE id = 2 FOR UPDATE;
> ERROR: deadlock detected
> DETAIL: Process 194520 (application_name: <insufficient privilege>) waits for
> ShareLock on transaction 776; blocked by process 194521.
> Process 194521 (application_name: woof) waits for ShareLock on transaction 775;
> blocked by process 194520.
> HINT: See server log for query details.
> CONTEXT: while locking tuple (0,2) in relation "q"
log_line_prefix supports application name --- why would you not use
that?
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
