Re: Should vacuum process config file reload more often

On Thu, Apr 27, 2023 at 6:30 PM John Naylor
<john.naylor@enterprisedb.com> wrote:
>
>
> On Fri, Apr 7, 2023 at 6:08 AM Daniel Gustafsson <daniel@yesql.se> wrote:
> >
> > I had another read-through and test-through of this version, and have applied
> > it with some minor changes to comments and whitespace.  Thanks for the quick
> > turnaround times on reviews in this thread!
>
> -       VacuumFailsafeActive = false;
> +       Assert(!VacuumFailsafeActive);
>
> I can trigger this assert added in commit 7d71d3dd08.
>
> First build with the patch in [1], then:
>
> session 1:
>
> CREATE EXTENSION xid_wraparound ;
>
> CREATE TABLE autovacuum_disabled(id serial primary key, data text) WITH (autovacuum_enabled=false);
> INSERT INTO autovacuum_disabled(data) SELECT generate_series(1,1000);
>
> -- I can trigger without this, but just make sure it doesn't get vacuumed
> BEGIN;
> DELETE FROM autovacuum_disabled WHERE id % 2 = 0;
>
> session 2:
>
> -- get to failsafe limit
> SELECT consume_xids(1*1000*1000*1000);
> INSERT INTO autovacuum_disabled(data) SELECT 1;
> SELECT consume_xids(1*1000*1000*1000);
> INSERT INTO autovacuum_disabled(data) SELECT 1;
>
> VACUUM autovacuum_disabled;
>
> WARNING:  cutoff for removing and freezing tuples is far in the past
> HINT:  Close open transactions soon to avoid wraparound problems.
> You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
> WARNING:  bypassing nonessential maintenance of table "john.public.autovacuum_disabled" as a failsafe after 0 index
scans
> DETAIL:  The table's relfrozenxid or relminmxid is too far in the past.
> HINT:  Consider increasing configuration parameter "maintenance_work_mem" or "autovacuum_work_mem".
> You might also need to consider other ways for VACUUM to keep up with the allocation of transaction IDs.
> server closed the connection unexpectedly
>
> #0  0x00007ff31f68ebec in __pthread_kill_implementation ()
>    from /lib64/libc.so.6
> #1  0x00007ff31f63e956 in raise () from /lib64/libc.so.6
> #2  0x00007ff31f6287f4 in abort () from /lib64/libc.so.6
> #3  0x0000000000978032 in ExceptionalCondition (
>     conditionName=conditionName@entry=0xa4e970 "!VacuumFailsafeActive",
>     fileName=fileName@entry=0xa4da38 "../src/backend/access/heap/vacuumlazy.c", lineNumber=lineNumber@entry=392) at
../src/backend/utils/error/assert.c:66
> #4  0x000000000058c598 in heap_vacuum_rel (rel=0x7ff31d8a97d0,
>     params=<optimized out>, bstrategy=<optimized out>)
>     at ../src/backend/access/heap/vacuumlazy.c:392
> #5  0x000000000069af1f in table_relation_vacuum (bstrategy=0x14ddca8,
>     params=0x7ffec28585f0, rel=0x7ff31d8a97d0)
>     at ../src/include/access/tableam.h:1705
> #6  vacuum_rel (relid=relid@entry=16402, relation=relation@entry=0x0,
>     params=params@entry=0x7ffec28585f0, skip_privs=skip_privs@entry=true,
>     bstrategy=bstrategy@entry=0x14ddca8)
>     at ../src/backend/commands/vacuum.c:2202
> #7  0x000000000069b0e4 in vacuum_rel (relid=16398, relation=<optimized out>,
>     params=params@entry=0x7ffec2858850, skip_privs=skip_privs@entry=false,
>     bstrategy=bstrategy@entry=0x14ddca8)
>     at ../src/backend/commands/vacuum.c:2236

Good catch. I think the problem is that vacuum_rel() is called
recursively and we don't reset VacuumFailsafeActive before vacuuming
the toast table. I think we should reset it in heap_vacuum_rel()
instead of Assert(). It's possible that we trigger the failsafe mode
only for either one.Please find the attached patch.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com