Detecting an in-doubt prepared transaction

While using a two-phase commit (2PC), you may end up in a situation where you have something locked but cannot find the backend that holds the locks. This recipe describes how to detect such a case.

How to do it…

Perform the following steps:

1. You need to look up the pg_locks table for those entries with an empty pid value. Run the following query:

   SELECT t.schemaname || '.' || t.relname AS tablename,
          l.pid, l.granted
          FROM pg_locks l JOIN pg_stat_user_tables t
          ON l.relation = t.relid;

2. The output will be something similar to the following:

    tablename |  pid  | granted
   -----------+-------+---------
       db.x   |       | t
       db.x...