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@commandprompt.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@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

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