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