Re: Logging conflicted queries on deadlocks

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.

---------------------------------------------------------------------------

ITAGAKI Takahiro wrote:
>
> 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. (It might be better to log
> all queries in the server log and mask them in the client response, but
> I'm not sure how to do it...)
>
> | ERROR: deadlock detected
> | DETAIL: Process 3088 waits for ShareLock on transaction 608; blocked by process 2928.
> | Process 2928 waits for ShareLock on transaction 609; blocked by process 2824.
> | Process 2824 waits for ShareLock on transaction 610; blocked by process 3088.
> | CONTEXT: Process 3088: UPDATE test SET i = i WHERE i = 1;
> | Process 2928: <insufficient privilege>
> | Process 2824: UPDATE test SET i = i WHERE i = 3;
> | STATEMENT: UPDATE test SET i = i WHERE i = 1;
>
>
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
>
> > Perhaps it could be shown in CONTEXT, like so:
> >
> > I think it's useful to show the PID of each statement, for the case
> > where there are more than two processes deadlocked.
>
> Thanks for response. I bought your suggestion :-)
>
> Regards,
> ---
> ITAGAKI Takahiro
> NTT Open Source Software Center
>

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +