Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Wed, Jan 31, 2018 at 7:37 PM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> I think the idea would not be an improvement, but just change the
> policy. The current launcher's policy is "let's launch a new worker as
> much as possible on the database that is at risk of wraparound most".
> The idea I suggested makes the cases mentioned on this thread better
> while perhaps making other cases worse.
>
> To improve while keeping the current policy, we might want to use the
> first idea I proposed. That is, we don't launch a new worker on a
> database impending wraparound if the last table of the database is
> being vacuumed. But it needs to share new information such as what
> tables exist in each database and which tables already have worker. It
> might be overkill in order to deal with only such a corner case
> though.

Something like that might work, but I think it needs more thought.
Maybe, for each database currently being processed by at least 1
worker, advertise in shared memory the oldest XID that isn't already
being vacuumed by some AV worker; when considering which database is
at greatest risk of wraparound, if that value is available, use it
instead of the database's datfrozenxid. Then when all tables that
make that database riskier than some other database already have
workers, some other database can get a chance.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company