Re: questions about wraparound

On Thu, 18 Mar 2021 09:56:16 +0100
Luca Ferrari <fluca1978(at)gmail(dot)com> wrote:
[...]
> Therefore my question is: shouldn't autovacuum be able to freeze other
> tables/databases? I mean, the wraparound problem in this scenario will
> cause problems, but I was expecting different numbers for different
> tables/databases.

In fact, when an autovacuum worker is spawned, here is how it chooses what
database to process:

1. look for any database needing a vacuum to prevent a wraparound.
2. same with multi-transaction
3. other autovacuum considerations

So as long as there's a database in desperate need for a vacuum to prevent a
wraparound, a worker will try to process it first, again and again.

Because of your long-running transaction, the xid horizon forbid to update the
rel/datfrozenxid. So next autovacuum round will keep trying to process the same
database, ignoring others.

Look at the comment in function "do_stat_worker()" in autovacuum.c for more
details:
https://git.postgresql.org/cgit/postgresql.git/tree/src/backend/postmaster/autovacuum.c#n1207

When looping over the database list, as soon as "for_xid_wrap" is true, any
other database is ignored. Then a new worker is popped from the freeWorkers,
init'ed with the database to freeze and started. So as far as I understand the
code (I might easily be wrong), all the workers will keep trying to process the
same database again and again without considering other ones. All because of
your really-long living xact.

Regards,