Thread: pg_locks "at-a-glance" view
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? Does anyone have any suggestions?
I couldn't find a generally accepted way to do this, although I'm sure
someone must have done something like this before.
Also, I had to define a function "lock_conflict()" (also included)
because I couldn't find a function to determine if two lock modes
conflict. The function is somewhat thrown together so it may have a few
problems; I just included it so people can run the example view.
Regards,
Jeff Davis
CREATE OR REPLACE VIEW query_lock_wait AS
SELECT
l1.pid AS pid,
a1.current_query AS query,
l2.pid AS waiting_on_pid,
a2.current_query AS waiting_on_query
FROM
pg_locks l1,
pg_locks l2,
pg_stat_activity a1,
pg_stat_activity a2
WHERE
l1.pid = a1.procpid AND
l2.pid = a2.procpid AND
NOT l1.granted AND
l2.granted AND
l1.locktype = l2.locktype AND
l1.pid <> l2.pid AND
(
(l1.locktype, l1.database, l1.relation, l1.page, l1.tuple,
l1.virtualxid, l1.transactionid, l1.classid, l1.objid, l1.objsubid)
IS NOT DISTINCT FROM
(l2.locktype, l2.database, l2.relation, l2.page, l2.tuple,
l2.virtualxid, l2.transactionid, l2.classid, l2.objid, l2.objsubid)
) AND
lock_conflict(l1.mode, l2.mode);
CREATE OR REPLACE FUNCTION lock_conflict(TEXT, TEXT)
RETURNS BOOLEAN LANGUAGE plpgsql AS
$$
BEGIN
IF $1 = 'AccessShareLock' THEN
IF $2 = 'AccessExclusiveLock' THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSIF $1 = 'RowShareLock' THEN
IF $2 = 'ExclusiveLock' OR
$2 = 'AccessExclusiveLock' THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSIF $1 = 'RowExclusiveLock' THEN
IF $2 = 'ShareLock' OR
$2 = 'ShareRowExclusiveLock' OR
$2 = 'ExclusiveLock' OR
$2 = 'AccessExclusiveLock' THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSIF $1 = 'ShareUpdateExclusiveLock' THEN
IF $2 = 'ShareUpdateExclusiveLock' OR
$2 = 'ShareLock' OR
$2 = 'ShareRowExclusiveLock' OR
$2 = 'ExclusiveLock' OR
$2 = 'AccessExclusiveLock' THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSIF $1 = 'ShareLock' THEN
IF $2 = 'RowExclusiveLock' OR
$2 = 'ShareUpdateExclusiveLock' OR
$2 = 'ShareRowExclusiveLock' OR
$2 = 'ExclusiveLock' OR
$2 = 'AccessExclusiveLock' THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSIF $1 = 'ShareRowExclusiveLock' THEN
IF $2 = 'RowExclusiveLock' OR
$2 = 'ShareUpdateExclusiveLock' OR
$2 = 'ShareLock' OR
$2 = 'ShareRowExclusiveLock' OR
$2 = 'ExclusiveLock' OR
$2 = 'AccessExclusiveLock' THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSIF $1 = 'ExclusiveLock' THEN
IF $2 = 'RowShareLock' OR
$2 = 'RowExclusiveLock' OR
$2 = 'ShareUpdateExclusiveLock' OR
$2 = 'ShareLock' OR
$2 = 'ShareRowExclusiveLock' OR
$2 = 'ExclusiveLock' OR
$2 = 'AccessExclusiveLock' THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSIF $1 = 'AccessExclusiveLock' THEN
RETURN TRUE;
ELSE
RAISE EXCEPTION 'Invalid Lock Mode: %', $1;
END IF;
END;
$$;
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[] = {
> Does anyone have any suggestions?
The function could be in SQL, with one minor bit in PL/PgSQL. File
attached.
> I couldn't find a generally accepted way to do this, although I'm sure
> someone must have done something like this before.
There's stuff in the aforementioned lock.c, but I don't see anything
visible to SQL.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Attachment
On Thu, Jun 19, 2008 at 9:07 AM, David Fetter <david@fetter.org> > There's stuff in the aforementioned lock.c, but I don't see anything > visible to SQL. Maybe it should be...via C. if you use an enum for lockmode, you don't need pl/pgsql at all... merlin
On Thu, 2008-06-19 at 06:07 -0700, David Fetter wrote:
> 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[] = {
>
I was more interested in the view itself. Is the view an accurate way to
interpret pg_locks?
Regards,
Jeff Davis
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@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Attachment
Decibel! escribió: > 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. This is not always the case. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.