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

Re: Logging conflicted queries on deadlocks

From: Mario Weilguni <mweilguni(at)sime(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>, pgsql-patches(at)postgresql(dot)org, Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Subject: Re: Logging conflicted queries on deadlocks
Date: 2008-06-05 13:17:46
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackerspgsql-patches
Tom Lane schrieb:
> ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)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>

In response to

pgsql-hackers by date

Next:From: Simon RiggsDate: 2008-06-05 13:27:50
Subject: Re: pg_dump restore time and Foreign Keys
Previous:From: Heikki LinnakangasDate: 2008-06-05 13:01:26
Subject: Re: pg_dump restore time and Foreign Keys

pgsql-patches by date

Next:From: Pavel StehuleDate: 2008-06-06 05:06:04
Subject: array_fill function
Previous:From: Heikki LinnakangasDate: 2008-06-04 08:09:37
Subject: Re: Free Space Map rewrite

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