doc/src/sgml/pgrowlocks.sgml

   1 <!-- doc/src/sgml/pgrowlocks.sgml -->
   2
   3 <sect1 id="pgrowlocks" xreflabel="pgrowlocks">
   4  <title>pgrowlocks &mdash; show a table's row locking information</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, roles with privileges 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 id="pgrowlocks-overview">
  23   <title>Overview</title>
  24
  25   <indexterm>
  26    <primary>pgrowlocks</primary>
  27   </indexterm>
  28
  29 <synopsis>
  30 pgrowlocks(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
  61       multitransaction;  see <xref linkend="transaction-id"/></entry>
  62      </row>
  63      <row>
  64       <entry><structfield>multi</structfield></entry>
  65       <entry><type>boolean</type></entry>
  66       <entry>True if locker is a multitransaction</entry>
  67      </row>
  68      <row>
  69       <entry><structfield>xids</structfield></entry>
  70       <entry><type>xid[]</type></entry>
  71       <entry>Transaction IDs of lockers (more than one if multitransaction)</entry>
  72      </row>
  73      <row>
  74       <entry><structfield>modes</structfield></entry>
  75       <entry><type>text[]</type></entry>
  76       <entry>Lock mode of lockers (more than one if multitransaction),
  77        an array of <literal>For Key Share</literal>, <literal>For Share</literal>,
  78        <literal>For No Key Update</literal>, <literal>No Key Update</literal>,
  79        <literal>For Update</literal>, <literal>Update</literal>.</entry>
  80      </row>
  81
  82      <row>
  83       <entry><structfield>pids</structfield></entry>
  84       <entry><type>integer[]</type></entry>
  85       <entry>Process IDs of locking backends (more than one if multitransaction)</entry>
  86      </row>
  87
  88     </tbody>
  89    </tgroup>
  90   </table>
  91
  92   <para>
  93    <function>pgrowlocks</function> takes <literal>AccessShareLock</literal> for the
  94    target table and reads each row one by one to collect the row locking
  95    information.  This is not very speedy for a large table.  Note that:
  96   </para>
  97
  98   <orderedlist>
  99    <listitem>
 100     <para>
 101      If an <literal>ACCESS EXCLUSIVE</literal> lock is taken on the table,
 102      <function>pgrowlocks</function> will be blocked.
 103     </para>
 104    </listitem>
 105    <listitem>
 106     <para>
 107      <function>pgrowlocks</function> is not guaranteed to produce a
 108      self-consistent snapshot.  It is possible that a new row lock is taken,
 109      or an old lock is freed, during its execution.
 110     </para>
 111    </listitem>
 112   </orderedlist>
 113
 114   <para>
 115    <function>pgrowlocks</function> does not show the contents of locked
 116    rows. If you want to take a look at the row contents at the same time, you
 117    could do something like this:
 118
 119 <programlisting>
 120 SELECT * FROM accounts AS a, pgrowlocks('accounts') AS p
 121   WHERE p.locked_row = a.ctid;
 122 </programlisting>
 123
 124    Be aware however that such a query will be very inefficient.
 125   </para>
 126  </sect2>
 127
 128  <sect2 id="pgrowlocks-sample-output">
 129   <title>Sample Output</title>
 130
 131   <screen>
 132 =# SELECT * FROM pgrowlocks('t1');
 133  locked_row | locker | multi | xids  |     modes      |  pids
 134 ------------+--------+-------+-------+----------------+--------
 135  (0,1)      |    609 | f     | {609} | {"For Share"}  | {3161}
 136  (0,2)      |    609 | f     | {609} | {"For Share"}  | {3161}
 137  (0,3)      |    607 | f     | {607} | {"For Update"} | {3107}
 138  (0,4)      |    607 | f     | {607} | {"For Update"} | {3107}
 139 (4 rows)
 140 </screen>
 141  </sect2>
 142
 143  <sect2 id="pgrowlocks-author">
 144   <title>Author</title>
 145
 146   <para>
 147    Tatsuo Ishii
 148   </para>
 149  </sect2>
 150
 151 </sect1>