On Thu, 18 Mar 2021 09:56:16 +0100 Luca Ferrari <fluca1978@xxxxxxxxx> 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,
