Shawn wrote: > The problem is though, when I look at age(datfrozenxid) for > "my_database_name" it is the oldest at 654,189,546 and should trigger the > autovacuum_freeze_max_age of 200,000,000 but it doesn't. The relation with > this age is "pg_database". If I issue a manual "vacuum freeze pg_database" > in the database, autovacuum will run on the next oldest database but get > stuck again with same symptoms (i.e. pg_database is the relation with the > oldest age). If I don't issue the manual "vacuum freeze" for all databases > and left things alone, it would get to wrap-around state.
I was thinking about this in connection with the shared catalog vacuuming bug I fixed yesterday. One problem here is that the relfrozenxid/relminmxid settings are local to each database, so a for-wraparound vacuum would have to scan the table in all databases, and advance the counter in all databases, before the overall "age" of the system goes down again, which is inconvenient. This is a problem inherent to having these columns in a per-database catalog, and the only solution I can see is to have another shared catalog containing age state for the shared catalogs. (However, this issue isn't terribly serious -- it just means you have to scan the catalog N times instead of one.) However this doesn't explain why the vacuuming of pg_database in one database doesn't actually advance the relfrozenxid in that database; it certainly should. This is the serious problem you're facing, it seems. I wonder if you've tweaked the freeze settings somehow. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
