RE: Temporary tables prevent autovacuum, leading to XID wraparound

From: Masahiko Sawada [mailto:sawada(dot)mshk(at)gmail(dot)com]
> What I thought is that a worker reports these two values after scanned
> pg_class and after freezed a table. The launcher decides to launch a new
> worker if the number of tables requiring anti-wraparound vacuum is greater
> than the number of workers running on the database. Similarly, the
> autovacuum launcher doesn't launch a new worker if two values are equal,
> which means all tables requiring an anti-wraparound vacuum is being vacuumed.
> There are chances that new relation is added during a worker is running
> on the last one table that requires anti-wraparound vacuum and launcher
> launches a new worker on the database. I think it's no problem because the
> new worker would update that two values and exits soon.

I got it. Currently, the launcher assigns all workers to one database even if that database has only one table in danger of wraparound. With your suggestion, the launcher assigns as many workers as the tables to be frozen, and use remaining workers for the other databases.

> > How about just modifying do_start_worker(), so that the launcher chooses
> a database in the following order?
> >
> > 1. wraparound-risky database not being vacuumed by any worker 2.
> > non-wraparound-risky database not being vacuumed by any worker 3.
> > wraparound-risky database being vacuumed by any worker 4.
> > non-wraparound-risky database being vacuumed by any worker
> >
>
> IMO the limiting the number of worker on a database to 1 seems risky.
> If a database has many tables that require an anti-wraparound vacuum, it
> takes a long time to freeze the all of these tables. In current
> implementation, as I mentioned as above, launcher can launch multiple worker
> on the one database.

I can understand your concern. On the other hand, it's unfair that one database could monopolize all workers, because other databases might also be facing wraparound risk.

> Since the above idea would be complex a bit, as an
> alternated idea it might be better to specify the number of worker to launch
> per database by a new GUC parameter or something. If the number of worker
> running on the database exceeds that limit, the launcher doesn't select
> the database even if the database is about to wraparound.

I'm afraid the GUC would be difficult for the user to understand and tune.

I want to submit the patch for handling the garbage temporary table metadata as Robert suggested in the next CF. That should be enough to prevent this customer's problem. I would appreciate if anyone could address the other improvement that Sawada-san proposed.

Regards
Takayuki Tsunakawa