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 :-(

Yes, but I couldn't find any other way.

> 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.

If I understand correctly, it seems the above method does show a
locked row's TID which does not block someone else. That is a little
bit different from what I expcted.

> > 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.
--
Tatsuo Ishii