Thread: Lock issue when trying to vacuum db
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)
"Jess Balint" <jbalint@gmail.com> writes:
> 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.
AFAIK, the only way for a lock to survive a database restart is a
prepared transaction. Are you running 8.1, and if so what does
pg_prepared_xacts show?
regards, tom lane
That was it. There were two in there. I rolled 'em back and everything is
smooth now. Thanks a lot.
Jess
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, December 19, 2005 10:03 PM
To: Jess Balint
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Lock issue when trying to vacuum db
"Jess Balint" <jbalint@gmail.com> writes:
> 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.
AFAIK, the only way for a lock to survive a database restart is a
prepared transaction. Are you running 8.1, and if so what does
pg_prepared_xacts show?
regards, tom lane