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.
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 table, which has no process associated with it.
If you need to remove ...