Create a function to reliably identify which sessions block which others.

This patch introduces "pg_blocking_pids(int) returns int[]", which returns
the PIDs of any sessions that are blocking the session with the given PID.
Historically people have obtained such information using a self-join on
the pg_locks view, but it's unreasonably tedious to do it that way with any
modicum of correctness, and the addition of parallel queries has pretty
much broken that approach altogether.  (Given some more columns in the view
than there are today, you could imagine handling parallel-query cases with
a 4-way join; but ugh.)

The new function has the following behaviors that are painful or impossible
to get right via pg_locks:

1. Correctly understands which lock modes block which other ones.

2. In soft-block situations (two processes both waiting for conflicting lock
modes), only the one that's in front in the wait queue is reported to
block the other.

3. In parallel-query cases, reports all sessions blocking any member of
the given PID's lock group, and reports a session by naming its leader
process's PID, which will be the pg_backend_pid() value visible to
clients.

The motivation for doing this right now is mostly to fix the isolation
tests.  Commit 38f8bdcac4982215beb9f65a19debecaf22fd470 lobotomized
isolationtester's is-it-waiting query by removing its ability to recognize
nonconflicting lock modes, as a crude workaround for the inability to
handle soft-block situations properly.  But even without the lock mode
tests, the old query was excessively slow, particularly in
CLOBBER_CACHE_ALWAYS builds; some of our buildfarm animals fail the new
deadlock-hard test because the deadlock timeout elapses before they can
probe the waiting status of all eight sessions.  Replacing the pg_locks
self-join with use of pg_blocking_pids() is not only much more correct, but
a lot faster: I measure it at about 9X faster in a typical dev build with
Asserts, and 3X faster in CLOBBER_CACHE_ALWAYS builds.  That should provide
enough headroom for the slower CLOBBER_CACHE_ALWAYS animals to pass the
test, without having to lengthen deadlock_timeout yet more and thus slow
down the test for everyone else.

2 files changed, 64 insertions, 21 deletions

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index d77e99988ff..951f59b76c1 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -7376,7 +7376,7 @@
 
      <row>
       <entry><link linkend="view-pg-locks"><structname>pg_locks</structname></link></entry>
-      <entry>currently held locks</entry>
+      <entry>locks currently held or awaited</entry>
      </row>
 
      <row>
@@ -8015,16 +8015,16 @@
 
   <para>
    The view <structname>pg_locks</structname> provides access to
-   information about the locks held by open transactions within the
+   information about the locks held by active processes within the
    database server.  See <xref linkend="mvcc"> for more discussion
    of locking.
   </para>
 
   <para>
    <structname>pg_locks</structname> contains one row per active lockable
-   object, requested lock mode, and relevant transaction.  Thus, the same
+   object, requested lock mode, and relevant process.  Thus, the same
    lockable object might
-   appear many times, if multiple transactions are holding or waiting
+   appear many times, if multiple processes are holding or waiting
    for locks on it.  However, an object that currently has no locks on it
    will not appear at all.
   </para>
@@ -8200,31 +8200,31 @@
 
   <para>
    <structfield>granted</structfield> is true in a row representing a lock
-   held by the indicated transaction.  False indicates that this transaction is
-   currently waiting to acquire this lock, which implies that some other
-   transaction is holding a conflicting lock mode on the same lockable object.
-   The waiting transaction will sleep until the other lock is released (or a
-   deadlock situation is detected). A single transaction can be waiting to
-   acquire at most one lock at a time.
+   held by the indicated process.  False indicates that this process is
+   currently waiting to acquire this lock, which implies that at least one
+   other process is holding or waiting for a conflicting lock mode on the same
+   lockable object.  The waiting process will sleep until the other lock is
+   released (or a deadlock situation is detected).  A single process can be
+   waiting to acquire at most one lock at a time.
   </para>
 
   <para>
-   Every transaction holds an exclusive lock on its virtual transaction ID for
-   its entire duration.  If a permanent ID is assigned to the transaction
-   (which normally happens only if the transaction changes the state of the
-   database), it also holds an exclusive lock on its permanent transaction ID
-   until it ends.  When one transaction finds it necessary to wait specifically
-   for another transaction, it does so by attempting to acquire share lock on
-   the other transaction ID (either virtual or permanent ID depending on the
-   situation). That will succeed only when the other transaction
-   terminates and releases its locks.
+   Throughout running a transaction, a server process holds an exclusive lock
+   on the transaction's virtual transaction ID.  If a permanent ID is assigned
+   to the transaction (which normally happens only if the transaction changes
+   the state of the database), it also holds an exclusive lock on the
+   transaction's permanent transaction ID until it ends.  When a process finds
+   it necessary to wait specifically for another transaction to end, it does
+   so by attempting to acquire share lock on the other transaction's ID
+   (either virtual or permanent ID depending on the situation). That will
+   succeed only when the other transaction terminates and releases its locks.
   </para>
 
   <para>
    Although tuples are a lockable type of object,
    information about row-level locks is stored on disk, not in memory,
    and therefore row-level locks normally do not appear in this view.
-   If a transaction is waiting for a
+   If a process is waiting for a
    row-level lock, it will usually appear in the view as waiting for the
    permanent transaction ID of the current holder of that row lock.
   </para>
@@ -8260,7 +8260,7 @@
    <structfield>pid</structfield> column of the <link
    linkend="pg-stat-activity-view"><structname>pg_stat_activity</structname></link>
    view to get more
-   information on the session holding or waiting to hold each lock,
+   information on the session holding or awaiting each lock,
    for example
 <programlisting>
 SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa
@@ -8281,6 +8281,20 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
   </para>
 
   <para>
+   While it is possible to obtain information about which processes block
+   which other processes by joining <structname>pg_locks</structname> against
+   itself, this is very difficult to get right in detail.  Such a query would
+   have to encode knowledge about which lock modes conflict with which
+   others.  Worse, the <structname>pg_locks</structname> view does not expose
+   information about which processes are ahead of which others in lock wait
+   queues, nor information about which processes are parallel workers running
+   on behalf of which other client sessions.  It is better to use
+   the <function>pg_blocking_pids()</> function
+   (see <xref linkend="functions-info-session-table">) to identify which
+   process(es) a waiting process is blocked behind.
+  </para>
+
+  <para>
    The <structname>pg_locks</structname> view displays data from both the
    regular lock manager and the predicate lock manager, which are
    separate systems; in addition, the regular lock manager subdivides its
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index b001ce548d8..c0b94bc0728 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14997,6 +14997,12 @@ SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
       </row>
 
       <row>
+       <entry><literal><function>pg_blocking_pids(<type>int</type>)</function></literal></entry>
+       <entry><type>int[]</type></entry>
+       <entry>Process ID(s) that are blocking specified server process ID</entry>
+      </row>
+
+      <row>
        <entry><literal><function>pg_conf_load_time()</function></literal></entry>
        <entry><type>timestamp with time zone</type></entry>
        <entry>configuration load time</entry>
@@ -15184,6 +15190,29 @@ SET search_path TO <replaceable>schema</> <optional>, <replaceable>schema</>, ..
    </para>
 
    <indexterm>
+    <primary>pg_blocking_pids</primary>
+   </indexterm>
+
+   <para>
+    <function>pg_blocking_pids</function> returns an array of the process IDs
+    of the sessions that are blocking the server process with the specified
+    process ID, or an empty array if there is no such server process or it is
+    not blocked.  One server process blocks another if it either holds a lock
+    that conflicts with the blocked process's lock request (hard block), or is
+    waiting for a lock that would conflict with the blocked process's lock
+    request and is ahead of it in the wait queue (soft block).  When using
+    parallel queries the result always lists client-visible process IDs (that
+    is, <function>pg_backend_pid</> results) even if the actual lock is held
+    or awaited by a child worker process.  As a result of that, there may be
+    duplicated PIDs in the result.  Also note that when a prepared transaction
+    holds a conflicting lock, it will be represented by a zero process ID in
+    the result of this function.
+    Frequent calls to this function could have some impact on database
+    performance, because it needs exclusive access to the lock manager's
+    shared state for a short time.
+   </para>
+
+   <indexterm>
     <primary>pg_conf_load_time</primary>
    </indexterm>