Re: pg_attribute has way too many row versions

Michael Guerin <guerin(at)rentec(dot)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 would expect 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