Re: AutoVacuum and growing transaction XID's

On Thu, May 7, 2020 at 11:04 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> 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.
>
* How much value I can assign to lock_timeout so that I dont get into
trouble to test my DDL commands and without impacting other sessions.*

>
> > 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.
>
> How can I change the value of the global setting of the
autovacuum_freeze_max_Age value.

> David
>