RFC: listing lock status

I've been working on the TODO list item "Add SHOW command to display locks". The
code is basically finished, but I'd like to make sure the user interface is okay
with everyone before I send it in to -patches (if you're interested, the patch
is attached).

Rather than adding another SHOW command, I think using a table function
is a better idea. That's because the information returned by the lock
listing code will often need to be correlated with other information in
the system catalogs, or sorted/aggregated in various ways (e.g. "show me
the names of all locked relations", or "show me the relation with the most
AccessShareLocks'"). Written as a table function, the lock listing code
itself can be fairly simple, and the DBA can write the necessary SQL
queries to produce the information he needs. It also makes it easier to
parse the lock status information, if you're writing (for example) a
GUI admin tool.

Usage examples:

Basic information returned from function:

nconway=# select * from show_locks();
relation | database | backendpid | mode | isgranted
----------+----------+------------+-----------------+-----------
16575 | 16689 | 13091 | AccessShareLock | t
376 | 0 | 13091 | ExclusiveLock | t

After creating a simple relation and starting 2 transactions, one
of which has acquired the lock and one which is waiting on it:

nconway=# select l.backendpid, l.mode, l.isgranted from show_locks() l,
pg_class c where l.relation = c.oid and c.relname = 'a';

backendpid | mode | isgranted
------------+-----------------------+-----------
13098 | RowExclusiveLock | t
13108 | ShareRowExclusiveLock | f

During a 128 client pgbench run:

pgbench1=# select c.relname, count(l.isgranted) from show_locks() l,
pg_class c where c.oid = l.relation group by c.relname
order by count desc;
relname | count
---------------------+-------
accounts | 1081
tellers | 718
pg_xactlock | 337
branches | 208
history | 4
pg_class | 3
__show_locks_result | 1

And so on -- I think you get the idea.

Regarding performance, the only performance-critical aspect of the patch
is the place where we need to acquire the LockMgrLock, to ensure that
we get a consistent view of data from the lock manager's hash tables.
The patch is designed so that this lock is held for as short a period
as possible: the lock is acquired, the data is copied from shared memory
to local memory, the lock is released, and then the data is processed.
Any suggestions on how to optimize performance any further would be
welcome.

Let me know if there are any objections or suggestions for improvement.
In particular, should we provide some pre-defined views that correlate
the show_locks() data with data from the system catalogs? And if so,
which views should be pre-defined?

Also, should locks on special relations (e.g. pg_xactlock) or on
system catalogs be shown?

Cheers,

Neil

--
Neil Conway <neilconway(at)rogers(dot)com>
PGP Key ID: DB3C29FC