Julie Nishimura <juliezain@hotmail.com> writes:
> Thank you Tom. I can see bunch of old processes running ROLLBACK... Should I kill them or they only way to clear
thoseis to restart the server?
As Ron pointed out, those processes aren't actually running ROLLBACK;
that was just their last query. They're idle (and not "idle in
transaction") so in theory they're not holding any locks.
After thinking more about it I realize that the process holding the
buffer pin doesn't have to be as old as the VACUUM; so you probably
should just look at pg_locks and see what else has got a lock on
the table the VACUUM is trying to process. The hypothetical offender
is probably somewhat old, or you'd not have noticed the blockage,
but we don't have any clear evidence as to how old.
regards, tom lane