Re: pg_locks "at-a-glance" view

On Jun 19, 2008, at 8:07 AM, David Fetter wrote:
> On Wed, Jun 18, 2008 at 05:39:59PM -0700, Jeff Davis wrote:
>> I was trying to create a more "at-a-glance" view of the pg_locks
>> table.
>> I included the SQL I came up with (after talking to Merlin) at the
>> bottom of this message.
>>
>> The idea is to show any queries that are waiting on a lock, and the
>> query that currently holds the lock on which those queries are
>> waiting.
>>
>> Is my logic correct?
>
> I'm not exactly sure, but it appears to match, at first blush, what's
> in src/backend/storage/lmgr/lock.c:
> static const LOCKMASK LockConflicts[] = {

Yeah, if you look at the code, locks are defined as numbers and I
believe there's a very simple patter of what conflicts; a higher lock
number conflicts with all those that are lower. So, it might be a lot
cleaner to have a function that defines numbers for all the lock
modes and just test to see if one lock is higher than another.

I think your logic in the view is correct. It might be helpful to
also list how long the queries have been running.

I really wish we had some kind of a site for listing useful queries
like this...
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828