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