public inbox for [email protected]help / color / mirror / Atom feed
[PATCH] Auto vacuum should still run when clock is set back 4+ messages / 2 participants [nested] [flat]
* [PATCH] Auto vacuum should still run when clock is set back @ 2026-03-24 20:38 Cliff Clark <[email protected]> 2026-03-25 01:55 ` Re: [PATCH] Auto vacuum should still run when clock is set back Michael Paquier <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Cliff Clark @ 2026-03-24 20:38 UTC (permalink / raw) To: [email protected] <[email protected]> If the system clock is set back a large amount after PostgreSQL starts up, auto vacuum may stop running for a very long time. Attached is a small patch to reschedule the auto vacuum workers when the auto vacuum launcher detects that the current time is before the last time it was run. I tested this with a script that simply updated a bunch of rows in a test table. Without the fix, the database files keep growing, with the fix, the storage used remains stable. The patch is based on version f227b7b20c36b7348867eec48d539d28cfdf831c on the master branch of the git repo as of this morning. Thank you, Cliff Clark ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [PATCH] Auto vacuum should still run when clock is set back 2026-03-24 20:38 [PATCH] Auto vacuum should still run when clock is set back Cliff Clark <[email protected]> @ 2026-03-25 01:55 ` Michael Paquier <[email protected]> 2026-03-25 16:21 ` Re: [PATCH] Auto vacuum should still run when clock is set back Cliff Clark <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Michael Paquier @ 2026-03-25 01:55 UTC (permalink / raw) To: Cliff Clark <[email protected]>; +Cc: [email protected] <[email protected]> On Tue, Mar 24, 2026 at 08:38:21PM +0000, Cliff Clark wrote: > Attached is a small patch to reschedule the auto vacuum workers when > the auto vacuum launcher detects that the current time is before the > last time it was run. Do you mean clock changes, particularly due to Winter/Summer time switches? > I tested this with a script that simply updated a bunch of rows in a > test table. Without the fix, the database files keep growing, with > the fix, the storage used remains stable. > > The patch is based on version > f227b7b20c36b7348867eec48d539d28cfdf831c on the master branch of the > git repo as of this morning. You have not posted a patch, as far as I can see, so it is not really possible to comment about your intention. -- Michael Attachments: [application/pgp-signature] signature.asc (833B, 2-signature.asc) download ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [PATCH] Auto vacuum should still run when clock is set back 2026-03-24 20:38 [PATCH] Auto vacuum should still run when clock is set back Cliff Clark <[email protected]> 2026-03-25 01:55 ` Re: [PATCH] Auto vacuum should still run when clock is set back Michael Paquier <[email protected]> @ 2026-03-25 16:21 ` Cliff Clark <[email protected]> 2026-03-25 16:24 ` Re: [PATCH] Auto vacuum should still run when clock is set back Cliff Clark <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Cliff Clark @ 2026-03-25 16:21 UTC (permalink / raw) To: Michael Paquier <[email protected]>; +Cc: [email protected] <[email protected]> The clock changes are not due to DST, but the UTC system time. There was an NTP server on an offline network that went from 2019 to 2006 due to GPS week number rollover. The date being wrong did cause some problems, but nothing fatal, but several machines filled up their disk to the point of failure because autovacuum was no longer running. I will attempt to attach the patch again. ________________________________________ From: Michael Paquier <[email protected]> Sent: Tuesday, March 24, 2026 18:55 To: Cliff Clark <[email protected]> Cc: [email protected] <[email protected]> Subject: Re: [PATCH] Auto vacuum should still run when clock is set back [You don't often get email from [email protected]. Learn why this is important at https://aka.ms/LearnAboutSenderIdentification ] [Caution - External] Attachments: [text/x-patch] 0001-Autovacuum-should-still-run-when-clock-is-set-back.patch (1.8K, 2-0001-Autovacuum-should-still-run-when-clock-is-set-back.patch) download | inline diff: From a7f04a8b5202eb794ed6846bb8fddccdeecd643c Mon Sep 17 00:00:00 2001 From: Cliff Clark <[email protected]> Date: Tue, 24 Mar 2026 11:32:43 -0700 Subject: [PATCH] Autovacuum should still run when clock is set back Add code to detect when the clock was set back since the last autovacuum launcher run. When this happens, rebuild the database list so that autovacuum will continue to run. --- src/backend/postmaster/autovacuum.c | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 219673db930..9f2c6434802 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -377,6 +377,7 @@ void AutoVacLauncherMain(const void *startup_data, size_t startup_data_len) { sigjmp_buf local_sigjmp_buf; + TimestampTz last_current_time; Assert(startup_data_len == 0); @@ -568,6 +569,12 @@ AutoVacLauncherMain(const void *startup_data, size_t startup_data_len) AutoVacuumShmem->av_launcherpid = MyProcPid; + /* + * Set the initial last run time to just before we build the worker + * schedule. + */ + last_current_time = GetCurrentTimestamp(); + /* * Create the initial database list. The invariant we want this list to * keep is that it's ordered by decreasing next_worker. As soon as an @@ -649,6 +656,16 @@ AutoVacLauncherMain(const void *startup_data, size_t startup_data_len) */ current_time = GetCurrentTimestamp(); + if (current_time < last_current_time) + { + /* + * The clock jumped backwards so reschedule the workers so that + * databases won't stop getting auto-vacuumed. + */ + rebuild_database_list(InvalidOid); + } + last_current_time = current_time; + LWLockAcquire(AutovacuumLock, LW_SHARED); can_launch = av_worker_available(); -- 2.43.0 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [PATCH] Auto vacuum should still run when clock is set back 2026-03-24 20:38 [PATCH] Auto vacuum should still run when clock is set back Cliff Clark <[email protected]> 2026-03-25 01:55 ` Re: [PATCH] Auto vacuum should still run when clock is set back Michael Paquier <[email protected]> 2026-03-25 16:21 ` Re: [PATCH] Auto vacuum should still run when clock is set back Cliff Clark <[email protected]> @ 2026-03-25 16:24 ` Cliff Clark <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Cliff Clark @ 2026-03-25 16:24 UTC (permalink / raw) To: Michael Paquier <[email protected]>; +Cc: [email protected] <[email protected]> Let me try this again: ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-03-25 16:24 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-03-24 20:38 [PATCH] Auto vacuum should still run when clock is set back Cliff Clark <[email protected]> 2026-03-25 01:55 ` Michael Paquier <[email protected]> 2026-03-25 16:21 ` Cliff Clark <[email protected]> 2026-03-25 16:24 ` Cliff Clark <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox