Re: AutoVacuum and growing transaction XID's

On Fri, 8 May 2020 at 13:51, github kran <githubkran(at)gmail(dot)com> wrote:
> I can't either DROP or ALTER any other tables ( REMOVE Inheritance for any of old tables where the WRITES are not getting written to). Any of the ALTER TABLE OR DROP TABLE DDL's arer not getting exeucted even I WAITED FOR SEVERAL MINUTES , so I have terminated those queries as I didn't have luck.

The auto-vacuum freeze holds an SharedUpdateExclusiveLock on the table
being vacuumed. If you try any DDL that requires an
AccessExclusiveLock, it'll have to wait until the vacuum has
completed. If you leave the DDL running then all accesses to the table
will be queued behind the ungranted AccessExclusiveLock. It's likely
a good idea to always run DDL with a fairly short lock_timeout, just
in case this happens.

> 3) Can I increase the autovacuum_freeze_max_age on the tables on production system ?

Yes, but you cannot increase the per-table setting above the global
setting. Changing the global setting requires a restart.

David