PG 8.2.3, FSM warning & VACUUM - Mailing list pgsql-general

Hello All,

Recently I experienced the strange PG behaviour with the large tables in
PG 8.2.3 which I didn't have in 8.1:

I have the large table: around 1 million records and around 400 columns.
When I try to run VACUUM ANALYZE on it, I'm getting the message:

WARNING:  relation "public.xx" contains more than "max_fsm_pages" pages
with useful free space
HINT:  Consider compacting this relation or increasing the configuration
parameter "max_fsm_pages".

Which was strange to me , because I never updated or deleted from that
table.
I tried to run VACUUM FULL -- but it didn't change anything ... The
plain VACUUM still was producing the warning about the FSM...

I even tried to recreate the table doing
CREATE new_table as SELECT * FROM old_table

But it didn't change anything ... Still the warning is produced by VACUUM.
Which is strange for me, because I thought that the recreation of table
should garantee the absence of the free space ...

Below I inserted the output of VACUUM FULL VERBOSE ANALYZE and
VACUUM FULL VERBOSE ANALYZE

The value of  max_fsm_pages is 204800

So. Is the observed behaviour expected (especially the warning after
executing VACUUM FULL and recreation of the table CREATE TABLE AS SELECT *
FROM ) ? Or is this a bug ?

BTW, it is strange that  VACUUM FULL doesn't produce the warning ... ,
while plain VACUUM does.

Thanks,
     Sergey

Output of VACUUM  and VACUUM FULL...

cas=# VACUUM FULL VERBOSE ANALYZE xx;
INFO:  vacuuming "public.xx"
INFO:  "xx": found 0 removable, 1647599 nonremovable row versions in
554353
pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 292 to 3092 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 268302952 bytes.
0 pages are or will become empty, including 0 at the end of the table.
327022 pages containing 224924296 free bytes are potential move
destinations.
CPU 2.51s/1.90u sec elapsed 4.42 sec.
INFO:  "xx": moved 0 row versions, truncated 554353 to 554353 pages
DETAIL:  CPU 0.00s/0.01u sec elapsed 0.01 sec.
INFO:  vacuuming "pg_toast.pg_toast_845776"
INFO:  "pg_toast_845776": 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_845776_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.xx"
INFO:  "xx": scanned 3000 of 554353 pages, containing 8918 live rows and 0
dead rows; 3000 rows in sample, 1647907 estimated total rows
VACUUM



cas=# VACUUM VERBOSE ANALYZE xx;
INFO:  vacuuming "public.xx"
INFO:  "xx": found 0 removable, 1647599 nonremovable row versions in
554353 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
364358 pages contain useful free space.
0 pages are entirely empty.
CPU 4.29s/2.32u sec elapsed 39.88 sec.
WARNING:  relation "public.xx" contains more than "max_fsm_pages" pages
with useful free space
HINT:  Consider compacting this relation or increasing the configuration
parameter "max_fsm_pages".
INFO:  vacuuming "pg_toast.pg_toast_845776"
INFO:  index "pg_toast_845776_index" now contains 0 row versions in 1
pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_845776": found 0 removable, 0 nonremovable row versions
in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.xx"
INFO:  "xx": scanned 3000 of 554353 pages, containing 8907 live rows and 0
dead rows; 3000 rows in sample, 1645874 estimated total rows
VACUUM


*******************************************************************
Sergey E. Koposov
Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: math@sai.msu.ru