1<!-- doc/src/sgml/pgrowlocks.sgml -->
2
3<sect1 id="pgrowlocks" xreflabel="pgrowlocks">
4 <title>pgrowlocks</title>
5
6 <indexterm zone="pgrowlocks">
7  <primary>pgrowlocks</primary>
8 </indexterm>
9
10 <para>
11  The <filename>pgrowlocks</filename> module provides a function to show row
12  locking information for a specified table.
13 </para>
14
15 <para>
16  By default use is restricted to superusers, members of the
17  <literal>pg_stat_scan_tables</literal> role, and users with
18  <literal>SELECT</literal> permissions on the table.
19 </para>
20
21
22 <sect2>
23  <title>Overview</title>
24
25  <indexterm>
26   <primary>pgrowlocks</primary>
27  </indexterm>
28
29<synopsis>
30pgrowlocks(text) returns setof record
31</synopsis>
32
33  <para>
34   The parameter is the name of a table.  The result is a set of records,
35   with one row for each locked row within the table.  The output columns
36   are shown in <xref linkend="pgrowlocks-columns"/>.
37  </para>
38
39  <table id="pgrowlocks-columns">
40   <title><function>pgrowlocks</function> Output Columns</title>
41
42   <tgroup cols="3">
43    <thead>
44     <row>
45      <entry>Name</entry>
46      <entry>Type</entry>
47      <entry>Description</entry>
48     </row>
49    </thead>
50    <tbody>
51
52     <row>
53      <entry><structfield>locked_row</structfield></entry>
54      <entry><type>tid</type></entry>
55      <entry>Tuple ID (TID) of locked row</entry>
56     </row>
57     <row>
58      <entry><structfield>locker</structfield></entry>
59      <entry><type>xid</type></entry>
60      <entry>Transaction ID of locker, or multixact ID if multitransaction</entry>
61     </row>
62     <row>
63      <entry><structfield>multi</structfield></entry>
64      <entry><type>boolean</type></entry>
65      <entry>True if locker is a multitransaction</entry>
66     </row>
67     <row>
68      <entry><structfield>xids</structfield></entry>
69      <entry><type>xid[]</type></entry>
70      <entry>Transaction IDs of lockers (more than one if multitransaction)</entry>
71     </row>
72     <row>
73      <entry><structfield>modes</structfield></entry>
74      <entry><type>text[]</type></entry>
75      <entry>Lock mode of lockers (more than one if multitransaction),
76       an array of <literal>Key Share</literal>, <literal>Share</literal>,
77       <literal>For No Key Update</literal>, <literal>No Key Update</literal>,
78       <literal>For Update</literal>, <literal>Update</literal>.</entry>
79     </row>
80
81     <row>
82      <entry><structfield>pids</structfield></entry>
83      <entry><type>integer[]</type></entry>
84      <entry>Process IDs of locking backends (more than one if multitransaction)</entry>
85     </row>
86
87    </tbody>
88   </tgroup>
89  </table>
90
91  <para>
92   <function>pgrowlocks</function> takes <literal>AccessShareLock</literal> for the
93   target table and reads each row one by one to collect the row locking
94   information.  This is not very speedy for a large table.  Note that:
95  </para>
96
97  <orderedlist>
98   <listitem>
99    <para>
100     If an <literal>ACCESS EXCLUSIVE</literal> lock is taken on the table,
101     <function>pgrowlocks</function> will be blocked.
102    </para>
103   </listitem>
104   <listitem>
105    <para>
106     <function>pgrowlocks</function> is not guaranteed to produce a
107     self-consistent snapshot.  It is possible that a new row lock is taken,
108     or an old lock is freed, during its execution.
109    </para>
110   </listitem>
111  </orderedlist>
112
113  <para>
114   <function>pgrowlocks</function> does not show the contents of locked
115   rows. If you want to take a look at the row contents at the same time, you
116   could do something like this:
117
118<programlisting>
119SELECT * FROM accounts AS a, pgrowlocks('accounts') AS p
120  WHERE p.locked_row = a.ctid;
121</programlisting>
122
123   Be aware however that such a query will be very inefficient.
124  </para>
125 </sect2>
126
127 <sect2>
128  <title>Sample Output</title>
129
130  <screen>
131=# SELECT * FROM pgrowlocks('t1');
132 locked_row | locker | multi | xids  |     modes      |  pids
133------------+--------+-------+-------+----------------+--------
134 (0,1)      |    609 | f     | {609} | {"For Share"}  | {3161}
135 (0,2)      |    609 | f     | {609} | {"For Share"}  | {3161}
136 (0,3)      |    607 | f     | {607} | {"For Update"} | {3107}
137 (0,4)      |    607 | f     | {607} | {"For Update"} | {3107}
138(4 rows)
139</screen>
140 </sect2>
141
142 <sect2>
143  <title>Author</title>
144
145  <para>
146   Tatsuo Ishii
147  </para>
148 </sect2>
149
150</sect1>
151