I was trying to drop a schema, but it was hanging. My whats running script didn’t help too much:

$ db_running.sh
   datname   | procpid | usename  |           sql            |       age       | query_ip | locktype |      mode       | hold_sql | blocker_pid | blocker_ip
-------------+---------+----------+--------------------------+-----------------+----------+----------+-----------------+----------+-------------+------------
 mydb        |   10371 | postgres | drop schema old cascade; | 00:05:17.994846 |          | relation | AccessShareLock |          |             |

So something is blocking the drop, but there are no details as to what who it is. Looking at the pg_locks rows of interest:

mydb=# select * from pg_locks where relation=437506;
 locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |        mode         | granted
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------
 relation |    16384 |   437506 |      |       |            |               |         |       |          | 2/5                | 10371 | AccessExclusiveLock | f
 relation |    16384 |   437506 |      |       |            |               |         |       |          | -1/30795916        |       | AccessShareLock     | t
(2 rows)

Whatever is blocking my session has no PID or transaction. From past experience, I know that weird locking behavior like this is usually due to prepared transactions (gaah I hate them!). Sure enough:

postgres=# select * from pg_prepared_xacts ;
 transaction |                                                                    gid                                                                    |           prepared            |  owner   |  database
-------------+-------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------+----------+-------------
    30795916 | 4871251_a5kEAHHYB2tlcHBsaW4wMS1nZmgscnBwLXNpdGVhLW5vZGUwMS1pbnN0MDEsUDMzNzAw_ZXBwbGluMDEtZ2ZoLHJwcC1zaXRlYS1ub2RlMDEtaW5zdDAxLFAzMzcwMCwB | 2011-07-27 23:56:24.371114+10 | postgres | mydb
(1 row)

It’s a few days old and the owning process is dead. Let’s remove it:

postgres=# \c mydb
You are now connected to database "mydb".
mydb=# rollback prepared '4871251_a5kEAHHYB2tlcHBsaW4wMS1nZmgscnBwLXNpdGVhLW5vZGUwMS1pbnN0MDEsUDMzNzAw_ZXBwbGluMDEtZ2ZoLHJwcC1zaXRlYS1ub2RlMDEtaW5zdDAxLFAzMzcwMCwB';
ROLLBACK PREPARED

And my blocked session immediate resumed and completed the drop.