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)
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
