pg_locks "at-a-glance" view

[Jeff Davis]

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;
$$;

--
Sent via pgsql-general mailing list (pgsql-...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Merlin Moncure]

On Thu, Jun 19, 2008 at 9:07 AM, David Fetter <da...@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

[Jeff Davis]

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

[Alvaro Herrera]

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.