Hello, I have a production database which have not been full vacuumed for ~2 months (autovacuum worked fine with default settings all this time). When I run VACUUM FULL VERBOSE ANALYZE it processes several tables/indexes and than hangs (at least I tried to wait for 30 mins and nothing happened) on one particular table "education" which is quite ordinary I think. When it hangs I see in `ps auxww` process with "VACUUM waiting" in its status. There are no clients connected to the database except me in that moment since I switched application to another DB. pg_locks says that there are several AccessShareLocks and one RowExclusiveLock but no one related with "education" table (yes, also how is it possible to have several locks on unused database with only clean operations and several postmaster stop/starts in the past? how should I clean them if it is essential?). I tried dump/restore of this database to the new one -- it works perfectly now under production load and VACUUM FULL on it runs only 1 minute. I know that proper settings for autovacuum can eliminate need of VACUUM FULL but anyway it looks somewhat strange, doesn't it? PostgreSQL version is PostgreSQL 8.1.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-52) Below I pasted last lines concerning above table from VACUUM output (it stops after the last line): [...] INFO: vacuuming "public.education" INFO: "education": found 0 removable, 41764 nonremovable row versions in 674 pages DETAIL: 1111 dead row versions cannot be removed yet. Nonremovable row versions range from 64 to 1968 bytes long. There were 458 unused item pointers. Total free space (including removable row versions) is 38540 bytes. 0 pages are or will become empty, including 0 at the end of the table. 206 pages containing 25740 free bytes are potential move destinations. CPU 0.04s/0.00u sec elapsed 0.55 sec. INFO: index "pk_education" now contains 41764 row versions in 121 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.01s/0.00u sec elapsed 0.15 sec. INFO: "education": moved 0 row versions, truncated 674 to 674 pages DETAIL: CPU 0.00s/0.02u sec elapsed 0.01 sec. INFO: vacuuming "pg_toast.pg_toast_1519428" INFO: "pg_toast_1519428": found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 0 to 0 bytes long. There were 0 unused item pointers. Total free space (including removable row versions) is 0 bytes. 0 pages are or will become empty, including 0 at the end of the table. 0 pages containing 0 free bytes are potential move destinations. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "pg_toast_1519428_index" now contains 0 row versions in 1 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. 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 Hope that someone explains me how's that possible. Thanks, Ivan Zolotukhin
