Re: Logging conflicted queries on deadlocks

Tom Lane schrieb:
> ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp> writes:
>
>> Here is a patch to log conflicted queries on deadlocks. Queries are dumped
>> at CONTEXT in the same sorting order as DETAIL messages. Those queries are
>> picked from pg_stat_get_backend_activity, as same as pg_stat_activity,
>> so that users cannot see other user's queries.
>>
>
> Applied with revisions.  It's a cute idea --- I first thought it
> couldn't work reliably because of race conditions, but actually we
> haven't aborted our own transaction at this point, so everyone else
> involved in the deadlock is still waiting and it should be safe to
> grab their activity strings.
>
> However there was still a big implementation problem, which is that
> looking at pg_stat_activity could deliver very stale results, not only
> about other backends' queries but even our own.  The data for that
> comes from a snapshot that might have been taken much earlier in our
> transaction.  I replaced the code you were using with a new pgstat.c
> entry point that delivers up-to-date info directly from the shared
> memory array.
>

Is there a chance that I might get this patch running in 8.1 and 8.2
releases? I modified the original patch to compile with 8.2.7, but it
did not work:
ERROR:  deadlock detected
DETAIL:  Process 26289 waits for ShareLock on transaction 57039190;
blocked by process 26277.
Process 26277 waits for ShareLock on transaction 57039191; blocked by
process 26289.
CONTEXT:  Process 26289: <backend information not available>
Process 26277: <backend information not available>