O'Reilly logo

PostgreSQL 9 Administration Cookbook - Second Edition by Simon Riggs, Gianni Ciolli, Hannu Krosing, Gabriele Bartolini

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 table, which has no process associated with it.

If you need to remove ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required