O'Reilly logo

PostgreSQL 9 Administration Cookbook by Hannu Krosing, Simon Riggs

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

Resolving an in-doubt prepared transaction

When using 2PC (two phase commit), you may end up in a situation where you kind of have something locked, but cannot find a backend that holds the locks.

For example:

db=# select t.schemaname || '.' || t.relname as tablename,
db-# l.pid, l.granted
db-# from pg_locks l join pg_stat_user_tables t
db-# on l.relation = t.relid;
tablename | pid | granted
-----------+-------+---------
db.x | | t
db.x | 27289 | f
(2 rows)

has a lock on table db.x, which has no process associated with it.

Getting ready

Look at the recipe on Removing old prepared transactions in Regular Maintenance

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