pgrowlocks

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

By default, usage is restricted to superusers, members of the pg_stat_scan_tables role, and users with SELECT privilege on the table.

1. Overview

pgrowlocks(text) returns setof record

The argument is the name of a table. The result is a set of records, where each row corresponds to a locked row in the table. The output columns are shown in Table C.20.

Table C.20. pgrowlocks Output Columns

Name	Type	Description
locked_row	tid	Tuple ID (TID) of the locked row
locker	xid	Transaction ID of the locker, or multixact ID if it is a multi-transaction
multi	boolean	True if the locker is a multi-transaction
xids	xid[]	Transaction IDs of the lockers (more than one if it is a multi-transaction)
modes	text[]	Lock modes of the lockers (more than one if it is a multi-transaction), an array composed of Key Share, Share, For No Key Update, No Key Update, For Update, Update
pids	integer[]	Process IDs of the locking backends (more than one if it is a multi-transaction)

pgrowlocks takes an AccessShareLock on the target table and reads each row one by one to collect row locking information. This is not fast for large tables.

Note:

1. If the table is exclusively locked by someone else, pgrowlocks will be blocked.

2. pgrowlocks does not guarantee a self-consistent snapshot. During its execution, new row locks may be added, and old row locks may be released.

pgrowlocks does not display the content of the locked rows. If you want to view the row content at the same time, you can do:

SELECT * FROM accounts AS a, pgrowlocks('accounts') AS p

WHERE p.locked_row = a.ctid;

However, note that such a query will be very inefficient.

2. Sample Output

test=## SELECT * FROM pgrowlocks('t1');

locked_row | locker | multi | xids | modes | pids

------------+--------+-------+-------+----------------+--------

(0,1) | 609 | f | {609} | {"For Share"} | {3161}

(0,2) | 609 | f | {609} | {"For Share"} | {3161}

(0,3) | 607 | f | {607} | {"For Update"} | {3107}

(0,4) | 607 | f | {607} | {"For Update"} | {3107}

(4 rows)