Re: VACUUM FULL hangs on ordinary table

On 5/19/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Ivan Zolotukhin" <ivan(dot)zolotukhin(at)gmail(dot)com> writes:
> > quite ordinary I think. When it hangs I see in `ps auxww` process with
> > "VACUUM waiting" in its status.
>
> It's definitely waiting for a lock then.

Yep, I checked that it waits for acquiring AccessExclusiveLock on the
next table to vacuum after it finished "education" table.

>
> > Below I pasted last lines concerning above table from VACUUM output
> > (it stops after the last line):
> > ...
> > INFO: analyzing "public.education"
> > INFO: "education": scanned 674 of 674 pages, containing 40653 live
> > rows and 1111 dead rows; 3000 rows in sample, 40653 estimated total
> > rows
>
> If it hangs there then I'd venture that it's trying to get writer's
> lock (RowExclusiveLock) on pg_statistic so it can store the new
> statistic rows. Or possibly pg_class. You should be looking for locks
> on the system catalogs not locks on "education" itself.

Actually, Joachim was closer to the truth: there was one not committed
prepared transaction started several days ago (client disconnected at
the beginning) that holded one RowExclusiveLock and 12
AccessShareLocks on several relations in that DB (that obviously
interfered with vacuum trying to acquire AccessExclusiveLock on one of
them). After ROLLBACK PREPARED vacuum works fine so thanks to your
advices -- they helped me to solve the problem.

Regards,
Ivan Zolotukhin