Lock issue when trying to vacuum db

["Jess Balint" <jbalint(at)gmail(dot)com>]

Hi, I have a database that had a large table in it. I dropped the table, but
when I try to full vacuum the db, it just freezes indefinitely. There are
shared locks held on this that I can't identify. I've tried bouncing this
instance and ran some queries immediately after starting up. The results are
below. I've selected from pg_locks and pg_stat_activity when I started the
instance and then again after I started the vacuum command. Any advice would
be appreciated. Thanks a lot.

Jess

See query results below.

----------------------------------------------------------------------------
------------
--------------------------------------------> Queries after starting the
server before running vacuum
----------------------------------------------------------------------------
------------

scratch02=> select * from pg_locks ;
   locktype    | database | relation | page | tuple | transactionid |
classid | objid | objsubid | transaction | pid  |      mode       | granted
---------------+----------+----------+------+-------+---------------+-------
--+-------+----------+-------------+------+-----------------+---------
 relation      |    16389 |    16721 |      |       |               |
|       |          |        3969 |      | AccessShareLock | t
 relation      |    16389 |    16721 |      |       |               |
|       |          |        1620 |      | AccessShareLock | t
 transactionid |          |          |      |       |         70546 |
|       |          |       70546 | 9762 | ExclusiveLock   | t
 transactionid |          |          |      |       |          3969 |
|       |          |        3969 |      | ExclusiveLock   | t
 transactionid |          |          |      |       |          1620 |
|       |          |        1620 |      | ExclusiveLock   | t
 relation      |    16389 |    10342 |      |       |               |
|       |          |       70546 | 9762 | AccessShareLock | t
(6 rows)

scratch02=> select * from pg_Stat_activity;
 datid |  datname  | procpid | usesysid | usename | current_query |
query_start          |         backend_start         | client_addr |
client_port
-------+-----------+---------+----------+---------+---------------+---------
----------------------+-------------------------------+-------------+-------
------
 16389 | scratch02 |    9762 |    16384 | jbalint | <IDLE>        |
2005-12-19 18:24:52.900749-05 | 2005-12-19 18:24:16.901981-05 |
|          -1
(1 row)

----------------------------------------------------------------------------
------------
--------------------------------------------> Queries after starting
starting the vacuum
Notice the first lock isn't granted, which is keeping the vacuum from doing
anything
----------------------------------------------------------------------------
------------

scratch02=> select * from pg_locks ;
   locktype    | database | relation | page | tuple | transactionid |
classid | objid | objsubid | transaction | pid  |        mode         |
granted
---------------+----------+----------+------+-------+---------------+-------
--+-------+----------+-------------+------+---------------------+---------
 relation      |    16389 |    16721 |      |       |               |
|       |          |       70610 | 9764 | AccessExclusiveLock | f
 relation      |    16389 |    16721 |      |       |               |
|       |          |        3969 |      | AccessShareLock     | t
 relation      |    16389 |    16721 |      |       |               |
|       |          |        1620 |      | AccessShareLock     | t
 relation      |    16389 |    10342 |      |       |               |
|       |          |       70611 | 9762 | AccessShareLock     | t
 transactionid |          |          |      |       |          3969 |
|       |          |        3969 |      | ExclusiveLock       | t
 transactionid |          |          |      |       |          1620 |
|       |          |        1620 |      | ExclusiveLock       | t
 transactionid |          |          |      |       |         70611 |
|       |          |       70611 | 9762 | ExclusiveLock       | t
 transactionid |          |          |      |       |         70610 |
|       |          |       70610 | 9764 | ExclusiveLock       | t
(8 rows)

scratch02=> select * from pg_Stat_activity;
 datid |  datname  | procpid | usesysid | usename | current_query |
query_start          |         backend_start         | client_addr |
client_port
-------+-----------+---------+----------+---------+---------------+---------
----------------------+-------------------------------+-------------+-------
------
 16389 | scratch02 |    9764 |    16384 | jbalint | VACUUM full ; |
2005-12-19 18:25:24.748624-05 | 2005-12-19 18:25:14.743367-05 |
|          -1
 16389 | scratch02 |    9762 |    16384 | jbalint | <IDLE>        |
2005-12-19 18:25:32.011666-05 | 2005-12-19 18:24:16.901981-05 |
|          -1
(2 rows)