Re: Vacuum full hang

"Gaetano Mendola" <mendola(at)bigfoot(dot)com> writes:
> I'm trying to vacuum full a table ( Postgres 7.2.3 )
> but the command:
> vacuum full <my_table>
> hang and all processes that are trying to update that table hang too,
> the CPU occupation for the process that perform the
> vacuum is 0. What is going on?

Are you sure it's actually hung, and not busy doing the vacuum? Vacuum
is generally I/O bound not CPU bound, so I place little faith in using
the CPU idle time to decide that it's not doing anything.

If it *is* waiting, the reason is probably that some other process has a
lock on the table and is failing to commit its transaction and release
the lock. The vacuum will be queued up waiting to get exclusive lock
--- and everything else that might want to access the table will queue
up behind the vacuum.

In 7.3 you could look at the pg_locks view to confirm that idea, but
in 7.2 you can't do much more than look at ps to see if there are any
"idle in transaction" processes. Those would be the most likely
culprits to be sitting on locks.

regards, tom lane