Skip site navigation (1) Skip section navigation (2)

Logging conflicted queries on deadlocks

From: ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
To: pgsql-patches(at)postgresql(dot)org
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Subject: Logging conflicted queries on deadlocks
Date: 2008-02-29 03:12:58
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackerspgsql-patches
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 :-)

ITAGAKI Takahiro
NTT Open Source Software Center

Attachment: deadlock.patch
Description: application/octet-stream (2.2 KB)

In response to


pgsql-hackers by date

Next:From: Tom LaneDate: 2008-02-29 03:23:21
Subject: A couple of PG schedule reminders
Previous:From: John SmithDate: 2008-02-29 01:54:38
Subject: "could not open relation 1663/16384/16584: No such file or directory" in a specific combination of transactions with temp tables

pgsql-patches by date

Next:From: Andrew DunstanDate: 2008-02-29 05:17:51
Subject: Re: Fix for initdb failures on Vista
Previous:From: Neil ConwayDate: 2008-02-29 02:50:35
Subject: Re: SRF memory leaks

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group