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"
I added a new LocalPgBackendCurrentActivity struct combining application name and query string pointers and a sameProcess boolean. It is returned by value, since it's small. Performance-wise, this is a a part of the deadlock handler, if the DB hits it frequently, there are much more serious problems going on.
I could extend it by sending the queries back to the client, with an identical security check, but this is a potential information exposure of whatever's in the query plaintext. Another extension is to replace "(application_name: <insufficient privilege>)" with something better like "(unknown application_name)", or even nothing.
Attached patch is for master, 2fb7560c. It doesn't contain any tests.
Let me know if you approve of the patch and if it makes sense to continue working on it.
Best,