Lock issue when trying to vacuum db - Mailing list pgsql-hackers

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 | trelation      |    16389 |    16721 |      |       |
       |
 
|       |          |        1620 |      | AccessShareLock | ttransactionid |          |          |      |       |
 70546 |
 
|       |          |       70546 | 9762 | ExclusiveLock   | ttransactionid |          |          |      |       |
  3969 |
 
|       |          |        3969 |      | ExclusiveLock   | ttransactionid |          |          |      |       |
  1620 |
 
|       |          |        1620 |      | ExclusiveLock   | trelation      |    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 | frelation      |    16389 |    16721 |      |       |
           |
 
|       |          |        3969 |      | AccessShareLock     | trelation      |    16389 |    16721 |      |       |
           |
 
|       |          |        1620 |      | AccessShareLock     | trelation      |    16389 |    10342 |      |       |
           |
 
|       |          |       70611 | 9762 | AccessShareLock     | ttransactionid |          |          |      |       |
      3969 |
 
|       |          |        3969 |      | ExclusiveLock       | ttransactionid |          |          |      |       |
      1620 |
 
|       |          |        1620 |      | ExclusiveLock       | ttransactionid |          |          |      |       |
     70611 |
 
|       |          |       70611 | 9762 | ExclusiveLock       | ttransactionid |          |          |      |       |
     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 |
|          -116389 | scratch02 |    9762 |    16384 | jbalint | <IDLE>        |
2005-12-19 18:25:32.011666-05 | 2005-12-19 18:24:16.901981-05 |
|          -1
(2 rows)