Re: [HACKERS] Autovacuum loose ends

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> On Fri, Jul 29, 2005 at 03:33:09PM -0400, Tom Lane wrote:
>> It occurs to me that vacuuming to prevent XID wraparound is not the only
>> reason to do DB-wide vacuums: we also need to keep
>> pg_database.datvacuumxid from getting too old, else we will have
>> problems with clog bloat. We may need to rethink the test used.

> I was unable to come up with a reasonable test for this. How would we
> determine what is "too old"?

Well, it depends what you think is too much space for pg_clog. If we
just follow the standard anti-wrap policy, we'll vacuum at least once
every half billion transactions, so pg_clog could be expected to grow
to about 125Mb, which maybe isn't a problem these days.

> OTOH I just saw this comment in createdb():

> /*
> * Normally we mark the new database with the same datvacuumxid and
> * datfrozenxid as the source. However, if the source is not allowing
> * connections then we assume it is fully frozen, and we can set the
> * current transaction ID as the xid limits. This avoids immediately
> * starting to generate warnings after cloning template0.
> */

> This means that if the user manages to unfreeze a database, disallow
> connections, and later use it as a template, we could suffer Xid-
> wraparound data loss in the new database. Should we rethink this?

I don't think so. Fooling with a template database is risky in any
case, and the fact that autovacuum might save your bacon (if you are
running autovacuum) doesn't make it less so.

BTW, it strikes me that there is one serious error in the current
autovac logic: it does VACUUM ANALYZE rather than merely VACUUM
when doing XID-wrap protection. This means that it actively introduces
unfrozen tuples into template databases, which is A Bad Move. We
should just VACUUM, instead.

> Sadly, the only interface for disallowing connections is to manually
> update pg_database,

As of now, we have a documented way of disallowing connections that
doesn't involve messing with datallowconn, so this argument seems a
lot weaker than it might have awhile back.

regards, tom lane