Thread: pg_attribute has way too many row versions

pg_attribute has way too many row versions, how can I find out what's holding a reference to these row versions?  I
wouldexpect at most 4-5K row versions in this table. 

INFO:  vacuuming "pg_catalog.pg_attribute"
INFO:  index "pg_attribute_relid_attnam_index" now contains 15089905 row versions in 513390 pages
DETAIL:  724892 index row versions were removed.
29132 index pages have been deleted, 0 are currently reusable.
CPU 16.49s/8.57u sec elapsed 3799.71 sec.
INFO:  index "pg_attribute_relid_attnum_index" now contains 15112894 row versions in 131544 pages
DETAIL:  724892 index row versions were removed.
200 index pages have been deleted, 0 are currently reusable.
CPU 4.03s/5.94u sec elapsed 964.61 sec.
INFO:  "pg_attribute": removed 724892 row versions in 67005 pages
DETAIL:  CPU 2.52s/3.30u sec elapsed 4136.68 sec.
INFO:  "pg_attribute": found 724892 removable, 15000823 nonremovable row versions in 268172 pages

*DETAIL:  14998918 dead row versions cannot be removed yet.
*
There were 316065 unused item pointers.
0 pages are entirely empty.
CPU 27.13s/19.97u sec elapsed 9394.54 sec.
INFO:  analyzing "pg_catalog.pg_attribute"
INFO:  "pg_attribute": 268172 pages, 1877 rows sampled, 1877 estimated total rows

Michael Guerin <guerin@rentec.com> writes:
> pg_attribute has way too many row versions, how can I find out what's holding a reference to these row versions?  I
wouldexpect at most 4-5K row versions in this table. 
> INFO:  vacuuming "pg_catalog.pg_attribute"
> ...
> INFO:  "pg_attribute": removed 724892 row versions in 67005 pages
> DETAIL:  CPU 2.52s/3.30u sec elapsed 4136.68 sec.
> INFO:  "pg_attribute": found 724892 removable, 15000823 nonremovable row versions in 268172 pages

> *DETAIL:  14998918 dead row versions cannot be removed yet.

Whoa, that last is a bit large.  I take it your applications create and
delete temp tables at a furious rate?

Anyway, there is not anything "holding a reference" to any one of those
dead rows specifically.  Your problem is an open transaction someplace.
VACUUM is afraid to remove those rows because it thinks they might still
be visible to the oldest open transaction.  You've probably got a client
app somewhere that did a BEGIN and then went to sleep indefinitely.
Find it, kill it, and then do a VACUUM FULL on pg_attribute (pg_class
probably needs it too).  You might need to REINDEX these two tables as
well to get their indexes back down to a reasonable size.

            regards, tom lane