Re: Should vacuum process config file reload more often

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
#8  0x000000000069c594 in vacuum (relations=0x14dde38,
    params=0x7ffec2858850, bstrategy=0x14ddca8, vac_context=0x14ddb90,
    isTopLevel=<optimized out>) at ../src/backend/commands/vacuum.c:623

[1]
https://www.postgresql.org/message-id/CAD21AoAyYBZOiB1UPCPZJHTLk0-arrq5zqNGj%2BPrsbpdUy%3Dg-g%40mail.gmail.com

--
John Naylor
EDB: http://www.enterprisedb.com