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 <[EMAIL PROTECTED]> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [EMAIL PROTECTED]
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
CREATE OR REPLACE FUNCTION raise_exception(in_message TEXT)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
RAISE EXCEPTION '%', in_message;
RETURN true;
END;
$$;
CREATE OR REPLACE FUNCTION lock_conflict(TEXT, TEXT)
RETURNS BOOLEAN
STRICT
LANGUAGE SQL
AS $$
SELECT
CASE
WHEN $1 NOT IN (
'AccessShareLock', 'RowShareLock', 'RowExclusiveLock',
'ShareUpdateExclusiveLock', 'ShareLock', 'ShareRowExclusiveLock',
'ExclusiveLock', 'AccessExclusiveLock'
)
OR $2 NOT IN (
'AccessShareLock', 'RowShareLock', 'RowExclusiveLock',
'ShareUpdateExclusiveLock', 'ShareLock', 'ShareRowExclusiveLock',
'ExclusiveLock', 'AccessExclusiveLock'
) THEN
raise_exception('Both arguments must be valid lock names.')
WHEN $1 = 'AccessShareLock' THEN
CASE WHEN $2 = 'AccessExclusiveLock' THEN
TRUE
ELSE
FALSE
END
WHEN $1 = 'RowShareLock' THEN
CASE WHEN $2 = 'ExclusiveLock' OR
$2 = 'AccessExclusiveLock' THEN
TRUE
ELSE
FALSE
END
WHEN $1 = 'RowExclusiveLock' THEN
CASE WHEN $2 = 'ShareLock' OR
$2 = 'ShareRowExclusiveLock' OR
$2 = 'ExclusiveLock' OR
$2 = 'AccessExclusiveLock' THEN
TRUE
ELSE
FALSE
END
WHEN $1 = 'ShareUpdateExclusiveLock' THEN
CASE WHEN $2 = 'ShareUpdateExclusiveLock' OR
$2 = 'ShareLock' OR
$2 = 'ShareRowExclusiveLock' OR
$2 = 'ExclusiveLock' OR
$2 = 'AccessExclusiveLock' THEN
TRUE
ELSE
FALSE
END
WHEN $1 = 'ShareLock' THEN
CASE WHEN $2 = 'RowExclusiveLock' OR
$2 = 'ShareUpdateExclusiveLock' OR
$2 = 'ShareRowExclusiveLock' OR
$2 = 'ExclusiveLock' OR
$2 = 'AccessExclusiveLock' THEN
TRUE
ELSE
FALSE
END
WHEN $1 = 'ShareRowExclusiveLock' THEN
CASE WHEN $2 = 'RowExclusiveLock' OR
$2 = 'ShareUpdateExclusiveLock' OR
$2 = 'ShareLock' OR
$2 = 'ShareRowExclusiveLock' OR
$2 = 'ExclusiveLock' OR
$2 = 'AccessExclusiveLock' THEN
TRUE
ELSE
FALSE
END
WHEN $1 = 'ExclusiveLock' THEN
CASE WHEN $2 = 'RowShareLock' OR
$2 = 'RowExclusiveLock' OR
$2 = 'ShareUpdateExclusiveLock' OR
$2 = 'ShareLock' OR
$2 = 'ShareRowExclusiveLock' OR
$2 = 'ExclusiveLock' OR
$2 = 'AccessExclusiveLock' THEN
TRUE
ELSE
FALSE
END
WHEN $1 = 'AccessExclusiveLock' THEN
TRUE
END
$$;
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
