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.
Detecting an in-doubt prepared transaction
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...