System catalog vacuum issues - Mailing list pgsql-hackers

Hello,

We are suffering from a long-standing issue with autovacuuming/vacuuming 
system catalogs on the production server. We are actively using 
temporary tables in the legacy application, so system catalogs grows 
unbounded in time. Autovacuum does not remove dead tuples and neither do 
the manual vacuum. We are running PostgreSQL 9.2.4 on Linux 2.6.18 x86_64.

Nobody's holding an open transaction for long periods.

dcdb=# select xact_start, query_start, state, query from 
pg_stat_activity where state <> 'idle';          xact_start           |          query_start          | state  
| query

-------------------------------+-------------------------------+--------+-------------------------------------------------------------------------------------------
2013-08-0614:46:56.303261+09 | 2013-08-06 14:46:56.303261+09 | active 
 
| select xact_start, query_start, state, query from pg_stat_activity 
where state <> 'idle';
(1 row)

dcdb=# select count(*) from pg_attribute; count
------- 51279
(1 row)

dcdb=# select relname, n_live_tup, n_dead_tup, last_vacuum, 
last_autovacuum from pg_stat_sys_tables where relname = 'pg_attribute';   relname    | n_live_tup | n_dead_tup |
last_vacuum         |        
 
last_autovacuum
--------------+------------+------------+-------------------------------+------------------------------- pg_attribute |
 39318143 |     427798 | 2013-08-06 14:46:09.323187+09 
 
| 2013-08-06 13:43:03.162286+09
(1 row)

dcdb=# vacuum analyze verbose pg_attribute;
INFO:  vacuuming "pg_catalog.pg_attribute"
INFO:  index "pg_attribute_relid_attnam_index" now contains 492122 row 
versions in 166671 pages
DETAIL:  0 index row versions were removed.
163952 index pages have been deleted, 162834 are currently reusable.
CPU 0.69s/0.21u sec elapsed 0.94 sec.
INFO:  index "pg_attribute_relid_attnum_index" now contains 492253 row 
versions in 118119 pages
DETAIL:  0 index row versions were removed.
116071 index pages have been deleted, 115269 are currently reusable.
CPU 0.51s/0.14u sec elapsed 0.67 sec.
INFO:  "pg_attribute": found 0 removable, 460354 nonremovable row 
versions in 9570 out of 776848 pages
DETAIL:  440706 dead row versions cannot be removed yet.
There were 1788424 unused item pointers.
0 pages are entirely empty.
CPU 1.28s/0.43u sec elapsed 1.75 sec.
INFO:  analyzing "pg_catalog.pg_attribute"
INFO:  "pg_attribute": scanned 30000 of 776848 pages, containing 1918 
live rows and 15226 dead rows; 1878 rows in sample, 39318086 estimated 
total rows
VACUUM

dcdb=# select relname, n_live_tup, n_dead_tup, last_vacuum, 
last_autovacuum from pg_stat_sys_tables where relname = 'pg_attribute';   relname    | n_live_tup | n_dead_tup |
last_vacuum         |        
 
last_autovacuum
--------------+------------+------------+-------------------------------+------------------------------- pg_attribute |
 39318086 |     395478 | 2013-08-06 14:47:48.187259+09 
 
| 2013-08-06 13:43:03.162286+09
(1 row)