Re: obtaining row locking information

Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp> writes:
> With a help from Bruce, I wrote a small function which returns row
> locking information(see attached file if you are interested).

Scanning the whole table seems a bit slow :-(

There is another possibility: in CVS tip, anyone who is actually blocked
on a row lock will be holding a tuple lock that shows exactly what they
are waiting for. For example:

Session 1:

regression=# begin;
BEGIN
regression=# select * from int4_tbl where f1 = 123456 for update;
f1
--------
123456
(1 row)

Session 2:

<< same as above, leaving session 2 blocked >

Session 1:

regression=# select * from pg_locks;
locktype | database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction | pid | mode | granted
---------------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+------+-----------------+---------
transactionid | | | | | 14575 | | | | 14576 | 2501 | ShareLock | f
tuple | 48344 | 48369 | 0 | 2 | | | | | 14576 | 2501 | ExclusiveLock | t
relation | 48344 | 48369 | | | | | | | 14576 | 2501 | AccessShareLock | t
relation | 48344 | 48369 | | | | | | | 14576 | 2501 | RowShareLock | t
transactionid | | | | | 14576 | | | | 14576 | 2501 | ExclusiveLock | t
relation | 48344 | 10339 | | | | | | | 14575 | 2503 | AccessShareLock | t
relation | 48344 | 48369 | | | | | | | 14575 | 2503 | AccessShareLock | t
relation | 48344 | 48369 | | | | | | | 14575 | 2503 | RowShareLock | t
transactionid | | | | | 14575 | | | | 14575 | 2503 | ExclusiveLock | t
(9 rows)

Session 2 (XID 14576) is blocked on session 1 (XID 14575) according to
the first row of this output. The second row shows the exact tuple
that it is after.

This isn't an amazingly user-friendly way of displaying things, of
course, but maybe somebody could make a function that would show it
better using pg_locks as input.

> I think it will be more usefull if actual xids are shown in the case
> "locker" is a multixid. It seems GetMultiXactIdMembers() does the
> job. Unfortunately that is a static funtcion, however. Is there any
> chance GetMultiXactIdMembers() becomes public funtion?

No particular objection here.

regards, tom lane