Thread: deadlock error messages

When there's a deadlock detected, the error message recorded in the
log prints out the query being aborted, and the process id of the
other transaction involved in the deadlock.  It would be great if more
context was printed for the other query (e.g. the query itself), as
opposed to just the process id.  By the time the logs are parsed, the
process id isn't very useful.  Example output we can parse  out:

PGError: ERROR:  deadlock detected
DETAIL:  Process 29245 waits for ShareLock on transaction 136665841;
blocked by process 4483.
Process 4483 waits for ShareLock on transaction 136665996; blocked by
process 29245.
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."forms" x WHERE
"id" = $1 FOR SHARE OF x"
SQL statement "INSERT INTO form_qualifications (arrival_id,
program_id, simple_form_id, associated_form_id, position, site_id,
created_at) select  $1 ,  $2 [gs.ser],  $3 [gs.ser],  $4 ,  $5 ,  $6 ,
now() from generate_series(1, array_upper( $3 , 1)) as gs(ser)"
PL/pgSQL function "insert_form_qualifications" line 2 at SQL statement
:
           select insert_form_qualifications(73404580,

array
[9997,9998,11334,2034,2051,10006,2053,6088,8966,2054,8967,2055,2056,8977,8978,2058,2052,2065
],

array
[6991,6991,6991,6831,6831,6831,6831,6831,6831,6831,6831,6831,6831,6992,6831,6992,6831,6831
],
                                             1479,
                                             2,
                                             1)

What would be really great would be to know what the other query is,
as opposed to just the pid (not sure from this output if it's 4483 or
29245).  Also not sure if this is the right list for this.  But
potentially someone on here may have a good tip on debugging
deadlocking?

Thanks,

Dan

dan chak wrote:
>
> What would be really great would be to know what the other query is,
> as opposed to just the pid (not sure from this output if it's 4483 or
> 29245).  Also not sure if this is the right list for this.  But
> potentially someone on here may have a good tip on debugging deadlocking?
>
Just logging the other query would be a nasty information disclosure
problem.

Process blah waits for sharelock on transaction blah blocked by process blah
Context: sql statement "update passwords set password = 'kitty' where
user = 'dummy'"
Blocking sql statement: "update passwords set password = 'woof' where
user 'dummy'"

Ouch.

What could be more securely done, though, would be to issue a NOTICE on
the backend on which the deadlocking transaction not killed is running
that includes the backend pid and the problem statement. Your later log
analysis could then match up the statements from the separate log
records. Including the transaction IDs of both in both log lines would
be nice too, as pids get reused.

Sound sane?

--
Craig Ringer