Documentazione di PostgreSQL 9.0 > Appendici > Additional Supplied Modules > pgrowlocks
Precedentepg_freespacemappg_standbySuccessivo

F.27. pgrowlocks

The pgrowlocks module provides a function to show row locking information for a specified table.

F.27.1. Overview

pgrowlocks(text) returns setof record

The parameter is the name of a table. The result is a set of records, with one row for each locked row within the table. The output columns are shown in Tabella F.19, «pgrowlocks output columns».

Tabella F.19. pgrowlocks output columns

NameTypeDescription
locked_rowtidTuple ID (TID) of locked row
lock_typetextShared for shared lock, or Exclusive for exclusive lock
lockerxidTransaction ID of locker, or multixact ID if multi-transaction
multibooleanTrue if locker is a multi-transaction
xidsxid[]Transaction IDs of lockers (more than one if multi-transaction)
pidsinteger[]Process IDs of locking backends (more than one if multi-transaction)

pgrowlocks takes AccessShareLock for the target table and reads each row one by one to collect the row locking information. This is not very speedy for a large table. Note that:

  1. If the table as a whole is exclusive-locked by someone else, pgrowlocks will be blocked.

  2. pgrowlocks is not guaranteed to produce a self-consistent snapshot. It is possible that a new row lock is taken, or an old lock is freed, during its execution.

pgrowlocks does not show the contents of locked rows. If you want to take a look at the row contents at the same time, you could do something like this:

SELECT * FROM accounts AS a, pgrowlocks('accounts') AS p
  WHERE p.locked_row = a.ctid;

Be aware however that (as of PostgreSQL™ 8.3) such a query will be very inefficient.

F.27.2. Sample output

test=# SELECT * FROM pgrowlocks('t1');
 locked_row | lock_type | locker | multi |   xids    |     pids
------------+-----------+--------+-------+-----------+---------------
      (0,1) | Shared    |     19 | t     | {804,805} | {29066,29068}
      (0,2) | Shared    |     19 | t     | {804,805} | {29066,29068}
      (0,3) | Exclusive |    804 | f     | {804}     | {29066}
      (0,4) | Exclusive |    804 | f     | {804}     | {29066}
(4 rows)

F.27.3. Author

Tatsuo Ishii

Documentazione di PostgreSQL 9.0 > Appendici > Additional Supplied Modules > pgrowlocks
Precedentepg_freespacemappg_standbySuccessivo