Detecting an in-doubt prepared transaction

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

How to do it...

You need to look up the pg_locks table for those entries with an empty pid value. Run this 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;

The output will be something similar to the following:

tablename | pid | granted

-----------+-------+---------
    db.x   |       | t
    db.x   | 27289 | f
(2 rows)

The preceding example shows a lock on the db.x...