| PostgreSQL 8.3.23 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Appendix F. Additional Supplied Modules | Fast Forward | Next |
The pgrowlocks module provides a function to show row locking information for a specified table.
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:
Table F-25. pgrowlocks output columns
| Name | Type | Description |
|---|---|---|
| locked_row | tid | Tuple ID (TID) of locked row |
| lock_type | text | Shared for shared lock, or Exclusive for exclusive lock |
| locker | xid | Transaction ID of locker, or multixact ID if multi-transaction |
| multi | boolean | True if locker is a multi-transaction |
| xids | xid[] | Transaction IDs of lockers (more than one if multi-transaction) |
| pids | integer[] | 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:
If the table as a whole is exclusive-locked by someone
else, pgrowlocks will be
blocked.
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.
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)
Tatsuo Ishii